Soft delete needs a taxonomy of children, not a decision per table

A test suite went red in the middle of a session on code nobody had touched. The cause was that it picked the newest entry from a price list and used the product on it, and that product had been soft deleted by other work in the shared development database. The price entry survived its parent. Every lookup of that product returned not found, so the test failed on a record that was, from the API’s point of view, correctly absent.

That is a small annoyance in a test. In the portal it was about twenty-five rows on a page whose labels could not resolve, and an action offered on each of them that could never succeed.

Soft deleting a product had no cascade at all. Not a wrong cascade — none. The variants under it, the price-list entries referencing it and the per-customer price overrides all stayed active and reachable, pointing at a parent that no longer answered.

The repair, and the part it missed

The fix cascaded the soft delete from a product to its variants, its price-list entries and its customer overrides, and a migration repaired what was already broken: 211 orphaned price entries and 104 orphaned variants, cleared in one pass.

That felt complete. It was not.

Deleting a variant directly had no cascade either. A variant is a child of a product and a parent of price entries, and the fix had only been written on the product path. So the exact rows that had just been repaired could be orphaned again the next day, by a different delete, through a path nobody had looked at because the bug had been reported against products.

The same omission had a third instance. Branch deletion orphaned stock-level rows, and stock-level rows had not been considered at all in the first round because the reported symptom involved a product.

The pattern: a cascade fixed on the path where the bug was reported is a fix for that report, not for the relationship. The relationship is between a parent and a child, and it has to be settled once for every way that parent can be removed.

Why “just cascade everything” is wrong

The tempting correction after finding three missing cascades is to cascade everything. That would be worse, and it took a second round to say why properly.

An order line references a product. If deleting a product removed order lines, then deleting a discontinued item would rewrite last year’s sales. A stock movement references a variant and a branch. If deleting a branch removed movements, the ledger that the current quantities are derived from would develop holes, and the arithmetic would stop balancing.

So the answer is not per table and it is not universal. It is a small taxonomy applied once, and then applied mechanically to every new child table.

Child configuration. Rows that exist only to configure the parent and have no meaning without it: variants, price-list entries, per-customer price overrides. These cascade. Nothing is lost, because they described a thing that is now gone.

Transactional history. Rows recording that something happened: order lines, dispatch lines, stock movements, and the orders, invoices and payments that carry a branch. These never cascade. They are the record of the past and the past does not change because a catalogue entry was retired. A report covering last quarter must produce the same figures next year as it does today.

Derived current state. Rows that are a projection of transactional history rather than a record in their own right. The stock-level row is the clearest case: it is the current quantity, and the current quantity is reproducible from the immutable movement ledger.

That third category was the one that needed an argument, because it looks like history — it is stock data, it sits next to the ledger — and it behaves like configuration. It cascades, on three grounds. Nothing is lost, because the ledger keeps everything. The row is unreachable in practice once its parent returns not found, so leaving it active only makes the list longer and the labels blank. And it carried the reorder-level setting, which meant the stock list was offering an action on rows where the action could never succeed.

Getting the classification right is the whole job. Once a child table is classified, the cascade question has already been answered for every delete path that reaches it, including the ones written later.

Measure the damage before and after

Both cascade fixes shipped with a repair migration, and both migrations reported counts rather than running quietly.

Of 523 live stock-level rows, 259 were orphaned by a deleted product and 22 by a deleted branch. That is roughly half the table pointing at something that no longer exists, in a system that had been running for weeks with tests passing.

Counting matters for two reasons beyond the obvious. It tells you whether the bug was theoretical or whether it had been quietly accumulating, which is a different conversation with whoever is waiting on the release. And it gives the migration an assertion: after the repair, the count of orphans is zero, and that assertion is re-run after the migration is exercised forward, backward and forward again. A repair migration with no count is a script you hope worked.

What a soft delete actually promises

Underneath all of this is a question worth asking before adopting soft deletes at all, and I did not ask it early enough.

A hard delete has a clear meaning enforced by the database: the row is gone, and foreign keys either prevented the delete or removed the children. The rules are declarative and the database applies them whether or not anybody remembered.

A soft delete is a flag on a column. Nothing in the database knows it means anything. Every read must remember to filter on it, every index that should be unique-among-live-rows must be partial on it, and every parent-child relationship must have its cascade written by hand in application code. None of that is enforced. All of it is convention, and conventions have a failure rate.

That is a real cost and it buys a real thing — records that can be recovered, and history that survives a mistake. It is a fair trade. But it should be made knowingly, with the understanding that you have taken referential integrity out of the database’s hands and put it into a list of rules that somebody has to keep applying.

Rules

Classify every child table once: configuration, history, or derived state. Configuration and derived state cascade. History never does.

Settle the cascade on the relationship, not on the delete path that was reported. Every way the parent can be removed needs the same behaviour, including the paths where the parent is itself a child.

Ship a repair migration with counts, and assert zero afterwards. If the number surprises you, the bug was older than the report.

If you adopt soft deletes, write down that you have moved referential integrity into application code. It is a defensible trade and it stops being defensible the moment people forget it was made.