Soft deletes are a strategy in database design where records are not physically removed from a table when deleted. Instead, a special column—often a timestamp or boolean flag—is used to mark records as deleted. This approach is favored over hard deletes, which permanently remove data, because it allows you to retain historical information, enable data recovery, and maintain referential integrity for related records. For instance, if you delete a project but want to keep its associated tasks and audit trails, soft deletes make this possible without losing valuable context.

-- Add a 'deleted_at' column to the 'projects' table for soft deletes
ALTER TABLE projects
ADD COLUMN deleted_at TIMESTAMP NULL;

Once a deleted_at column is present, you can distinguish between active and soft-deleted records by checking whether this column is NULL or contains a timestamp. Active records have deleted_at set to NULL, while deleted records have a timestamp indicating when they were marked as deleted. This pattern also enables archiving: you can periodically move old or soft-deleted records to a separate archive table, reducing clutter in your main tables and improving query performance. Archiving is especially useful for managing data lifecycle and compliance requirements.

-- Query only active (not soft-deleted) projects
SELECT *
FROM projects
WHERE deleted_at IS NULL;

-- Query only soft-deleted projects
SELECT *
FROM projects
WHERE deleted_at IS NOT NULL;

-- Archive projects soft-deleted more than 1 year ago
INSERT INTO projects_archive
SELECT *
FROM projects
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL '1 year';

-- Remove archived projects from main table
DELETE FROM projects
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL '1 year';