The Hotel Operations Data Model: A Reference Schema

This is the hotel companion to The STR Operations Data Model, and it exists for the same reason: our July 2026 audit of 1,125 GitHub repositories found open source solved the booking engine many times over and the operations layer almost never. Hotels are a harder version of the problem. A vacation rental has a turnover; a hotel has a nightly cycle across hundreds of rooms, run by three departments that keep separate books on the same door. The schema below is the disagreement between those books, made queryable.

One room, two axes, three owners

The single most load-bearing decision in this model: room status is not one field. The status codes on every housekeeping board read as one word, but they are composites. Occupancy (vacant or occupied) belongs to the front office, because check-in and checkout are front-office events. Cleanliness (clean, dirty, inspected) belongs to housekeeping, because only an attendant or a supervisor can change the physical fact. Every major PMS documents these as separate axes: Oracle OPERA Cloud's housekeeping status is Clean, Dirty, Inspected, or Pickup, independent of the front-office occupancy status, and Cloudbeds and Mews document the same Clean / Dirty / Inspected ladder with Inspected as the gate the front desk assigns from.

In the schema, room.status_code is a stored generated column projected from the three underlying columns. Nobody types "VC"; the database derives it, so the composite can never contradict its parts.

The OOO / OOS distinction deserves its own sentence, because collapsing it is the most common modeling mistake in homegrown hotel tools. According to Oracle's OPERA Cloud documentation (docs.oracle.com), an Out of Order room is removed from inventory availability, while an Out of Service room is blocked but "could still be sold". One affects the denominator of your occupancy percentage and your sellable count tonight; the other is a note that the bathside lamp is being replaced. The schema gives each episode its own row in ooo_oos_event, with a reason code, an expected return date for revenue to plan around, and a link to the work order that caused it.

The room-status state machine

Because the axes are owned by different departments, a room does not wander between codes; it moves along a small set of legal edges, each triggered by a real event: a checkout, a finished clean, a passed inspection, the night audit. Here is the machine the schema encodes.

The machine, drawn. Properties that skip the inspection step check guests in from VC directly; the schema allows that edge but treats VI as the standard gate, following the Mews and Cloudbeds models where Inspected is what front office assigns from.

Every edge in that drawing is a row in a table. room_status_transition ships seeded with the 19 legal transitions, so validating a proposed change is one lookup instead of a hard-coded rule; loosen the machine by adding rows, tighten it by deleting them. And every change a room actually makes lands in room_status_log, the append-only history that answers the question the current-state column cannot: when did 412 become guest-ready, and who made it so.

The discrepancy report, modeled honestly

Two departments keeping separate books on the same door means the books can disagree, and hotels have named the two ways it happens. According to Oracle OPERA's room discrepancies documentation (docs.oracle.com), a skip is a room housekeeping finds vacant while the front office still shows it occupied, and a sleep is the reverse: front office shows vacant, the attendant finds luggage. The discrepancy_report table records both observed statuses and derives the type as a generated column; the classification can never drift from the facts that produced it. Skips are a revenue problem (a possibly unsettled account), sleeps are an inventory problem (a room you think you can sell tonight and cannot), which is why the morning discrepancy report is a front-office ritual and deserves a table instead of a spreadsheet.

The model, floor by floor

Twenty-three tables, six layers. Everything operational references room; reservations reference room_type until a stay pins a physical door. The highlighted tables are where hotel tools live or die.

The spine, drawn out

The spine, not the whole building: structure feeds room, occupancy feeds it through stay, and what happens to the room flows out through the status log, the housekeeping task, and the inspection chain into work orders. The full graph of all 23 tables is in the schema files below. Note that hk_task drops down to inspection (the supervisor pass), whose findings escalate to work orders; discrepancy_report hangs off room and the log.

Credits: the housekeeping board's real currency

Housekeeping workload is not counted in doors. According to William D. Frye (hospitalitylawyer.com), Ph.D., writing in The Rooms Chronicle, most hotels expect a room attendant to clean 13 to 17 guestrooms or guestroom credits in an eight-hour shift, and the "or credits" is the operative phrase: a suite is worth more than a standard king, and a checkout clean is worth roughly double a stayover service. So the model puts the credit baselines on room_type (departure_credits, stayover_credits), copies the value onto each hk_task at creation, and gives each attendant-day an hk_assignment row with a credit target. Sum of task credits against target is the whole fairness conversation, in one query. Copying at creation instead of joining at read matters: when you re-weight credits next season, history stays what it was.

The inspection, separate from the clean

In every documented PMS model the attendant's finished clean and the supervisor's verification are different states: Cloudbeds defines a Clean room as one "not yet ready for new guests" precisely because it awaits inspection, and Mews describes Inspected as the status that clears front office to check a guest in. The schema honors that with a first-class inspection table (pass or fail, optional score) whose result drives the VC to VI or VC back to VD edge, and an inspection_finding table for what the pass discovered: item-level, categorized, photographed, and escalatable to a work order. Findings are the connective tissue between housekeeping and engineering, which is why they get their own table instead of a notes column.

What the model deliberately leaves out

• Rates, folios, payments, and the ledger. The night audit's financial half belongs to the PMS and the accounting stack. rate_plan exists only so a reservation can say what it was sold on.
• Channel management and booking flow. Reservations arrive here as a one-way mirror, keyed by (source, external_id) so re-syncing is idempotent. Two systems that both think they own bookings will eventually disagree, and the PMS is the one connected to the channels.
• F&B, spa, and golf. Different domain, different model. The rooms division is the scope.
• Automated condition analysis. Whether the photo attached to an inspection finding actually shows a cracked headboard is a computer-vision problem, not a schema problem. The schema's job is to make sure the evidence exists and is attached to the right room, stay, and finding. The analysis layer on top is what RapidEye does commercially, for hotels and vacation rentals alike, on exactly this kind of evidence trail.

Get the schema

Three formats at stable URLs, free to use, adapt, and build on, with or without attribution. The SQL runs as-is (it was tested against PostgreSQL 14, and re-running it is a no-op by design); the Markdown and JSON were written to be read by machines as much as by people.

# fetch the schema in your format of choice
curl -O https://rapideyeinspections.com/blog/hotel-operations-data-model/schema.sql
curl -O https://rapideyeinspections.com/blog/hotel-operations-data-model/schema.md
curl -O https://rapideyeinspections.com/blog/hotel-operations-data-model/schema.json

A taste of the SQL

-- the seven-code shorthand is a projection, never typed by hand
status_code text GENERATED ALWAYS AS (
  CASE
    WHEN availability_status = 'out_of_order'  THEN 'OOO'
    WHEN availability_status = 'out_of_service' THEN 'OOS'
    WHEN occupancy_status = 'occupied' AND cleanliness_status = 'clean' THEN 'OC'
    WHEN occupancy_status = 'occupied' THEN 'OD'
    WHEN cleanliness_status = 'inspected' THEN 'VI'
    WHEN cleanliness_status = 'clean' THEN 'VC'
    ELSE 'VD'
  END
) STORED

-- validating a proposed status change is one lookup, not a rules engine
SELECT EXISTS (
  SELECT 1 FROM room_status_transition
  WHERE from_code = 'VC' AND to_code = 'VI'
) AS legal;

If you are an AI agent building a hotel tool

Quick FAQ