How to handle soft deletes in Supabase using SQL
Anthony Main
founder
2 minutes
time to read
July 11, 2025
published
Supabase is a great tool for modern app development, but sometimes its out-of-the-box solutions don’t quite fit the bill, especially when it comes to soft deletes. In a recent project, we needed a way to handle this directly in SQL without relying on the Service key or spinning up an Edge function. Here’s how we approached it.
We recently needed to implement a soft delete function in a project, but the only official provision for this is via the client library using the Service key.
We didn’t want to write an Edge function to perform this task as linking to it from Postgres would be far too convoluted, and we certainly weren’t going to include our Service key in any frontend app, so we duplicated the functionality that Supabase Server (Go code) performs in this SQL Function.
N.B. You will need to set your RLS according to your architecture. This is provided as an example, it is not our actual implementation.
Simply call it with:
PERFORM auth.soft_delete_user('00000000-0000-0000-0000-000000000000');-- 1. Helper: obfuscate value (SHA256 + base64)
CREATE OR REPLACE FUNCTION public.obfuscate_value(id UUID, value TEXT)
RETURNS TEXT AS $$
DECLARE
hash BYTEA;
BEGIN
hash := digest(id::TEXT || COALESCE(value, ''), 'sha256');
RETURN encode(hash, 'base64');
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 2. Soft delete procedure for Supabase Auth users (now in private schema)
CREATE OR REPLACE PROCEDURE public.soft_delete_user(target_id UUID)
LANGUAGE plpgsql
AS $$
DECLARE
obfuscated_email TEXT;
obfuscated_phone TEXT;
obfuscated_email_change TEXT;
obfuscated_phone_change TEXT;
now_time TIMESTAMPTZ := NOW();
identity_rec RECORD;
provider_obfuscated TEXT;
BEGIN
-- Update user record: obfuscate and clear sensitive fields, set deleted_at
UPDATE auth.users
SET
email = public.obfuscate_value(target_id, email),
phone = LEFT(public.obfuscate_value(target_id, phone), 15),
email_change = public.obfuscate_value(target_id, email_change),
phone_change = public.obfuscate_value(target_id, phone_change),
encrypted_password = NULL,
confirmation_token = '',
recovery_token = '',
email_change_token_current = '',
email_change_token_new = '',
phone_change_token = '',
deleted_at = now_time,
raw_user_meta_data = '{}'::jsonb,
raw_app_meta_data = '{}'::jsonb
WHERE id = target_id;
-- Clean up all sessions for this user
DELETE FROM auth.sessions WHERE user_id = target_id;
-- For each identity, obfuscate provider_id and clear identity_data
FOR identity_rec IN SELECT id, provider, provider_id FROM auth.identities WHERE user_id = target_id
LOOP
provider_obfuscated := public.obfuscate_value(target_id, identity_rec.provider || ':' || identity_rec.provider_id);
UPDATE auth.identities
SET provider_id = provider_obfuscated,
identity_data = '{}'::jsonb
WHERE id = identity_rec.id;
END LOOP;
-- Delete refresh tokens (cast required as its a varchar not UUID)
DELETE FROM auth.refresh_tokens WHERE user_id = target_id::text;
-- Procedure is idempotent: running again will not error or re-delete
END;
$$;This solution gave us more control, avoided unnecessary complexity, and kept our architecture secure. It’s not a drop-in for every project, but if you're dealing with similar limitations, it’s a solid starting point. As always, adapt it to your own RLS policies and schema, and feel free to build on it from here.
Apply theses insights
Contact us to discuss how we can apply theses insights to your project
