How I prevent double bookings in Postgres (advisory locks + Supabase)

Yassine Khattari — Sat, 07 Nov 2026 15:15:13 +0000

I built a booking app for gyms with React, TanStack Start and Supabase. The part that looked easy and wasn't: two people booking the same slot at the same moment.

This post explains the approach I ended up with, with a simplified version of the code.

The problem

A client opens the booking page, picks a service, a date and a time slot. Two clients can do this for the same trainer and the same slot within milliseconds of each other.

If your app does this in two steps:

SELECT to check the slot is free
INSERT the booking

then both requests can pass step 1 before either one reaches step 2. Result: a double booking.

Checking in the frontend or in your API code doesn't fix it, because the race happens between your two queries.

Why not just a unique constraint?

A unique constraint works if every booking uses exactly the same start time. It breaks down quickly:

Services have different durations (30 min, 60 min), so bookings overlap without being equal.
Group classes allow several people in the same slot, up to a capacity.

An exclusion constraint can handle simple overlaps, but group capacity ("up to 10 people") doesn't fit that model nicely.

The approach: lock, then check, inside the database

I put the check in a BEFORE INSERT trigger on the bookings table, and I take a transaction-level advisory lock first. The lock key is the staff member plus the date:

sql
-- Simplified. The real version also handles group capacity and expired holds.
CREATE OR REPLACE FUNCTION check_booking_availability()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
overlapping integer;
BEGIN
-- Serialize concurrent bookings for the same staff member on the same day.
PERFORM pg_advisory_xact_lock(
hashtextextended(NEW.staff_id::text || NEW.booking_date::text, 0)
);

SELECT count(*) INTO overlapping
FROM bookings b
WHERE b.staff_id = NEW.staff_id
AND b.booking_date = NEW.booking_date
AND b.status IN ('pending', 'confirmed')
AND b.start_time < NEW.end_time
AND b.end_time > NEW.start_time;

IF overlapping > 0 THEN
RAISE EXCEPTION 'This time slot is no longer available';
END IF;

RETURN NEW;
END;
$$;

CREATE TRIGGER bookings_check_availability
BEFORE INSERT ON bookings
FOR EACH ROW EXECUTE FUNCTION check_booking_availability();

What this gives you:

The advisory lock is held until the transaction ends. A second booking for the same staff member and day waits for the first to commit or roll back, then runs its own check and sees the first booking.
Different staff members, or different days, use different lock keys, so they don't block each other.
The rule lives in the database, so it applies no matter which client inserts (your API, an admin script, a future mobile app).

In the app, when the trigger raises the exception, the booking request fails and the client sees a message asking them to pick another slot.

Group classes and capacity

For group classes, the check changes from "any overlap blocks" to "count the people already in this class and compare with capacity". Same lock, same trigger, different condition.

Temporary holds for paid bookings

If a service requires payment, I create the booking as pending with a hold_expires_at timestamp while the client pays. The availability check ignores pending bookings whose hold has expired, so an abandoned checkout doesn't block a slot forever:

sql
AND NOT (b.status = 'pending'
AND b.hold_expires_at IS NOT NULL
AND b.hold_expires_at < now())
Things I'd warn you about
Test the race, don't assume it. Fire two inserts for the same slot in parallel (two terminals, or a small script) and confirm only one succeeds.
Advisory locks are application-defined. Nothing stops other code from inserting without going through the trigger path, so keep the check in the trigger and not in the app only.
Time zones. Store timestamps in UTC and convert per business. It's easy to get the "day" wrong near midnight.
Row Level Security still matters. The trigger prevents double bookings; RLS policies decide who can create or cancel a booking at all.
Wrapping up

The core idea is small: lock the resource, then check, then insert, all in one transaction. Everything else (capacity, holds, payments) builds on that.

I packaged this booking engine, the owner dashboard and the public booking page as a source-code starter kit for developers who build booking apps for clients. It's $99 one-time. The known limits are listed upfront: the CMI (Moroccan gateway) integration is a mock, Stripe is test mode only, and there's no automated test suite.

30-second demo: https://youtube.com/shorts/SKSHpPnNcOQ
Full walkthrough: https://youtu.be/83ZVDX_zGSg
Details and live demo: https://grow-roots-landing.vercel.app

If you've solved double bookings differently (exclusion constraints, SELECT ... FOR UPDATE, queues), I'd like to hear what worked for you.

DEV Community: Yassine Khattari

How I prevent double bookings in Postgres (advisory locks + Supabase)