Soft Deletes And Audit Columns
A soft delete marks a row as removed instead of physically deleting it. Rather than running a delete, you set a column such as a deleted at timestamp. The row stays in the table but is hidden from normal queries.
Soft deletes preserve history, allow undo, and keep foreign keys from breaking when other rows still reference the record. The costs are real, though. Every query must filter out the deleted rows, which is easy to forget, and unique constraints get tricky because a deleted row still occupies its old value.
Audit columns record the lifecycle of a row directly on the table:
- created at and updated at timestamps
- created by and updated by user references
- A version number for optimistic concurrency
These columns answer who changed this and when without a separate log. For deeper needs, a dedicated audit table captures the full before and after of each change as separate history rows.
A subtle interaction is uniqueness with soft deletes. A unique constraint on an email column will reject a new user reusing the email of a soft deleted account. The fix is a partial unique index that applies only where deleted at is null, so uniqueness holds among live rows while deleted rows step aside.
Key idea
Soft deletes hide rows with a flag to preserve history and enable undo, but they force every query to filter deleted rows and need partial unique indexes for uniqueness among live rows.