How I Solved Double-Booking Race Conditions in a Multi-Tenant SaaS — No Redis, No Distributed Locks

Two clients open the same salon's booking page. Both pick 2:00 PM on Friday. Both hit "Confirm" within 200ms of each other.

Without a fix at the database level, both requests pass validation, both inserts succeed, and now the stylist has two people showing up for the same chair.

This is a classic time-of-check-to-time-of-use (TOCTOU) race condition, and it's one of the first hard problems I hit building Pronto, an open-source booking, CRM and POS platform for service businesses. Here's the exact bug, why the obvious fixes don't fully close it, and the Postgres trigger that does.

Why the obvious fix doesn't work

The natural first instinct is application-level:

// ❌ Looks safe. Isn't.
const existing = await supabase
  .from('appointments')
  .select('id')
  .eq('employee_id', employeeId)
  .lt('starts_at', endsAt)
  .gt('ends_at', startsAt)

if (existing.data?.length) {
  return Response.json({ error: 'Slot taken' }, { status: 409 })
}

await supabase.from('appointments').insert({ employeeId, startsAt, endsAt })

This works fine in manual testing. It breaks under real concurrency, because the SELECT and the INSERT are two separate round trips. Two requests can both run the SELECT, both see zero conflicts, and both proceed to INSERT — the classic race window between "check" and "use."

A unique constraint on (employee_id, starts_at) doesn't solve it either, for two unrelated reasons:

1. It only catches exact-same-start-time collisions. A 2:00–2:30 booking and a 2:15–2:45 booking overlap but don't share a starts_at — the constraint lets both through.
2. employee_id can be NULL (a booking not yet assigned to a specific staff member). In SQL, NULL = NULL evaluates to NULL, not true — so a plain uniqueness constraint silently stops enforcing anything the moment staff assignment is optional. ## The fix: push the check into the same transaction as the write

The only way to close the race is to make the conflict check and the insert atomic — meaning they happen inside the same database transaction, with the same row-level locking Postgres already uses for writes. A trigger does exactly that.

CREATE OR REPLACE FUNCTION prevent_double_booking()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM appointments
    WHERE business_id = NEW.business_id
      AND employee_id IS NOT DISTINCT FROM NEW.employee_id
      AND id IS DISTINCT FROM NEW.id
      AND status NOT IN ('cancelled', 'no_show')
      AND NEW.starts_at < ends_at
      AND NEW.ends_at > starts_at
  ) THEN
    RAISE EXCEPTION 'SLOT_CONFLICT: overlapping appointment for this employee'
      USING ERRCODE = 'P0001';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_double_booking
  BEFORE INSERT OR UPDATE ON appointments
  FOR EACH ROW EXECUTE FUNCTION prevent_double_booking();

Two details matter more than they look like they should:

IS NOT DISTINCT FROM instead of =. This is the NULL-safe equality operator in Postgres. NULL = NULL is NULL (falsy), but NULL IS NOT DISTINCT FROM NULL is true. Without this, two unassigned bookings for the same business at the same time sail straight past the check — the exact case a plain unique constraint also misses.

The overlap condition, not an exact-match condition. NEW.starts_at < ends_at AND NEW.ends_at > starts_at catches any interval overlap, not just identical start times. This is the standard interval-overlap test, and it's the part a naive starts_at = starts_at comparison always gets wrong.

Because this runs inside the same BEFORE INSERT trigger as the write itself, Postgres's row-level locking makes the check-and-write atomic. There's no window between "checked" and "inserted" for a second request to sneak through — the second transaction simply sees the first one's row as soon as it commits, or blocks briefly if they land at the exact same instant.

Surfacing the conflict to the client

The trigger raises a Postgres exception. The API layer catches it and maps it to a proper HTTP status:

try {
  await supabase.from('appointments').insert({ employeeId, startsAt, endsAt })
} catch (err) {
  if (err.message?.includes('SLOT_CONFLICT')) {
    return Response.json(
      { error: 'This slot was just booked. Please pick another time.' },
      { status: 409 }
    )
  }
  throw err
}

409 Conflict is the correct status here — it's not a validation error (400) and it's not a server failure (500), it's "your request was valid when you made it, but the resource state changed underneath you." The frontend listens specifically for 409, refetches the slot grid, and shows the client a clear message instead of a generic error toast.

What this doesn't solve

Worth being honest about the edges:

• This protects against overlapping bookings for the same employee within the same business (business_id is in the check, so multi-tenant isolation holds — one tenant's schedule can never block another's). It does not, by itself, stop a business from double-booking a shared resource that isn't modeled as an "employee" (a single treatment room used by two staff, for example) — that needs the same pattern applied to whatever column represents the actual constrained resource.
• Cancelled and no-show appointments are explicitly excluded from the conflict check (status NOT IN ('cancelled', 'no_show')). Skipping this filter is a subtle bug of its own: without it, cancelling a booking and rebooking the same slot immediately after would incorrectly conflict with itself.
• IS DISTINCT FROM NEW.id matters for the UPDATE case — without it, updating an existing appointment's notes would trip the trigger against itself, since it would find its own row as a "conflict." ## Why a database trigger and not Redis or app-level locking

For a single-instance deployment (which covers most self-hosted installs of Pronto, and a good chunk of small SaaS tenancy patterns), pulling in Redis for a distributed lock is real infrastructure for a problem Postgres already solves natively. Row-level locking inside a trigger is:

• Zero extra infrastructure — no Redis instance to provision, monitor, or pay for
• Correct by construction — the guarantee comes from the database's own transaction isolation, not from an assumption that every code path remembers to acquire a lock
• Impossible to bypass by accident — even a raw SQL insert from a script, a migration, or an admin tool goes through the same trigger; an application-level check only protects code paths that remember to call it If you're running multiple app instances behind a load balancer, this still holds — the guarantee lives in Postgres, not in any one instance's memory, so it doesn't matter how many API servers are hitting the database concurrently.

The pattern generalizes

Anywhere you have "no two rows should overlap on some resource + time interval," this same shape applies: room bookings, equipment reservations, shift scheduling, rental systems. The three pieces are always the same — a BEFORE INSERT OR UPDATE trigger, a NULL-safe comparison on whatever "resource" column can be optional, and an interval-overlap condition instead of an exact-match condition.

---

Pronto is open-source under MIT. If you're building something with the same overlap-scheduling problem, the full implementation is in the migrations folder.

GitHub: github.com/SGrappelli/pronto
Live demo: demo.trypronto.app

Update: this section was wrong, caught independently in two separate comment threads (here, and on the IndieHackers repost — worth reading both). A BEFORE INSERT trigger running a plain SELECT-based check is not atomic under Postgres's default READ COMMITTED isolation — no existing conflicting row for a concurrent transaction to lock against, so two simultaneous inserts could each pass and both land. Reproduced it directly (five concurrent attempts, five races) before fixing it.
Fixed with pg_advisory_xact_lock rather than an EXCLUDE constraint, since some services here allow more than one concurrent booking (group classes), which a plain exclusion constraint can't express. Verified with the same concurrency test after the fix: zero races in five more attempts. Live now.

Comments

[Mads Hansen]

Important concurrency caveat: a BEFORE INSERT trigger that does IF EXISTS is still vulnerable under PostgreSQL’s default READ COMMITTED isolation. Two transactions can both run the predicate before either row is committed, see no conflict, and insert. Row-level locking does not serialize this because there is no existing conflicting row to lock, and predicate reads do not acquire range locks at that isolation level.

PostgreSQL’s native tool for this invariant is usually an exclusion constraint, e.g. with btree_gist on (business_id WITH =, resource_id WITH =, tstzrange(starts_at, ends_at, '[)') WITH &&) plus the active-status predicate. That makes the database arbitrate concurrent overlapping inserts correctly. I’d also model a canonical, non-null resource_id for whatever is actually exclusive (employee, room, chair) instead of treating NULL employee_id as a resource. If a trigger must stay, it needs explicit serialization such as locking a stable parent/resource row, or SERIALIZABLE with retry handling. A two-session concurrency test with a barrier before commit is essential here; sequential tests will make the trigger look correct.

[Konstantin]

You're completely right, and this is a real gap, not a nitpick. A plain SELECT ... IF EXISTS inside a BEFORE INSERT trigger has nothing to lock onto when two transactions run the check concurrently under READ COMMITTED — there's no existing conflicting row for either to block on yet, so both pass and both insert. The "same transaction as the write" framing in the article was misleading; atomicity of the trigger doesn't help if there's nothing to serialize against.

The correct fix is an EXCLUDE constraint with btree_gist on (business_id WITH =, resource_id WITH =, tstzrange(starts_at, ends_at, '[)') WITH &&), which lets the index itself arbitrate overlapping inserts instead of an app-level check-then-act pattern. Also fair point on modeling a canonical non-null resource_id rather than treating a nullable employee_id as "the resource" — that's a real design gap, not just an implementation detail.

I'm auditing my actual production trigger against this right now.
Appreciate the correction — updating the article once I've verified
and fixed it, with credit to this comment.

[Cenk KURTOĞLU]

The interval-overlap test and the IS NOT DISTINCT FROM detail are both right, and "put it in the database, not in the code path that remembers to call it" is the correct instinct. But the paragraph that closes the argument does not hold, and it is worth knowing because the trigger leaves open exactly the race the post sets out to fix.

Because this runs inside the same BEFORE INSERT trigger as the write itself, Postgres's row-level locking makes the check-and-write atomic.

Row-level locking protects rows that already exist. Your trigger runs a plain SELECT ... EXISTS, which takes no locks at all, and the rows in question are two brand-new inserts — which never conflict with each other. Under READ COMMITTED, which is the default, a statement sees only what was committed before that statement began, so:

• T1 begins, inserts, the trigger's EXISTS finds nothing, the row goes in uncommitted
• T2 begins, inserts, the trigger's EXISTS cannot see T1's uncommitted row, finds nothing, the row goes in
• Both commit. Two bookings, same chair.

There is nothing for T2 to block on, so "blocks briefly if they land at the exact same instant" does not happen. The check moved from the application into the database, but the TOCTOU window moved with it — it is now between the trigger's SELECT and the insert's commit, which is narrower in wall-clock terms and just as real. Two curl loops firing concurrently will reproduce it; a browser test almost never will, which is why this shape survives so long.

The version that is atomic by construction is an exclusion constraint, because the guarantee lives in the index rather than in a query:

create extension if not exists btree_gist;

alter table appointments
  add constraint appointments_no_overlap
  exclude using gist (
    business_id with =,
    coalesce(employee_id, '00000000-0000-0000-0000-000000000000'::uuid) with =,
    tstzrange(starts_at, ends_at, '[)') with &&
  )
  where (status not in ('cancelled', 'no_show'));

Three notes on that:

• btree_gist is what lets a scalar = participate in a GiST index alongside the range &&.
• The coalesce is there to keep your NULL-safety. Exclusion constraints ignore NULLs the same way unique indexes do, so a bare employee_id with = would silently drop the unassigned-booking case you specifically fixed with IS NOT DISTINCT FROM.
• '[)' makes the bounds half-open, so a 2:00–2:30 and a 2:30–3:00 booking are adjacent rather than overlapping. That is almost certainly what you want and is easy to get wrong with the default '[]'.

Violations raise 23P01 exclusion_violation rather than your P0001, so the client mapping in the next section wants that code added — and the constraint replaces the trigger entirely rather than sitting beside it.

If you would rather keep the trigger for the custom error message, it becomes correct with one line before the EXISTS, which serialises concurrent inserts for the same employee for the duration of the transaction:

perform pg_advisory_xact_lock(
  hashtextextended(NEW.business_id::text || ':' || coalesce(NEW.employee_id::text, ''), 0)
);

That is the "row-level locking" the post is reaching for — it just has to be asked for explicitly, because there is no existing row whose lock would provide it.

[Konstantin]

Update on this: fixed it for real this time, verified with an actual concurrency test — five concurrent insert pairs raced through 100% of the time before the fix, zero after. Went with your pg_advisory_xact_lock suggestion rather than the EXCLUDE constraint, since bookings here aren't strictly 1-per-slot — some services allow multiple concurrent bookings (group classes), which a plain exclusion constraint can't express without extra modeling.
Turned out there was a second, unrelated gap too: bookings with no assigned staff member ("anyone available") had zero server-side capacity check at all — not a narrow race, just nothing enforcing it. Fixed that by having the trigger atomically find and assign a free staff member at insert time instead of just validating a count.
Appreciate the push to actually verify instead of taking the fix on faith — I'd made exactly that mistake once already earlier in this same thread.