# Pattern 12 — Soft delete with RLS

## What this is

Soft deletes (`deleted_at` timestamp) combined with RLS have two failure modes that are easy to miss: a missing `deleted_at is null` in the SELECT policy leaks tombstoned rows, and a missing constraint in the UPDATE policy lets users restore their own deleted records.

## The partial index pattern

A standard index on `org_id` is fine for tenant isolation. A soft-delete SELECT policy adds a second filter: `and deleted_at is null`. For large tables, create a partial index that only covers active rows:

```sql
create index on documents(org_id)
  where deleted_at is null;
```

This index is smaller (doesn't cover deleted rows) and PostgreSQL uses it for the common query pattern. The regular `documents_org_id_idx` still covers admin queries that need to see deleted rows.

## The restore vulnerability in UPDATE

The subtle failure mode: if your UPDATE USING clause doesn't require `deleted_at is null`, users can restore their own deleted rows:

```sql
-- Dangerous UPDATE policy:
create policy "tenant_update"
  on documents for update to authenticated
  using (org_id = (select org_id from users where id = auth.uid()));
  -- No deleted_at check — user can update a deleted row to clear deleted_at
```

The correct policy adds `and deleted_at is null` to the USING clause. This means once a row is soft-deleted, the user can no longer SELECT or UPDATE it — the row is invisible to them. Restore is an admin-only operation.

## How the policy interaction works

When a user soft-deletes a row (`set deleted_at = now()`), the UPDATE succeeds (the row was active at the time the USING clause was evaluated). After that update, the row has `deleted_at IS NOT NULL` — so the USING clause for future UPDATEs no longer matches. The user cannot touch it again.

## Testing

```sql
-- Soft delete a document
update documents
set deleted_at = now()
where id = '<row-id>';

-- As the owning user — should return 0 rows (invisible after soft delete)
select * from documents where id = '<row-id>';

-- Attempt to restore (should fail — row is no longer visible to UPDATE)
update documents
set deleted_at = null
where id = '<row-id>';
-- Result: 0 rows affected (not an error, just silently blocked)

-- As admin — row is still visible
-- (set app_metadata.app_role = 'admin' in the JWT)
select * from documents where id = '<row-id>';
-- Returns the soft-deleted row
```

## Common mistake

Forgetting `deleted_at is null` in the SELECT policy. Users query "my documents" and receive a mix of active and deleted rows — either the UI shows ghost entries or the product leaks content the user believed they deleted.