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

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.