Deleting an account in our app is one line of SQL, and the audit row has to be written before it runs

Nakodo is an outreach app for brands. The deal it makes about data is written out on the privacy page: you can export what is yours, and you can delete the account, and deleting means deleting.

You can watch the last step of it happen without having an account. This URL is the page a deleted account is sent to:

nakodo.app/?deleted=1

$ curl -s "https://nakodo.app/?deleted=1" | grep -o "Your account and its data have been deleted"
Your account and its data have been deleted
$ curl -s "https://nakodo.app/" | grep -c "have been deleted"
0

This post is everything that happens before that redirect.

The whole routine

export async function deleteAccountAction(_: unknown, form: FormData): Promise<ActionResult> {
  const user = await requireUser();
  const { email } = formText(form, "email");
  if (!email || email.toLowerCase() !== user.email.toLowerCase())
    return fail("Type your account email exactly to confirm.");
  const slow = await tooFast("billing", user.id);
  if (slow) return slow;
  try {
    await deleteStripeCustomer(user.id);
  } catch (e) {
    console.error("Deleting Stripe customer failed", user.id, e);
    return fail("We couldn't cancel your paid plan, so nothing was deleted. Try again, or email [email protected].");
  }
  await audit({ action: "account.deleted", userId: user.id, entityType: "user", entityId: user.id });
  try {
    await db.execute(sql`delete from auth.users where id = ${user.id}`);
  } catch (e) {
    return failure(e, { context: "Deleting account failed" });
  }
  const supabase = await createSupabase();
  await supabase.auth.signOut();
  redirect("/?deleted=1");
}

Twenty-two lines. Confirm, rate limit, Stripe, audit, one DELETE, sign out, redirect. Every interesting decision in the feature is in the order of those steps rather than in their contents.

Stripe goes first, and failing there deletes nothing

A deleted account with a live subscription is the worst possible outcome: a card still being charged on a monthly cycle with nobody left to email about it, and no row anywhere in our database to explain the charge.

So the Stripe customer is deleted first, which cancels the subscription and removes the saved card, and if that throws, the function returns and nothing else has run. The only tolerated failure is a customer that is already gone:

export async function deleteStripeCustomer(userId: string): Promise<void> {
  const sub = await getSubscription(userId);
  if (!sub.stripeCustomerId) return;
  try {
    await stripe().customers.del(sub.stripeCustomerId);
  } catch (e) {
    if (!isMissingResource(e)) throw e;
  }
}

A missing resource means the work is done, which is the right reading of a 404 on a del call for anything idempotent.

The audit row has to be written before the delete

This is the line I would have got wrong if the schema had not stopped me.

await audit({ action: "account.deleted", userId: user.id, ... });
await db.execute(sql`delete from auth.users where id = ${user.id}`);

The audit table's actor column is a foreign key:

userId: uuid().references(() => authUsers.id, { onDelete: "set null" }),

Write the audit row after the delete and you are inserting a reference to a user who no longer exists, which Postgres refuses, so the log would simply have nothing about the one action that matters most. Write it before, and ON DELETE SET NULL does something better than either alternative: the row survives, the account identifier in it does not. The log keeps that an account was deleted, when, and what it was, and loses who, which is the only arrangement under which an audit trail and a deletion request can both be honoured.

Two orderings, one foreign key clause, and the difference between them is whether a deletion is auditable at all.

Why it is raw SQL and not the admin API

The obvious way to delete a Supabase auth user is supabase.auth.admin.deleteUser(id). That call needs a service-role key, and this app does not have one. The only Supabase key in the repo is the publishable anon key, which sits in a file as a plain literal with a comment saying it is not a secret, because it is sent to every browser and what it can reach is bounded by row-level security.

Adding a service-role key to get one feature would mean holding a credential that can read and write every row belonging to every account, in a serverless app, for the sake of a statement the database connection we already have can run:

delete from auth.users where id = $1

DATABASE_URL is already in the environment because the ORM needs it, the statement is parameterised, and the parameter arrives from a verified session rather than from the form. The form only carries the typed confirmation.

There is a real cost to this choice and it belongs in the post: deleting the auth row directly skips whatever else Supabase's own API would do, and if that platform ever grows a step we are not doing, we will not find out from a type error. The mitigation is that auth.users is the root of our foreign key graph rather than a satellite of it, so the database is the thing with the complete picture.

What the DELETE takes with it

Nothing in the function says what else to delete. Nine foreign keys point at auth.users, six of them cascading:

profiles           id        cascade     the app-side account row
subscriptions      userId    cascade     plan, status, period
usageEvents        userId    cascade     everything that counted against a limit
campaigns          userId    cascade     and below them: keywords, jobs, results
outreachThreads    userId    cascade     every conversation and every email in it
supportTickets     userId    cascade     and every message on them

and three set to null:

auditLog           userId    set null    the action stays, the actor goes
emailPreferences   userId    set null    consent belongs to the address
noticeSends        sentBy    set null    who sent a notice, if an operator leaves

The cascade continues below those six: deleting a campaign row takes its keywords, its queued jobs and its result rows, and deleting a conversation takes its emails. One statement, the whole subtree.

The emailPreferences and suppressions rows are the deliberate survivors. If somebody asked us never to email them again, that request cannot quietly expire because the account that originally wrote to them was closed, so both tables are keyed by the recipient rather than by the account: email_preferences by the lowercased address, which is its primary key, and suppressions by a hash of it under a unique index on (kind, value). The account column goes to null and the refusal stands. The hash is the part I like: the list can keep saying no to an address without storing the address.

Two more things survive, and I would rather say so than let somebody find out: the shared library of creators and businesses is not account data and has no user column at all, and our own inbound mail log keeps one row per message that arrives at our support and contact addresses, with its sender and a preview. Those are our records of our own mailboxes. They are not in the account's subtree and a deletion does not touch them.

The small lie in the error message

return fail("We couldn't cancel your paid plan, so nothing was deleted. Try again, or email [email protected].");

True if Stripe throws. Not quite true in the other branch: if Stripe succeeds and the DELETE then fails, the user has an account with no Stripe customer and no subscription, which is a free account they did not ask for, and the message they see says their deletion failed rather than "half of it happened". It is recoverable, the audit row is there, and the fix is one line: delete the audit row's companion step into a transaction with the SQL, or move the Stripe call after the delete and accept the opposite risk. I would take neither until I had seen it happen once, and I am writing it down because an unlogged known-imperfect ordering is how a known issue becomes a surprise.

The operator path is the same four steps with a different confirmation

An operator can also delete an account, from the admin panel, and the sequence is identical. Only the confirmation differs:

const [account] = await db.execute<{ email: string | null }>(
  sql`select email from auth.users where id = ${userId}::uuid`,
);
if (!account) return fail("That account is already gone.");
if (!typed || typed !== (account.email ?? "").toLowerCase())
  return fail("Type the account's own email address exactly to confirm.");

An operator is working from a support thread with an ID pasted from somewhere, so the typed string has to match the address that the ID actually resolves to, not the address they believe it is. The audit row for that path is a different action with the operator's address in its metadata, so the log says which account was deleted and that a human did it, under a name that is not the account's.

And the operator's version does not redirect anywhere; it revalidates the user list, which then does not contain the row.

No soft delete, no grace period

There is no deleted_at column anywhere in the schema, no thirty-day window, no restore. The terms do not promise one, the privacy page says the opposite, and a soft delete is a promise to keep the data that is worded as a promise to destroy it.

What the app does instead is make the exit cheap to prepare for: the export is a separate button, it produces a CSV of your results, and nothing about the deletion depends on it. Take your data first, then type your email address, and the only thing left of the account is one row in a log with a hole where the account used to be.