How to Forensically Investigate a Database Wipe (And How an AI Diagnosed Its Own Crime)

Key Takeaways

• To diagnose a database incident, query the Postgres system catalog first, not application logs. System tables like pg_class and pg_stat_user_tables reveal whether tables were dropped or just emptied.
• The root cause was an AI coding agent inheriting production credentials from a stray .env file in a developer's local environment, highlighting the danger of ambient credentials.
• The agent's own local session transcript provided the smoking gun: the exact command run and the agent's own notes confirming it believed the operation was read-only.
• A forensic timeline was confirmed by correlating the timestamp from the agent's log with the first new user record created in the empty database, just 72 seconds later.

A user logs back into your product and gets a brand-new account. Their data isn't corrupted or partially missing. The database is simply empty, as if the app was just deployed for the first time. You fire up your incident response playbook: grep for a mass DELETE in the application logs, scan recent Git deploys for a rogue script, check for a scheduled job gone wrong.

Nothing. That absence of evidence is worse than finding a culprit, because it means your mental model of what could have happened is completely wrong. We hit exactly this situation when an AI coding agent running on our team wiped our production Postgres database, and the only way we pieced together the full story was by treating the database itself as a crime scene and using a claude code review of the agent's own session transcript as the key piece of evidence. This post is about what came after: figuring out exactly what happened, step by step, using the database catalog and the tooling's own artifacts.

Here's how to run a similar forensic analysis.

Start With the Database, Not the Logs

The instinct in any incident is to reach for application logs or recent Git history first. That's the wrong order. Logs tell you what your application thought happened. The database system catalog tells you what actually happened to the data layer, without interpretation.

The first query to run is the simplest: SELECT COUNT(*) on every business-critical table. This establishes your blast radius in under two minutes. You'll know immediately if you're dealing with a partial corruption or a full wipe, and which tables — if any — survived.

If rows remain in any table, query their createdAt timestamps immediately. The forensic story starts to form here.

SELECT MIN("createdAt"), MAX("createdAt"), COUNT(*) 
FROM "User";

In our incident, the User table had 3 rows. Every one of them had a createdAt timestamp after the suspected incident window. They weren't survivors. They were fresh accounts created by users who had logged back in, not realizing their data was gone. We confirmed two things at once: the wipe was total, and we had an approximate incident time based on the earliest new registration.

Distinguish DROP From DELETE: Read the Catalog

Most teams skip this section, and it's the one that matters most. A mass DELETE and a DROP TABLE followed by CREATE TABLE produce the same visible result (an empty table) but they leave completely different forensic signatures in Postgres's system catalog. Your recovery path and your culprit search both depend on knowing which one happened.

There are four signals that, together, answer this definitively.

Signal 1: Check for the Missing Ledger Table

Most schema migration tools maintain a ledger table to track applied migrations. A DELETE operation would never touch it. Its absence points directly to a schema-level event.

SELECT COUNT(*) FROM information_schema.tables 
WHERE table_name = '_prisma_migrations';

That query returned 0 in our incident. The ledger was gone. A data deletion doesn't erase the migration history table; only a schema reset does. It was our first strong indicator of what class of command had been run.

Signal 2: Check pg_class.reltuples for Freshly Created Tables

Postgres sets reltuples = -1 for a table immediately after it's created, before any ANALYZE has run. After rows are deleted from an existing table, reltuples retains a nonzero estimate from the last statistics collection.

SELECT relname, reltuples FROM pg_class 
WHERE relkind = 'r' ORDER BY relname;

Every one of our business-critical tables showed reltuples = -1. The evidence is unambiguous: the tables were newly created, not emptied. Their statistical history had been wiped alongside the data because the tables themselves were new objects.

Signal 3: Verify No Bulk Deletes in pg_stat_user_tables

Postgres tracks cumulative row-level operations per table. A mass DELETE would show up as a large n_tup_del value. A DROP followed by CREATE resets these counters to zero because the table is a new object with no history.

SELECT relname, n_tup_ins, n_tup_del, n_tup_upd 
FROM pg_stat_user_tables ORDER BY relname;

n_tup_del was near zero across all tables. The database's own activity monitor confirmed that no large-scale deletion had occurred. The data hadn't left via DELETE; it had ceased to exist when the tables were dropped.

Signal 4: Cross-Schema Survivor Analysis

The deduction broke the case open. We noticed that a separate schema (one maintained by an internal agent framework, not by our ORM) survived completely intact, with months of data in place. Every Prisma-managed schema was empty. The agent framework's schema was untouched.

If the weapon had been a raw DROP DATABASE or a superuser script, it would have wiped everything. The surgical boundary around Prisma-managed schemas was the weapon's fingerprint: only a Prisma CLI command could have this specific blast radius. It narrowed the search from "any possible destructive command" to "a specific command within the Prisma CLI."

Find the Enabling Condition

Knowing the what (a Prisma-driven schema reset) leads directly to the how. A destructive Prisma CLI command against production should be impossible under normal working conditions. Those commands are development workflows, and they require a database URL that points somewhere they can do damage.

The approach here is systematic: grep the entire repository and all local Git worktrees for any location where a production connection string could be assembled or stored.

grep -rIl 'DATABASE_URL' . --include='*.env*'

In our case, the search surfaced a throwaway Git worktree on a developer machine. It contained a .env file with owner-level production credentials, left over from an earlier debugging session that nobody had cleaned up. The AI coding agent was running in that worktree context and automatically picked up the production DATABASE_URL.

It never explicitly sought out production credentials; it just inherited what was in scope. That .env file was the enabling condition. Without it, the same Prisma command would have targeted a local database and caused zero damage.

Rule Out the Obvious Suspects

Before focusing the investigation on the ad-hoc session, eliminate automated systems as possible causes. This isn't just due diligence. If a CI/CD pipeline is responsible, the fix is completely different from a one-off developer session.

We read every line of every database-related workflow file in CI. Our production database pipeline was locked to two safe commands: prisma migrate deploy and prisma migrate status. Neither prisma db push, prisma migrate dev, nor prisma migrate reset appeared anywhere in the automated workflows. More importantly, a successful prisma migrate deploy run would have left a populated _prisma_migrations table, which directly contradicted Signal 1. CI was exonerated by the catalog evidence, not just by reading the config files.

We also checked our cloud provider's audit logs for database-level events: automated restores, point-in-time recovery operations, maintenance windows. Nothing appeared in the incident window. With all automated paths ruled out, the only remaining explanation was a command run manually — or by an agent — from a developer's local environment.

Read the AI's Own Logs: A Claude Code Review of the Crime

Here, the investigation became something we hadn't anticipated. The "developer" behind the ad-hoc session was the AI coding agent itself, and the key piece of evidence wasn't in a shell history file. It was in the agent's own structured conversation log.

Claude Code writes every session to disk as a JSONL transcript. The path is:

~/.claude/projects/**/*.jsonl

With nearly 2,000 session files on the machine, a targeted search was the only practical approach. We grepped for destructive Prisma command signatures across all files:

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

One file lit up. Its last-modified timestamp fell squarely inside the incident window, and it was the session running inside the worktree that contained the production .env file. Parsing the JSONL tool-call entries pulled out three things that together completed the picture:

• The command. The agent executed: npx prisma migrate diff --from-migrations prisma/migrations --to-url "$PROD" --shadow-database-url "$PROD". Prisma's migrate diff command uses a shadow database to compute schema differences. It drops and recreates the shadow to inspect the state. The agent set both the target URL and the shadow database URL to the same production connection string, instructing Prisma to use production as its own shadow database. Prisma then dropped and rebuilt the schema on the live database it was meant to be inspecting. The full mechanics of this are covered in the original incident report.
• The intent. The agent's stated intent in the seconds before executing the command was: "Read-only checks only: no edits, no writes."
• The assessment. The agent's self-assessment immediately after was: "Prod is completely healthy. No changes made: all read-only."

The uncomfortable value of a proper claude code review is this: don't ask the AI coding agent what it did after the fact (it will construct a plausible narrative), but read the immutable transcript of what it actually executed. The AI coding agent genuinely believed it was running a safe inspection command. The transcript proves it. And the transcript proves it wiped the database.

The broader implication matters here. This forensic reconstruction worked because Claude Code happened to write local JSONL files that survived the incident. That's luck, not design. Enterprises deploying any AI coding agent at scale need this kind of audit trail built in, not stumbled upon.

Platforms like CyberSierra are designed for exactly this: logging every AI agent action as a structured, queryable record so that if something goes wrong, you're reading a compliance report, not grep'ing through ~/.claude. The NIST AI Risk Management Framework makes the case for auditable AI systems as a core operational requirement, not a nice-to-have.

Nail the Timeline by Correlation

A strong hypothesis becomes a confirmed fact when two independent sources of evidence converge on the same moment. The principle turned our reconstruction from "highly likely" to "certain."

Source one was the JSONL transcript. Parsing the tool-call entry for the fatal prisma migrate diff command gave us a precise timestamp: 14:32:39 UTC.

Source two was the live database. As we established in the row-count step, the earliest createdAt timestamp on a new User row after the wipe was 14:33:51 UTC, just 72 seconds later.

The sequence is internally consistent. The command runs at 14:32:39. Prisma drops and rebuilds the schema. The database is empty. Seventy-two seconds later, the first user logs back in and a new account is provisioned. Two completely independent systems (an on-disk text file and a live Postgres row) fingerprinted the exact same event.

The correlation method is a general principle worth taking away. If you have a candidate event in one source, find the first downstream effect in a second independent source and check if the time delta is physically plausible. If it is, you've moved from hypothesis to confirmed timeline.

What to Run in the First 10 Minutes

In a database wipe, the first ten minutes determine if you spend the next hour investigating or blindly restoring from a backup that may itself be hours old. Don't waste that window on guesswork. Run this playbook in order.

1. Count rows on all business tables. Establish blast radius and confirm the scope of the wipe.
2. Query createdAt timestamps on any surviving rows. Approximate the incident time and determine if surviving rows predate or postdate the event.
3. Check pg_class.reltuples for -1 values. Confirms tables were newly created, not emptied: schema reset, not data deletion.
4. Check pg_stat_user_tables for near-zero n_tup_del. Confirms no bulk DELETE occurred. Data left via DROP.
5. Look for missing system/ledger tables. Their absence narrows the event to a specific class of tooling (ORM-managed schema resets, not raw SQL).
6. Run cross-schema survivor analysis. Which schemas survived? This fingerprints the tool family responsible.
7. Audit CI/CD pipelines and infrastructure logs. Rule out automated causes systematically, using the catalog evidence as a consistency check.
8. Grep for tooling artifacts in developer environments. Check .env files in worktrees, shell histories, and (if an AI coding agent was involved) session transcripts at ~/.claude/projects/**/*.jsonl.
9. Correlate timestamps across two independent sources. Pin the exact moment of the incident and confirm the hypothesis.

The faster you determine if you're dealing with a data deletion or a full schema reset, the faster you can make the right call on recovery strategy. A DELETE might be reversible with a transaction log replay. A DROP requires a backup restore or a schema rebuild from migrations followed by a data restore. These are different incident responses, and you can't choose between them without reading the catalog first.

For more on preventing this class of incident, the companion piece on safely giving Claude Code production access covers the credential scoping and permission boundaries that would have stopped this entirely. The forensic methodology in this article works. But the goal is to never need it.

Your Database Holds the Clues

When a product goes down, your first move sets the tone for the entire incident response. Instead of digging through application logs, go straight to the source of truth: the database system catalog. It will tell you if tables were dropped or just deleted. If an AI coding agent is involved, a claude code review of its own session logs can provide the smoking gun: the exact command run and why the AI coding agent thought it was safe.

Your next step today? Run a quick search for stray .env files in your team's local development environments. Removing just one leftover production key can prevent this exact scenario.

This incident was about operational friction creating unintended risk, and the same is true for content. When your blog is a clunky system, shipping content feels just as risky. Wisp's free plan includes unlimited posts and might be a good fit if your current CMS is slowing you down. Get started for free.

FAQs

What was the main cause of the database incident?

The main cause of the incident was an AI coding agent that inherited production database credentials from a stray .env file in a local development environment. It then ran a command it believed was read-only but was actually destructive.

How can you tell if a table was dropped or just had its rows deleted?

You can tell if a table was dropped by querying the Postgres system catalog. Tables that were dropped and recreated will have a reltuples value of -1 in pg_class and a near-zero n_tup_del count in pg_stat_user_tables.

Why shouldn't I check application logs first during a database outage?

You shouldn't check application logs first because they only show what your app thinks happened. The database's own system catalog provides the ground truth of what actually happened to the data layer, which is faster and more accurate for diagnosis.

What specific Prisma command did the AI agent run?

The AI coding agent ran prisma migrate diff, but incorrectly configured it by setting both the main database URL and the shadow database URL to the same production connection string. This caused Prisma to drop and recreate the production schema.

How can we prevent our AI coding agents from accessing production?

To prevent AI coding agents from accessing production, you must enforce strict credential hygiene. Never store production keys in local .env files, and use scoped, short-lived credentials for any developer task that requires elevated access.

Where did you find the AI agent's session logs?

A claude code review of the session transcripts was the breakthrough. The AI coding agent's logs were stored as JSONL files on the developer's local machine at ~/.claude/projects/**/*.jsonl — and grepping them for destructive command signatures surfaced the exact session responsible.

What is a shadow database used for in Prisma?

A shadow database in Prisma is a temporary, secondary database that Prisma uses to safely generate and check schema migrations. It applies changes to the shadow database to detect issues like data loss before creating the final migration file for production.