soft-delete-strategy

Implement a soft delete by adding a deleted_at timestamp to your tables to hide rows from normal queries. Use partial indexes to preserve unique constraints for active records and schedule a daily hard purge to maintain compliance.

Restoring soft deleted records requires transactional integrity and conflict checks to prevent violations of unique constraints like slugs. The restore workflow validates these constraints before undeleting the cascaded related entities.

Cascading soft deletes propagate the deleted_at timestamp across related entities, such as projects and documents, within a single transaction. This ensures that restoring or purging parent records automatically processes all dependent child rows.

A hard purge workflow is needed to meet data-minimization and compliance requirements after a defined retention window. It permanently removes soft deleted records and their related entities to enforce scheduled data expiration.

Partial indexes solve unique constraint conflicts by indexing only rows where deleted_at is null. This allows identical slug or email values to exist across deleted records without breaking uniqueness rules for active data.

Query filtering applies a default condition to exclude rows where deleted_at is not null. This ensures normal application queries never expose soft deleted records while preserving them for audit trails and historical context.