Database

Crest keeps its data in PostgreSQL 17. The schema, the access rules and the functions are plain SQL migrations that can be read and reviewed (Apps/Api/Migrations). The Tables page is generated from the real schema and a mapping file, so it cannot drift from it.

How it is organised

Group	Tables
Identity and access	app_user, auth_session, auth_token, auth_totp, access_request, audit_log
Spaces and sharing	space, space_member, space_invitation
Money	financial_account, category, txn, transfer, recurrence, budget, savings_goal, goal_contribution, exchange_rate
Notifications and contributions	device_push_token, contribution_payment

All tables shows, for each one, what it is for and which business requirements, business rules and user journeys it serves. Each table also lists its columns, constraints and indexes, with a diagram of the tables it links to.

Rules that apply everywhere

• Standard columns. Every business table has id, created_at, created_by, updated_at, updated_by and deleted_at. There are no redundant or copied columns. The one allowed copy is txn.space_id, which row-level security needs. A transaction's date is its created_at.
• Money is an integer. Amounts are bigint in the currency's smallest unit, never floating point. A balance is worked out from its transactions, not stored. See Technical design §6.
• The database decides who sees what. Every business table has row-level security. Four database roles have different grants: crest_owner owns the tables and runs migrations, crest_app serves users, crest_admin serves the Super Admin portal and gets no grants on financial tables, and crest_worker runs background jobs. See Technical design §7.
• Migrations are append-only. A change to the schema is a new numbered migration with its row-level security and grants, and tests that act as different users against a real PostgreSQL. An applied migration is never edited.

Where the Tables page comes from

Two committed files feed it: Apps/Docs/Data/DatabaseSchema.json, a snapshot of the real schema (columns, constraints, indexes, relations), and Apps/Docs/Data/SchemaTraceability.json, which says what each table is for and which requirements and journeys it serves.

After you change a migration, run:

pnpm docs:schema

It starts a throwaway PostgreSQL in Docker, applies all migrations, and rewrites the snapshot, then rebuilds the page. Commit the changed snapshot. Nothing is edited by hand: the test Apps/Api/Test/Integration/Database/DatabaseSchemaSpec.ts fails in pnpm test and in CI when the snapshot no longer matches the migrations, and the page build fails when a table has no entry in the traceability file or an entry names a requirement or journey that does not exist. pnpm dev:docs and pnpm docs:build only read the two files, so they need no database.