How I stopped double-bookings with a PostgreSQL exclusion constraint

Every booking system has the same nightmare: two customers book the same appointment at the same moment, and both get a confirmation email. One of them turns up to find someone already in the chair.

When I built Slotwise, an open-source booking engine for UK businesses that sell one-to-one appointments, I wanted a guarantee that this can never happen, even when 100 requests arrive for the same slot at once. Here is how I got it, and why the answer lives in the database rather than in my application code.

Why "check, then insert" isn't enough

The obvious approach is to look before you leap. When a booking request comes in, query the diary, see whether 10:00 is free, and if it is, insert the booking.

The trouble is the gap between the check and the insert. Two requests can arrive a few milliseconds apart. Both look at the diary, both see 10:00 as free, and both insert. Each request did the right thing on its own; together they produced a double-booking.

Row locks don't rescue you here either. You can only lock a row that exists, and the row that would conflict with yours hasn't been written yet. There is nothing to lock.

Let PostgreSQL refuse the overlap

PostgreSQL has a feature built for exactly this: an exclusion constraint. A unique constraint says "no two rows may have equal values". An exclusion constraint is more general. It says "no two rows may have values that satisfy this comparison". For bookings, the comparison is "same staff member, and the time ranges overlap".

This is the constraint Slotwise uses:

ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (staff_id WITH =, tstzrange(blocked_start, blocked_end, '[)') WITH &&)
  WHERE (status = 'confirmed');

Reading it in plain English:

• staff_id WITH = means the rule only applies to bookings for the same person.
• tstzrange(blocked_start, blocked_end, '[)') turns each booking into a time range, and && means "overlaps".
• blocked_start and blocked_end are the appointment widened by the service's buffers, so the setup and tidy-up time is protected too.
• '[)' makes the range include its start but not its end. A booking can start the exact moment the previous one finishes.
• WHERE (status = 'confirmed') means cancelled bookings don't block the diary.

Now, when two requests race for the same slot, PostgreSQL lets the first insert through and refuses the second with an error. There is no gap to slip through, because the check and the write are the same operation inside the database.

Why not the newer syntax?

PostgreSQL 18 added UNIQUE (… WITHOUT OVERLAPS), which uses the same GiST machinery with friendlier syntax. I looked at it and stayed with the exclusion constraint for one reason: a unique constraint can't have a WHERE clause. Without it, a cancelled booking would keep blocking its slot forever.

I also considered two code-level alternatives. Locking the staff member's row with SELECT … FOR UPDATE would work, but it moves the guarantee into code that every write path must remember to call. Advisory locks have the same weakness and are easy to get wrong. I wanted a rule that holds no matter which code path writes the booking.

Living with Prisma

Slotwise uses Prisma, and Prisma can't express an exclusion constraint in its schema. So the constraint lives in a hand-edited migration created with --create-only. I checked that prisma migrate diff shows no drift afterwards, so later migrations won't try to drop it. Each new migration is still checked by hand.

The error itself needed some care too. With Prisma 7 and @prisma/adapter-pg, PostgreSQL's error code for an exclusion violation, 23P01, arrives nested inside the driver adapter's error. One small helper, isSlotConflict, recognises that code and the constraint name, and the API turns it into a clear HTTP 409 with the code SLOT_TAKEN. The test for it triggers the real error against a real PostgreSQL database, not a mocked one.

Two details that make it pleasant

"Any staff" bookings, where the customer doesn't mind who they see, try eligible people in a fixed order: fewest bookings that day, then id. Each attempt runs inside a SAVEPOINT, so one conflict doesn't abort the whole transaction; the engine simply moves on to the next person.

Rescheduling is a single UPDATE of the times. The constraint checks the new range against every other booking, so the old slot is freed and the new one taken in one step. Either the move succeeds completely or nothing changes.

The slot engine still runs first, to show customers only free times and to reject times that aren't on the grid. But correctness never depends on it. If the engine and the database ever disagreed, the database would win.

The proof

An integration test fires 100 parallel POST /bookings requests at one slot. The result is exactly one 201 Created, 99 409 Conflicts, and one row in the table. That test is the reason I trust the design.

The lesson I took from this: when a rule must never be broken, put it where it can't be bypassed. For overlapping bookings, that place is the database.

The full code, tests and decision record are on GitHub: github.com/ToukirOfficial/slotwise.