Deletes, history and time travel, explained before you need it

Most write-ups of this start in the middle. We'll start earlier than strictly necessary, because nine times in ten the confusion comes from a word two people are using differently, not from the mechanism.

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.

Made concrete: a claim file passes through several pairs of hands, and each of them believes it owns the record. What's above is how you let all of them be right without letting any of them silently overwrite the others.

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.

Picture a specialty claims administrator at about 15,000 claims a month. The naive version works perfectly in testing and falls over the first day two adjusters touch the same claim file in the same second.

WHERE IT GOES WRONG

• Ending a relationship with a hard delete, then finding every historical report has quietly changed.
• 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.

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

THE SHORT VERSION

None of this is advanced. It's ordinary practice that gets skipped when a date is close, and then paid for with interest by whoever's on call.