Somebody tidied up a leave type that was not being used. One row, from a table with three rows in it. A year of approved annual leave went with it, including the record of days people had actually taken and been paid for.

Nobody noticed for a while, because the people affected were not looking at their leave history that week. It surfaced when one person asked why their balance was wrong.

The shape of the problem

The schema is the obvious one. A leave_types table — Casual, Sick, Annual — and a leave_requests table with a leave_type_id pointing at it. Textbook lookup table, textbook foreign key.

The foreign key was declared ON DELETE CASCADE, which is a reasonable default in a lot of places and catastrophically wrong here.

Cascade is right when the child rows are part of the parent — order lines belong to an order, and an order with no lines is meaningless. It is wrong when the child rows merely refer to the parent. A leave request is not part of a leave type. It is a historical fact about a person, which happens to mention a category.

That is the whole bug, stated as a design question: is this row owned by that row, or does it just point at it?

Why it was not caught

Three reasons, all common.

The table looked harmless. Three rows of configuration. Nobody runs a careful review before deleting a dropdown option.

The UI gave no warning. A delete button, a confirm dialog, and no indication that anything else would go. A dialog that says “this will also delete 847 leave requests” would have stopped it instantly.

The delete was silent. The cascade happened inside the database. The application logged one deletion, because the application only issued one.

The fix, in order

Order matters here, because the first change stops the bleeding and the others merely make the result pleasant.

1. RESTRICT, not CASCADE

Change the foreign key so the database refuses to delete a type that anything still references. This is a migration and it is the whole safety net. After it, the same mistake produces an error instead of a loss.

It is worth doing even if you plan to add soft deletes five minutes later, because the window between “we decided to fix this” and “the fix is deployed” is exactly when somebody tidies up a table.

Do this audit across every lookup table at once, not just the one that burned you. Wherever a child row records a historical fact — time entries pointing at projects, invoices pointing at clients, audit entries pointing at anything — cascade is the wrong rule and it is probably there by default.

2. Soft delete the type

With RESTRICT in place, the honest delete now fails, which is correct but unhelpful. Somebody genuinely does want unused types out of the way.

Soft delete solves it: a deleted_at column, and the type stops appearing where new things are created. The row stays in the table, so every request that references it still resolves to a real name rather than a dangling id.

3. Filter the picker, not the records

This is the step that gets implemented wrongly.

The archived type must disappear from the dropdown where new requests are created. It must not disappear from existing requests. If the global query scope hides archived types everywhere, every historical request suddenly displays a blank leave type, which looks exactly like the data loss you were trying to prevent.

So: filter at the point of selection, not at the point of retrieval. A type can be archived and still render its name on a two-year-old approved request.

Recovering what was lost

A cascade delete leaves nothing behind in the application. If there is no database backup from before the deletion, the records are gone and the only recovery is reconstruction from whatever else recorded the same facts — approval emails, a payroll export, a calendar.

Reconstruction is slow and approximate, and it produces records with a creation date of today, which is confusing forever afterwards. Worth adding a note on each reconstructed row saying what it is, because the alternative is someone in two years finding a leave request filed eighteen months after the leave was taken and drawing the wrong conclusion.

The general rule

Before writing ON DELETE CASCADE, ask one question: if the parent disappears, is the child meaningless, or is the child a fact that happened?

Order lines are meaningless without the order. Cascade.

Leave requests, time entries, invoices and audit log rows are facts that happened. They mention their parent; they do not belong to it. Those get RESTRICT, and the parent gets a deleted_at column instead.

The cost of getting this wrong is not a bug report. It is a quiet, complete, unrecoverable loss of exactly the records you keep a database in order to have.