Design Hotel Reservation System

Design Hotel Reservation System

Designing a hotel reservation system at scale is a classic distributed systems challenge. We need to handle concurrent bookings, maintain accurate inventory across thousands of hotels, process payments reliably, and provide fast availability searches across date ranges. Think Booking.com or Expedia scale: millions of searches per day, thousands of concurrent bookings, and zero tolerance for double-booking rooms.

The core challenge is the inventory problem. Unlike e-commerce where you might have 1000 units of a product, hotels have limited rooms (typically 50-500 per property) with complex availability patterns based on date ranges, room types, and rates. A single room can’t be booked for overlapping dates, and we need to handle this with millisecond-level precision under high concurrency.

Step 1: Requirements Gathering

Let’s start by clarifying what we’re building and the scale we need to support.

Functional Requirements

Room Search and Availability:

• Search hotels by location (city, coordinates), dates, guests, room type
• Display real-time room availability for date ranges
• Filter by price, amenities, star rating, reviews
• Show multiple room types per hotel (standard, deluxe, suite)
• Support multi-room bookings (e.g., 3 rooms for family trip)

Booking Flow:

• Select rooms and hold inventory temporarily (5-15 minutes)
• Enter guest details and payment information
• Process payment through third-party gateway
• Confirm booking with instant confirmation number
• Send confirmation emails with booking details

Payment Processing:

• Support multiple payment methods (credit cards, digital wallets)
• Handle full payment, partial payment (deposit), pay-at-hotel
• Process refunds for cancellations based on policy
• Store payment information securely (PCI compliance)
• Handle payment failures and retry logic

Cancellation and Modifications:

• Cancel bookings based on hotel cancellation policy
• Modify bookings (change dates, room type, guests)
• Calculate and process refunds automatically
• Free up inventory immediately on cancellation
• Handle no-show scenarios

Check-in/Check-out:

• Digital check-in 24 hours before arrival
• Room assignment at check-in (auto-assign or manual)
• Early check-in and late check-out requests
• Check-out processing with final charges
• Handle room service, minibar, extras billing

Additional Features:

• User accounts with booking history
• Hotel admin dashboard for inventory management
• Pricing calendar with dynamic rates
• Reviews and ratings after checkout
• Loyalty programs and discounts

Non-Functional Requirements

Scale:

• 100,000 hotels globally
• 10 million rooms in inventory
• 500,000 searches per minute during peak
• 5,000 bookings per minute
• 50 million active users

Performance:

• Search results in <500ms (p99)
• Booking confirmation in <2 seconds
• 99.99% availability for booking system
• Inventory updates reflected in <1 second

Consistency:

• No double-bookings (strong consistency for inventory)
• Payment and booking state must be atomic
• Eventual consistency acceptable for reviews, ratings
• Stale availability data tolerable for <10 seconds in search

Security:

• PCI DSS compliance for payment data
• Encryption at rest and in transit
• Secure authentication and authorization
• Fraud detection and prevention

Step 2: High-Level Design

Let’s break down the system into microservices, each handling a specific domain.

Core Services Architecture

Hotel Service:

• Manages hotel metadata (name, location, amenities, images)
• Stores hotel configuration (policies, check-in times, contact)
• Provides search by location (geo-spatial queries)
• Handles hotel onboarding and profile updates
• Database: PostgreSQL with PostGIS for location data

Room Service:

• Manages room types for each hotel (standard, deluxe, suite)
• Stores room metadata (size, bed type, max occupancy, amenities)
• Maintains room count per type (e.g., 50 deluxe rooms)
• Handles room configuration and pricing setup
• Database: PostgreSQL

Inventory Service:

• The heart of the system - manages room availability
• Tracks available rooms for each hotel, room type, and date
• Handles inventory holds during booking process
• Prevents double-bookings with distributed locks
• Processes inventory releases on cancellation
• Database: PostgreSQL with row-level locking + Redis for caching

Booking Service:

• Orchestrates the entire booking workflow
• Creates and manages reservations
• Implements state machine (pending -> confirmed -> checked-in -> checked-out)
• Handles booking modifications and cancellations
• Stores booking history and guest details
• Database: PostgreSQL

Payment Service:

• Integrates with payment gateways (Stripe, PayPal, Adyen)
• Processes charges, holds, refunds
• Implements idempotency for payment operations
• Handles payment webhooks and callbacks
• Stores payment records securely
• Database: PostgreSQL with encryption

Pricing Service:

• Manages dynamic pricing rules (seasonal, demand-based)
• Calculates final price based on dates, room type, discounts
• Handles special offers and promo codes
• Provides pricing calendar for hotels
• Cache: Redis for price calculations

Search Service:

• Provides fast hotel and room search
• Indexes hotel data with Elasticsearch
• Handles complex filters (price range, amenities, location radius)
• Aggregates availability data from Inventory Service
• Caches popular searches in Redis

Notification Service:

• Sends booking confirmations via email/SMS
• Reminder emails before check-in
• Cancellation confirmations and refund notifications
• Marketing emails (optional)
• Queue: Kafka for async processing

User Service:

• Manages user accounts and authentication
• Stores user profiles and preferences
• Booking history and saved hotels
• Loyalty program integration
• Database: PostgreSQL

Data Flow for Booking

1. User searches hotels
   -> Search Service queries Elasticsearch
   -> Aggregates availability from Inventory Service cache
   -> Returns results in 200-500ms

2. User selects rooms and initiates booking
   -> Booking Service creates pending reservation
   -> Inventory Service places hold on rooms (15-min TTL)
   -> Returns booking ID to frontend

3. User enters payment details
   -> Payment Service validates payment method
   -> Processes authorization (hold on credit card)

4. Payment success
   -> Booking Service confirms reservation
   -> Inventory Service converts hold to confirmed booking
   -> Payment Service captures payment
   -> Notification Service sends confirmation email

5. Payment failure
   -> Booking Service marks reservation as failed
   -> Inventory Service releases hold
   -> User notified to retry

Database Schema Highlights

Hotels Table:

CREATE TABLE hotels (
    hotel_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location GEOGRAPHY(POINT),
    address TEXT,
    star_rating INT,
    amenities JSONB,
    policies JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_hotels_location ON hotels USING GIST(location);

Room Types Table:

CREATE TABLE room_types (
    room_type_id BIGSERIAL PRIMARY KEY,
    hotel_id BIGINT REFERENCES hotels(hotel_id),
    name VARCHAR(255),
    total_rooms INT,
    max_occupancy INT,
    amenities JSONB,
    base_price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

Inventory Table (Critical):

CREATE TABLE inventory (
    inventory_id BIGSERIAL PRIMARY KEY,
    hotel_id BIGINT,
    room_type_id BIGINT,
    date DATE NOT NULL,
    total_rooms INT,
    available_rooms INT,
    version INT DEFAULT 0, -- Optimistic locking
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(hotel_id, room_type_id, date)
);

CREATE INDEX idx_inventory_search ON inventory(hotel_id, room_type_id, date);
CREATE INDEX idx_inventory_date_range ON inventory(date, available_rooms);

Bookings Table:

CREATE TABLE bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    hotel_id BIGINT,
    room_type_id BIGINT,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    num_rooms INT,
    num_guests INT,
    total_price DECIMAL(10,2),
    status VARCHAR(50), -- pending, confirmed, cancelled, checked_in, checked_out
    payment_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_bookings_user ON bookings(user_id, created_at);
CREATE INDEX idx_bookings_hotel ON bookings(hotel_id, check_in_date);
CREATE INDEX idx_bookings_status ON bookings(status, created_at);

Inventory Holds Table:

CREATE TABLE inventory_holds (
    hold_id BIGSERIAL PRIMARY KEY,
    booking_id BIGINT REFERENCES bookings(booking_id),
    hotel_id BIGINT,
    room_type_id BIGINT,
    date DATE NOT NULL,
    num_rooms INT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_holds_expiry ON inventory_holds(expires_at);

Step 3: Deep Dives

Now let’s dive deep into the critical technical challenges.

3.1 Room Inventory Management with Concurrent Bookings

The inventory problem is the hardest challenge. We need to prevent double-bookings when multiple users try to book the last available room simultaneously.

Pessimistic Locking Approach:

-- Start transaction
BEGIN;

-- Lock the inventory rows for the date range
SELECT inventory_id, available_rooms
FROM inventory
WHERE hotel_id = 12345
  AND room_type_id = 67
  AND date BETWEEN '2025-12-01' AND '2025-12-03'
FOR UPDATE; -- Exclusive lock

-- Check if enough rooms available for all dates
-- If yes, decrement available_rooms
UPDATE inventory
SET available_rooms = available_rooms - 1,
    version = version + 1,
    updated_at = NOW()
WHERE hotel_id = 12345
  AND room_type_id = 67
  AND date BETWEEN '2025-12-01' AND '2025-12-03'
  AND available_rooms >= 1;

-- Create booking record
INSERT INTO bookings (...) VALUES (...);

COMMIT;

Optimistic Locking Approach:

-- Read current version
SELECT inventory_id, available_rooms, version
FROM inventory
WHERE hotel_id = 12345 AND room_type_id = 67 AND date = '2025-12-01';
-- version = 42, available_rooms = 5

-- Update with version check
UPDATE inventory
SET available_rooms = available_rooms - 1,
    version = version + 1
WHERE inventory_id = 987
  AND version = 42  -- Only succeeds if no one else updated
  AND available_rooms >= 1;

-- If affected rows = 0, retry the operation

Distributed Locking with Redis:

For additional safety across multiple database replicas:

import redis
from contextlib import contextmanager

@contextmanager
def inventory_lock(hotel_id, room_type_id, date_range):
    """Distributed lock for inventory operations"""
    redis_client = redis.Redis()
    lock_key = f"inventory_lock:{hotel_id}:{room_type_id}:{date_range}"
    lock = redis_client.lock(lock_key, timeout=10)

    try:
        acquired = lock.acquire(blocking=True, blocking_timeout=5)
        if not acquired:
            raise Exception("Could not acquire inventory lock")
        yield
    finally:
        if lock.owned():
            lock.release()

# Usage
with inventory_lock(hotel_id=12345, room_type_id=67, date_range="2025-12-01:2025-12-03"):
    # Perform database operations
    check_and_update_inventory()

Inventory Hold Pattern:

Instead of immediately confirming bookings, we place a temporary hold:

def create_booking_with_hold(user_id, hotel_id, room_type_id, check_in, check_out, num_rooms):
    """Create booking with temporary inventory hold"""
    hold_duration = 15 * 60  # 15 minutes

    with inventory_lock(hotel_id, room_type_id, f"{check_in}:{check_out}"):
        # Check availability
        available = check_inventory_availability(hotel_id, room_type_id, check_in, check_out, num_rooms)
        if not available:
            raise InsufficientInventoryError()

        # Create pending booking
        booking_id = create_booking(user_id, hotel_id, room_type_id, check_in, check_out, num_rooms, status="pending")

        # Create inventory hold
        create_inventory_hold(booking_id, hotel_id, room_type_id, check_in, check_out, num_rooms,
                            expires_at=now() + hold_duration)

        # Decrement available inventory
        decrement_inventory(hotel_id, room_type_id, check_in, check_out, num_rooms)

        return booking_id

Hold Expiration Worker:

Background job to release expired holds:

def release_expired_holds():
    """Runs every minute to release expired holds"""
    expired_holds = db.query("""
        SELECT hold_id, booking_id, hotel_id, room_type_id, date, num_rooms
        FROM inventory_holds
        WHERE expires_at < NOW()
          AND released_at IS NULL
        FOR UPDATE SKIP LOCKED
    """)

    for hold in expired_holds:
        with inventory_lock(hold.hotel_id, hold.room_type_id, hold.date):
            # Increment inventory back
            increment_inventory(hold.hotel_id, hold.room_type_id, hold.date, hold.num_rooms)

            # Mark hold as released
            mark_hold_released(hold.hold_id)

            # Cancel the pending booking
            cancel_booking(hold.booking_id, reason="payment_timeout")

3.2 Availability Checking with Date Range Queries

Checking availability across date ranges is query-intensive. For a 7-night stay, we need to check 7 days of inventory.

Efficient Query Pattern:

-- Check if hotel has availability for ALL dates in range
SELECT
    hotel_id,
    room_type_id,
    MIN(available_rooms) as min_available
FROM inventory
WHERE hotel_id = 12345
  AND room_type_id = 67
  AND date BETWEEN '2025-12-01' AND '2025-12-07'
GROUP BY hotel_id, room_type_id
HAVING MIN(available_rooms) >= 2;  -- Need 2 rooms

Redis Caching for Availability:

Cache availability data to reduce database load:

def get_availability_cached(hotel_id, room_type_id, check_in, check_out, num_rooms):
    """Check availability with Redis caching"""
    cache_key = f"availability:{hotel_id}:{room_type_id}:{check_in}:{check_out}"

    # Check cache first
    cached = redis.get(cache_key)
    if cached:
        return json.loads(cached)

    # Query database
    availability = db.query("""
        SELECT MIN(available_rooms) as min_available
        FROM inventory
        WHERE hotel_id = %s AND room_type_id = %s
          AND date BETWEEN %s AND %s
    """, hotel_id, room_type_id, check_in, check_out)

    result = availability[0]['min_available'] >= num_rooms

    # Cache for 60 seconds
    redis.setex(cache_key, 60, json.dumps(result))

    return result

Cache Invalidation on Booking:

def invalidate_availability_cache(hotel_id, room_type_id, check_in, check_out):
    """Invalidate cache when inventory changes"""
    # Generate all possible date range combinations that overlap
    date_ranges = generate_overlapping_ranges(check_in, check_out)

    for start, end in date_ranges:
        cache_key = f"availability:{hotel_id}:{room_type_id}:{start}:{end}"
        redis.delete(cache_key)

3.3 Booking Workflow with State Machine

Bookings transition through multiple states. We use a state machine to ensure valid transitions.

State Machine Definition:

States: pending -> confirmed -> checked_in -> checked_out
                -> cancelled (from pending, confirmed)
                -> failed (from pending)

Implementation:

class BookingStateMachine:
    TRANSITIONS = {
        'pending': ['confirmed', 'cancelled', 'failed'],
        'confirmed': ['checked_in', 'cancelled'],
        'checked_in': ['checked_out'],
        'checked_out': [],
        'cancelled': [],
        'failed': []
    }

    @staticmethod
    def can_transition(current_state, new_state):
        return new_state in BookingStateMachine.TRANSITIONS.get(current_state, [])

    @staticmethod
    def transition_booking(booking_id, new_state, reason=None):
        booking = get_booking(booking_id)

        if not BookingStateMachine.can_transition(booking.status, new_state):
            raise InvalidStateTransitionError(f"Cannot transition from {booking.status} to {new_state}")

        # Update booking status atomically
        updated = db.execute("""
            UPDATE bookings
            SET status = %s, updated_at = NOW()
            WHERE booking_id = %s AND status = %s
            RETURNING booking_id
        """, new_state, booking_id, booking.status)

        if not updated:
            raise ConcurrentModificationError("Booking was modified by another transaction")

        # Trigger side effects based on state
        handle_state_change(booking_id, booking.status, new_state, reason)

        return True

Complete Booking Flow:

def complete_booking_flow(booking_id, payment_method):
    """Complete booking with payment processing"""
    booking = get_booking(booking_id)

    # 1. Process payment
    try:
        payment_result = payment_service.charge(
            amount=booking.total_price,
            payment_method=payment_method,
            idempotency_key=f"booking_{booking_id}"
        )
    except PaymentError as e:
        # Transition to failed
        BookingStateMachine.transition_booking(booking_id, 'failed', reason=str(e))

        # Release inventory hold
        release_inventory_hold(booking_id)
        return False

    # 2. Confirm booking
    with db.transaction():
        BookingStateMachine.transition_booking(booking_id, 'confirmed')

        # Convert hold to confirmed inventory
        convert_hold_to_booking(booking_id)

        # Store payment reference
        update_booking_payment(booking_id, payment_result.payment_id)

    # 3. Send confirmation (async)
    notification_service.send_booking_confirmation(booking_id)

    return True

3.4 Overbooking Strategy

Airlines overbook to maximize revenue. Hotels can do the same, but more conservatively.

Dynamic Overbooking Calculation:

def calculate_overbooking_limit(hotel_id, room_type_id, date):
    """Calculate safe overbooking limit based on historical data"""
    # Get historical cancellation rate for this hotel/room type
    stats = db.query("""
        SELECT
            COUNT(*) as total_bookings,
            SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancellations
        FROM bookings
        WHERE hotel_id = %s
          AND room_type_id = %s
          AND check_in_date = %s
          AND created_at > NOW() - INTERVAL '90 days'
    """, hotel_id, room_type_id, date)

    cancellation_rate = stats[0]['cancellations'] / max(stats[0]['total_bookings'], 1)

    # Get total rooms
    total_rooms = get_room_type_total(hotel_id, room_type_id)

    # Conservative overbooking: allow 5-10% based on cancellation rate
    if cancellation_rate > 0.15:
        overbooking_pct = 0.10
    elif cancellation_rate > 0.10:
        overbooking_pct = 0.05
    else:
        overbooking_pct = 0.0

    overbooking_limit = int(total_rooms * overbooking_pct)

    return overbooking_limit

Modified Availability Check:

def check_availability_with_overbooking(hotel_id, room_type_id, date, num_rooms):
    """Check availability including overbooking buffer"""
    inventory = get_inventory(hotel_id, room_type_id, date)
    overbooking_limit = calculate_overbooking_limit(hotel_id, room_type_id, date)

    # Effective availability = actual + overbooking buffer
    effective_availability = inventory.available_rooms + overbooking_limit

    return effective_availability >= num_rooms

3.5 Dynamic Pricing by Season and Demand

Pricing changes based on demand, season, events, day of week.

Pricing Model:

class DynamicPricingEngine:
    def calculate_price(self, hotel_id, room_type_id, check_in_date, num_nights):
        """Calculate dynamic price for a stay"""
        base_price = get_base_price(hotel_id, room_type_id)

        total_price = 0
        current_date = check_in_date

        for night in range(num_nights):
            # Start with base price
            night_price = base_price

            # Apply seasonal multiplier
            season_multiplier = self.get_season_multiplier(current_date)
            night_price *= season_multiplier

            # Apply day of week multiplier (weekends cost more)
            dow_multiplier = self.get_day_of_week_multiplier(current_date)
            night_price *= dow_multiplier

            # Apply demand-based pricing
            occupancy_rate = self.get_occupancy_rate(hotel_id, current_date)
            demand_multiplier = self.get_demand_multiplier(occupancy_rate)
            night_price *= demand_multiplier

            # Check for special events
            event_multiplier = self.get_event_multiplier(hotel_id, current_date)
            night_price *= event_multiplier

            total_price += night_price
            current_date += timedelta(days=1)

        return round(total_price, 2)

    def get_season_multiplier(self, date):
        """Seasonal pricing (summer, winter, holidays)"""
        month = date.month

        if month in [12, 1]:  # Holiday season
            return 1.3
        elif month in [6, 7, 8]:  # Summer
            return 1.2
        elif month in [2, 3, 11]:  # Off-season
            return 0.9
        else:
            return 1.0

    def get_day_of_week_multiplier(self, date):
        """Weekend vs weekday pricing"""
        if date.weekday() in [4, 5]:  # Friday, Saturday
            return 1.15
        return 1.0

    def get_demand_multiplier(self, occupancy_rate):
        """Dynamic pricing based on current occupancy"""
        if occupancy_rate > 0.90:
            return 1.4
        elif occupancy_rate > 0.80:
            return 1.25
        elif occupancy_rate > 0.70:
            return 1.15
        elif occupancy_rate < 0.30:
            return 0.85
        return 1.0

    def get_event_multiplier(self, hotel_id, date):
        """Special event pricing (conferences, concerts, sports)"""
        events = get_nearby_events(hotel_id, date)
        if events:
            return 1.5
        return 1.0

Caching Price Calculations:

def get_cached_price(hotel_id, room_type_id, check_in, check_out):
    """Cache price calculations in Redis"""
    cache_key = f"price:{hotel_id}:{room_type_id}:{check_in}:{check_out}"

    cached_price = redis.get(cache_key)
    if cached_price:
        return float(cached_price)

    num_nights = (check_out - check_in).days
    price = pricing_engine.calculate_price(hotel_id, room_type_id, check_in, num_nights)

    # Cache for 1 hour
    redis.setex(cache_key, 3600, str(price))

    return price

3.6 Cancellation and Refund Policies

Hotels have different cancellation policies. We need to enforce them automatically.

Policy Types:

class CancellationPolicy:
    FREE_CANCELLATION = "free"  # Full refund until 24-48 hours before
    PARTIAL_REFUND = "partial"   # 50% refund until 7 days before
    NON_REFUNDABLE = "non_refundable"

Refund Calculation:

def calculate_refund(booking_id):
    """Calculate refund amount based on cancellation policy"""
    booking = get_booking(booking_id)
    hotel = get_hotel(booking.hotel_id)
    policy = hotel.cancellation_policy

    hours_until_checkin = (booking.check_in_date - now()).total_seconds() / 3600

    if policy == CancellationPolicy.NON_REFUNDABLE:
        return 0.0

    elif policy == CancellationPolicy.FREE_CANCELLATION:
        if hours_until_checkin >= 24:
            return booking.total_price
        else:
            return 0.0

    elif policy == CancellationPolicy.PARTIAL_REFUND:
        if hours_until_checkin >= 168:  # 7 days
            return booking.total_price
        elif hours_until_checkin >= 24:
            return booking.total_price * 0.5
        else:
            return 0.0

    return 0.0

Cancellation Flow:

def cancel_booking(booking_id, cancelled_by_user=True):
    """Cancel booking and process refund"""
    booking = get_booking(booking_id)

    if booking.status not in ['pending', 'confirmed']:
        raise InvalidOperationError(f"Cannot cancel booking in {booking.status} state")

    # Calculate refund
    refund_amount = calculate_refund(booking_id)

    with db.transaction():
        # Transition to cancelled
        BookingStateMachine.transition_booking(booking_id, 'cancelled')

        # Release inventory
        increment_inventory(booking.hotel_id, booking.room_type_id,
                          booking.check_in_date, booking.check_out_date, booking.num_rooms)

        # Process refund if applicable
        if refund_amount > 0 and booking.payment_id:
            payment_service.refund(
                payment_id=booking.payment_id,
                amount=refund_amount,
                idempotency_key=f"refund_{booking_id}"
            )

    # Invalidate availability cache
    invalidate_availability_cache(booking.hotel_id, booking.room_type_id,
                                 booking.check_in_date, booking.check_out_date)

    # Send cancellation confirmation
    notification_service.send_cancellation_confirmation(booking_id, refund_amount)

    return refund_amount

3.7 Check-in/Check-out Workflow

Digital Check-in:

def digital_checkin(booking_id, user_id):
    """Process digital check-in"""
    booking = get_booking(booking_id)

    # Validate booking
    if booking.user_id != user_id:
        raise UnauthorizedError()

    if booking.status != 'confirmed':
        raise InvalidOperationError("Booking must be confirmed")

    # Check if within check-in window (24 hours before to check-in time)
    hours_until_checkin = (booking.check_in_date - now()).total_seconds() / 3600
    if hours_until_checkin > 24 or hours_until_checkin < -4:
        raise InvalidOperationError("Not within check-in window")

    # Assign room (if not already assigned)
    if not booking.assigned_room_number:
        room_number = assign_room(booking.hotel_id, booking.room_type_id, booking.check_in_date)
        update_booking_room_assignment(booking_id, room_number)

    # Transition to checked_in
    BookingStateMachine.transition_booking(booking_id, 'checked_in')

    # Generate digital key (QR code or mobile key)
    digital_key = generate_digital_key(booking_id, booking.assigned_room_number)

    return digital_key

Room Assignment Optimization:

def assign_room(hotel_id, room_type_id, check_in_date):
    """Intelligently assign room to minimize housekeeping"""
    # Get list of available rooms for this type
    available_rooms = db.query("""
        SELECT room_number, last_checkout_date
        FROM rooms
        WHERE hotel_id = %s
          AND room_type_id = %s
          AND room_number NOT IN (
              SELECT assigned_room_number
              FROM bookings
              WHERE hotel_id = %s
                AND check_in_date <= %s
                AND check_out_date > %s
                AND assigned_room_number IS NOT NULL
          )
    """, hotel_id, room_type_id, hotel_id, check_in_date, check_in_date)

    # Prioritize rooms that were recently cleaned
    available_rooms.sort(key=lambda r: r.last_checkout_date or date.min)

    if not available_rooms:
        raise NoRoomsAvailableError()

    return available_rooms[0].room_number

Check-out Processing:

def checkout(booking_id, final_charges=None):
    """Process checkout and final billing"""
    booking = get_booking(booking_id)

    if booking.status != 'checked_in':
        raise InvalidOperationError("Must be checked in to check out")

    # Calculate final charges (room service, minibar, damages)
    total_extra_charges = sum(charge.amount for charge in final_charges or [])

    # Charge extra fees if any
    if total_extra_charges > 0:
        payment_service.charge(
            payment_method=booking.payment_method,
            amount=total_extra_charges,
            idempotency_key=f"checkout_{booking_id}"
        )

    # Transition to checked_out
    BookingStateMachine.transition_booking(booking_id, 'checked_out')

    # Mark room as needing housekeeping
    mark_room_for_cleaning(booking.hotel_id, booking.assigned_room_number)

    # Send final receipt
    notification_service.send_checkout_receipt(booking_id, total_extra_charges)

    # Trigger review request (after 24 hours)
    schedule_review_request(booking_id, delay_hours=24)

Step 4: Wrap-up

We’ve designed a production-grade hotel reservation system that handles the core challenges of inventory management, concurrent bookings, payment processing, and dynamic pricing.

Key Design Decisions:

1. Inventory Locking: Used pessimistic locking with PostgreSQL FOR UPDATE combined with distributed Redis locks to prevent double-bookings under high concurrency.

2. Hold Pattern: Implemented temporary inventory holds (15 minutes) to give users time to complete payment without blocking rooms indefinitely.

3. Caching Strategy: Redis caches availability and pricing data with 60-second TTL, invalidated on inventory changes. Search results cached in Elasticsearch.

4. State Machine: Enforced valid booking state transitions (pending -> confirmed -> checked_in -> checked_out) with atomic database updates.

5. Payment Idempotency: All payment operations use idempotency keys to prevent duplicate charges on retries.

6. Dynamic Pricing: Multi-factor pricing engine considers season, day of week, occupancy, and events with 1-hour cache.

Scalability Considerations:

• Database Sharding: Shard bookings and inventory by hotel_id for horizontal scaling
• Read Replicas: Route availability queries to read replicas, writes to primary
• Search Offloading: Elasticsearch handles all search traffic, synced via CDC
• Async Processing: Kafka for notifications, refunds, review requests
• CDN: Static hotel images, reviews served via CloudFront

Monitoring and Alerts:

• Track double-booking attempts (should be zero)
• Monitor inventory lock wait times (p99 < 50ms)
• Alert on payment failures >5%
• Track booking conversion rate (search -> booking)
• Monitor availability cache hit rate (target >80%)

Future Enhancements:

• Machine learning for demand forecasting and pricing
• Bidding system for last-minute room upgrades
• Group booking management for conferences
• Loyalty points and rewards integration
• Multi-property bookings (hotel + flight packages)
• Real-time occupancy dashboard for hotel managers

This architecture supports millions of users, thousands of hotels, and handles the complex distributed systems challenges of inventory management with strong consistency guarantees where needed and eventual consistency for performance optimization.