Soft Delete Everywhere
Blanket soft delete adds an is_deleted flag to every table, so all queries must filter it, unique constraints collide, and tables bloat with dead rows. Decide retention per table, use archival tables and audit logs, and apply soft delete only where it fits.
Soft delete marks a row as deleted with a flag (is_deleted, deleted_at) instead of physically removing it. Applied selectively where retention or recovery genuinely matters, it is useful. Applied as a blanket default on every table, it becomes an anti-pattern that quietly degrades correctness and performance across the whole schema.
Why It Happens
Fear of permanent data loss makes never-really-deleting feel safe. A vague desire for an audit trail or undo gets implemented as a flag rather than a purpose-built mechanism. The pattern spreads by convention: once one table has it, every table gets it for consistency, without asking whether each table needs it.
Why It Hurts
Every query against every soft-deleted table must remember to filter out deleted rows. Miss the filter once and deleted data leaks into a UI, a report, or an export — a whole class of subtle, recurring bugs. Unique constraints break, because a deleted row still occupies the unique value, blocking a new row with the same email or code unless the constraint is made partial. Foreign keys can point at logically deleted parents. Tables bloat with dead rows that slow scans and inflate indexes and backups. Aggregations and analytics must everywhere account for the flag, and getting it wrong corrupts metrics.
Warning Signs
- Almost every table carries an
is_deletedordeleted_atcolumn. - Bugs where deleted records reappear because a query forgot the filter.
- Unique constraint violations caused by deleted rows holding the value.
- Large tables dominated by rows that are logically gone.
Better Alternatives
Decide retention table by table. For data that needs to survive deletion, move removed rows to an archival table so the live table stays clean and queries need no flag. For tracking who changed what, use a dedicated audit log rather than overloading the primary table. Where soft delete truly fits, apply it selectively, use partial unique indexes that ignore deleted rows, and centralize the filter so individual queries cannot forget it.
How to Refactor Out of It
Classify each table's actual deletion needs. For tables that do not need recovery, switch to hard deletes plus an audit log. For those that do, move to archival tables or, if keeping soft delete, enforce the filter through a view or query layer and add partial unique indexes. Backfill by purging or archiving long-dead rows to reclaim space. Add tests that assert deleted rows never surface. The result is correctness by construction rather than by everyone remembering a WHERE clause.