Keysproperty operations Corbelstone Residential

Ticket past its SLA

An open maintenance ticket that is losing its attendance SLA: no vendor dispatched at dispatch_share of the SLA ("no vendor"), or a vendor dispatched who has not attended by the SLA's end ("no show"). A vendor dispatched or re-dispatched late gets reattend_hours before the ticket counts again.

Definition
ticket_sla_breach

Params: dispatch_share = 0.5, reattend_hours = 2, lookback_days = 30 · evaluated at Mon 20:00

maintenance · v1 · Head of Maintenance

1

tickets at Mon 20:00

Ticket opened before now and open at now (not closed, merged or cancelled before now). No vendor: nobody dispatched before now and now past opened_at + dispatch_share x (due_at - opened_at). No show: dispatched, not attended before now, and now past due_at and past the dispatch + reattend_hours. Candidate vendors: active panel vendors of the ticket's trade, the building's city first, best rating first, not the vendor already sent.

maintenance · changed 2026-11-01: First version.

Tests

  • count_between min 0.0 max 80.0
  • unique on ticket_id
  • detector_shape

SQL

WITH open_tickets AS (
  SELECT t.*, b.city_id, b.name AS building, b.manager_id,
         CASE WHEN t.assigned_at IS NULL OR t.assigned_at > {now} THEN 'no_vendor' ELSE 'no_show' END AS mode,
         t.opened_at + (t.due_at - t.opened_at) * {dispatch_share} AS dispatch_by
  FROM tickets t JOIN buildings b ON b.id = t.building_id
  WHERE t.opened_at <= {now} AND (t.closed_at IS NULL OR t.closed_at > {now})
    AND (t.attended_at IS NULL OR t.attended_at > {now})
),
late AS (
  SELECT *, CASE WHEN mode = 'no_vendor' THEN dispatch_by
                 ELSE greatest(due_at, assigned_at + INTERVAL ({reattend_hours}) HOUR) END AS started_at
  FROM open_tickets
),
no_shows AS (
  SELECT vendor_id, count(*) AS no_shows
  FROM tickets
  WHERE vendor_id IS NOT NULL AND assigned_at <= {now} AND due_at <= {now}
    AND due_at >= {now} - INTERVAL ({lookback_days}) DAY
    AND (attended_at IS NULL OR attended_at > due_at)
  GROUP BY vendor_id
),
candidates AS (
  SELECT l.id AS ticket_id,
         list(v.id ORDER BY v.city_id = l.city_id DESC, v.rating DESC, v.on_time_rate DESC, v.id) AS ids
  FROM late l JOIN vendors v ON v.trade = l.trade AND v.active AND v.on_panel
   AND v.id IS DISTINCT FROM CASE WHEN l.mode = 'no_show' THEN l.vendor_id END
  WHERE l.started_at <= {now}
  GROUP BY l.id
)
SELECT 'ticket' AS subject_type, l.id AS subject_id, l.id AS ticket_id, l.building_id, l.unit_id,
       CASE WHEN l.mode = 'no_show' THEN l.vendor_id END AS vendor_id, l.started_at,
       CASE WHEN l.priority = 'emergency' THEN 'critical' WHEN l.priority = 'urgent' THEN 'high' ELSE 'medium' END
         AS severity,
       l.mode, coalesce(n.no_shows, 0) AS vendor_no_shows_30d,
       list_slice(c.ids, 1, 3) AS candidate_vendor_ids,
       json_object('priority', l.priority, 'category', l.category, 'trade', l.trade, 'opened_at', l.opened_at,
                   'due_at', l.due_at, 'minutes_to_due', date_diff('minute', {now}, l.due_at),
                   'assigned_at', CASE WHEN l.assigned_at <= {now} THEN l.assigned_at END,
                   'building', l.building, 'manager_id', l.manager_id, 'source', l.source,
                   'summary', l.summary) AS evidence
FROM late l
LEFT JOIN no_shows n ON n.vendor_id = l.vendor_id AND l.mode = 'no_show'
LEFT JOIN candidates c ON c.ticket_id = l.id
WHERE l.started_at <= {now}
ORDER BY l.started_at, l.id