Deleting an account calls one function and names no table

Our privacy policy says that when you delete your account, your game sessions, exercise attempts and feedback reports are removed immediately. The route that has to make that true is about forty lines long and does not mention a single application table.

await logAuditAction({ userId: user!.id, action: 'account_deletion', ... });

const supabaseAdmin = createAdminClient();
const { error } = await supabaseAdmin.auth.admin.deleteUser(user!.id);

That is the whole deletion. One audit row, one call to the auth provider. Everything else happens in the database.

The link that does not exist

The reason this needs explaining is that the obvious mechanism is absent. Our users_table.id is a bare text primary key with no foreign key to Supabase's auth.users. The app creates that row on first activity rather than at signup, so there is deliberately no referential link between the auth record and the application record, which means deleting an auth user cascades to precisely nothing.

What closes the gap is one trigger:

CREATE OR REPLACE FUNCTION public.handle_user_deletion()
  RETURNS trigger
  LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path TO 'public', 'auth'
AS $$
BEGIN
  -- Cast auth.users.id (uuid) to text to match users_table.id (text).
  DELETE FROM public.users_table WHERE id = OLD.id::text;
  RETURN OLD;
END;
$$;

CREATE OR REPLACE TRIGGER on_auth_user_deleted
  AFTER DELETE ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_user_deletion();

From there the schema takes over. There are 25 tables and 22 of them declare onDelete: 'cascade' against a parent row, so one DELETE from users_table removes sessions, interviews, credits, exercise attempts, onboarding answers and affiliate rows without any of them being named anywhere in the application code. The cast on OLD.id is not cosmetic: a uuid compared against a text column is a type error, and without it the trigger would fail on every deletion rather than on none.

The file your migration tool cannot generate

This is the part worth knowing if you use an ORM with generated migrations. We use Drizzle, and Drizzle does not manage objects on the auth schema. It has no idea that schema exists. So the single most consequential statement in our database is not in lib/db/migrations/, and drizzle-kit generate will never produce it.

It lives in a checked-in .sql file with a comment explaining that it is applied by hand, as the postgres role, through the Supabase SQL editor, because creating a trigger on auth.users needs that privilege. Both statements are CREATE OR REPLACE, so re-running the file is a no-op and anyone restoring the database from a dump can paste it without thinking about ordering.

The file also ends with a line that is pure housekeeping:

DROP FUNCTION IF EXISTS public.handle_auth_user_deleted();

At one point the database contained two functions with the same body: the live one, and an earlier twin attached to no trigger at all. Neither name told you which was which. An unattached function is not a bug, it is worse, because the next person to read the schema has to work out which copy actually runs before they can trust anything. Deleting it was the fix, and keeping the DROP in the file is what stops it coming back on the next restore.

The record of the deletion deletes itself

One honest wrinkle. That audit row written on the line before the call is stored in audit_logs, which is one of the 22 tables with ON DELETE CASCADE to users_table. So the cascade erases the record of the erasure. For a right-to-erasure request that is the correct behaviour, because a retained log keyed on a user id is still personal data. But it does mean the log is there to let a user see their own account activity in an export, not to prove to anyone later that a deletion happened. Proof of that kind has to live outside the user's own data graph, and ours deliberately does not.

The route is also behind the sensitive rate-limit preset, 5 requests a minute. Deleting an account twice is harmless; what the preset is really there for is the class of destructive endpoints as a whole.

See it

Open cogniprep.app/privacy and read the Data Retention section. Every line in it that says "removed immediately when you delete your account" is a promise about cascade behaviour, not about application code, and the application code is not capable of keeping it on its own. If you are building the same thing on Supabase, the check worth running is simple: find your auth.users to application-row link, and confirm your migration tool knows it exists.