The backup showed a parent group with 327 units hanging off it, and the job was to bring back a deleted customer’s site. A deleted site usually means one account, a handful of pages, a quick restore. This one meant restoring the whole group.

In our app, “the site” isn’t a database we can RESTORE. Every org’s data lives as rows keyed by a tenant identifier, spread across hundreds of per-category tables: records, scores, layout, news, member lists, stats. Recovering one tenant means re-inserting all of those rows from a dated backup database into live production, table by table, and rewriting every foreign key as you go, because the new tables hand out their own auto-increment IDs. Recovering the group meant doing that 327 times. The units underneath share a parent, share old ID ranges, and reference each other.

So the number that mattered was 327 multiplied by every way a bulk insert can go wrong. A dropped column, a double insert, a foreign key remapped against the wrong tenant: any one of those, times 327, is a corrupted group we’d have to clean up by hand, in production, with a customer waiting.

What kept that from happening was boring. Before we wrote a single row to production, the entire recovery ran in plan mode and produced a file we could read.

Plan is a mode

The recovery engine has one constant that decides everything:

RECOVERY_PHASE = "plan"   // vs "run"

In plan, it does the full traversal. It connects to the backup, reads the org structure, enumerates all 327 units, and counts the rows it would copy out of every per-table source. It logs every decision: which mode it picked, which category it detected, how many records and scores and member rows each unit carries. Then it writes exactly zero rows. The first run produced a 683-line plan file.

We did not give ourselves two switches to keep in sync. EDIT_DATABASE, the flag the insert path actually checks, is derived from RECOVERY_PHASE and never set alongside it. There is one source of truth. You can’t leave the phase on plan and accidentally flip the write flag, because there is no separate write flag to flip. Set the phase back to plan and the whole write path goes cold at once.

That matters more than it looks. Most dry-run bugs live outside the dry-run logic, in the second boolean somebody forgot to clear, the one still true from last time. Derive the dangerous flag from the safe one and that class of bug can’t exist.

The plan file is something you can diff

A dry run that only prints “would copy 14,000 rows, OK” gives you nothing to check. Our plan file was different because we could compare it to something.

The 683-line plan enumerated every unit and every per-table count. When the live run finished, it emitted its own results file, 988 lines, with one Recovered: line per unit. That gave us two artifacts describing the same operation, one written before any prod write and one written after. If a unit showed up in the plan and not in the results, we’d see it. If a unit’s record count came back different from what the plan predicted, we’d see that too. “Did the recovery work” became a line-by-line comparison.

This is the part people skip when they bolt a --dry-run flag onto a script. They print a summary and call it done, and a summary you can’t diff is a comfort blanket. The plan has to emit the same shape of output as the real run, at the same granularity, so the only difference between the two files is whether anything was written. Then verification is a diff and needs no judgment call.

The comparison also caught a bug before prod, and this is where we were wrong first. Early in the build, a unit’s records came back duplicated after a test run: the backup had 7, the target had 14, perfect duplicates with different new IDs. The insert path had put the same rows in twice. Once the counts disagreed on the page, the fix was structural. Every inserted record now stamps an origin key (the original record ID), and a pre-insert guard reuses the existing row instead of inserting again. The recovery became idempotent by construction, so “did this run twice?” is now a five-second COUNT instead of an archaeology dig. We only knew to look because the artifact made the count wrong, and loud.

A disposable copy comes before prod

The plan file proves the traversal is sane. It does not prove the writes are correct, because in plan mode there are no writes. So the runbook had a middle rung between “plan” and “go.”

The ladder went like this. First a plan-only pass that wrote nothing into prod. Then a single-account run against a disposable test database, never the live one, where we let it actually insert and then ran verification SQL against the result:

  • Backup record count equals target record count.
  • No NULL origin keys, meaning every row got stamped.
  • No duplicate (tenant, origin-id) pairs, meaning the idempotency guard held.

Only after one account passed all of that on a throwaway database did the remote prod run happen.

A plan pass can’t catch a bug in the insert logic, because it doesn’t insert. Reading the recovered rows back and checking them against the backup is the only thing that does, and you can’t do that against production without writing to production. So you write to a copy you’re allowed to throw away, verify there, and carry the verified script over. The test DB is where the insert path earns trust, and prod is where it spends it.

Re-safe the gun in the same session

One more piece, and it’s the one we’re most likely to skip when we’re tired and the recovery just worked.

After the successful prod run, the script was sitting in a dangerous resting state: a real tenant hardcoded as the target, the phase set to run. Anyone who hit that page again would re-run a live, writing recovery against a real account. A recovery tool one stray GET away from rewriting production is a liability parked in the repo.

So the last commit of the day set the target back to a placeholder and the phase back to plan, with a one-line comment saying which constant to flip for a live run. The default state writes nothing and targets nothing real. Arming it again takes two deliberate edits, a real tenant and a flip to run, and because the write flag is derived from the phase, that one flip is the whole arming step. Dangerous operator scripts should fail safe and fail loud: default-deny, nothing real hardcoded at rest, and arming is a conscious act.

Why 327 changes the math

None of this is exotic. Plan mode, a diffable artifact, a disposable validation copy, re-safing at the end. The value scales with the blast radius. For a one-account fix, a plan file is mild overkill, and you could eyeball it. At 327 units, the plan file is the only thing standing between a recovered group and a corrupted one, because the failure is one bad pattern multiplied 327 times in a place you can’t easily walk back. The cost of building the plan is roughly flat regardless of N.

So here is what we’d hand anyone writing a bulk operation that touches production. Build a dry-run mode that emits an artifact at the same granularity as the real run. Derive your write flag from your mode flag so it can’t be half-armed. Verify the writes on a copy you can throw away. When the job is done, reset the tool to a safe default with no real target left in it.