How to implement soft deletes

Short answer

Soft deletes mark rows as deleted without removing them from the table, typically using a deleted_at timestamp or is_active boolean.

Steps

1. Add a deleted_at TIMESTAMP NULLABLE column to the table.
2. On delete, run UPDATE users SET deleted_at = NOW() WHERE id = 1; instead of DELETE.
3. Filter active rows in queries: SELECT * FROM users WHERE deleted_at IS NULL;
4. Use a database view or application scope to apply the filter automatically.
5. Provide an admin interface to restore or permanently purge soft-deleted rows.

Tips

• Add an index on deleted_at to keep active-row queries fast.
• Use partial indexes in PostgreSQL to exclude soft-deleted rows entirely.

Common issues

• Forgetting to add WHERE deleted_at IS NULL returns deleted data accidentally.
• Unique constraints may conflict with soft-deleted rows unless the index is partial.r