Every booking app has the same race. Two people look at the same free slot, both press Book, and both requests check availability before either one writes. A check-then-insert in application code can’t win that race, because the check is stale by the time the insert lands.
In CoWorkHQ I moved the rule into the database, which is the one place that actually sees every write.
The constraint
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE "bookings"
ADD COLUMN "during" tsrange
GENERATED ALWAYS AS (tsrange("startsAt", "endsAt", '[)')) STORED;
ALTER TABLE "bookings"
ADD CONSTRAINT "no_overlapping_bookings"
EXCLUDE USING gist ("resourceId" WITH =, "during" WITH &&)
WHERE ("status" <> 'cancelled');“during” is a generated column holding each booking’s time range, half-open so a booking ending at 10:00 doesn’t clash with one starting at 10:00. The EXCLUDE constraint says no two rows may have the same space and overlapping ranges. btree_gist is what lets a plain equality check on the space sit in the same GiST index as the range overlap.
The WHERE clause matters. Cancelled bookings don’t block anything, but a booking held while someone pays does, so a slot can’t be sold twice during checkout. Expired holds are released just before each insert.
The gotcha: tsrange, not tstzrange
My first version used tstzrange and Postgres refused with “generation expression is not immutable”. Prisma stores DateTime as timestamp without a time zone, and casting that to timestamptz depends on the session’s TimeZone setting, so it isn’t immutable. Prisma writes everything in UTC, so comparing as tsrange is correct, and the migration says so in case the columns ever change.
Turning the error into a product message
The UI shows which slots are free, but that’s a hint, not the guarantee. When an insert hits the constraint, the API recognises the exclusion violation (SQLSTATE 23P01) and returns “that slot was just taken” instead of a 500. The same constraint guards rescheduling for free: a row never conflicts with itself, so moving a booking only fails on a genuine clash.
Prisma’s schema language can’t express an EXCLUDE constraint, so it lives in a hand-written migration. Of everything in the project, it’s the migration I’d least want to lose.