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

A few weeks ago I wrote about preventing double bookings in Postgres with advisory locks, and recorded a short video where I test it live with two concurrent requests:

Readers reviewed the code and found real problems. This post lists three of them, what each one looked like, and the fix. If you build booking or checkout flows, you may have the same bugs.

Bug 1: the trigger ran with the wrong privileges

My first snippet was a plain trigger function. A trigger function runs with the privileges of the user doing the insert. With Row Level Security on, that user may only see their own rows, so count(*) could miss other people's bookings and the check would pass when it should not.

Fix: run the function as its owner, with a fixed search_path and qualified table names.

sql
CREATE OR REPLACE FUNCTION public.check_booking_availability()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
-- ... lock, then count overlapping rows from public.bookings ...
$$;

On Supabase the owner is postgres, which has BYPASSRLS. I verified this with a query on pg_proc and pg_roles instead of assuming it.

Bug 2: the trigger only fired on INSERT

This one is subtle. A BEFORE INSERT trigger checks new bookings, but nothing checks the UPDATE that confirms one.

Client A starts paying. Their booking is pending with a hold.
The hold expires while A is still on the payment page.
Client B books the freed slot.
A's payment webhook arrives and sets A to confirmed.

Both rows end up confirmed for the same slot, with no error. The same happens if an admin moves a booking to another time.

Fix: fire the same function on UPDATE, and exclude the row itself from the overlap query.

sql
-- in the overlap query
AND b.id IS DISTINCT FROM NEW.id

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();

Now A's late confirmation raises "This time slot is no longer available".

Bug 3: the webhook ignored the error

Fixing the trigger created a new question: what does the Stripe webhook do when that exception fires?

I opened my handler. It updated the booking with supabase-js, which does not throw on a database error. It returns { error }, and I was not checking it. So the handler returned 200 OK, the payment stayed paid, the booking stayed pending, and nothing retried. The client would have been charged for a slot they did not get, silently.

Fix: check the error. On a slot conflict, refund the payment and return 200 (it is handled). On any other failure, return 500 so Stripe retries.

ts
const { error: confirmError } = await supabaseAdmin
.from("bookings")
.update({ status: "confirmed", hold_expires_at: null })
.eq("id", bookingId)
.eq("status", "pending");

if (confirmError) {
const isSlotConflict = confirmError.message.includes("no longer available");
if (!isSlotConflict) {
return new Response("Booking confirmation failed", { status: 500 }); // Stripe retries
}

await stripe.refunds.create(
{ payment_intent: paymentIntentId },
{ idempotencyKey: slot-conflict-refund-${paymentId} }, // no double refund on retries
);
// mark the payment refunded, cancel the pending booking...
return new Response("Slot conflict: payment refunded", { status: 200 });
}

The idempotency key matters: if the refund succeeds but a later step fails and Stripe retries the event, you do not refund twice.

Honest note: this refund path is written and type-checked, but I have not yet tested it end to end against Stripe. Do that in test mode before you rely on it.

What I would take from this
Test the race, then test the edges around it. My first test only covered two inserts. The bugs lived in updates and in the code around the trigger.
Check how your client library reports errors. If it returns { error }, an unchecked call fails silently.
Decide what "handled" means for webhooks. Return 200 only when the situation is really dealt with. Return 500 when you want a retry.
Public code review is useful. Every bug above came from someone reading what I wrote.

If you want to see the whole thing running, the full booking engine (with these fixes) is available as a source-code starter kit. It is $99 one-time, and the known limits are listed on the page: Stripe is test mode only, the Moroccan CMI gateway is a mock, and there is no automated test suite yet.

Details and live demo: https://grow-roots-landing.vercel.app/?ref=devto2
First article: https://dev.to/yassine_khattari_3cb9d27d/how-i-prevent-double-bookings-in-postgres-advisory-locks-supabase-11dl

What bugs did your booking or checkout code have that you only found later?