Design a Hotel Reservation System

1. Problem Statement & Scope Clarification

System Mission

Design a global hotel booking and room reservation platform (similar to Booking.com, Airbnb, and Marriott.com) capable of managing millions of hotel rooms across 500,000 properties worldwide, supporting multi-attribute geospatial search by dates/amenities, handling 15-minute temporary reservation holds, and strictly guaranteeing zero overbooking across overlapping date ranges.

Functional Requirements

1. Hotel Search & Availability Filter: Search available hotels by geographic city/bounding box, check-in/check-out dates, guest count, and price range within 200ms.
2. Temporary Room Hold (ReserveRoomHold): Temporarily lock a room type for a selected date range for 15 minutes while the guest completes payment.
3. Confirmed Booking (ConfirmBooking): Transition temporary hold to a permanent confirmed reservation upon successful payment capture.
4. Automated Hold Expiration: Automatically release held room inventory back to the general availability pool if payment is not completed within 15 minutes.
5. Zero Overbooking Invariant: Strict mathematical guarantee that no room type on any specific calendar date is ever booked beyond its total physical capacity.

Non-Functional Requirements (SLAs & SLOs)

• Inventory Consistency: Strict ACID serializability on inventory counters. Zero double-booking.
• Latency:
  • Availability Search: $P95 < 150\text{ ms}$, $P99 < 300\text{ ms}$.
  • Reservation Hold & Confirmation: $P99 < 100\text{ ms}$.
• Availability: $99.99\%$ uptime SLA globally.
• Scale: Support $50,000\text{ search QPS}$ and $500\text{ reservation holds/sec}$ at peak.

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

Scale & Inventory Matrix Sizing

• Total Registered Hotels: $500,000\text{ properties}$ worldwide.
• Average Room Types per Hotel: 5 room types (e.g., Deluxe King, Standard Queen, Executive Suite).
• Total Unique Room-Type Entities: $500,000 \times 5 = 2,500,000\text{ room types}$.
• Booking Window Horizon: 2 years forward ($730\text{ days}$).
• Total Inventory Matrix Rows: $\text{Inventory Rows} = 2,500,000\text{ room types} \times 730\text{ days} = \mathbf{1.825 \times 10^9\text{ rows (1.825 Billion Rows)}}$
• Row Size in PostgreSQL: hotel_id (16B) + room_type_id (16B) + date (4B) + total_rooms (2B) + reserved_rooms (2B) + Index overhead (20B) $\approx 60\text{ bytes}$.
• Total Inventory Storage Footprint: $\text{Inventory Database Size} = 1.825 \times 10^9 \times 60\text{ bytes} \approx \mathbf{109.5\text{ GB RAM / Disk}}$ This compact volume easily fits inside an Amazon Aurora PostgreSQL Multi-AZ Cluster with memory caching.

Traffic Volume

• Peak Search QPS: $50,000\text{ queries/sec}$ ($99\%$ read traffic).
• Peak Reservation Holds: $500\text{ holds/sec}$ ($1\%$ write traffic).
• Daily Completed Reservations: $1,000,000\text{ bookings/day}$.

3. High-Level Architecture & AWS Component Mapping

Interactive Architecture Diagram
100%Fit100%
Synthesizing vector architecture diagram...

4. API Interface Design & Wire Protocol

1. Temporary Reservation Hold API

httpCopy
POST /v1/reservations/hold
Host: booking.production.aws.internal
Idempotency-Key: 9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d
Content-Type: application/json

{
  "hotel_id": "htl_marriott_times_square",
  "room_type_id": "deluxe_king",
  "check_in_date": "2026-10-15",
  "check_out_date": "2026-10-18",
  "num_rooms": 1,
  "guest_id": "usr_998124"
}

Response: 201 Created
Content-Type: application/json

{
  "reservation_id": "res_71829384",
  "status": "HELD",
  "hold_expires_at_epoch": 1792070400,
  "hold_duration_seconds": 900,
  "total_price_cents": 89700,
  "currency": "USD"
}

5. Storage Engine & Daily Inventory Matrix Schema

1. Relational Inventory Table (Amazon Aurora PostgreSQL)

To eliminate race conditions across multi-night stays, inventory is modeled as one row per room type per calendar date:

sqlCopy
-- 1. Daily Inventory Matrix Table
CREATE TABLE room_type_daily_inventory (
    hotel_id VARCHAR(64) NOT NULL,
    room_type_id VARCHAR(64) NOT NULL,
    stay_date DATE NOT NULL,
    total_inventory SMALLINT NOT NULL,
    reserved_count SMALLINT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1,
    PRIMARY KEY (hotel_id, room_type_id, stay_date),
    CONSTRAINT chk_inventory_not_exceeded CHECK (reserved_count <= total_inventory),
    CONSTRAINT chk_reserved_non_negative CHECK (reserved_count >= 0)
);

-- 2. Reservations Master Table
CREATE TABLE reservations (
    reservation_id VARCHAR(64) PRIMARY KEY,
    idempotency_key VARCHAR(128) UNIQUE NOT NULL,
    hotel_id VARCHAR(64) NOT NULL,
    room_type_id VARCHAR(64) NOT NULL,
    guest_id VARCHAR(64) NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    num_rooms SMALLINT NOT NULL DEFAULT 1,
    status VARCHAR(16) NOT NULL, -- 'HELD', 'CONFIRMED', 'CANCELLED', 'EXPIRED'
    hold_expires_at TIMESTAMPTZ NOT NULL,
    total_amount_cents BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Atomic Multi-Date Hold SQL Query

To book a 3-night stay (Oct 15, 16, 17), the transaction locks all 3 consecutive date rows in chronological order:

sqlCopy
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 1. Lock All Date Rows in Date Order to Prevent Deadlocks
SELECT stay_date, total_inventory, reserved_count 
FROM room_type_daily_inventory
WHERE hotel_id = 'htl_marriott_times_square'
  AND room_type_id = 'deluxe_king'
  AND stay_date >= '2026-10-15' AND stay_date < '2026-10-18'
ORDER BY stay_date ASC
FOR UPDATE;

-- 2. Increment Reserved Count atomically (Will throw error if CHECK constraint fails)
UPDATE room_type_daily_inventory
SET reserved_count = reserved_count + 1, version = version + 1
WHERE hotel_id = 'htl_marriott_times_square'
  AND room_type_id = 'deluxe_king'
  AND stay_date >= '2026-10-15' AND stay_date < '2026-10-18';

-- 3. Insert Reservation Record in HELD state
INSERT INTO reservations (reservation_id, idempotency_key, hotel_id, room_type_id, guest_id, check_in, check_out, num_rooms, status, hold_expires_at, total_amount_cents)
VALUES ('res_71829384', 'idemp_key_1029', 'htl_marriott_times_square', 'deluxe_king', 'usr_998124', '2026-10-15', '2026-10-18', 1, 'HELD', NOW() + INTERVAL '15 minutes', 89700);

COMMIT;