No. 27Automation that holds up2 min readOCTYN
How to prevent accidental data deletion in automated systems: make it refuse.
To prevent accidental data deletion in automated systems, make the database refuse any delete that would take other records with it, and have the refusal say what it is blocking. The damaging deletes tend to come from a cleanup step that reports success, so the loss stays hidden until somebody needs the record.
The pub on the corner has two accounts with its drinks distributor, because two depots each opened one around the time it changed hands. Say an overnight job finds the pair, decides they are one customer, keeps the newer account and deletes the older. The job reports one duplicate merged. The older account was the one holding the returns history and the note that the cellar door is round the back and nobody answers it before ten, and the first person to learn this is a driver standing at the front door on a Tuesday.
That pub is made up. Most deletes that hurt an operation look like it, though. A step whose job is tidying finishes without an error, and the loss turns up weeks later when somebody goes looking for a record that is no longer there.
Where the quiet deletes come from
They tend to start in one of two places. The first is a cleanup step, merging duplicates or clearing out whatever looks old. The second sits underneath, in how records are linked: a link set to cascade means that removing one record removes everything attached to it, and nothing asks first.
That second kind happened in a database OCTYN runs. Two links were set to cascade, and they silently removed interaction records that had been expensive to collect. Nothing reported a problem, since as far as the database knew it had done what it was told.
Make the delete refuse
Both links were changed to restrict. Now a delete that would take other data with it is blocked, and it says what it is blocking. Somebody reads it the same morning and decides what to do.
At the distributor, the overnight merge would stop at the older account and report that returns and delivery notes are still attached, and somebody in customer service would move them across before anything is removed. A refusal costs the few minutes it takes to read. The driver never hears about any of it.
Spotting that two accounts belong to one pub takes judgement, and that is a fair job for a system that knows how the distributor runs. The step that actually removes anything should have no judgement in it, and it should check what is attached every single time, however sure the suggestion sounded.
What to ask before a system may delete
Put this to whoever builds or runs the system, before it goes live.
| What the system wants to remove | What should happen |
|---|---|
| one of two duplicate accounts | move everything attached, then remove the empty one |
| a site that has closed | mark it closed, keep its history readable |
| a record with anything still attached | refuse, and name what is attached |
| a record the model thinks is old | put it in front of a person, delete nothing |
Then ask what happens to rows that are already gone. Reverting the code puts last week's version back and leaves the deleted rows deleted, so the rollback question gets asked twice, once about the code and once about the rows. The post on rollback plans covers the code half.
The cellar door is still round the back, whatever the account says.
Bring the version of this that is about your company.
The first conversation is with the people who would build it. If there is a fit, the Consult comes next.