Clinic scheduling is a constraint problem wearing a calendar

Appointment booking looks like a calendar. Pick a clinician, pick a slot, save. It works for a single-doctor practice and breaks the moment a procedure needs a specific room, a specific machine, and a nurse.

The resources are plural

An appointment consumes a set of resources simultaneously. Availability is the intersection of every one being free:

appointment_resources
  appointment_id
  resource_id       -- clinician, room, device, interpreter
  resource_kind

Finding a slot means finding a window where every required resource is unbooked. Model only the clinician and the room will be double-booked by a different clinic on the same corridor.

The constraint belongs in the database

Same lesson as hotel rooms. Two requests checking availability concurrently will both pass before either writes. An exclusion constraint over resource and time range makes the overlap physically impossible:

EXCLUDE USING gist (
  resource_id WITH =,
  tstzrange(starts_at, ends_at) WITH &&
)

The application still checks first, to give a good error. The constraint is what makes it true under load.

Duration is per procedure, per clinician

A standard consultation is fifteen minutes, except for the consultant who takes twenty-five, except for a first appointment which takes forty. Storing a single slot_minutes on the clinic guarantees an overrunning list by mid-morning.

Duration is a lookup on procedure plus clinician, with a default. It is one small table and it is the difference between a schedule that holds and one that everyone works around.

Buffers are not padding

Room turnover, cleaning between procedures, notes time after a consultation. These are real occupancy of a real resource, and modelling them as slack in the slot length hides them. Book them as their own resource intervals — then a cleaning overrun shows up as what it is rather than as a clinician running late.

Cancellations free the whole set

Obvious and routinely missed: cancelling an appointment must release every resource it held, including the interpreter booked through a third party. One deletion cascading over appointment_resources does it. A cancellation that only clears the clinician leaves a room permanently unavailable and nobody knows why.