Implementing soft deletes

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:

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.

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:

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

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:

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.

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:

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:

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
• Scheduled jobs with pg_cron
• Indexes
• Calling database functions (RPC)