Definitions / double_booking
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