Teardown: deletes, history and time travel

We inherit systems more often than we start them. This is how we read one: what we look at first, and what each thing tells us about decisions made by people we'll never meet.

Almost nothing in a business system is truly deleted, and almost nothing is only true now. Systems that model records as a single current row throw away the two things people ask for most: what it used to say, and who changed it.

THREE DIFFERENT DELETES

"Delete" collapses three unrelated intentions. It might mean this was entered by mistake and never existed. It might mean this ended, and remains true of the past. It might mean a legal obligation to destroy personal data. They need different mechanisms, and using one for all three guarantees you'll get an important case wrong.

The one people conflate most is the middle case. An employee who left, a rate that no longer applies, a supplier you stopped using. None of that is deletion, it's an end date. Model it as deletion and every historical report becomes wrong the moment someone leaves.

TWO KINDS OF TIME

Business systems have two clocks: when something was true in the world, and when the system found out. They come apart constantly. A correction filed today about last month's reading means the value changed as of last month, but yesterday's report was still correct given what was known yesterday.

If you keep both, "why did this report change?" becomes answerable. If you keep only one, someone spends a fortnight with two exports and a spreadsheet, and the answer they produce won't be trusted anyway.

The exception handlers were the most honest documentation in the repo. Every branch that swallowed an error marked a spot where production planners had once been paged and somebody had made it stop.

DON'T DO IT EVERYWHERE

This gets expensive if applied indiscriminately. Full temporal modelling on every table is a lot of machinery for data nobody will ever ask about, and it makes every simple query worse.

Pick the handful of records that get argued about. Anything with money attached, anything a regulator can ask about, anything where a decision was made. Everything else can be a plain row, and should be.

The schema told the real story. Three nullable columns added in one migration, all named after a work instruction field, all populated for precisely the weeks when a revised instruction reached the floor after the batch had run was an open ticket.

WHERE IT GOES WRONG

• No record of when the system learned something, so no report can ever be reproduced.
• Temporal modelling applied to all forty tables, making every query slower to protect data nobody queries.
• One delete mechanism serving three different meanings, so the important case is handled wrong.
• Ending a relationship with a hard delete, then finding every historical report has quietly changed.

Deleted, ended, and erased are three different things. Say which one you mean.

THE VERDICT

Better than its reputation. Most of what looked like bad engineering turned out to be a sensible answer to a constraint that had since been lifted, and nobody had gone back to check.