My “idempotent” upsert assumed trial ID 14 was unused. It wasn’t. It was a live “Staff Reminders” mailer, and at 15:58:09 EDT my update path overwrote its subject, from-address, and body with “Get back in the game…” content, in production.

Here is the part I want to be honest about up front: the blast radius turned out cosmetic, and I confirmed that from the backup row and a code read, not from the column names or a hopeful guess. But the bug that caused it was the kind that waits around. There was a sibling SQL file sitting in the same directory carrying the exact same assumption, primed to replay the whole thing the next time Bamboo or a human ran it.

The setup

I was building a marketing mailer for a sports SaaS trial campaign. The pattern was a SQL upsert, the thing everyone reaches for so a script is safe to run twice:

-- pseudocode of what I had
IF EXISTS (SELECT 1 FROM dbo.<mailer-trials-table> WHERE <trial-id-column> = 14)
    -- "already seeded", just UPDATE the linked mailer row
ELSE
    SET IDENTITY_INSERT dbo.<mailer-trials-table> ON
    -- INSERT trial 14, then SET IDENTITY_INSERT OFF

The logic read clean. Trial 14 exists, so skip the insert and update the mailer. Trial 14 doesn’t exist, so claim ID 14 with SET IDENTITY_INSERT and seed it. Run it once, run it ten times, you land in the same place. Idempotent. That word is doing a lot of load-bearing work in that sentence, and it’s wrong, but I didn’t see it yet.

Both the SQL file and the runner that invoked it carried the same baked-in belief: that trialID = 14 was a free slot I was claiming. Nobody wrote that belief down anywhere. It was just the number that happened to be next, or the number I’d seen in a dev DB, and it got hardcoded as if it were a fact about production.

It was not a fact about production. In prod, trial 14 was already taken.

The discovery

The thing that saved me was a verify block I’d put after the update, the kind of “read it back and print what you touched” step that feels like overkill until it isn’t. It printed:

trialName: "Staff Reminders"
mailerID: <id>
isActive: 1

That is not the output you want to see when you believed you were writing to an empty row. isActive: 1 means it’s a live mailer that actually goes out to staff. trialName: "Staff Reminders" means I had just pointed a “Staff Reminders” record at “Get back in the game” marketing copy.

I stopped. I did not keep going to see if the rest of the script “worked,” because the rest of the script working would have meant more writes on top of a row I’d already corrupted. The right move when a verify step contradicts your model of the world is to halt and report, not to plow through and reconcile it later.

The damage assessment, done from evidence

Here’s where the discipline matters more than the panic. The corrupted row was a row in dbo.<mailers-table>, named “Staff Reminds - Main”. My update had overwritten four columns on it: emailHTML, subject, fromName, and fromEmail. On the surface that is a production mailer with the wrong subject line, the wrong from-address, and the wrong body. That sounds bad, and if I’d stopped reasoning at the column names I would have logged it as a serious incident and maybe scrambled a comms message.

Two things turned that around, and neither one was an assumption.

First, the backup row. The pre-overwrite version of the target row had a blank subject, blank from-fields, and a blank body. The “live mailer” I’d clobbered had no actual content in those columns to begin with. That’s a strong hint, but a hint is not a conclusion, so:

Second, the code. I read the file that handles the actual send, the relevant lines showing where the mailer runs. The send path builds the subject and the body inline, in code, every time it runs. The mailer row is used as a tracking placeholder, a record that this mailer type exists, not as the source of the content that goes out. The trialID:14 linkage is never consulted at send time. So the columns I’d overwritten were columns the send path never reads.

That is the difference between “I overwrote a live mailer’s subject and body in prod” (true, and alarming) and “the overwrite was cosmetic because those columns are dead at send time” (also true, and the actual blast radius). You only get from the first sentence to the second by reading the backup and the code. The column names alone would have left me guessing, and a guess about blast radius during a prod incident is exactly the kind of thing you should never ship as fact.

I restored the row anyway, at 16:04:18 EDT, about six minutes after the overwrite. Not because the data mattered to the send path, but because the next person to open that row should see “Staff Reminders” content, not stray marketing copy. Leaving a misleading row in prod because “it doesn’t matter functionally” is how you breed a confusing incident three months from now.

The real bug was sitting next door

If the story ended at “restored the row,” I’d have fixed an instance and left the cause in place. The cause was the hardcoded ID, and it had a clone.

A sibling SQL file in the same directory carried the identical trialID = 14 assumption. It was the same time-bomb with a different filename. If Bamboo picked it up in a deploy, or a teammate ran it to “re-seed” the campaign, it would walk straight back into the target row and overwrite “Staff Reminders” again. The verify block would catch it again, sure, but a guardrail you trip every time is not a fix.

The fix was to stop addressing the row by a surrogate integer at all. Instead of WHERE trialID = 14, look the row up by something that actually describes it: trialName, or a mailerSummary LIKE match on a stable campaign identifier. A stable business key tells the truth about which row you mean. A hardcoded 14 only tells the truth in the one database where 14 happened to point at the thing you meant, and prod was not that database.

There’s a second half to the fix that’s just as important as the lookup: assert the row matches before you UPDATE. Pull the row by its business key, check that it’s the campaign row you expect (not a live staff mailer), and only then write. If the assertion fails, halt loudly. That converts “silently overwrite whatever row 14 happens to be” into “refuse to write unless this is the row I think it is.”

The takeaway

“Idempotent” built on a hardcoded surrogate ID is a time-bomb with extra steps. The idempotency is real in the sense that running it twice produces the same result. It’s just the wrong result, repeatably, against a row you never meant to touch. The word lulls you because it sounds like a safety property, and the actual safety property you wanted (this writes to the row I intend and no other) is a completely different claim.

So two rules came out of this day, and I’d hand them to anyone writing data-mutating scripts against a live system:

Look the row up by a stable business key, and assert it matches what you think before you UPDATE. A surrogate integer is an address, not an identity. Addresses get reused. If your script’s correctness depends on 14 meaning the same thing in prod as it did in your head, your script is wrong and you just haven’t run it in prod yet.

And when you do hit a prod incident, confirm the actual blast radius from the backup and the code, not from the column names. “Overwrote the subject and body of a live mailer” and “overwrote four dead tracking columns the send path never reads” are the same overwrite. Which one you’re in is a fact you can check in two reads, and it’s the difference between a six-minute restore and a fire drill. Check it. Don’t narrate the scary version as if it were confirmed.