Migrate the Schema

Goal

Change the wordloop-core Postgres schema safely using the current declarative target-state workflow.

Wordloop Core does not use timestamped .up.sql / .down.sql migration files. The source of truth is services/wordloop-core/db/schema.sql; the migrator reads that file, compares it with the live database using pg-schema-diff, prints hazards, and applies the generated DDL.

Prerequisites

• Familiarity with Postgres and Data Engineering principles.
• Local infrastructure running so the migrator can connect to Postgres:

./dev start infra

How the Migrator Works

services/wordloop-core/cmd/migrate is a Go command built into the Core image as /app/migrate.

1. It loads database settings from environment variables and .env through Core config.
2. It reads the target schema from db/schema.sql unless -schema <path> is provided.
3. If -pre-migration <file> is given, it runs that SQL file statement by statement, outside a transaction, before generating the plan (see Pre-migration hooks below).
4. It ensures the vector extension exists in the target and temporary databases.
5. It creates a temporary database on the same Postgres instance.
6. It applies the target schema to the temporary database.
7. It asks pg-schema-diff to generate a plan from the current live schema to the target schema.
8. It logs each statement, timeout, lock timeout, and hazard.
9. In up mode, it refuses to apply a plan containing a blocking hazard — DELETES_DATA, ACQUIRES_ACCESS_EXCLUSIVE_LOCK, CORRECTNESS, or HAS_UNTRACKABLE_DEPENDENCIES — unless -allow-hazards is passed (see Blocking hazards below). dry-run always exits 0; it prints the hazards and warns that up would refuse them.
10. It either exits without changes in dry-run mode or applies the generated SQL in migrate mode.

Flags

Blocking hazards

pg-schema-diff classifies every statement in a plan with a hazard type. INDEX_BUILD and INDEX_DROPPED are reported but never block — they fire on every ordinary CREATE INDEX CONCURRENTLY, so blocking on them would break routine index additions. The following hazards do block up unless you pass -allow-hazards:

• DELETES_DATA
• ACQUIRES_ACCESS_EXCLUSIVE_LOCK
• CORRECTNESS
• HAS_UNTRACKABLE_DEPENDENCIES

A fresh database produces zero blocking hazards for the current schema; you'll only see one when migrating an existing database across a hazardous change (a dropped constraint, a column type rewrite, etc.). Review the plan output before re-running with -allow-hazards — it is not a flag to reach for reflexively.

Pre-migration hooks

A pre-migration file is plain SQL, run one statement at a time, outside a transaction — required because CREATE/DROP INDEX CONCURRENTLY cannot run inside one. Use it when the schema change itself would fail against existing data (a stricter type, a new NOT NULL, a check constraint) and the fix is data cleanup rather than a schema decision. The file should be idempotent: safe to run again against an already-migrated database.

Worked example — vector(512) → vector(192). services/wordloop-core/db/manual/2026-09-09-vector-192.sql prepares people.voice_vector and transcript_segments.feature_vector for the ECAPA-TDNN embedding dimension change (see ADR 0008). Migrating the column type in place fails outright on any row still holding a 512-dimension vector (pq: expected 192 dimensions, not 512) because there is no meaningful conversion between vectors from two different embedding models. The runbook:

1. Drops the two HNSW indexes with DROP INDEX CONCURRENTLY — the plan that follows rebuilds them concurrently, so the exclusive-lock rewrite is not also paying for an index build.
2. NULLs any vector whose dimensionality is not 192 (vector_dims(v) <> 192) — those rows are re-embeddable from source audio, so nothing is permanently lost. people.voice_model_status reverts to untrained for cleared rows.
3. Is idempotent: running it twice is a no-op.

Run it as:

migrate -pre-migration db/manual/2026-09-09-vector-192.sql -allow-hazards up

-allow-hazards is still required alongside it: the pre-migration only clears the data that would make the type change fail; the type change itself is still an ACCESS EXCLUSIVE rewrite. Before running it in an environment with real data, count the rows that will lose their vector:

SELECT count(*) FROM people             WHERE voice_vector   IS NOT NULL AND vector_dims(voice_vector)   <> 192;
SELECT count(*) FROM transcript_segments WHERE feature_vector IS NOT NULL AND vector_dims(feature_vector) <> 192;

A fresh database (or one already at 192 dimensions and on the current schema) produces zero blocking hazards from this change — the compose and system-test stacks are unaffected. A database already at 192 dimensions but on the pre-branch schema still reports one blocking hazard (a dropped unique constraint on segment_manifest, labelled ACQUIRES_ACCESS_EXCLUSIVE_LOCK); review it and re-run with -allow-hazards.

Steps

1. Edit the target schema

Change services/wordloop-core/db/schema.sql so it describes the final desired schema.

Prefer additive, low-lock changes:

• Add new nullable columns first, or use cheap defaults only when safe for the table size.
• Add new tables empty.
• Add indexes for known access patterns, not speculative future reads.
• Avoid renames, drops, and type rewrites in the same release as the consuming code change.

For multi-release changes, keep the schema compatible across deploys: add the new shape, deploy code that can read/write both shapes where needed, backfill separately, switch readers, then remove the old shape later.

2. Review the generated plan

From the monorepo root:

./dev db dry-run

Review the logged DDL and all pg-schema-diff hazards. Treat the dry-run plan as the migration review artifact; there is no checked-in migration version table or down migration to inspect.

3. Apply locally

./dev db migrate

This runs go run cmd/migrate/main.go up from services/wordloop-core against the configured database.

4. Validate the app and repositories

Run the service tests that cover the changed schema and repository behavior. At minimum for Core schema work:

./dev test core

If the change affects cross-service behavior, add the relevant system or bet tests.

5. Refresh the reference snapshot when needed

./dev db dump applies the target schema locally and writes a reference-only dump to .dev/schema.sql:

./dev db dump

The dump is not the migration source of truth. Use it for documentation/reference generation only; keep services/wordloop-core/db/schema.sql as the canonical schema.

6. Commit schema, code, and docs together

The PR should include:

• The db/schema.sql change.
• Any Core repository/domain/service/API changes required by the schema.
• Updated documentation when tables, columns, workflow, or operational expectations changed.
• Test coverage for the changed behavior.

Deployment Notes

Docker Compose runs the same migrator before the API starts. The wordloop-core-migrate service executes /app/migrate up, and wordloop-core depends on that migration container completing successfully.

Production migrations should use the same target-state schema and migrator. Do not apply manual DDL outside the reviewed workflow; it creates drift that future pg-schema-diff runs must reconcile.

Verification

• ./dev db dry-run shows either no changes or an expected plan with reviewed hazards.
• ./dev db migrate completes successfully.
• ./dev test core passes, plus any affected system/bet tests.
• Database Reference matches services/wordloop-core/db/schema.sql.

Troubleshooting

• ./dev db dry-run fails before planning. Confirm Postgres is running with ./dev start infra, and check DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME, and DB_SSLMODE.
• The plan contains a destructive or blocking statement. Redesign as expand-contract across releases where possible: add the new shape first, backfill outside the migrator, then remove the old shape in a later change. If the hazard is genuinely intended for this release (reviewed and expected), re-run with -allow-hazards.
• up refuses with "refusing to apply N blocking hazard(s)". This is -allow-hazards doing its job — up will not silently apply DELETES_DATA, ACQUIRES_ACCESS_EXCLUSIVE_LOCK, CORRECTNESS, or HAS_UNTRACKABLE_DEPENDENCIES hazards. Read the printed plan, confirm it is intended, and re-run with -allow-hazards. If the change also requires data cleanup first (a type change that fails against existing rows), write a -pre-migration file — see Pre-migration hooks.
• A type or constraint change fails partway with a data error (e.g. pq: expected N dimensions, not M). The schema change is valid but existing data does not satisfy it yet. Write an idempotent -pre-migration <file> that cleans up the offending rows before the plan is generated — see the vector(512) → vector(192) worked example above.
• The plan references vector or extension errors. The migrator creates vector, but the Postgres image must support pgvector. Local and system-test stacks use pgvector/pgvector:pg15.
• The database is in a dirty or partially migrated state. Inspect the live schema and rerun ./dev db dry-run. Because the workflow is target-state based, the next plan is generated from the current live database to db/schema.sql.

See Postgres for the stance that shapes this workflow.