Deleting the row isn't the shortcut it looks like

It's a well-meant mistake. That's exactly why it sticks around. Every team that makes it can explain why, and the explanation holds up right until the system has to survive its second year.

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.

This is where the reconciliation spreadsheet comes from. Somebody built it once to settle an argument. Now roughly 40,000 loads a month depends on a file with one author, no tests, and a filename ending in _v4_final.

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 bill shows up in the handover notes: nine paragraphs on which of two systems to trust for a given load tender. When two carriers were committed against the same trailer, it took two days to work out what had actually happened, in what order.

WHERE IT GOES WRONG

• 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.
• 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.

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

THE REPLACEMENT

The fix isn't more discipline. Discipline wears off. The fix is a structure where the wrong thing can't be said, so nobody has to remember the rule at 3am.