Docs.

ARCHIVED. Written when the Google Sheet was the inventory source; the sheet sync was retired in 0005 and the DB is canonical — see ../architecture/data-model.md and ../architecture/inventory-provenance.md. Nothing here is current — see docs/README.md for the live map.

This is the canonical schema for the rental inventory. It maps directly to the current Google Sheet inventory (one tab, 191 data rows, 208 physical assets as of 2026-05-23) and adds the operational columns the rental system needs (rates, restrictions, operational state).

The schema is split into:

1. Items — the catalog of things we own.
2. Assets — physical copies of an item, expanded out from the sheet's Quantity column. (The table was named units in the 0001 baseline and renamed to assets in 0002_assets_spaces_settings.sql.)
3. Bundles — pre-defined groupings, modeled as a parent items row plus a bundle_items join to its component items (greenfield; not in the sheet today). A vestigial kits table also ships — see §3.
4. Settings — system-wide constants, including the FX-rate snapshot.

The split matters: a "Sony FX3" is one Item; if we own three of them, they are three Assets, each with their own condition, location, and service history.

Sheet → schema column mapping

Status: inventory is DB-canonical now. The automated importer (scripts/import-inventory.ts) was removed in migration 0005 along with the rest of the sheet-sync subsystem (see 06-sync-architecture.md). This mapping is kept as the emergency re-import reference — the spec for rebuilding items + assets from the sheet by hand if the DB is ever lost. The present-tense "importer" descriptions below document how that removed script behaved, and how a supervised manual recovery should replicate it.

The Google Sheet Inventory tab was the original source of record for the gear we already own; its CSV export resolves columns by header — note the leading space in Model (normalized away) and the misspelling of Reciept.

Identity is the sheet's UUID column (O), not make/model. Each sheet row is its own Item. Column O carries items.id; the importer INSERT … ON CONFLICT (id) DO UPDATE upserts on that UUID, so a re-run with unchanged data is a no-op. Rows with no UUID are new: Postgres mints an items.id and the importer writes it back to column O inside the same transaction. There is no (make, mpn) uniqueness on import; rows that look like duplicates of the same make/model are deliberately distinct Items (and may share a generated sku, which is not unique on items at baseline — 0002 later added UNIQUE(sku); see §3).

Assets (the units/assets table) seed only on first import for a given Item: if the Item already has any asset rows, the importer assumes admin owns asset state and skips them.

The importer only writes these items columns: sku, name, manufacturer, mpn, category, summary_en, included_accessories, and bookable_online (the pre-0002 name for reservable_online; the script predates the rename and still targets the old column name).

Fields with no source in the sheet (default or backfill required): items.name_es, items.summary_es, items.description_*, items.specs, items.cover_image, items.gallery, items.review_images, items.tags, items.not_included, items.replacement_value_usd_cents (+ items.replacement_value_updated_at), items.rate_day_usd_cents and friends, items.requires_operator, items.lead_time_days. items.sku is generated by the importer (slugified make-model, with the manufacturer prefix dropped when the model already starts with it). items.serialized is backfilled by a migration (true when any asset of the SKU carries a serial), not by the importer. Rental rates are the largest open item — see 05-open-questions.md; replacement values are backfilled via ../operations/replacement-prices.md.

1. Items

A model/SKU level entity. One row per gear type, regardless of how many assets we own.

There is no items.slug column — the catalog and detail routes key on sku. (Only kits and spaces carry a slug.) There is also no published or production_locked boolean; public visibility is governed by availability + published_at + reservable_online.

Uniqueness: UNIQUE(sku) (items_sku_uniq, added in 0002 after a de-dupe pass). The 0001 baseline had no unique constraint on items — duplicate-SKU rows were expected under the legacy "one items row per physical unit" model.

Indexes (baseline + 0002):

• items_sku_idx on sku.
• items_availability_idx on availability.
• items_published_at_idx on published_at.
• items_make_mpn_idx on (lower(manufacturer), lower(mpn)) where mpn is not null.
• items_make_name_idx on (lower(manufacturer), lower(name)).

2. Assets

Physical copies of an Item. One row per copy. The table was named units in the 0001 baseline and renamed to assets in 0002 (along with the FK columns that referenced it: reservation_items.assigned_unit_ids → assigned_asset_ids, item_blackouts.unit_id → asset_id).

The baseline units table also had an asset_tag column (with a partial unique index) for an internal sticker/barcode. It was dropped in 0002. Physical QR stickers now live in the short_links table: a row keyed by short_links.asset_id maps a qr.studio.chat/<code> short code to the asset. Assets carry no tag/code column of their own; the office UI surfaces an asset's sticker code by joining short_links (see src/lib/office/inventory.ts).

Invariants:

• An Asset on location = 'MDE' with condition ∈ {like_new, good, fair} is publicly rentable (subject to the Item's availability / reservable_online). Whether it's currently out on rental is resolved at query time from the reservations table. Inferred design — not yet built; confirm.
• An Asset with current_reservation_id set is on an active reservation.
• condition = 'retired' is permanent; the row stays for audit.
• location = 'LAS' is private staging and never surfaces in the public catalog.

The baseline shipped a published_units view (MDE + rentable condition + bookable, published Item) for the public-catalog read. It was dropped in 0002 as dead code — it had no callers. The public catalog now reads items directly via the service role and filters on availability + published_at (src/lib/rentals.ts); it does not currently join asset/location state.

Acquisition / import-tax bookkeeping. The Colombian Amazon import threshold (>$200 triggers per-shipment customs duty) is tracked in assets.notes_internal as a free-text annotation for now — e.g. amazon-import:2024-09 duty $42. We'll formalize a separate table only when volume justifies it. Inferred design — not yet built; confirm.

Indexes: the baseline ships only assets_pkey. The (item_id, condition) and (location, condition) dashboard indexes described in earlier drafts were never created — add them if the desk views need them.

3. Bundles

Pre-defined groupings sold at a bundle rate. No source data in the sheet — bundle definitions are greenfield.

The shipped bundle model (added in 0002) is a parent items row with no Assets of its own, joined to its component items via the bundle_items table. Availability derives from the components. (Example seeded in 0002: a dzofilm-vespid2-prime-set parent whose six prime lenses are the children.)

bundle_items:

Duplicate child rows are allowed (e.g. "2 batteries" = two rows), so each slot carries its own required and ord. A bundle_items_no_self_loop check forbids bundle_item_id = child_item_id. Indexed on bundle_item_id and on child_item_id.

A bundle is "available" iff every required component has an available Asset on the requested dates. Inferred design — not yet built; confirm.

Vestigial kits table. The 0001 baseline also defines a separate kits table (id, sku, slug, name_en, name_es, description_*, item_lines jsonb default '[]', rate_day_usd_cents, rate_week_usd_cents, replacement_value_usd_cents default 0, published default false, cover_image, gallery, timestamps; its tier column was dropped in migration 0005), with reservation_items.kit_id FK still pointing at it. This is the older jsonb-line bundle design that the bundle_items join superseded; it still ships (unique sku/slug indexes, audit + updated-at triggers) but holds no data and is not the model new bundles should use. Treat it as legacy until it's removed.

4. Categories

Locked enum — matches the actual taxonomy in the sheet today. Use tags for finer slicing.

camera body
camera body accessory
camera body stabilizer
camera lens
camera lens accessory
camera lens filter
camera monitor
camera monitor accessory
camera tripod
camera tripod accessory
light
light accessory
light modifier
light stand
light trigger
audio recorder
audio monitor
microphone
timecode generator
timecode generator accessory
video monitor
grip
battery
digital storage
digital storage accessory
storage
computer
workstation
phone

Import-time reservable_online by category:

• computer, workstation, phone → false (production-only by default).
• Everything else → true.

These are written by the importer per row (against the bookable_online column it still targets — see §1); the DB column default is simply true. Admins can override per item afterward.

5. Settings

An append-only revision history (since 0002): each save inserts a new row, and "current settings" is the row with the newest created_at. The 0001 baseline modeled this as a mutated singleton (id = 1, updated_at); 0002 swapped the singleton for per-revision rows (uuid PK, provenance columns, no updated_at) and indexed (created_at desc) for the current-settings read.

0002 dropped DEFAULTs on the data columns above (so a partial save fails loudly instead of silently using a stale baseline number), and dropped four baseline columns entirely: updated_at, cop_per_usd_updated_at (created_at carries both now), and the never-enforced first_time_renter_cap_usd_cents and blackout_default_days placeholders. There is no production_lockout_default_days column — that was a doc-only invention.

Currency notes:

• USD cents is the canonical store for every monetary field across items, assets, kits, reservations, and payments.
• COP is a display conversion computed from settings.cop_per_usd_snapshot at the moment of quote or render. We snapshot the rate onto reservations.fx_rate_snapshot at quote time so invoices stay reproducible.
• items.replacement_value_cop_cents (bigint) is a legacy/optional cache carried from the baseline; normally the UI computes COP on the fly from the USD value and the FX snapshot.

6. Audit log

Writes to items, assets, kits, and settings emit a row in audit_log via per-table Postgres triggers (fn_emit_audit), not application code, so anything that mutates those tables — admin UI, importer, SQL console — is captured. The trigger fires on INSERT/UPDATE/DELETE and records created/updated/deleted with the full row payload (and the actor from app.actor_user_id when set). The audit_log table is defined in 01-architecture.md.

Not every table carries this trigger. bundle_items, short_links, and short_link_scans (all added in 0002) have RLS auto-enabled but no audit trigger; the soft-delete tables (reservations, customers, short_links) are audited explicitly by app code (writeAudit, source = 'user') rather than by a generic trigger.

7. Row-level security

Every table here has RLS enabled — the auto-applied ensure_rls event trigger at CREATE TABLE turns it on for every new public table. Most have no policy (so they are service-role-only); a few add an explicit USING (false) admin-only policy (items, kits, settings, assets/units, audit_log, sync_events, sync_errors). The public catalog reads via the service role server-side (Next.js server components); there's no anon read surface.

Tags (open-ended, free-text)

Standard tag conventions:

• mount: pl-mount, e-mount, ef-mount, m4/3-mount
• format: super35, full-frame, medium-format
• characteristics: low-light, slow-mo, anamorphic, silent (no fan), daylight, tungsten
• use case: interview, event, narrative, bts, social
• audio: wireless, lavalier, boom, usb-c
• source: ours, subrental

Tags are not enumerated in the DB — they're plain text[].

Open data questions (still TBD)

These remain after the sheet import:

• Rental rates — the sheet has zero rate data. We need rate_day_usd_cents per item before we can quote. See 05-open-questions.md §pricing.
• Bundle definitions — the sheet has no bundle data. Author the first 3–5 bundles manually via bundle_items (the seeded dzofilm-vespid2-prime-set is the only example so far).
• Bilingual copy — sheet is English-only; ES summary/description need to be authored.
• Subrentals — the sheet implies we only have owned gear. If we start sub-renting from peers, we'd add a source flag + a vendor link (no such columns today).
• Per-asset pricing (e.g. a "B-cam" asset cheaper than the pristine A asset). Not supported — assets has no rate columns; rates live only on items. Would need new columns if pursued.
• Receipt-on-file flag (Reciept column) — currently stuffed into notes_internal as a receipt:<value> token. If we need to query/report on it, add a dedicated receipt_on_file bool.

See 05-open-questions.md for the full operational/policy list.