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

Source: https://rapideyeinspections.com/blog/str-operations-data-model/
Also available: [schema.sql](https://rapideyeinspections.com/blog/str-operations-data-model/schema.sql) (PostgreSQL DDL) and [schema.json](https://rapideyeinspections.com/blog/str-operations-data-model/schema.json) (machine-readable entity map).
Published by RapidEye Research, July 2026. Free to use, adapt, and build on, with or without attribution.

A reference data model for building internal vacation rental operations tools: turnovers, cleaning and inspection tasks, SOP checklists, damage findings, condition media, assets, and work orders. 18 tables in 7 layers.

## Design principles

1. **Your PMS stays the system of record for reservations.** The booking layer here is a one-way synced mirror, keyed by `(source, external_id)` so ingestion is idempotent. Never write bookings here first; sync them in from the PMS or from channel iCal feeds.
2. **The turnover is the pivot entity.** It sits between the reservation that just ended (`reservation_out_id`) and the one about to begin (`reservation_in_id`). This adjacency is what makes damage attribution possible: what a turnover discovers is answerable by the stay that just ended.
3. **Condition evidence is first-class.** `media` rows carry device capture time separately from upload time (the gap is a signal) and a `sha256` content hash (deduplication, and it catches re-submitted old photos).
4. **Checklists are versioned and immutable.** Editing an SOP creates a new version; completed work pins the version it was performed against, so history never silently rewrites.
5. **All timestamps are UTC** (`timestamptz`). The property carries its IANA timezone; convert at render time, never at storage time.

## Layer 1: Property

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

### units
One property has one or more units; a single beach house still gets exactly one unit row. All operations tables reference units, never properties directly, so the same model serves a 1-unit house and a 40-unit aparthotel.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| property_id | uuid | FK properties, required |
| name | text | `Main`, `Unit 2B`, `Casita` |
| bedrooms, bathrooms | numeric | |
| max_occupancy | int | |
| floor_area_m2 | numeric | |
| status | text | |

### listings
A unit's presence on a channel (Airbnb, Vrbo, direct). The `ical_url` is the universal fallback: every major channel exports one, so a tool with nothing but this table can still see bookings.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| channel | text | `airbnb` / `vrbo` / `booking` / `direct` / `other` |
| external_id | text | listing id on the channel; unique with channel |
| url, ical_url | text | |
| status | text | |

## Layer 2: Booking (synced mirror)

### guests
Keep guest PII minimal: 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 |

### reservations
| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| listing_id | uuid | FK listings |
| guest_id | uuid | FK guests |
| status | text | `confirmed` / `cancelled` / `completed` / `no_show` |
| check_in, check_out | timestamptz | required; check_out > check_in |
| adults, children, pets | int | |
| channel | text | |
| source, external_id | text | required; unique together (idempotent sync) |
| synced_at | timestamptz | last time the mirror refreshed this row |

Index: `(unit_id, check_in, check_out)` for calendar queries.

## Layer 3: People

### vendors
| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| name | text | required |
| kind | text | `cleaning` / `maintenance` / `inspection` / `other` |
| email, phone, status | text | |

### staff
Everyone who performs or manages work. In-house staff have `vendor_id` null; vendor personnel reference their company.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| vendor_id | uuid | FK vendors, nullable |
| full_name | text | required |
| role | text | `cleaner` / `inspector` / `maintenance` / `manager` / `admin` |
| email, phone, status | text | |

## Layer 4: SOPs / checklists (versioned)

### checklists
One row is one VERSION of one SOP. Editing inserts version + 1 and flips `is_current`; old versions are never mutated.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| name | text | `Standard turnover clean` |
| kind | text | `cleaning` / `inspection` / `maintenance` / `custom` |
| version | int | unique with name |
| is_current | boolean | |

### checklist_items
| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| checklist_id | uuid | FK checklists, required |
| section | text | `Kitchen`, `Exterior` |
| position | int | order within the checklist |
| instruction | text | `Photograph all four walls` |
| requires_photo, requires_video | boolean | evidence requirements per step |

## Layer 5: Operations (the heart)

### turnovers
The window between a departing stay and an arriving one. Both reservation references are nullable (a first-ever turnover has no departure), but when `reservation_out_id` is present, everything discovered during the turnover is attributable to that stay.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| reservation_out_id | uuid | FK reservations; the stay that just ended |
| reservation_in_id | uuid | FK reservations; the stay about to begin |
| window_start, window_end | timestamptz | usually checkout time and next check-in |
| status | text | `pending` / `scheduled` / `in_progress` / `done` / `failed_inspection` |

### tasks
One turnover usually produces several tasks (clean, inspection, maintenance visit). Tasks can also exist outside a turnover (deep cleans, preventive maintenance), so `turnover_id` is nullable but `unit_id` is not.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| turnover_id | uuid | FK turnovers, nullable |
| kind | text | `cleaning` / `inspection` / `maintenance` / `delivery` / `custom` |
| status | text | `open` / `assigned` / `in_progress` / `done` / `verified` / `cancelled` |
| assignee_id | uuid | FK staff |
| checklist_id | uuid | FK checklists; pins the exact SOP VERSION performed |
| due_at, started_at, completed_at | timestamptz | |
| verified_by_id | uuid | FK staff; who checked the work |
| notes | text | |

### task_item_results
Per-item completion state for a task performed against a checklist. Unique on `(task_id, checklist_item_id)`.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| task_id | uuid | FK tasks, required |
| checklist_item_id | uuid | FK checklist_items, required |
| status | text | `done` / `skipped` / `flagged` |
| note | text | |
| completed_at | timestamptz | |

### findings
Anything discovered that needs attention. `attributed_reservation_id` is copied from the parent turnover's `reservation_out_id` at creation time, denormalized on purpose so attribution survives later edits to the turnover.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| task_id | uuid | FK tasks |
| attributed_reservation_id | uuid | FK reservations; the stay that answers for this |
| category | text | `damage` / `cleanliness` / `maintenance` / `missing_item` / `safety` |
| severity | text | `minor` / `moderate` / `major` / `urgent` |
| title | text | required |
| description | text | |
| status | text | `open` / `acknowledged` / `in_repair` / `resolved` / `claimed` / `waived` |
| estimated_cost | numeric(10,2) | |
| resolved_at | timestamptz | |

### media
Condition evidence. `captured_at` (device clock / EXIF) is stored separately from `uploaded_at`; the gap between them is itself a signal. `sha256` deduplicates and catches re-submitted old photos.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| task_id | uuid | FK tasks, nullable |
| finding_id | uuid | FK findings, nullable |
| unit_id | uuid | FK units, required |
| kind | text | `photo` / `video` |
| storage_uri | text | `s3://...`; never a public URL |
| room | text | `Kitchen`, `Bedroom 2`, `Exterior` |
| captured_at | timestamptz | from the device |
| uploaded_at | timestamptz | server receipt time |
| captured_by | uuid | FK staff |
| sha256 | text | content hash |

## Layer 6: Assets and maintenance

### assets
| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| category | text | `appliance` / `hvac` / `hot_tub` / `pool` / `furniture` / `other` |
| name | text | `Samsung washer` |
| make, model, serial_no | text | |
| installed_on, warranty_until | date | |
| status | text | `in_service` / `needs_repair` / `retired` |

### work_orders
The money-and-labor half of a problem; a finding is the discovery half. Preventive work has no finding, so `finding_id` is nullable.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| unit_id | uuid | FK units, required |
| asset_id | uuid | FK assets |
| finding_id | uuid | FK findings |
| vendor_id | uuid | FK vendors |
| title | text | required |
| priority | text | `low` / `normal` / `high` / `emergency` |
| status | text | `open` / `scheduled` / `in_progress` / `done` / `cancelled` |
| scheduled_for, completed_at | timestamptz | |
| cost | numeric(10,2) | |
| invoice_ref | text | |

## Layer 7: Integration

### sync_events
Append-only inbox for everything external systems send (webhooks, iCal poll diffs, API sync runs). Ingest first, process after. Unique on `(source, external_event_id)` so retries and replays are harmless.

| column | type | notes |
|---|---|---|
| id | uuid | primary key |
| source | text | `guesty` / `airbnb_ical` / `hostaway` / ... |
| event_type | text | `reservation.updated`, `calendar.polled`, ... |
| external_event_id | text | unique with source |
| payload | jsonb | raw, untouched |
| received_at, processed_at | timestamptz | |
| error | text | |

### audit_log
Who changed what, when. Populated from application code on every mutation of operational data; records that end up attached to damage claims need an audit trail.

| column | type | notes |
|---|---|---|
| id | bigint | identity primary key |
| actor_id | uuid | staff id, or null for system |
| entity | text | `finding`, `task`, ... |
| entity_id | uuid | |
| action | text | `create` / `update` / `status_change` / `delete` |
| changes | jsonb | `{"status": ["open", "resolved"]}` |
| at | timestamptz | |

## Common queries the model is shaped for

```sql
-- Today's turnovers with their departing guest, for a property
SELECT t.id, u.name AS unit, r.check_out, g.full_name
FROM turnovers t
JOIN units u ON u.id = t.unit_id
LEFT JOIN reservations r ON r.id = t.reservation_out_id
LEFT JOIN guests g ON g.id = r.guest_id
WHERE u.property_id = $1
  AND t.window_start::date = current_date;

-- Open damage findings attributed to a specific stay (claim evidence)
SELECT f.*, m.storage_uri, m.captured_at
FROM findings f
LEFT JOIN media m ON m.finding_id = f.id
WHERE f.attributed_reservation_id = $1
  AND f.category = 'damage';

-- Cleaner completion rate against required-photo checklist items, last 30 days
SELECT s.full_name,
       count(*) FILTER (WHERE r.status = 'done')::float / count(*) AS completion
FROM task_item_results r
JOIN tasks t ON t.id = r.task_id
JOIN staff s ON s.id = t.assignee_id
JOIN checklist_items ci ON ci.id = r.checklist_item_id
WHERE ci.requires_photo
  AND t.completed_at > now() - interval '30 days'
GROUP BY s.full_name;
```

## What this model deliberately leaves out

- **Pricing, payouts, accounting.** Your PMS and accounting stack own money; mirroring it here doubles your reconciliation surface for no operational gain.
- **Guest messaging.** Saturated commercial category, and the data lives with the channel.
- **Automated condition analysis.** Detecting damage or verification issues from the `media` table's contents is a computer-vision problem, not a schema problem. The schema's job is to make sure the evidence exists, is attributed, and is trustworthy. (That analysis layer is what RapidEye provides commercially on top of exactly this kind of evidence trail.)