domainID 1368 Was Deleted by a Cleanup We Trusted

At 9:59 AM, we deleted a ticket built around 13 supposed production duplicates and restored a test-site domain row, domainID 1368, that the cleanup had removed.

When we filed that ticket it looked airtight. A domain table appeared to hold 13 copies of the same domain. If the rows were concurrent, the unique constraint had a hole. The proposed fix was two familiar moves: clean up the duplicates, then tighten the index so an unsecured domain couldn’t get through again.

The story was wrong in two separate ways. The index already covered the case we thought it missed. And the 13 rows weren’t duplicate records at all. They were the sequential soft-delete history of one record.

Reading the index that was actually deployed

The alleged problem was a unique constraint that seemed to protect only part of the domain population. Before writing another migration, or letting a deletion script run any farther, we checked the live production definition:

UNIQUE(domain) WHERE removedOn IS NULL

That predicate changes the question. It doesn’t ask whether a domain value has ever appeared more than once in the table. It asks whether more than one row with that domain is active at the same time.

So the index already covered unsecured rows. There was no separate unsecured path around it and no missing uniqueness rule to patch. We had inferred the index’s behavior from the shape of the rows instead of reading the index that was deployed.

The first wrong turn was the index change itself. We had it lined up as the fix, and it fell apart the moment the live definition was in front of us. A second constraint would not close an integrity gap, because there wasn’t one. At best it would duplicate a rule already enforced. At worst it would make a later migration harder to read by hiding the real constraint behind a second version of the same idea.

Thirteen rows, one record

That left the question of why the data looked so bad. The table had 13 rows with the same domain, and a cleanup had already treated them as junk. If they weren’t concurrent duplicates, what were they?

The answer was in the timestamps. Each row’s removedOn matched the next row’s createdOn. The sequence had one active row at a time, then a tombstone, then the next incarnation of the same domain. The apparent duplicates were a timeline:

row 1: createdOn → removedOn
row 2:            createdOn → removedOn
row 3:                         createdOn → active

The overlap is visual, not temporal. A query that groups only by domain will call that shape a duplicate. The partial unique index won’t, because every earlier row has removedOn populated and only the current row qualifies for the active set.

That matters because the older rows weren’t an accidental repeat. They were the record’s soft-delete history. Removing them would turn a recoverable, inspectable sequence into a claim that the earlier states never existed.

The second wrong turn was the cleanup that acted on this misreading: a broad sweep of every repeated domain value. It stopped making sense after the timestamp check. A duplicate detector built on counts alone can’t tell concurrent active rows from a valid history chain. The correct test has to include the active-row predicate and the relationship between removedOn and the next createdOn.

What the cleanup had already cost

This wasn’t an abstract worry about preserving history. The cleanup had collaterally removed the one active row for a test site. We restored that row, domainID 1368, before closing out the work.

That was when the ticket stopped being harmless database hygiene. The script had been pointed at a pattern that sounded obvious, 13 rows for one domain, and it had crossed from cleanup into deleting legitimate state. The test-site row was recoverable because we caught the premise and restored it. The historical rows were the bigger risk, since removing them would have erased the chain that explained why the table had that shape.

We didn’t replace the original ticket with a more elaborate cleanup ticket. We deleted it as invalid and corrected the epic note so the same false premise wouldn’t get rediscovered later. The deployment already had the constraint it needed:

UNIQUE(domain) WHERE removedOn IS NULL

Repeated values are easy to read as proof that a database is broken. In a soft-delete model the question isn’t how many times a value appears. It’s whether two live rows can own it at once.

We had 13 rows, one active record, an index that already enforced the rule, and one restored test-site row. The right fix was to delete the bad theory before it deleted more data.

Before approving a duplicate cleanup, get the live index definition and check whether the repeated rows are active at the same time or are a soft-delete history. Here they were one record’s history, and the cleanup had already removed an active test-site row that had to be restored.