Schema-level guarantees vs. application-level locks.

The pattern most code reaches for first: check-then-write.

The first instinct is reasonable: query the table, see whether anyone else has already booked the window, and only insert if the answer was no. It looks correct because each statement is correct. The bug is the gap between them.

-- Terminal 1: agent A asks Postgres if the slot is free.
SELECT 1
FROM   "Booking"
WHERE  "agentId" = 'ag_…'
  AND  status     = 'confirmed'
  AND  tsrange("startsAt", "endsAt", '[)') &&
       tsrange($1, $2, '[)')
LIMIT  1;

-- Zero rows → "no collision, ship it".

-- Terminal 2: another request lands between the SELECT and the INSERT.
-- Its SELECT also returns zero rows, because the first INSERT hasn't
-- committed yet.

INSERT INTO "Booking" (...) VALUES (...);   -- commits ✅
INSERT INTO "Booking" (...) VALUES (...);   -- commits ✅ (race won)

This is the classic time-of-check-to-time-of-usewindow: two transactions each see a clean slate, each insert, and the database accepts both. The race is invisible at single-request test time and goes red the moment two agents share a window. The same shape reappears when the “check” is a Redis key, a row count, an API call to a third-party service, or an external lock — anything that lives outside the same transaction as the insert is a check, never a guarantee.

Pessimistic row locks: SELECT … FOR UPDATE.

The standard upgrade is to escalate to a row-level pessimistic lock: lock the matching rows (or, more likely here, lock a sentinel row for the agent so all bookings for that agent serialize through one lock), check the window, insert, commit.

-- "Look up or create the booking row" pattern, with a row lock.
SELECT id, "startsAt", "endsAt"
FROM   "Booking"
WHERE  "agentId" = 'ag_…'
  AND  tsrange("startsAt", "endsAt", '[)') &&
       tsrange($1, $2, '[)')
FOR UPDATE;          -- holds until commit

-- inside the transaction:
INSERT INTO "Booking" (...) VALUES (...);

The race closes, but the lock surface does not. A hot agent — the same agentId seeing a hundred bookings a minute — becomes one lock queue: every new request waits on FOR UPDATE, latency tails blow up, and a slow downstream call inside the transaction stretches the lock the rest of the queue is waiting on. The deadlock retry storm is the next thing teams write: detect 40P01, back off, retry, and watch the retries stack on top of each other under burst traffic. None of this is wrong; it is just the price of pushing the decision into the row lock and accepting that the row lock is now on the hot path.

Postgres advisory locks.

Advisory locks keep the booking row lock free by giving each slot a stable hash, and serializing every transaction that hashes to the same number. The pattern below is the one engineers arrive at once the row lock starts hurting.

-- pg_advisory_xact_lock keyed on a stable hash of the slot.
SELECT pg_advisory_xact_lock(hashtext('ag_…' || $1 || $2));

-- inside the transaction:
SELECT 1 FROM "Booking" WHERE …;   -- collision check
INSERT INTO "Booking" (...) VALUES (...);
-- COMMIT releases the advisory lock.

The cost moves to the lock table. Each distinct key creates a queue; per-key partitioning — a separate lock key for each (agentId, day)cell, say — pushes the queue count up to whatever the calendar’s traffic profile supports. The honest picture is an in-memory QPS map: under burst, hot cells still queue, idle cells hold entries indefinitely, and a deploy, restart, or new replica loses the map entirely. A retry-loop fallback that pairs the advisory lock with a second-tier row lock is the common extension, and a lost update on a node restart — the lock map says “free” because it forgot — is the failure mode nobody catches in staging.

SERIALIZABLE retries.

The other path is to ask Postgres to arbitrate. Under SERIALIZABLE, serializable snapshot isolation (SSI) tracks read-write dependencies and aborts one of two transactions that would have produced a non-serializable result. The application's job is to retry.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT 1 FROM "Booking"
WHERE  "agentId" = 'ag_…'
  AND  status     = 'confirmed'
  AND  tsrange("startsAt", "endsAt", '[)') &&
       tsrange($1, $2, '[)');
INSERT INTO "Booking" (...) VALUES (...);
COMMIT;
-- Postgres may raise 40001 "could not serialize access";
-- client catches, sleeps jitter, retries up to N times.

The honest shapes are: SSI bookkeeping is not free (every tracked dependency costs memory and CPU), the 40001 error surfaces on the wire as a generic could not serialize access, and every caller must catch it, jitter the backoff, and retry within a budget. Under sustained contention the retry budget is the second thing teams tune — and the budgets that fail open (give up and let the request through with a non-conflict outcome) re-introduce the very race you were trying to close.

What STRALO picked: EXCLUDE on tstzrange.

The decision was made once, in the schema, where the database itself can enforce it on every write path that touches the table. One constraint, no application retry, the same protection on direct POST /api/bookings INSERTS, on PATCH /api/proposals/[id]/accept, and on any future write that lands in the table. The shape, copied verbatim from src/lib/server-bookings/insert-booking.ts:

ALTER TABLE "Booking"
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING gist (
  "agentId" WITH =,
  tsrange("startsAt", "endsAt", '[)') WITH &&
)
WHERE (status = 'confirmed')

Reading the constraint aloud: “On the Booking table, no two rows may have the same agentId and overlapping half-open tstzrange(startsAt, endsAt, '[)') ranges — but only while they are confirmed.” Three details matter:

• 1.The GiST index mixes a B-tree equality (on agentId) with a range overlap (on tstzrange) — that combination is the reason btree_gist is created on first request: the equality opclass has to be available inside the GiST index.
• 2.The partial WHERE status = 'confirmed' clause excludes cancelled rows. Rescheduling an old window works because cancelled rows no longer block the constraint; retry-on-cancel becomes trivial.
• 3.The [) half-open range means end == next start is NOT a collision — back-to-back bookings (10:00→11:00 followed by 11:00→12:00) are legal. Same constraint, no special case.

The full answer ships in the FAQ entry; the curl shape and response are in /docs#post-bookings.

The worked example: two concurrent POST /bookings.

Two terminals, the same agentId, the same window, fired within the same millisecond. Both ask STRALO to confirm a booking on the same slot. The constraint is the gate; only one writer can possibly commit. The example deliberately uses the same body as /docs#post-bookings so any developer who already read /docs sees the same shape:

curl -X POST https://stralo.polsia.app/api/bookings \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <YOUR_API_KEY>" \
  -d '{
    "agentId":  "ag_5b8e3a1c9c2b4e1c8f7d6a5b",
    "startsAt": "2026-09-11T00:49:25.773Z",
    "endsAt":   "2026-09-11T01:49:25.773Z"
  }'

curl -X POST https://stralo.polsia.app/api/bookings \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <YOUR_API_KEY>" \
  -d '{
    "agentId":  "ag_5b8e3a1c9c2b4e1c8f7d6a5b",
    "startsAt": "2026-09-11T00:49:25.773Z",
    "endsAt":   "2026-09-11T01:49:25.773Z"
  }'

The two requests interleave inside Postgres, and the constraint decides which one commits. There is no application-layer retry, no in-memory lock table, no fallback to Redis — the schema itself answers. The wire responses, one of each:

HTTP/1.1 201 Created
Content-Type: application/json

{
  "id":       "bk_…",
  "agentId":  "ag_5b8e3a1c9c2b4e1c8f7d6a5b",
  "startsAt": "2026-09-11T00:49:25.773Z",
  "endsAt":   "2026-09-11T01:49:25.773Z",
  "status":   "confirmed"
}

---

HTTP/1.1 409 Conflict
Content-Type: application/json

{
  "error":   "slot_taken",
  "message": "Booking slot already taken for this agent."
}

The 409 shape is the same one the FAQ documents as the overlap rejection: the helper maps the SQLSTATE 23P01 exclusion_violation from isExclusionViolation() into BookingConflictError → HTTP 409 slot_taken. There is no second code path for “the application rejected” versus “the database rejected” — there is only Postgres’s verdict.

Why this survives what application locks do not.

A schema-level constraint has properties the application-level patterns cannot buy back without rewriting the application:

• ▸It is the contract. Every write path that touches "Booking" — the direct POST /api/bookings INSERT, the accept-proposal.ts INSERT, any future endpoint, any internal script, any ad-hoc psql fix-up — inherits the same protection. isExclusionViolation() returns the same answer on any direct INSERT, because the constraint is on the table, not on the application layer that called the helper.
• ▸No retry loop on the server. The constraint throws 23P01 at commit time. The caller gets a clean 409 the first time. There is no client retry buried in the helper, no exponential backoff on double-bookings, no silent fallback that lets two “conflicting” requests through. The retry budget the SERIALIZABLE pattern needs is gone — the rejection is free.
• ▸Cancelled rows fall out of the constraint. The partial WHERE status = 'confirmed' means a DELETE /api/bookings/[id] soft-cancel automatically releases the window for re-booking — no cleanup code, no tombstone table, no expiry sweeper. The DELETE endpoint preserves the audit row and the constraint handles the rest.
• ▸Crashes commit-or-rollback as one transaction.A process death mid-write leaves the database in one of two states: the row exists, or it does not. There is no “the application held the lock and then lost it” state. Advisory lock maps and in-memory lock tables and FOR UPDATE queues all need a recovery story; the Postgres EXCLUDE constraint is the recovery story.
• ▸Back-to-back bookings just work. The [) half-open boundary is a Postgres convention, not a special case in the helper: a 10:00–11:00 booking followed by an 11:00–12:00 booking is not a collision because the second tstzrange starts on or after the first ends, and && is strict overlap — touch counts, sharing an endpoint does not.