# The AutoFolio data model

The repository's architectural decision, rationale and consequences.

# The AutoFolio data model

**Status:** accepted · **Date:** 2026-10-22 · **Related:** [ADR-0009](0009-row-level-security.md), [ADR-0014](0014-autofolio-on-the-template.md)

**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 [#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_ACTION`s when they write them.

## Consequences [#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.