Add a Restore Path for Soft Deletes

Mark rows deleted instead of removing them, filter them from normal queries, and expose restore and purge endpoints with clear retention rules.

~35 mindatabasesdata-lifecycleapi-design

Task

Implement soft delete with a restore API and a scheduled hard purge after a retention period.

When to use this

Hard deletes make an accidental removal unrecoverable, yet soft-deleted rows silently leak into listings and counts if queries are not updated.

Prerequisites

A nullable deleted_at column or equivalent flag
A retention policy defining when purge happens

Steps

1. Add deleted_at and set it instead of issuing DELETE.

Expected output: Rows remain recoverable.

2. Filter deleted_at IS NULL in every normal read and count query.

Expected output: Deleted rows do not appear in listings.

3. Expose a restore endpoint that clears deleted_at for a bounded window.

curl -X POST https://api.example.com/notes/42/restore

Expected output: Users or support can undo an accidental delete.

4. Run a purge job that hard-deletes rows older than the retention window.

Expected output: Storage is reclaimed after the grace period.

Troubleshooting

Problem

Deleted rows still counted

Solution

Audit every query and view; partial indexes on deleted_at IS NULL keep filtering efficient.

Problem

Unique constraints block re-creation

Solution

Use a partial unique index excluding deleted rows or include deleted_at in the constraint.

Limitations

Soft delete complicates every query and constraint with a deleted filter, which must be consistently applied.
It is not a substitute for backups and does not satisfy a legal erasure request on its own.

Context

Implement soft delete with a restore API and a scheduled hard purge after a retention period. Hard deletes make an accidental removal unrecoverable, yet soft-deleted rows silently leak into listings and counts if queries are not updated.

Steps

Add deleted_at and set it instead of issuing DELETE. Expected: Rows remain recoverable.
Filter deleted_at IS NULL in every normal read and count query. Expected: Deleted rows do not appear in listings.
Expose a restore endpoint that clears deleted_at for a bounded window. Expected: Users or support can undo an accidental delete.
Run a purge job that hard-deletes rows older than the retention window. Expected: Storage is reclaimed after the grace period.

-- restore within the grace window
UPDATE notes SET deleted_at = NULL
WHERE id = $1 AND deleted_at > now() - interval '30 days';

Verify

Rows remain recoverable.
Deleted rows do not appear in listings.
Users or support can undo an accidental delete.
Storage is reclaimed after the grace period.

Limits

Soft delete complicates every query and constraint with a deleted filter, which must be consistently applied.
It is not a substitute for backups and does not satisfy a legal erasure request on its own.