Soft deletes: when a deleted_at column is the wrong answer
Every web app eventually gets the request: "Can you make delete undoable?" The soft-delete flag seems like the cheapest answer. Add a deleted_at column, filter it out of queries, done. Then six months later the admin dashboard shows wrong totals because a report forgot the scope, a deleted user's email blocks a new signup, and nobody remembers which of the fourteen queries touching that table was supposed to exclude the rows. Soft deletes are a real tool, but they're a design decision, not a default.
What the flag actually costs
A deleted_at column changes the meaning of every query against the table. In Laravel the global scope helps: SoftDeletes hides those rows from Eloquent automatically. The catches live everywhere else:
- Raw queries, joins, reports, and analytics SQL don't know about the scope. Each one needs an explicit
WHERE deleted_at IS NULL, and the ones that miss it produce numbers that are almost right. - Unique constraints fight the flag.
UNIQUE(email)plus a soft-deleted row means the user can't sign up again with the same address. Partial unique indexes can fix this (we wrote about that in the Postgres constraints piece), but you have to remember to fight for it. - "Delete" stops meaning anything. Users see a deleted record, then find it in a dropdown elsewhere, or their request to be removed isn't actually removal.
The failure mode is consistent: one forgotten filter, and deleted data reappears for some users and not others. Those bugs are hard to reproduce because they depend on which code path built the list.
When soft deletes are the right call
Some data earns the flag. The test is whether other rows depend on it and whether someone may need it back:
- A user account that owns invoices. Delete the user row hard, and the invoice history loses its author, or the invoice has to be an orphan. Keeping the user with a flag preserves the references.
- Content with an undo window, where "delete" is really "hide until the user changes their mind."
Notice what's common: the row is referenced by other data, and restoration is a plausible support request. A favorites list or a draft comment doesn't meet that bar. Nothing references them in a way a hard delete would break.
The alternatives worth considering
Before adding the flag, check whether something simpler fits:
- A status column.
orders.status = 'cancelled'is more honest thanorders.deleted_at. States like cancelled, archived, or disabled usually carry meaning that a boolean timestamp erases, and reports can group by them. - Hard delete plus an audit record. If the goal is traceability, an audit table (who deleted what, when, and the old values) answers it without polluting the live table. Our audit logging article covers the shape of that.
- Hard delete, full stop. Backups exist. Undoing a wrong delete by restoring from last night's backup is rare, and when it happens, keeping every deleted row in the live table to save that one recovery is a bad trade.
There's also the GDPR problem. An erasure request isn't satisfied by a row marked deleted while the data still sits in the table. If your product handles personal data, decide before launch which fields get anonymized on erasure (keeping only an opaque ID for references) and which tables simply hard-delete. That decision is much easier to make in a schema review than as an incident.
If you do keep the flag
Three disciplines make it survivable. First, exclude deleted rows in raw SQL and reports too, and write a test that deletes a record and walks the main screens looking for it. Second, fix the unique constraints so a deleted row can't block a new one, usually with a partial index. Third, set expectations: state in the UI whether delete is permanent or recoverable, and for how long. "We keep it for 30 days" is a sentence a user can act on; a silent flag is not.
The honest version of this post is that most tables don't need soft deletes. Add them where references and support requests genuinely justify them, and let everything else delete for real.