Design sample Invented clinic, invented appointments, simulated server. Not medical software and not a real booking system. Read the system design →

+ Meridian Clinic

Dr N. Ahmed · General practice

The bug you cannot find by clicking carefully.

Two patients tap the same slot in the same instant. Almost every booking system checks whether the slot is free and then writes the appointment — and the world is allowed to change between those two lines. Below, the same race runs both ways.

This week · 20 minute slots

How the write is guarded

Pick a free slot yourself, or press the button to fire two requests at one slot simultaneously. Try it in both modes.

booked 0 refused 0 double-booked 0

Request log

    How it is designed

    The check and the write are not one step

    The natural way to write this is the wrong way: 

    -- the version everybody writes first
SELECT count(*) FROM appointments
 WHERE clinician_id = $1 AND starts_at = $2;   -- says 0

-- ... and in this gap, someone else does exactly the same ...

INSERT INTO appointments (clinician_id, starts_at, patient_id)
VALUES ($1, $2, $3);                            -- both succeed

    Both requests read free. Both write. Two people have the same appointment, nothing errored, and nobody finds out until the second patient is standing at the desk. It is rare enough to survive testing and common enough to be a weekly complaint.

    Stop asking, and let the database refuse

    The fix is not a longer check — a longer check has the same gap. It is to make the rule impossible to break and write the caller to expect failure: 

    -- the rule lives in the schema, not in the application
ALTER TABLE appointments
  ADD CONSTRAINT one_patient_per_slot
  UNIQUE (clinician_id, starts_at);

-- and the insert is allowed to lose
INSERT INTO appointments (clinician_id, starts_at, patient_id)
VALUES ($1, $2, $3)
ON CONFLICT (clinician_id, starts_at) DO NOTHING
RETURNING id;                        -- no row back = you lost the race

    Whoever loses gets an immediate, honest “that slot has just gone” and the calendar refreshes under them. That is a good experience. Two people in one chair is not.

    A free slot is not a row

    The tempting model is a table of slots with an is_booked flag. It is wrong twice over: you have to generate rows forward forever, and changing the opening hours means rewriting history. Store only the appointments that exist; free time is what is left when you subtract them from the clinician’s working pattern, computed at read time. The week above is generated exactly that way.

    The constraint is the documentation

    A rule enforced in application code is a rule that holds until the next import script, admin tool or migration touches the table directly. A constraint holds against everything, including the developer at midnight with psql open. That is the whole argument for putting it in the schema rather than in a service.

    Other demos · Hire the developer