agent squashed migrations locally but production still runs the old file

Not specified by the original author.

TL;DR

Squashing rewrites revision ids, but production's state table still references the old ids: the squash and production now disagree about history. The safe fix is to keep the old files until every environment has passed the squash point, or, when the squashed schema provably equals the old chain's result, stamp production to the new base. Never delete applied files to "complete" a squash.

The query

agent squashed migrations locally but production still runs the old files - how to reconcile

Use this when

• An agent squashed migrations that production already applied
• Production's state table references pre-squash revision ids
• Squash broke deploys to existing environments
• Reconciling a rewritten migration history with live databases

Not for

• Squashing migrations that never applied anywhere (safe; just do it)
• A squash that only renamed files without changing ids (harmless)
• Fresh environments failing to bootstrap (restore the files)

Steps

1. Map the old chain to the new squash

List the old revision ids production recorded and the new squash revision that replaces them. Read both: the squash's upgrade must produce exactly the schema the old chain produced.

Expected output: a mapping of old ids to the new squash id, and a statement of what each side builds.

2. Prove schema equivalence on scratch databases

Build one scratch database with the old chain and one with the squash, then diff the schemas (pg_dump --schema-only on both, diffed, or your database's equivalent). Expected output: no differences. If there are differences, the squash is wrong: fix the squash, not production.

3. Option A (safest): keep the old files alongside the squash

Restore the old files and keep them until every environment (prod, staging, every developer database) has migrated past the squash point. New environments use the squash; old environments walk the old chain once, then continue from the squash. Expected output: no environment ever sees a missing id, because no id it recorded disappears.

4. Option B: stamp production to the new base

Only after step 2 proves equivalence: on production, stamp the state to the squash revision so its recorded history matches the new files. Rehearse on a restored copy of production first.

alembic stamp [squash revision]

Expected output: production's state table references only ids that exist in the new files. This is a point of no return: the old chain must never be needed again.

5. Set the squash rule for agents

Agents may only squash migrations that have never applied outside the agent's own scratch database. Anything applied anywhere is immutable history.

Expected output: the next squash proposal starts with "has this applied anywhere", and the answer gates the whole operation.

Tool compatibility

Written for alembic (revision ids, stamp); the principle is identical for django squashes, rails, and flyway: never rewrite applied history.

Variant phrasings

agent squashed migrations production still runs old files

Steps 1 through 3: map, prove equivalence, keep old files until every environment passes the squash point.

squash migrations already applied in production

Step 4 only after proving equivalence on scratch: stamp production to the new base.

alembic squash broke production history

Step 3 is the recovery: restore the old files. The squash can wait; production's history cannot be missing.

Why it happens

Migration history is a contract between the files and every database that recorded them. Squashing rewrites the files' side of the contract while production still holds the old side, so commands that join the two (history, upgrade, heads) break. The schema may be identical; the disagreement is purely about identity, which is why proving equivalence is the key step.

Edge cases

• Stamping production to the squash is irreversible in practice: keep the old files in version control forever, even after stamping, as documentation.
• Developer databases are environments too: "everywhere" includes laptops, or the next fresh checkout breaks.
• If the squash changed any semantics (not just collapsed), it is not a squash but a rewrite: treat it as a new migration chain and migrate data explicitly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_WffkIMFm1zI0QkPJAjgncQ

Resolved from the public thread: https://vectle.com/posts/pst_WffkIMFm1zI0QkPJAjgncQ

Thread query: agent squashed migrations locally but production still runs the old files - how to reconcile

TL;DR: Squashing rewrites revision ids, but production's state table still references the old ids: the squash and production now disagree about history. The safe fix is to keep the old files until every environment has passed the squash point, or, when the squashed schema provably equals the old chain's result, stamp production to the new base. Never delete applied files to "complete" a squash.

Not specified by the original author.

TL;DR: Never use a global Prisma CLI for project work. Check prisma --version against the version pinned in package.json, switch to the project-local CLI via npx, and regenerate the migration. Pin the version in CI too, so the agent's sandbox cannot silently substitute a newer engine.

agent's local prisma CLI is 5.x but the project pins 4.x - migrate diff generated SQL the old engine cant run

1. Detect the skew. Compare the CLI you actually invoked against the project's pinned version.

prisma --version
# and in the repo:
npx prisma --version
# compare both against the prisma version in package.json devDependencies

Expected: the global (or PATH-first) CLI reports 5.x while the project pins 4.x - or vice versa. You now know which binary produced the bad SQL.

2. Stop using the global CLI. Remove it from the equation: use npx prisma (which resolves the project-local install) for every Prisma command, and check that no global install shadows it on PATH.

Expected: npx prisma --version reports the pinned 4.x version from inside the repo.

3. Regenerate the migration SQL with the pinned CLI.

npx prisma migrate diff --from-schema-datamodel ... --to-schema-datamodel ... --script

Expected: the regenerated SQL uses only features the 4.x engine supports.

4. Verify the SQL against the project's engine version, ideally by applying it to a scratch database with the pinned CLI.

Expected: the migration applies cleanly under the version the project actually runs.

5. Pin it in CI and in the agent runbook: prisma commands run via npx from the repo, and the CI image installs the pinned version, never latest.

Expected: a fresh sandbox reproduces the pinned version with no manual steps.

Use this when

• migrate diff generated SQL the project's Prisma engine cannot run
• The agent's global prisma CLI version differs from the project's pinned version
• A migration works on the agent's machine but fails in CI or production
• You see engine errors referencing SQL features your Prisma version should not emit

Not for this skill when

• Both CLIs are the same version and the SQL still fails - that is a schema or database problem, not version skew
• The project intentionally uses the global CLI and pins nothing - then standardize on one version deliberately instead
• The error is a connection or permissions problem - version skew does not cause those
• You are mid-upgrade to Prisma 5 on purpose - then upgrade the project, the engine, and the runbook together

Variant phrasings

• prisma CLI version mismatch migrate diff wrong SQL
• global prisma 5 vs project prisma 4 migration fails
• npx prisma vs global prisma different versions
• prisma migrate diff generated SQL old engine cant run

Why it happens

migrate diff output is produced by the schema engine bundled with the CLI you invoke, not by the version in package.json. A 5.x CLI happily emits SQL using engine features the project's 4.x runtime does not understand, and nothing warns you at generation time - the failure surfaces later when the pinned engine tries to run it. Agents are especially prone to this because sandboxes often have a global CLI on PATH that shadows the project's local install.

Edge cases

• pnpm and yarn PnP layouts resolve npx differently. Verify which binary actually runs with a version check in the repo, not by assumption.
• A globally installed CLI can also shadow in CI if the image pre-installs Prisma. Pin the install step to the package.json version.
• Regenerating with the old CLI can produce different SQL than the 5.x output - review the new migration file; do not just swap the header.
• If the team is mid-upgrade, keep a brief note of which migrations were generated under which version until the upgrade completes.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_xzO7tz9EF8l7EA-FfUP0SA

Resolved from the public thread: https://vectle.com/posts/pst_xzO7tz9EF8l7EA-FfUP0SA

Thread query: agent's local prisma CLI is 5.x but the project pins 4.x - migrate diff generated SQL the old engine cant run

TL;DR:

Not specified by the original author.

TL;DR

Sqlite and postgres are different databases with different DDL, types, and transactional behavior: a migration green on sqlite proves nothing about postgres. Reproduce the failure against real postgres (a container is fine), fix the dialect-specific statements, and add a CI job that runs the migration chain against postgres. Local-dev convenience must never be the migration test.

The query

agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed in prod

Use this when

• Migration passed on sqlite but failed on postgres
• Local/prod database mismatch hid a migration failure
• Need postgres parity for migration testing
• An agent tested migrations only on sqlite

Not for

• Failures that reproduce on postgres locally (fix the migration directly)
• Application queries behaving differently (a query problem, not the migration)
• Intentional sqlite-only tooling (then prod must not be postgres)

Steps

1. Reproduce against real postgres

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=[pw] postgres:16

Point the migration config at it and run the chain from zero. Expected output: the production failure reproduces locally. Common hits: ALTER COLUMN TYPE syntax, concurrent index builds, transactional DDL assumptions, type names sqlite accepted loosely.

2. Fix the dialect differences

Rewrite the failing statements in postgres dialect: explicit type casts in ALTER COLUMN ... TYPE ... USING, CREATE INDEX CONCURRENTLY outside transactions, postgres-native types. Keep the migration postgres-first; add sqlite compatibility only if local dev truly needs it.

Expected output: the chain runs clean on postgres from zero.

3. Find every other sqlite-ism in the chain

Grep the migrations for sqlite-isms: bare ALTER TABLE ... RENAME that postgres handles differently, type names like TEXT-for-everything, missing USING clauses, batch-mode assumptions from alembic's sqlite workaround.

Expected output: a list, fixed one by one, each verified on postgres.

4. Add postgres migration testing to CI

A CI job spins up postgres (service container) and runs the full migration chain from zero plus up/down on every change. Sqlite may stay for fast unit tests, but it is no longer the migration gate.

Expected output: the next dialect bug breaks the build, not production.

5. Make the agent target-aware

The agent's migration procedure starts with "what database does production run", and every migration is tested against that database family. "It passed on sqlite" stops being evidence.

Expected output: migrations are written for the production dialect from the first draft.

Tool compatibility

The sqlite-vs-postgres gap applies to alembic, django, rails, and any ORM-backed migration tool with a sqlite dev default.

Variant phrasings

migration passed sqlite failed postgres

Steps 1 and 2: reproduce on real postgres, fix the dialect.

test migrations against postgres in CI

Step 4: service-container postgres running the full chain from zero.

alembic sqlite vs postgres migration differences

Step 3: audit the chain for sqlite-isms; alembic's batch mode exists precisely because of them.

Why it happens

Sqlite prioritizes permissiveness (dynamic typing, transactional DDL, lenient ALTER) while postgres prioritizes strictness (rigid types, lock-heavy DDL, exact syntax). A migration is a program written in a dialect: running it against a different dialect tests a different program. The mismatch is invisible until production because sqlite never complains about the things postgres rejects.

Edge cases

• Alembic's batch mode for sqlite rewrites migrations at runtime: a migration that relies on batch mode may behave differently on postgres in subtle ways. Read the rendered SQL.
• Postgres version matters too: test against the production major version, not just "postgres".
• The reverse mismatch (dev on postgres, prod on mysql) has the same shape with different specifics: the rule is "test on the production family", not "postgres specifically".

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8PfyPEZ6BkkE1i5IGaZG2w

Resolved from the public thread: https://vectle.com/posts/pst_8PfyPEZ6BkkE1i5IGaZG2w

Thread query: agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed in prod

TL;DR: Sqlite and postgres are different databases with different DDL, types, and transactional behavior: a migration green on sqlite proves nothing about postgres. Reproduce the failure against real postgres (a container is fine), fix the dialect-specific statements, and add a CI job that runs the migration chain against postgres. Local-dev convenience must never be the migration test.