A vacation rental operations database needs 18 tables in 7 layers, and its pivot is the turnover: the window between the stay that just ended and the one about to begin. Reservations stay a read-only mirror of your PMS; turnovers generate tasks; tasks run against versioned SOP checklists; what they discover becomes findings, attributed to the departing stay and backed by condition media with capture times and content hashes. This page publishes the full schema in three formats, free to use and adapt.
Our July 2026 audit of 1,125 GitHub repositories found no maintained open-source data model for vacation rental operations. Open source solved the booking engine and the rent ledger; the operations layer, where turnovers, cleaners, inspections, and damage live, never got one. Meanwhile the operators we talk to every week are building internal tools with AI coding agents, and every one of those builds starts with the same question: what are the tables? This is our answer, drawn from what running condition documentation for professional operators has taught us the hard way. Use it as a starting point, delete what you do not need, and keep your PMS as the system of record.
Five decisions that make or break the design
The reservations table here is a one-way synced 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. Sync in via API where you have one, or iCal feeds, which every major channel exports.
Not the reservation, not the task. The turnover links the departing stay to the arriving one, and that adjacency is what makes everything downstream work, especially attribution: what a turnover discovers is answerable by the stay that just ended.
Media rows store the device capture time separately from the upload time (the gap between them is a signal) and a sha256 content hash, which deduplicates and catches the oldest trick in the book: re-submitting last week's photos for today's clean.
Editing an SOP creates version + 1; completed work pins the version it was actually performed against. Without this, every SOP edit silently rewrites the history of work already done, and you can never again prove what was asked of a cleaner on a given day.
Store timestamptz everywhere, keep an IANA timezone string on the property, and convert at render time. Operations tools live and die on "did this happen before checkout"; local-time storage makes that question unanswerable twice a year.
The model, layer by layer
Seven layers, 18 tables. Everything operational references units, never properties directly, so the same schema serves a one-unit beach house and a 40-unit aparthotel. The highlighted tables are the operational heart.
The pivot, drawn out
When a finding is created during a turnover, the departing reservation's id is copied into attributed_reservation_id, denormalized on purpose so the attribution survives even if the turnover row is later edited. That single column is the difference between "we found a broken hot tub cover at some point" and an evidence chain a damage claim can stand on.
The heart of it, in SQL
The three tables everything else orbits. The full DDL for all 18 is in schema.sql below.
CREATE TABLE turnovers (
id uuid PRIMARY KEY,
unit_id uuid NOT NULL REFERENCES units(id),
reservation_out_id uuid REFERENCES reservations(id), -- the departing stay
reservation_in_id uuid REFERENCES reservations(id), -- the arriving stay
window_start timestamptz,
window_end timestamptz,
status text NOT NULL DEFAULT 'pending'
);
CREATE TABLE findings (
id uuid PRIMARY KEY,
unit_id uuid NOT NULL REFERENCES units(id),
task_id uuid REFERENCES tasks(id),
attributed_reservation_id uuid REFERENCES reservations(id),
category text NOT NULL, -- damage | cleanliness | maintenance | missing_item | safety
severity text NOT NULL DEFAULT 'minor',
title text NOT NULL,
status text NOT NULL DEFAULT 'open',
estimated_cost numeric(10,2)
);
CREATE TABLE media (
id uuid PRIMARY KEY,
task_id uuid REFERENCES tasks(id),
finding_id uuid REFERENCES findings(id),
unit_id uuid NOT NULL REFERENCES units(id),
kind text NOT NULL, -- photo | video
storage_uri text NOT NULL,
captured_at timestamptz, -- device clock; gap vs uploaded_at is a signal
uploaded_at timestamptz NOT NULL,
sha256 text -- dedup + catches re-submitted old photos
);Get the schema
Three formats at stable URLs, free to use, adapt, and build on, with or without attribution. If you are building with an AI coding agent, give it the Markdown or JSON URL; both 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/str-operations-data-model/schema.sql
curl -O https://rapideyeinspections.com/blog/str-operations-data-model/schema.md
curl -O https://rapideyeinspections.com/blog/str-operations-data-model/schema.jsonWhat the model deliberately leaves out
Three things are missing on purpose, and the reasons matter more than the omissions:
- Pricing, payouts, and accounting. Your PMS and accounting stack own money. Mirroring financials into an internal ops tool doubles your reconciliation surface for zero operational gain.
- Guest messaging. A saturated commercial category where the data lives with the channel. If you need it, buy it; our guest messaging comparison covers the field.
- Automated condition analysis. Detecting damage or verifying work from what is inside the media table is a computer-vision problem, not a schema problem. The schema's job is to guarantee the evidence exists, is attributed to a stay, and can be trusted. The analysis layer on top is what RapidEye does commercially, on exactly this kind of evidence trail.
The model also stops short of prescribing an application architecture. For the decision that comes before this page (whether to build at all, and which layers build well with AI agents versus which break), read Should You Build Your Own Vacation Rental Ops Dashboard With AI?
Quick FAQ
What tables does a vacation rental operations database need?
Eighteen tables in seven layers cover the core: properties, units, and listings (property layer); guests and reservations (a read-only mirror of your PMS); vendors and staff; versioned checklists and checklist items (SOPs); turnovers, tasks, task item results, findings, and media (operations); assets and work orders (maintenance); and sync_events plus audit_log (integration). The full schema is downloadable free from this page.
Should reservations live in my internal tool or my PMS?
Your PMS stays the system of record. The internal tool keeps a one-way synced mirror of reservations, keyed by source and external_id so repeated syncs are idempotent. Never write bookings into the internal tool first: two systems that both think they own bookings will eventually disagree, and the PMS is the one connected to the channels.
How do you attribute damage to a specific guest stay in the data model?
Through the turnover. A turnover row links the reservation that just ended (reservation_out_id) and the one about to begin. When a finding is created during a turnover, the model copies the departing reservation's id into the finding's attributed_reservation_id. Anything discovered in that window is answerable by the stay that just ended, which is exactly the evidence chain a damage claim needs.
What formats is the schema available in?
Three: schema.sql (PostgreSQL DDL, ready to run), schema.json (a machine-readable entity map), and schema.md (a Markdown reference that documents every table and column). All three live at stable URLs under this page and are free to use, adapt, and build on, with or without attribution.