RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

Double booking

Two live bookings that overlap in time with the same clinician or in the same room. The later booking is the one listed; the earlier one is in the evidence.

Unit: appointments · parameters: horizon days 14 · also said as: double booking, double booked, double-booked, clash, room clash, clinician clash, overlapping bookings

Booking

Head of booking (booking) · version 1, changed 2026-11-02

Appointments starting after now and within horizon_days, booked by now and not cancelled by now. For a room clash the evidence names a free room in the same clinic; for a clinician clash, a colleague of the same specialty with a session and no booking at that time, when there is one.

First version.

WITH live AS (
  SELECT a.id, a.clinician_id, a.room_id, a.clinic_id, a.start_at, a.end_at, a.booked_at, a.patient_id,
         a.specialty_id
  FROM appointments a
  WHERE a.booked_at <= {now} AND (a.cancelled_at IS NULL OR a.cancelled_at > {now})
    AND a.start_at > {now} AND a.start_at <= {now} + INTERVAL ({horizon_days}) DAY
),
clashes AS (
  SELECT n.*, o.id AS other_appointment_id, 'clinician' AS clash
  FROM live n JOIN live o ON o.clinician_id = n.clinician_id AND o.id <> n.id
    AND o.start_at < n.end_at AND n.start_at < o.end_at
    AND (o.booked_at < n.booked_at OR (o.booked_at = n.booked_at AND o.id < n.id))
  UNION ALL
  SELECT n.*, o.id AS other_appointment_id, 'room' AS clash
  FROM live n JOIN live o ON o.room_id = n.room_id AND o.clinician_id <> n.clinician_id AND o.id <> n.id
    AND o.start_at < n.end_at AND n.start_at < o.end_at
    AND (o.booked_at < n.booked_at OR (o.booked_at = n.booked_at AND o.id < n.id))
),
options AS (
  SELECT x.*,
         (SELECT r.id FROM rooms r WHERE r.clinic_id = x.clinic_id AND NOT EXISTS (
             SELECT 1 FROM live y WHERE y.room_id = r.id AND y.start_at < x.end_at AND x.start_at < y.end_at)
          ORDER BY r.kind = 'treatment room', r.id LIMIT 1) AS free_room_id,
         (SELECT min(s.clinician_id) FROM sessions s JOIN clinicians k ON k.id = s.clinician_id
           WHERE s.clinic_id = x.clinic_id AND k.specialty_id = x.specialty_id AND s.clinician_id <> x.clinician_id
             AND s.status = 'planned' AND (s.cancelled_at IS NULL OR s.cancelled_at > {now})
             AND s.start_at <= x.start_at AND s.end_at >= x.end_at
             AND NOT EXISTS (SELECT 1 FROM live y WHERE y.clinician_id = s.clinician_id
                               AND y.start_at < x.end_at AND x.start_at < y.end_at)) AS free_clinician_id
  FROM clashes x
)
SELECT 'appointment' AS subject_type, x.id AS subject_id, x.id AS appointment_id, x.other_appointment_id,
       x.patient_id, x.clinic_id, x.clinician_id, x.room_id, x.booked_at AS started_at,
       CASE WHEN x.start_at < {now} + INTERVAL 1 DAY THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'clash', x.clash, 'start_at', x.start_at, 'end_at', x.end_at, 'clinic', c.name,
         'appointment_ids', [x.id, x.other_appointment_id], 'free_room_id', x.free_room_id,
         'free_room_kind', fr.kind, 'free_clinician_id', x.free_clinician_id
       ) AS evidence
FROM options x JOIN clinics c ON c.id = x.clinic_id LEFT JOIN rooms fr ON fr.id = x.free_room_id
ORDER BY started_at, x.id

Tests: count_between, unique