SLA Tracking and Analytics

SLA Tracking and Analytics

ApprovalML automatically tracks Service Level Agreement (SLA) duration for every workflow step, enabling comprehensive performance monitoring and bottleneck identification.

Overview

SLA is the time from when a step is created (created_at) to when it's completed (approved_at). This is automatically calculated and stored in seconds for all workflow steps.

🔢 How SLA is Calculated

The system evaluates step performance by comparing the actual duration taken against the target deadline.

Calculation Context

• Historical Reference Window: The system uses a 90-day rolling window as the reference period to calculate your organization's baseline performance, helping to normalize data and identify long-term trends.
• SLA Percentage: SLA % = (Duration Taken / Target SLA) * 100

Example: The 3-Day SLA Target

If your target is 3 days (72 hours):

| Status | Time Taken | SLA % | Calculation | | :--- | :--- | :--- | :--- | | Healthy | 48 hours | 66.7% | 48 / 72 | | Warning | 69.6 hours | 96.7% | 69.6 / 72 | | Breach | 144 hours | 200% | 144 / 72 |

Note: While performance is benchmarked against your 90-day historical reference window, individual step breaches are triggered immediately when a step exceeds its sla target defined in your YAML.

What Gets Tracked

• ✅ Manual approval steps - Time until approver takes action
• ✅ Automatic steps - API call response time
• ✅ Parallel approvals - Individual step completion time
• ✅ Signature steps - Time to sign
• ✅ Acknowledgement steps - Time to acknowledge
• ✅ All approval types - Comprehensive coverage

Storage

SLA data is stored in the approval_steps table:

ALTER TABLE approval_steps
ADD COLUMN sla_seconds INTEGER;

Automatic Calculation:

• Calculated when step status changes to approved, rejected, or completed
• Stored persistently for historical analysis
• Indexed for fast query performance

---

UI Display

Workflow Chronology

SLA appears as a blue badge next to each completed step in the Workflow Chronology section:

Step Approved

manager_approval

SLA: 2m 30s

By John Manager

Format Examples

| SLA Value | Display | Scenario | |-----------|---------|----------| | 15 seconds | SLA: 15s | Quick automatic step or fast approval | | 5 minutes 30 seconds | SLA: 5m 30s | Typical approval review | | 2 hours 15 minutes | SLA: 2h 15m | Extended review process | | 3 days 5 hours | SLA: 3d 5h | Multi-day approvals |

---

Analytics and Reporting

Average SLA by Step

SELECT
    step_name,
    COUNT(*) as total_steps,
    ROUND(AVG(sla_seconds) / 60, 2) as avg_minutes,
    ROUND(AVG(sla_seconds) / 3600, 2) as avg_hours,
    MIN(sla_seconds) / 60 as min_minutes,
    MAX(sla_seconds) / 3600 as max_hours
FROM approval_steps
WHERE sla_seconds IS NOT NULL
    AND company_id = 1  -- Your company ID
GROUP BY step_name
ORDER BY avg_hours DESC;

SLA Performance by Approver

SELECT
    e.name as approver_name,
    e.department,
    COUNT(*) as total_approvals,
    ROUND(AVG(ast.sla_seconds) / 60, 2) as avg_minutes,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ast.sla_seconds) / 60 as median_minutes,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ast.sla_seconds) / 3600 as p95_hours
FROM approval_steps ast
JOIN employees e ON ast.approver_id = e.id
WHERE ast.sla_seconds IS NOT NULL
    AND ast.company_id = 1
GROUP BY e.id, e.name, e.department
HAVING COUNT(*) >= 5  -- At least 5 approvals
ORDER BY avg_minutes ASC;

SLA Trends Over Time

SELECT
    DATE_TRUNC('month', ast.created_at) as month,
    COUNT(*) as total_steps,
    ROUND(AVG(ast.sla_seconds) / 3600, 2) as avg_hours,
    COUNT(CASE WHEN ast.sla_seconds <= 3600 THEN 1 END) as within_1_hour,
    COUNT(CASE WHEN ast.sla_seconds > 3600 AND ast.sla_seconds <= 86400 THEN 1 END) as within_1_day,
    COUNT(CASE WHEN ast.sla_seconds > 86400 THEN 1 END) as over_1_day
FROM approval_steps ast
WHERE ast.sla_seconds IS NOT NULL
    AND ast.company_id = 1
    AND ast.created_at >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', ast.created_at)
ORDER BY month DESC;

SLA by Workflow Type

SELECT
    aw.name as workflow_name,
    ast.step_name,
    COUNT(*) as total_steps,
    ROUND(AVG(ast.sla_seconds) / 60, 2) as avg_minutes,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ast.sla_seconds) / 3600 as p95_hours
FROM approval_steps ast
JOIN approval_instances ai ON ast.instance_id = ai.id
JOIN approval_workflows aw ON ai.workflow_id = aw.id
WHERE ast.sla_seconds IS NOT NULL
    AND ast.company_id = 1
GROUP BY aw.id, aw.name, ast.step_name
ORDER BY aw.name, avg_minutes DESC;

SLA Violations (Exceeding Targets)

-- Find steps that took longer than 24 hours
SELECT
    ast.id,
    ast.step_name,
    aw.name as workflow_name,
    e.name as approver_name,
    ROUND(ast.sla_seconds / 3600.0, 2) as sla_hours,
    ast.created_at,
    ast.approved_at
FROM approval_steps ast
JOIN approval_instances ai ON ast.instance_id = ai.id
JOIN approval_workflows aw ON ai.workflow_id = aw.id
LEFT JOIN employees e ON ast.approver_id = e.id
WHERE ast.sla_seconds > 86400  -- 24 hours
    AND ast.company_id = 1
ORDER BY ast.sla_seconds DESC
LIMIT 50;

Parallel vs Sequential Step Performance

SELECT
    CASE
        WHEN ast.parent_step_id IS NOT NULL THEN 'Parallel'
        ELSE 'Sequential'
    END as step_type,
    COUNT(*) as total_steps,
    ROUND(AVG(ast.sla_seconds) / 60, 2) as avg_minutes,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ast.sla_seconds) / 60 as median_minutes
FROM approval_steps ast
WHERE ast.sla_seconds IS NOT NULL
    AND ast.company_id = 1
    AND ast.approval_type = 'needs_to_approve'
GROUP BY CASE WHEN ast.parent_step_id IS NOT NULL THEN 'Parallel' ELSE 'Sequential' END;

Department Performance Comparison

SELECT
    e.department,
    COUNT(*) as total_approvals,
    ROUND(AVG(ast.sla_seconds) / 3600, 2) as avg_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ast.sla_seconds) / 60 as median_minutes,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ast.sla_seconds) / 3600 as p95_hours
FROM approval_steps ast
JOIN employees e ON ast.approver_id = e.id
WHERE ast.sla_seconds IS NOT NULL
    AND ast.company_id = 1
GROUP BY e.department
ORDER BY avg_hours ASC;

---

Automatic Step Performance (API Response Time)

Track how long automatic steps (data source fetches, API calls) take to execute:

SELECT
    ast.step_name,
    COUNT(*) as total_executions,
    ROUND(AVG(ast.sla_seconds), 2) as avg_seconds,
    MIN(ast.sla_seconds) as min_seconds,
    MAX(ast.sla_seconds) as max_seconds,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ast.sla_seconds) as p95_seconds
FROM approval_steps ast
WHERE ast.sla_seconds IS NOT NULL
    AND ast.company_id = 1
    AND ast.approver_role = 'system'  -- Automatic steps
GROUP BY ast.step_name
ORDER BY avg_seconds DESC;

Use Cases:

• Identify slow API endpoints
• Monitor data source performance
• Detect integration bottlenecks
• Optimize automatic step efficiency

---

Setting SLA Targets

Configuration (YAML)

workflow:
  manager_approval:
    name: "Manager Review"
    type: decision
    approver: manager
    sla: "24_hours"
    on_approve:
      continue_to: next_step

How SLA enforcement works

| Threshold | Action | |-----------|--------| | 75% of sla elapsed | Email reminder to current approver | | 100% of sla elapsed | Step auto-reassigned to approver's direct manager |

---

Best Practices

1. Set Realistic Targets

Define SLA targets based on historical data:

-- Analyze current performance before setting targets
SELECT
    step_name,
    ROUND(AVG(sla_seconds) / 3600, 2) as current_avg_hours,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sla_seconds) / 3600 as p75_hours,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY sla_seconds) / 3600 as p90_hours
FROM approval_steps
WHERE company_id = 1 AND sla_seconds IS NOT NULL
GROUP BY step_name;

Set targets slightly above P75 to allow for realistic achievement.

2. Regular Reviews

• Weekly: Review longest SLA violations
• Monthly: Analyze department performance trends
• Quarterly: Adjust SLA targets based on data

3. Identify Bottlenecks

-- Find consistently slow steps
SELECT
    step_name,
    COUNT(*) as occurrences,
    ROUND(AVG(sla_seconds) / 3600, 2) as avg_hours,
    COUNT(CASE WHEN sla_seconds > 172800 THEN 1 END) as over_2_days_count
FROM approval_steps
WHERE company_id = 1 AND sla_seconds IS NOT NULL
GROUP BY step_name
HAVING AVG(sla_seconds) > 86400  -- Average over 24 hours
ORDER BY avg_hours DESC;

4. Optimize Parallel Approvals

Compare parallel vs sequential performance to determine optimal workflow structure:

-- Measure workflow total time with parallel steps
SELECT
    ai.id as instance_id,
    aw.name as workflow_name,
    MAX(ast.approved_at) - MIN(ast.created_at) as total_duration
FROM approval_instances ai
JOIN approval_workflows aw ON ai.workflow_id = aw.id
JOIN approval_steps ast ON ast.instance_id = ai.id
WHERE ai.company_id = 1
    AND ai.status = 'approved'
GROUP BY ai.id, aw.name
ORDER BY total_duration DESC
LIMIT 20;

5. Monitor Automatic Steps

Set alerts for slow API calls:

-- Alert on automatic steps taking > 30 seconds
SELECT
    step_name,
    COUNT(*) as slow_executions,
    AVG(sla_seconds) as avg_seconds
FROM approval_steps
WHERE approver_role = 'system'
    AND sla_seconds > 30  -- Over 30 seconds
    AND created_at >= NOW() - INTERVAL '7 days'
GROUP BY step_name
ORDER BY slow_executions DESC;

---

Backfilling Historical Data

If you have historical approval steps without SLA data:

-- Backfill SLA for existing completed steps
UPDATE approval_steps
SET sla_seconds = EXTRACT(EPOCH FROM (approved_at - created_at))::INTEGER
WHERE sla_seconds IS NULL
    AND approved_at IS NOT NULL
    AND created_at IS NOT NULL
    AND company_id = 1;

Note: Run this once after the SLA feature is deployed to populate historical data.

---

Related Documentation

• Parallel Approval - Multiple approvers with SLA tracking
• Decision Steps - Approval step configuration
• Conditional Routing - Dynamic workflow paths
• Form Integration - Data collection and validation

---

Technical Details

Database Schema

-- Column definition
approval_steps.sla_seconds INTEGER DEFAULT NULL

-- Indexes for performance
CREATE INDEX idx_approval_steps_sla_seconds
    ON approval_steps(sla_seconds)
    WHERE sla_seconds IS NOT NULL;

CREATE INDEX idx_approval_steps_sla_analysis
    ON approval_steps(company_id, step_name, sla_seconds)
    WHERE sla_seconds IS NOT NULL;

Calculation Logic

Run Mode (SQL):

UPDATE approval_steps
SET sla_seconds = EXTRACT(EPOCH FROM (approved_at - created_at))::INTEGER
WHERE id = $1;

Test Mode (Python):

if step.created_at and approved_at:
    sla_delta = approved_at - step.created_at
    step.sla_seconds = int(sla_delta.total_seconds())

---

Summary

✅ Automatic Tracking - No configuration needed, works out of the box ✅ Comprehensive Coverage - All step types tracked ✅ Persistent Storage - Data stored for historical analysis ✅ Fast Queries - Indexed for performance ✅ UI Display - Visible in Workflow Chronology ✅ Analytics Ready - SQL queries for insights

SLA tracking enables data-driven workflow optimization and performance monitoring without any additional setup required.