Soft Delete Pattern
debt(d7/e5/b7/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints indicate no automated tooling exists (automated: no). Missing WHERE deleted_at IS NULL filters silently return deleted records in production; no linter or SAST tool in the metadata catches this. Only manual code review or runtime observation of incorrect result sets reveals the problem.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix requires adding a column and updating all SELECT/UPDATE queries across the codebase with WHERE deleted_at IS NULL. Common mistakes include broken unique constraints requiring schema changes, plus query audits across multiple files — well beyond a single-line patch but not yet a full architectural rework.
Closest to 'strong gravitational pull' (b7). The pattern applies to both web and cli contexts and shapes every query written against affected tables. Every developer must remember to add the deleted_at IS NULL filter, unique constraints must be redesigned, and archival/cleanup strategies must be maintained. This persistent filter requirement gravitates every future query and schema decision around it.
Closest to 'serious trap — contradicts how a similar concept works elsewhere' (t7). The misconception field explicitly states that soft delete is believed to fully solve data recovery, but without an archive/cleanup strategy, soft-deleted rows accumulate indefinitely and degrade performance. Additionally, unique constraints behave unexpectedly (a re-registered deleted email violates UNIQUE), which contradicts how developers expect uniqueness enforcement to work. These are non-obvious, real-world gotchas that competent developers frequently fall into.
Also Known As
TL;DR
Explanation
Soft delete adds a nullable DATETIME column (commonly deleted_at or is_deleted). All queries filter WHERE deleted_at IS NULL to exclude deleted records. Advantages: data recovery, audit trail, referential integrity preservation. Disadvantages: all queries need the filter, indexes are less efficient as soft-deleted rows accumulate, unique constraints must account for soft-deleted duplicates. Hard deleting after a retention period (archiving) is common.
Common Misconception
Why It Matters
Common Mistakes
- Forgetting the WHERE deleted_at IS NULL filter in some queries — returning deleted records.
- Unique constraints broken by soft-deleted duplicates — a deleted email re-registered violates UNIQUE.
- Not archiving old soft-deleted rows — table size grows indefinitely.
Avoid When
- Avoid soft delete for high-volume append-only data (logs, events) — deleted rows accumulate and degrade performance.
- Do not use soft delete without a cleanup/archive strategy — tables grow indefinitely.
When To Use
- Use soft delete for user data, financial records, and any content requiring audit trails or recovery.
- Use soft delete when related records reference the row via foreign keys — hard delete would violate referential integrity.
Code Examples
-- Hard delete — irreversible, breaks foreign keys, loses history
DELETE FROM users WHERE id = ?;-- Schema
ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;
CREATE INDEX idx_users_deleted ON users (deleted_at);
-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = ?;
-- All queries filter deleted
SELECT * FROM users WHERE deleted_at IS NULL;
// PHP: unique email workaround for soft-deleted
$stmt = $pdo->prepare(
'SELECT id FROM users WHERE email = ? AND deleted_at IS NULL'
);