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