Skip to content
Skills Library
Programming · Building & coding

postgres-migrations

/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_pending index tables. Triggers on adding a DB migration, a DROP/RENAME in a migration, or a review of one.

Repository
boxel ↗
Licence
MIT
Last repository change
27.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

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_pending index tables. Triggers on adding a DB migration, a DROP/RENAME in a migration, or a review of one.

Original author description

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_pending index tables. Triggers on adding a DB migration, a DROP/RENAME in a migration, or a review of one.

How it works

  1. Which directory?
  2. Creating a migration
  3. Enforcement
  4. Applying migrations locally / in CI
  5. Gotchas
Original SKILL.md headings · 87 lines, 623 words
SKILL.md preview
---
name: postgres-migrations
allowed-tools: Read, Grep, Bash
description: 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_pending index tables. Triggers on adding a DB migration, a DROP/RENAME in a migration, or a review of one.
---

# 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).

| Directory             | Tracking table       | When it runs in a deploy                                                                                                  | For                                   |
| --------------------- | -------------------- | ------------------------------------------------------------------------------------------------------------------------- | ------------------------------------- |
| `migrations/`         | `migrations`         | **pre**-deploy (`migrate-db` job)                                                                                         | additive, backward-compatible changes |
| `migrations-removal/` | `migrations_removal` | **post**-deploy (`migrate-db-remove` job), gated on the realm-server rollout reaching stability so old tasks have drained | destructive 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

```sh
# 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

- **Each production index table has a pending twin that must stay
  column-compatible with it**: `boxel_index` ↔ `boxel_index_pending` and
  `prerendered_html` ↔ `prerendered_html_pending`. A pass stages its rows in the
  pending table and its commit copies every production column out of the
  pending row, so a column added to a production table must be added to its
  pending twin in the same migration. Changing only one breaks the commit
  with `column "..." does not exist`. The pending tables carry two extra columns
  (`job_id`, `staging_id`) that production does not.
- **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.

Versions

The database has no other skills named postgres-migrations.

Database snapshot: 13.11.2026 · package version 3534a4aa5bc4 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100django-migrations
    Django migration patterns and safety workflow for PostHog. Use when creating, adjusting, or reviewing Django/Postgres migrations, including non-blocking index/constraint changes, multi-phase schema changes, data backfills, migration conflict rebasing, and product model moves…
  • 100migration-safety-review
    Review a database migration (Alembic, Django, Prisma, handwritten SQL) for operations that will lock tables, rewrite data or break the running app before it deploys. Use whenever the user writes, edits or autogenerates a schema migration, says "review this migration", asks "is…
  • 87postgres-migration
    Change the Postgres schema without breaking the previous release, which keeps serving on the new schema: the expand/contract recipes for adding a column or table, removing one, renaming one, changing its type, making it NOT NULL, adding an index or a unique constraint and…
  • 100add-migration
    Add or change a Postgres schema migration. Use when a feature needs a new table, column or index, or when running `npm run migrate`.
  • 96postgres-safe-migration
    Use when writing a migration for a table that holds data, when changing a column, adding a required column, an index or a constraint, or when a pg- check refuses a migration.
  • 100migration-helper
    Write safe database schema and data migrations with rollback steps, lock and timeout checks, and an expand-and-contract plan for zero-downtime deploys. Use when the user asks to add, rename or drop a column or table, add an index or constraint, backfill data, or review a…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.