Design a Hotel Booking System (Booking.com)

BEGIN TRANSACTION
SELECT * FROM room_availability 
WHERE room_id = 101 AND date = '2026-06-15' 
FOR UPDATE           ← acquires row-level lock

-- User A checks availability, fills payment form (up to 10 minutes)
-- Row is LOCKED for the entire duration

UPDATE room_availability SET status = 'booked' WHERE room_id = 101 AND date = '2026-06-15'
COMMIT

READ:
  SELECT room_id, status, version FROM room_availability
  WHERE room_id = 101 AND date = '2026-06-15'
  → returns {status: "available", version: 42}

-- User fills in payment form...

WRITE (conditional update):
  UPDATE room_availability
  SET status = 'booked', version = version + 1
  WHERE room_id = 101 AND date = '2026-06-15' AND version = 42
  ← only succeeds if nobody else updated it

  If 0 rows affected: another user got there first → show "no longer available"
  If 1 row affected: success → create booking record

Step 1: User selects room → system creates a HOLD (10-minute TTL)
  INSERT INTO holds (room_id, date, user_id, expires_at)
  WHERE NOT EXISTS (SELECT 1 FROM holds WHERE room_id = X AND date = Y AND expires_at > NOW())
  AND NOT EXISTS (SELECT 1 FROM bookings WHERE room_id = X AND date = Y)

Step 2: User completes payment (within 10 minutes)
  → Convert hold to confirmed booking
  → Delete hold

Step 3: Hold expires without payment
  → Background job deletes expired holds → room becomes available again

┌───────────────────────────────────────────────────────────┐
│                        Clients                             │
└───────────────────────┬───────────────────────────────────┘
                        │
              ┌─────────▼──────────┐
              │    API Gateway      │
              └──┬──────┬──────────┘
                 │      │
    ┌────────────▼──┐  ┌▼───────────────────┐
    │ Search Service│  │  Booking Service    │
    │               │  │  (state machine)    │
    │ Elasticsearch │  │  + Payment Client   │
    │ for property  │  └──────────┬──────────┘
    │ search        │             │
    └───────────────┘    ┌────────▼──────────┐
                         │   PostgreSQL       │
                         │  (inventory,       │
                         │   bookings,        │
                         │   holds)           │
                         └───────────────────┘

1. User searches "Paris hotels, Jun 15-17, 2 guests, max €200/night, ≥4 stars"

2. Elasticsearch query:
   → geo filter: within 20km of Paris centre
   → filter: star_rating >= 4
   → returns: [property_1, property_2, ..., property_200]

3. PostgreSQL availability query (batch):
   SELECT property_id, room_type, price FROM room_availability
   WHERE property_id IN (property_1, ..., property_200)
     AND date BETWEEN '2026-06-15' AND '2026-06-17'
     AND status = 'available'
     AND guests_capacity >= 2
     AND price_per_night <= 200
   → returns: properties that actually have rooms available for those dates

4. Merge Elasticsearch metadata + PostgreSQL availability
   Sort by relevance / price
   Return top 20 results to user

SEARCHED → HOLD_PLACED (10 min TTL)
                 ├─ PAYMENT_PROCESSING
                 │        ├─ CONFIRMED ──────────────────┐
                 │        └─ PAYMENT_FAILED → (hold expires) │
                 └─ HOLD_EXPIRED (user didn't complete)      │
                                                              │
CONFIRMED ──── CANCELLED (> 24h before check-in, full refund)│
           └── CANCELLED (< 24h before check-in, no refund)  │
                                                              │
COMPLETED ◄───────────────────────────────────────────────────┘
  (check-out date passed, booking archived)

Price is calculated at search time by a Pricing Service:
  base_price (from hotel's rate table)
  × occupancy_multiplier (% rooms booked for that date)
  × seasonal_multiplier (Christmas, summer)
  × length_of_stay_discount (3+ nights = 5% off)

Price is locked in at the time of hold creation.
If the hold expires, the price may change on re-search.

1. Update booking status to CANCELLED
2. Update room_availability: SET status = 'available' for those dates
3. Initiate refund via payment provider (if eligible)
4. Update Elasticsearch availability index (async, acceptable slight delay)

All of steps 1-3 happen in ONE database transaction.
If any fails, the whole thing rolls back.
Step 4 is asynchronous — Elasticsearch is eventually consistent for search.