Hotel Booking System like Booking.com

The problem and scope

When multiple travelers simultaneously attempt to book the last available room at a hotel for the same calendar dates, the reservation system faces high write contention under strict correctness constraints. It must guarantee that only one traveler secures the room, never double-book, never charge a card without held inventory, and automatically release held rooms if checkout is abandoned. Under network drops and client retries, requests must resolve idempotently without duplicate charges.

Core boundaries and assumptions

Room Types over Physical Doors: Unlike vacation rentals where specific physical units are rented, commercial hotels manage interchangeable room types (e.g., Standard King, Deluxe Suite). Physical room numbers are assigned by the front desk upon arrival, allowing the system to model inventory as daily counters rather than complex multi-room calendar intervals.
Controlled Overbooking: To offset predictable guest cancellations and no-shows, the inventory engine supports a bounded 10% overbooking buffer beyond physical capacity.
Exclusions: Loyalty reward engines, user reviews, machine learning recommendation models, and customer support chat operate as external auxiliary services outside this design.

Functional requirements

Core guest and administrator operations

Search hotels: A traveler searches by destination, check-in date, check-out date, and guest count, applying optional filters for price ranges, star ratings, and amenities. The system returns available properties with current prices.
View hotel and room details: A traveler views property descriptions, photo galleries, room type details, and per-night rate breakdowns for selected dates.
Hold and reserve a room: A traveler selects a room type for specific dates. The system acquires a temporary 10-minute inventory hold, presents an order summary, captures payment, and issues a confirmed reservation identifier.
View reservation history: A logged-in traveler queries active and historical bookings, retrieving reservation status, room parameters, payment summaries, and hotel contact details.
Cancel a reservation: A traveler cancels an active reservation prior to check-in. The system updates booking status, returns held inventory to the pool, and initiates an asynchronous refund.
Manage hotel inventory (admin): Authorized hotel managers onboard properties, add or modify room types, update physical room allocations, and review daily booking sheets.
Accommodate controlled overbooking: The inventory engine accepts confirmed reservations up to 110% of physical capacity per room type per calendar night.
Guarantee booking idempotency: Re-transmitting a booking request with an identical client-generated identifier returns the existing reservation without duplicate inventory decrements or double charges.

Non-functional requirements and assumptions

Performance targets and availability boundaries

The following operational metrics establish target capacity baselines across a mid-to-large hotel booking platform.

Metric / Dimension	Target Benchmark	Engineering Rationale
Platform Scale	5,000 hotels, 1,000,000 total rooms (~200 rooms per hotel)	Represents a mid-to-large online travel agency (OTA) or multinational hotel chain.
Availability	99.9% monthly uptime for booking; 99.99% for search	Booking downtime directly loses revenue; search outages eliminate discovery.
Data Consistency	Strict consistency on inventory booking via row locks; eventual consistency on search	Inventory counters must never oversell; search results tolerate brief synchronization lag.
Read-to-Write Ratio	100:1 to 1,000:1 read-dominant profile	Users browse, filter, and compare dozens of properties before selecting a room.
Booking Latency	p95 < 500 ms for hold acquisition; payment completion within gateway SLA	Hold operations must be fast to minimize checkout abandonment.
Search Latency	p95 < 200 ms for filtered queries	Search responsiveness directly dictates user conversion rates.
Durability	Zero data loss for confirmed reservations	Semi-synchronous replication to at least one standby replica (e.g., Multi-AZ standby) guarantees zero committed data loss upon primary failover.
Payment Safety	At-least-once delivery with idempotent payment execution	Idempotency keys scoped per request ensure network retries never trigger duplicate charges.

The mental model and architecture

The counter abstraction over physical calendars

Guests don’t book a specific physical room (like Room 304)—they book a room category (like “Deluxe King”). Specific physical rooms are assigned by the front desk upon arrival.

Because rooms within a category are interchangeable, the system doesn’t need to track separate calendars for individual rooms. Inventory simplifies to a daily integer counter per (hotel_id, room_type_id, date):

Available Rooms = ⌊Physical Capacity × 1.10⌋ - (Active Holds + Confirmed Bookings)

Reserving an N-night stay is simply N atomic row increments on daily counters, completely avoiding complex date-range overlap queries.

Single vs. Dual Counters: While a unified total_reserved column minimizes lock contention, production systems often split this into held_rooms (transient checkout holds) and booked_rooms (settled reservations) to monitor cart abandonment without table scans.

The booking funnel and read-write divergence

Traffic in a hotel platform funnels sharply downward at each stage of the guest journey. For every 1,000 users searching across a metropolitan area, approximately 100 view a specific hotel details page, roughly 10 initiate a room hold at checkout, and exactly 1 completes payment.

Because read operations outnumber writes by orders of magnitude, coupling search directly to the transactional booking database guarantees performance degradation. The architecture enforces a strict boundary: the search path runs against a horizontally scalable, denormalized search engine, while the booking path operates against an ACID-compliant relational engine.

High-level architecture

The architecture strictly decouples the high-throughput, eventual-consistency Read Path (Search & Discovery) from the strict-serializability Write Path (Holds, Payments, and Bookings). Client reads never query the transactional booking databases directly; instead, committed state synchronizes asynchronously through Change Data Capture (CDC) and Kafka, while checkouts execute synchronously across private relational boundaries.

End-to-end system topology

To prevent visual clutter and keep the CQRS separation intuitive, the system topology is split into two numbered architectural diagrams: the Read & Discovery Path (handling 99% of user traffic) and the Transactional Booking & Asynchronous Event Mesh (handling strict ACID writes and cache synchronization).

1. Read & Discovery Topology (CQRS Query Path — ~3,000 QPS)

This flow illustrates how high-throughput discovery requests evaluate geographic filters, faceted search, and multi-date availability without querying the transactional database.

What is the BFF (Backend-for-Frontend)? Rather than forcing mobile or web clients to make multiple separate network requests over high-latency cellular connections (one for search cards, another for dynamic prices, and a third for amenities), the BFF serves as an API aggregator. It receives one request from the client, queries downstream microservices in parallel, stitches the data into a single screen-ready response, and returns it in a single round-trip.

Read and discovery topology — High-throughput discovery requests evaluate geographic filters, faceted search, and multi-date availability without querying the transactional database.

2. Transactional Booking & Asynchronous Event Mesh (CQRS Command Path — ~30 QPS)

This flow illustrates how temporary holds, payments, and background cache synchronization execute under strict ACID isolation, using the Universal Outbox pattern to eliminate dual-write risks:

Transactional booking and asynchronous event mesh — Temporary holds, payments, and background cache synchronization execute under strict ACID isolation, using the Universal Outbox pattern to eliminate dual-write risks.

Core architectural invariants

The API Gateway and BFF: Provide centralized routing, rate limiting, and client-tailored data aggregation, insulating frontend clients from microservice complexity.
Co-Located Transactional Core: Booking and Inventory share a unified service boundary and relational schema (Booking & Inventory DB). Inventory checks and reservation holds commit in a single local ACID transaction (< 2ms), eliminating distributed locks (2PC) on the hot checkout path.
Universal Outbox & Async Sync: Every inventory mutation (HoldAcquired, InventoryReleased, HoldExpired, BookingConfirmed) writes an outbox record within the same commit. A CDC pipeline tails the MySQL binlog to Kafka, driving Redis counters and Elasticsearch with zero dual-write drift.
Authoritative Data Guarantees: Redis provides rapid pre-filtering for search, but all final hold and booking transactions execute strictly against the authoritative relational inventory database.

From the simple approach to the chosen design

The monolithic baseline and why it breaks

The simplest architecture is a monolithic web service backed by a single relational database joining hotels, room_types, rates, and historical reservations to calculate room availability on the fly during search queries.

In production with 5,000 properties and millions of bookings, this naive approach collapses for three reasons:

Severe query latency from join amplification: Computing availability by aggregating reservation rows across overlapping multi-date intervals requires scanning millions of records. Under 3,000 search QPS, database CPU saturates immediately.
Lock contention and connection starvation: High-frequency, slow-running search queries monopolize database connection pools. When a user attempts to book a room, their transaction queues behind read queries, leading to checkout timeouts.
Inability to handle complex faceted discovery: Relational databases struggle with combined full-text matching, geospatial proximity calculations, and multi-attribute filtering without specialized, slow indexes.

Decoupling search queries from booking transactions

To resolve these bottlenecks, the chosen architecture decouples the system along the read-write boundary using command-query responsibility segregation (CQRS):

Search read path: All property discovery queries route to an Elasticsearch cluster. Property details, coordinates, amenities, and daily base rates are flattened into denormalized search documents, returning faceted results in under 50 milliseconds.
Booking write path: All inventory reservations, hold acquisitions, and payment state transitions route to a dedicated relational database running under strict ACID transaction isolation.
Log-based replication: Rather than updating both stores synchronously in application code—which introduces distributed transaction vulnerabilities—the system employs Change Data Capture (CDC). A transaction-log tailer publishes database commits to Kafka, which asynchronously indexes updates into Elasticsearch.

Every architectural option carries an engineering cost. Decoupling search from booking eliminates database lock contention and provides sub-second search scaling, but introduces an eventual consistency lag of 1 to 2 seconds between booking a room and having that room reflected as unavailable in search results. The system manages this trade-off by introducing a synchronous Just-in-Time validation step at checkout.

Technology choices

Relational storage for inventory and reservations

Engineering pressure: Reserving inventory requires checking current capacity, verifying that total reservations do not exceed 110% of capacity, decrementing the counter, and inserting a booking record. These steps must execute as a single atomic unit to prevent overselling.

Storage Option	Evaluation for Booking Core	Operational Cost and Trade-off
PostgreSQL / MySQL (Chosen)	Native ACID transactions, mature row-level locking (`SELECT ... FOR UPDATE`), and strong isolation.	Vertical scaling ceiling; requires explicit partitioning by `hotel_id` at extreme scale.
NoSQL (MongoDB / DynamoDB)	Excellent horizontal partitioning and document storage.	Lacks multi-row atomic transactions across heterogeneous collections without performance penalties.
Distributed NewSQL (Spanner / CockroachDB)	Globally distributed ACID transactions with consensus replication.	Significant operational overhead, increased write latency (multi-region consensus), and higher infrastructure cost.

Decision rationale: PostgreSQL or MySQL is selected for the inventory and booking tier. At a baseline sustained write load of ~3 reservations per second (peaking at ~30 per second), a properly indexed relational engine handles the throughput comfortably on a single primary node. The ACID model prevents double bookings, while hotel_id provides a natural sharding boundary for long-term growth.

Dedicated search cluster with Elasticsearch

Engineering pressure: Finding hotels requires filtering simultaneously by location (e.g., within 5 km of city center), star rating, amenities (pool, wifi), and price. Running multi-attribute geospatial queries across thousands of properties on a high-throughput read path would cripple a relational database.

Decision rationale: Elasticsearch powers property discovery:

Denormalized documents: Each JSON document holds a complete property profile—coordinates, star ratings, amenities, and baseline prices—eliminating expensive multi-table SQL joins.
Specialized indexing: Inverted indexes filter amenities instantly, while BKD trees evaluate spatial coordinates (geo_distance) and price ranges in sub-50ms.
Cursor pagination: Queries use search_after with Point-in-Time (PIT) snapshots, ensuring consistent, low-memory pagination without deep from/size offsets.

Coarse Discovery vs. Dynamic Pricing: Elasticsearch only handles coarse discovery using baseline rates (base_min_rate). Calculating fluctuating nightly rates for 730 forward calendar dates is delegated to the downstream Rate Service and Redis, preventing high-frequency write churn on search indices.

In-memory cache for fast availability checks

Engineering pressure: Evaluating availability across search results would overwhelm the relational database if every search hit triggered SQL queries.

Decision rationale: Redis stores pre-computed availability counters for active dates (avail:{hotel_id}:{room_type_id}:{date}). Batch lookups (MGET, taking O(N keys) grouped by cluster hash slots) pre-filter available hotels in memory. Redis serves strictly as an optimization layer for search discovery; authoritative availability checks execute against MySQL during checkout hold acquisition.

Distributed message log for event distribution

Engineering pressure: Booking lifecycle updates, property modifications, and rate revisions must fan out to search indexing, financial reporting, and notifications without creating synchronous failure dependencies.

Decision rationale: Apache Kafka provides durable, partitioned event streaming. Partitioning by hotel_id guarantees that all state updates for a specific property are processed in strict chronological order by downstream consumers.

Components and contracts

Service ownership and failure contracts

The platform is structured into decoupled domain services, each maintaining strict ownership over its data models and operational boundaries.

Component	Domain Ownership	Inbound Contract	Outbound Calls	Failure Strategy
API Gateway & BFF	Routing, auth, rate limiting, UI aggregation	Client HTTPS requests	Calls Search, Hotel, Rate, Booking services	Returns HTTP 429 on rate limit; degrades gracefully omitting non-critical amenities.
Search Service	Property discovery and filtering	`GET /v1/hotels/search`	Queries Elasticsearch and Redis	Returns cached regional discovery results if search cluster fails.
Hotel Service	Hotel metadata and media CDN references	`GET/POST /v1/hotels`	Reads/writes Hotel DB; publishes Kafka events	Serves cached metadata from edge CDNs.
Rate Service	Dynamic pricing models	`GET /v1/rates`	Queries Rate DB; caches in Redis	Falls back to static base seasonal rates on cache miss.
Booking & Inventory Service	Reservation lifecycle and daily room inventory counters	`POST /v1/reservations`, `POST /v1/reservations/:id/confirm`	Manages MySQL Primary; calls Payment Service	Releases inventory on hard payment decline; freezes lease during processing; emits universal outbox events.
Payment Service	Payment gateway integration & reconciliation	`POST /v1/payments/charge`	Calls external Payment Gateway	Enforces client idempotency keys; exposes webhooks and reconciliation jobs for timeout recovery.

The Booking and Inventory transactional bounded context

Should Booking and Inventory have separate databases? No.

The Invariant: You cannot hold a room without creating a reservation, and you cannot create a reservation without holding a room. Splitting them across separate databases requires distributed locks (2PC or Sagas), which cause high latency, deadlocks, and overselling risks during high-demand surges.
The Solution: Co-locate reservations and room_type_inventory in the same relational database. A single local ACID transaction (SELECT ... FOR UPDATE + INSERT) secures the hold in < 2ms—guaranteeing zero double-booking without distributed locking. Sagas are reserved strictly for external third-party boundaries like payment gateways.

How the system works

The search and discovery path

Notice that property search executes in two clean stages—filtering by location and amenities in Elasticsearch, then verifying active room counts in Redis—without querying the authoritative transactional database.

Search and discovery sequence — Property search filters by location and amenities in Elasticsearch, then verifies active room counts in Redis, without querying the authoritative transactional database.

Stage 1 (Filter by physical traits in Elasticsearch): Elasticsearch evaluates static filters (city boundaries, spatial coordinates, star ratings, and amenities). It returns hotels that physically match the user’s criteria.
Stage 2 (Verify calendar availability in Redis): Because Elasticsearch does not store fluctuating daily room counters, the Search Service checks Redis (MGET) across all requested dates. A room type is considered available if its counter exceeds zero for every single night of the stay:

Available = counter[date] > 0 for all dates in [check_in, check_out)

Stage 3 (Enrichment in BFF): The BFF takes the available hotels, fetches dynamic pricing from the Rate Service and photo URLs from the CDN, and returns screen-ready cards in a single network round-trip.

Deep Dive: The Pagination vs. Sold-Out Hotels Dilemma: Discarding fully booked hotels post-search creates fragmented, underfilled pages (e.g., returning 3 results instead of 20). Production platforms handle this via:

Display & Badge (Booking.com style): Return all 20 hotels, badging unavailable properties with “Sold out for your dates”. This preserves fixed page sizes, avoids empty result sets, and drives conversion urgency.

Coarse Availability Flags: The CDC pipeline syncs a coarse boolean flag (is_sold_out) to Elasticsearch only when a hotel hits zero vacancies—filtering out sold-out properties before pagination without write-churning exact room counters.

Preview reads vs. authoritative checkout checks

Because the search index and Redis cache synchronize asynchronously via Kafka CDC, search availability exhibits a 1-to-2 second eventual-consistency lag. Rather than introducing redundant, deadlock-prone non-locking database preview reads (SELECT ... FOR SHARE) before entering checkout, the architecture delegates authoritative validation directly to the Phase 1 Hold Acquisition transaction. If inventory was claimed in the intervening seconds, the atomic hold transaction fails immediately with HTTP 409 Conflict, cleanly bouncing the user before they enter payment details.

The reservation write path

Checkout executes as a two-phase transaction lifecycle separated by the guest’s 10-minute payment window.

Phase 1: Temporary Hold Acquisition (10-Minute Lease)

The traveler selects a room type and stay dates. The Booking Service acquires a time-bound inventory hold before requesting payment details:

Hold acquisition lifecycle — The Booking Service acquires a 10-minute pessimistic row lock on inventory counters and publishes an outbox hold event before requesting payment.

Pessimistic Acquisition & Price Verification: The Booking engine opens a transaction and acquires exclusive row locks (ORDER BY date ASC FOR UPDATE) across the stay dates. It validates that the quoted search rate matches current pricing (expected_total_amount). If valid, it increments reserved capacity, creates the PENDING reservation, and writes a HoldAcquired event to the outbox table in the same atomic commit.
Time-Bound Leases: An asynchronous timer is dispatched to SQS (delay = 600s). The client receives HTTP 201 Created with a 10-minute lease. If SQS publishing drops, the low-frequency safety sweeper acts as a fallback.

Phase 2: Payment Settlement & Confirmation

After entering card details, the client confirms the booking. The system freezes the lease and captures payment:

Payment settlement and confirmation lifecycle — The system freezes the lease before charging the payment gateway, handling definitive declines, successful captures, and indeterminate timeouts.

Pre-Charge Lease Freezing: Before contacting the payment gateway, the Booking Service freezes the lease: UPDATE reservations SET status = 'PAYMENT_PROCESSING' WHERE reservation_id = :id AND status = 'PENDING' AND hold_expires_at > NOW(). If the timer already expired, 0 rows match and checkout halts with HTTP 409 Conflict—guaranteeing customer cards are never charged on released rooms.
Definitive Declines vs. Gateway Timeouts:
- Definitive Failure (Hard Decline): The gateway explicitly declines the card (HTTP 402). The service immediately rolls back inventory (total_reserved - 1), transitions status to PAYMENT_FAILED, writes an InventoryReleased outbox event, and notifies the client.
- Indeterminate Failure (Timeout / 5xx): A timeout is an unknown outcome; the card may have already settled. The system leaves the reservation in PAYMENT_PROCESSING and returns HTTP 202 Accepted. A background reconciler queries the gateway using idempotency key K2 or awaits an asynchronous webhook. Inventory is released if and only if the gateway confirms no charge occurred.
Universal Outbox Event Publication: Every transaction that modifies inventory (Hold, Release, Expiration, Confirmation) inserts into the outbox table within the same commit. A CDC connector tails the MySQL binlog and relays events to Kafka, eliminating dual-write drift across Redis caches and Elasticsearch indices.

Administrative and guest API interfaces

The system exposes clean RESTful interfaces over HTTPS, enforcing strict payload validation and authorization rules:

# Guest Search and Discovery
GET    /v1/hotels/search                 # Paginated hotel search with multi-attribute filters
GET    /v1/hotels/:id                    # Comprehensive hotel metadata, room types, and media

# Reservation Lifecycle (Two-Phase Checkout)
POST   /v1/reservations                  # Phase 1: Acquire 10-min inventory hold (requires client Idempotency-Key)
POST   /v1/reservations/:id/confirm      # Phase 2: Settle payment & confirm booking (requires client Idempotency-Key)
GET    /v1/reservations/:id              # Retrieve reservation status and stay details
DELETE /v1/reservations/:id              # Request cancellation and refund processing

# Administrative Inventory Management
POST   /v1/admin/hotels                  # Register a new hotel property
PUT    /v1/admin/hotels/:id/inventory    # Batch adjust base inventory allocation
PUT    /v1/admin/hotels/:id/rates        # Update nightly room pricing structures

The Phase 1 hold request initiates the reservation lease under a client-supplied idempotency key, carrying the quoted rate to catch price updates:

/* POST /v1/reservations (Phase 1: Acquire Hold) */
/* Header: Idempotency-Key: 7c8e9b0a-1a2b-4c3d-8e5f-0a1b2c3d4e5f */
{
  "hotel_id": "h-5012",
  "room_type_id": "rt-king-std",
  "check_in_date": "2026-12-20",
  "check_out_date": "2026-12-22",
  "guest_count": 2,
  "expected_total_amount": 290.00,
  "currency": "USD",
  "guest_details": {
    "full_name": "Jane Doe",
    "email": "jane.doe@example.com"
  }
}

The Phase 2 confirmation request settles payment with an explicit client idempotency key:

/* POST /v1/reservations/res-8831/confirm (Phase 2: Confirm & Pay) */
/* Header: Idempotency-Key: 9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d */
{
  "payment_method_token": "pm_tok_1N4mC2Lkd8unv",
  "currency": "USD"
}

Data model and inventory schema

ROOM_TYPE_INVENTORY stores daily availability counters rather than individual physical room records, turning multi-night stays into simple range scans.

Daily inventory matrix: ROOM_TYPE_INVENTORY stores one row per (hotel_id, room_type_id, date). Clustered primary key indexing makes multi-night availability lookups fast sequential range scans.
Database idempotency fence: A unique constraint on (hotel_id, idempotency_key) in the RESERVATION table acts as the durable backstop. Even if Redis cache fails or retries race, the database guarantees duplicate requests cannot write two bookings.
Property-local timezones: Inventory dates are strictly anchored to the property’s local timezone (HOTEL.timezone). A booking for Dec 20 in Tokyo anchors to JST, preventing UTC date-boundary drift.
10-minute price lock: When a hold is created, the quoted price snapshots into RESERVATION.total_amount. Even if dynamic pricing adjusts rates mid-checkout, the guest pays the locked snapshot.
Pre-allocated timeline: Background jobs pre-populate dates 2 years in advance:
```
5,000 hotels × 20 room types × 730 days = 73,000,000 rows (~7.3 GB)
```
At ~7.3 GB, this narrow table fits entirely inside database buffer pool memory on standard hardware.

Reservation lifecycle and state transitions

The reservation lifecycle operates as a strictly unidirectional state machine to prevent race conditions and illegal status revivals:

Hold & Payment States: Bookings begin in PENDING. Initiating checkout transitions the row to PAYMENT_PROCESSING, freezing the lease to disarm the 10-minute expiry timer. Successful capture transitions to PAID, while card declines move to PAYMENT_FAILED and restore inventory.
Expiration vs. Cancellation: If checkout abandons, the 10-minute timer marks the hold EXPIRED. Voluntary guest cancellations move PAID bookings to CANCELLED, asynchronously triggering a refund workflow terminating in REFUNDED for eligible rate plans.
Terminal Invariant: Terminal states (EXPIRED, CANCELLED, REFUNDED) are immutable and cannot transition back to active bookings under any retry pathway.

The hardest design choices

Concurrency control on inventory counters

The engineering pressure: When hundreds of users simultaneously attempt to reserve the final room for a holiday weekend, multiple transactions read total_reserved = 9 against a capacity of 10. Without rigorous concurrency control, all transactions increment the counter, causing severe overselling beyond the permitted overbooking threshold.

Concurrency Mechanism	Structural Behavior	Engineering Cost and Evaluation
Single-Statement Conditional Update	Executes `UPDATE ... WHERE total_reserved + :qty <= cap`, checking affected rows.	Eliminates read-before-write latency. Ideal for single-night bookings, but multi-night stays span multiple rows where partial updates require complex application compensation.
Pessimistic Locking (`FOR UPDATE`)	Explicitly locks matching `ROOM_TYPE_INVENTORY` rows on read, serializing concurrent transactions.	Excellent multi-night correctness; holds connection resources for transaction duration (< 5ms); requires strict row lock ordering to avoid deadlocks.
Optimistic Concurrency Control (OCC)	Reads without locking; verifies a `version` column at write time (`WHERE version = :old_version`).	Performs well under low contention; degrades into massive retry storms and high CPU during high-contention events.
Database `CHECK` Constraint	Relies on an engine-level rule: `CHECK (total_reserved <= total_inventory * 1.10)`.	Best used as an unbreachable last-line defense backstop; provides no queueing or graceful error recovery for competing transactions.

The chosen design: The engine uses pessimistic locking (SELECT ... FOR UPDATE) under READ COMMITTED isolation. Because transactions only verify and update counters without external network calls, row locks hold for under 5 ms, avoiding gap-lock contention on neighboring dates.

To guarantee that concurrent transactions booking multi-day stays never deadlock, the application strictly sorts locked dates chronologically before issuing the query:

-- Atomic, ordered pessimistic locking transaction (half-open interval [check_in, check_out))
BEGIN;

SELECT date, total_inventory, total_reserved
FROM room_type_inventory
WHERE hotel_id = 'h-1002'
  AND room_type_id = 'rt-deluxe-queen'
  AND date >= '2026-12-20'
  AND date < '2026-12-22'
ORDER BY date ASC
FOR UPDATE;

-- Application evaluates condition across all returned rows:
-- total_reserved + :requested_rooms <= FLOOR(total_inventory * 1.10)

UPDATE room_type_inventory
SET total_reserved = total_reserved + :requested_rooms
WHERE hotel_id = 'h-1002'
  AND room_type_id = 'rt-deluxe-queen'
  AND date >= '2026-12-20'
  AND date < '2026-12-22';

INSERT INTO reservations (
  reservation_id, hotel_id, room_type_id, 
  check_in_date, check_out_date, total_amount, currency, 
  idempotency_key, hold_expires_at, status
)
VALUES (
  'res-8831', 'h-1002', 'rt-deluxe-queen', 
  '2026-12-20', '2026-12-22', 290.00, 'USD', 
  '7c8e9b0a-1a2b-4c3d-8e5f-0a1b2c3d4e5f', NOW() + INTERVAL 10 MINUTE, 'PENDING'
);

INSERT INTO outbox (topic, payload)
VALUES ('inventory-events', '{"event": "HoldAcquired", "reservation_id": "res-8831"}');

COMMIT;

Locking dates in chronological sequence (ORDER BY date ASC) eliminates booking-vs-booking deadlocks. All other write pathways—cancellations, expiry releases, and administrative adjustments—must acquire row locks in the exact same chronological sequence, and callers should retain a 3-attempt jittered retry policy.

The time-bound hold problem

The engineering pressure: Holding inventory during checkout introduces a critical failure mode: if a traveler closes their browser tab or loses network connectivity mid-checkout, the reserved room remains locked indefinitely. Conversely, if no hold is acquired, travelers frequently enter payment details only to find the room sold out.

The platform requires an automated, resilient mechanism to hold inventory for exactly 10 minutes and release it if payment does not clear.

Hold Implementation Strategy	Operational Mechanics	Core Limitation / Architectural Cost
Database Polling Worker	A cron worker queries `WHERE status = 'PENDING' AND created_at < NOW() - INTERVAL 10 MIN`.	Constant read overhead and table scans against the relational database; introduces release latency.
Redis Keyspace Notifications	Keys set with a 10-minute TTL; expiration events trigger a cancellation listener.	Redis Pub/Sub is fire-and-forget; if the listener crashes during key expiration, the event is permanently lost.
Delayed Message Queue (Chosen)	Upon hold creation, a message is dispatched to a delay queue scheduled for delivery in 600s.	Requires an external message broker (e.g. AWS SQS 15-min limit); requires consumer idempotency and a safety sweeper backstop.

The chosen design: The system implements a Delayed Message Queue backed by a Reconciliation Sweeper. When a hold is acquired, the Booking Service produces an event to an AWS SQS Delay Queue (within its 15-minute maximum delay cap) scheduled for delivery in 10 minutes (600 seconds). SQS acts purely as an asynchronous timer; the consumer validates reservation state against the database before reverting inventory, backed by a periodic reconciliation sweeper.

Resolving the payment-vs-expiry race condition

A critical race condition occurs when a guest submits payment at second 599, right as the 10-minute hold lease expires. If the expiration timer releases inventory while the bank processes the charge, the traveler is billed for a lost room.

The architecture eliminates this race via a Pre-Charge Lease Freeze:

Freeze the lease (t = 599s): Before contacting the external payment gateway, the system atomically updates the reservation:
```
UPDATE reservations 
SET status = 'PAYMENT_PROCESSING' 
WHERE reservation_id = :id AND status = 'PENDING' AND hold_expires_at > NOW();
```
If 0 rows update (the hold already expired), payment aborts immediately—the guest’s card is never charged.
Disarm the timer (t = 600s): When the 10-minute queue timer fires, the worker inspects the database. Because the row is in PAYMENT_PROCESSING (not PENDING), the worker discards the message without touching inventory.
Settle payment (t = 602s): Once the gateway confirms the charge, the status flips to PAID.
Crash recovery backstop: If the gateway hangs or the worker crashes, a 15-minute reconciliation cron queries the payment provider with the idempotency key. If charged, it transitions to PAID; if uncharged after the grace window, it releases inventory and transitions to EXPIRED.

Freezing the lease before charging guarantees a customer is never billed for an expired or reallocated room.

Search index synchronization without dual writes

The engineering pressure: When hotel attributes, base rates, or room availability change, the updates must be indexed into Elasticsearch. Writing to the database and then directly issuing an HTTP write to Elasticsearch introduces permanent data drift if the second call fails.

CDC pipeline from relational log to Elasticsearch

Notice that a CDC connector tails the MySQL binary log asynchronously, eliminating distributed transactions between the relational database and the search cluster.

CDC pipeline from relational log to search cluster — A CDC connector tails the MySQL binary log asynchronously to stream committed inventory and property updates to Kafka and Elasticsearch.

The Database as the Single Source of Truth: All mutations commit exclusively to the relational engine; no application code issues writes directly to Elasticsearch.
Log-Based Change Data Capture: The CDC pipeline continuously tails the MySQL binary log, extracting committed row transitions and publishing them to Kafka as structured change envelopes.
Rebuildable Search State: If Elasticsearch indices become corrupted, the entire search cluster can be reconstructed by rewinding Kafka consumer offsets or streaming an initial database snapshot.

Design deep dives

Two-tier idempotency for bookings and payments

Client retries after network drops must never create duplicate bookings or double charges. The platform enforces idempotency across two distinct tiers:

Booking Tier: The API Gateway locks concurrent in-flight retries using a short-lived Redis lease (SETNX) and serves cached results for 24 hours. A database unique constraint (hotel_id, idempotency_key) acts as the durable backstop against duplicate booking records.
Payment Tier: The Booking Service forwards the idempotency key directly to the external payment gateway. If an API call times out after funds settle, retrying returns the original charge receipt rather than executing a duplicate charge.

Distributed saga orchestration for checkout

Two-Phase Commit (2PC) cannot span external payment gateways without holding database locks indefinitely. Instead, checkout executes as an Orchestrated Saga (Hold → Payment) managed by the Booking Service:

Checkout saga orchestration — Forward saga flow and compensating transactions coordinating inventory holds, lease freezes, payment execution, and inventory releases.

Compensating Rollbacks: If payment fails, the orchestrator triggers an automatic rollback—releasing the room hold and emitting an InventoryReleased outbox event.
Idempotent Retries: All compensating steps are idempotent; network blips during refunds or inventory releases can be safely retried without compounding errors.
Decoupled Notifications: Confirmation emails asynchronously consume the BookingConfirmed event from Kafka. An email delivery failure never rolls back a settled reservation.

Mathematical inventory counter streaming in cache

Deleting Redis keys on inventory changes triggers severe cache stampedes during high-demand searches. Instead of invalidation, the system uses delta streaming:

Atomic Counter Streaming: Kafka consumers stream deltas directly to Redis—decrementing availability on HoldAcquired and restoring it on InventoryReleased or HoldExpired. An atomic Lua script updates counts only for keys currently resident in cache.
Nightly Drift Reconciliation: A scheduled background job recalculates Redis counters directly from the database to correct any drift from key evictions or consumer lag.

Scale and capacity

Capacity planning and throughput baseline

System capacity is derived from a baseline of 5,000 properties averaging 200 rooms per hotel (1,000,000 total physical rooms), with a 70% average annual occupancy rate and a 3-night average stay duration.

Daily Check-ins / Bookings = (1,000,000 rooms × 0.70 occupancy) / 3-night stay ≈ 233,333 bookings/day

Sustained Paid Booking QPS = 233,333 bookings / 86,400 seconds ≈ 2.7 QPS (round to ~3 QPS)

In hospitality platforms, user traffic follows a steep discovery funnel where approximately 10 room holds occur for every completed booking (10:1 hold-to-booking ratio):

Sustained Hold Write QPS = 3 bookings/s × 10 = 30 hold QPS (Peak: ~300 QPS at 10× surge)

The following capacity matrix synthesizes traffic throughput, storage footprint, and operational takeaways:

Dimension	Baseline Metric	Derivation / Formula	Peak Scale	Architectural Takeaway
Hold Write Transactions	30 QPS	10:1 hold-to-booking funnel	300 QPS (10× surge)	Single MySQL primary handles write transactions comfortably without sharding.
Confirmed Booking Writes	3 QPS	233,333 bookings / 86,400s	30 QPS (10× surge)	Trivial commit volume; outbox events stream asynchronously to Kafka.
Hotel Details Reads	300 QPS	100:1 details-to-booking ratio	1,000 QPS (3× surge)	Cached at edge CDNs and Redis; zero transactional DB hits.
Discovery Search Reads	3,000 QPS	1,000:1 search-to-booking ratio	9,000 QPS (3× surge)	Fully offloaded to Elasticsearch cluster using cursor pagination.
Active Inventory Table	7.3 GB (73M rows)	5K hotels × 20 room types × 730 days × 100B	Constant (2-yr rolling window)	Fits 100% within a single 32 GB MySQL buffer pool in RAM.
Reservation Records	42.5 GB / year (85M rows)	233K daily bookings × 365 days × 500B	Linear annual growth	Low volume; old completed stays cold-archive asynchronously to S3.
Search Index Size	~500 MB	5,000 property documents × 100 KB JSON	Negligible growth	Entire search catalog resides permanently in Elasticsearch cluster memory.

Architectural Insight (Where the Real Bottleneck Lies): Distributed NewSQL or early sharding is unnecessary: 30 sustained write QPS (300 surge) is easily handled by a single indexed MySQL primary. The true bottleneck is concurrency contention, not data volume—hundreds of travelers competing for the last remaining rooms during a major event creates row-level lock serialization on single date counters, not disk or network exhaustion.

Deeper look: scaling strategy at 10× scale

If the platform expands by an order of magnitude (50,000 properties, 10,000,000 rooms, 30,000 search QPS, and 300 sustained paid booking QPS / 3,000 hold QPS), the architecture adapts through three targeted structural shifts:

Relational Sharding by hotel_id: Every inventory lookup, row-level lock (SELECT ... FOR UPDATE), and reservation insertion filters by hotel_id. Multi-day booking transactions never span multiple shards, completely avoiding distributed two-phase commit (2PC). To prevent cross-shard scatter-gather queries for guest trip histories (WHERE user_id = :id), completed stays replicate asynchronously via Kafka to a secondary datastore partitioned strictly by user_id.
Search Read Caching: A distributed cache layer (such as Memcached) sits in front of Elasticsearch to absorb repeated hot search queries for major metropolitan hubs.
Partitioned Event Queues: Kafka topics expand to 128+ partitions partitioned by hotel_id, preserving strict per-property event ordering while parallelizing consumer throughput.

Failure modes and recovery

Failure mitigation matrix

The following operational playbook outlines automated mitigation pathways for primary component outages across the architecture.

Component Failure	Immediate Impact	Automated Architecture Defense	Residual System Risk
MySQL Primary Instance Crash	Write operations fail; users cannot acquire holds or confirm bookings.	Automated failover promotes a semi-synchronous hot standby replica (e.g., AWS Multi-AZ Orchestrator) within 30 seconds with zero committed data loss.	In-flight uncommitted transactions are aborted and retried idempotently by clients.
Elasticsearch Cluster Outage	Property search and discovery fails completely.	Circuit breaker trips; BFF serves cached regional discovery pages from Redis and CDN.	Search results are temporarily static; deep filtering is disabled.
Redis Cache Cluster Failure	Search service encounters cache misses; stampede risk.	Search degrades gracefully: bypasses availability pre-filtering; authoritative availability is validated strictly upon checkout hold. Direct search queries never hit relational replicas.	Temporary inability to pre-filter sold-out rooms on discovery pages.
Payment Gateway Timeout	Checkout transactions hang waiting for HTTP replies.	Reservation remains in `PAYMENT_PROCESSING`; background reconciler polls gateway by idempotency key. No inventory is released until gateway confirms no charge.	User sees a “Processing Confirmation” screen rather than instant confirmation.
Kafka Broker Partition Outage	CDC pipeline and search index updates halt.	Microservices continue committing locally to MySQL; the CDC connector pauses and resumes streaming from its binlog checkpoint once Kafka recovers. Binlog retention is configured for 72 hours.	Search results exhibit increased staleness relative to database truth until backlog clears.
CDC Pipeline Process Failure	Elasticsearch index drifts out of sync with relational data.	The CDC connector automatically resumes streaming from its last committed checkpoint offset upon restart.	Stale search availability until the replication backlog clears.

Cascading failure protection with circuit breakers

When external payment gateways or third-party service providers experience brownouts, downstream HTTP request threads block, quickly exhausting application container thread pools and causing cascading system-wide failure.

To prevent thread starvation, all outbound gateway integrations enforce strict socket timeouts (3,000ms for payments) and circuit breakers. If error rates exceed threshold boundaries, the circuit breaker fails fast immediately, redirecting requests to asynchronous webhooks or cached fallbacks without tying up system resources.

Key takeaways

Core design takeawaysReview the core architectural paths before moving on to the quiz.Show summaryHide summary

Hotel Booking System at Scale

Review the core architectural paths before moving on to the quiz.

Mental Model & Data AbstractionCounter abstraction, timezone anchoring, and overbooking rules

Daily Counter Matrix

Room types over physical doors: physical room assigned at front-desk check-in

Daily integer counters per (hotel_id, room_type_id, date) eliminate multi-room interval overlaps

Reserving N nights is simply N atomic row increments

Timezone & Pricing Invariants

Timezone Anchor: inventory dates anchored to property timezone, preventing boundary drift

10-Minute Price Lock: quoted price snapshotted into reservation record at hold creation

Controlled Overbooking: accepts reservations up to 110% of physical capacity per night

CQRS & Read/Write SeparationDecoupling discovery traffic from transactional booking writes

Search & Discovery Read Path

Elasticsearch powers sub-50ms faceted, spatial, and amenity discovery queries

Redis MGET pre-filters calendar availability without touching relational databases

Pagination resilience: badges sold-out hotels to preserve consistent page sizes

Log-Based Event Mesh

Universal Outbox: mutations commit outbox events in the same local ACID transaction

CDC connector tails MySQL binary log to Kafka, eliminating dual-write data drift

Rebuildable search state: Elasticsearch index reconstructs by replaying Kafka offsets

Transactional Booking CoreCo-location, pessimistic locking, and deadlock elimination

Co-Located Schema Boundary

Reservations and inventory reside in the same relational schema

Local ACID transaction (< 2ms) eliminates slow, brittle distributed 2PC locks

Sharding natural boundary: hotel_id isolates multi-day booking transactions

Concurrency & Deadlock Prevention

Pessimistic Locking: SELECT ... FOR UPDATE serializes concurrent writes cleanly

READ COMMITTED isolation disables next-key gap locks on neighboring dates

Chronological ORDER BY date ASC breaks circular wait to eliminate booking deadlocks

Hold Lifecycle & Race ResolutionSQS delay queues, lease freezing, and settlement sagas

Time-Bound Hold Management

SQS delay queue acts as a 600-second timer; database is the sole arbiter of state

Reconciliation sweeper: 15-minute cron catches rare publish dropouts

Universal Outbox emits HoldExpired and decrements reserved count atomically

Pre-Charge Lease Freezing

Updates status to PAYMENT_PROCESSING before calling external payment gateway

If lease already expired (0 rows updated), payment halts before charging card

Disarms 600s SQS timer: worker discards messages for rows in PAYMENT_PROCESSING

Distributed Resilience & ScaleTwo-tier idempotency, cache streaming, and capacity bottlenecks

Two-Tier Idempotency & Saga

Booking tier: Redis SETNX 120s lease + 24-hour response cache absorbs fast retries

Database backstop: UNIQUE KEY (hotel_id, idempotency_key) prevents duplicate records

Orchestrated Saga: coordinates Hold -> Payment with idempotent compensating rollbacks

Cache Streaming & Capacity Reality

Delta streaming via Lua updates Redis counters in place, eliminating cache stampedes

73M inventory rows (7.3 GB) fit 100% within a single MySQL buffer pool in RAM

True bottleneck: row-level lock contention on hot dates during peak surges, not storage

Architecture recap challenge

Hotel booking architecture challenge

Can you design a hotel booking system that survives peak event contention?

Ten critical scenarios across inventory modeling, concurrency, CQRS, sagas, and capacity. Test your architectural instincts.

1 / 10

Question 1 of 10

Why does the system model hotel inventory as daily integer counters per (hotel_id, room_type_id, date) instead of tracking individual physical room calendars (e.g., Room 304)?