Migrate the Schema

Goal

Change the Postgres schema in a way that is safe for production: additive first, reversible, and non-blocking on hot tables.

Prerequisites

• Familiarity with Postgres and Data Engineering principles.
• Local stack running (./dev start infra) so you can test the migration against a real database.

Steps

1. Draft the migration

Migrations live under services/wordloop-core/migrations/ (or the equivalent directory for the service that owns the schema). Name them by timestamp and intent: 20260419123000_add_loops_archived_at.up.sql.

Write the .up.sql additively:

• Add columns as nullable, or with a default expression that is cheap on a hot table.
• Add new tables as empty.
• Never rename or drop in a single migration — split into "add new", "backfill", "stop reading old", "drop old" across releases.

Write the .down.sql as an exact reverse, tested locally.

2. Test locally

./dev migrate up
./dev migrate down
./dev migrate up

Round-tripping catches broken .down.sql early.

3. Backfill in a separate job

If the column needs a non-trivial value on historical rows, write a backfill job that chunks through the table and commits in batches. Do not backfill inside the migration itself — long-running DDL blocks replication and terrifies on-call engineers.

4. Coordinate with consumers

If the schema change is part of a renaming or restructuring, the order of deploys matters:

• Deploy the code that reads both old and new columns.
• Run the migration.
• Backfill.
• Deploy the code that reads only the new column.
• In a later release, drop the old column.

5. Commit the migration and the code change together

The PR should include the migration and the code that uses it. Reviewers can see the full scope of the change.

Verification

• ./dev migrate status shows the migration as applied.
• Service tests pass against the migrated schema.
• Rollback tested locally.
• Database Reference regenerates cleanly.

Troubleshooting

• ALTER TABLE is taking forever in staging. If it is a large table with a NOT NULL DEFAULT, the DDL is rewriting every row. Split into "add nullable → backfill → tighten to NOT NULL."
• .down.sql fails. Down migrations often break when the up migration contains data transformations. Consider whether the down is genuinely needed; some migrations are forward-only (and the code has to be able to tolerate that).

See Postgres for the stance that shapes this workflow.