Migration works locally but fails in production: causes and fixes

What you'll see

A page or API route returns 500 after a deploy, and the log says a column, table or function does not exist.
A migration passes on your laptop and in staging, then fails or stalls on production data.
Writes to one table hang for the length of an index build, and the app looks down for everyone.
Serverless functions work for a while after a deploy, then every request fails with 'too many connections' or a connection timeout.
A retried webhook, job or button press creates duplicate rows, or two refreshes delete each other's rows.
The health check is green, but the app is quietly serving from an in-memory fallback or empty data.

Why it happens

Cause 1

Deploy order is not enforced

The new code ships before the migration it needs, or the pipeline does not run migrations at all. A shared dev and production database lets an untested migration reach production by accident. Some migration tools record a migration as applied when it did not run, so the schema and the migration table disagree.

Cause 2

Migrations were tested only on small data

A migration that is instant on an empty table can be slow or fail on production rows, for example a new required column that old rows cannot satisfy. In PostgreSQL, a normal CREATE INDEX blocks inserts, updates and deletes on the table until the build finishes. A busy tenant is down for as long as the build runs.

Cause 3

Connections are unbounded or held too long

Every serverless instance that opens its own pool adds to the database's connection limit. A transaction left open while the code waits on a slow external call holds its connection and its locks, and an idle open transaction also blocks vacuum cleanup. The pool fills with connections that are doing nothing.

Cause 4

Writes are not idempotent

Retries are normal: webhooks are redelivered, clients reconnect, jobs rerun. Without a unique constraint or an idempotency key, each retry inserts again. Two writers that each delete and re-insert the same rows can also remove each other's work.

Cause 5

Failure paths degrade silently

The app catches the database error, falls back to an in-memory store and keeps answering. A health check that returns 200 from the process proves the process is up. It does not prove the real store is reachable.

How to fix it

Run migrations once, before the code that needs them
Put the migration in the pipeline as its own step, not in app start-up where several instances can race. On Render, the pre-deploy command is meant for this: it runs after the build and before the new version goes live, and if it fails the deploy is aborted and the previous version keeps running. Use a separate production database. Make additive changes first (a nullable column), deploy the code, then tighten the constraint in a later migration.
```
# CI or pre-deploy step, with your tool's deploy command
npx prisma migrate deploy
```
Rehearse on a copy of production-shaped data
Restore a recent backup to a scratch database and run the migration there, timing it. Build indexes with CONCURRENTLY, which does not block writes but takes longer and cannot run inside a transaction block, so turn off your migration tool's automatic transaction for that file. If a concurrent build fails, it leaves an INVALID index (visible in psql with \d). Drop it and retry.
```
CREATE INDEX CONCURRENTLY events_tenant_idx ON events (tenant_id);
-- afterwards, in psql: \d events   (look for INVALID)
```
Give schema changes a lock timeout
lock_timeout aborts a statement that waits too long for a lock, so a blocked ALTER TABLE fails visibly instead of waiting. Set it in the session that runs the migration. Also set server-side limits so a stuck transaction cannot hold a connection indefinitely.
```
SET lock_timeout = '5s';
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10min';
```
Cap the pool and release the connection before slow calls
Create the pool once at module level and give it a maximum. Do not keep a client checked out while you call an external API. On Supabase, serverless code should use the shared pooler in transaction mode (turn off prepared statements in your driver), while migrations should use a direct connection. Pool size advice differs by platform. Vercel's guide warns against a maximum of 1 on Fluid compute and suggests a short idle timeout of around 5 seconds, so check your host's page.
```
const pool = new Pool({ max: 10, idleTimeoutMillis: 5000 });

const res = await fetch(slowApi);          // slow call first, no connection held
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT ...', [await res.text()]);
  await client.query('COMMIT');
} finally {
  client.release();
}
```
Make every write that must happen once idempotent
Add a unique constraint on the natural key (client plus source id, or the provider's event id) and insert with ON CONFLICT DO NOTHING. With RETURNING, an empty result means the row already existed, so read it instead of inserting again. For calls to Stripe, send an Idempotency-Key header on POST requests. Stripe returns the saved response to a retry with the same key, and returns an error if the parameters differ.
```
CREATE UNIQUE INDEX imports_client_source_uq ON imports (client_id, source_id);

INSERT INTO imports (client_id, source_id, payload)
VALUES ($1, $2, $3)
ON CONFLICT (client_id, source_id) DO NOTHING
RETURNING id;
```
Fail the readiness check when the real store is unreachable
Remove any in-memory fallback in production, and make start-up fail if the database is not configured. Kubernetes separates the two probes: liveness reflects the app's own health, while readiness can also check each required back-end service, so traffic is not sent to an instance that can only return errors. Keep the dependency check out of the liveness probe, or a database outage will restart healthy instances.
```
app.get('/ready', async (_req, res) => {
  try { await pool.query('select 1'); res.sendStatus(200); }
  catch { res.sendStatus(503); }
});
```

Check it's fixed

Restore a production backup to a scratch database, run the full migration set, and confirm it finishes in a time you can accept with no INVALID indexes left.
Deploy a change that needs a new column. Confirm the migration step ran first and that a failed migration stops the deploy.
Under load, watch connection counts (select state, count(*) from pg_stat_activity group by state) and confirm they stay below the database limit with no long idle in transaction sessions.
Replay the same webhook or request twice, then stop the database briefly. You should see one row, and the readiness check should return 503.

Fix it with Gemmein

On Gemmein, community, shared and direct collections have a shape you declare in a file, which Development enforces exactly as Live does, and a promote compares it with Live field by field without destroying anything. A keyed create gives your own retry its existing record back, and a relay your app calls answers the same person's retry with the same key from the same run (deduped: true).

Declare the shape before the app writes
Declare the fields of community, shared and direct collections in the collection's file, choosing maxLength from what the field is for, not from your test data; nothing is learned from writes. In Development, a write carrying a field the file doesn't declare is 400 undeclared_field, and a value of the wrong kind, too long, or a required field missing is 400 invalid_shape.
```
{ "rule": "community", "means": "members post updates",
  "fields": {
    "body": { "type": "text", "maxLength": 2000, "required": true },
    "mood": { "type": "text", "maxLength": 20, "default": "calm" }
  } }
```
Carry schema changes to Live with a promote
Edit the file, sync, and promote: a new field is added with one question for your human (what do existing records show?) unless the declaration's default answers it, a field declared required joins optional with the tighten offered on the next promote, and a tighter limit first counts the live records over it and applies only with your human's yes, to new writes and edits only, else promote refuses with tighten_needs_decision and writes nothing. A new field reaches Live only through a promote run, so until then send only the fields Live has; in Live, a 400 invalid_shape names the field that isn't in Live's fields or doesn't fit.
```
npx gemmein sync
npx gemmein go-live
```
Key writes that must not land twice
Pass a key derived from the thing that must be unique: a second writer gets 409 conflict, and your own retry gets your existing record back with existing: true. A relay your app calls answers the same person's retry with the same key from the same run (deduped: true); it answers 202 call_in_progress while that call is still working (ask again with the same key), and 409 call_not_finished if that call charged nothing or was refunded (send a new key).
```
create(data, { key: "slot:2026-07-15T15:00" })

const { records, balance } = await g.relays.call("book-class", { class: classId }, { key: tapId });
```
Read what the live pages hit
npx gemmein site errors --live reads what the Live site's pages reported from real visitors; while "Customer details for AI assistants" is off (the default), only each error's kind, where it happened in the site's own published files, the parts of page addresses the app itself names (every other part reads :x), Gemmein's own codes and how often come back. It needs the live:read grant, npx gemmein login --live-read, which your human approves in the browser; without it the answer is live_read_required.
```
npx gemmein login --live-read
npx gemmein site errors --live
```

Questions

Should I run migrations when the app starts?

Avoid it when you have more than one instance. They can race, and a failed migration then takes the app down with it. A separate pipeline step, such as Render's pre-deploy command, runs once and aborts the deploy on failure.

Why did a simple CREATE INDEX make my app unavailable?

A normal build blocks inserts, updates and deletes on the table until it finishes, though reads continue. CREATE INDEX CONCURRENTLY avoids that block at the cost of a longer build.

Which Supabase connection string should migrations use?

Supabase's guidance is a direct connection for migrations and other single-session tools, and the transaction-mode pooler for serverless code with prepared statements turned off.

How long does a Stripe idempotency key last?

For API v1, Stripe may remove keys once they are at least 24 hours old, and a reused key after that creates a new request. Keys can be up to 255 characters, and a retry with different parameters returns an error.

Why is my health check green when the database is down?

It probably checks only that the process responds. Add a readiness endpoint that queries the database and returns 503 on failure.