02. Hotel / Stay Booking — Low-Level Design¶
~20 min read · Part 3 of 4 (Overview → HLD → LLD → Q&A)
The HLD named the two lanes. This file opens the components that carry the design's weight — the availability calendar, the search filter, the reservation engine, and the pricing quote — and pins down the schemas, the exact algorithm that resolves Guest A against Guest B, and the concurrency corners where a booking system actually breaks.
Data models¶
The system of record is the inventory calendar, sharded by listing_id. The central choice is to model availability as one row per room-night and let a unique constraint be the arbiter of ownership.
-- The arbitration table. One row exists only when a night is spoken for.
CREATE TABLE room_night (
listing_id BIGINT NOT NULL,
night DATE NOT NULL, -- the calendar date of the stay
booking_id BIGINT NOT NULL, -- who owns this night
state SMALLINT NOT NULL, -- 1=HELD, 2=CONFIRMED
hold_expires TIMESTAMP NULL, -- set while HELD, null once CONFIRMED
PRIMARY KEY (listing_id, night) -- THE guard: one owner per night
) PARTITION BY HASH (listing_id);
The primary key on (listing_id, night) is the whole game: a night can have at most one row, so an attempt to book an already-owned night fails at insert. "Available" is the absence of a row (or a row whose HELD expiry has lapsed), which keeps the table small — it holds only spoken-for nights, not all 3.65 billion cells. Partitioning by listing_id co-locates every night of a listing, so the collision between A and B is resolved inside one partition with a local transaction, never a distributed one.
CREATE TABLE booking (
booking_id BIGINT PRIMARY KEY,
listing_id BIGINT NOT NULL,
guest_id BIGINT NOT NULL,
checkin DATE NOT NULL,
checkout DATE NOT NULL, -- checkout night is NOT occupied
state SMALLINT NOT NULL, -- HELD / CONFIRMED / CANCELLED / EXPIRED
price_total INT NOT NULL, -- frozen from the quote, in minor units
quote_id VARCHAR(32) NOT NULL,
idem_key VARCHAR(64) NOT NULL,
hold_expires TIMESTAMP NULL,
created_at TIMESTAMP NOT NULL,
UNIQUE (idem_key) -- retried confirm/hold collapses to one booking
);
CREATE INDEX idx_booking_guest ON booking (guest_id); -- "my trips"
CREATE INDEX idx_booking_expiry ON booking (state, hold_expires); -- reaper scan
checkout is exclusive: a Dec 24–27 stay occupies the nights of the 24th, 25th, and 26th, and the guest leaves on the morning of the 27th — so the 27th is free for the next guest to check in. Getting this boundary right is what lets back-to-back bookings share a changeover day without a phantom conflict. idem_key is unique so a retried request never creates a second booking, and the (state, hold_expires) index is exactly what the reaper scans.
The listing metadata and the derived search document are separate:
listing (row): { listing_id, host_id, lat, lng, capacity, property_type,
amenities[], base_price, min_nights, ... }
search doc (ES): { listing_id, geo:{lat,lng}, capacity, property_type,
amenities[], price_band, rating,
avail_months: ["2026-12","2027-01"] } # COARSE, async-updated
avail_months is the deliberately-coarse availability signal: a list of months in which the listing has any open nights. It is cheap to keep roughly fresh and lets search prune obviously-full listings without the index ever needing to know exact per-night state.
Component internals¶
Component 1 — Availability calendar¶
Responsibility: answer "is this date range free?" fast, and represent 3.65 billion potential room-nights without storing them all.
Availability is the complement of the room_night table. To check a range, look for any owning row that overlaps it and is not an expired hold:
def is_range_free(listing_id, checkin, checkout) -> bool:
rows = db.query("""
SELECT night FROM room_night
WHERE listing_id = %s
AND night >= %s AND night < %s -- checkout exclusive
AND (state = CONFIRMED
OR (state = HELD AND hold_expires > now()))
""", listing_id, checkin, checkout)
return len(rows) == 0 # free iff no live owner exists on any night
Two design consequences follow. First, an expired hold is treated as free without being deleted — the hold_expires > now() predicate makes a lapsed hold invisible to availability instantly, so a guest never waits for the reaper to physically remove it before the night is bookable again. The reaper's deletion is just garbage collection, not correctness. Second, host-blocked dates (owner marks the room unavailable) are the same shape as a booking — a room_night row in a BLOCKED state — so blocking and booking share one uniqueness guard rather than two competing tables.
The coarse avail_months in the search index is refreshed asynchronously off booking events: when a listing's last open night in a month is taken, the indexer drops that month from avail_months; when a hold expires, it adds it back. This is allowed to lag a few seconds — the search-side approximation the whole design is built to tolerate.
Component 2 — Search filter¶
Responsibility: turn location + dates + filters into a ranked candidate list from Elasticsearch, over-returning rather than under-returning, in ~100 ms.
def search(lat, lng, radius, checkin, checkout, guests, filters) -> list[Listing]:
months = months_spanning(checkin, checkout) # e.g. ["2026-12"]
query = {
"bool": {
"filter": [
{"geo_distance": {"distance": radius, "location": {lat, lng}}},
{"range": {"capacity": {"gte": guests}}},
{"terms": {"avail_months": months}}, # COARSE prune, not exact
*facet_filters(filters), # price_band, amenities, type
]
}
}
hits = es.search(query, sort=rank_by(relevance, distance, price), size=200)
return [h for h in hits] # marked avail:"likely"; exact check deferred
The avail_months filter is a coarse prune, not a guarantee — it removes listings with no December openings but happily returns LST-42 even if only the 28th–30th are free while the guest wanted the 24th–27th. That is intentional: exact availability is confirmed at the detail/reserve step, so search stays a single fast index query and never fans out to the calendar. Over-returning is the correct failure direction — a plausible listing that turns out to be booked costs one extra detail-page check, while under-returning hides real inventory from the guest forever.
Component 3 — Reservation engine (two-phase, constraint-guarded)¶
Responsibility: place a hold that exactly one of two colliding bookers can win, then confirm it after payment without holding a lock across the payment.
def place_hold(listing_id, checkin, checkout, guest_id, quote_id, idem_key):
if existing := booking_by_idem(idem_key): # retry-safe
return existing
nights = date_range(checkin, checkout) # [24, 25, 26]
total = freeze_quote(quote_id) # price locked onto the hold
bid = new_booking_id()
try:
with db.transaction(): # single shard, single partition
db.insert("booking", id=bid, state=HELD,
price_total=total, idem_key=idem_key,
hold_expires=now()+HOLD_TTL)
for n in nights:
db.insert("room_night", # unique(listing_id, night) guards
listing_id=listing_id, night=n,
booking_id=bid, state=HELD,
hold_expires=now()+HOLD_TTL)
return Hold(bid, total) # committed → guest owns nights
except UniqueViolation as e:
return Conflict(nights_taken=e.conflicting) # 409, no partial hold
The confirm step deliberately runs outside any inventory lock:
def confirm(booking_id, payment_token, idem_key):
b = load_booking(booking_id)
if b.state == CONFIRMED: return b # idempotent
if b.state != HELD or b.hold_expires < now():
return Expired() # reaper may have released it
charge = payment.charge(payment_token, b.price_total, idem_key) # SLOW, external
if not charge.ok: return PaymentFailed()
with db.transaction():
db.update("room_night", set_state=CONFIRMED, clear_expiry=True,
where=(listing_id=b.listing_id, booking_id=booking_id))
db.update("booking", id=booking_id, set_state=CONFIRMED)
return b
The payment call sits between two short transactions, never inside one, so no other booker on the same listing is blocked while a card processor takes its time.
Core algorithm — resolving Guest A vs Guest B, stepped through¶
Here is the threaded scenario at instruction granularity. LST-42 has no held or confirmed nights in late December. At 20:00:00.000, Guest A submits Dec 24–27 (nights [24, 25, 26]); at 20:00:00.180, Guest B submits Dec 25–28 (nights [25, 26, 27]). Both requests land on the booking service and open transactions against the same shard.
- A's transaction begins, inserts
booking BKG-88 (HELD), then inserts threeroom_nightrows for(LST-42, 24),(LST-42, 25),(LST-42, 26). No rows exist yet, so all three inserts succeed and A's transaction commits at 20:00:00.140. A now owns the nights of the 24th, 25th, and 26th. - B's transaction begins at 20:00:00.180, inserts
booking BKG-89 (HELD), then attemptsroom_nightrows for(LST-42, 25),(LST-42, 26),(LST-42, 27). The insert for(LST-42, 25)violates the primary-key constraint — A already owns it. - B's whole transaction rolls back:
BKG-89and its would-be night rows vanish atomically. There is no state in which B holds the 27th but not the 25th; the reservation is all-or-nothing. - The booking service catches the
UniqueViolationand returns409 dates_unavailablewithconflicting_nights: ["2026-12-25","2026-12-26"]to B — within a few milliseconds, because the constraint check is a single index lookup, not a wait on a lock. - A proceeds to confirm. A's client posts
confirm; the booking service charges the payment gateway (say 1.2 s), and on success flips A's three night-rows and booking toCONFIRMED. Had A abandoned checkout instead, the reaper would clear A'sHELDnights athold_expiresand the nights would return to inventory — at which point B, if still trying, could win them.
The ordering of who committed first is decided by the database, not the application, so there is no window in which both bookers observe the nights as free and both proceed. That is the difference between this design and a read-then-write check: the check and the claim are the same atomic operation.
Sequence diagram — the collision¶
Guest A Guest B Booking svc Calendar DB (shard for LST-42)
│ POST hold │ │ │
├───────────────┼─────────────▶│ BEGIN tx(A) │
│ │ ├─ insert nights 24,25,26 ─────────▶│ (all new → OK)
│ │ POST hold │ │
│ ├─────────────▶│ BEGIN tx(B) │
│ │ ├─ insert nights 25,26,27 ─────────▶│ (waits: 25 locked by A)
│ │ │ COMMIT tx(A) ────────────────────▶│ 25,26,27 A owns 24-26
│◀─ 201 HELD ───┼──────────────┤ │
│ │ │ tx(B) insert 25 → UNIQUE VIOLATION│
│ │ │ ROLLBACK tx(B) ──────────────────▶│
│ │◀─ 409 taken ─┤ conflicting: [25,26] │
│ POST confirm │ │ │
├───────────────┼─────────────▶│ charge gateway (1.2s, no lock held)
│◀─ 200 CONFIRM ┼──────────────┤ flip 24,25,26 → CONFIRMED ───────▶│
Exactly one hold commits; the loser is rejected in milliseconds; and the slow payment for the winner happens with no lock held against the shard.
Concurrency and edge cases¶
- The core race (double-book): resolved by construction. The unique constraint on
(listing_id, night)means two overlapping inserts cannot both succeed; the second aborts. There is no application-level check-then-act window to lose. - Expired hold vs late confirm: if A's hold lapses and the reaper releases the nights, and A's confirm arrives late,
confirmseesstate != HELD or hold_expires < now()and returnsExpired()before charging — so we never take money for nights we cannot deliver. If B has since grabbed the nights, A simply lost the race for good. - Idempotent confirm: the payment gateway is charged with
idem_key, andconfirmshort-circuits if the booking is alreadyCONFIRMED, so a client retry after a network timeout charges the card exactly once and produces one confirmed booking. - Idempotent hold:
place_holdlooks upidem_keyfirst, so a retried hold returns the existing hold rather than minting a second booking that ties up the same nights against the same guest. - Reaper races the guest: the reaper releases a hold at
hold_expires; a confirm arriving in the same instant is safe because both take the row transactionally — either confirm wins (flips to CONFIRMED before release) or release wins (confirm then sees no HELD row and returns Expired). The row transaction serializes them. - Cancellation: cancelling a
CONFIRMEDbooking deletes itsroom_nightrows in one transaction and emits an event so the indexer re-adds the freed months toavail_months. Refund policy is a payment concern, off this path. - Changeover day: because
checkoutis exclusive, a guest leaving on the 27th and another arriving on the 27th both touch the date 27 but occupy different night sets — the departing guest never held the night of the 27th — so back-to-back stays never falsely conflict. - Search/calendar skew: the index can lag, so search may show
LST-42aslikelyavailable after A booked it. That surfaces as a409at reserve time, not a corrupted booking — the tolerated staleness the design is built around, resolved at the one place correctness is enforced.