Automatic Service Level Agreement tracking for workflow performance monitoring and analysis

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.

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_target: "24_hours"  # Target SLA
    on_approve:
      continue_to: next_step

settings:
  timeout:
    manager_approval: "48_hours"  # Escalation timeout
    finance_review: "3_days"
    executive_approval: "5_business_days"

SLA Target vs Timeout

Property	Purpose	Action on Breach
`sla_target`	Performance goal	Warning notification only
`timeout`	Hard deadline	Escalation or auto-action

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.

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())

✅ 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.

SLA Tracking and Analytics

On this page

SLA Tracking and Analytics

On this page