Soft Delete Everywhere

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_deleted or deleted_at column.
• 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.