# Ticket SLA & Escalation

**Date:** 2026-05-31  
**Status:** Draft  
**Spec:** 91  
**Tier:** Business+  
**Depends on:** `crm-support-center`, `system-communications-notifications`, `foundation-auth-rbac`  
**Referenced by:** `crm-support-center`

---

## Overview

Spec 14 (`crm-support-center`) defines tickets with `priority` (low/medium/high/urgent) but no SLA targets or breach escalation. This spec adds: per-priority response time targets, a `due_at` column on tickets, breach detection via cron, and escalation notifications.

---

## Data Model

```sql
ALTER TABLE tickets ADD COLUMN due_at TIMESTAMPTZ;
ALTER TABLE tickets ADD COLUMN first_response_at TIMESTAMPTZ;
ALTER TABLE tickets ADD COLUMN sla_breached BOOLEAN DEFAULT false;

CREATE TABLE sla_policies (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     UUID NOT NULL REFERENCES tenants(id),
  priority      TEXT NOT NULL CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  first_response_hours INTEGER NOT NULL,  -- SLA target: first response within N hours
  resolution_hours     INTEGER NOT NULL,  -- SLA target: resolution within N hours
  escalation_email     TEXT,              -- per-priority escalation recipient (nullable)
  notify_email         BOOLEAN NOT NULL DEFAULT true,  -- per-policy: send escalation email on breach
  notify_in_app        BOOLEAN NOT NULL DEFAULT true,  -- per-policy: send in-app notification on breach
  created_at    TIMESTAMPTZ DEFAULT now(),
  updated_at    TIMESTAMPTZ DEFAULT now(),
  UNIQUE (tenant_id, priority)
);
```

Default SLA targets (seeded for each tenant on Business+ upgrade):

| Priority | First response | Resolution |
|----------|---------------|-----------|
| urgent | 1 hour | 4 hours |
| high | 4 hours | 24 hours |
| medium | 8 hours | 72 hours |
| low | 24 hours | 168 hours (1 week) |

`due_at` = `tickets.created_at + sla_policies.resolution_hours` (set on ticket create or priority change).

---

## SLA Breach Detection

Cron: `ticket-sla-check` — runs every 15 minutes (Cloudflare Cron Trigger: `*/15 * * * *`).

```sql
-- Find tickets past resolution SLA:
UPDATE tickets SET sla_breached = true
WHERE status NOT IN ('resolved', 'closed')
  AND sla_breached = false
  AND due_at < now()
  AND tenant_id IN (
    SELECT DISTINCT tenant_id FROM sla_policies
  )
RETURNING id, tenant_id, assignee_id, priority, customer_id
```

On each newly-breached ticket, look up the matching `sla_policies` row (by priority):
1. If the policy's `notify_in_app` is true: emit in-app notification to assignee and OWNER/ADMIN
2. If the policy's `notify_email` is true and its `escalation_email` is set: send the escalation email

---

## First Response Tracking

When a staff member (not customer) posts the first message on a ticket:

```ts
if (!ticket.first_response_at) {
  await tx.update(tickets).set({ first_response_at: now() }).where(eq(tickets.id, id))
  // Check if first response SLA was met
  const policy = await getSlaPolicy(tenantId, ticket.priority)
  const firstResponseDeadline = ticket.created_at + policy.first_response_hours * 3600000
  if (now() > firstResponseDeadline) {
    // Log first_response_sla_breached event (in-app notification, not re-escalation)
  }
}
```

---

## Ticket Detail: SLA Badge

```
┌──────────────────────────────────────────────────────────────┐
│  Ticket #TKT-0041  [HIGH]  [OPEN]                            │
│                                                              │
│  Customer: Acme Corp — Login page broken                    │
│                                                              │
│  SLA: Due by 2026-06-01 16:00  [⚡ 3h 20min remaining]      │
│       First response: ✅ Responded (within SLA)             │
│                                                              │
│  ─────────────────────────────────────────────────────────   │
│  ...ticket messages...                                       │
└──────────────────────────────────────────────────────────────┘
```

SLA badge variants:
- `[⚡ 3h remaining]` — green/warning when within 25% of deadline
- `[⏰ Overdue by 2h]` — red badge after `due_at` passed
- `[✅ Resolved within SLA]` — shown on resolved/closed tickets

---

## Escalation Configuration

New section in `/settings/sla` (spec 145, `sla-config-ui` — the canonical SLA settings page):

```
┌──────────────────────────────────────────────────────────────┐
│  SLA Policies                          (Business+ feature)   │
│                                                              │
│  Priority    First response   Resolution   Escalation email  │
│  Urgent      [1___] hours     [4___] hours  [cto@acme.com__] │
│  High        [4___] hours     [24__] hours  [support@acme.__]│
│  Medium      [8___] hours     [72__] hours  [support@acme.__]│
│  Low         [24__] hours     [168_] hours  [______________ ]│
│                                                              │
│  Per priority, edited in the row's [Edit] sheet:            │
│    ☑ In-app notification on breach   (notify_in_app)        │
│    ☑ Email escalation address on breach (notify_email)      │
└──────────────────────────────────────────────────────────────┘
```

`escalation_email`, `notify_email`, and `notify_in_app` are per-priority columns on `sla_policies` (created with the table — see Data Model above). Breach routing is per-policy; there is **no** global escalation toggle.

---

## Ticket List: SLA Column

Ticket list view gains SLA column:

```
│ Subject             │ Customer  │ Priority │ Assignee │ SLA status │
│ Login page broken   │ Acme Corp │ HIGH     │ Dana     │ ⚡ 3h left  │
│ Invoice missing     │ Beta Ltd  │ MEDIUM   │ Ronen    │ ✅ On track │
│ Export fails        │ Gama Inc  │ URGENT   │ Unassigned│ 🔴 Breached│
```

Filterable: "SLA breached only" filter chip.

---

## Schema Delta on tenant_settings

```sql
ALTER TABLE tenant_settings ADD COLUMN sla_enabled BOOLEAN NOT NULL DEFAULT false;
```

SLA tracking is only computed for tenants with `sla_enabled = true` (Business+ gate). This is the single `tenant_settings` column this spec owns (base table owned by foundation-auth-rbac), read/written via a `tenantQuery` domain helper — never the AI-config `getTenantSettings`/`upsertTenantSettings`. Breach-notification routing is **per-policy** (`sla_policies.notify_email` / `notify_in_app`), not a global tenant flag.

---

## API

```
GET /api/settings/sla
    → get tenant SLA policies + sla_enabled
      Requires: OWNER, ADMIN (Business+)

PATCH /api/settings/sla/:policyId
    → update one priority's SLA policy
      body: { first_response_hours?, resolution_hours?, escalation_email?, notify_email?, notify_in_app? }
      Requires: OWNER (Business+)

GET /api/tickets?sla_breached=true
    → filter breached tickets
      (extends existing list endpoint)
```

---

## Architecture Decisions

| Decision | Choice | Reason |
|----------|--------|--------|
| `sla_policies` table | Not hardcoded constants | Tenants have different SLA commitments; israelis business service SLAs vary widely |
| 15-minute cron | Not per-ticket timer | Cloudflare Cron is the right primitive; per-ticket timers (Durable Objects) are overkill for SLA checks |
| `due_at` on tickets | Not computed on-read | Indexed for range queries; cron UPDATE is O(breached tickets), not O(all tickets) |
| Business+ gate | Not all tiers | SLA breach emails + escalation routing are support-team features; Freelancer tier has no support team |