Design: Hotel Reservation System

April 2026 · 24 min read

Problem Statement

Design a hotel reservation system similar to Booking.com or Marriott's reservation platform. The system must let guests search for hotels, view real-time room availability, make reservations with payment, and handle cancellations — all while serving thousands of concurrent bookings without double-selling rooms.

Hotel reservation is one of the most demanding real-world system design problems because it combines inventory management (finite rooms), concurrency control (many users booking the same room type simultaneously), dynamic pricing (prices change with demand), and search (geo + date + filter queries at scale).

Requirements

Functional Requirements

• Search hotels by location, check-in/check-out dates, number of guests, and filters (price range, amenities, star rating)
• View room availability for a specific hotel and date range, including room types, photos, amenities, and pricing
• Reserve a room — select room type, enter guest details, and confirm booking
• Process payment — charge guest at booking time or at check-in (depending on policy)
• Cancel reservation — release inventory and process refund based on cancellation policy
• Hotel management portal — hotel admins can update room inventory, prices, availability, and policies
• Booking confirmation — email/SMS notification with reservation details and confirmation code

Non-Functional Requirements

• Scale: 5,000 hotels, ~1 million rooms total, averaging 200 rooms per hotel
• Consistency: No double-booking — a room that's fully booked must not accept more reservations (or overbooking must be intentional and controlled)
• Availability: 99.99% uptime — users must always be able to search and book
• Latency: Search results < 500ms, booking confirmation < 2 seconds
• Concurrency: Handle hundreds of simultaneous bookings for the same room type at popular hotels during peak season

Back-of-Envelope Estimation

High-Level Architecture

┌──────────┐      ┌──────────────┐      ┌──────────────────────────────────────┐
│  Mobile  │─────▶│              │      │         Service Layer                │
│   App    │      │   API        │      │                                      │
└──────────┘      │   Gateway    │─────▶│  ┌─────────────┐  ┌──────────────┐  │
                  │              │      │  │ Hotel       │  │ Search       │  │
┌──────────┐      │  (Auth,      │      │  │ Service     │  │ Service      │  │
│  Web     │─────▶│   Rate       │      │  │             │  │ (Elastic-    │  │
│  Client  │      │   Limiting,  │      │  └─────────────┘  │  search)     │  │
└──────────┘      │   Routing)   │      │                    └──────────────┘  │
                  │              │      │  ┌─────────────┐  ┌──────────────┐  │
┌──────────┐      │              │      │  │ Inventory   │  │ Booking      │  │
│  Hotel   │─────▶│              │      │  │ Service     │  │ Service      │  │
│  Admin   │      └──────────────┘      │  │             │  │              │  │
└──────────┘                            │  └─────────────┘  └──────────────┘  │
                                        │                                      │
                                        │  ┌─────────────┐  ┌──────────────┐  │
                                        │  │ Payment     │  │ Notification │  │
                                        │  │ Service     │  │ Service      │  │
                                        │  └─────────────┘  └──────────────┘  │
                                        └──────────────────────────────────────┘
                                                       │
                          ┌────────────────────────────┼────────────────────────┐
                          │                            │                        │
                    ┌─────▼──────┐            ┌───────▼────────┐      ┌───────▼────────┐
                    │ PostgreSQL │            │ Elasticsearch  │      │     Redis      │
                    │ (Inventory │            │ (Search Index) │      │   (Cache &     │
                    │  & Booking)│            │                │      │    Sessions)   │
                    └────────────┘            └────────────────┘      └────────────────┘

Service Responsibilities

Database Schema

The schema is the heart of a hotel reservation system. We model inventory at the room type + date granularity — not individual rooms — because guests book "a Deluxe room for 3 nights," not "Room 417."

Core Tables

-- ═══════════════════════════════════════════════════════════════
-- HOTEL & ROOM METADATA
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE hotels (
    hotel_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(255) NOT NULL,
    description     TEXT,
    address         TEXT NOT NULL,
    city            VARCHAR(100) NOT NULL,
    country         VARCHAR(100) NOT NULL,
    latitude        DECIMAL(10, 7) NOT NULL,
    longitude       DECIMAL(10, 7) NOT NULL,
    geohash         VARCHAR(12) NOT NULL,          -- precomputed for search
    star_rating     SMALLINT CHECK (star_rating BETWEEN 1 AND 5),
    review_score    DECIMAL(2, 1) DEFAULT 0.0,     -- avg guest rating
    review_count    INTEGER DEFAULT 0,
    amenities       JSONB DEFAULT '[]',            -- ["pool","wifi","gym",...]
    check_in_time   TIME DEFAULT '15:00',
    check_out_time  TIME DEFAULT '11:00',
    status          VARCHAR(20) DEFAULT 'active',  -- active, suspended, closed
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_hotels_geohash ON hotels (geohash);
CREATE INDEX idx_hotels_city ON hotels (city);
CREATE INDEX idx_hotels_amenities ON hotels USING gin (amenities);

CREATE TABLE room_types (
    room_type_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hotel_id        UUID NOT NULL REFERENCES hotels(hotel_id),
    name            VARCHAR(100) NOT NULL,         -- "Deluxe King", "Standard Twin"
    description     TEXT,
    max_occupancy   SMALLINT NOT NULL DEFAULT 2,
    bed_type        VARCHAR(50),                   -- "King", "Twin", "Queen"
    room_size_sqft  INTEGER,
    amenities       JSONB DEFAULT '[]',            -- ["minibar","balcony","ocean_view"]
    photos          JSONB DEFAULT '[]',            -- array of photo URLs
    base_price      DECIMAL(10, 2) NOT NULL,       -- default nightly rate
    currency        CHAR(3) DEFAULT 'USD',
    total_rooms     INTEGER NOT NULL,              -- total physical rooms of this type
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (hotel_id, name)
);

CREATE INDEX idx_room_types_hotel ON room_types (hotel_id);

The Room Type Inventory Table — The Core Model

This is the most critical table in the entire system. It tracks how many rooms of each type are available on each date:

-- ═══════════════════════════════════════════════════════════════
-- ROOM INVENTORY (per room_type per date)
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE room_type_inventory (
    hotel_id        UUID NOT NULL REFERENCES hotels(hotel_id),
    room_type_id    UUID NOT NULL REFERENCES room_types(room_type_id),
    date            DATE NOT NULL,
    total_rooms     SMALLINT NOT NULL,             -- total physical rooms of this type
    reserved_rooms  SMALLINT NOT NULL DEFAULT 0,   -- currently reserved count
    price           DECIMAL(10, 2) NOT NULL,       -- nightly rate for this date
    version         INTEGER NOT NULL DEFAULT 0,    -- optimistic lock version
    
    PRIMARY KEY (hotel_id, room_type_id, date),
    CHECK (reserved_rooms >= 0)
    -- NOTE: We intentionally do NOT enforce reserved_rooms <= total_rooms
    -- because overbooking up to 110% is an industry practice
);

CREATE INDEX idx_inventory_date ON room_type_inventory (date);
CREATE INDEX idx_inventory_hotel_date ON room_type_inventory (hotel_id, date);
CREATE INDEX idx_inventory_availability ON room_type_inventory (hotel_id, room_type_id, date)
    WHERE reserved_rooms < total_rooms;

Booking Tables

-- ═══════════════════════════════════════════════════════════════
-- RESERVATIONS
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE reservations (
    reservation_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hotel_id         UUID NOT NULL REFERENCES hotels(hotel_id),
    room_type_id     UUID NOT NULL REFERENCES room_types(room_type_id),
    guest_id         UUID NOT NULL,
    confirmation_code VARCHAR(12) NOT NULL UNIQUE,  -- human-readable "HRS-A3K9M2"
    check_in_date    DATE NOT NULL,
    check_out_date   DATE NOT NULL,
    num_guests       SMALLINT NOT NULL DEFAULT 1,
    num_rooms        SMALLINT NOT NULL DEFAULT 1,
    
    -- Pricing snapshot at booking time
    nightly_rate     DECIMAL(10, 2) NOT NULL,
    total_amount     DECIMAL(10, 2) NOT NULL,
    currency         CHAR(3) DEFAULT 'USD',
    
    -- Status management
    status           VARCHAR(20) NOT NULL DEFAULT 'pending',
    -- pending → confirmed → checked_in → checked_out
    -- pending → cancelled
    -- pending → expired (payment timeout)
    
    -- Cancellation
    cancellation_policy  VARCHAR(20),               -- free, moderate, strict
    cancelled_at         TIMESTAMPTZ,
    refund_amount        DECIMAL(10, 2),
    
    -- Payment
    payment_id       UUID,
    payment_status   VARCHAR(20) DEFAULT 'pending', -- pending, charged, refunded
    
    -- Timestamps
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW(),
    expires_at       TIMESTAMPTZ,                   -- payment deadline
    
    CHECK (check_out_date > check_in_date),
    CHECK (status IN ('pending','confirmed','checked_in','checked_out','cancelled','expired'))
);

CREATE INDEX idx_reservations_guest ON reservations (guest_id);
CREATE INDEX idx_reservations_hotel_dates ON reservations (hotel_id, check_in_date, check_out_date);
CREATE INDEX idx_reservations_status ON reservations (status) WHERE status IN ('pending', 'confirmed');
CREATE INDEX idx_reservations_expires ON reservations (expires_at) WHERE status = 'pending';

-- ═══════════════════════════════════════════════════════════════
-- GUESTS
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE guests (
    guest_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email            VARCHAR(255) NOT NULL UNIQUE,
    name             VARCHAR(255) NOT NULL,
    phone            VARCHAR(20),
    loyalty_tier     VARCHAR(20) DEFAULT 'standard', -- standard, silver, gold, platinum
    loyalty_points   INTEGER DEFAULT 0,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════
-- PAYMENTS
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE payments (
    payment_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_id   UUID NOT NULL REFERENCES reservations(reservation_id),
    amount           DECIMAL(10, 2) NOT NULL,
    currency         CHAR(3) DEFAULT 'USD',
    method           VARCHAR(20) NOT NULL,          -- credit_card, debit_card, paypal
    gateway_ref      VARCHAR(255),                  -- Stripe payment_intent ID
    status           VARCHAR(20) NOT NULL DEFAULT 'pending',
    -- pending → authorized → captured → refunded (partial or full)
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

Availability Check

To check if a room type is available for a date range, we query the room_type_inventory table for every date in the range and verify that reserved_rooms < total_rooms on all dates:

-- Check availability for "Deluxe King" at Hotel X, checking in May 10, out May 13
-- (Guest stays nights of May 10, 11, 12 — NOT May 13)

SELECT date, total_rooms, reserved_rooms,
       (total_rooms - reserved_rooms) AS available
FROM room_type_inventory
WHERE hotel_id = :hotel_id
  AND room_type_id = :room_type_id
  AND date >= '2026-05-10'       -- check-in date
  AND date <  '2026-05-13'       -- check-out date (exclusive)
  AND reserved_rooms < total_rooms
ORDER BY date;

-- If this returns exactly 3 rows, the room is available for all 3 nights.
-- If fewer rows, at least one night is sold out.

-- Compact version: count matching days
SELECT COUNT(*) AS available_nights
FROM room_type_inventory
WHERE hotel_id = :hotel_id
  AND room_type_id = :room_type_id
  AND date >= '2026-05-10'
  AND date <  '2026-05-13'
  AND reserved_rooms < total_rooms;

-- available_nights = 3 → room available
-- available_nights < 3 → at least one night sold out

Batch Availability for Search Results

When displaying search results, we need availability for all room types across many hotels simultaneously:

-- Get all available room types across hotels in a city for a date range
-- Used by the Search Service after geo-filtering

WITH date_range AS (
    SELECT generate_series('2026-05-10'::date, '2026-05-12'::date, '1 day') AS date
),
required_nights AS (
    SELECT COUNT(*) AS total FROM date_range
)
SELECT i.hotel_id, i.room_type_id, 
       MIN(i.total_rooms - i.reserved_rooms) AS min_available,
       AVG(i.price) AS avg_nightly_price,
       SUM(i.price) AS total_price
FROM room_type_inventory i
JOIN date_range d ON i.date = d.date
WHERE i.hotel_id IN (:filtered_hotel_ids)           -- pre-filtered by geo search
  AND i.reserved_rooms < i.total_rooms
GROUP BY i.hotel_id, i.room_type_id
HAVING COUNT(*) = (SELECT total FROM required_nights) -- available ALL nights
ORDER BY avg_nightly_price ASC;

Reservation Flow

The reservation process involves four critical steps, each requiring careful handling:

┌────────┐     ┌───────────┐     ┌───────────┐     ┌─────────┐     ┌──────────┐
│ Guest  │────▶│  Check    │────▶│  Reserve  │────▶│ Payment │────▶│ Confirm  │
│ selects│     │Availability│    │ Inventory │     │ Charge  │     │ Booking  │
│ room   │     │           │     │ (lock)    │     │         │     │          │
└────────┘     └───────────┘     └───────────┘     └─────────┘     └──────────┘
                                       │                                │
                                       │  if payment fails              │
                                       │◀──── RELEASE inventory ────────│
                                       │                                │
                                 ┌─────▼─────┐                   ┌─────▼─────┐
                                 │ Rollback  │                   │  Send     │
                                 │ reserved  │                   │ Email +   │
                                 │ count     │                   │ SMS       │
                                 └───────────┘                   └───────────┘

Step-by-Step Implementation

// Reservation Service — complete booking flow

async function createReservation(request: BookingRequest): Promise<Reservation> {
  const { hotelId, roomTypeId, checkIn, checkOut, guestId, numRooms } = request;
  
  // Step 1: Check availability (read-only, no locks)
  const nights = getDateRange(checkIn, checkOut);  // [May 10, May 11, May 12]
  const availability = await db.query(`
    SELECT date, total_rooms, reserved_rooms, version
    FROM room_type_inventory
    WHERE hotel_id = $1 AND room_type_id = $2
      AND date >= $3 AND date < $4
      AND reserved_rooms + $5 <= total_rooms
  `, [hotelId, roomTypeId, checkIn, checkOut, numRooms]);
  
  if (availability.rows.length !== nights.length) {
    throw new Error('ROOM_NOT_AVAILABLE');
  }
  
  // Step 2: Reserve inventory (optimistic locking — the critical section)
  const tx = await db.beginTransaction();
  try {
    for (const row of availability.rows) {
      const result = await tx.query(`
        UPDATE room_type_inventory
        SET reserved_rooms = reserved_rooms + $1,
            version = version + 1
        WHERE hotel_id = $2
          AND room_type_id = $3
          AND date = $4
          AND version = $5
          AND reserved_rooms + $1 <= total_rooms
      `, [numRooms, hotelId, roomTypeId, row.date, row.version]);
      
      if (result.rowCount === 0) {
        await tx.rollback();
        throw new Error('CONCURRENT_BOOKING_CONFLICT');
      }
    }
    
    // Step 3: Create reservation record
    const totalPrice = availability.rows.reduce(
      (sum, row) => sum + parseFloat(row.price) * numRooms, 0
    );
    const reservation = await tx.query(`
      INSERT INTO reservations 
        (hotel_id, room_type_id, guest_id, confirmation_code,
         check_in_date, check_out_date, num_rooms,
         nightly_rate, total_amount, status, expires_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'pending', NOW() + INTERVAL '10 minutes')
      RETURNING *
    `, [hotelId, roomTypeId, guestId, generateConfCode(),
        checkIn, checkOut, numRooms,
        availability.rows[0].price, totalPrice]);
    
    await tx.commit();
    
    // Step 4: Process payment (async, with timeout)
    try {
      const payment = await paymentService.charge({
        reservationId: reservation.reservation_id,
        amount: totalPrice,
        guestId: guestId,
      });
      
      // Step 5: Confirm booking
      await db.query(`
        UPDATE reservations
        SET status = 'confirmed', payment_id = $1, payment_status = 'charged'
        WHERE reservation_id = $2
      `, [payment.id, reservation.reservation_id]);
      
      // Step 6: Send confirmation
      await notificationService.sendBookingConfirmation(reservation);
      
      return reservation;
    } catch (paymentError) {
      // Payment failed — release inventory
      await releaseInventory(hotelId, roomTypeId, nights, numRooms);
      await db.query(`
        UPDATE reservations SET status = 'expired' WHERE reservation_id = $1
      `, [reservation.reservation_id]);
      throw new Error('PAYMENT_FAILED');
    }
    
  } catch (error) {
    await tx.rollback();
    throw error;
  }
}

Concurrency Control — Optimistic Locking

The biggest challenge in hotel reservation is preventing double-booking when multiple users try to reserve the same room type simultaneously. We use optimistic locking with a version column.

Why Optimistic Locking?

For our scale (~30 TPS booking), optimistic locking is ideal. Conflicts are rare enough that retries are negligible, but when they happen, the system correctly prevents double-booking.

How Optimistic Locking Works

-- The magic is in the WHERE clause:
-- We update ONLY IF the version hasn't changed since we read it

-- User A reads inventory:
-- { date: '2026-05-10', total: 20, reserved: 19, version: 42 }
-- → 1 room available

-- User A tries to reserve:
UPDATE room_type_inventory
SET reserved_rooms = reserved_rooms + 1,
    version = version + 1                   -- bump version
WHERE hotel_id = 'hotel-1'
  AND room_type_id = 'deluxe-king'
  AND date = '2026-05-10'
  AND version = 42                          -- version guard
  AND reserved_rooms + 1 <= total_rooms;   -- availability guard

-- Result: 1 row updated → SUCCESS
-- Now: { reserved: 20, version: 43 }

-- User B also read version 42, tries the same UPDATE:
UPDATE room_type_inventory
SET reserved_rooms = reserved_rooms + 1,
    version = version + 1
WHERE hotel_id = 'hotel-1'
  AND room_type_id = 'deluxe-king'
  AND date = '2026-05-10'
  AND version = 42                          -- STALE! version is now 43
  AND reserved_rooms + 1 <= total_rooms;   -- ALSO fails: 20+1 > 20

-- Result: 0 rows updated → CONFLICT → Retry or report "sold out"

Retry Strategy

async function reserveWithRetry(
  hotelId, roomTypeId, date, numRooms, maxRetries = 3
): Promise<boolean> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      // Re-read current version
      const row = await db.queryOne(`
        SELECT total_rooms, reserved_rooms, version
        FROM room_type_inventory
        WHERE hotel_id = $1 AND room_type_id = $2 AND date = $3
      `, [hotelId, roomTypeId, date]);
      
      if (row.reserved_rooms + numRooms > row.total_rooms) {
        return false; // genuinely sold out, no point retrying
      }
      
      // Attempt optimistic update
      const result = await db.query(`
        UPDATE room_type_inventory
        SET reserved_rooms = reserved_rooms + $1, version = version + 1
        WHERE hotel_id = $2 AND room_type_id = $3 AND date = $4
          AND version = $5
          AND reserved_rooms + $1 <= total_rooms
      `, [numRooms, hotelId, roomTypeId, date, row.version]);
      
      if (result.rowCount > 0) return true; // success!
      
      // Conflict — wait with exponential backoff, then retry
      await sleep(50 * Math.pow(2, attempt)); // 100ms, 200ms, 400ms
    } catch (error) {
      if (attempt === maxRetries) throw error;
    }
  }
  return false; // exhausted retries
}

Animation: Reservation Flow with Optimistic Lock

Watch two users compete for the last available room. User A succeeds because it commits first; User B's optimistic lock check fails because the version has changed.

Overbooking Strategy

Overbooking is an industry-standard practice. Hotels intentionally accept more reservations than they have physical rooms, because a percentage of guests always cancel or no-show. The math is straightforward:

-- Typical hotel no-show/cancellation rates:
-- Last-minute cancellations:  8-12%
-- No-shows:                   3-5%
-- Early departures:           2-3%
-- Combined "shrinkage":       ~10-15%
--
-- If a hotel has 100 Deluxe rooms and allows 110% overbooking:
-- total_rooms = 100 (physical)
-- overbooking_limit = 110 (logical maximum)
-- reserved_rooms can go up to 110

-- Implementation: modify the inventory constraint
UPDATE room_type_inventory
SET reserved_rooms = reserved_rooms + 1,
    version = version + 1
WHERE hotel_id = $1
  AND room_type_id = $2
  AND date = $3
  AND version = $4
  AND reserved_rooms + 1 <= CEIL(total_rooms * 1.10);  -- 110% limit

Configurable Overbooking

CREATE TABLE overbooking_policy (
    hotel_id         UUID NOT NULL REFERENCES hotels(hotel_id),
    room_type_id     UUID NOT NULL REFERENCES room_types(room_type_id),
    overbooking_pct  DECIMAL(4, 2) NOT NULL DEFAULT 10.00,  -- 10% = allow 110% booking
    max_overbook     SMALLINT,                              -- absolute cap
    season           VARCHAR(20) DEFAULT 'default',         -- default, peak, holiday
    effective_from   DATE NOT NULL,
    effective_to     DATE,
    PRIMARY KEY (hotel_id, room_type_id, season, effective_from)
);

-- Peak season: reduce overbooking (more guests actually show up)
-- Off-season: increase overbooking (more cancellations)
INSERT INTO overbooking_policy VALUES
  ('hotel-1', 'deluxe-king', 10.00, NULL, 'default',  '2026-01-01', NULL),
  ('hotel-1', 'deluxe-king',  5.00, NULL, 'peak',     '2026-06-15', '2026-09-01'),
  ('hotel-1', 'deluxe-king', 15.00, NULL, 'offseason', '2026-11-01', '2026-02-28');

When Overbooking Goes Wrong — The "Walked Guest"

When more guests show up than rooms available, the hotel must "walk" a guest — relocate them to another hotel. The standard compensation:

• Free upgrade at the same hotel (if a higher room type is available)
• Room at a comparable or better nearby hotel — hotel pays the difference
• Transportation to the alternative hotel
• Complimentary night on a future stay or loyalty points
• Full refund for the walked night

The cost of walking a guest (~$200-500) is far less than the revenue lost from empty rooms due to no-shows. With 100 rooms at $150/night, a 10% overbooking captures ~$1,500/night in revenue that would otherwise be lost.

Hotel Search System

Search is the highest-traffic operation (~3,000 QPS peak). Users search by location + dates + filters, and they expect fast, relevant results.

Geohash-Based Location Search

We use geohash to convert geographic coordinates into searchable strings. Hotels near each other share the same geohash prefix:

// Geohash precision levels:
// Length 1: ~5,000km × 5,000km  (continent)
// Length 2: ~1,250km × 625km    (country region)
// Length 3: ~156km × 156km      (state/province)
// Length 4: ~39km × 19.5km      (city)
// Length 5: ~4.9km × 4.9km      (neighborhood)
// Length 6: ~1.2km × 0.6km      (few blocks)

// Example: Times Square, NYC
// lat: 40.7580, lon: -73.9855
// geohash: "dr5ru6" (precision 6)

// Hotels within ~5km of Times Square:
// All have geohash prefix "dr5ru" (precision 5)

// Elasticsearch query for geo search:
{
  "query": {
    "bool": {
      "must": [
        {
          "geo_distance": {
            "distance": "5km",
            "location": { "lat": 40.758, "lon": -73.985 }
          }
        }
      ]
    }
  }
}

Elasticsearch Index Design

// Hotel search index — denormalized for fast queries
PUT /hotels
{
  "mappings": {
    "properties": {
      "hotel_id":      { "type": "keyword" },
      "name":          { "type": "text", "analyzer": "standard",
                         "fields": { "keyword": { "type": "keyword" } } },
      "city":          { "type": "keyword" },
      "country":       { "type": "keyword" },
      "location":      { "type": "geo_point" },       // lat/lon for geo queries
      "geohash":       { "type": "keyword" },
      "star_rating":   { "type": "integer" },
      "review_score":  { "type": "float" },
      "review_count":  { "type": "integer" },
      "amenities":     { "type": "keyword" },          // ["pool","wifi","gym"]
      "room_types": {
        "type": "nested",
        "properties": {
          "room_type_id":   { "type": "keyword" },
          "name":           { "type": "text" },
          "max_occupancy":  { "type": "integer" },
          "base_price":     { "type": "float" },
          "bed_type":       { "type": "keyword" }
        }
      },
      "min_price":     { "type": "float" },            // cheapest room type
      "max_price":     { "type": "float" },            // most expensive
      "popularity":    { "type": "float" },            // booking count / ranking signal
      "last_updated":  { "type": "date" }
    }
  }
}

Full Search Query Example

// Search: Hotels in NYC, May 10-13, 2 guests, under $300, with pool, 4+ stars
POST /hotels/_search
{
  "query": {
    "bool": {
      "must": [
        { "geo_distance": { "distance": "10km",
            "location": { "lat": 40.758, "lon": -73.985 } } },
        { "range": { "min_price": { "lte": 300 } } },
        { "range": { "star_rating": { "gte": 4 } } },
        { "term": { "amenities": "pool" } },
        { "nested": {
            "path": "room_types",
            "query": {
              "range": { "room_types.max_occupancy": { "gte": 2 } }
            }
        }}
      ]
    }
  },
  "sort": [
    { "_geo_distance": { "location": { "lat": 40.758, "lon": -73.985 },
        "order": "asc", "unit": "km" } },
    { "review_score": { "order": "desc" } },
    { "popularity": { "order": "desc" } }
  ],
  "size": 20
}

Search Pipeline Architecture

Search doesn't hit the inventory database directly. Instead, it's a multi-stage pipeline:

┌──────────────────────────────────────────────────────────────────────────┐
│                        SEARCH PIPELINE                                   │
│                                                                          │
│  ① Parse Query                                                           │
│  "NYC, May 10-13, 2 guests, pool, <$300"                               │
│           │                                                              │
│           ▼                                                              │
│  ② Geohash Filter                     ┌──────────────────────────┐       │
│  Find all hotels within radius ──────▶│ Elasticsearch geo_query  │       │
│  Result: 200 hotels near NYC           └──────────────────────────┘       │
│           │                                                              │
│           ▼                                                              │
│  ③ Apply Filters                                                         │
│  star_rating ≥ 4, amenities has "pool", occupancy ≥ 2, price ≤ $300     │
│  Result: 45 hotels match                                                 │
│           │                                                              │
│           ▼                                                              │
│  ④ Date Availability Check             ┌──────────────────────────┐       │
│  Check room_type_inventory ───────────▶│ PostgreSQL / Redis cache │       │
│  for May 10, 11, 12                    └──────────────────────────┘       │
│  Result: 38 hotels have available rooms                                  │
│           │                                                              │
│           ▼                                                              │
│  ⑤ Rank Results                                                          │
│  Score = 0.3 × relevance + 0.25 × review_score + 0.2 × price_value     │
│        + 0.15 × popularity + 0.1 × distance                             │
│           │                                                              │
│           ▼                                                              │
│  ⑥ Return Top 20 Results with prices                                     │
└──────────────────────────────────────────────────────────────────────────┘

Animation: Hotel Search Pipeline

Step through the search pipeline: from user query to ranked results. Each stage progressively narrows the result set.

Caching Strategy

Different data has vastly different caching characteristics:

Cache-Aside Pattern for Hotel Details

async function getHotelDetails(hotelId: string): Promise<Hotel> {
  const cacheKey = `hotel:${hotelId}`;
  
  // Try cache first
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
  
  // Cache miss — fetch from DB
  const hotel = await db.queryOne(
    'SELECT * FROM hotels WHERE hotel_id = $1', [hotelId]
  );
  
  // Store in cache with 24-hour TTL
  await redis.setex(cacheKey, 86400, JSON.stringify(hotel));
  
  return hotel;
}

// Invalidate on update (event-driven)
async function onHotelUpdated(event: HotelUpdatedEvent) {
  await redis.del(`hotel:${event.hotelId}`);
  // Also invalidate Elasticsearch index
  await elasticsearch.update('hotels', event.hotelId, event.changes);
}

Availability Caching for Search

// For search results, we cache approximate availability with a short TTL.
// The booking flow always checks the DB directly (not cache) for accuracy.

async function getCachedAvailability(
  hotelId: string, roomTypeId: string, dates: string[]
): Promise<number | null> {
  const keys = dates.map(d => `avail:${hotelId}:${roomTypeId}:${d}`);
  const values = await redis.mget(keys);
  
  // If any date is missing from cache, return null (cache miss)
  if (values.some(v => v === null)) return null;
  
  // Return minimum available rooms across all dates
  return Math.min(...values.map(Number));
}

// Update cache after every booking (write-through)
async function onInventoryChanged(event: InventoryChangedEvent) {
  const key = `avail:${event.hotelId}:${event.roomTypeId}:${event.date}`;
  const available = event.totalRooms - event.reservedRooms;
  await redis.setex(key, 30, available.toString()); // 30-second TTL
}

Dynamic Pricing

Hotel room prices are not fixed — they change based on multiple demand signals. This is a critical revenue optimization feature.

Pricing Factors

┌─────────────────────────────────────────────────────────────────────────┐
│                    DYNAMIC PRICING FACTORS                               │
│                                                                         │
│  ┌─────────────────┐  ┌──────────────────┐  ┌───────────────────────┐  │
│  │   DEMAND         │  │   TEMPORAL        │  │   EXTERNAL            │  │
│  │                  │  │                   │  │                       │  │
│  │ • Occupancy %    │  │ • Day of week     │  │ • Competitor prices   │  │
│  │ • Booking pace   │  │ • Season          │  │ • Local events        │  │
│  │ • Search volume  │  │ • Holidays         │  │ • Flights to city    │  │
│  │ • Cancellation   │  │ • Lead time       │  │ • Weather forecast    │  │
│  │   rate           │  │   (days until      │  │ • Convention dates    │  │
│  │ • Historical     │  │    check-in)       │  │                       │  │
│  │   patterns       │  │ • Time of day     │  │                       │  │
│  └─────────────────┘  └──────────────────┘  └───────────────────────┘  │
│                                                                         │
│                    ┌──────────────────────────┐                          │
│                    │   PRICING ENGINE          │                          │
│                    │                          │                          │
│                    │   base_price             │                          │
│                    │   × demand_multiplier    │                          │
│                    │   × seasonal_factor      │                          │
│                    │   × lead_time_factor     │                          │
│                    │   × event_surge          │                          │
│                    │   = final_price          │                          │
│                    └──────────────────────────┘                          │
└─────────────────────────────────────────────────────────────────────────┘

Pricing Algorithm

interface PricingContext {
  basePrice: number;
  occupancyRate: number;        // 0.0 - 1.0
  dayOfWeek: number;            // 0=Sun, 6=Sat
  daysUntilCheckin: number;
  season: 'peak' | 'shoulder' | 'off';
  localEvents: Event[];         // conferences, concerts, sports
  competitorAvgPrice: number;
}

function calculateDynamicPrice(ctx: PricingContext): number {
  let price = ctx.basePrice;
  
  // 1. Demand multiplier (based on current occupancy)
  //    More rooms booked → higher price for remaining
  const demandMultiplier = getDemandMultiplier(ctx.occupancyRate);
  price *= demandMultiplier;
  // occupancy 0-50%: 0.85-1.0   (discount to attract bookings)
  // occupancy 50-80%: 1.0-1.3   (standard to moderate markup)
  // occupancy 80-95%: 1.3-1.8   (significant markup)
  // occupancy 95-100%: 1.8-2.5  (premium for last rooms)
  
  // 2. Seasonal factor
  const seasonalFactor = {
    'peak':     1.30,  // summer, holidays: +30%
    'shoulder': 1.00,  // spring/fall: base rate
    'off':      0.75,  // winter off-peak: -25%
  }[ctx.season];
  price *= seasonalFactor;
  
  // 3. Day-of-week factor
  const dowFactor = [0.90, 0.85, 0.85, 0.90, 1.05, 1.20, 1.15];
  //                  Sun   Mon   Tue   Wed   Thu   Fri   Sat
  price *= dowFactor[ctx.dayOfWeek];
  
  // 4. Lead time factor (last-minute bookings cost more)
  const leadTimeFactor = getLeadTimeFactor(ctx.daysUntilCheckin);
  price *= leadTimeFactor;
  // 30+ days: 0.90    (early bird discount)
  // 14-30 days: 1.00  (standard)
  // 7-14 days: 1.10   (slight premium)
  // 1-7 days: 1.25    (last minute premium)
  // 0 days: 1.40      (same-day surge)
  
  // 5. Event surge
  if (ctx.localEvents.length > 0) {
    const maxEventSurge = Math.max(
      ...ctx.localEvents.map(e => e.priceSurge)
    );
    price *= (1 + maxEventSurge); // e.g., CES in Vegas → +50% surge
  }
  
  // 6. Competitive floor/ceiling
  const competitorFloor = ctx.competitorAvgPrice * 0.85;
  const competitorCeiling = ctx.competitorAvgPrice * 1.30;
  price = Math.max(price, competitorFloor);
  price = Math.min(price, competitorCeiling);
  
  // 7. Apply minimum and maximum bounds
  const minPrice = ctx.basePrice * 0.50;  // never below 50% of base
  const maxPrice = ctx.basePrice * 3.00;  // never above 300% of base
  price = Math.max(price, minPrice);
  price = Math.min(price, maxPrice);
  
  return Math.round(price * 100) / 100; // round to cents
}

function getDemandMultiplier(occupancy: number): number {
  if (occupancy <= 0.50) return 0.85 + (occupancy / 0.50) * 0.15;
  if (occupancy <= 0.80) return 1.0 + ((occupancy - 0.50) / 0.30) * 0.30;
  if (occupancy <= 0.95) return 1.30 + ((occupancy - 0.80) / 0.15) * 0.50;
  return 1.80 + ((occupancy - 0.95) / 0.05) * 0.70;
}

Price Update Pipeline

// Batch job runs every 15 minutes (or on demand triggers)
async function updatePrices(hotelId: string): Promise<void> {
  const roomTypes = await db.query(
    'SELECT * FROM room_types WHERE hotel_id = $1', [hotelId]
  );
  
  for (const roomType of roomTypes.rows) {
    // Get inventory for next 90 days
    const inventory = await db.query(`
      SELECT date, total_rooms, reserved_rooms, price
      FROM room_type_inventory
      WHERE hotel_id = $1 AND room_type_id = $2
        AND date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '90 days'
    `, [hotelId, roomType.room_type_id]);
    
    for (const day of inventory.rows) {
      const occupancy = day.reserved_rooms / day.total_rooms;
      const ctx: PricingContext = {
        basePrice: roomType.base_price,
        occupancyRate: occupancy,
        dayOfWeek: new Date(day.date).getDay(),
        daysUntilCheckin: daysBetween(new Date(), day.date),
        season: getSeason(day.date),
        localEvents: await getLocalEvents(hotelId, day.date),
        competitorAvgPrice: await getCompetitorPrice(hotelId, roomType, day.date),
      };
      
      const newPrice = calculateDynamicPrice(ctx);
      
      if (Math.abs(newPrice - day.price) > 0.01) {
        await db.query(`
          UPDATE room_type_inventory
          SET price = $1 WHERE hotel_id = $2 AND room_type_id = $3 AND date = $4
        `, [newPrice, hotelId, roomType.room_type_id, day.date]);
      }
    }
  }
  
  // Invalidate price caches
  await redis.del(`prices:${hotelId}:*`);
}

Cancellation & Refund

Cancellation must atomically release inventory and process the refund based on the hotel's cancellation policy.

Cancellation Policies

Cancellation Implementation

async function cancelReservation(
  reservationId: string, guestId: string
): Promise<CancellationResult> {
  const reservation = await db.queryOne(`
    SELECT * FROM reservations WHERE reservation_id = $1 AND guest_id = $2
  `, [reservationId, guestId]);
  
  if (!reservation) throw new Error('RESERVATION_NOT_FOUND');
  if (['cancelled', 'checked_in', 'checked_out'].includes(reservation.status)) {
    throw new Error('CANNOT_CANCEL');
  }
  
  // Calculate refund based on cancellation policy
  const daysUntilCheckin = daysBetween(new Date(), reservation.check_in_date);
  const refund = calculateRefund(reservation, daysUntilCheckin);
  
  const tx = await db.beginTransaction();
  try {
    // 1. Release inventory for all nights
    const nights = getDateRange(reservation.check_in_date, reservation.check_out_date);
    for (const date of nights) {
      await tx.query(`
        UPDATE room_type_inventory
        SET reserved_rooms = reserved_rooms - $1,
            version = version + 1
        WHERE hotel_id = $2 AND room_type_id = $3 AND date = $4
      `, [reservation.num_rooms, reservation.hotel_id, 
          reservation.room_type_id, date]);
    }
    
    // 2. Update reservation status
    await tx.query(`
      UPDATE reservations
      SET status = 'cancelled', cancelled_at = NOW(),
          refund_amount = $1, updated_at = NOW()
      WHERE reservation_id = $2
    `, [refund.amount, reservationId]);
    
    await tx.commit();
    
    // 3. Process refund (async)
    if (refund.amount > 0) {
      await paymentService.refund({
        paymentId: reservation.payment_id,
        amount: refund.amount,
        reason: 'guest_cancellation',
      });
    }
    
    // 4. Send cancellation confirmation
    await notificationService.sendCancellationConfirmation(reservation, refund);
    
    return { refundAmount: refund.amount, refundPercent: refund.percent };
  } catch (error) {
    await tx.rollback();
    throw error;
  }
}

function calculateRefund(
  reservation: Reservation, daysUntilCheckin: number
): { amount: number; percent: number } {
  const policy = reservation.cancellation_policy;
  const total = reservation.total_amount;
  
  switch (policy) {
    case 'free':
      if (daysUntilCheckin >= 1) return { amount: total, percent: 100 };
      return { amount: total - reservation.nightly_rate, percent: 
        Math.round((1 - reservation.nightly_rate / total) * 100) };
    
    case 'moderate':
      if (daysUntilCheckin >= 5) return { amount: total, percent: 100 };
      return { amount: total * 0.50, percent: 50 };
    
    case 'strict':
      if (daysUntilCheckin >= 14) return { amount: total, percent: 100 };
      return { amount: 0, percent: 0 };
    
    default:
      return { amount: total, percent: 100 }; // default: full refund
  }
}

Handling Expired Reservations

When a guest starts a booking but doesn't complete payment, the reservation enters "pending" status with a 10-minute expiry. A background job cleans up expired reservations:

// Runs every minute
async function cleanupExpiredReservations(): Promise<void> {
  const expired = await db.query(`
    SELECT reservation_id, hotel_id, room_type_id, 
           check_in_date, check_out_date, num_rooms
    FROM reservations
    WHERE status = 'pending' AND expires_at < NOW()
    FOR UPDATE SKIP LOCKED            -- avoid blocking other cleanup workers
  `);
  
  for (const res of expired.rows) {
    const tx = await db.beginTransaction();
    try {
      // Release inventory
      const nights = getDateRange(res.check_in_date, res.check_out_date);
      for (const date of nights) {
        await tx.query(`
          UPDATE room_type_inventory
          SET reserved_rooms = GREATEST(reserved_rooms - $1, 0),
              version = version + 1
          WHERE hotel_id = $2 AND room_type_id = $3 AND date = $4
        `, [res.num_rooms, res.hotel_id, res.room_type_id, date]);
      }
      
      await tx.query(`
        UPDATE reservations SET status = 'expired', updated_at = NOW()
        WHERE reservation_id = $1
      `, [res.reservation_id]);
      
      await tx.commit();
      logger.info(`Expired reservation ${res.reservation_id} — inventory released`);
    } catch (error) {
      await tx.rollback();
      logger.error(`Failed to expire reservation ${res.reservation_id}`, error);
    }
  }
}

Search Indexing Strategy

Keeping Elasticsearch in sync with PostgreSQL is critical. We use a CDC (Change Data Capture) approach:

┌─────────────┐     ┌──────────┐     ┌──────────────┐     ┌───────────────┐
│ PostgreSQL  │────▶│ Debezium │────▶│    Kafka     │────▶│ Elasticsearch │
│ (source)    │     │  (CDC)   │     │  (events)    │     │   Indexer     │
└─────────────┘     └──────────┘     └──────────────┘     └───────────────┘
                                                                  │
                                                           ┌──────▼──────┐
                                                           │Elasticsearch│
                                                           │   Index     │
                                                           └─────────────┘

Events That Trigger Re-Indexing

• Hotel updated → re-index hotel document (name, amenities, rating)
• Room type added/updated → re-index nested room_types in hotel document
• Price changed → update min_price/max_price fields
• Review submitted → update review_score and review_count
• Booking completed → increment popularity score

// Kafka consumer for search index updates
class SearchIndexer {
  async onHotelChanged(event: HotelChangedEvent) {
    const hotel = await db.queryOne(
      'SELECT * FROM hotels WHERE hotel_id = $1', [event.hotelId]
    );
    const roomTypes = await db.query(
      'SELECT * FROM room_types WHERE hotel_id = $1', [event.hotelId]
    );
    
    await elasticsearch.index('hotels', event.hotelId, {
      hotel_id: hotel.hotel_id,
      name: hotel.name,
      city: hotel.city,
      country: hotel.country,
      location: { lat: hotel.latitude, lon: hotel.longitude },
      geohash: hotel.geohash,
      star_rating: hotel.star_rating,
      review_score: hotel.review_score,
      review_count: hotel.review_count,
      amenities: hotel.amenities,
      room_types: roomTypes.rows.map(rt => ({
        room_type_id: rt.room_type_id,
        name: rt.name,
        max_occupancy: rt.max_occupancy,
        base_price: rt.base_price,
        bed_type: rt.bed_type,
      })),
      min_price: Math.min(...roomTypes.rows.map(r => r.base_price)),
      max_price: Math.max(...roomTypes.rows.map(r => r.base_price)),
      popularity: await getPopularityScore(event.hotelId),
      last_updated: new Date().toISOString(),
    });
  }
}

API Design

Key Endpoints

// ═══════════════════════════════════════════════════════════════
// SEARCH
// ═══════════════════════════════════════════════════════════════

GET /api/v1/hotels/search
  ?location=40.758,-73.985    // lat,lon
  &radius=10km
  &check_in=2026-05-10
  &check_out=2026-05-13
  &guests=2
  &min_price=100
  &max_price=300
  &amenities=pool,wifi
  &star_rating=4
  &sort=relevance              // relevance, price_asc, price_desc, rating
  &page=1
  &limit=20

Response: {
  "results": [
    {
      "hotel_id": "...",
      "name": "Grand Hyatt NYC",
      "star_rating": 5,
      "review_score": 8.7,
      "distance_km": 0.3,
      "cheapest_room": {
        "room_type_id": "...",
        "name": "Standard King",
        "total_price": 597.00,
        "avg_nightly": 199.00
      },
      "thumbnail": "https://cdn.example.com/hotels/..."
    },
    ...
  ],
  "total": 38,
  "page": 1,
  "pages": 2
}

// ═══════════════════════════════════════════════════════════════
// HOTEL DETAILS & AVAILABILITY
// ═══════════════════════════════════════════════════════════════

GET /api/v1/hotels/:hotelId
  ?check_in=2026-05-10
  &check_out=2026-05-13

Response: {
  "hotel": { ... },            // full hotel details, photos, amenities
  "room_types": [
    {
      "room_type_id": "...",
      "name": "Deluxe King",
      "available_rooms": 5,
      "nightly_prices": [199, 219, 249],
      "total_price": 667,
      "amenities": ["minibar", "ocean_view"],
      "photos": [...]
    },
    ...
  ]
}

// ═══════════════════════════════════════════════════════════════
// BOOKING
// ═══════════════════════════════════════════════════════════════

POST /api/v1/reservations
Body: {
  "hotel_id": "...",
  "room_type_id": "...",
  "check_in": "2026-05-10",
  "check_out": "2026-05-13",
  "num_rooms": 1,
  "num_guests": 2,
  "guest": {
    "name": "Alice Johnson",
    "email": "alice@example.com",
    "phone": "+1-555-0123"
  },
  "payment": {
    "method": "credit_card",
    "token": "tok_visa_4242"    // tokenized by client-side Stripe.js
  },
  "special_requests": "Late check-in"
}

Response: {
  "reservation_id": "...",
  "confirmation_code": "HRS-A3K9M2",
  "status": "confirmed",
  "total_amount": 667.00,
  "check_in": "2026-05-10T15:00:00",
  "check_out": "2026-05-13T11:00:00"
}

// ═══════════════════════════════════════════════════════════════
// CANCELLATION
// ═══════════════════════════════════════════════════════════════

DELETE /api/v1/reservations/:reservationId

Response: {
  "status": "cancelled",
  "refund_amount": 667.00,
  "refund_percent": 100,
  "refund_method": "credit_card",
  "message": "Full refund — cancelled 8 days before check-in (free cancellation policy)"
}

Data Flow Summary

┌──────────────────────────────────────────────────────────────────────────────┐
│                         COMPLETE DATA FLOW                                   │
│                                                                              │
│  SEARCH PATH (read-heavy, ~3000 QPS):                                        │
│  Client → API Gateway → Search Service → Elasticsearch (geo + filters)       │
│       → Inventory Service (availability check via Redis cache)               │
│       → Ranking → Response                                                   │
│                                                                              │
│  BOOKING PATH (write-heavy, ~30 TPS, strong consistency):                    │
│  Client → API Gateway → Booking Service                                      │
│       → Inventory Service (optimistic lock UPDATE in PostgreSQL)             │
│       → Payment Service (Stripe charge)                                      │
│       → Booking confirmed → Notification Service (email/SMS)                 │
│       → Kafka event → Search Indexer (update popularity)                     │
│                                                                              │
│  ADMIN PATH (low traffic):                                                   │
│  Hotel Admin → API Gateway → Hotel Service                                   │
│       → PostgreSQL (update hotel/room data)                                  │
│       → Kafka event → Search Indexer (re-index hotel)                        │
│       → Redis invalidation                                                   │
│                                                                              │
│  PRICING PATH (batch, every 15 min):                                         │
│  Pricing Engine → read occupancy + external signals                          │
│       → calculate new prices → UPDATE room_type_inventory                    │
│       → Kafka event → Search Indexer (update price range)                    │
│       → Redis cache invalidation                                             │
└──────────────────────────────────────────────────────────────────────────────┘

Scaling Considerations

Database Sharding Strategy

At 9.1M inventory rows, a single PostgreSQL instance handles it fine. But at scale (100K+ hotels), we shard:

• Shard key: hotel_id — all inventory and bookings for a hotel on the same shard
• Benefits: booking transactions are always local to one shard (no distributed transactions)
• Trade-off: cross-hotel queries (like search) go through Elasticsearch, not PostgreSQL

// Shard routing
function getShardForHotel(hotelId: string): number {
  const hash = murmur3(hotelId);
  return hash % NUM_SHARDS;  // e.g., 16 shards
}

// All inventory operations for a hotel go to the same shard
// No cross-shard transactions needed!

Read Replicas

• Search availability checks → read from replicas (slight staleness is OK for search display)
• Booking operations → always hit the primary (strong consistency required)
• Hotel details → read from replicas + Redis cache

Elasticsearch Scaling

// 5,000 hotels → single index, 1 primary shard, 1 replica
// 100,000 hotels → 5 primary shards, 1 replica each
// 1,000,000 hotels → multiple indices (by region), 10+ shards each

// Index lifecycle: hotels index is small, rarely changes
// Performance tuning:
//   - Warm cache for popular cities
//   - Use routing by city to co-locate hotels on same shard
//   - Refresh interval: 30 seconds (not real-time)

Monitoring & Observability

Key Business Metrics

• Booking success rate — % of booking attempts that succeed (target: >95%)
• Optimistic lock conflict rate — % of bookings that need retry (target: <2%)
• Search latency P99 — target: <500ms
• Booking latency P99 — target: <2s (including payment)
• Inventory accuracy — reserved_rooms matches actual reservation count
• Overbooking walk rate — % of guests walked (target: <0.5%)
• Revenue per available room (RevPAR) — key hotel industry metric

Critical Alerts

// Alert: Optimistic lock conflicts exceeding threshold
if (conflictRate > 0.05) {  // >5% conflicts
  alert('HIGH_BOOKING_CONTENTION', {
    message: 'Lock conflict rate above 5% — consider pessimistic locking for hot hotels',
    metric: conflictRate,
  });
}

// Alert: Inventory mismatch
// Nightly job reconciles reserved_rooms with actual reservation count
const mismatch = await db.query(`
  SELECT i.hotel_id, i.room_type_id, i.date,
         i.reserved_rooms AS inventory_count,
         COALESCE(r.actual_count, 0) AS reservation_count,
         i.reserved_rooms - COALESCE(r.actual_count, 0) AS drift
  FROM room_type_inventory i
  LEFT JOIN (
    SELECT hotel_id, room_type_id, d.date, SUM(num_rooms) AS actual_count
    FROM reservations r
    CROSS JOIN LATERAL generate_series(r.check_in_date, r.check_out_date - 1, '1 day') d(date)
    WHERE r.status IN ('confirmed', 'pending')
    GROUP BY hotel_id, room_type_id, d.date
  ) r ON i.hotel_id = r.hotel_id 
     AND i.room_type_id = r.room_type_id
     AND i.date = r.date
  WHERE ABS(i.reserved_rooms - COALESCE(r.actual_count, 0)) > 0
`);

Key Takeaways

• Model inventory at room_type + date granularity — not per individual room. The room_type_inventory table with total_rooms and reserved_rooms is the core data model.
• Optimistic locking prevents double-booking with minimal performance overhead. The UPDATE ... WHERE version = V AND reserved + N <= total pattern is elegant and effective.
• Overbooking at 110% is industry standard — the revenue from filling otherwise-empty rooms far exceeds the cost of occasionally walking a guest.
• Separate search from booking — Elasticsearch handles the read-heavy search path (~3000 QPS); PostgreSQL handles the write-heavy booking path (~30 TPS) with strong consistency.
• Dynamic pricing maximizes revenue — occupancy, season, day-of-week, lead time, and local events all feed into the pricing algorithm.
• Short TTL caching for availability (30s) balances freshness with performance. Booking always reads from the DB directly.
• Expired reservation cleanup prevents inventory leaks — pending bookings that timeout must release their reserved rooms.
• CDC with Debezium + Kafka keeps Elasticsearch in sync without tight coupling between services.