Hotel Booking System: Inventory That Is a Physical Room, Not a Database Row

A hotel booking system looks like a row with a boolean. It is not, and the distance between the two is the entire engineering problem: inventory is per room type, per rate plan, per night, per physical room, and a sold-out hotel is frequently a hotel that has rooms.

The scale

  a group or chain
    200 properties
    15,000 rooms total
    300,000 room-nights sold per month

    inventory dimensions
      - property
      - room type (king, twin, suite,
        accessible, connecting)
      - rate plan (BAR, corporate,
        member, prepaid, package)
      - night (a stay spans several,
        each independently sold)
      - physical room (for housekeeping
        and for the last-room problem)

    a booking touches
      - 3-14 room-nights
      - 1 rate plan
      - 1 room type
      - 1 payment
      - 1 cancellation policy snapshot
      - 1 guest identity

The night dimension is the one that makes it a system rather than a record. A three-night stay is three separate inventory units, and the booking has to take all three or none. That single fact is what makes the concurrency hard, and it drives most of the design.

The inventory model

  the naive model
    property, date, available_count: int

  the real model
    (property, date, room_type, rate_plan)
      -> allotment / physical capacity
      -> committed (confirmed bookings)
      -> held (unexpired holds)
      -> sold out vs sold-through are
         different states

  availability for a query =
      for each night in [check_in, check_out)
        if any night is unavailable for
        (room_type, rate_plan):
            not bookable
        else:
            bookable

  and a stay of 3 nights needs a
  consistent answer across all 3

The “any night fails” rule is the basic correctness constraint, and it is where the naive implementation breaks: booking a three-night stay where night 2 was sold out for that room type produces a reservation the front desk cannot honour. The hotel then has a guest to walk. So the availability check must be atomic across the whole date range, not per-night.

Rate plan as a dimension, not a filter. This is the most consequential modelling decision. If rate plans are a filter on top of room-type inventory, then a sold-out corporate rate makes the room look unavailable, and the hotel loses a sale it could have made at the member rate. If rate plans are part of the inventory key, the system correctly reports “the king is sold out on the corporate rate but available on the prepaid rate”. The second model is more complex and the first one produces an unbookable inventory, which is worse.

Allotment vs physical capacity is the other axis. A property may expose only part of a room-type pool to a channel, but allotments are constraints on the same physical inventory, not extra rooms. “12 online and 8 direct” can be a partition, a release-back rule, or a nested cap; the contract must say which. Selling through one channel must still decrement the physical room-type/night balance, or two valid allotments can oversell the same building.

Rate plans are not independent physical inventory either. A refundable and a prepaid rate can compete for the same king room. The inventory ledger is normally keyed by property, stay date, and room type (plus an explicit sell limit/overbooking policy); rate-plan eligibility, restrictions, price, and channel allotment are additional predicates and caps. If every rate plan owns a separate capacity counter, the same room can be sold once per plan.

Holds, bookings, and the race

  the sequence that must be correct

  t0  user searches        -> sees "1 room left"
  t1  clicks Book          -> POST /hold
                            server creates a HOLD
                            with a 10-15 min expiry
                            inventory now:
                              available -= 1
                              held += 1
  t2  enters details/card  -> hold counts down
  t3  clicks Confirm        -> POST /book
                            hold -> BOOKING (confirmed)
                            inventory now:
                              committed += 1
  t4  payment authorised
                            booking status: confirmed
                            (or: pending_payment)

  if the hold expires at any point
  before t3
    -> inventory released
    -> the page must say so

The hold is the entire mechanism, and getting it right is most of the concurrency work. Requirements:

Atomic multi-night acquisition. The hold must take all nights or none, in a single serialisable operation or with explicit locking per (property, date, room_type, rate_plan). A naive “check then insert” has a race that will show up under load as a rare, unreproducible overbooking.

Expiry enforced server-side, always. A hold that is released by a client-side timer is a hold that leaks. The authoritative expiry is a timestamp in the database, swept by a worker; clients optimistically count down but the server is the arbiter. And a user who clicks Confirm at 14:59 on a 15:00 hold should either be allowed (the hold was still theirs) or be told clearly — not silently given an error that looks like a sold-out hotel.

Payment ordering. Inventory and a payment provider do not share a transaction. Charge-first risks authorized money with no reservation; reserve-first risks inventory held without payment. A robust flow uses a durable booking/payment state machine, idempotency keys at both boundaries, and reconciliation against provider events. Do not claim one ordering is what “most systems” use: merchant model, pay-at-property rates, 3-D Secure, and cancellation terms change the choice.

Idempotency. Every booking and payment call needs a client-supplied idempotency key, because users double-click, mobile networks retry, and payment providers call back. A duplicate booking created by a retried request is a real-money problem.

Cancellation is a state machine

  booking state machine

  held
    -> confirmed_pending_payment
    -> confirmed_paid
    -> cancelled (by user, within window,
      refund created)
    -> expired (hold timed out)

  confirmed_paid
    -> cancelled_outside_window
      (no refund, penalty may apply)
    -> modified (dates or room changed,
      requires repricing + re-payment)
    -> no_show (past check-out, never
      checked in)

  and modification is a release+reacquire
  under the same locking rules as a new
  booking, which means a modification can
  FAIL because the new dates are sold
  out. This is the worst support ticket
  in this business, and the design must
  anticipate it.

The policy snapshot is the detail that saves the most trouble. Cancellation terms are a function of the rate plan, the lead time at booking, and sometimes the current date — so the terms at the time of booking must be copied onto the booking record, not looked up at cancellation time. A hotel that changes its policy, or a rate plan whose terms differ by channel, will otherwise create cancellations that quietly disagree with what the guest was told at purchase, and that is both a support problem and, in many jurisdictions, a legal one.

Refunds are a separate ledger, not a field. refund_amount on a booking cannot express partial refunds, multiple refunds, or a refund that failed. A refund is an entry in a payments ledger with its own status, its own retry, and its own reconciliation against the provider. This is unglamorous and it is the difference between a system that can answer “did this guest get their money back” and one that guesses.

Modifications and price changes

  a guest changes dates
    1. reprice the new stay
       (rates move daily; the new dates
        may cost more)
    2. attempt to release the old
       inventory and acquire the new
    3. if acquisition fails
       -> the whole modification fails
    4. if price increased
       -> charge the difference
       -> some rate plans forbid
          modification entirely

  the right pattern
    acquire the new inventory FIRST,
    then release the old
    (holding both briefly)
    -> this means a brief
       double-hold, which is correct
    -> the alternative risks
       destroying a booking the guest
       already had

Acquire-then-release is the correct order and it is counterintuitive enough that implementations get it backwards. The cost is a temporary double-hold, which the expiry sweep cleans up. The cost of getting it wrong is a guest who had a confirmed booking and now has nothing, which is a cancellation you did not want and a compensation you must pay.

Rate-plan restrictions matter here: prepaid and non-refundable plans frequently forbid date changes entirely, and the system has to know that at the time the guest asks, not at the time the change fails.

Overbooking

  the question
    a 100-room hotel, sold out
    for the weekend, 95% average
    occupancy

  options
    1. stop selling at 100%  -> the
       5% walk-in is lost, and any
       no-show on the day costs a
       full room-night at ~100%
    2. overbook to 105%  -> you gain
       the no-show revenue, and you
       walk 5 guests in the worst case

  the break-even
    p(no-show) x gain_per_no_show_room
      >
    p(walk) x cost_per_walked_guest

  with
    cost_per_walked_guest ~= the cost of
      finding them another hotel
      + transport + compensation
      + the reputational cost, which
      is not in the model and is the
      biggest term

  so the model is a
  no-show-vs-walk trade-off,
  not a revenue-maximisation

Overbooking limits normally attach to the physical sell pool—property/room type/date or an explicitly substitutable room-class group—not independently to every rate plan. The policy may incorporate channel and rate restrictions, but counters must reconcile to one physical capacity. Its model needs a cost function for no-shows, cancellations, upgrades, and walks; a blanket percentage hides nights where substitution is impossible.

The related, less-discussed constraint is the last physical room: if the property is genuinely full, no policy helps, and the system should be offering a different room type, a different property, or a waitlist rather than a booking it cannot honour.

Failure stories worth testing

Sell the last king room for three nights where night 2 is the last one

The whole booking must fail atomically. A partial booking is a walked guest.

Let a hold expire while the user is on the payment page

Confirm the page reports it clearly and inventory is released on schedule by a server-side sweep, not a client timer.

Double-click Confirm and retry the payment

Exactly one booking and one charge. This is the idempotency test.

Change the cancellation policy for a rate plan after bookings exist

Existing bookings must keep their snapshotted terms. This is the policy-snapshot test.

Attempt a date modification into a sold-out period

The modification must fail without destroying the original booking. Acquire-then-release is what makes this safe.

Make the payment provider timeout after the room is confirmed

The booking must sit in a reconcilable pending state and end in a defined outcome — never silently confirmed-and-unpaid, and never paid-and-gone.

Attempt a partial refund, then a second partial refund

The ledger must express both. A refund_amount field cannot.

Set the channel allotment to 12 of 20 physical rooms and sell 12

The 13th request follows the allotment contract: reject it, consume released/free-sale inventory, or request an allotment release. The channel must never infer that the other eight rooms are available merely because physical capacity remains.

Raise the overbooking level by 10% and run 1,000 simulated nights

Measure walks versus no-show gains. The break-even is often much closer to 100% than intuition suggests.

Make a prepaid booking and try to change dates

The rate plan must forbid it at the time of the request.

Sell the same physical room as two different room types on the same night

The housekeeping/inventory join must prevent it. This is a classic and expensive bug.

Issue a refund against a booking that was partially paid in two currencies

The ledger must handle it. Anything less and finance will find it.

A production-ready architecture

   search / availability
        |
        v
  +----------------------------------------------------------+
  |  AVAILABILITY SERVICE                                    |
  |  keyed by (property, date, room_type, rate_plan)         |
  |  allotment vs physical capacity                          |
  |  multi-night atomic check across the stay                |
  +----------------------------+-----------------------------+
                               |
                               v
  +----------------------------------------------------------+
  |  HOLD SERVICE (the race boundary)                        |
  |  - atomic multi-night acquire, serialisable             |
  |  - server-side expiry, swept, authoritative              |
  |  - idempotency keys                                       |
  +----------------------------+-----------------------------+
                               |
                               v
  +----------------------------------------------------------+
  |  BOOKING SERVICE                                         |
  |  - hold -> confirmed_pending_payment -> confirmed_paid  |
  |  - policy snapshot copied onto the booking               |
  |  - modify: acquire new inventory FIRST, then release old |
  |  - status history, never a mutable current-state-only    |
  +----------------------------+-----------------------------+
                               |
                               v
  +----------------------------------------------------------+
  |  PAYMENTS LEDGER (separate from bookings)                |
  |  - charges, partial refunds, multiple refunds            |
  |  - per-entry status, retry, reconciliation                |
  |  - webhooks from providers, idempotent                   |
  +----------------------------+-----------------------------+
                               |
                               v
  +----------------------------------------------------------+
  |  OVERBOOKING POLICY + LAST ROOM                           |
  |  - no-show vs walk model, per property/rate/day         |
  |  - when exhausted: alternative room, property, waitlist  |
  +----------------------------+-----------------------------+

  watch: hold expiry rate, overbooking walks, payment reconciliation
        gaps, modification failure rate, allotment vs physical drift

Delivery checklist:

1. Keep physical inventory at (property, date, room type) or a documented substitutable pool. Apply rate-plan restrictions and channel allotments without multiplying capacity.
2. Make multi-night availability checks atomic. A three-night stay takes all three nights or none.
3. Put the hold at the centre of the flow with server-side expiry and a sweeper, and make the expiry visible in the UI.
4. Give every booking, cancellation, modification, and payment an idempotency key, and keep a status history rather than a mutable state field.
5. Snapshot the cancellation policy onto the booking at purchase time, and make refunds ledger entries rather than a field.
6. Order modifications acquire-then-release, so a failed change never destroys a booking the guest already had.
7. Represent confirmed-versus-paid as distinct states and reconcile pending payments with a background job.
8. Apply overbooking as an explicit policy on top of physical capacity, with the level set by a no-show-versus-walk model, and know where the real last room is.
9. When inventory is exhausted, offer the alternative room type, a different property, or a waitlist — never a booking you cannot honour.
10. Check allotment and physical sell limits in the same acquisition; unused physical rooms are not automatically available to every channel.
11. Reconcile payment webhooks idempotently and monitor for gaps; a missed webhook is a guest who paid and shows as unpaid.
12. Track holds-expiring-per-thousand as a UX metric. A high rate means checkout is too slow or the hold is too short.

Common mistakes

Mistake	What actually happens	Better decision
Capacity copied per rate plan	The same physical room is sellable once per plan	Shared room-type/night balance plus offer restrictions
Per-night availability checks	Multi-night bookings that cannot be honoured	Atomic across the whole stay
Client-side hold expiry	Leaked holds, phantom availability	Server-side expiry with a sweeper
No idempotency on confirm	Double bookings, double charges	Client-supplied keys everywhere
Cancellation policy looked up live	Cancellations that disagree with what was sold	Snapshot the terms onto the booking
Refund as a field	Cannot express partial or repeated refunds	A payments ledger with entries
Release-then-acquire on modify	Guest loses a booking they had	Acquire the new, then release the old
Overbooking as a revenue max	Walked guests at 11pm	No-show vs walk break-even model
No last-room awareness	A booking that is physically impossible	Alternative, waitlist, or honest failure
Allotment ignored	Channel either oversells or blocks the property	Model allotment and physical separately
Booking state as a single field	No history, no forensics	Append-only status history
Confirmed and paid conflated	Guests “confirmed” with no money taken	Distinct states plus reconciliation
No payment webhook replay	Paid bookings that show unpaid	Idempotent, replayed, monitored
No hold-expiry metric	Checkout friction goes unnoticed	Expiry rate per thousand sessions

The complete story in one minute

Hotel inventory is not a row with a boolean. A stay consumes several independently sold nights, and several rate plans and channels can compete for the same physical room-type pool. The physical key must not multiply capacity by rate plan. Availability evaluates room-type/night balance, overbooking policy, allotment, restrictions, and price together; the multi-night acquisition is all-or-nothing.

The race between a hold and a booking is where the money is lost. A hold with a server-authoritative expiry — swept, never trusted to a client timer — acquired atomically across the whole stay, with idempotency keys on every confirm and payment, is the mechanism. Distinguish confirmed from paid as separate states, reconcile pending payments in the background, and make the expiry visible in the UI so a user at 14:59 on a 15:00 hold is told clearly rather than shown a sold-out error.

Cancellation and modification are state machines, not fields. Snapshot the policy terms onto the booking at purchase time, so later policy changes never retroactively alter what the guest was told. Keep refunds in a ledger with entries, because a field cannot express partial or repeated refunds. And order modifications acquire-then-release — hold both briefly, so a failed date change never destroys a booking the guest already had.

Overbooking is the number that decides whether the rest works, and it is a no-show-versus-walk trade-off, not a revenue maximisation. The reputational cost of walking someone at 11pm is the largest term in that model and the one nobody puts in the spreadsheet.

inventory keyed by room type and rate plan; multi-night atomic acquire
holds with server-side expiry; idempotency everywhere
confirmed vs paid as distinct states; refunds in a ledger
modify acquire-then-release; policy snapshotted at purchase
overbooking from a no-show vs walk model; know the last room

The hard part was never reserving a row. It was keeping multiple commercial offers tied to one physical sell limit, then preserving that promise through payment ambiguity, modification, cancellation, room assignment, and housekeeping.

Technical references

• OpenTravel Alliance: hospitality specifications
• PostgreSQL: serializable isolation
• Stripe: idempotent requests
• Stripe: PaymentIntent lifecycle