maintenance · v2 · Head of Maintenance
0
tickets at Mon 20:00
Later ticket opened before now and still open at now; earlier ticket of the same category in the same home (up to window_hours before it) or both in the common areas of the same building (up to common_window_hours before it), not closed before the later one was opened, and not raised by a different alarm. The earliest such ticket is the original. Tickets already merged are left out.
maintenance · changed 2026-11-02: Common areas get their own, shorter window (8 hours), and tickets from two different alarms are never a pair. Chosen on seed 7: the 48-hour window paired separate faults in busy buildings' hallways and the two lifts of one building.
Tests
- count_between min 0.0 max 80.0
- unique on ticket_id
- detector_shape
SQL
WITH live AS (
SELECT * FROM tickets
WHERE opened_at <= {now} AND (status <> 'merged' OR closed_at > {now})
),
pairs AS (
SELECT t2.id AS ticket_id, t1.id AS original_ticket_id, t2.building_id, t2.unit_id, t2.category,
t2.opened_at, t1.opened_at AS original_opened_at, t2.vendor_id, t1.vendor_id AS original_vendor_id,
t2.source, t1.source AS original_source, t2.summary,
row_number() OVER (PARTITION BY t2.id ORDER BY t1.opened_at, t1.id) AS rn
FROM live t2
JOIN live t1 ON t1.building_id = t2.building_id AND t1.category = t2.category AND t1.id <> t2.id
AND t1.unit_id IS NOT DISTINCT FROM t2.unit_id
AND t1.opened_at < t2.opened_at
AND t1.opened_at >= t2.opened_at - CASE WHEN t2.unit_id IS NULL THEN INTERVAL ({common_window_hours}) HOUR
ELSE INTERVAL ({window_hours}) HOUR END
AND NOT (t1.alarm_id IS NOT NULL AND t2.alarm_id IS NOT NULL AND t1.alarm_id <> t2.alarm_id)
AND (t1.closed_at IS NULL OR t1.closed_at > t2.opened_at)
WHERE (t2.closed_at IS NULL OR t2.closed_at > {now})
AND t1.status <> 'merged'
)
SELECT 'ticket' AS subject_type, ticket_id AS subject_id, ticket_id, original_ticket_id, building_id, unit_id,
opened_at AS started_at, 'low' AS severity,
json_object('category', category, 'scope', CASE WHEN unit_id IS NULL THEN 'common' ELSE 'home' END,
'minutes_apart', date_diff('minute', original_opened_at, opened_at),
'source', source, 'original_source', original_source,
'vendor_id_on_duplicate', CASE WHEN vendor_id IS NOT NULL THEN vendor_id END,
'original_vendor_id', original_vendor_id, 'summary', summary) AS evidence
FROM pairs WHERE rn = 1
ORDER BY started_at, ticket_id