A customer’s entire site got deleted: a whole organization with 327 sub-accounts under it. The fix was no button and no RESTORE DATABASE. It was a 1,477-line JavaScript recovery engine that resurrects one account from a backup database, row by row, table by table, against live production, behind a hard 30-minute script timeout.

It has to work that way because of the data model. That data model is the whole point of this post, and it’s also where the bug that almost shipped lives.

Our first master-file draft still carried the unscoped remap pattern from the older per-category scripts. We caught it while writing the consolidated engine’s header comment, before it reached a tenant’s rows. The mistake was treating an old ID as though it meant the same thing outside the username that owned it.

Why recovery is a copy and remap job

The production app stores each customer’s site as rows keyed by username across hundreds of per-category tables: <app-db>.<category>games, <category>scores, <category>layoutstruct, and so on for every category. There is no per-tenant database. There is one big database where a tenant is a value in a column. So when one tenant’s rows get deleted, the rest of the database is fine, and a database-level restore would clobber every other customer’s live data to bring one customer back.

That leaves exactly one option: re-insert that tenant’s rows out of a dated backup database (<app-db><date>) into live prod. Application-level copy, scoped to one tenant, into a table full of everyone else’s rows.

We consolidated this into one master recovery file. Before that there was a pile of older variants, one copy-pasted per category, each drifted from the others, and each carrying the same latent bug. We’ll get to the bug.

The engine’s runtime budget is set at the top of the file:

var HOURS_TO_RUN = 0.5;
var MINUTES_TO_RUN = HOURS_TO_RUN * 60;
var SECONDS_TO_RUN = MINUTES_TO_RUN * 60;
scriptTimeout = SECONDS_TO_RUN;
var RECOVERY_BACKUP_DB = "<app-db><date>";

Each table’s copyable columns are declared through a tiny DSL, two comma-separated lists per table, one for string columns and one for numeric columns. The same generic copy loop then handles about, news, layoutstruct, games, scores, and every stats table, because they all reduce to “these string cols and these numeric cols.”

Copy first, then remap IDs

Here is the constraint that shapes everything. Child rows reference parent rows by integer ID. Scores point at games by gameID. Stats point at players. Photo details point at albums. Those parent tables have IDENTITY primary keys, so when you insert a game into prod, SQL Server assigns it a brand new gameID. You cannot know that new ID until the row is in.

So you can’t copy a score row and keep its old gameID, because that ID now belongs to some other tenant’s real game. Copy it verbatim and you have pointed this customer’s scores at another customer’s games. That is cross-account corruption that no error will tell you about.

The invariant, stated in the file’s own header comment, is “keep ‘copy first, then remap IDs’ as a clear, auditable ordering.” You insert all the rows first with placeholder foreign keys, then make a second pass that rewrites the foreign keys to the real new IDs.

The OFFSET_ID placeholder

The placeholder is one magic number:

var OFFSET_ID = commit-E59B;


function AddOffset(numVal) {
  numVal = Number(numVal);
  if (!numVal) return 0;
  return numVal + OFFSET_ID;
}

On insert, every foreign key gets OFFSET_ID added to it. That parks it at 100,000,000-plus, a range no real ID in these tables has ever reached, so the offset FKs can’t collide with a live row. The child rows go in with offset FKs that are deliberately wrong but unambiguously recognizable as “needs remapping.”

Then the second pass walks the maps it built during insertion, (oldID -> newID) for game, archived-game, location, player, and album, and runs UPDATEs against the children: score rows, registration rows, the opponent-game-id column, every per-category stat table, plus the archive variants of each. The offset value is swapped for the real new ID.

The remap reaches past FK columns. Layout IDs get embedded inside serialized layout blobs, so a second remap function builds a per-ID regex and string-replaces old layout IDs inside the serialized text columns (content body, parent reference, and slideshow settings):

reOldID = new RegExp("\\\\b" + Clean(oldID) + "\\\\b", "g");

That runs against each of those serialized text columns in the layout table. If you only remapped FK columns, dangling IDs would stay baked into the serialized layout, and the recovered site would render with broken structure even though every foreign key checked out.

The bug that eats multi-tenant migrations

We wrote this bug ourselves before we wrote the fix. The older per-category scripts were ours, copy-pasted one category at a time, and the same unscoped remap pattern sat in our first draft of the master file until the header comment forced the question. In paraphrase, that comment says:

The biggest bug in several older scripts is that remap UPDATEs do NOT filter by username, which can corrupt an organization with many sub-accounts, where different sub-accounts share the same old ID values.

Read that with the data model in mind. Everything is a row keyed by tenant. Old IDs are not globally unique across tenants. Two different sub-accounts in the same organization can have a game with the same old gameID, because those IDs were only ever unique within a tenant.

So if your remap is “find every row where gameID = oldVal and set it to newVal,” you will happily rewrite a different sub-account’s game that happened to share that old ID. The remap meant to fix one tenant reaches across tenants and corrupts the ones it never touched. With 327 sub-accounts under one organization, a shared old ID is near certain.

Every remap UPDATE in this engine is scoped to the tenant:

WHERE username = ? AND field = oldVal

Username first, then the value. Drop the scope and the engine becomes a corruption machine that looks like it’s working. The only signal you’d get is a customer asking why another sub-account’s scores are in their site.

Why three scripts became one engine

Before the master file there were per-category recovery scripts, one for each of three categories. All three carried the same unscoped remap bug, and each had been edited on its own since it was copied. Merging them into one data-driven file gave that bug exactly one place to live. Fix the unscoped remap once and it is fixed for every category, including any added later, because there is no later copy to forget. The single file is large, so line count was never the reason to consolidate.

What to check in your own recovery tooling

In a shared-database system where many customers live in one set of tables, recovering one customer means copying rows back and remapping IDs, and every remap must be scoped to that customer. Check whether your remap updates filter by tenant, and whether anyone has run them against accounts that share old ID values.