# Soft deletes: the column is step one of four

`deleted_at` without policy enforcement is a comment, not a feature. Every read path must exclude trashed rows, and restore and purge need their own controlled paths.

## Checkable procedure

1. Add `deleted_at timestamptz` to the table, default null. Existing rows are all "alive", which is the correct backfill.
2. Add `deleted_at is null` to every SELECT policy's USING clause. This is the step agents skip: the old policy without the filter keeps serving trashed rows.
3. For UPDATE and DELETE policies, decide the semantics: updates to a trashed row should generally be rejected (it is gone), and "delete" in app code becomes an update setting `deleted_at`. Enforce with WITH CHECK so clients cannot un-delete by writing null.
4. Provide a restore path (sets `deleted_at` back to null) gated to owners or admins, and a purge path: a pg_cron job that hard-deletes rows trashed more than N days ago.
5. Unique constraints need care: a unique email on a table with trashed rows blocks reuse. Use partial unique indexes (`where deleted_at is null`) so trashed rows do not hold the uniqueness slot.

## Ordering constraints

Column first, then policy updates, then the app-code change from delete to soft-delete, then the purge job. Deploying the app change before the policies leaks trashed rows in between.

## Verification

Trash a row and confirm it vanishes from every read path but still exists in the table. Restore it and confirm it reappears. Run the purge job against an old trashed row and confirm hard deletion.