Recovering a 327-Team League: Plan-Run Before You Touch Prod

A customer’s site got deleted. That sentence usually means one account, a handful of pages, a quick restore from a backup. This one didn’t. When I pulled up the backup and read the org structure, the thing I had to bring back wasn’t a site. It was a league with 327 teams hanging off it.

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

So the real number wasn’t 327. It 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 no longer “a bug.” It’s a corrupted league I’d have to clean up by hand, in production, with a customer waiting.

The thing that kept that from happening was boring, and that’s the whole point. Before I wrote a single row to production, the entire recovery ran in plan mode and produced a file I could read.

Plan is a real mode, not a comment

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 teams, and counts the rows it would copy out of every per-table source. It logs every decision: which mode it picked, which sport it detected, how many games and scores and roster rows each team carries. Then it writes exactly zero rows. The first run produced a 683-line plan file.

The detail that makes this trustworthy is that I did not give myself two switches to keep in sync. EDIT_DATABASE, the flag the insert path actually checks, is derived from RECOVERY_PHASE, not set alongside it. There is one source of truth. You cannot half-arm the thing. You cannot leave it in plan but accidentally flip the write flag, because there is no separate write flag to flip. Flip the phase back to plan and the entire write path goes cold at once.

That matters more than it looks. Most “dry-run mode” bugs aren’t in the dry run. They’re in the second boolean somebody forgot to clear, the one that was still true from last time. Derive the dangerous flag from the safe one and that class of bug stops existing.

The artifact is the deliverable

A dry run that only prints “would copy 14,000 rows, OK” tells you nothing you can check. The plan file is different because it’s diffable.

The 683-line plan enumerated every team and every per-table count. When the live run finished, it emitted its own results file, 988 lines, one Recovered: line per team. Now I had two artifacts describing the same operation: one written before any prod write, one written after. If a team showed up in the plan and not the results, I’d see it. If a team’s game count came back different from what the plan predicted, I’d see that too. The plan turned “did the recovery work” from a vibe into a line-by-line comparison.

This is the part people skip when they bolt a --dry-run flag onto a script. They make it print a summary and call it done. A summary you can’t diff is a comfort blanket, not a control. 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 “did it actually write.” Then verification is diff, not judgment.

It also caught a real bug before prod. Early in the build, a team’s games came back duplicated after a test run: the backup had 7, the target had 14, perfect duplicates with different new IDs. The plan and results comparison is exactly what surfaces that. Once it was visible, the fix was structural. Every inserted game now stamps an origin key (the original game 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?” became a five-second COUNT, not an archaeology dig. But I only knew to look because the artifact made the count wrong, loud.

Validate against a disposable copy, never 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 was: plan-only pass first, reading nothing into prod. Then a single-account run against a disposable test database, never the live one, where I could let it actually insert and then run verification SQL against the result. Backup game count equals target game 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.

The disposable database is the unglamorous hero here. 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 cannot 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; prod is where it spends it.

Re-safe the gun in the same session

One more piece, and it’s the one I’m most likely to skip when I’m 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 isn’t a tool, it’s a liability parked in the repo.

So the last commit of the day wasn’t a feature. It 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. Default state: writes nothing, 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, hardcode nothing real at rest, make arming a conscious act. The cheapest incident is the one a stray request can’t trigger.

The lesson scales with fan-out

None of this is exotic. Plan mode, a diffable artifact, a disposable validation copy, re-safing at the end. The reason I’m writing it down is that the value isn’t fixed, it scales with the blast radius.

For a one-account fix, a plan file is mild overkill. You could eyeball it. At 327 teams, the plan file is the only thing standing between “recovered a league” and “corrupted a league,” because the failure mode isn’t one bad row, it’s one bad pattern multiplied 327 times in a place you can’t easily walk back. The bigger the fan-out, the more a plan file pays for itself, and the cost of building one is roughly flat regardless of N.

So the rule I’d hand anyone writing a bulk operation that touches production: build a dry-run mode that emits a diffable artifact at the same granularity as the real run. Derive your write flag from your mode flag so you can’t half-arm it. Validate the writes against a copy you can throw away, never the live target. And when you’re done, put the safety back on before you close the laptop. The size of the operation tells you how much that discipline is worth, and it’s almost always worth more than it costs.