Schema changes
A change to a table, column, policy, RPC, view, trigger or setting is one migration file in db/migrations/, the
only source of Andon’s schema. The server applies it on every Postgres it runs on: andon up, the desktop app, Vercel
with Neon, a self-hosted server. The mechanics of db/ (the frozen baseline, upgrading old databases, what the schema
names mean) are in db/README.md.
The folder is append-only. An applied migration never changes. The runner records each file’s checksum and refuses a file that changed, which would stop every instance that applied it from starting. A fix is a new migration.
Make a schema change
-
Write the migration. Name it by the current UTC time:
db/migrations/<YYYYMMDDHHMMSS>_<slug>.sql. Parallel branches each add files, so “the next number” collides. Write it against the server’s names: theandonschema (andon_privatefor what callers never reach),andon_auth.uid()for the caller, the rolesandon_authenticatedandandon_anon.db/README.mdhas the rest. -
Follow the house rules for what it adds:
- a new table has
org_id uuid not null, composite foreign keys(org_id, parent_id) references parent (org_id, id), row-level security on, the standard select, insert and update policies, and a restrictive visibility policy; - a new
security definerfunction hasset search_path to ''and is granted toandon_authenticatedonly, never toandon_anon; - anything that points at a card or a team checks
can_see_cardorcan_see_team, not org membership alone; asecurity definerfunction checkscan_read_orgon the row’s org as well, because those two say yes to any open team in any org; - a changed view or function is redefined whole (
create or replacewith its full body), never by finding a fragment of its stored definition and replacing it; - scheduled work is a tick of
andon.run_due_ticks()inandon_private.ticks, a call out is a job (andon_private.call_functionqueues one), a storage rule goes inandon_private.can_access_object()and a Realtime rule inandon_private.can_join_topic().
- a new table has
-
Prove it upgrades:
shell pnpm db:checkIt runs on your worktree’s own Postgres (no Docker), and checks that three ways of getting to this branch’s schema land on the same one: a fresh database, a database migrated as of where your branch left
origin/mainand then upgraded, and every earlier revision of the frozen baseline plus the files after it.--from <ref>starts the upgrade somewhere else. If it fails, fix your migration, never an applied file. -
Register what you added in the tenancy registry,
apps/server/test/rls/tenancy-registry.ts: a table with its org column, who may read it and a seed row; an RPC with how it is called; a Realtime topic or a server function with who may join or call it.tenancy-coverage.test.tsfails without the entry. -
Regenerate the data API’s allowlist:
shell pnpm db:catalogIt writes
apps/server/src/db/catalog.gen.ts(whatandon_authenticatedandandon_anonmay reach) andcatalog.service.gen.ts.apps/server/test/catalog.test.tsfails while either is stale. -
Update the row types in
packages/shared/src/database.types.tsby hand: your new tables, columns and functions, in the shape the file already has. -
Test it as the real caller in
apps/server/test/rls/, with the negative cases: another org, a member who can’t see the team, a machine, andanon. Testing says how to run them. -
Name the file in the pull request. Changes under
db/migrations/**never merge on their own: a person merges them.
What never to do
- Edit
db/migrations/20261021160000_baseline.sql. It is frozen: instances applied it, and a changed checksum stops them. - Edit, rename or remove any migration already on
main. - Add a line to
20261021160000_baseline.revisions.json. It lists every content the baseline ever had onmain, so the runner can carry an old database forward. - Set anything with a session-level
SET. The server runs each request in one transaction withset localandset_config(…, true), because a session setting leaks to the next client through a transaction pooler.
