The AutoFolio data model

Status: accepted · Date: 2026-10-22 · Related: ADR-0009, ADR-0014

Enforcement: packages/db/src/autofolio-rls.test.ts (in test:rls-matrix), packages/db/src/rls-catalog.test.ts and db:verify-rls (grants), packages/db/src/seed-autofolio.test.ts, packages/api/src/platform/crypto/field-encryption.test.ts, packages/errors/src/log-scrub.test.ts.

The handoff's draft schema (handoff/reference/db/schema.ts) assumed its own users, households and memberships. Here the Organization is the household (ADR-0014), so every vehicle table carries organization_id under the RLS rules of ADR-0009. These are the decisions the draft left open.

Decision

Tables. packages/db/src/schema/: preferences.ts (account_preference, consent_record), catalog.ts (vehicle_catalog), garage.ts (vehicle, ownership_period, odometer_reading, vehicle_photo, vehicle_document, reminder), ledger.ts (ledger_entry, expense, service, loan, loan_snapshot, valuation_snapshot, acquisition, disposal) and passport.ts (share_package, share_access). Client vocabulary is in the *.enums.ts leaves; CHECK constraints are generated from the same arrays.

Per User, not per Organization: account_preference and consent_record. Language, currency, units, reminder channels and consent are a person's choices, and two Members of one household may differ. RLS admits the context User's own row in any Organization. data_region is not updatable by tenant traffic: moving regions is a data migration. When regions are enforced, an Organization lives in its owner's region.

The catalog is shared, not tenant data. Every trusted session reads vehicle_catalog (the feature_flag rule); only system code writes it. A vehicle copies make, model, trim and year, so a refreshed or removed catalog row never changes a car.

★ tables are append-only for every role. ledger_entry, odometer_reading, loan_snapshot, valuation_snapshot, acquisition, disposal, share_access and consent_record: app_tenant has SELECT and INSERT only (share_access SELECT only; the public passport route writes it), and the append_only trigger refuses UPDATE, DELETE and TRUNCATE for the owner, superusers included. A correction is a new row: a correction ledger entry must name corrects_id (same car) and a non-blank reason. The one exception is erasure: a ★ row may be deleted only by the cascade that deletes its whole Organization (or, for consent_record, its User), so account deletion (RNF09) keeps working. ★ rows reference cars and periods without ON DELETE, so a car with history cannot be deleted; a wishlist car can. Authors are plain author_id text without a foreign key, like audit_log, because history outlives the User who wrote it; insert policies require the context User as author.

Same car, same Organization, even on the system path. Children reference (vehicle_id, organization_id); entries, acquisitions and disposals reference (period_id, vehicle_id); corrections reference (corrects_id, vehicle_id). Optional links (receipt documents, the catalog) are single-column ON DELETE SET NULL.

Money. bigint minor units with a char(3) ISO 4217 code checked by pattern, never a float. A foreign-currency entry stores fx_currency, an exact numeric fx_rate and fx_date, all or none. Fuel and energy are integer millilitres and watt-hours; distance is integer km. Service item amounts only explain the service's ledger entry. Loan rates are integer basis points with their period (year or month).

Idempotency without a key column. Ids are UUIDs that clients may generate offline; replaying the same id is the idempotency key of the offline queue (RNF04), instead of the draft's idempotency_key.

Trust. Tenant traffic can never write partner_verified (ledger and services) or edit a verified service; that is the partner path (v2).

Passports. share_package stores only the SHA-256 of its token, the ticked field names and the snapshot. Tenant traffic may change nothing but a live package's expiry (US39, amended in #29) and revoking, which sets revoked_at and drops the snapshot together; a revoked package never changes again; CHECKs keep a revoked package snapshot-free and keep plate, VIN, address and garage-location keys out of both the fields and the snapshot, for every role.

Personal fields. vehicle.plate_enc and vin_enc hold AES-256-GCM ciphertext (packages/api/src/platform/crypto/field-encryption.ts) under FIELD_ENCRYPTION_KEY, bound to the Organization and field. The env key is optional so the app boots without it; encrypting then fails. Logs go through @autofolio/errors/log-scrub, which drops sensitive keys and redacts amounts, VINs, emails, Brazilian and German plates, Drizzle's bound parameters and PostgreSQL's failing rows.

Audit. AutoFolio events go to the template's audit_log; it holds who, what, target and when, and a correction's reason is on the ledger row it creates. Feature slices add their AUDIT_ACTIONs when they write them.

Consequences

• A new ★ table needs SELECT/INSERT-only grants, the trigger (add it to the migration loop and the list in autofolio-rls.test.ts) and no ON DELETE on its parents.
• FIELD_ENCRYPTION_KEY must never be replaced in place; rotating it means a v2 ciphertext prefix and a re-encryption job.
• US plates have no free-text shape: they are redacted only under a plate key, so never interpolate a plate into a message.
• Documents and photos are not seeded: their rows need real objects in storage.