Docs.

Admin data model — reservations & finance

architecture/data-model.md

The reservations + finance schema ships in supabase/migrations/0001_baseline.sql (the full operational baseline). Migrations 0002–0005 layer on top: 0002 renames units→assets and areas→spaces (and reservation_items.area_id→space_id, assigned_unit_ids→assigned_asset_ids), dedupes items by SKU, and rebuilds settings as an append-only revision history (0007 later reshapes it into a generic append-only key/value store — keys namespaced by domain, e.g. pricing.*); 0003 hardens function security; 0004 adds soft-delete; 0005 consolidates the rest of the in-flight work (it was folded from seven files before push) — the office RPCs (create_reservation, assign_units, find_or_create_account, short_link_scan_leaderboard) + a reservation_reference_counters table, removal of the dead sheet-sync subsystem (renaming sync_source → audit_source), a 100% deposit default, the lost asset condition, removal of vetting/insurance tiering, dropping the vestigial COI columns, and the identity overhaul: clients becomes accounts (the single auth.users-linked identity for clients and staff) plus a client_profiles extension, with roles/account_roles (office RBAC) and account_verifications, reservations.client_id → account_id, and launch_subscribers → subscribers; stale object names from earlier renames are normalized too. All money is USD cents (the convention from items/assets/settings); COP is display-only, converted from settings.cop_per_usd_snapshot and snapshotted onto each reservation (fx_rate_snapshot) for reproducible invoices.

Why this differs from docs/architecture/rentals.md

That doc predates the live schema and is internally inconsistent (three different reservation state machines; *_cop columns; an availability enum the table definition lacks). The baseline schema reconciles those:

  • Currency: USD cents canonical (doc 01 used *_cop). Reversed.
  • State machine: one canonical enum (below) of the three variants.
  • Audit: audit_log + the fn_emit_audit trigger are part of the baseline; the operational tables don't reuse the generic trigger. See "Audit" below.

Tables added

  • accounts (was clients, renamed in 0005) — the single identity row for everyone. display_name, email (unique on lower(email)), phone, locale (en|es, was preferred_language), user_id (nullable → auth.users; null until first sign-in), source (how acquired), standing (neutral|blacklist — the old trust_tier; 0023 dropped the stored repeat value, now derived from paid reservations >= 2, and folded standard/unverified/verified into neutral). A client = an account with no role; staff carry office roles.
  • client_profiles — commercial extension of an account (account_id PK): company, tax_id, address, tags, internal_notes. Present only for accounts that are clients.
  • roles / account_roles — office RBAC. roles (key, label, domain, rank) seeds staff(10)/manager(20)/administrator(30) in domain office, plus editor (editorial). account_roles is the many-to-many grant (account_id, role_id, granted_by). Zero rows = a client/lead.
  • account_verifications — proof records (kind: email|phone|national_id|foreign_id|passport, identifier, file_uri, verified_at, verified_by, expires_at). A client is derived: an account with active email + phone + one government-ID verification. Replaces the single identity_verified_at/identity_doc_url columns.
  • reservations — reference (random R-XXXXXX, confusable-free alphabet, since 0013; older rows keep their R-YYYY-MMDD-NN), account_id (was client_id until 0005), status, planned + actual pickup_at/return_at, money (subtotal/discount/ damage_waiver/deposit/total_usd_cents), fx_rate_snapshot, intake context (project_context, how_heard), notes_*, contract_url, and a lifecycle timeline (held_at, confirmed_at — renamed from the old booked stamp in 0010 — picked_up_at, returned_at, settled_at, closed_at, cancelled_at).
  • reservation_items — one per item/kit/space on a reservation (reservation_items_target_check requires exactly one of item_id, kit_id, or space_id): qty, snapshot rates (rate_day/week_usd_cents_snapshot), line_total_usd_cents, assigned_asset_ids uuid[]. The assigned_asset_ids column was assigned_unit_ids and space_id was area_id until the 0002 units→assets / areas→spaces renames. Discounts/fees and other future per-reservation concerns are intended to live in their own dedicated tables, not as polymorphic line rows here.
  • payments — provider (stripe|wompi|cash|transfer), kind (deposit_hold|deposit_capture|deposit_release|balance_charge|refund| damage_charge), amount_usd_cents, provider_id, metadata.
  • claims — kind (damage|loss|late|cleaning|other), severity (cosmetic|functional|total_loss), status (draft|notified|accepted|disputed|closed), amount_usd_cents, photos.
  • asset_blackouts / space_blackouts — "production always wins" windows. asset_blackouts take a specific unit out of supply (asset_id NOT NULL; the table was item_blackouts with a nullable item_id until 0010 made blackouts asset-scoped — blacking out a whole item means a row per unit, the form's default); space_blackouts block a rentable studio space (space_id, formerly area_id) — independent of gear. The reservations calendar renders both, /office/reservations/blackouts has create/remove UI, and the overlap guard (getReservationConflicts) counts blacked-out units against an item's available supply.
  • condition_reports — pickup/return inspections, direction (out|in), checklist jsonb, photos, signature fields, booking_line_id → a reservation_items row. (Table only; UI deferred.)

assets.current_reservation_id is a baseline FK (on delete set null, constraint renamed to assets_current_reservation_fk in 0002); the office code sets/clears it on pickup/return.

Reservation state machine (canonical)

inquired ─▶ quoted ─▶ held ─▶ confirmed ─▶ returned ─▶ settled ─▶ closed
   │          │  └──────────────▶│             │          ▲  │       ▲
   │          │  (quoted skips straight        ▼          │  ▼       │
   │          │       to confirmed)            └─────▶ disputed ─────┘
   └─▶ cancelled ◀──┴──────┴─────┘        (disputed → settled | closed)

Exact edges (RESERVATION_TRANSITIONS in src/lib/office/reservations.ts): inquired → quoted|cancelled; quoted → held|confirmed|cancelled; held → confirmed|cancelled; confirmed → returned|cancelled; returned → settled|disputed; settled → closed|disputed; disputed → settled|closed. closed and cancelled are terminal. The reservation detail page renders exactly the allowed next states as buttons.

Gear being out is not a status (0010 dropped the old gear-out and inspection statuses, and renamed booked → confirmed, returning → returned): it's scan state — picked_up_at + asset assignments inside the reservation window. The first pickup scan (staff-app API) stamps picked_up_at/actual_pickup_at and the reservation stays confirmed; the last return scan flips it to returned. Inspection happens while the reservation sits in returned. A reservation still confirmed past its return_at is overdue.

Side effects on transition (transitionReservation):

  • entering returned → sets returned_at/actual_return_at and releases any assets still bound (current_reservation_id = null) — assets are assigned by pickup scans via the staff-app API, not by a status change.
  • entering cancelled → releases assets, stores cancel_reason.
  • each transition writes a state-change audit row via writeAudit().

ACTIVE_STATUSES = held, confirmed, returned (used by the dashboard + finance "deposits held").

Pricing / deposit engine (src/lib/office/pricing.ts)

  • rental days = ceil((return − pickup) / 1 day), min 1.
  • line cost = weeks stack at the week rate; the remainder is min(remDays × dayRate, weekRate) (so you never pay more than the next tier up). Week rate falls back to dayRate × settings.week_multiplier.
  • deposit = max(deposit_percent × Σ replacement_value, deposit_minimum).
  • Rates are NULL across the catalog today, so subtotal is usually $0 and the deposit (from replacement value) carries the quote. The UI flags unpriced lines.

Audit

  • Actor = account. Every "who acted" column references accounts(id): audit_log.actor_account_id (renamed from actor_user_id; NOT NULL, FK to accounts), plus settings.created_by_account_id, asset_blackouts/ space_blackouts.created_by_account_id, and condition_reports .inspector_account_id (all nullable, FK ON DELETE SET NULL). The app writes the actor's accountId (OfficeUser.accountId), not the auth-user id.
  • System account. Any action not performed by a signed-in user — a trigger-emitted row, an agent/automation, or the dev bypass — is attributed to the system account (accounts row, all-zeros UUID, administrator@studio.chat). writeAudit() coalesces a null actor to it, and the trigger defaults to it; that's why actor_account_id can be NOT NULL.

Two audit paths

1. Operational tables — writeAudit() (the primary, rich path). The operational tables (reservations, payments, claims, plus role/account changes, links, etc.) have no generic trigger. Instead each server action calls writeAudit() (src/lib/office/audit.ts), which writes a purposeful row capturing the actor (actorAccountId, from the session's OfficeUser.accountId, coalesced to the system account when null), the source (user/api/agent/system), and — for lifecycle changes — the from_state/to_state. This is the trail to trust for who-did-what.

2. The four inventory-ish tables — fn_emit_audit trigger (a safety net). items, assets, kits, and settings additionally carry the generic AFTER-INSERT/UPDATE/DELETE trigger fn_emit_audit (per-table triggers items_audit, units_audit [the assets table, pre-rename name], kits_audit, settings_audit in 0001; the function reshaped in 0005). It auto-captures {old,new} and guarantees an audit_log row for any write to those tables, even one not made through a server action.

  • Known limitation — attribution is SYSTEM. The trigger reads the actor from current_setting('app.actor_account_id', true), but the app can't set that GUC over its pooled PostgREST/supabase-js connections (there's no per-request transaction to SET LOCAL it on), so it's effectively always unset → the function coalesces to the system account (00000000-0000-0000-0000-000000000000, administrator@studio.chat). So trigger rows are real and complete except the actor, which reads "system".
  • It's kept deliberately as a safety net, and the office's item/asset edit forms (live since 2026-06-10) rely on it exclusively — they do NOT call writeAudit() on top, because that would double-log every edit (the inventory e2e suite pins the single-row contract). Known trade-off: those rows attribute to the system account rather than the signed-in editor. If per-staffer attribution ever matters for inventory edits, the fix is passing the actor GUC per-request or swapping the trigger rows for explicit writeAudit calls — not both.

RLS

Every table here is RLS-enabled with no policies (auto-applied by the rls_auto_enable event trigger at CREATE TABLE), making them service-role-only — the same model as the rest of the app. No policies were written.