postgres-migration
/postgres-migration
Zmienia schemat Postgresa bez psucia poprzedniego wydania: wzorzec expand i contract dla kolumn, tabel, typów i indeksów.
- Autor
- langwatch ↗
- Repozytorium
- langwatch ↗
- Licencja
- Apache-2.0
- Ostatnia zmiana repozytorium
- 27.10.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 2
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 0
Brak sygnałów. Repozytorium zmienione 27.10.2026, bez odwołań do wycofanych modeli i API.
Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.
Co robi i kiedy
Zmienia schemat Postgresa bez psucia poprzedniego wydania: wzorzec expand i contract dla kolumn, tabel, typów i indeksów.
Argumenty
/postgres-migration <the schema change, or the migration name the scanner refused>
Oryginalny opis (EN)
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 splitting a table; why a migration never drops a table or a column (a drop is an operator step confirmed in Ops); the lock-heavy shapes the guard refuses and the ops pre-build note for an index; re-runnable statements; one owner per migration; timestamp keys and the migration-order check; no new foreign key and no new @relati
Jak działa
- The rules that never bend
- What the guard refuses, and what to write instead
- No foreign keys, no new @relation
- Recipes
- When it failed on deploy
- The scanner
Podgląd SKILL.md
---
name: postgres-migration
description: "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 splitting a table; why a migration never drops a table or a column (a drop is an operator step confirmed in Ops); the lock-heavy shapes the guard refuses and the ops pre-build note for an index; re-runnable statements; one owner per migration; timestamp keys and the migration-order check; no new foreign key and no new @relation; where schema.prisma and packages/prisma-client/prisma/migrations live; why inline DML on an existing table, a volatile default, two ALTERs on one table and a long lock_timeout are refused; and how a projection table changes (a rebuild beside the old one). Use whenever someone says 'add a field', 'add a column', 'add a table', 'drop that column', 'drop the table', 'rename this table', 'rename the column', 'change the column type', 'make it required', 'NOT NULL', 'add an index', 'unique constraint', 'add a relation', 'foreign key', '@relation', 'onDelete Cascade', 'write a Prisma migration', 'the migration failed on deploy', 'prisma migrate resolve', or the migration-safety test named their migration."
user-invocable: true
argument-hint: "<the schema change, or the migration name the scanner refused>"
---
# Change the Postgres schema safely
**The rule** (ADR-155; ADR-173): the previous image serves on the new schema during the rollout
and after a rollback, so every change is expand/contract. No release is a required stop (Alex,
2026-10-11): any install upgrades to the latest image through every step. Nothing is deleted
automatically (Alex, 2026-10-11): a migration never drops a table or a column; a drop is an operator step confirmed in Ops once safe.
Which recipe applies at all: the `migration` skill.
Files: `packages/prisma-client/prisma/schema.prisma` and
`packages/prisma-client/prisma/migrations/<timestamp>_<slug>/migration.sql`.
```bash
# Edit schema.prisma, then generate the SQL WITHOUT applying it, so you can shape it:
pnpm --filter @langwatch/prisma-client exec prisma migrate dev --create-only --config ./prisma.config.ts
# Apply it the way a deployment does (`pnpm task upgrade`; the worker runs it at boot):
pnpm start:prepare:db
```
`--create-only` is not optional: Prisma generates the breaking form by default, and every recipe
below edits the SQL first. The root `pnpm prisma:migrate` is an alias of `pnpm task upgrade`, so it
goes through the ledger; raw `prisma migrate` does not, and the api and worker keep refusing until
`upgrade` runs. The release PR stamps the
folder into a release manifest (`packages/upgrade/releases/<release>.json`); you never write one.
## The rules that never bend
1. **Never edit, rename or delete a merged migration.** The `migration-order` workflow refuses it
and prints the `git checkout` that restores it (`specs/ci/migration-order.feature`).
2. **Key above main.** The folder's timestamp sorts above the newest folder on `origin/main`, not
just your branch. Two PRs that pick the same key, or a key below main's newest, fail the check,
which prints the `git mv` to a free key. Rebase, rename the folder, never reuse a key.
3. **One owner.** Every table the file touches belongs to one module (the one whose repository
claims the model). `pnpm lint:architecture --policies migration-owners` refuses two; split the file.
4. **Unqualified table names.** `"Monitor"`, never `"langwatch_db"."Monitor"`.
5. **Every new column is nullable or has a `DEFAULT`.** Releases still serving do not name it.
6. **Re-runnable statements** (round 21): Prisma applies a migration statement by statement, so one
cancelled by `lock_timeout` keeps its earlier statements. Every folder newer than the marker
`RERUNNABLE_PRISMA_FROM` (`packages/upgrade/src/stepping/rerunnable-migrations.ts`) must survive a
second run from its first statement; `pnpm lint:architecture --policies rerunnable-migrations`
refuses anything else. Write `CREATE TABLE IF NOT EXISTS`, `ADD COLUMN IF NOT EXISTS`,
`CREATE INDEX IF NOT EXISTS` (never `CONCURRENTLY` in the file), `DROP ... IF EXISTS`,
`ADD VALUE IF NOT EXISTS`, `INSERT ... ON CONFLICT`, `CREATE OR REPLACE`; guard a constraint, a
type or a rename with a `DO $$ ... $$` block that checks the catalogue. Prisma's generated SQL
has none of these: edit it before you commit. The marker may move earlier, never later.
7. **A migration never drops a table or a column** (`drop-table-or-column`, above the newest
`langwatch@v*` tag). Leave it, stop reading and writing it, and declare an operator step
(`mode: "operator"`) in the owning module that drops it; an operator confirms it in Ops once
it is safe. Ops has no generic action to start such a step yet and none is declared today, so
until it exists leave the column in place and never bridge the gap with SQL. Dropping an index, constraint, default or type holds no data and stays allowed.
Released history kept its drops under `-- contract: retired in <release>` and `-- archive:`.
8. **Nothing is renamed or retyped in place.**
9. **No new foreign key and no new `@relation`** (Alex, 2026-10-06; existing ones stay).
## What the guard refuses, and what to write instead
Every finding prints its own fix. Source: `packages/prisma-client/src/__tests__/migration-safety.rules.ts`.
A table created in the same migration is empty, so the locking rules leave it alone. The last four
rows keep the api serving through the upgrade (Alex, 2026-10-09). Migrations in the newest `langwatch@v*`
tag are history they skip; the test reads that tag from git and fails with the fetch command in a clone
without tags (`git fetch --tags origin`). Above it there is no allow-list: fix the migration. An unreleased
folder may be rewritten (one `ALTER` per table per migration, new keys for the rest); one merged on main
is never edited and goes in `migration-safety.from-main.txt` instead.
| Rule | Refuses | Write instead |
| -------------------------------------------------- | ---------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------- |
| `drop-table-or-column` | `DROP TABLE`, `DROP COLUMN` in a new migration | leave it; an operator step in the owning module drops it once an operator confirms |
| `drop-without-retirement-note` | `DROP TABLE`, `DROP COLUMN` with no note (released history) | history only: a new drop is refused above |
| `add-not-null-column-without-default` | `ADD COLUMN ... NOT NULL` with no `DEFAULT` | a `DEFAULT`, or nullable now |
| `set-not-null-on-populated-column` | `ALTER COLUMN ... SET NOT NULL` on an existing table | app-level enforcement, or `CHECK (col IS NOT NULL) NOT VALID` then `VALIDATE` later; no escape hatch (round 22) |
| `alter-column-type` | `ALTER COLUMN ... TYPE` | new column, backfill, switch, retire the old |
| `enum-recreated` | `ALTER TYPE ... RENAME TO`, drop-and-recreate an enum | `ALTER TYPE ... ADD VALUE`; never remove a value |
| `unique-or-validated-constraint-on-existing-table` | `ADD UNIQUE`/`PRIMARY KEY` built at once, `CHECK` validated at once | pre-build the index and `ADD CONSTRAINT ... USING INDEX`, or `NOT VALID` + a later `VALIDATE` |
| `plain-index-on-existing-table` | `CREATE INDEX` on an existing table with no pre-build note | the ops pre-build note (below) |
| `rename-in-place` | `RENAME COLUMN`, `ALTER TABLE ... RENAME TO` | add, backfill, dual-write, switch, retire |
| `new-foreign-key` | `FOREIGN KEY` or a `REFERENCES <table>(...)` clause, on a new or an existing table | a plain column with an index (below); a new `@relation` fails the enforcer's relation ratchet |
| `inline-dml-on-existing-table` | `UPDATE` or `DELETE FROM` on an existing table | a batched, checkpointed background step (`migration-data-step`) |
| `volatile-default-on-existing-table` | `ADD COLUMN ... DEFAULT gen_random_uuid()`/`random()`/`nextval()`, or `SERIAL` | nullable or a constant default, a background fill, the app sets it on insert; `now()` is fine |
| `several-alters-on-one-table` | two `ALTER TABLE` statements on one existing table in one migration | one `ALTER TABLE` with comma-separated actions, or a later migration |
| `lock-timeout-above-ceiling` | `SET lock_timeout` above 2 s, or 0 | nothing: the runner sets 2 s and retries; split a migration that needs a longer wait |
**The ops pre-build note.** `CONCURRENTLY` cannot run inside Prisma's transaction, so an index on an
existing table carries a comment naming the statement an operator runs ahead; the migration's own
`IF NOT EXISTS` then finds it built. Exemplar:
`packages/prisma-client/prisma/migrations/20261006120000_process_outbox_lease_by_process_index/`.
```sql
-- ops pre-build: CREATE INDEX CONCURRENTLY IF NOT EXISTS "Widget_projectId_idx" ON "Widget" ("projectId");
CREATE INDEX IF NOT EXISTS "Widget_projectId_idx" ON "Widget" ("projectId");
```
A failed `CONCURRENTLY` build leaves an invalid index under that name that `IF NOT EXISTS` then skips;
say in the note to check `pg_index.indisvalid` and `DROP INDEX CONCURRENTLY` before retrying.
Sessions run with `lock_timeout` 2 s (Alex, 2026-10-09); a statement that waits longer fails the
attempt, `upgrade` retries it (up to 3 attempts), the old image keeps serving, and the operator re-runs
`upgrade` if all fail.
**A re-runnable migration, worked.** The shape the `rerunnable-migrations` policy accepts
(`packages/architecture-enforcer/src/policies/persistence/rerunnable-migrations.ts`, cases in
`packages/architecture-enforcer/tests/rerunnable-migrations.unit.test.ts`; the in-tree index
exemplar is the `20261006120000_process_outbox_lease_by_process_index` folder above). Every
statement is a no-op on its second run, in the order Prisma would apply them:
```sql
CREATE TABLE IF NOT EXISTS "ProjectWidget" ("id" TEXT NOT NULL, "projectId" TEXT NOT NULL,
CONSTRAINT "ProjectWidget_pkey" PRIMARY KEY ("id"));
ALTER TABLE "Project" ADD COLUMN IF NOT EXISTS "slug" TEXT;
CREATE INDEX IF NOT EXISTS "ProjectWidget_projectId_idx" ON "ProjectWidget" ("projectId");
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'Project_slug_present') THEN
ALTER TABLE "Project" ADD CONSTRAINT "Project_slug_present"
CHECK ("slug" IS NOT NULL) NOT VALID;
END IF;
END $$;
```
The policy names the fix for each bare form (`a second CREATE TABLE fails: write CREATE TABLE IF NOT
EXISTS`, `a second INSERT duplicates its rows ...: add ON CONFLICT DO NOTHING`, `a cancelled concurrent
build leaves an INVALID index ...`); a statement it does not recognise is refused with the `DO $$`
guard as the fix. Prisma's generated SQL has none of these guards: edit it after `--create-only`.
## No foreign keys, no new `@relation`
The W-01 guard is two checks: the scanner's `new-foreign-key` rule refuses a new `FOREIGN KEY` or
`REFERENCES` clause in a migration, and the relation ratchet
(`packages/architecture-enforcer/tests/prisma-relations.unit.test.ts`, list in
`packages/architecture-enforcer/tests/baselines/prisma-relations.json`, shrink-only) refuses a new `@relation` in `schema.prisma`;
existing ones stay. A reference is a plain scalar column with an index. Joins happen in the owning repository by a second
query; a table another module owns is never joined (ask its `*Api`). Deleting dependents is a
service's behaviour; children in another module go by a fact and that module's purge subscriber (§9.1).
```prisma
model ProjectWidget {
id String @id
projectId String
@@index([projectId])
}
```
## Recipes
**Add a column or table.** One release. `ALTER TABLE "Project" ADD COLUMN IF NOT EXISTS "slug" TEXT;`
or with `NOT NULL DEFAULT 'free'`. New writers set the new shape from this release: a fresh install
plans data steps by mode, so a backfill is never the only way a row becomes correct.
**Remove a column or table.** Release A stops reading and writing it, marked `/// retired: unread
since A` in `schema.prisma`, no migration. Nothing drops it automatically: the owning module
declares an operator step (`defineMigrationStep({ mode: "operator", ... })`) whose run drops it
through the module's own repository, and an operator confirms it in Ops once old writers are gone.
The upgrade registers it pending and never runs it (`packages/upgrade/specs/contract-order.feature`).
Ops' generic "start this operator step" action is not built yet, so today stopping use is the
whole job.
**Rename a column, or change its type.** Expand: the new column beside the old. Migrate: dual-write
from release A and copy with a background `.withMigrations` step (never an inline `UPDATE` on an
existing table); readers switch in A or read `coalesce(new, old)`. The old column stays until an operator step
drops it (rule 7).
**Make a column required.** Writers set it from release A; a step fills the gaps; enforce it in the
application. If the database must enforce it, add `CHECK ("slug" IS NOT NULL) NOT VALID` once
old writers are gone, and `VALIDATE CONSTRAINT` in a later migration. Never `SET NOT NULL`.
**A new unique constraint.** A plain index with the pre-build note; a step repairs duplicates;
writers comply from A; then a unique index pre-built the same way and attached with
`ADD CONSTRAINT ... USING INDEX`.
**Split a table.** Create the new table, dual-write and copy (a data step once it is large), switch
readers, retire the old columns under rule 7.
**Inline DML** in a migration is refused on an existing table (`inline-dml-on-existing-table`): it
locks the rows the serving api writes. Only a table the migration creates takes inline `UPDATE` or
`DELETE`; a seed `INSERT ... ON CONFLICT` is fine. A blocking data step may touch only tables created
in its own release (`upgrade-sign-in-tables`); anything older is a background step.
**A projection table** is derived state: create the new shape under its own name, replay it
(`packages/eventing/src/replay/`), switch readers, retire the old table under rule 7. Never `ALTER`.
## When it failed on deploy
A folder newer than the marker is re-runnable, so `upgrade` clears it itself: it marks the failed
row rolled back (`prisma migrate resolve --rolled-back`), waits the retry backoff (2 s, then 4 s) and
applies it again, up to 3 attempts, logging each by name (`Prisma migration <name> failed on attempt 1
of 3 and is re-runnable: marked it rolled back`). A failed row an earlier run left is cleared the same
way at preflight. Still failing after the last attempt, the run exits 1 with code
`rerunnable_migration_failed`: fix the cause (often a lock another session holds, `lock_timeout` 2 s)
and run `upgrade` again; no resolve command is needed. The few named hand-applied folders are not
re-run even above the marker (`findHandAppliedSql` prints the SQL that finishes one).
A folder at or below the marker, or one the image does not ship, keeps the conservative path:
`upgrade` exits 1 with code `failed_prisma_migration`. Retry on Ops > Upgrades records consent to
rerun it, and a first install may rerun it too. Otherwise check what it left, then
`prisma migrate resolve --rolled-back <name>` if nothing remains, or `--applied <name>` if you
completed it by hand, then run `upgrade` again. Nothing has rolled; the old image still serves.
**An index that already exists.** Before applying, the runner checks each pending folder's
`CREATE INDEX` against what an operator pre-built: an existing index of the same name with a
different shape, or one that is invalid, stops the run with a message naming it
(`packages/upgrade/src/stepping/prebuilt-indexes.ts`). Drop the wrong index (`DROP INDEX
CONCURRENTLY`) and let the migration or the pre-build recreate it. Spec:
`specs/upgrade/rerunnable-migrations.feature`.
## The scanner
```bash
VITEST_MAX_WORKERS=2 pnpm --filter @langwatch/prisma-client test src/__tests__/migration-safety.unit.test.ts
```
It reads every folder not in `src/__tests__/migration-safety.baseline.txt` (frozen; adding yours is
refused) and fails by name with the fix. Folders merged from main are in
`migration-safety.from-main.txt` by blob sha. The seven archive-note, lock and foreign-key rules
(`FLOOR_AND_LOCK_RULES` in `migration-safety.rules.ts`) hold only folders after the test's
`NEW_RULES_FROM` marker (`20261006170527_data_privacy_project_scope`); every new folder sorts above
it, so all ten rules apply to yours. Spec: `specs/ops/migration-safety.feature`.
ClickHouse: the `clickhouse-migration` skill.
Pliki w paczce (2)
- SKILL.md 18 KB
- evals/evals.json 3 KB
Wersje
W bazie nie ma innych skilli o nazwie postgres-migration.
Migawka bazy z 13.11.2026 · wersja paczki c0d20122850d (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 96postgres-safe-migration
Pisze bezpieczne migracje PostgreSQL dla tabel z danymi, zmian kolumn, indeksów i ograniczeń. - 100add-migration
Dodaje lub zmienia migrację schematu Postgres: nową tabelę, kolumnę albo indeks, i uruchamia polecenie migracji w projekcie. - 100zero-downtime-database-migration
Prowadzi zmiany schematu produkcyjnej bazy bez przerwy w ruchu: migracje expand i contract, backfill dużych tabel, dual-write, zmiana nazwy gorącej kolumny, podział tabeli i dodawanie kluczy obcych. - 95db-migration
Pilnuje zasad zmian schematu bazy: tabel, kolumn, indeksów, ograniczeń, enumów, triggerów, uzupełnień danych i plików migracji. - 87postgres-database-migration
Planuje i testuje migracje schematu PostgreSQL na produkcyjnych danych: dodawanie kolumn, zmianę typów, indeksy i ograniczenia bez przestojów. - 100alembic-safe-migrations
Pisze i sprawdza migracje Alembic, które nie zablokują produkcyjnej bazy PostgreSQL, przy dodawaniu indeksów, kolumn NOT NULL, zmianie typów i backfillu.