Data model

The tables are declared with Drizzle in apps/server/src/db/schema/, one file per area. The design and every column’s reasoning are in the engineering spec: §1 for the tables and §7.13 for the integrity amendments, which win where the two differ (engineering spec). This page is the map.

Rules every table follows

• Ids are UUIDv7. A phone creates rows offline with its own id; the server accepts a client-supplied id only within ± 7 days of now.
• Every owned row carries its scope: location_id, owner_account_id or user_id, child tables included, so a row-level-security policy never needs a join. Parents expose UNIQUE (location_id, id) and children reference them with composite foreign keys that cascade on update, so moving a thing to another location carries location_id down.
• Mutable tables have updated_at, row_version and change_seq, kept by the kept.touch_row() trigger. Writes send the row_version they started from. Append-only tables (audit_events, llm_calls, deliveries, notifications, idempotency_keys) have none.
• Enumerations are text with a CHECK, never Postgres enums, so a one-version rollback survives a new value.
• Soft delete is deleted_at. Trash is restorable; the daily purge job removes what has expired.
• Money is numeric(16,4) plus a currency with a foreign key to currencies, amount and currency null together. Calendar facts are date, instants timestamptz.

The main tables

A user belongs to locations through memberships and owns one account, which owns locations; a location holds the place tree, things sit in places or containers, and a thing's records hang off it.

People and tenancy

Table	File	What it is
auth.user, auth.session, auth.account, auth.passkey, auth.two_factor, …	auth.ts	Better Auth’s tables, in schema auth, plus Kept’s auth.session_mfa and auth.sign_in_failures
user_profiles	tenancy.ts	Display name, locale, time zone, units, digits, quiet hours, managed
owner_accounts	tenancy.ts	One per user; owns locations and the account registries
locations	tenancy.ts	A household: kind, currency, time zone, require_2fa, deleted_at (30-day grace)
memberships	tenancy.ts	(location_id, user_id, role: owner · admin · member · viewer, expires_at)
invites, location_modules, user_hidden_modules	tenancy.ts	Invites; which modules a location has on; what a user hides
instance_admins, instance_settings	tenancy.ts	Who administers the instance; instance-wide settings

Every new account gets a Personal location with its Unplaced area and the owner membership, made by ensureAccount() through kept.ensure_account() in one transaction.

The tree

Table	File	What it is
places	places.ts	Rooms, shelves, areas; a tree by parent_id, one is_unplaced per location
things	things.ts	Exactly one of place_id or container_id (another thing); type_id, brand_id, lifecycle, custom, review_state, search_tsv
short_ids	things.ts	The six-character label codes, never deleted or reissued
thing_links, thing_tags	things.ts	Accessory-of and similar links; tags
types, type_fields, place_kinds, brands, vendors, people, tags	registries.ts	The account registries, scoped by owner_account_id; built-in types have none
secret_values, secret_field_policies	secrets.ts	Encrypted field values and who may reveal them

Loops in the tree are refused by triggers, and moving a container moves everything inside it.

Files

files (files.ts) holds a stored blob’s key, hash, size and type, scoped by location_id and deduplicated per location (UNIQUE location_id, sha256). A file is never read directly: an attachments row links a file, or a URL, to at most one subject through typed nullable columns (thing_id, place_id, purchase_id, warranty_id, claim_id, loan_id, service_record_id, meter_reading_id, fuel_entry_id, incident_id, valuation_id, expiring_document_id); with none set, it belongs to the location itself. file_derivatives holds the display and thumbnail variants; file_text (capture.ts) the searchable text of PDFs and receipts.

Records about things

Area	Tables	File
Purchases	purchases, purchase_lines (a thing points at its line), currencies, fx_rates, valuations	purchases.ts, currencies.ts, money.ts
Meters	meters, meter_readings, meter_events	meters.ts
Upkeep	schedules, service_completions, expiring_documents	schedules.ts
	service_records, service_lines	services.ts
	fuel_entries	fuel.ts
	stock_rules	consumables.ts
Cover	warranties, claims	warranties.ts
	loans	lending.ts
	incidents, incident_things	money.ts
Capture	extractions, inbox_items, templates	capture.ts

schedules, expiring_documents and service_records name their subject with typed columns (thing_id or place_id) and a CHECK on num_nonnulls(…), as attachments do.

Reminders and notifications

reminder_occurrences (reminders.ts) is the ledger of what is due: one row per subject, source, kind and due period, unique, so the 15-minute scan can run any number of times. reminder_deliveries records each channel’s send; notifications is the in-app centre. Channels, push subscriptions, preferences and calendar feeds are per user (notify.ts).

Audit, tokens and operations

Table	File	What it is
audit_events, audit_event_subjects	audit.ts	Every write: actor (user · token · system · import), entity, action, a redacted diff; partitioned by month. Subjects fan a container move out to its contents
api_tokens, token_locations, token_rate_windows	tokens.ts	Personal tokens and OAuth grants, the locations each reaches, the rate windows
idempotency_keys	tenancy.ts	Stored responses per (user_id, key)
sync_tombstones, sync_ops	tenancy.ts, sync.ts	Deletions phones must hear about; each user’s ledger of applied offline ops
ai_providers, ai_budgets, llm_calls, …	ai.ts	AI keys (sealed), budgets and the call ledger
webhooks, webhook_deliveries	webhooks.ts	Location webhooks
backup_runs, release_history	operations.ts	Backups and the releases that touched this database

pg-boss keeps its jobs in its own schema, pgboss, installed by kept migrate.

Adding a table

A new table needs its scope column, row-level-security policies in a custom migration, fixture rows in the leak test, and an audited path for every write. The steps are on migrations and row-level security.