Account deletion

Deleting an account is the one irreversible thing your app lets a user do to themselves, so it ships fully built: a Danger Zone entry point, a precheck that explains blockers before anything is destroyed, a type-to-confirm dialog, an atomic database cascade, and asynchronous cleanup of everything Postgres cannot reach on its own.

There is nothing to turn on. There is one secret to set, and getting it wrong fails every deletion closed.

The pepper

Both halves of the deletion ledger are keyed with a single Vault secret, account_deletion_email_pepper.

The ledger itself is private.account_deletions: a permanent, minimal record that an account existed and was deleted. It stores no email, only an HMAC-SHA256 of the lowercased address, keyed with the pepper. That is pseudonymized, not anonymized, so the table lives in the non-exposed private schema with RLS on, no policies, and no grants for anon, authenticated or service_role.

Two triggers read the pepper, and they disagree on purpose about what to do when it is missing.

The asymmetry is the right one. A deletion that cannot be recorded must not happen: writing an unkeyed or weakly keyed hash would be worse than refusing. A signup, on the other hand, must never be blocked by a fraud heuristic, so the trial-abuse check (which closes the "delete account, sign up again with the same email, get a fresh trial" loophole) just skips itself and says so in the Postgres log.

The symptom

With no pepper, or one shorter than 32 characters, every deletion fails, including deletions from the dashboard or from admin SQL, because the trigger is on the table, not on the Edge Function.

What the user sees is a generic failure:

{ "error": { "code": "DELETION_FAILED", "message": "The account could not be deleted." } }

with HTTP 500. The real cause, The account_deletion_email_pepper Vault secret is missing or invalid., is written by console.error and lives only in the delete-account Edge Function logs. If deletion is broken and the app is showing "Something went wrong", that is the first place to look.

The fix

The secret is read from the Vault under its lowercase name. Locally, [db.vault] in supabase/config.toml seeds it from supabase/.env.local:

[db.vault]
account_deletion_email_pepper = "env(ACCOUNT_DELETION_EMAIL_PEPPER)"

floot create generates a value unique to your project, so a fresh project deletes accounts on the first try:

ACCOUNT_DELETION_EMAIL_PEPPER=<48 random bytes, base64>

If you set up the kit without the CLI, generate one with openssl rand -base64 48.

On a hosted project nothing seeds the Vault for you. Create the secret yourself (Studio → Vault, or vault.create_secret()) using the exact lowercase name account_deletion_email_pepper.

supabase stop --no-backup && supabase start

What blocks a deletion

delete-account calls public.check_account_deletable(), a user-context RPC, before it touches anything. It returns either {"success": true} or a typed error the client can act on, which is the whole point: it exists so the app can say why, rather than surfacing a 500 from a trigger.

The precheck is advisory. The authoritative guard is the BEFORE DELETE trigger trg_prevent_user_deletion on public.users, which raises the same codes and fires as part of the cascade from auth.admin.deleteUser(). It catches the race between checking and deleting, and it cannot be bypassed by deleting the auth.users row directly.

The order is deliberate. Hard blockers surface first and identity freshness last, because freshness is the one blocker every user can clear immediately; reporting it first to someone who also owns three organizations would just make them do the work twice.

Re-verifying identity

app.has_recent_identity_verification() uses a 5-minute window, and picks its evidence based on whether the user has a verified TOTP factor:

• MFA-enrolled: requires a recent TOTP verification (aal2).
• Everyone else: any amr entry in the JWT within the window. A fresh sign-in, at any AAL, satisfies it.

The app falls back through the least disruptive method that fits: TOTP, an in-place password prompt, native Google or Apple re-authentication on mobile, and, when none of those apply, a dialog asking the user to sign out and back in.

That last rung is where GitHub-only accounts, magic-link-only accounts and web OAuth sessions land. It is not a dead end: because the window is a plain 5 minutes on any amr entry, signing out and back in clears REVERIFICATION_REQUIRED outright, and the deletion flow can be started again straight away.

What happens on deletion

Everything destructive is one transaction, and that transaction talks to nothing outside Postgres: auth.admin.deleteUser() deletes the auth.users row, and every foreign key in the schema is ON DELETE CASCADE, so the guard trigger, the deletion ledger and both cleanup enqueues (below) all run inside that same transaction. Any one of them raising aborts the whole thing with the account untouched. Only once that transaction has committed does best-effort work follow: the RevenueCat subscriber is deleted (a no-op when REVENUECAT_SECRET_API_KEY is unset, and a 404 counts as success), and the account_deleted email is sent. Neither failing can fail a deletion that already succeeded; both are logged only.

Object storage and Stripe are queued, never called inline

Postgres CASCADE does not reach object storage, and no transaction should be holding locks while it waits on api.stripe.com. Both are handled by a transactional outbox on pgmq.

Deleting the user enqueues two {bucket, prefix} messages onto pgmq.q_storage_cleanup: avatars/users/{user_id}/ and feedback/users/{user_id}/, one prefix covering every feedback attachment the account ever filed. The cascaded public.customers rows enqueue onto pgmq.q_stripe_cleanup, one message per row whose provider is stripe, and nothing for the auto-created RevenueCat rows every account gets. pgmq.send() is an ordinary INSERT, so those messages commit, and roll back, with the deletion itself.

An AFTER DELETE statement trigger on each table then pings the matching drain function over pg_net, once per transaction no matter how many rows cascaded. Those pings fail open by design: a Vault hiccup or a cold function must never become the reason an account deletion aborts.

Each queue has a pg_cron backstop for every way that ping can fail to arrive, running on the hour at different minutes so the two jobs stay distinguishable in cron.job_run_details:

Nothing is dropped; it is only ever late. The Stripe half, what drain-stripe-cleanup actually does with a customer, and why a 404 is a success, is on the Stripe page under Customer cleanup.

SELECT cron.unschedule('storage-cleanup-drain-sweep');
SELECT cron.unschedule('stripe-cleanup-drain-sweep');

The signal to alert on

drain-storage-cleanup deletes a message it completed and archives one it gave up on. There are exactly two ways to be given up on: an unusable payload, or a sixth delivery. read_ct is incremented by every read, and the drainer archives rather than processes once it exceeds 5. Anything else, a storage error, a timeout, leaves the message queued, its visibility timeout lapses, and the next kick or sweep retries it.

So a message in the archive is a prefix whose objects were never removed. That is your alert:

-- Storage cleanups that were given up on. This should be empty.
SELECT msg_id, read_ct, enqueued_at, archived_at, message
FROM pgmq.a_storage_cleanup
ORDER BY archived_at DESC;

A backing-up queue is the same story caught earlier: messages that keep failing have not exhausted their retries yet, so watch the depth and the age of the oldest message too:

-- Depth and oldest message. Anything much older than the hourly sweep is stuck.
SELECT count(*) AS depth, min(enqueued_at) AS oldest
FROM pgmq.q_storage_cleanup;

pgmq.a_stripe_cleanup and pgmq.q_stripe_cleanup answer the same two queries for the Stripe side. That drainer is far more patient, 48 deliveries rather than 5, sized to outlast a Stripe outage rather than to count attempts, so a message reaching that archive has been failing for days.

The account-deleted email

Once the deletion has succeeded, delete-account sends the account_deleted email directly. It does not go through the send-email function, because there is no auth event behind it.

• Template: supabase/functions/send-email/_templates/account/account-deleted-email.tsx
• Subject: supabase/functions/send-email/_templates/l10n.ts, key account_deleted
• Language: read from users_prefs.language before the cascade, falling back to en

The body confirms the deletion and carries a retention notice (financial records such as invoices are kept for the period the law requires, no longer linked to the user's identity) plus a "if you didn't request this, contact support" line. Edit the copy in the template; both en and fr live in the same file.