1. Requirements & Scope (5 min)

Functional Requirements

  1. Search for hotels by location, dates, guests, and filters (price range, star rating, amenities) with availability-aware results
  2. View hotel details, room types, photos, reviews, and real-time pricing for selected dates
  3. Book a room with a hold-then-confirm workflow: hold inventory for 10 minutes while user enters payment, then confirm or release
  4. Prevent double-booking: two users cannot book the same room for overlapping dates, even under concurrent requests
  5. Support booking lifecycle: create, confirm, modify (change dates), cancel (with cancellation policy enforcement), and refund

Non-Functional Requirements

  • Availability: 99.99% for search, 99.999% for booking (losing a confirmed booking is catastrophic)
  • Latency: Search results < 500ms. Booking confirmation < 2 seconds (includes payment).
  • Consistency: Strong consistency for inventory. A room shown as available must actually be bookable. Overbooking must be prevented at the database level.
  • Scale: 500K hotels, 50M rooms globally, 100M searches/day, 1M bookings/day
  • Durability: Booking records and payment transactions must survive any failure. Zero data loss.

2. Estimation (3 min)

Traffic

  • Search: 100M/day = ~1,150 searches/sec average, 5× peak = ~5,750/sec
  • Hotel detail page views: 3× searches = ~3,450/sec average
  • Booking attempts: 1M/day = ~12 bookings/sec average, peak = ~60/sec
  • Booking holds created: 3× bookings (many abandoned) = ~36 holds/sec
  • Price lookups: combined with search and detail = ~10K/sec

Storage

  • Hotels: 500K × 5 KB = 2.5 GB
  • Room types: 500K hotels × 5 room types × 1 KB = 2.5 GB
  • Room inventory (per room per night): 50M rooms × 365 nights × 50 bytes = 913 GB (~1 TB)
  • Bookings: 1M/day × 365 days × 1 KB = 365 GB/year
  • Search index (Elasticsearch): hotel metadata + denormalized availability = ~50 GB

Pricing

  • Price varies by date, room type, demand, and channel
  • Rate table: 500K hotels × 5 room types × 365 days = ~900M rate entries
  • Each entry: ~30 bytes → 27 GB of rate data
  • Cached in Redis for fast lookup

Key Insight

This is an inventory management + search problem. The core challenge is maintaining a correct, real-time inventory count (rooms available per night) under concurrent booking pressure while also providing fast, filter-rich search results. The booking flow is a distributed transaction spanning inventory, payment, and confirmation.

3. API Design (3 min)

Search Hotels

GET /search?city=new_york
  &check_in=2024-03-15
  &check_out=2024-03-18
  &guests=2
  &rooms=1
  &price_min=100
  &price_max=300
  &star_rating=4,5
  &amenities=wifi,pool,parking
  &sort=price_asc
  &page=1
  &limit=20

Response 200: {
  "total_results": 342,
  "hotels": [
    {
      "hotel_id": "htl_456",
      "name": "Grand Hotel NYC",
      "star_rating": 4,
      "location": { "lat": 40.7580, "lng": -73.9855, "neighborhood": "Midtown" },
      "thumbnail_url": "...",
      "min_price_per_night": 189,
      "total_price": 567,               // 3 nights
      "currency": "USD",
      "review_score": 8.7,
      "review_count": 2340,
      "amenities": ["wifi", "pool", "gym"],
      "available_room_types": 3
    },
    ...
  ]
}

Hotel Details + Room Availability

GET /hotels/htl_456?check_in=2024-03-15&check_out=2024-03-18&guests=2

Response 200: {
  "hotel": { ... },                    // full hotel info, photos, description
  "room_types": [
    {
      "room_type_id": "rt_101",
      "name": "Deluxe King",
      "description": "...",
      "max_guests": 2,
      "amenities": ["king_bed", "city_view", "minibar"],
      "photos": ["..."],
      "available_count": 5,            // rooms available for ALL requested nights
      "pricing": {
        "per_night": [189, 199, 189],  // price per night (varies by date)
        "total": 577,
        "taxes": 72,
        "grand_total": 649,
        "currency": "USD"
      },
      "cancellation_policy": {
        "type": "free_cancellation",
        "deadline": "2024-03-14T18:00:00Z",
        "penalty_after_deadline": "100%"
      }
    },
    ...
  ]
}

Create Booking (Hold)

POST /bookings
Body: {
  "hotel_id": "htl_456",
  "room_type_id": "rt_101",
  "check_in": "2024-03-15",
  "check_out": "2024-03-18",
  "guests": [
    { "first_name": "Alice", "last_name": "Smith", "email": "[email protected]" }
  ],
  "special_requests": "High floor, late check-out",
  "idempotency_key": "booking-alice-march15-htl456"
}
Response 201: {
  "booking_id": "bk_789",
  "status": "held",
  "hold_expires_at": "2024-02-22T14:40:00Z",  // 10 minutes
  "total_price": 649,
  "payment_url": "/bookings/bk_789/pay"
}

Confirm Booking (with payment)

POST /bookings/bk_789/confirm
Body: {
  "payment_method_id": "pm_stripe_xyz",
  "billing_address": { ... }
}
Response 200: {
  "booking_id": "bk_789",
  "status": "confirmed",
  "confirmation_code": "NYC-HTL456-2024-789",
  "total_charged": 649
}

Cancel Booking

POST /bookings/bk_789/cancel
Response 200: {
  "booking_id": "bk_789",
  "status": "cancelled",
  "refund_amount": 649,                // full refund (before deadline)
  "refund_status": "processing"
}

Key Decisions

  • Hold-then-confirm two-phase booking prevents inventory being permanently locked by abandoned sessions
  • Idempotency key prevents duplicate bookings from retried requests
  • Per-night pricing exposed in API (prices vary by date, weekends more expensive)
  • Availability count returned so users can see “Only 2 rooms left!” urgency

4. Data Model (3 min)

Hotels (PostgreSQL)

Table: hotels
  hotel_id            | varchar(50) (PK)
  name                | varchar(255)
  description         | text
  star_rating         | int
  city                | varchar(100)
  country             | varchar(50)
  lat                 | float
  lng                 | float
  address             | text
  amenities           | text[]
  photos              | jsonb
  review_score        | float
  review_count        | int
  status              | enum('active', 'inactive')
  created_at          | timestamp

Room Types (PostgreSQL)

Table: room_types
  room_type_id        | varchar(50) (PK)
  hotel_id            | varchar(50) (FK)
  name                | varchar(100)
  description         | text
  max_guests          | int
  total_rooms         | int             -- total physical rooms of this type
  amenities           | text[]
  photos              | jsonb
  base_price          | decimal(10,2)

Index: (hotel_id)

Room Inventory (PostgreSQL — the critical table)

Table: room_inventory
  hotel_id            | varchar(50)
  room_type_id        | varchar(50)
  date                | date
  total_rooms         | int             -- total rooms of this type
  booked_rooms        | int DEFAULT 0   -- currently booked (confirmed)
  held_rooms          | int DEFAULT 0   -- temporarily held (pending payment)
  price               | decimal(10,2)   -- price for this specific date

  PRIMARY KEY (hotel_id, room_type_id, date)

-- Available rooms = total_rooms - booked_rooms - held_rooms
-- Constraint: booked_rooms + held_rooms <= total_rooms

-- This is the HOT table. Every booking reads and writes here.

Bookings (PostgreSQL)

Table: bookings
  booking_id          | varchar(50) (PK)
  hotel_id            | varchar(50)
  room_type_id        | varchar(50)
  check_in            | date
  check_out           | date
  status              | enum('held', 'confirmed', 'cancelled', 'completed', 'no_show')
  guest_name          | varchar(200)
  guest_email         | varchar(200)
  total_price         | decimal(10,2)
  currency            | varchar(3)
  hold_expires_at     | timestamp       -- NULL after confirmation
  payment_id          | varchar(100)    -- Stripe payment intent ID
  confirmation_code   | varchar(50)     -- unique, human-readable
  cancellation_policy | jsonb
  special_requests    | text
  idempotency_key     | varchar(255)    -- unique index
  created_at          | timestamp
  updated_at          | timestamp

Index: (idempotency_key) UNIQUE
Index: (hotel_id, check_in, check_out)
Index: (guest_email)
Index: (status, hold_expires_at)      -- for hold expiry cleanup

Payments (PostgreSQL)

Table: payments
  payment_id          | varchar(100) (PK)
  booking_id          | varchar(50) (FK)
  amount              | decimal(10,2)
  currency            | varchar(3)
  status              | enum('pending', 'charged', 'refunded', 'failed')
  provider            | varchar(50)     -- 'stripe', 'paypal'
  provider_ref        | varchar(200)
  created_at          | timestamp

Why PostgreSQL

  • ACID transactions — critical for inventory consistency. The booking flow is a multi-row transaction (update inventory + create booking + process payment).
  • Row-level locking — SELECT ... FOR UPDATE on room_inventory rows prevents double-booking.
  • Constraints — CHECK (booked_rooms + held_rooms <= total_rooms) as a database-level invariant.
  • Partitioning — room_inventory partitioned by date range (current month + next 12 months active, older archived).

5. High-Level Design (12 min)

Architecture

User
  → CDN (static content, images, hotel listings cache)
  → API Gateway → Search Service → Elasticsearch
                → Hotel Service → PostgreSQL (hotels, room_types)
                → Booking Service → PostgreSQL (inventory, bookings)
                → Pricing Service → Redis (rate cache)
                → Payment Service → Stripe/PayPal

Search Flow

1. User searches: "New York, Mar 15-18, 2 guests, $100-300"

2. Search Service:
   a. Query Elasticsearch:
      {
        "query": {
          "bool": {
            "must": [
              { "term": { "city": "new_york" } },
              { "range": { "star_rating": { "gte": 4 } } },
              { "terms": { "amenities": ["wifi", "pool"] } }
            ]
          }
        },
        "sort": [{ "min_price": "asc" }]
      }

   b. Elasticsearch returns candidate hotels (IDs + basic info)
      Elasticsearch has pre-indexed: hotel_id, city, star_rating, amenities,
      min_price, review_score, location (geo_point)

   c. Availability filter (critical step):
      For each candidate hotel, check room_inventory:
        SELECT room_type_id, MIN(total_rooms - booked_rooms - held_rooms) as avail
        FROM room_inventory
        WHERE hotel_id = ? AND date BETWEEN '2024-03-15' AND '2024-03-17'
        GROUP BY room_type_id
        HAVING MIN(total_rooms - booked_rooms - held_rooms) > 0;

      This finds room types where ALL nights have availability.
      Hotels with 0 available room types are filtered out.

   d. Price lookup (Redis):
      For each available hotel + room type, get per-night prices.
      Compute total price for the stay.

   e. Return sorted, paginated results with availability and price.

Booking Flow (Hold → Confirm)

Phase 1: HOLD (10-minute reservation)
═══════════════════════════════════════
1. POST /bookings { hotel, room_type, dates, idempotency_key }

2. Booking Service:
   a. Check idempotency_key → if exists, return existing booking
   b. BEGIN TRANSACTION:
      -- Lock inventory rows for the requested dates
      SELECT * FROM room_inventory
      WHERE hotel_id = ? AND room_type_id = ?
        AND date BETWEEN check_in AND check_out - 1
      FOR UPDATE;                            -- row-level lock

      -- Verify availability for ALL dates
      IF any row has (total_rooms - booked_rooms - held_rooms) < 1:
        ROLLBACK → return "sold out"

      -- Increment held_rooms for each date
      UPDATE room_inventory
      SET held_rooms = held_rooms + 1
      WHERE hotel_id = ? AND room_type_id = ?
        AND date BETWEEN check_in AND check_out - 1;

      -- Create booking record
      INSERT INTO bookings (booking_id, status, hold_expires_at, ...)
      VALUES (gen_id(), 'held', now() + interval '10 minutes', ...);

      COMMIT;
   c. Return booking_id + payment URL

Phase 2: CONFIRM (user submits payment)
═══════════════════════════════════════
1. POST /bookings/bk_789/confirm { payment_method }

2. Booking Service:
   a. Verify booking exists and status = 'held'
   b. Verify hold hasn't expired (hold_expires_at > now())
   c. Call Payment Service → charge card via Stripe
      → If payment fails: return error (hold remains, user can retry)
   d. BEGIN TRANSACTION:
      -- Move from held to booked
      UPDATE room_inventory
      SET held_rooms = held_rooms - 1,
          booked_rooms = booked_rooms + 1
      WHERE hotel_id = ? AND room_type_id = ?
        AND date BETWEEN check_in AND check_out - 1;

      UPDATE bookings SET status = 'confirmed', payment_id = ? WHERE booking_id = ?;
      COMMIT;
   e. Send confirmation email (async via Kafka)

Phase 3: HOLD EXPIRY (cleanup abandoned holds)
═══════════════════════════════════════
Background job every 30 seconds:
  SELECT * FROM bookings
  WHERE status = 'held' AND hold_expires_at < now()
  FOR UPDATE SKIP LOCKED;

  For each expired hold:
    BEGIN;
    UPDATE room_inventory SET held_rooms = held_rooms - 1
    WHERE hotel_id = ? AND room_type_id = ? AND date BETWEEN ...;
    UPDATE bookings SET status = 'expired' WHERE booking_id = ?;
    COMMIT;

Components

  1. Search Service: Handles search queries. Queries Elasticsearch for candidate hotels, PostgreSQL for real-time availability, Redis for prices. Caches popular searches.
  2. Hotel Service: CRUD for hotel metadata. Powers hotel detail pages. Syncs data to Elasticsearch.
  3. Booking Service: Core booking logic. Hold, confirm, cancel, modify. Manages inventory atomically.
  4. Pricing Service: Dynamic pricing engine. Base price × demand multiplier × season factor. Cached in Redis.
  5. Payment Service: Integrates with Stripe/PayPal. Handles charges, refunds, disputes.
  6. Hold Expiry Worker: Background job that releases expired holds. Runs every 30 seconds.
  7. Notification Service: Sends booking confirmation, reminder, and cancellation emails.
  8. Elasticsearch: Full-text search + geo queries + filtering. Updated via CDC from PostgreSQL.

6. Deep Dives (15 min)

Deep Dive 1: Preventing Double-Booking — Optimistic vs Pessimistic Locking

The core problem: Two users simultaneously try to book the last room for March 15.

Approach 1: Pessimistic Locking (SELECT FOR UPDATE)

-- User A and User B both execute this concurrently:
BEGIN;
SELECT * FROM room_inventory
WHERE hotel_id = 'htl_456' AND room_type_id = 'rt_101'
  AND date BETWEEN '2024-03-15' AND '2024-03-17'
FOR UPDATE;                              -- acquires row-level lock

-- User A gets the lock first, User B WAITS

-- User A checks: available = total - booked - held = 1
-- User A: UPDATE SET held_rooms = held_rooms + 1
-- User A: COMMIT → lock released

-- User B now gets the lock
-- User B checks: available = total - booked - held = 0 → SOLD OUT
-- User B: ROLLBACK

Pros: Simple, correct, PostgreSQL handles it natively Cons: Lock contention on hot rooms (popular hotels, peak dates). Lock wait = added latency for User B.

Approach 2: Optimistic Locking (version-based)

-- User A reads:
SELECT *, version FROM room_inventory
WHERE hotel_id = 'htl_456' AND room_type_id = 'rt_101'
  AND date = '2024-03-15';
-- Returns: { booked: 9, held: 0, total: 10, version: 42 }

-- User A updates:
UPDATE room_inventory
SET held_rooms = held_rooms + 1, version = version + 1
WHERE hotel_id = 'htl_456' AND room_type_id = 'rt_101'
  AND date = '2024-03-15'
  AND version = 42;                      -- only if version hasn't changed

-- If rows_affected = 1: success
-- If rows_affected = 0: someone else modified it → retry from SELECT

Pros: No lock held during the full transaction. Better throughput under contention. Cons: Retry logic needed. Under very high contention (flash sale), many retries → wasted work.

Approach 3: Database constraint (belt and suspenders)

ALTER TABLE room_inventory
ADD CONSTRAINT no_overbooking
CHECK (booked_rooms + held_rooms <= total_rooms);

Even if application logic has a bug, the database constraint prevents overbooking. Any UPDATE that would violate this check fails with an error.

Recommendation: Pessimistic locking (FOR UPDATE) for the booking flow — it’s simpler and the contention is low (even popular hotels have ~50 rooms, and bookings are infrequent per-room). Add the CHECK constraint as defense-in-depth.

Deep Dive 2: Inventory Availability in Search Results

The problem: Search returns 342 hotels. We need availability for each. Querying room_inventory for 342 hotels × 5 room types × 3 nights = 5,130 rows per search. At 5,750 searches/sec = 29.5M inventory lookups/sec. Too many direct DB queries.

Solution: Availability cache + eventual consistency

Architecture:
  PostgreSQL (room_inventory) → CDC stream → Availability Cache (Redis)

Redis cache structure:
  Key: avail:{hotel_id}:{room_type_id}:{date}
  Value: { "available": 5, "price": 189 }
  TTL: 60 seconds (refresh from DB periodically + on booking events)

Search flow (optimized):
  1. Elasticsearch returns 342 candidate hotels
  2. For each hotel, batch-query Redis:
     MGET avail:htl_1:rt_1:2024-03-15 avail:htl_1:rt_1:2024-03-16 ...
  3. Filter to hotels with availability > 0 for all requested nights
  4. Return results (cache hit rate > 95%)

Cache invalidation:
  On booking hold/confirm/cancel/expire:
    → Publish event to Kafka
    → Cache updater consumes event
    → Recalculate availability for affected (hotel, room_type, date) keys
    → Update Redis

    This adds ~1-2 second delay between a booking and the cache reflecting it.
    Acceptable: worst case, a user sees "1 room available" when it just became 0,
    clicks book, and gets "sold out" at the hold step (which checks DB directly).

Inventory pre-aggregation for search:

Instead of per-date availability, pre-aggregate:

Key: search_avail:{hotel_id}:{check_in}:{check_out}
Value: {
  "room_types": {
    "rt_101": { "min_available": 5, "total_price": 567 },
    "rt_102": { "min_available": 2, "total_price": 789 }
  }
}
TTL: 30 seconds

Pre-computed for popular date ranges (next 7 days × top 1000 cities).
Reduces per-hotel lookup from (rooms × nights) keys to 1 key.

Deep Dive 3: Pricing Engine

Dynamic pricing factors:

final_price = base_price × demand_multiplier × season_factor × day_of_week_factor
              - discount (if any)

1. Base price: set by hotel per room type (e.g., $150/night)

2. Demand multiplier (occupancy-based):
   occupancy = booked_rooms / total_rooms
   if occupancy < 50%: multiplier = 0.85 (discount to attract bookings)
   if occupancy 50-80%: multiplier = 1.0 (base price)
   if occupancy 80-90%: multiplier = 1.15
   if occupancy > 90%: multiplier = 1.35 (last few rooms are premium)

3. Season factor:
   Peak season (summer, holidays): 1.5×
   Shoulder season: 1.2×
   Off-season: 0.9×
   Configured per hotel/city with specific date ranges.

4. Day of week:
   Weekend (Fri-Sat): 1.2× for leisure destinations
   Weekday (Mon-Thu): 1.2× for business destinations
   Configurable per hotel.

5. Special events:
   Super Bowl week, major conferences, local festivals
   Event pricing overlay: 2-3× for affected dates

Price computation pipeline:

Nightly batch job (runs at 2 AM):
  For each (hotel, room_type, date) in the next 365 days:
    1. Fetch base_price
    2. Fetch current occupancy (from inventory)
    3. Look up season/day/event factors
    4. Compute final_price
    5. Write to rate table (PostgreSQL)
    6. Push to Redis cache

Real-time price adjustments:
  On booking (occupancy changes):
    → Recalculate prices for affected dates
    → Update Redis cache
    → Price change visible to next search within ~5 seconds

Displaying price consistency:

Problem: User sees $189/night in search results, clicks hotel detail,
sees $199/night. Price changed between the two requests.

Solution: Price snapshot with TTL
  Search result includes: price_snapshot_id = "ps_abc"
  Hotel detail page sends: price_snapshot_id to verify price
  If price changed:
    → Show both: "Was $189, now $199" (transparent)
    → Or: honor the snapshot price for 15 minutes (price guarantee)

  At booking hold time:
    → Always use LIVE price from rate table (not cache)
    → Display final price clearly before payment
    → If price increased since search: notify user explicitly

7. Extensions (2 min)

  • Multi-room bookings: Group bookings (10 rooms for a wedding). Requires atomic hold of multiple rooms across potentially different room types. Use a single transaction spanning all inventory rows. Show group pricing discounts.
  • Waitlist and price alerts: If a hotel is sold out for requested dates, let users join a waitlist. Notify when a cancellation frees up inventory. Also support price drop alerts: “Notify me if this room drops below $200.”
  • Channel manager integration: Hotels list on multiple OTAs (Booking.com, Expedia, direct). Each channel gets an allocation of rooms. Inventory updates from one channel must propagate to others in real-time to prevent cross-channel overbooking. This is the most complex operational challenge in hotel tech.
  • Loyalty and rewards: Points-based redemption alongside cash. Dynamic award pricing (more points needed on high-demand dates). Points + cash hybrid payments. Tier-based benefits (room upgrades, late checkout).
  • Reviews and trust: Verified reviews (only from confirmed guests). Photo reviews. Response from hotel. Review score impacts search ranking. Fraud detection for fake reviews (NLP analysis, behavioral patterns).