agentsclimarketplace

Postgres migrations

Skill cardstack/boxel/.claude/skills/postgres-migrations

How to author Postgres migrations in packages/postgres under the two-phase (additive vs removal) system — additive changes go in migrations/ and run pre-deploy; destructive changes (DROP COLUMN/TABLE, RENAME) go in migrations-removal/ and run post-deploy so they never break the previous code revision mid-rollout. Use whenever creating, editing, moving, or reviewing a migration in packages/postgres, deciding which directory a migration belongs in, or touching the boxel_index / boxel_index_working index tables. Triggers on adding a DB migration, a DROP/RENAME in a migration, or a review of one.From its SKILL.md

Install
npx -y skills add cardstack/boxel --skill postgres-migrations

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

SKILL.md

5.3 KB, as published. Nobody here has run it

Postgres migrations — the two-phase system

packages/postgres/ migrations are split into two directories, each with its own node-pg-migrate tracking table, because a destructive schema change applied during a rolling deploy breaks the previous code revision while it is still serving (old tasks query a column the migration just dropped).

DirectoryTracking tableWhen it runs in a deployFor
migrations/migrationspre-deploy (migrate-db job)additive, backward-compatible changes
migrations-removal/migrations_removalpost-deploy (migrate-db-remove job), gated on the realm-server rollout reaching stability so old tasks have draineddestructive changes

Which directory?

Ask: does the migration's up() remove or rename something the currently deployed code still reads? If yes → migrations-removal/.

  • Additive → migrations/: addColumn, createTable, new index, backfill, dual-write. Safe for old code to run against.
  • Destructive → migrations-removal/: dropColumn / dropColumns / dropTable / renameColumn / renameTable, and the raw-SQL equivalents (pgm.sql('ALTER TABLE ... DROP COLUMN ...'), RENAME). Also anything that tightens a contract the old code depends on (e.g. adding NOT NULL to a column old code doesn't populate, narrowing a type).

The standard expand/contract pattern spans two releases: release N adds the new shape and stops reading the old one (additive migration + code); release N+1 drops the old shape (removal migration). Never ship the drop in the same release that stops reading it unless you are certain no old task will run against the new schema.

Creating a migration

# additive (the common case) — writes to migrations/
pnpm --filter @cardstack/postgres migrate create <name>

# destructive — writes straight to migrations-removal/
pnpm --filter @cardstack/postgres migrate:create-removal <name>

Filenames need a real Date.now() millisecond timestamp; pnpm lint:migrations rejects any prefix with 6+ consecutive zeros. migrate create stamps a valid one. Never hand-pick a round number.

Migration files are CommonJS (exports.up / exports.down); both directories carry a package.json pinning { "type": "commonjs" }.

Enforcement

packages/postgres/scripts/check-removal-phase.cjs (CI: "Guard removal-phase migrations") AST-parses each newly added migrations/ file and fails if its up() drops or renames a column/table. It is scoped to changed files (so drops already present in migrations/ are grandfathered) and looks only at up() (an additive migration's down() legitimately calls dropColumn to reverse itself). It is heuristic — it will not catch NOT NULL tightening, type narrowing, or destructive SQL built from non-literal strings — so the decision rule above still applies.

Applying migrations locally / in CI

pnpm migrate up|down [count]|create is transparent — the driver (scripts/migrate-local.sh) runs both phases as one combined sequence (additive first on up; down [count] reverts the N most-recent across both phases, default 1). You do not think about the split when applying; only when authoring a destructive change.

Gotchas

  • boxel_index and boxel_index_working are twin tables that must stay schema-identical. The indexer does SELECT * FROM boxel_index and mirrors the row shape into boxel_index_working, so any column add/drop must touch both (the removal migration uses TABLES = ['boxel_index', 'boxel_index_working']). Changing only one breaks index writes with column "..." of relation "boxel_index_working" does not exist.
  • Moving an already-applied migration between directories re-runs it under the new tracking table. Only safe if its up() is idempotent (IF EXISTS / ifNotExists). Moving a not-yet-applied file is always clean.
  • Schema snapshot: after any migration change run pnpm make-schema; the host validates the committed packages/host/config/schema/<latest>_schema.sql against the newest migration across both directories.
  • packages/realm-server/migrations/ is a separate migration system (not this one); don't conflate them.

Keep looking

Skills are one crate of 613,757. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.