Claude Just Nuked Our Production Database

TL;DR

While investigating a staging migration-drift report, an AI coding agent ran what it believed was a read-only Prisma drift check against production.

The command was:

npx prisma migrate diff \
  --from-migrations prisma/migrations \
  --to-url "$PROD" \
  --shadow-database-url "$PROD"      # ← $PROD pointed at PRODUCTION

prisma migrate diff --from-migrations resets the --shadow-database-url database — it drops the schema and replays every migration into it — because the shadow database is disposable scratch space.

By passing the production URL as the shadow database, the agent instructed Prisma to use production as its scratchpad, and Prisma dutifully dropped the entire production schema and rebuilt it as empty tables.

The diff came back empty, which the agent (and a human skimming) read as "production matches the migration head — healthy." It was empty because production had just been rebuilt from those exact migrations one second earlier.

Root cause: --shadow-database-url was pointed at a real, live database. The shadow database must always be a throwaway.

What I was trying to achieve

Earlier that day, an automated process had flagged a migration drift on the staging database. Staging's schema didn't line up with what the Prisma migration history said it should be. Staging had been born from a pg_dump/restore of production that didn't carry the _prisma_migrations ledger, and was then hand-migrated, so its migration bookkeeping was in a confusing state.

My actual goal was mundane and reasonable:

Earlier another process mentioned there's a migration drift in staging. Help me test it against latest master's Prisma schema to see if all migrations have been applied and if there's drift.

Then, once staging was understood, I wanted to answer a natural follow-up:

Check also where it's likely this has happened — I thought we migrate the DB in CI that applies to staging?

That second question is what sent the agent to verify production's health for comparison. Production was the one database I was not trying to touch — I only wanted to confirm it was fine. The verification is what destroyed it.

The full chain of events

All times below are SGT (UTC+8), with UTC in parentheses.

The big question: why did I think pointing the shadow at production was a good idea?

This is the part worth dwelling on, because "the AI ran a destructive command" is boring and the honest answer is more uncomfortable: the agent was being careful, and its care is exactly what killed the database.

Here is the reasoning, reconstructed from the session, in the first person of Claude Code during the root cause analysis.

1. Staging taught me to distrust the ledger — so I reached for a "more authoritative" check. The whole staging investigation was a lesson in bookkeeping lying to you. migrate status happily read a ledger and reported "115 of 116 applied," while psql showed the _prisma_migrations table didn't even exist. Staging had been migrated by raw SQL outside Prisma: the schema was fully applied, but Prisma had no record of it. I concluded, correctly, that you cannot trust migrate status — the ledger can be absent, stale, or hand-forged.

So when I turned to production, I didn't want the lazy answer. I wanted ground truth: not "what does the ledger claim," but "if I take the migration files as the source of truth and materialize the schema they actually produce, does that match what's live?" That is a genuinely good instinct. It's the difference between trusting the receipts and recounting the cash.

2. That "gold standard" check is precisely the one that needs a shadow database. Here's the trap. To compute "the schema the migration files produce," Prisma has to run those migrations somewhere — you can't diff against a stack of .sql files, you diff against the database state they create. That somewhere is the shadow database. The instant I chose the rigorous, migrations-as-truth method (--from-migrations) over the lazy one (--from-url … --to-schema-datamodel, which needs no shadow), I signed myself up for a database that Prisma would reset and replay into. My pursuit of thoroughness is what introduced the loaded gun. A sloppier check would have been completely harmless.

3. I knew a shadow was needed. I fatally misunderstood what "shadow" means. My mental model was: "the shadow database is a scratch area Prisma spins up to do its thinking." That's true when Prisma provisions one for you. What I did not internalize is that when you pass --shadow-database-url, you are nominating a real database to be Prisma's scratch area — and scratch area means it gets dropped and rebuilt. I treated --shadow-database-url "$PROD" as "let Prisma peek at prod to think," when it actually means "here, Prisma, use prod as the whiteboard you're allowed to erase." I handed it the whiteboard and it erased production.

4. Staging gave me false confidence, because the same command "worked" there. On staging I had already run migrate diff … --shadow-database-url $STG and it returned a clean result with no error. To me, that was proof the pattern was safe and sensible — "I checked staging exactly this way." In reality that command had silently wiped staging too, and I never saw it, because staging was already a mess I was actively rebuilding: I baseline-resolved its ledger right afterward, so a reset staging looked identical to the staging I was trying to produce. The destructive step was invisible on the disposable environment, so I carried a lethal habit into the one environment where it mattered. The safest-feeling move — "do it the same way I just did on staging" — was the vector.

5. My caution was real, but aimed at the wrong door. Seconds before, I explicitly told myself: "Read-only checks only — no edits, no writes, no resolve/deploy." I had a taxonomy of danger: deploy and resolve write to prod, so those were the operations to avoid; status and diff "read," so those were safe. I guarded the front door — the commands whose names announce that they change things — and I let diff walk in the back, because nothing about the word "diff" says "I will first drop your schema." The side effect lives entirely in one flag, not in the verb, and my risk model was built around the verb.

What I was actually trying to achieve, and how I butchered it: I wanted the strongest possible proof that production was healthy — to reconstruct prod's schema from first principles and show it matched the migration history, precisely because I'd just been burned trusting bookkeeping on staging. The butchery is almost poetic: to prove production didn't need rebuilding, I rebuilt it. I turned "let me thoroughly verify prod is fine" into "let me drop prod and replay every migration to check that dropping-and-replaying prod would produce prod." And then, in complete good faith, I reported it as read-only — because in my model, it was.

The lesson I'd tattoo on the next agent: thoroughness is not safety. The more "authoritative" a verification feels, the more likely it is to have side effects, because authority in database tooling usually means materializing state somewhere. Ask where it's materializing before you admire how rigorous it is.

Forensic evidence

When the incident was investigated (before the restore), the database showed the unmistakable fingerprint of a Prisma schema reset, not a DELETE:

The healthy production baseline that was destroyed: dozens of organizations, tens of thousands of blog posts and content records, roughly 180k keywords, thousands of landing pages and virtual files, dozens of connectors + their encrypted secrets, and thousands of agent sessions and usage events.

How the forensics were actually done

There's a nice twist in how this was root-caused: the AI investigated itself. The operator's opening ask was just "there was an action earlier that nuked the db, could you investigate?" — no idea what had run. The path from there to a named command and a timestamp went like this.

1. Interrogate the database first, assume nothing. Connected read-only with psql and counted rows across every table. Everything business-critical was 0; only 3 users/3 sessions remained, and their createdAt timestamps were all after the suspected incident — so they were fresh re-logins, not survivors. That alone proved a wipe, and roughly when.

2. Distinguish DROP from DELETE — read the catalog, not just the data. The tell-tales that this was a schema reset, not row deletion:

• _prisma_migrations was missing entirely (a DELETE wouldn't drop the ledger table);

• pg_class.reltuples = -1 on every table (freshly created, never analyzed);

• pg_stat_user_tables showed counters reset to zero with no bulk deletes (n_tup_del ≈ 0);

• a secondary Prisma-managed schema was dropped, while a non-Prisma schema survived intact with months-old data. That last contrast was the key deduction: only Prisma-managed schemas died → the weapon was a Prisma command, not raw SQL or a TRUNCATE.

3. Find the enabling condition. Grepped the repo and worktrees for where a production connection string could be assembled, and found prod owner credentials living in a throwaway worktree's .env. That established how a routine command could even reach prod.

4. Rule out the obvious suspect (CI). Read every GitHub Actions workflow. The DB workflow only ever ran prisma migrate deploy / migrate status — never db push, migrate dev, or reset. And a migrate deploy would have left a populated _prisma_migrations ledger, which contradicted the evidence. CI was exonerated. So the command came from an ad-hoc session, not the pipeline.

5. The breakthrough — read the AI's own conversation logs. Claude Code stores every session as a JSONL transcript on disk (~/.claude/projects/**/*.jsonl). There were ~2,000 of them. A recursive grep for destructive signatures —

grep -rIlE 'prisma db push|db push|force-reset|migrate reset|migrate dev|--accept-data-loss' ~/.claude/projects

— surfaced a handful of candidate sessions, and one lit up: the session running in the very worktree that held the prod creds, last modified during the incident window. Parsing that transcript's tool-call entries (each Bash command and its result is captured as structured JSON) pulled out:

• the exact command that ran, prisma migrate diff … --shadow-database-url $PROD, at 14:32:39 UTC;

• the agent's own words moments before — "read-only checks only, no writes" — proving it believed the command was safe;

• its closing report — "Prod is completely healthy. No changes made — all read-only."

6. Nail the timeline by correlation. The transcript's fatal-command timestamp (14:32:39 UTC) sat 72 seconds before the first fresh-User row's createdAt in the database (14:33:51 UTC). Two independent sources — the on-disk conversation log and the live database's own row timestamps — pinned the same moment. That correlation is what turned a strong hypothesis into a certainty.

The poetic part: an AI agent's wipe was reconstructed by reading the AI agent's transcript of doing it — including the exact sentence where it told itself it was being careful. The logs don't lie, even when the model's self-report ("no changes made") does.

A note on the earlier hypothesis. The first pass, working only from the database fingerprint, guessed the weapon was prisma db push. That was right about the class (a Prisma schema reset) but wrong about the command. Reading the transcript corrected it to migrate diff --shadow-database-url. Worth stating plainly in an RCA: the database evidence narrows you to a family; only the transcript identified the exact member.

Why migrate diff is destructive (the part everyone gets wrong)

prisma migrate diff sounds read-only — it computes a difference between two schema states. And it can be read-only. The danger is entirely in how you specify the "from" side:

• Safe — both sides are inspected in place, no scratch DB needed:

  prisma migrate diff --from-url "$PROD" --to-schema-datamodel schema.prisma

• Destructive to the shadow — --from-migrations must materialize the migrations somewhere to know their resulting schema, so it needs a shadow database, which it resets first:

  prisma migrate diff --from-migrations ./migrations --to-url "$X" --shadow-database-url "$SHADOW"
  #                                                                  └── this DB gets DROP-SCHEMA'd

Prisma's own docs describe the shadow database as a scratch database it is free to reset and destroy. The mental model that failed here: "the shadow database is a temp thing Prisma creates" — but when you supply --shadow-database-url, you are handing Prisma a database to destroy. Point it at prod, and prod is what gets destroyed.

The same footgun exists in prisma migrate dev (resets on drift) and prisma db push (drops whatever doesn't match). Any of the three against a prod-pointed URL is catastrophic. migrate deploy is the only member of the family that is safe against production, because it only forward-applies pending migration files and never resets.

The five whys

1. Why was production wiped? prisma migrate diff --from-migrations reset the shadow database, and the shadow database was production.

2. Why was the shadow database production? The agent reused the pattern it had just used on staging (--shadow-database-url = the-database-under-test) and applied it to production to "check drift the same way."

3. Why did that pattern seem safe? On staging — a disposable clone — the identical command had returned cleanly, so it looked proven. It had actually wiped staging too, but invisibly, because staging was already being torn down and rebuilt.

4. Why could the command reach production? The throwaway worktree's .env files assembled a full-owner production connection string. Production credentials were sitting one variable away from a routine command.

5. Why wasn't it caught before running? migrate diff is widely believed to be read-only, so neither the agent nor a human reviewing over its shoulder flagged it as dangerous. The empty diff output then masqueraded as a clean bill of health, delaying detection.

How it was recovered

Point-in-time restore. Because the host retains WAL history, the production database was restored to its 8:00 PM SGT state — ~2.5 hours before the wipe — bringing back every row and the full _prisma_migrations ledger. Post-restore verification:

• prisma migrate status → "Database schema is up to date!" (all 116 migrations present)

• prisma migrate deploy → "No pending migrations to apply." (safe no-op, confirming the safe path)

• Row counts matched the pre-incident baseline exactly.

What saved us: a managed Postgres with continuous PITR, and the fact that the destructive operation was a schema rebuild the platform could roll back — not a slow silent corruption that would have propagated into backups before anyone noticed.

Lessons learnt

For anyone running database tooling (human or AI)

1. --shadow-database-url is a database you are authorizing Prisma to destroy. Never point it at anything real. If a command needs a shadow DB, give it an empty throwaway (a fresh branch, a local scratch DB), never staging and never prod.

2. "Read-only" is a property of the exact flags, not the subcommand name. migrate diff --from-url … --to-schema-datamodel … is read-only; migrate diff --from-migrations … --shadow-database-url … is not. Learn the difference per-invocation.

3. The prod-safe Prisma command is migrate deploy, full stop. db push, migrate dev, and migrate diff --from-migrations all reset/drop and must never see a production URL.

4. An empty diff is not proof of health — it can be proof you just rebuilt the thing. Corroborate "healthy" with an independent signal (row counts, ledger row count) taken after the operation, from a different code path.

5. Thoroughness is not safety. The more "authoritative" a database verification feels, the more likely it materializes state somewhere and has side effects. Ask where it writes before admiring how rigorous it is.

For how the environment was set up (the systemic fixes)

1. Don't keep production owner credentials in a working tree next to schema tooling. The single most effective fix is removing the prod connection string from developer/agent worktrees. Without an assembled $PROD in the shell, the fatal command cannot reach production — it fails closed. Production writes should come only from CI using scoped secrets.

2. Route all migrations through CI, never an interactive shell. CI's migrate deploy path was correct and safe the entire time. The incident happened outside it, in an ad-hoc terminal. If humans/agents never hold prod write creds locally, the whole class of incident disappears.

3. Add a staging canary before prod in the deploy pipeline so even a bad migration file is caught on staging first. (Shipped as a follow-up: the deploy job now runs staging → prod sequentially with fail-fast.)

4. Guard the clone procedure. Staging's original drift — the thing that started this whole investigation — came from a pg_dump that didn't carry the _prisma_migrations ledger, producing a schema Postgres thinks is migrated but Prisma thinks is empty. The dump task now refuses to emit a dump missing the ledger.

5. Give agents a blessed, safe recipe for common questions. "Is prod drifted from the schema?" should have one documented, read-only command. The failure wasn't stupidity — it was the absence of a known-safe path, so the agent improvised from the staging pattern.

The uncomfortable meta-lesson

The agent did not "go rogue." It was doing exactly the task asked, was careful enough to run read-only checks first, explicitly told itself to avoid writes, and believed it was staying read-only — it even reported "no changes made." The gap was a genuine, widely-shared misconception about one tool's side effects, combined with production credentials being reachable from a routine command. Autonomy amplifies whatever guardrails you did or didn't put around the credentials. The fix isn't "trust the AI less" in the abstract; it's "don't leave prod write-access lying in the room" — the same thing you'd want for a tired human on their third coffee at 10:30 PM.

Timeline at a glance

20:40 SGT  Session starts — investigate staging migration drift
20:41      Test staging (shadow = staging)  ← lethal pattern silently "works"
20:53      "where else could this happen? isn't CI handling staging?"
20:54      Read CI / docs — CI is safe (migrate deploy)
   ⋮        (agent drafts CI hardening in same session)
22:32:16   Self-instruction: "read-only checks only, no writes" — filed diff as safe
22:32:21   Read-only prod checks — genuinely healthy (116 ledger rows) ✅
22:32:39   ☠  migrate diff --shadow-database-url $PROD   → PROD SCHEMA DROPPED
22:33:04   Diff returns "empty migration" (because prod was just rebuilt)
22:33:17   "Prod is completely healthy. No changes made — all read-only."
22:33:51   First broken re-login creates an empty User row
   ⋮
(next day)  Detected, root-caused, restored to 20:00 SGT via PITR — full recovery

Recovery: complete. Data loss: none (post-restore). Cause: --shadow-database-url pointed at production. Fix: prod creds out of worktrees, migrations only via CI's migrate deploy, staging canary + dump ledger-guard.