Keysproperty operations Corbelstone Residential

Duplicate ticket

A second open ticket for a fault that already has an open ticket: same home and category (or, for a common area, same building and category), opened within window_hours of the first (common_window_hours for a common area) while the first was still open. Two tickets raised by two different alarms are two faults on two pieces of equipment.

Definition
duplicate_ticket

Params: window_hours = 48, common_window_hours = 8 · evaluated at Mon 20:00

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