Migration says applied, but production says column does not exist

We read 3,426 public posts from builders whose apps broke at or after launch, and verified 418 recent cases. Database and data loss is 6% of them. Six verified reports in that data describe the same failure: the deploy succeeded, the migration tool says everything is applied, and production disagrees.

From the builder's chair it looks like this. API routes return 500 straight after a deploy, and the logs say column "plan_id" does not exist. You run prisma migrate deploy or supabase db push and it reports nothing to apply. The feature stays broken for days, because every tool you ask says the schema is current. The quieter version: a data migration finishes without an error, and weeks later someone notices records missing. The missing ones are nearly always rows without a parent, such as events with no tenant or orders with no account.

Both versions come from one habit: trusting a success message instead of checking the database.

Reproduce it

Silent data loss in a data migration

You need Docker and psql. Save this as repro.sql:

-- old schema: events.tenant_id was never a foreign key
CREATE TABLE tenants (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE events  (id int PRIMARY KEY, tenant_id int, payload text);

INSERT INTO tenants VALUES (1, 'acme');
INSERT INTO events VALUES
  (1, 1,    'signup'),
  (2, 1,    'upgrade'),
  (3, NULL, 'webhook, before tenants existed'),
  (4, 99,   'import from a deleted tenant');

-- the migration: move events into a tenant-scoped table
BEGIN;
CREATE TABLE tenant_events (
  id int PRIMARY KEY,
  tenant_id int NOT NULL REFERENCES tenants(id),
  tenant_name text NOT NULL,
  payload text
);
INSERT INTO tenant_events
SELECT e.id, e.tenant_id, t.name, e.payload
FROM events e
JOIN tenants t ON t.id = e.tenant_id;
DROP TABLE events;
COMMIT;

SELECT count(*) AS moved FROM tenant_events;

docker run --rm -d --name pgrepro -e POSTGRES_PASSWORD=pg -p 54329:5432 postgres:17
until pg_isready -q -h localhost -p 54329; do sleep 1; done
psql postgresql://postgres:pg@localhost:54329/postgres -v ON_ERROR_STOP=1 -f repro.sql

psql reports INSERT 0 2 and COMMIT, then moved is 2. Four events went in and two came out. The old table is gone, and ON_ERROR_STOP never had an error to stop on.

A history table that says "applied"

This one needs a Prisma project, so here is the sequence instead of a fixture. A migration that adds subscriptions.plan_id fails in production, and prisma migrate deploy now refuses to apply anything after it. Most people find this way to unblock it first:

npx prisma migrate resolve --applied 20261001120000_add_plan_id
npx prisma migrate deploy   # reports nothing to apply
npx prisma migrate status   # reports the schema as up to date, exits 0

SELECT column_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'subscriptions' AND column_name = 'plan_id';
-- (0 rows)

Every tool is green. The column doesn't exist, and every request that reads it returns 500.

Why it happens

Migration tools trust their history table, not your schema

prisma migrate deploy applies the migrations in your folder that _prisma_migrations doesn't list as applied. The Prisma CLI reference says it does not check for drift. migrate status compares the same two things, the migrations folder and the table. Prisma's baselining guide says prisma migrate resolve --applied "adds the target migration to the _prisma_migrations table and marks it as applied". It writes a row and runs no SQL. Any statement that hadn't run when the migration failed will now never run.

Supabase behaves the same way. supabase db push records each migration in supabase_migrations.schema_migrations, and its reference says "subsequent pushes will skip migrations that have already been applied". supabase migration repair --status applied inserts that record directly.

The history table is a claim about the schema, and nothing checks the claim again at deploy time.

The right command ran against the wrong database

If DATABASE_URL in the pipeline, or on the laptop that ran the command, points at development or a preview branch, the migration runs there and that database's history looks perfect. On Vercel, each variable is scoped to Production, Preview or Development, and the docs say changes "are not applied to previous deployments, they only apply to new deployments". Correcting the URL changes nothing until you redeploy.

Nobody ran it

Build and deploy are automated, and schema changes are not. The new code goes live expecting a column whose creation depends on someone remembering a command. If development and production share one database, it gets worse: every migration runs first against real data, so there is no stage where it can fail safely.

Joins skip rows, and SQL doesn't call that an error

An inner join returns only matching rows. NULL = 1 is not true, so event 3 never matches. No tenant has id 99, so event 4 doesn't match either. The INSERT ... SELECT inserted every row the SELECT returned, which is all it was asked to do.

UPDATE ... FROM works the same way, and the PostgreSQL docs spell it out: "if there is no match for a particular accounts.sales_person entry", the join form "will not update that row at all". The command tag reports how many rows changed, and a count of 0 "is not considered an error". With an UPDATE, skipped rows stay in their old state. With a DROP after the copy, as in the repro, they are deleted.

The orphans usually exist because the old column had no foreign key, and the missing constraint is often the reason for the migration in the first place.

Catch it before your users do

Test 1: diff the live schema against the repo

This test ignores the history table and inspects the database itself. That way it catches all three cases: a lying history table, the wrong database and a migration that never ran. Save as scripts/check-schema.sh:

#!/usr/bin/env bash
# Fails if the live schema differs from prisma/schema.prisma.
# Prisma 7: the URL comes from prisma.config.ts.
set -euo pipefail

# exit 1: unapplied or failed migrations, or diverged history
npx prisma migrate status

# exit 2: the live schema differs from schema.prisma
npx prisma migrate diff --exit-code \
  --from-config-datasource \
  --to-schema=prisma/schema.prisma

If your prisma.config.ts reads DATABASE_URL, run it with the production URL from your CI secrets:

DATABASE_URL="$PROD_DATABASE_URL" bash scripts/check-schema.sh

After the resolve --applied sequence above, migrate status passes and migrate diff exits 2. The diff is the check that sees the gap. To print the missing SQL, swap --exit-code for --script.

On Supabase, use supabase db diff --linked, which "diffs local migration files against the linked project" using a shadow database built in a separate container. The reference doesn't document an exit code for a non-empty diff, so check the output and fail closed:

supabase migration list
out=$(supabase db diff --linked 2>&1)
grep -q "No schema changes found" <<<"$out" || { echo "$out"; exit 1; }

If the CLI ever changes that message, the check fails loudly instead of passing quietly, which is the safer way to break.

Test 2: a data migration moves every row or refuses

Seed a scratch database with the rows that break joins and run the migration. The test fails on one outcome only: the migration succeeds with rows missing. Save as scripts/test-data-migration.sh:

#!/usr/bin/env bash
# Usage: bash scripts/test-data-migration.sh path/to/migration.sql
set -uo pipefail
DB=postgresql://postgres:pg@localhost:54329/postgres

psql "$DB" -q -v ON_ERROR_STOP=1 <<'SQL' || exit 1
DROP SCHEMA public CASCADE; CREATE SCHEMA public;
CREATE TABLE tenants (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE events  (id int PRIMARY KEY, tenant_id int, payload text);
INSERT INTO tenants VALUES (1, 'acme');
INSERT INTO events VALUES (1, 1, 'ok'), (2, NULL, 'no tenant'), (3, 99, 'dangling');
SQL

if psql "$DB" -q -v ON_ERROR_STOP=1 -f "$1"; then
  moved=$(psql "$DB" -tAc "SELECT count(*) FROM tenant_events")
  [ "$moved" = 3 ] || { echo "FAIL: migration succeeded but moved $moved of 3 events"; exit 1; }
fi
echo "PASS: every row moved, or the migration refused"

Copy the migration part of repro.sql (from BEGIN to COMMIT) into move_events.sql and run:

bash scripts/test-data-migration.sh move_events.sql
# FAIL: migration succeeded but moved 1 of 3 events

A migration that raises an error passes this test. It fails the deploy and loses nothing. In a real project, build the scratch schema by applying your earlier migrations, with prisma migrate deploy against the scratch URL or supabase db reset on the local stack. Then insert the orphan rows.

The fix

Make migrations a deploy step that can fail the deploy. On Render, the pre-deploy command runs after the build and before it goes live. "If any command fails or times out, the entire deploy fails", and the last successful deploy keeps serving. On Railway, a failed pre-deploy command "will not be retried and the deployment will not proceed". On other hosts, run migrations in a CI job that the production deploy depends on. Run the schema check in the same step, so a lying history table also fails the deploy:

npx prisma migrate deploy && bash scripts/check-schema.sh

Give production its own database and credentials. Use separate databases for development, preview and production, with each DATABASE_URL scoped to its environment in your host's settings. Keep the production URL out of local .env files, where a local command or a coding agent can pick it up.

Repair history only after checking the schema. Run the information_schema query first. If the change exists, resolve --applied or repair --status applied is correct. If it doesn't, the fix depends on the tool. On Supabase, supabase migration repair --status reverted <version> deletes the record, so the next db push applies the migration. With Prisma, create a new migration that contains the missing change and deploy it.

Decide in the migration what happens to orphans. You can refuse them, keep them with a LEFT JOIN and a nullable column, or move them to a quarantine table. Any of those is a decision. Dropping them by accident is not. The cheapest guard counts unmatched rows and raises an error. That rolls back the transaction and fails the deploy step:

DO $$
DECLARE orphans int;
BEGIN
  SELECT count(*) INTO orphans
  FROM events e
  LEFT JOIN tenants t ON t.id = e.tenant_id
  WHERE t.id IS NULL;
  IF orphans > 0 THEN
    RAISE EXCEPTION '% events have no tenant', orphans;
  END IF;
END $$;

Put it straight after BEGIN in move_events.sql and Test 2 passes. The migration stops with 2 events have no tenant, and nothing is dropped.

Read agent-written migrations before production runs them. If an AI coding tool wrote the SQL, read it and run it against a staging copy first. Keep production credentials out of the environment the agent runs in, so it can't apply a migration directly.

Checklist

• [ ] Production deploys run migrations in a step that fails the deploy on error.
• [ ] After every deploy, prisma migrate diff --exit-code or supabase db diff --linked runs against production and reports no changes.
• [ ] prisma migrate status exits 0, or supabase migration list shows the same migrations locally and on the remote.
• [ ] Development, preview and production use separate databases, and the production URL lives only in the production environment's settings.
• [ ] Every data migration with a join has a guard for unmatched rows, and a test fixture with a NULL parent and a dangling parent.
• [ ] Nobody runs resolve --applied or repair --status applied until the information_schema query confirms the change.
• [ ] A deliberately broken migration on staging fails the deploy, and the previous version keeps serving.

The symptoms we see in the reports, and the fixes for each cause, are on the troubleshooting page: https://research.gemmein.com/guides/database-migration-not-applied-in-production

After your last migration went out, what told you it had worked: the history table or the database?