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

The ticket looked airtight when I filed it. A domain table appeared to contain 13 copies of the same domain, and the concern was straightforward: if the rows were concurrent, the unique constraint had a hole. The proposed fix combined two familiar moves. Clean up the duplicates, then tighten the index so an unsecured domain could not get through again.

That story was wrong in two separate ways. The index already covered the case I thought it missed. More importantly, the 13 rows were not duplicate records at all. They were the sequential soft-delete history of one record.

The index did not have the gap I thought it had

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 farther, I checked the live production definition:

UNIQUE(domain) WHERE removedOn IS NULL

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

The index already covered unsecured rows. There was no separate unsecured path around it, and no missing uniqueness rule to patch. The ticket existed because I had inferred the index behavior from the row shape instead of reading the index that was actually deployed.

The first discarded option was an index change. It lost immediately once the live definition was in front of me. Adding another constraint would not fix an integrity gap, because there was no integrity gap. At best it would duplicate a rule already enforced. At worst it would make a later migration harder to understand by hiding the real constraint behind a second version of the same idea.

Thirteen rows can be one record

The remaining question was 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 were not concurrent duplicates, what were they?

The answer was in the timestamps. Each row’s removedOn matched the next row’s createdOn. The sequence had a single active row at a time, followed by a tombstone and then the next incarnation of that 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 will not, because every earlier row has removedOn populated and only the current row qualifies for the active set.

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

The second discarded option was a broad cleanup of every repeated domain value. It lost after the timestamp check. A duplicate detector based on counts alone cannot distinguish concurrent active rows from a valid history chain. The correct test had to include both the active-row predicate and the relationship between removedOn and the next createdOn.

The cleanup had already proved the risk

This was not an abstract concern about preserving history. The cleanup had collaterally removed the one active row for a test site. I restored that row, domainID 1368, before closing out the work.

That was the moment the ticket stopped being a harmless database hygiene item. The script had been pointed at a pattern that sounded obvious, 13 rows for one domain, but it had crossed the line from cleanup into deletion of legitimate state. The test-site row was recoverable because I caught the premise and restored it. The historical rows were the larger risk because their removal would have erased the chain that explained why the table had that shape in the first place.

I did not replace the original ticket with a more elaborate cleanup ticket. I deleted it as invalid and corrected the epic note so the same false premise would not get rediscovered later. The deployment already had the constraint it needed:

UNIQUE(domain) WHERE removedOn IS NULL

There is a temptation to treat repeated values as proof that the database is broken. In a soft-delete model, the question is not how many times the value appears. It is whether two live rows can own it at once.

I had 13 rows, one active record, an index that already enforced the rule, and one restored test-site row. The right fix was not better deletion logic. It was deleting the bad theory before it deleted more data.