Design a Hotel Reservation System

1. Problem Statement & Scope Clarification

System Mission

Design a planetary-scale hotel reservation and lodging booking platform (equivalent to Booking.com, Airbnb, or Marriott.com) capable of orchestrating room searches, 15-minute temporary inventory holds, and confirmed bookings across 500,000 hotels and 2.5 million room types worldwide. The system must mathematically guarantee zero overbooking across overlapping calendar date ranges, deliver sub-150ms geospatial search latencies, and automatically release held room inventory back to the booking pool if guest payment is not captured within 15 minutes.

Hotel Reservation System at a Glance

Functional Requirements

1. Hotel Search & Filter (SearchHotels): Search available properties by geographic coordinates (lat/long bounding box), check-in/check-out dates, guest count, price tier, and amenities with sub-150ms latency.
2. Temporary Inventory Hold (ReserveRoomHold): Atomically reserve 1 or more rooms of a specific room type across a continuous date range (e.g., Oct 15 to Oct 18) for exactly 15 minutes while the guest completes payment.
3. Confirmed Booking (ConfirmBooking): Transition a temporary hold to a permanent confirmed reservation upon verified payment capture, generating a tamper-evident reservation confirmation code.
4. Automated Hold Expiration & Rollback: Automatically expire and release held rooms back into the general availability pool if payment is not completed before the 15-minute lease expires, without leaving orphaned locks.
5. Zero Overbooking Invariant: Enforce a strict mathematical guarantee that no room type on any single calendar date is ever booked beyond its physical room capacity ($R_{\text{reserved}} \le R_{\text{total}}$).

Non-Functional Requirements (SLAs & SLOs)

• Data Integrity & Consistency: Strict ACID serializability on inventory counters. Zero double-booking across overlapping date intervals under arbitrary concurrency.
• Availability: $99.99\%$ uptime SLA globally across both read (search) and write (booking) tiers.
• Latency (P95/P99):
  • Hotel Search & Availability Filter: $P95 < 150\text{ ms}$, $P99 < 300\text{ ms}$.
  • Temporary Hold Creation: $P99 < 80\text{ ms}$.
  • Booking Confirmation: $P99 < 100\text{ ms}$.
• Throughput & Scale: Support $50,000\text{ search QPS}$ and $500\text{ reservation holds/sec}$ at peak, handling $1,000,000\text{ completed bookings/day}$.
• Idempotency: Prevent duplicate bookings and credit card charges from client retries via cryptographic idempotency keys.

---

2. Capacity & Scale Estimation (Back-of-the-Envelope Math)

Scale & Inventory Matrix Sizing

• Total Properties: $500,000\text{ hotels}$ worldwide.
• Average Room Types per Hotel: 5 room types (e.g., Deluxe King, Standard Queen, Executive Suite, Ocean View, Penthouse).
• Total Unique Room-Type Entities: $N_{\text{room\_types}} = 500,000 \times 5 = \mathbf{2,500,000\text{ room types}}$
• Booking Window Horizon: 2 years forward ($730\text{ days}$).
• Total Rows in Daily Inventory Matrix: $N_{\text{rows}} = 2,500,000\text{ room types} \times 730\text{ days} = \mathbf{1,825,000,000\text{ rows (1.825 Billion Rows)}}$

Daily Inventory Table Storage Sizing (Aurora PostgreSQL)

Each daily inventory row requires:

• hotel_id (16 bytes UUID)
• room_type_id (16 bytes UUID)
• stay_date (4 bytes DATE)
• total_rooms (2 bytes SMALLINT)
• reserved_rooms (2 bytes SMALLINT)
• version (4 bytes INT)
• Row header & index pointers $\approx 16\text{ bytes}$
• Total per Row: $\approx 60\text{ bytes}$.

$$\text{Raw Inventory Data Footprint} = 1.825 \times 10^9 \times 60\text{ bytes} \approx \mathbf{109.5\text{ GB}}$$

• Including B-Tree indexes on (hotel_id, stay_date) and (room_type_id, stay_date) ($1.8\times$ multiplier): $\text{Total Table Size with Indexes} = 109.5\text{ GB} \times 1.8 \approx \mathbf{197.1\text{ GB}}$ This entire active inventory matrix fits comfortably inside the RAM and NVMe storage of an Amazon Aurora PostgreSQL Multi-AZ cluster (db.r6g.8xlarge).

Traffic Volume & QPS Calculations

• Peak Search QPS: $\mathbf{50,000\text{ queries/sec}}$ ($99\%$ read traffic).
• Peak Hold QPS: $\mathbf{500\text{ holds/sec}}$ ($1\%$ write traffic).
• Daily Completed Reservations: $1,000,000\text{ bookings/day}$.
• Average Stay Duration: 3 nights.
• Inventory Rows Locked per Hold: $1\text{ room type} \times 3\text{ days} = 3\text{ rows}$.
• Peak Row Mutation Rate: $\text{Row Updates/sec} = 500\text{ holds/sec} \times 3\text{ rows} = \mathbf{1,500\text{ row updates/sec}}$

---

3. High-Level Architecture & AWS Component Mapping

The architecture strictly decouples the Search & Discovery Path (geospatial searching, faceted filtering, and calendar availability caching) from the Transactional Booking & Hold Path (ACID inventory updates, Step Functions saga coordination, and payment processing).

Synthesizing vector architecture diagram...

After the shared edge (Route 53, CloudFront, WAF, API Gateway), traffic splits into two paths that scale very differently. In the "Search & Availability Tier" panel (about 50,000 QPS), searches find hotels in OpenSearch by location and filters, then check room availability in Redis, never touching the booking database. In the "Transactional Booking Tier" panel (about 500 TPS), a booking starts a Step Functions saga: it decrements the room-night inventory in Aurora, records a 15-minute hold in DynamoDB (whose TTL releases it automatically if the saga dies), and takes payment. In the "Asynchronous Inventory Sync & Invalidation" panel, Aurora changes flow through CDC to EventBridge, which refreshes the Redis availability and sends the confirmation. Search may briefly show a room that was just taken, but the booking path is the only one that can actually take it, and it is strictly consistent.

Data Flow Walkthrough

1. Geospatial Hotel Search: The guest searches for hotels in "Manhattan, New York" for Oct 15–18. The request routes via CloudFront and WAF to the SearchSvc. The service queries Amazon OpenSearch Service using a geo-bounding box filter combined with price and amenity criteria.
2. Calendar Availability Fast-Check: The search service checks Amazon ElastiCache Redis where room availability bitmasks or hash sets for the requested date window are cached. Hotels with zero availability across any night are filtered out.
3. 15-Minute Temporary Hold: The guest selects a Deluxe King room at the Marriott Times Square and clicks "Reserve". The request reaches the BookingSvc with an Idempotency-Key.
4. Atomic Inventory Reservation: The booking service executes an atomic transaction in Amazon Aurora PostgreSQL:
   • Locks the 3 daily inventory rows (2026-10-15, 2026-10-16, 2026-10-17) in ascending date order using SELECT ... FOR UPDATE.
   • Validates that reserved_rooms + num_rooms <= total_rooms for all 3 dates.
   • Increments reserved_rooms by 1 on each row.
   • Creates a record in reservation_holds with status = 'HOLD' and expires_at = NOW() + INTERVAL '15 minutes'.
5. Step Functions 15-Minute Timer: The service launches an AWS Step Functions Express/Standard Workflow that waits for a payment callback token or fires after 15 minutes. Concurrently, a record is written to DynamoDB with a 15-minute TTL as a secondary safety guardrail.
6. Confirmation or Rollback:
   • Payment Succeeded: The client submits payment details. The payment gateway confirms the charge. Step Functions receives the callback, updates the Aurora hold to CONFIRMED, creates a confirmed_bookings entry, and emits an EventBridge event.
   • Payment Timeout: If 15 minutes elapse with no payment confirmation, Step Functions executes compensating transactions, decrementing reserved_rooms by 1 across the 3 dates and marking the hold EXPIRED.

Core Request Tracing Execution Walkthrough

---

4. API Interface Design & Data Contracts

1. Reserve Temporary Room Hold (POST /v1/reservations/hold)

Request Headers

httpCopy
POST /v1/reservations/hold HTTP/1.1
Host: booking.platform.aws.internal
Authorization: Bearer sec_jwt_token_991823
Idempotency-Key: idemp_hold_9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d
Content-Type: application/json

Request Payload

jsonCopy
{
  "hotel_id": "htl_marriott_times_square_ny",
  "room_type_id": "rt_deluxe_king_001",
  "check_in_date": "2026-10-15",
  "check_out_date": "2026-10-18",
  "num_rooms": 1,
  "guest_id": "usr_99812450",
  "currency": "USD"
}

Response: 201 Created

jsonCopy
{
  "hold_id": "hld_live_8819230491",
  "status": "HOLD",
  "hotel_id": "htl_marriott_times_square_ny",
  "room_type_id": "rt_deluxe_king_001",
  "check_in_date": "2026-10-15",
  "check_out_date": "2026-10-18",
  "num_rooms": 1,
  "total_price_cents": 89700,
  "currency": "USD",
  "expires_at": "2026-09-16T15:15:00.000Z",
  "time_to_expire_seconds": 900,
  "payment_task_token": "token_stepfuncs_live_99a8b7c6d5e4"
}

2. Confirm Permanent Booking (POST /v1/reservations/{hold_id}/confirm)

httpCopy
POST /v1/reservations/hld_live_8819230491/confirm HTTP/1.1
Host: booking.platform.aws.internal
Idempotency-Key: idemp_conf_8819230491
Content-Type: application/json

{
  "payment_method_token": "tok_visa_4242_pci",
  "guest_name": "Jane Doe",
  "guest_email": "jane.doe@example.com",
  "special_requests": "High floor, quiet room"
}

Response: 200 OK

jsonCopy
{
  "booking_id": "bkg_conf_9918237465",
  "confirmation_code": "MRTT-8849-NY",
  "status": "CONFIRMED",
  "hold_id": "hld_live_8819230491",
  "payment_reference": "ch_stripe_3N8vK2Lkd",
  "created_at": "2026-09-16T15:05:22.180Z"
}

---

5. Data Models & Storage Architecture

Scaling Beyond One Writer: Shard by hotel_id

A single Aurora writer comfortably absorbs the $1{,}500$ row updates/sec computed in Section 2, so the baseline design is one cluster. When write volume outgrows it, shard by hotel_id (consistent hashing, or a shard map keyed on hotel_id ranges). Every table above carries hotel_id, so a hold transaction, which only ever touches one hotel's inventory rows, its reservation_holds row and its confirmed_bookings row, stays inside one shard and keeps its single-transaction ACID guarantee. Sharding by stay_date would look natural but is wrong: a 3-night stay would span shards and turn the hold into a distributed transaction, and it concentrates all of next weekend's traffic on one shard. Cross-hotel queries (a guest's booking history) go to a separate read model fed by CDC, never to a fan-out over shards.

Relational PostgreSQL DDL Schema (Aurora Multi-AZ)

sqlCopy
-- 1. Hotels Master Table
CREATE TABLE hotels (
    hotel_id VARCHAR(64) PRIMARY KEY, -- e.g. "htl_marriott_times_square_ny"
    name VARCHAR(128) NOT NULL,
    latitude DECIMAL(9, 6) NOT NULL,
    longitude DECIMAL(9, 6) NOT NULL,
    timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York',
    status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Room Types Master Table
CREATE TABLE room_types (
    room_type_id VARCHAR(64) PRIMARY KEY, -- e.g. "rt_deluxe_king_001"
    hotel_id VARCHAR(64) NOT NULL REFERENCES hotels(hotel_id),
    name VARCHAR(64) NOT NULL,
    total_physical_capacity INT NOT NULL CHECK (total_physical_capacity > 0),
    base_price_cents INT NOT NULL CHECK (base_price_cents > 0),
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Core Daily Room Inventory Matrix Table (Partitioned by Stay Date Year)
CREATE TABLE room_inventory_daily (
    hotel_id VARCHAR(64) NOT NULL REFERENCES hotels(hotel_id),
    room_type_id VARCHAR(64) NOT NULL REFERENCES room_types(room_type_id),
    stay_date DATE NOT NULL,
    total_rooms SMALLINT NOT NULL CHECK (total_rooms >= 0),
    reserved_rooms SMALLINT NOT NULL DEFAULT 0 CHECK (reserved_rooms >= 0),
    version INT NOT NULL DEFAULT 1,
    PRIMARY KEY (hotel_id, room_type_id, stay_date),
    -- Ironclad Database Invariant: Reserved rooms can NEVER exceed total physical rooms
    CONSTRAINT chk_zero_overbooking CHECK (reserved_rooms <= total_rooms)
) PARTITION BY RANGE (stay_date);

-- Create Yearly Partitions for Fast Vacuuming & Sharding
CREATE TABLE room_inventory_2026 PARTITION OF room_inventory_daily
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE room_inventory_2027 PARTITION OF room_inventory_daily
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

-- 4. Temporary Reservation Holds Table
CREATE TABLE reservation_holds (
    hold_id VARCHAR(64) PRIMARY KEY, -- e.g. "hld_live_8819230491"
    idempotency_key VARCHAR(128) UNIQUE NOT NULL,
    hotel_id VARCHAR(64) NOT NULL REFERENCES hotels(hotel_id),
    room_type_id VARCHAR(64) NOT NULL REFERENCES room_types(room_type_id),
    guest_id VARCHAR(64) NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    num_rooms SMALLINT NOT NULL DEFAULT 1 CHECK (num_rooms > 0),
    total_price_cents BIGINT NOT NULL,
    status VARCHAR(16) NOT NULL CHECK (status IN ('HOLD', 'CONFIRMING', 'CONFIRMED', 'EXPIRED', 'CANCELLED')),
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_holds_expiry ON reservation_holds(expires_at) WHERE status = 'HOLD';

-- (Sharding note: see "Scaling Beyond One Writer" below this DDL.)

-- 5. Confirmed Bookings Table
CREATE TABLE confirmed_bookings (
    booking_id VARCHAR(64) PRIMARY KEY,
    hold_id VARCHAR(64) NOT NULL REFERENCES reservation_holds(hold_id),
    confirmation_code VARCHAR(32) UNIQUE NOT NULL,
    payment_reference VARCHAR(128) NOT NULL,
    guest_email VARCHAR(128) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'CONFIRMED',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

---