Implementation of Soft Deletes
In this lesson, you will learn how to design schema patterns that allow users to "delete" data while keeping it recoverable for undo operations or audit history.
The Core Concept
A standard DELETE command removes a row from your database permanently, making recovery impossible without a backup. A soft delete changes this behavior by adding a "flag" to your schema. Instead of removing the record, you simply update a status column to indicate the record is no longer active. This allows the application to "hide" the data while maintaining the ability to restore it later.
Designing the Schema
To implement soft deletes, add a column such as is_deleted (Boolean) or deleted_at (Timestamp) to your tables. When a user deletes an item, your application performs an UPDATE rather than a DELETE.
Consider a simple tasks table:
-- Marking a task as deleted
UPDATE tasks
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 101;
Querying and Filtering
Because the data still exists in your database, you must ensure that your standard queries exclude "deleted" records. You can handle this by adding a WHERE clause to every fetch operation:
-- Normal view: only show non-deleted tasks
SELECT * FROM tasks WHERE deleted_at IS NULL;
-- Undo functionality: show only deleted tasks
SELECT * FROM tasks WHERE deleted_at IS NOT NULL;
Tracking History and Integrity
If you need to support more robust undo functionality or audit history, consider using a separate history table or an version integer column alongside your soft-delete flag. By incrementing a version number on every change, you can track how a record evolved before it was eventually "deleted."
Note that soft-deleted rows still contribute to your database size. Over time, this contributes to the Database Bloat we discussed earlier. If your tables grow large, you should periodically run your Data Pruning and Retention Policies to permanently remove records that have been in a soft-deleted state for more than, for example, 30 days.
Key Takeaway: Soft deletes preserve data integrity by flagging records as inactive instead of removing them, enabling seamless undo functionality at the cost of managing long-term storage bloat.