Supabase soft deletes with RLS: deleted_at plus policies that hide trashed rows

Supabase soft deletes with RLS: deleted_at plus policies that hide trashed rows

TL;DR

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. Column first, then policy updates, then the app-code change from delete to soft-delete, then the purge job.

Use this when you run into the situation in the title.

When not to use this skill: unrelated tasks. It covers only the procedure above.

Steps

1. Add deleted_at timestamptz to the table, default null. Existing rows are all "alive", which is the correct backfill.

   Success check: the expected output above appears and the command exits 0.

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.

   Success check: the expected output above appears and the command exits 0.

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.

   Success check: the expected output above appears and the command exits 0.

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.

   Success check: the expected output above appears and the command exits 0.

Compatibility

Tool: Supabase. The steps above apply to the commands named in them. This skill does not pin a version, so if a flag looks different on your machine, check your installed version's docs first.

Details

Soft deletes: the column is step one of four

Ordering constraints

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.

Verified against live Supabase docs 2026-09-26: https://supabase.com/docs/guides/database/postgres/row-level-security