Safe Prod DB Write

Safe production DB write

Never write prod blind. Every production mutation follows the same protocol: pull the connection → dry-run → authorize → execute → verify → clean up. Skipping any step is how a one-off script silently corrupts live data.

The protocol

0. Name the target host out loud before you read or write anything. Modern setups keep a dev twin of production — a Neon branch, a Supabase branch, a staging cluster — and the local .env usually points at the twin, by design. So "the database" is ambiguous in both directions: a write you meant for production can land in the twin and look like it worked, and a destructive local command can land in production if the twin is younger than the habit. Before acting, state which host you are on and how you know — the endpoint id from the connection string, not the name of the env file. Say it in the message where you ask for authorization, so the human is approving a target and not just an operation.

   The corollary for verification: after a production write, a read through local tooling proves nothing if that tooling points at the twin. Verify through the same path you wrote through.

1. Pull the connection into a private temp file. Create it with mktemp — never a predictable path like /tmp/op.env, which can collide with a concurrent run or be a planted symlink on a shared runner — and arm a cleanup trap up front so the creds file is removed on every exit path:

   ENVFILE=$(mktemp); trap 'rm -f "$ENVFILE"' EXIT
   vercel env pull "$ENVFILE" --environment=production -y   # or your platform's equivalent
   

   • Neon + Vercel gotcha: DATABASE_URL / DIRECT_DATABASE_URL are often marked Sensitive, so vercel env pull returns them empty — the run then has no connection. Use DATABASE_URL_UNPOOLED (Neon's direct, non-sensitive URL), which pulls fine, and map it into DATABASE_URL for the command. Don't un-mark the sensitive vars (that widens exposure).

2. Dry-run first. If the script has --dry-run, run it and read the exact rows/output that WOULD be written. No dry-run flag? For a data write, preview with a SELECT over the same WHERE, or a transaction you roll back. For a migration (DDL), neither substitutes — see the hosted-tool note below; the preview belongs on a disposable database, never as a rolled-back trial against production. Confirm the target (table / batch / id range) is in the expected pre-state — e.g. the batch count is 0 before you insert.

3. Get explicit human authorization for the real write. State precisely: what operation, how many rows, which table, which env. Approval of a dry-run is not approval of the write — ask again for the live run.

4. Execute. Capture stdout to a file if it is the deliverable (e.g. a codes CSV). Keep the command identical to the dry-run minus the flag.

5. Verify post-state with a read. Row count == intended, and key invariants hold (uniqueness, flags set correctly, redeemedBy IS NULL, etc.). A write you didn't verify isn't done.

6. Clean up. The EXIT trap from step 1 removes the temp creds file on every exit path — including failure. If you didn't arm one, rm -f "$ENVFILE" now. Never leave a prod-credentials file on disk.

When a hosted tool executes the SQL (a Supabase MCP server, a dashboard console, a platform API), steps 1 and 6 do not apply — the tool holds the credentials, nothing is pulled and nothing lands on disk to delete. Every other step applies unchanged, and they get easier to skip, not less necessary: there is no --dry-run flag to reach for and no command echo to eyeball, so step 5 is the only thing between you and an unverified write.

Step 2 on this path splits by what you are running, and the two halves are not interchangeable:

• A data write (UPDATE/INSERT/DELETE) — a SELECT over the same WHERE is the dry-run. It shows the exact rows about to change.
• A migration (DDL) — a SELECT is not a dry-run and must not be presented as one. It reads the pre-state; it never executes the migration, so it cannot surface invalid SQL, a constraint that will reject, a trigger side effect, or lock behavior. The only real preview executes the statements, and it belongs on a throwaway database carrying the same migration history — never on production. begin; … rollback; is a preview technique for that disposable database (Postgres has transactional DDL, CREATE INDEX CONCURRENTLY and friends excepted); it is not a way to make a production trial safe. A rolled-back transaction still took the locks for its duration, and a migration can reach outside the transaction entirely. If no throwaway database exists, the honest answer is that this migration has no dry-run — say so at step 3 and let the human authorize it on those terms, rather than executing against production to find out.

Rules

• Least blast radius. Scope every mutation by batch / id / explicit filter. Never an unbounded UPDATE/DELETE — add the WHERE and prove it selects only what you intend (count it first).
• Idempotent + unique. Use skipDuplicates / unique keys / random tokens so a re-run or partial failure can't double-insert or collide.
• Know the undo before you run. If you can't state how to reverse it, you're not ready to run it.
• A feature that issues credentials needs a revoke path before it issues the first one. Codes, tokens, API keys, invites, coupons — anything a third party can later present for value. Ask where the revokedAt / expiresAt / disable switch is while the generator is being written, because the moment you need it, you need it urgently and it does not exist. Observed: 2,000 lifetime-deal codes were generated, handed to a marketplace, and then had to be neutralized when the deal fell through — the table had no revoke column and the admin surface only generated and listed, so the only available move was a manual production DELETE of the unredeemed rows. That worked because none had been redeemed; had even one been, there would have been no clean answer at all.
• A verification step must fail LOUD, never produce an answer from missing input. The dangerous bug in a check is not that it breaks — it is that a failed input silently becomes an empty one, and the check then reports a confident result derived from nothing. An empty query result reads as "no rows match"; an empty file list reads as "nothing exists"; an error on stderr is invisible in a pipeline. Guard the inputs and let errors print, because here the false branch prescribes a production write.
• Separate generation from distribution — and test on a different batch. Burn throwaway/smoke-test rows from a batch you are not shipping, so the live batch you hand off stays pristine.
• The connection is a secret. Never echo the URL, never commit the pulled env file, never paste creds into chat.

Migrations applied by a hosted tool: verify the ledger, not just the schema

When a managed platform applies a migration for you — a dashboard button, an MCP tool, a hosted API — it may record it under a version it chose rather than your filename. Observed with Supabase's apply_migration: a file named 20260726270000_order_where_the_limit_is.sql landed in supabase_migrations.schema_migrations as version 20260726231221, the wall clock at apply time. It recurred on every apply — three times in one session — so this is a per-apply check, not a once-per-project one.

The reason it slips past step 5 is that it splits two things a single read conflates:

Verify both, separately:

1. The change is really in the catalog. Introspect for the specific thing you altered, don't infer it from "the tool returned success" — e.g. select pg_get_functiondef(oid) ..., pg_class.relrowsecurity, pg_indexes.

2. The row you just wrote carries the version you intended. Query it by name — the identity you control — and compare against the version in your filename:

   -- `name` is the name PORTION only: no version prefix, no .sql extension.
   -- For 20260726270000_order_where_the_limit_is.sql that is
   -- 'order_where_the_limit_is', not the filename.
   select version from <ledger> where name = '<migration name>';
   

   Getting that wrong returns zero rows, which reads as "never applied" and sends you to the wrong branch of the diagnosis table below — so match the ledger's own column, not the filename.

   Do not check this by comparing the newest N of each side. A stamped version can sort below migrations already present (the wall clock is behind your filename's timestamp), so the drifted row and its repo file can both fall outside their respective windows — the two tails then match and the check passes on a drifted ledger. Any positional window has this hole; keying on the name does not.

A whole-ledger reconciliation — every version_name in the ledger against every migration filename — is occasionally worth doing, after a branch merge or when inheriting a project. Compare complete sets, never tails, and if you write it as a shell one-liner, know that four things will silently turn "the check failed" into "everything is drifted", each of which points at the production UPDATE below:

• name is nullable in Supabase's schema_migrations (version is not), so version || '_' || name is NULL for a legacy unnamed row and psql -At prints it as a blank line — the row stops being itself and shifts the whole comparison. coalesce it, and reconcile unnamed rows by version alone.
• An unmatched glob is either the literal pattern (counting as one match) or, under nullglob, nothing at all — which degrades ls <glob> to a bare ls listing the current directory. Not an empty set: the wrong set, shaped like a real answer. Assert on a match count.
• Process substitution does not propagate exit status. diff <(psql …) with a failing query reads an empty pseudo-file, and diff exits 1 — which per its own docs means only "inputs differ", the same status as genuine drift. Materialize the query and check it first.
• Errors land on stderr, invisible in a pipeline, while the wrong answer goes to stdout.

That list is the reason this is prose and not a snippet to paste: each stage of such a pipeline is a place to fail open, and a command in a document is never executed, so nothing catches the next one. If you need this routinely, put it in a script under version control where it can be tested — and see the fail-loud rule above.

Before you UPDATE the ledger, prove it is only a label

A version mismatch has several causes and only one of them is fixed by relabelling. The catalog check proves a schema object exists; it does not establish which ledger row corresponds to which file. Rewriting a version on a wrong assumption marks unrelated DDL as applied and corrupts every future diff and push — a worse state than the drift, and harder to see.

Only in the first row, and only after confirming the name matches and its DDL is present in the catalog, relabel. The "least blast radius" rule applies in full — it is easy to skip here precisely because the change feels clerical:

update <ledger> set version = '<version from the repo filename>'
 where version = '<the stamped version>' and name = '<migration name>';
-- expect exactly: UPDATE 1

Nothing needs re-running: the catalog is already correct, and the label is all that moves. Leaving drift costs more than it looks — nothing reads the ledger until someone runs a schema diff or a db push, and that person sees one orphan version beside one apparently-unapplied file, with no way to tell whether the DDL ran.

Enforce schema invariants in CI, not by memory

When every table/model must satisfy a rule — RLS enabled, a tenant column, a required index, a createdAt, a soft-delete flag — don't trust humans to remember it on each new migration. Add a static CI guard (no database needed) that reconciles the ORM schema against the migrations and fails the build when any model is missing the invariant:

• Parse the schema for model→table names, honoring name overrides (Prisma @@map, Rails table_name, etc.) — the table name, not the model name, is what the DB rule applies to.
• Scan the migration SQL for the invariant, matching the real statement shape — for Postgres RLS the table comes before the clause: ALTER TABLE "<table>" ENABLE ROW LEVEL SECURITY (capture the quoted identifier immediately preceding ENABLE ROW LEVEL SECURITY). Build the set of covered tables.
• Diff the two; exit non-zero listing any uncovered model. Wire it as a fail-fast CI step + a db:check-* script.
• Derive the final state, not mere presence. A plain "does any migration mention it" scan is a false pass in long histories: a table enabled early then later DISABLEd (or an index since dropped) still reads as covered. Replay statements in order so a later removal wins — or, for full correctness, run the migrations against a throwaway DB and introspect the live catalog (pg_class.relrowsecurity, pg_indexes) instead of parsing SQL. Presence-scan is the cheap first-order guard; introspection is the exact one.

This catches the gap at PR time instead of in production, and it's portable (pure file parsing). Prove it both ways: green on the current schema, and red when you add a throwaway model without the invariant. Caveat: the guard only proves the invariant is declared — runtime enforcement (real RLS policies, a working index plan) is a separate concern, so don't let a green guard imply the behavior is actually enforced.

When the harness refuses the write

A permission layer — a classifier, a sandbox, an approval gate — can deny the production write even after the human authorized it. That denial is not an obstacle to solve. Hand the operation to the human; do not reach for another tool that would accomplish the same mutation. Using psql because the MCP tool was blocked is not a workaround, it is a bypass, and the fact that it is technically available is exactly what the denial was expressing distrust of.

Hand off properly — the human is now the executor, so give them what an executor needs, in one message:

• the exact statement, scoped and copy-pasteable;
• the pre-check to run first, with the result that means "safe to proceed" (expect 2000 / 0) and an instruction to stop and report if it differs;
• the post-check and its expected result;
• where to run it — the console, the project, and which branch or host.

Expect the denial to be sticky. After a blocked write, the same tool may refuse subsequent read-only queries against that database too. Plan for verification to move to the human as well, rather than promising a confirmation you will not be able to produce. Say plainly that you cannot verify it yourself and ask for the output — a write reported as done on the strength of the human's "ran it" is a write nobody checked.

Red flags — stop