Soft Delete Pattern

Also Known As

TL;DR

Explanation

Soft delete adds a deleted_at timestamp (or is_deleted boolean) to records instead of removing them from the database. Queries filter out soft-deleted records by default. Benefits: audit trail, data recovery, referential integrity preservation, and compliance with data retention requirements. Trade-offs: queries must always filter deleted records (easy to forget), unique constraints need adjustment (unique email among non-deleted users), and tables grow without bound unless archived. PHP ORMs (Eloquent's SoftDeletes trait, Doctrine's SoftDeleteable extension) handle filtering automatically.

Watch Out

Common Misconception

Why It Matters

Common Mistakes

• Forgetting to add WHERE deleted_at IS NULL to every query — deleted records silently appear.
• Not indexing the deleted_at column — every query scans deleted rows unnecessarily.
• Using soft deletes for everything — some data (PII, GDPR-covered data) must be hard deleted.
• Not handling unique constraints — a soft-deleted email prevents re-registration with the same address.

Avoid When

• Tables grow unbounded — soft-deleted rows accumulate forever, bloating indexes and slowing queries.
• Unique constraints are on deletable columns — a soft-deleted record blocks re-creation of the same value.
• Queries throughout the codebase forget the WHERE deleted_at IS NULL guard — data leaks are subtle and dangerous.
• Compliance requirements mandate physical deletion (GDPR right to erasure) — soft delete is not erasure.

When To Use

• Audit trails where you need to know what existed and when it was removed.
• Undo functionality — users can restore accidentally deleted records.
• Referential integrity — related records that reference the deleted item remain valid.
• Business domains where 'deletion' is a state change (order cancelled) rather than destruction of data.

Code Examples

// Soft delete leaking into queries:
SELECT * FROM users WHERE id = 42;
-- Returns soft-deleted user — missing WHERE deleted_at IS NULL

-- Unique constraint issue:
INSERT INTO users (email) VALUES ('bob@example.com');
-- Error: duplicate — previous bob@example.com is soft-deleted but still in table

-- Schema: add deleted_at column
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

-- PHP — Eloquent SoftDeletes trait
use Illuminate\Database\Eloquent\SoftDeletes;

class Order extends Model {
    use SoftDeletes; // adds deleted_at handling automatically
}

$order->delete();         // sets deleted_at, not a real DELETE
Order::withTrashed()->find($id);    // include soft-deleted
Order::onlyTrashed()->get();        // only soft-deleted
$order->restore();        // clears deleted_at

References

• ↗ https://laravel.com/docs/eloquent#soft-deleting

Tags

Related Terms