Data and Migrations

Two facts define the Hub's relationship with data, and everything on this page follows from them: the repository contains schema, never rows; and every change to that schema is a versioned, reviewed file in src/db/migrations/.

Why migrations exist at all

The Hub runs in several databases at once — your local Docker Postgres, CI scratch databases, Neon preview branches per pull request, production. Without migrations each environment drifts: a column renamed here but not there, an index present locally and absent in production, discovered at the worst time. Migrations are how the schema is versioned alongside the code that depends on it. The alternative — Payload's auto-sync push mode — is fine for prototyping and dangerous in production, because it will happily drop and rename columns without asking.

For an open-source project the migration chain is also the installer: a self-hoster checks out the repository, runs npm run migrate on an empty database, and the entire schema materialises in order.

What a migration is — and is not

A migration file contains CREATE TABLE, ALTER COLUMN, DROP TYPE — the shape of the database. It contains no rows. Member data never enters the repository through this door: there are no INSERT INTO ... VALUES ('Ada Lovelace', …) lines, no fixture dumps, no seed emails. When a migration needs to transform existing data — like the authority_records backfill that consolidated two legacy tables — it does so with INSERT ... SELECT that moves rows within the database:

INSERT INTO authority_records (account_id, kind, role, scope_type, scope_id, …)
SELECT a.account_id, 'participation', canon.role, canon.scope_type, a.scope_id, …
FROM assignments a
CROSS JOIN LATERAL ( … CASE … END ) AS canon(role, scope_type, council_seat)
ON CONFLICT DO NOTHING;

The file describes a transformation; the data stays where it always was. That is why reading the migrations tells you the data model — tables, columns, constraints — and nothing about the members.

The conventions

• Named by timestamp, applied in order: 20261115_120000_authority_records.ts. Never edit an applied migration; write a new one.
• up() and down() must be mirrors. If up renames a table, its constraints and every index, down renames all of them back — a partial rollback is worse than none.
• Idempotent where possible: ON CONFLICT DO NOTHING, IF EXISTS, IF NOT EXISTS. A migration that can be re-run safely is a migration you can retry at 2am.
• Data transforms are self-contained and cannot assume row contents — the backfill above handles unknown legacy role strings by keeping them verbatim and unmapped (denied by the resolver), never by guessing.
• The live check is payload_migrations, the table Payload keeps of applied migrations; npm run migrate applies what's pending.

Validating a migration

Two checks, both cheap and both worth running before you push:

# 1. Fresh chain — does the whole history build on an empty DB?
createdb -h localhost -p 5433 -U postgres mig_test
DATABASE_URL=postgres://postgres:postgres@localhost:5433/mig_test npm run migrate

# 2. Populated backfill — does it transform real data correctly?
# Apply against a copy of a populated database, then inspect the results:
psql $DATABASE_URL -c "select kind, count(*) from authority_records group by 1"

CI's provision job runs the fresh-chain check on every PR — a migration that breaks the chain fails before review, not after deploy.

Where rows actually come from

If the repo has no data, who fills the database? Three legitimate sources:

1. npm run seed — provisions baseline structure (policy scaffolding, the skeleton a deployment needs) into the target database.
2. Provisioning scripts and the API — operators and staff create real accounts, records and content through the live system, which is why the audit trail knows who made them.
3. The test harness — integration specs provision their own throwaway accounts and rows per run.

A demo environment is no exception — its cast is generated into its database, not into git. See Demo environments for how the demo gets its population.

The rule in one sentence

Structure in git, data in databases, transformations in INSERT…SELECT — and if a pull request ever puts a real name, email or row literal into a migration, that is a review blocker, not a style note.