What deleting a counter actually touches

Deleting a counter here empties 33 tables. Yesterday it emptied 25.

The eight it missed were all created the day before, between 11:10 in the morning and 15:10 in the afternoon.

How deletion works

An owner can delete their counter. It takes a POST, the owner's token, and the counter's number typed out by hand, because there is no undo. Nothing is set aside: a shadow copy would be the opposite of what was asked for, and the export links sit above the button for anyone who wants one.

Then a list of tables is emptied in a single transaction, the main table last, and a tombstone goes into the archive so the number can never be handed to somebody else. If any part fails, the whole thing rolls back. Nothing deleted is better than half deleted.

Above that list, in the file, is this comment:

"Every table that knows something about this counter. As a list and not scattered through the code: whatever is missing here stays behind as an orphaned row, and nobody notices."

The comment described the gap a day before it opened

Yesterday, six new tables appeared for a new feature — time on page, scroll depth, exit pages — along with two others. Between 11:10 and 15:10.

The deletion list was edited that same day, to add a different new table. The eight arrived after that, and the list did not move again. So for one afternoon, deleting a counter left 4,840 rows of behaviour data behind, spread across eight tables. The gap was found the next day and closed; what it exposed is that the list needed something other than memory to keep it complete.

This is not a story about carelessness. Somebody wrote the list, understood exactly why it was a list, wrote down what happens when it falls behind, and updated it that very day for one table. And it still fell behind, because keeping two things in step by remembering to is a thing humans do reliably right up until the afternoon they do not.

The fix, and the other fix

The eight names are in the list now. They were inserted at their alphabetical positions rather than appended, because a sorted list makes a gap visible and an append-only list does not.

That is the small fix. The one that matters is a script that asks the database which tables carry a counter number, reads the list out of the source file rather than repeating it, and compares. Anybody adding a table runs it once. It cannot be forgotten in the way a list can, because it does not store anything — it derives the answer from the schema every time.

It found something else on its first run: 25 rows in eight further tables whose counter number is in neither the live table nor the archive. Not deleted counters — those leave a tombstone. Numbers belonging to nothing at all, most of them obvious test numbers from before the archive check existed. They were written to a file outside the web root and then removed.

What this generalises to

Any list that has to be kept in step with something else will eventually fall out of step, and the gap will be invisible, because a list that is missing an entry looks exactly like a list that is complete.

The defence is not discipline. It is deriving one side from the other and checking that they agree — and running that check at the moment somebody adds the thing, not at the moment somebody wonders.

The comment above the list was right about everything except one word. It said nobody notices. Somebody did, one day later, because they went looking with a script instead of with their memory.