SLA Tracking & Reporting for Helpdesk

[SvenVw]

Context

SLA (Service Level Agreement) tracking ensures tickets get timely responses and resolutions. Business-hours-aware SLA calculation accounts for evenings, weekends, and holidays when agents aren't working. The reporting dashboard gives managers visibility into team performance.

Depends on: #599 (MVP — tickets with timestamps), #603 (Agent Availability — work schedule context)

Scope

In Scope

• SLA policies: configurable per priority (e.g., high = 4h response, 24h resolution)
• Business-hours calculator: counts only working hours (Europe/Amsterdam, Mon–Fri 8:00–17:30 default)
• SLA pause/resume: clock pauses on waiting_on_customer, resumes on customer reply
• Breach detection: track when SLA is breached, send internal notification
• Warning at 80%: alert agent when SLA is 80% consumed
• helpdesk_settings table: store business hours config as JSON
• sla_paused_at column on tickets: track when clock was paused
• Agent metrics view: avg response time, avg resolution time, tickets per period
• Dashboard UI (/admin/support/dashboard): metrics overview, SLA breach list, ticket volume chart

Out of Scope

• Customer-facing SLA commitments / external SLA display
• Per-customer SLA tiers
• Holiday calendar integration (workdays covers it)
• Real-time WebSocket updates on dashboard

Acceptance Criteria

• SLA policies can be configured per priority (first response target + resolution target in business hours)
• SLA due dates are calculated using business hours only (skip nights/weekends)
• SLA clock pauses when ticket status is waiting_on_customer
• SLA clock resumes when customer adds a new message
• Agents see SLA remaining time on ticket cards (color-coded: green/yellow/red)
• At 80% SLA consumption, agent sees a warning indicator on the ticket
• Breached SLAs are logged and visible in the dashboard
• Admin dashboard shows: total tickets, avg first response time, avg resolution time, open vs resolved, SLA breach count
• Dashboard metrics can be filtered by date range (last 7d, 30d, 90d)
• Agent performance table shows per-agent metrics (response time, resolution time, ticket count)

Technical Implementation

1. Schema Additions

Settings table (shared config):

export const helpdeskSettings = fdmHelpdeskSchema.table("helpdesk_settings", {
    key: text().primaryKey(),
    value: jsonb().notNull(),
    updated_by: text(),
    updated: timestamp({ withTimezone: true }).notNull().defaultNow(),
})

SLA columns on tickets:

// Added to tickets table:
sla_first_response_due: timestamp({ withTimezone: true }),
sla_resolution_due: timestamp({ withTimezone: true }),
sla_first_response_met: boolean(),
sla_resolution_met: boolean(),
sla_paused_at: timestamp({ withTimezone: true }),
sla_paused_duration: integer().notNull().default(0),  // accumulated pause time in minutes

2. SLA Policies Configuration

Stored in helpdesk_settings with key sla_policies:

{
    "business_hours": {
        "timezone": "Europe/Amsterdam",
        "workdays": [1, 2, 3, 4, 5],
        "start_hour": 8,
        "end_hour": 17.5
    },
    "policies": {
        "urgent": { "first_response_hours": 1, "resolution_hours": 8 },
        "high": { "first_response_hours": 4, "resolution_hours": 24 },
        "normal": { "first_response_hours": 8, "resolution_hours": 48 },
        "low": { "first_response_hours": 24, "resolution_hours": 120 }
    }
}

3. Business-Hours Calculator

export function calculateSlaDueDate(
    createdAt: Date,
    targetHours: number,
    businessHours: BusinessHoursConfig,
): Date {
    // Walk forward from createdAt, counting only business hours
    // Skip weekends and outside start_hour/end_hour
    // Returns the due date in UTC
}

export function calculateRemainingMinutes(
    dueDate: Date,
    now: Date,
    pausedDuration: number,
    businessHours: BusinessHoursConfig,
): number {
    // Returns remaining business minutes (can be negative if breached)
}

4. SLA Lifecycle

1. Ticket created → calculate sla_first_response_due and sla_resolution_due based on priority
2. Agent sends first reply → set sla_first_response_met = true/false based on whether due was exceeded
3. Status → waiting_on_customer → set sla_paused_at = now()
4. Customer replies (status leaves WOC) → add elapsed pause to sla_paused_duration, clear sla_paused_at, recalculate sla_resolution_due
5. Ticket resolved/closed → set sla_resolution_met = true/false

5. SLA Visual Indicators

On ticket cards and ticket detail:

// In ticket list:
TK-A3B2C4 · Kan niet inloggen · 🟢 2u 15m

// Color coding:
🟢 Green:  > 50% remaining
🟡 Yellow: 20–50% remaining (80% warning)
🔴 Red:    < 20% remaining or breached
⚫ Black:  Breached (past due)

6. UI — Admin Dashboard (/admin/support/dashboard)

┌──────────────────────────────────────────────────────────────────────────────┐
│  📊 Helpdesk Dashboard                            [Laatste 30 dagen ▾]       │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  ┌──────────┐  ┌──────────────────┐  ┌──────────────────┐  ┌────────────┐  │
│  │ 42       │  │ 3u 12m           │  │ 18u 45m          │  │ 3          │  │
│  │ Tickets  │  │ Gem. 1e reactie  │  │ Gem. oplostijd   │  │ SLA-breach │  │
│  └──────────┘  └──────────────────┘  └──────────────────┘  └────────────┘  │
│                                                                              │
│  ── Ticket volume (afgelopen 30d) ──                                         │
│  ▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊▊  [sparkline chart]                      │
│                                                                              │
│  ── Agent prestaties ──                                                      │
│  Agent     1e reactie   Oplostijd   Tickets   SLA %                          │
│  ───────────────────────────────────────────────────                         │
│  Jan       2u 30m       14u 20m     18        94%                            │
│  Sophie    3u 50m       22u 10m     14        86%                            │
│  Kees      4u 15m       19u 55m     10        90%                            │
│                                                                              │
│  ── SLA breaches (openstaand) ──                                             │
│  │ 🔴 TK-X9Y2Z1 · Exportprobleem · 2u over SLA        │                    │
│  │ 🔴 TK-M4N5P6 · Perceel verdwenen · 45m over SLA    │                    │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

7. App Routes

Route	Purpose
admin.support.dashboard.tsx	Metrics overview
admin.support.settings.sla.tsx	SLA policy configuration

8. Metrics Queries (SQL views or functions)

-- Average first response time (business hours) over period
-- Average resolution time (business hours) over period
-- SLA compliance rate (% tickets meeting first response + resolution)
-- Ticket volume per day/week
-- Per-agent breakdown

These can be implemented as Drizzle queries with aggregations or as PostgreSQL views for performance.

Testing Requirements

• Unit tests: Business-hours calculator (crossing weekends, overnight, exact boundary), SLA pause/resume duration math
• Integration tests: Create ticket → verify SLA due dates set → simulate agent reply → verify sla_first_response_met; pause on WOC → resume → verify duration accumulated
• Edge cases: Ticket created Friday 17:00 (due date should be Monday), pause spanning a weekend, midnight boundary

Definition of Done

Agents and managers have visibility into SLA compliance. Tickets show time remaining. The dashboard provides actionable metrics for team performance improvement.