# 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`](https://github.com/psci-labs/andon/blob/main/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

1. **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: the `andon` schema
   (`andon_private` for what callers never reach), `andon_auth.uid()` for the caller, the roles `andon_authenticated`
   and `andon_anon`. [`db/README.md`](https://github.com/psci-labs/andon/blob/main/db/README.md#what-the-schema-names-mean) has the rest.
2. **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 definer` function has `set search_path to ''` and is granted to `andon_authenticated` only, never
     to `andon_anon`;
   - anything that points at a card or a team checks `can_see_card` or `can_see_team`, not org membership alone; a
     `security definer` function checks `can_read_org` on 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 replace` with 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()` in `andon_private.ticks`, a call out is a job
     (`andon_private.call_function` queues one), a storage rule goes in `andon_private.can_access_object()` and a
     Realtime rule in `andon_private.can_join_topic()`.
3. **Prove it upgrades:**

   ```sh
   pnpm db:check
   ```

   It 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/main` and 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.
4. **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.ts` fails without the entry.
5. **Regenerate the data API's allowlist:**

   ```sh
   pnpm db:catalog
   ```

   It writes `apps/server/src/db/catalog.gen.ts` (what `andon_authenticated` and `andon_anon` may reach) and
   `catalog.service.gen.ts`. `apps/server/test/catalog.test.ts` fails while either is stale.
6. **Update the row types** in `packages/shared/src/database.types.ts` by hand: your new tables, columns and functions,
   in the shape the file already has.
7. **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, and `anon`. [Testing](https://andon-docs.pages.dev/contributing/testing.md) says how to run them.
8. **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 on `main`, so
  the runner can carry an old database forward.
- Set anything with a session-level `SET`. The server runs each request in one transaction with `set local` and
  `set_config(…, true)`, because a session setting leaks to the next client through a transaction pooler.
