SET IDENTITY_INSERT pinned a mailer row to ID 14 on prod

Trial ID 14 was already taken. We did not know that until we had already overwritten it.

The feature we were shipping was an exit-feedback page for expiring trial teams. Instead of routing them to a Google Form on expiration, we wanted an in-house page: a short survey, a responses table, an admin report, and a deep-linkable preview tool for reviewing individual submissions. The page went up, the expiration emails pointed to it, and the admin tooling rendered cleanly. The feature was done.

The provisioning step we had run earlier in the day had overwritten a completely different production row.

The feature itself was clean

An expiring trial team gets an email with a link to the exit-feedback page. They fill out a short form, and the submission lands in a responses table. An admin page lists submissions and deep-links to individual previews.

An AI assistant did most of the scaffolding under direction, and nothing about the page itself was surprising. The provisioning step is what bit us.

The assumption baked into the provisioning script

To wire the expiration email, we needed a row in the mailer configuration table. That table has an identity column. The AI assistant drafted a provisioning script that used SET IDENTITY_INSERT to pin the row to ID 14.

The reasoning looked sensible. The script checked whether a row with ID 14 already existed. If it did, it ran an UPDATE, and if it did not, it ran an INSERT. That is an idempotent pattern, safe to re-run and free of duplicates, and we ran it.

The check answered the wrong question. It asked “does a row with ID 14 exist?” and never asked “does this row belong to the mailer we are trying to provision?” On prod, a row with ID 14 had been there for months. It was a scheduled email digest, the one that sends staff their weekly digest.

The script saw an existing row and ran the UPDATE, replacing the digest mailer configuration with the exit-feedback configuration. It raised no error and no warning. The idempotent pattern did exactly what it was designed to do, just on the wrong row.

How we found it

We checked the mailer table after the provisioning run and noticed the row name did not match. It said something about exit feedback where it should have said weekly digest.

We pulled the backup, found the original digest mailer row there, and used those values to reconstruct it.

The digest mailer runs on a weekly schedule, and when we found the corruption, the next send was hours away. Every staff member would have gotten an exit-feedback survey instead of their digest, sent from the same infrastructure with no alert firing. The mailer machinery has no concept of what a row is supposed to say. Every dashboard would have shown the feature as fine, and anyone chasing the complaint would have had no obvious path back to that morning’s provisioning run.

We caught it before that send, but only out of habit. Nothing pointed us at it.

The check we rebuilt around

Before overwriting a row it provisions by identity, a script has to read the name, the marker, or whatever field separates “this is the row we own” from “this is some other row.” If that check fails, it should halt and surface the discrepancy.

The script we landed on reads WHERE ownerTag LIKE '%exit-feedback-mailer%'. If a row with that marker exists, it updates that row. If none does, it inserts without specifying an ID and lets the database assign one. The numeric ID becomes an artifact of insertion order, and the script no longer hardcodes a guess about what is free.

“Row ID 14 exists” and “row ID 14 is ours” are different questions. We learned that by restoring the digest mailer from a backup and rebuilding the script around an ownerTag marker the original never bothered to check.

What it cost

A provisioning script that pins a record by its ID can overwrite a live record that already holds that ID, with no error and no alert. Here the overwritten record was a weekly staff digest, and a manual check after the run was the only thing that caught it.

dxdev LESSON · paste into your AI coding agent
LESSON: IDENTITY_INSERT row-existence vs. row-ownership
SOURCE: dxdev.com/blog/exit-survey-overwrote-live-mailer
WHAT HAPPENED: A provisioning script used SET IDENTITY_INSERT to pin a new configuration row to a hardcoded numeric ID. The script checked whether a row with that ID already existed and, finding one, ran an UPDATE. The existing row belonged to a completely unrelated scheduled mailer that had been live for months. The UPDATE silently replaced the live mailer config with the new feature config. No error fired. The corruption was found only by manual inspection hours before the damaged mailer would have sent to real users.
THE RULE: An existence check on a numeric ID is not an ownership check; always verify that the row's content identifies the thing you own before writing to it.
CHECK MY CODE, then report PASS / FAIL with file:line for each:
  1. Any INSERT or UPDATE that targets a row by numeric primary key without also verifying a content-based ownership marker (name, tag, type, slug, or equivalent distinguishing field)
  2. Any provisioning script or migration that uses SET IDENTITY_INSERT (or equivalent dialect: OVERRIDING SYSTEM VALUE, INSERT OR REPLACE, ON CONFLICT DO UPDATE) and assumes a specific ID is unoccupied without reading the row first
  3. Any idempotent upsert that treats "row exists" as equivalent to "row belongs to this feature" and skips a content diff before writing
THEN PRINT: a table (check, PASS/FAIL, evidence, fix) + a verdict (applies / partially / OUT_OF_SCOPE / no) + the single most important next action.