# The Hotel Operations Data Model (reference schema v0.1)

Source: https://rapideyeinspections.com/blog/hotel-operations-data-model/
Also available: [schema.sql](https://rapideyeinspections.com/blog/hotel-operations-data-model/schema.sql) (PostgreSQL DDL, tested against PostgreSQL 14) and [schema.json](https://rapideyeinspections.com/blog/hotel-operations-data-model/schema.json) (machine-readable entity map).
Companion model for vacation rentals: [The STR Operations Data Model](https://rapideyeinspections.com/blog/str-operations-data-model/).
Published by RapidEye Research, August 2026. Free to use, adapt, and build on, with or without attribution.

A reference data model for building internal hotel operations tools: rooms and the room-status state machine, the housekeeping board and its credit-based workload, supervisor inspections, discrepancy reports, work orders and preventive maintenance, incidents, lost and found, and linen par levels. 23 tables in 6 layers.

## Design principles

1. **Occupancy and cleanliness are two separate axes owned by two separate departments.** Front office owns `occupied`/`vacant`; housekeeping owns `clean`/`dirty`/`inspected` (the axis Oracle OPERA, Mews, and Cloudbeds all document as the housekeeping status). The familiar composite codes (VC, VD, VI, OC, OD) are derived by a generated column, never typed by hand. Discrepancy reports exist precisely because the two axes can disagree.
2. **Out of Order is not Out of Service.** Per Oracle OPERA's documented model, an OOO room is removed from inventory availability; an OOS room is blocked but remains sellable. The schema encodes them as distinct availability states with their own episode records, never as a shared "broken" flag.
3. **Room status is an event log, not a column.** The `room` row carries current state for fast board queries; `room_status_log` is the append-only history that makes "when did this room actually become guest-ready" answerable.
4. **Credits are the workload currency.** Housekeeping boards balance credits, not door counts; a suite is not a standard king, and a checkout clean is not a stayover service. Credit values live on `room_type` and are copied onto each task at creation so history survives later re-weighting.
5. **The inspection is a first-class entity, separate from the clean.** A clean makes a room Clean; only a supervisor inspection makes it Inspected. What inspections discover becomes findings, and findings escalate to work orders.
6. **Your PMS stays the system of record for reservations.** The `guest` and `reservation` tables are a one-way synced mirror keyed by `(source, external_id)` so ingestion is idempotent. Never write bookings here first.

## The room-status state machine

Seven codes, three underlying facts. `status_code` on `room` is a stored generated column projecting `occupancy_status` x `cleanliness_status` x `availability_status`:

| Code | Meaning | Underlying state |
|---|---|---|
| VC | Vacant Clean | vacant + clean, awaiting inspection |
| VD | Vacant Dirty | vacant + dirty; every checkout lands here |
| VI | Vacant Inspected | vacant + inspected; the only state front desk should assign from where the inspection step runs |
| OC | Occupied Clean | occupied + serviced today |
| OD | Occupied Dirty | occupied + service due |
| OOO | Out of Order | removed from inventory availability; not sellable |
| OOS | Out of Service | flagged (minor defect) but remains in sellable inventory |

Legal transitions (seeded into `room_status_transition`, so an application or agent can validate a proposed change with one lookup):

| From | To | Trigger |
|---|---|---|
| OC | OD | night audit rollover: an occupied room owes service each day |
| OD | OC | stayover service completed |
| OC | VD | guest checkout |
| OD | VD | guest checkout |
| VD | VC | departure clean completed by room attendant |
| VC | VI | supervisor inspection passed |
| VC | VD | supervisor inspection failed, room returned to the board |
| VI | OC | guest check-in |
| VC | OC | guest check-in at properties not running the inspection step |
| VI | VD | inspected room sat vacant too long and reverts to dirty |
| VD / VC / VI | OOO | room removed from inventory (maintenance, renovation) |
| OOO | VD | room returned to inventory; it always re-enters dirty |
| VD / VC / VI | OOS | room flagged out of service but left sellable |
| OOS | VD | out-of-service flag cleared; room re-enters dirty |
| OOS | OC | room sold while out of service: OOS rooms remain sellable |

Notes: there is no OI (occupied inspected) state; inspection applies to vacant rooms. OPERA's optional Pickup status (a light-touch refresh) is treated as `dirty` on the cleanliness axis; add a fourth axis value if your operation runs it.

## Layer 1: Property (5 tables)

### property
| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| name | text | required |
| brand | text | flag / brand, if any |
| address_line1, address_line2, city, region, postal_code | text | |
| country_code | text | ISO 3166-1 alpha-2 |
| timezone | text | IANA, e.g. `America/Denver`, required |
| source, external_id | text | which PMS this property syncs from + its id there; unique together |
| status | text | `active` / `pre_opening` / `closed` |
| created_at, updated_at | timestamptz | |

### floor_zone
Floors, wings, towers, and housekeeping zones. Boards are built per zone, attendants are assigned per zone, and assets (elevators, ice machines) can live in a zone without living in a room.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| name | text | `Floor 4`, `North Wing`, `Tower B 12-18` |
| kind | text | `floor` / `wing` / `tower` / `building` / `zone` |
| sort_order | int | |

### room_type
Carries the housekeeping credit baseline, because workload is a property of the room type: a suite takes longer than a standard king, and a checkout clean takes roughly twice a stayover service.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| code | text | `KNG`, `DBL`, `STE`; unique per property |
| name, description | text | |
| max_occupancy | int | |
| bed_config | text | `1 King`, `2 Queens` |
| departure_credits | numeric | credit value of a checkout clean, default 1.0 |
| stayover_credits | numeric | credit value of a stayover service, default 0.5 |

### room
Current state is denormalized here for fast board queries; the authoritative history lives in `room_status_log`. `status_code` is GENERATED, never written by hand.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| floor_zone_id | uuid | FK floor_zone |
| room_type_id | uuid | FK room_type, required |
| room_number | text | text, not int: `204A`, `PH1`; unique per property |
| connects_to | text | connecting room number, if any |
| occupancy_status | text | `vacant` / `occupied` (front office's axis) |
| cleanliness_status | text | `clean` / `dirty` / `inspected` (housekeeping's axis) |
| availability_status | text | `in_service` / `out_of_service` / `out_of_order` |
| status_code | text | generated: `VC` / `VD` / `VI` / `OC` / `OD` / `OOO` / `OOS` |
| status_changed_at | timestamptz | |

### rate_plan
Deliberately light: operations is this model's focus. Exists so a reservation can say what it was sold on, nothing more. Pricing, restrictions, and yielding stay in the PMS/RMS.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| code | text | `BAR`, `CORP`, `AAA`; unique per property |
| name, description | text | |

## Layer 2: Guests and stays (3 tables, synced mirror)

### guest
Minimal PII on purpose: an operations tool needs to attribute a stay, not run a CRM.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| full_name, email, phone | text | minimal on purpose |
| source, external_id | text | unique together |

### reservation
One-way mirror of the PMS reservation, keyed by `(source, external_id)` so re-syncing is idempotent.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| guest_id | uuid | FK guest |
| room_type_id | uuid | FK room_type (reservations sell a type, not a room) |
| rate_plan_id | uuid | FK rate_plan |
| confirmation_number | text | |
| source, external_id | text | required; unique together |
| arrival_date, departure_date | date | required |
| adults, children | int | |
| status | text | `booked` / `in_house` / `checked_out` / `cancelled` / `no_show` |
| synced_at | timestamptz | last sync touch |

### stay
The physical occupancy of one room. A reservation with a room move has two stay rows; occupancy questions ("who was in 412 on the 9th") join through `stay`, never through `reservation`, which only knows the room type.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| reservation_id | uuid | FK reservation, required |
| room_id | uuid | FK room, required |
| checked_in_at | timestamptz | required |
| checked_out_at | timestamptz | null while in-house |
| move_reason | text | set on the new row after a room move |

## Layer 3: Room status (4 tables)

### room_status_log
Append-only history of every status change. The `room` row answers "what is 412 now"; this table answers "when did 412 become guest-ready, who made it so, and through which states did it pass".

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| room_id | uuid | FK room, required |
| from_code, to_code | text | one of the seven codes; to_code required |
| changed_at | timestamptz | |
| changed_by | uuid | FK staff |
| source | text | `app` / `pms_sync` / `night_audit` / `housekeeping` / `front_office` / `maintenance` |
| note | text | |

### room_status_transition
The legal transitions of the state machine, as data (see the table above), so an application or AI agent can validate a proposed change with one lookup instead of hard-coding the rules. Seeded by schema.sql; add rows to loosen the machine, delete rows to tighten it.

| column | type | notes |
|---|---|---|
| from_code, to_code | text | primary key together |
| trigger_event | text | what legitimately causes this edge |

### ooo_oos_event
One row per OOO or OOS episode. `kind` encodes the distinction that matters to revenue: `out_of_order` rooms are deducted from inventory availability; `out_of_service` rooms remain sellable while flagged.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| room_id | uuid | FK room, required |
| kind | text | `out_of_order` / `out_of_service` |
| reason_code | text | `maintenance` / `renovation` / `deep_clean` / `damage` / `pest_control` / `other` |
| description | text | |
| starts_at | timestamptz | |
| expected_return_at | timestamptz | what revenue management plans around |
| ended_at | timestamptz | null while the episode is open |
| work_order_id | uuid | FK work_order |
| created_by | uuid | FK staff |

### discrepancy_report
Front office and housekeeping each own one axis of room status, so the two can disagree. `discrepancy_type` is generated: `skip` when FO says occupied and HK finds vacant; `sleep` when FO says vacant and HK finds occupied.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| room_id | uuid | FK room, required |
| business_date | date | required |
| fo_status | text | `vacant` / `occupied` (what the front office system shows) |
| hk_status | text | `vacant` / `occupied` (what the attendant physically found) |
| discrepancy_type | text | generated: `skip` / `sleep` / `none` |
| detected_by | uuid | FK staff |
| detected_at, resolved_at | timestamptz | |
| resolution_note | text | |

## Layer 4: Housekeeping (6 tables)

### staff
| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| full_name | text | required |
| role | text | `room_attendant` / `hk_supervisor` / `exec_housekeeper` / `front_desk` / `maintenance` / `engineer` / `manager` / `other` |
| active | boolean | |

### hk_assignment
One attendant's section for one business date: the row a printed board line becomes. `credit_target` is what a full shift is at this property; the sum of task credits pointing here against the target makes over-assignment visible.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| business_date | date | required; unique with staff_id + shift |
| staff_id | uuid | FK staff, required |
| floor_zone_id | uuid | FK floor_zone |
| shift | text | `am` / `pm` / `turndown` / `overnight` |
| credit_target | numeric | e.g. 15.0 |

### hk_task
One unit of housekeeping work against one room. `credit_value` is copied from `room_type` at creation, not joined at read time, so re-weighting credits next season does not rewrite the history of work already done.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| room_id | uuid | FK room, required |
| assignment_id | uuid | FK hk_assignment |
| stay_id | uuid | FK stay (the stay being serviced, if any) |
| business_date | date | required |
| task_type | text | `departure_clean` / `stayover_service` / `turndown` / `deep_clean` / `touch_up` / `correction` |
| credit_value | numeric | copied at creation |
| status | text | `pending` / `in_progress` / `done` / `verified` / `dnd` / `refused` / `skipped` |
| started_at, completed_at | timestamptz | |
| note | text | |

### inspection
The supervisor pass. A clean makes a room Clean; only this makes it Inspected. `result` drives the state machine: pass is the VC to VI edge, fail is VC back to VD with the task reopened as a correction.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| room_id | uuid | FK room, required |
| hk_task_id | uuid | FK hk_task (the clean being verified) |
| inspector_id | uuid | FK staff, required |
| inspected_at | timestamptz | |
| result | text | `pass` / `fail` |
| score | numeric | optional 0-100 against the property standard |
| note | text | |

### inspection_finding
What the inspection found, item-level. Findings survive the inspection that raised them: a chipped headboard found today is the work order verified next week.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| inspection_id | uuid | FK inspection, required |
| category | text | `cleanliness` / `damage` / `maintenance` / `missing_item` / `safety` / `other` |
| severity | text | `minor` / `major` / `critical` |
| location | text | `bathroom`, `entry`, `balcony` |
| description | text | required |
| photo_uri | text | evidence beats adjectives |
| work_order_id | uuid | FK work_order (escalation) |
| resolved_at | timestamptz | |

### linen_par_level
Par levels per room type: how many of each linen item a made-up room carries, times the number of full sets the property circulates (in the room, in the laundry, on the shelf). Par math is what keeps a full house from becoming a towel shortage on day three.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| room_type_id | uuid | FK room_type, required; unique with item |
| item | text | `bath_towel`, `king_sheet_set`, `bath_mat` |
| per_room_qty | int | how many one made-up room carries |
| par_sets | numeric | full sets in circulation, default 3.0 |

## Layer 5: Maintenance (3 tables)

### asset
Things that break. An asset can live in a room (PTAC unit in 412), in a zone (Floor 4 ice machine), or in neither (the boiler).

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| room_id | uuid | FK room |
| floor_zone_id | uuid | FK floor_zone |
| name | text | required |
| category | text | `hvac` / `plumbing` / `electrical` / `appliance` / `furniture` / `fixture` / `elevator` / `life_safety` / `laundry` / `kitchen` / `other` |
| manufacturer, model, serial_number | text | |
| installed_on, warranty_expires_on | date | |
| status | text | `in_service` / `out_of_service` / `retired` |

### work_order
The maintenance unit of work. `source` records where it came from, because "how much of engineering's day is reactive vs preventive" is a question worth answering.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| room_id | uuid | FK room |
| asset_id | uuid | FK asset |
| reported_by, assigned_to | uuid | FK staff |
| source | text | `inspection_finding` / `hk_task` / `guest_report` / `pm_schedule` / `discrepancy` / `ad_hoc` |
| priority | text | `urgent` / `high` / `routine` / `low` |
| status | text | `open` / `assigned` / `in_progress` / `on_hold` / `completed` / `verified` / `cancelled` |
| title | text | required |
| description | text | |
| due_at, completed_at | timestamptz | |

### pm_schedule
Preventive maintenance cycles: quarterly PTAC filters, annual deep cleans, monthly life-safety checks. A scheduler walks this table and creates `work_order` rows with `source = 'pm_schedule'`. Rooms cycle through PM too, which is the most common planned reason a room goes OOO.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| asset_id | uuid | FK asset |
| room_type_id | uuid | FK room_type (for per-room cycles) |
| name | text | `Quarterly PTAC filter change` |
| description | text | |
| frequency_days | int | 90 for quarterly, 365 for annual |
| takes_room_ooo | boolean | whether this cycle pulls the room from inventory |
| last_completed_on, next_due_on | date | |
| active | boolean | |

## Layer 6: Incidents and recovery (2 tables)

### incident
Anything that needs a record: guest injury, damage discovered mid-stay, security, smoking, biohazard. Linking to `stay` (not just room) is what lets a damage incident carry the "who was in the room" answer with it.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| room_id | uuid | FK room |
| stay_id | uuid | FK stay |
| occurred_at | timestamptz | required |
| category | text | `guest_injury` / `staff_injury` / `property_damage` / `security` / `theft` / `smoking` / `biohazard` / `noise` / `other` |
| severity | text | `minor` / `major` / `critical` |
| description | text | required |
| reported_by | uuid | FK staff |
| status | text | `open` / `under_review` / `resolved` / `closed` |
| resolution_note | text | |

### lost_found_item
Chain of custody for guest property. Status is a lifecycle, not a flag, because "we mailed it back on the 14th" is the sentence that ends the one-star review.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK property, required |
| room_id | uuid | FK room |
| stay_id | uuid | FK stay (whose stay it likely belongs to) |
| found_at | timestamptz | |
| found_by | uuid | FK staff |
| description | text | required |
| storage_location | text | `HK office shelf B` |
| status | text | `logged` / `claimed` / `returned` / `shipped` / `donated` / `disposed` |
| released_at | timestamptz | |
| released_note | text | |

## Example queries

The morning housekeeping board, with credits:

```sql
SELECT s.full_name, fz.name AS zone,
       count(t.id)                              AS rooms,
       sum(t.credit_value)                      AS credits,
       a.credit_target
FROM hk_assignment a
JOIN staff s        ON s.id = a.staff_id
LEFT JOIN floor_zone fz ON fz.id = a.floor_zone_id
LEFT JOIN hk_task t ON t.assignment_id = a.id
WHERE a.business_date = current_date AND a.shift = 'am'
GROUP BY s.full_name, fz.name, a.credit_target
ORDER BY credits DESC;
```

Rooms not yet guest-ready for tonight's arrivals:

```sql
SELECT r.room_number, r.status_code, rt.code AS room_type
FROM reservation res
JOIN room_type rt ON rt.id = res.room_type_id
JOIN room r       ON r.room_type_id = rt.id
WHERE res.arrival_date = current_date
  AND res.status = 'booked'
  AND r.status_code NOT IN ('VI','VC')
ORDER BY r.room_number;
```

Validate a proposed status change (the one lookup that replaces hard-coded rules):

```sql
SELECT EXISTS (
  SELECT 1 FROM room_status_transition
  WHERE from_code = 'VC' AND to_code = 'VI'
) AS legal;
```

Open discrepancies from this morning's report:

```sql
SELECT r.room_number, d.discrepancy_type, d.fo_status, d.hk_status, d.detected_at
FROM discrepancy_report d
JOIN room r ON r.id = d.room_id
WHERE d.business_date = current_date AND d.resolved_at IS NULL;
```

## Terminology grounding

- Housekeeping statuses Clean, Dirty, Inspected (and optional Pickup), and the OOO vs OOS inventory distinction: Oracle OPERA Cloud documentation, "Room Statuses" (docs.oracle.com).
- Dirty / Clean / Inspected / Out of service / Out of order as the operator-facing status set, with Inspected as the check-in gate: Mews room status documentation (mews.com).
- Clean vs Inspected as distinct conditions and the nightly occupied-to-dirty reset: Cloudbeds "Housekeeping room conditions" (cloudbeds.com).
- Skip and sleep discrepancy definitions: Oracle OPERA "Room Discrepancies" documentation (docs.oracle.com).
- The 13-17 guestrooms-or-credits-per-8-hour-shift workload norm: William D. Frye, Ph.D., The Rooms Chronicle, "Motivating room attendants to clean better and pay attention to detail" (Vol. 15, No. 5).