DEV Community

Yassine Khattari
Yassine Khattari

Posted on Edited on

3 bugs readers found in my Postgres booking trigger (and how I fixed them)

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.

I also recorded a short video where I test it live, with two requests hitting the same slot:

Updates after reader feedback (thank you to everyone who reviewed the code):

My first snippet ran with the inserter's privileges, so Row Level Security could hide other users' bookings from the availability check. Fixed below (SECURITY DEFINER, fixed search_path, qualified table names).
The trigger only fired on INSERT, so a late UPDATE to confirmed could bypass it. Fixed below.
Sunnatulla pointed out the webhook side: what happens when that late confirmation is rejected, and later two more cases (an UPDATE that matches zero rows, and matching on the error code instead of the message). I checked my handler and both were real. See "Handle it in your webhook" below.
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 public.check_booking_availability()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
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 public.bookings b
WHERE b.staff_id = NEW.staff_id
AND b.booking_date = NEW.booking_date
AND b.id IS DISTINCT FROM NEW.id -- don't count the row itself on UPDATE
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'
USING ERRCODE = 'exclusion_violation'; -- SQLSTATE 23P01, easy to match in code
END IF;

RETURN NEW;
END;
$$;

CREATE TRIGGER bookings_check_availability
BEFORE INSERT OR UPDATE OF status, staff_id, booking_date, start_time, end_time
ON public.bookings
FOR EACH ROW
WHEN (NEW.status IN ('pending', 'confirmed'))
EXECUTE FUNCTION public.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).
Why SECURITY DEFINER matters here

A trigger function normally runs with the privileges of the user doing the insert. If bookings has Row Level Security, that user may only be allowed to see their own rows, and then count(*) would miss other people's bookings. The check would pass when it shouldn't.

Running the function as its owner fixes that, as long as the owner can see all the relevant rows. In my case the owner is postgres, which has BYPASSRLS on Supabase (you can verify with pg_proc and pg_roles). A few notes:

Set a fixed search_path on every SECURITY DEFINER function and qualify table names (public.bookings). The real project uses a fixed search_path of public; an empty one is stricter.
FORCE ROW LEVEL SECURITY can still constrain an ordinary table owner, but superusers and BYPASSRLS roles skip it, so know which role owns your function.
Keep the lock and the check together in the same function. Changing visibility alone doesn't fix the race.

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.

Don't forget UPDATE

A BEFORE INSERT trigger alone leaves a gap: nothing checks the UPDATE that confirms a booking. Example: client A's hold expires while they are still on the payment page, client B books the freed slot, then A's payment webhook sets A to confirmed. Both rows end up confirmed for the same slot. The same happens if an admin moves a booking to another time.

The fix is to fire the same function on UPDATE too, and to exclude the row itself from the overlap query (b.id IS DISTINCT FROM NEW.id). Now A's late confirm raises "This time slot is no longer available", and you can refund or offer another slot. If a booking can move to another staff member or day, the lock only covers the new key, which is fine since that is the slot being claimed.

Handle it in your webhook

Once the trigger rejects the late UPDATE, your payment webhook has to deal with it. This is where I found two more bugs in my own code, both silent.

  1. A rejected update is not an exception. supabase-js does not throw on a database error. It returns { error }, and I was not checking it. The handler returned 200 OK, the payment stayed paid, the booking stayed pending, and the client was charged for a slot they did not get.

  2. An update that matches zero rows looks like a success. Say the client (or an admin) cancels the pending booking while the Stripe page is still open, and then the payment goes through. .eq("status", "pending") matches nothing, and the library reports success. Same result: charged, no booking. (Thanks again to Sunnatulla for this one.)

The fix is to ask for the updated rows back and treat each outcome on purpose:

ts
const { data: updated, error } = await supabaseAdmin
.from("bookings")
.update({ status: "confirmed", hold_expires_at: null })
.eq("id", bookingId)
.eq("status", "pending")
.select("id"); // ask for the updated rows back

if (error) {
// 23P01 is what the trigger raises for a slot conflict
return error.code === "23P01" ? refund() : retryLater(); // 500: Stripe retries
}

if (updated.length === 0) {
// matched nothing: already confirmed (a Stripe retry) or cancelled meanwhile
const { data: row } = await supabaseAdmin
.from("bookings").select("status").eq("id", bookingId).maybeSingle();
return row?.status === "confirmed" ? ok() : refund();
}

(refund(), retryLater() and ok() are placeholders for your own responses.)

Details that matter:

The "already confirmed" branch is important. Stripe retries events. Without it, a retry of an event you already handled would look like a failure and you would refund a booking that is fine.
Refund with an idempotency key, so a retried event cannot refund twice.
Match the error code, not the message. message.includes("no longer available") breaks quietly the day someone rewords the exception. With USING ERRCODE = 'exclusion_violation' in the trigger, the client gets error.code === "23P01". A reader checked this behind PostgREST. Keep the message check only as a fallback for databases that have not run the migration yet.

I moved this decision into a small pure function and unit-tested the cases (conflict, zero rows, already confirmed, cancelled, unreadable status). I have not run the full flow against Stripe yet, so test it in Stripe test mode before you rely on it.

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. And remember the trigger must be able to see every relevant booking, which is why the function above is SECURITY DEFINER.
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.

Video: https://youtu.be/fQfZT6RWkCs
30-second demo: https://youtube.com/shorts/SKSHpPnNcOQ
Details and live demo: https://grow-roots-landing.vercel.app/?ref=devto

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

Top comments (2)

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

Thanks for the mention. One more case in that webhook, and it fails silently too: the update matches zero rows. Say A cancels the pending booking while the Stripe page is still open, or an admin cancels it, and then the payment goes through. .eq("status", "pending") matches nothing, and supabase-js reports that as success.

I ran your trigger behind PostgREST 16.5 with a booking already set to cancelled:

update(...).eq("id", 1).eq("status", "pending")
-> { error: null, data: null, status: 204 }

same + .select("id")
-> { error: null, data: [], status: 200 }
Enter fullscreen mode Exit fullscreen mode

So the handler returns 200, A is charged and has no booking. Adding .select("id") and treating an empty array as its own branch catches it. Then read the row: if it's already confirmed, it's a Stripe retry and 200 is right. Anything else goes the refund way, like a slot conflict.

Small one while you're in there: message.includes("no longer available") breaks quietly the day someone rewords the exception. RAISE EXCEPTION '...' USING ERRCODE = 'exclusion_violation' comes back as error.code === "23P01" (checked it in the same setup), so you can match on the code instead.

Collapse
 
yassine_khattari_3cb9d27d profile image
Yassine Khattari •

Both points were right, thank you. I confirmed the zero-row case in my own code: a booking cancelled while the Stripe page was open matched nothing, supabase-js reported success, and the client would have been charged with no booking.

Fixed in the latest version: the update now returns the updated rows, and an empty result is its own branch. It reads the booking: already confirmed means it's a Stripe retry (200, no refund), anything else is refunded. I also moved the conflict check to error.code 23P01 with a migration that raises it, keeping the message check only as a fallback for older databases. The decision logic is unit tested; I haven't run the full flow against Stripe yet, so the README says so.