Hotel Reservation Architecture Deep Dive

One room_night_inventory row per (hotel_id, room_type_id, stay_date) with sellable, held and confirmed, plus CHECK (held + confirmed <= sellable). Availability is sellable - held - confirmed: an O(1) read of one row. A single conditional UPDATE is both the check and the write (Deep Dive 02-03). The primary key is also the lock key and the range index for a stay: nights [check_in, check_out) are exactly check_out - check_in adjacent rows; the checkout date is never a night.

Every allocation is appended as an immutable row with a delta, and availability is a sum. Appends do not conflict, which is the problem: two concurrent appends can both pass a sum check, so the ledger still needs one lockable row per room-night.

A conditional UPDATE that matches nothing succeeds with 0 rows affected; no exception, the transaction stays open. Code that only checks for errors will commit 11-14 after 11-15 failed. Read the affected-row count of every statement, require exactly 1, and stop at the first 0 so the 409 names the blocking night.

Nights in different databases would need a reserve-then-confirm protocol or a compensating saga. That never happens here: a stay belongs to one hotel and a hotel lives on one shard (Deep Dive 14). Only multi-hotel packages would need it.

In PostgreSQL, a transaction at REPEATABLE READ or SERIALIZABLE does not re-check: once A commits, B's UPDATE fails with could not serialize access due to concurrent update (SQLSTATE 40001) and the whole transaction must be retried. Still correct, but on a hot row every loser becomes an abort and a retry instead of a clean 0-row answer. The conditional UPDATE needs nothing stronger than READ COMMITTED, so the booking path runs at that level.

A lock taken by an UPDATE is held until the transaction ends, not just for the statement. In a 2-night hold, 11-14 stays locked while the transaction waits on 11-15. Three consequences: no network call may run inside T1 or T3; the hot-row budget (2-5 ms per allocation, roughly 200-500 allocations/s on one room-night) is the whole transaction's duration; and waits should be bounded with SET LOCAL lock_timeout, so a pile-up on a conference night fails fast with a retryable error instead of exhausting the connection pool.

SKIP LOCKED passes over holds another worker claimed or a confirmation (T3) is spending; the next tick sees their final state. Small batches, because room-night locks, possibly on hot nights, are held until COMMIT. Key-ordered releases keep the worker in the bookings' global lock order (Deep Dive 03). `held >= :k`, exactly 1 row: a mismatch is counter drift, so roll back and alert rather than clamp to zero.

T3 commits at t=598 s (HLD-1 CONSUMED, nights held → confirmed). The worker's tick at t=601 s skips the row or matches 0 rows; nothing is released.

The provider outlasted auth_timeout, so the deferral ended and the worker expires HLD-1 at t=600 s; at t=602 s request B's HLD-2 takes the freed 11-15 unit. A late capture's T3 at t=605 s matches 0 rows on HLD-1's fence and rolls back; reconciliation refunds (Deep Dive 08). It only addresses HLD-1's row, so it cannot touch HLD-2's unit.

Primary signal: expiry lag, now() - min(expires_at) over non-deferred ACTIVE holds; growth means abandoned holds hide inventory past their TTL. An explicit abandon runs the same transaction with RELEASED, returning the unit immediately instead of after 10 minutes.

Read-then-insert lets two retries on two servers both see "no row"; only the unique index sees both. In PostgreSQL a violating INSERT aborts the transaction, so catch-then-SELECT needs a savepoint; ON CONFLICT DO NOTHING avoids that. If the conflicting row is still uncommitted, the INSERT waits for its transaction, so the follow-up lookup sees a committed row (or the INSERT succeeds if that transaction rolled back).

The same key with a different hold, amount or card is a client bug, rejected rather than answered with another request's result. A new card after a decline is a new key; the per-hold partial index admits it once the earlier attempt has left PENDING_PAYMENT.

POST /holds with key 3f9c2a71-hold records its key in the same T1 as the counter updates (a unique (principal_id, endpoint, idempotency_key) record). Without it, a retried hold would increment held twice and hide a unit for 10 minutes. For -cancel, the guarded transition alone would answer a retry with "0 rows"; the key turns that into the original CANCELLED response.

Authorize, capture and refund calls carry references derived from the attempt (BKA-1-auth, BKA-1-capture), so a retried call resolves to the same charge and reconciliation can query exactly that reference (Deep Dive 08). Attempts are kept with the reservation, not as a 24-hour cache, so even a very late retry finds its outcome.

A duplicate retry, a stale webhook or a racing cancellation finds the row past its expected state; the caller reloads it and answers from its current state, never overwriting or retrying blindly. Late or reordered provider webhooks ("authorized" after "captured") are no-ops for the same reason, and provider event ids recorded with the transition catch exact duplicates.

Holds and attempts only move forward into terminal states, so state is a sufficient fence. A reservation stays CONFIRMED through modifications, so only the version distinguishes "CONFIRMED v1 I read" from "CONFIRMED v2", e.g. a cancellation priced from v1 racing a modification that committed v2.

seq is assigned at insert, but transactions commit out of order: seq 41 can commit after 42. A relay remembering "done up to 42" would skip 41 forever; polling published_at IS NULL cannot.

The relay publishes, then marks; a crash in between republishes, whereas marking first would lose the event. Every committed change is published at least once, never exactly once.

The projector applies absolute per-night values only if their seq is newer than the stored one (Deep Dive 10); deltas such as held + 1 would not be idempotent. Consumers with a database insert (consumer, event_id) into a processed-events table in the same transaction as their effect. External effects such as email pass event_id as the provider's idempotency key where supported, and otherwise accept a rare duplicate.

Per-key order comes from the broker partition (aggregate_key); after redeliveries consumers rely on seq. seq is a valid per-night version: a second writer can update a room-night only after the first commits, and each transaction inserts its outbox row after its room-night updates, so the later writer draws the larger seq.

A compensation is a new guarded, idempotent action (release through the hold fence, refund with a stable reference), not a rollback, so a resumed saga can re-run it. The original nights are released only in step 3, so any failure leaves the traveler with the stay they had.

The cancellation transaction writes refund.requested; the Payment Service refunds under a reference fixed at cancellation, as its own record (REQUESTED → SUCCEEDED, retried). A failing refund never reopens the cancellation; a redelivery cannot refund twice.

PAYMENT_CAPTURED_BOOKING_FAILED records a fact and can come from several detectors (this sweep, and a slower settlement-export comparison that also catches captures on attempts already FAILED); RECONCILING records that one resolver owns the case. The online T3 guards the attempt with state = 'PENDING_PAYMENT' and the reconciliation T3 with state = 'RECONCILING', so a slow orchestrator and the worker can never both complete it.

Every repair uses from-state guards and a stable provider reference, so a re-run cannot confirm or refund twice. If the hold expired, a product may first try a fresh conditional allocation before refunding; never skip the conditional update to honor a payment.

Every rate edit bumps the price_version of the affected DailyRate rows. The quote stores nightly amounts, taxes and that version, so audit can show why ₹14,560 was charged after rates change.

A rate edit inside the 10 minutes does not change an existing quote: the traveler pays the price shown, and the hotel accepts that bounded exposure (Complete Design, Chapter 17). After expiry the quote is refused with 410 QUOTE_EXPIRED and the client re-quotes at the current version. Because the quote claims no inventory, it costs nothing to abandon, and the unit can still sell out while the quote is valid; that surfaces as a 409 at hold time.

T1 copies the amount and version into the hold. From then on the hold's amount is what the booking charges, and it is part of the attempt fingerprint (Deep Dive 05), so a retry under the same key cannot change it. The hold's 600-second TTL bounds how long the price stays locked after the quote.

A stop-sell or minimum stay added after quoting still blocks the hold, because restrictions are re-read inside T1 (Deep Dive 12). A quote promises a price, never sellability.

Each event carries, for every night it touched, the absolute availability after the change, plus its outbox seq. The projector writes a night only if the incoming seq is greater than the stored one. Duplicates and reordered deliveries become no-ops, and a lost event heals at the next change to that night, because the values are absolute. Deltas would require exactly-once, in-order delivery, which the relay does not provide (Deep Dive 07).

A conference night can change 50-200 times within seconds; the projector applies only the highest seq per night in each batch, so writes scale with distinct nights changed.

Lag per partition is now() minus the created_at of the oldest event not yet applied, with an alert as it approaches 60 s. Responses carry availability_as_of and cache_freshness_seconds (Complete Design, Chapter 07). Any result cache in front of search spends from the same budget: a 30 s result TTL on top of 40 s of projection lag means 70 s of staleness.

A periodic per-hotel comparison against replica counts emits corrections. A full rebuild (bootstrap, schema change) loads a snapshot, then replays the outbox from a position safely before it; the seq check makes the overlap harmless. It is never the freshness mechanism.

HTL-204 is in Asia/Kolkata, which has no daylight saving, so take a London hotel instead: a 2-night stay from 2026-10-24 to 2026-10-26. Store check-in as local midnight in UTC (2026-10-23T23:00Z, because BST is UTC+1) and add 48 hours: 2026-10-25T23:00Z. Clocks went back on 10-25, so that instant is 23:00 on 10-25 in London. The computed checkout lands a day early, and the stay silently loses a night.

Nightly rates, taxes, minimum-stay and stop-sell checks are all evaluated per local date (Deep Dive 12). A minimum-stay rule is a statement about specific calendar nights; a model that miscounts which nights those are enforces it against the wrong ones.

The restriction read is a plain READ COMMITTED read. A stop-sell committing a millisecond later does not block an in-flight hold; both commit, equivalent to the hold happening first, which matches the rule that a stop-sell governs new sales and never cancels existing holds. A stricter rule would need both to write a common row.

Search shows restriction flags from the projection and quoting checks them so a traveler learns early, but only the read inside T1 decides. Modifications re-validate the new stay in their own T1 (Deep Dive 07).

Reading the cursor and comparing in the application lets two concurrent deliveries both see 884 and both apply. The conditional UPDATE locks the cursor row: the second batch waits, re-evaluates (Deep Dive 03) and matches 0 rows; a replay of 885 is a no-op.

"Apply only if newer" is safe when batches carry absolute per-night values: a skipped older batch is superseded. If a PMS sends deltas, a skipped batch is lost data; the connector must then require cursor = last + 1 and request a resend on a gap.

For bookings made on this platform, the platform ledger is authoritative. A night reported below held + confirmed is not applied; the rest of the batch is and the cursor advances, so one conflict does not block the stream, and operations resolves it. Sales on other channels arrive only through later batches, so a cross-channel oversell window bounded by sync latency remains (Trade-offs & Decisions, Decision 12).

The shard router resolves hotel_id → shard from a small cached directory rather than hash(hotel_id) mod N, so one hot hotel can move to its own shard without rehashing everyone: copy its rows, catch up from the change stream, freeze its writes briefly, flip the entry.

Hold, attempt and reservation ids map to the hotel, so a retried POST /bookings carrying HLD-1 reaches the shard holding the original attempt, and per-shard unique constraints on (principal_id, endpoint, idempotency_key) suffice. The gap: a key reused for another hotel's hold is treated as a new request, not a fingerprint conflict; it cannot double-charge because it is a different hold.

Inventory, holds, attempts, reservations, outbox and sync cursor share a hotel's shard, so T1, T2, T3, cancellation, modification and PMS batches are single-shard. Cross-hotel reads (a traveler's bookings, reporting) come from outbox-fed projections, not scatter-gather.

A hot room-night is one row on one shard. 50-200 attempts on (HTL-204, RT-DLX, 2026-11-15) serialize no matter how many shards exist; only short transactions, lock_timeout, fail-fast 409s and admission control in front of the database help (Deep Dive 03).