Skip to content
AndonDocs
GitHub
Change Andon

Schema changes

View as MarkdownThe same text, at this page's address plus .mdOpen in ClaudeAsk questions about this pageOpen in ChatGPTAsk questions about this page

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.

On this page
View as MarkdownThe same text, at this page's address plus .mdOpen in ClaudeAsk questions about this pageOpen in ChatGPTAsk questions about this page

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 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:

    shell
    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:

    shell
    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 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.