Skip to content

Migration conventions

Status: Current | Last reviewed: 2026-08-31

How to add a new init-script.

Post-squash (2026-06-20). The schema is a single audited baseline (000_full_schema.sql) + 001_reference_data.sql + 002_tsf_app_bypassrls.sql + 099_normalize_user.sql, and changes are forward-only from there. New migrations are numbered scripts on top; the historical 1XX ledger is frozen under deploy/init-scripts/archive/ and never applied. Demo schema/data lives in seed-demo/. See overview.md. A new migration that changes the schema must be re-squashed into the baseline (overview.md → "Regenerating the baseline") so the drift CI stays green — OR kept as a standalone forward file if you prefer an incremental trail (the drift test compares the baseline to a full fresh boot of all active 0NN files, so either works as long as the committed baseline matches).

File name

deploy/init-scripts/<NNN>_<short_description>.sql
  • NNN — three-digit sequence number, one greater than the highest active file (currently 043; 099 is reserved for role-normalization-last). ls deploy/init-scripts/[0-9][0-9][0-9]_*.sql | tail -2 is the check; don't trust this sentence.
  • <short_description> — snake_case, verb-first, no dates. Examples: add_agent_locale_column, index_conversations_by_tenant.

Required structure

-- <NNN>: <human description, one line>
-- <why this exists, one or two lines>

BEGIN;

-- Schema changes go here. Use IF NOT EXISTS / IF EXISTS where possible
-- so the script is idempotent.

CREATE TABLE IF NOT EXISTS my_new_table (...);

CREATE INDEX IF NOT EXISTS idx_my_new_table_tenant
  ON my_new_table (tenant_id);

-- RLS for tenant-scoped tables (REQUIRED if tenant_id exists)
ALTER TABLE my_new_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON my_new_table
  USING (tenant_id::text = current_setting('app.current_tenant', true));

COMMIT;

Idempotency

Every script must be safe to run twice. The CI applies all init scripts to a fresh database, then applies them again to confirm no errors. If yours raises on the second run, it is rejected.

Use:

  • CREATE TABLE IF NOT EXISTS
  • CREATE INDEX IF NOT EXISTS
  • ALTER TABLE ... ADD COLUMN IF NOT EXISTS
  • DROP ... IF EXISTS
  • For INSERT, use ON CONFLICT DO NOTHING or DO UPDATE.

For more complex flows that cannot be expressed idempotently in DDL, wrap in a DO $$ BEGIN ... EXCEPTION WHEN ... END $$ block that swallows the expected duplicate-object error.

Tenant-scoped tables

If your table has a tenant_id column, it must:

  1. Have a foreign key to tenants(id) with ON DELETE CASCADE.
  2. Have an index on tenant_id (or a composite index starting with tenant_id).
  3. Enable row-level security.
  4. Define a policy reading current_setting('app.current_tenant', true).

The CI verifies these. Skip them and you will fail review.

Naming conventions inside the schema

  • Table names: snake_case, plural (agents, not agent).
  • Column names: snake_case, no Hungarian prefixes.
  • Foreign keys: <referenced_table_singular>_id (e.g., agent_id, tenant_id).
  • Booleans: is_* or has_* (e.g., is_current_published, has_recording).
  • Timestamps: *_at (e.g., created_at, published_at).
  • JSONB blobs: <purpose>_json only when you need to disambiguate; otherwise unsuffixed (config, metadata).

When to add a new SQL function

Most queries should be plain SQL in the calling app. Add a PL/pgSQL function only when:

  • The same multi-statement pattern shows up in two or more services (cross-language reuse).
  • Atomicity matters and a single statement cannot express it.
  • You are encapsulating a constraint that is hard to enforce declaratively.

If you add one, follow the pattern in sql-functions.md: name it for what it returns, document arguments, define LANGUAGE plpgsql STABLE (or VOLATILE if it writes), and SECURITY INVOKER unless you have a specific reason for DEFINER.

Registering it for populated databases

A new 0NN_*.sql file must also be added to the for bn in ... loop in migrate-city-db.sh Phase C, with a comment paragraph above it saying what it does and why it is safe to re-apply.

This is easy to skip and fails silently. Fresh boots and CI glob the directory, so an unregistered script applies there and looks correct — but migrate-city-db.sh walks a hand-maintained list, so the script is never applied to any already-populated database (vanguard, the city EC2s). The bug surfaces much later as a missing table, column, or unconverted config in one environment only. Five scripts had drifted out of the loop this way.

Two rules follow:

  • Never reuse a number. The loop and the _schema_migrations ledger both key on filename, so two files sharing a prefix are both applied but read as unrelated entries. (The pre-existing 005 pair is grandfathered.)
  • Data-only scripts count too. A script that only rewrites JSONB adds nothing to the baseline and passes the drift guard whether or not it ran, so Phase C is the only thing that applies it to a live database.

See the city-migration runbook.

Reference data is not covered by the drift guard

The drift guard proves the shape of the schema, and nothing else. check-schema-drift.sh dumps with --schema-only, so every row in every table is outside what it compares. The companion idempotency step in schema-drift.yml applies each forward 0NN file twice under ON_ERROR_STOP=1 — which catches a seed that raises on re-application, and says nothing about a seed that never existed.

Two consequences, in increasing order of how much they cost:

  • A data-only seed must be re-runnable. ON CONFLICT DO NOTHING (or DO UPDATE) throughout, matching 001_reference_data.sql. This one CI does enforce.
  • Losing a seed produces no CI signal at all. Not a red job, not a warning — the schema still matches the baseline byte-for-byte, because reference rows were never in the baseline to begin with. The only witness is a fresh boot of the product, and only if someone exercises the feature that reads the rows.

043_agent_templates.sql is the worked example, and it stayed broken for seven weeks. The 12 builtin agent templates were seeded by archive/101_agent_templates_and_tags.sql. The 2026-06-20 squash carried the schema forward and re-homed the integration_types / tool_types seeds into 001, but the template rows were data in a 1XX migration and were carried nowhere. Every database created after the squash — a new city EC2, a KMS environment, every CI boot — had zero templates, and all three readers degrade silently: the wizard's "Build with Template" step renders an empty grid, the Tools & Templates panel is empty, and template-baseline.ts falls through from its clone path to the bare getDefaultConfig factory. Nobody noticed because every long-lived environment predates the squash and still carries the rows from 101.

So when a change moves or re-homes reference data, the fresh boot is the test:

# 12, not 0 — and the same count after applying the file a second time
psql "$DATABASE_URL" -tAc \
  "SELECT count(*) FROM agents WHERE is_template AND id LIKE 'tpl-%';"

Two habits fall out of this:

  • Snapshot seeds need a regenerator, committed next to them. Templates are edited in the product, so 043 is a point-in-time dump — dump-agent-templates.sql re-emits it. (pg_dump --column-inserts, 001's idiom, can't be used here: it filters by table, and agents / agent_versions hold every real agent too.)
  • Order the file after every migration that rewrites the same rows. 043 is numbered after 033_copilot_config_migration.sql, which re-cuts the tpl-copilot-* configs — so the dump must come from an environment where 033 has already run. Dump from a database that predates it and you seed the pre-033 shape into fresh boots, where the migration that fixes it has already passed by. (033's own header notes that its WHERE clauses match zero rows on a fresh boot. That was this same gap, seen from the other side.)

Nor is behaviour — the guards never publish anything

The drift guard boots a schema and dumps it; the idempotency step re-applies the forward files. Neither one uses the database, so a constraint or trigger that rejects a write the product performs every day passes both. protect_published_ai_artifact (050_ai_governance.sql) shipped that way: it rejected publishAgentVersion's own write of the capability-expanded config onto the row it had just published, so publishing an agent failed and — because the whole publish is one transaction — left no version at all, while both schema jobs stayed green.

check-publish-invariants.sh closes that gap for the publish path: it creates an agent, publishes it, amends the release the way the publisher does, and rolls everything back, then proves a later transaction is still refused. It runs in schema-drift.yml against the already-migrated trusrv_idem database.

The same trigger also broke this file's own subject matter. Eight forward files rewrite config on published version rows (003, 004, 005, 006, 022, 025, 033, 037) — rewriting history is what a data migration is for. On a fresh boot they run before the trigger exists and have nothing to rewrite, so neither schema job noticed; on a populated database migrate-city-db.sh Phase C aborted at 004. Each of those files now opens with:

SET app.schema_migration = 'on';

which the trigger honours and no application ever sets. It is session-scoped, and every caller (the fresh-boot glob, Phase C, the CI idempotency loop, the chart's initdb configmap) applies one file per psql session, so it does not leak to the next file.

Three rules for a migration that adds an enforcement object:

  • Name the writes it will see. A trigger on a versioned artifact table will be hit by the publisher amending the row it just created — the intent is usually "immutable once committed", so compare the row's xmin to pg_current_xact_id() rather than refusing every UPDATE.
  • Check it against the migrations already in the directory. A constraint that forbids what 005 does will pass CI and abort the city-migration runbook. Add the maintenance marker to whichever files need it in the same change.
  • Exercise it, don't just install it. If the object guards a path the product drives, add the assertion to check-publish-invariants.sh (or a sibling) and confirm it fails with the object's previous definition. A guard that passes both before and after the fix is testing nothing.

Changing an existing function body is also a two-file edit: the forward 0NN file and the copy in 000_full_schema.sql, byte-for-byte, because the drift guard dumps prosrc verbatim. Confirm with SELECT md5(prosrc) FROM pg_proc WHERE proname = '…' before and after applying the forward file to a baseline-only database — the hashes must match.

Adding it to the chart

The chart's postgres-init-configmap.yaml globs migrations/0*.sql (the symlink to deploy/init-scripts/) — i.e. the active 0NN_*.sql scripts. The glob is non-recursive, so seed-demo/ and archive/ are never bundled into the cluster. A new 0NN_*.sql migration is picked up automatically; you do not need to edit the chart. (Demo seeds are local/dev only and are not applied in clusters.)

Testing locally

# Reset the local DB
bash .devcontainer/db-init-local.sh

# Or run only the new migration against the live DB (after a backup)
psql "$DATABASE_URL" -f deploy/init-scripts/<NNN>_<your_file>.sql

# Verify with idempotency check
psql "$DATABASE_URL" -f deploy/init-scripts/<NNN>_<your_file>.sql

# Confirm RLS works
psql "$DATABASE_URL" -c "
  SET LOCAL app.current_tenant = '00000000-0000-0000-0000-000000000000';
  SELECT * FROM <your_table>;
"

See also