The scheduling core of a medical practice, built as a Postgres project: what does it take to make double-booking structurally impossible rather than merely unlikely?
- Problem
- Double-booking is a race condition. Check-then-insert reads an open slot, finds it free, and writes — and so does the session running the same three steps a millisecond later. Retrying around it moves the race rather than removing it.
- Constraint
- I wanted the rule to hold for every writer, not just the ones that go through a booking service — a backfill script, a seed, a psql session. That rules out enforcement in application code.
- Decision
- Push the invariant into Postgres. Provider time — appointments, holds, time off, admin blocks — is one table with a tstzrange and a gist exclusion constraint, so two overlapping live claims on the same provider cannot both exist. There is no patient on that table: an appointment points at a claim rather than carrying its own times, so one constraint covers every way a calendar can be occupied, and a vacation block prevents double-booking for free. The losing write fails at insert time; the booking function turns that failure into a named
slot_taken error.
- Tradeoff
- Callers have to treat a constraint violation as an expected outcome rather than a bug, and the scheduling rules live in migrations instead of in code a developer reads first. In exchange the invariant is a property of the data, not of the paths that happen to reach it.
The constraint — migrations/0003_occupancy.sql, trimmed
CREATE TYPE claim_kind AS ENUM ('appointment', 'hold', 'time_off', 'admin');
CREATE TABLE time_claim (
id uuid PRIMARY KEY DEFAULT uuidv7(),
provider_id uuid NOT NULL REFERENCES provider(id),
during tstzrange NOT NULL,
kind claim_kind NOT NULL,
expires_at timestamptz, -- holds only: NULL = never lapses
released_at timestamptz, -- set on cancel; the row stays
-- two live claims on one provider cannot overlap.
-- not a check the app runs. a fact about the table.
CONSTRAINT no_overlap EXCLUDE USING gist (
provider_id WITH =,
during WITH &&
) WHERE (released_at IS NULL)
);
Cancellation is a release, not a delete. Appointment status changes cascade into released_at through a trigger — canceled and bumped free the time, a no-show deliberately does not — and the row stays, so the history survives. The same table carries time-limited holds, which is the groundwork for an ASAP waitlist: a released slot is held for one patient at a time, and a hold that nobody takes lapses on its own rather than needing a sweeper to be running.