# Implementing soft deletes

> Mark rows as deleted instead of removing them, without RLS errors. A deleted_at column, a filtered view, an RPC to delete, partial indexes, and a cron job to purge old rows.

Source: https://supahost.app/kb/soft-deletes · Updated 2026-11-11

A soft delete keeps the row and marks it as deleted, so users can undo it and you keep history. The common first attempt, a `deleted_at` column plus a select policy that hides deleted rows, fails in a confusing way. Here's a version that works, using an invoice tool as the example.

## The trap

You add `deleted_at`, change the select policy to `using (deleted_at is null and ...)`, and soft delete with an update:

```ts
await supabase.from('invoices').update({ deleted_at: new Date().toISOString() }).eq('id', id)
```

and get:

```
new row violates row-level security policy for table "invoices"
```

When an `update` needs to read rows (it does, because of `.eq('id', id)`), Postgres also checks the **updated** row against the table's select policies. The updated row has `deleted_at` set, the select policy hides it, so the update is rejected.

## A pattern that works

**1. Add the column and keep the select policy about ownership only.**

```sql
alter table public.invoices add column deleted_at timestamptz;

create policy "Owners read their invoices"
on public.invoices for select
to authenticated
using ( (select auth.uid()) = owner_id );
```

**2. Read through a view that hides deleted rows.** `security_invoker` makes the view apply the table's RLS for the caller:

```sql
create view public.active_invoices
with (security_invoker = true) as
select * from public.invoices
where deleted_at is null;
```

```ts
const { data } = await supabase.from('active_invoices').select('id, number, total_cents')
```

**3. Delete and restore through functions**, so the rules live in one place:

```sql
create or replace function public.soft_delete_invoice(invoice_id bigint)
returns void
language sql
set search_path = ''
as $$
  update public.invoices
  set deleted_at = now()
  where id = invoice_id and deleted_at is null;
$$;

create or replace function public.restore_invoice(invoice_id bigint)
returns void
language sql
set search_path = ''
as $$
  update public.invoices
  set deleted_at = null
  where id = invoice_id;
$$;
```

These are `security invoker` (the default), so your existing update policy still decides who can change which invoice.

```ts
await supabase.rpc('soft_delete_invoice', { invoice_id: 42 })
```

**4. Stop hard deletes from clients** by not granting a delete policy (or `revoke delete on public.invoices from anon, authenticated`).

## Keep unique constraints and indexes working

A plain `unique (owner_id, number)` would stop a user reusing an invoice number after deleting the old one. Make it a partial index over live rows only, and index what live queries filter on:

```sql
alter table public.invoices drop constraint if exists invoices_owner_id_number_key;
create unique index invoices_live_number_key
  on public.invoices (owner_id, number) where deleted_at is null;

create index invoices_live_owner_idx
  on public.invoices (owner_id) where deleted_at is null;
```

## Purge old rows on a schedule

Soft-deleted rows still take space. Remove them after 30 days with pg_cron:

```sql
select cron.schedule(
  'purge-deleted-invoices',
  '15 3 * * *',
  $$ delete from public.invoices where deleted_at < now() - interval '30 days' $$
);
```

## Things to decide

- **Related rows.** Foreign keys with `on delete cascade` don't fire on a soft delete. Soft delete children in the same function, or hide them through their own views.
- **Realtime.** A soft delete is an `UPDATE` event, not a `DELETE`. Handle it in your listener.
- **Privacy requests.** When someone asks for their data to be erased, do a real `delete`.

## On SupaHost

pg_cron is already set up in every project; don't run `create extension pg_cron`. The job also shows in *Dashboard → your project → **Cron** tab*, where you can pause it or see recent runs. Cron schedules are in UTC.

## Related

- [Row Level Security, simplified](/kb/rls-simplified)
- [Scheduled jobs with pg_cron](/docs/cron-queues/cron)
- [Indexes](/docs/database/indexes)
- [Calling database functions (RPC)](/docs/api/rpc)