The biggest delete in our app is one line of raw SQL against a table our ORM does not manage

Nakodo deletes an account like this:

await db.execute(sql`delete from auth.users where id = ${user.id}`);

One statement, written as raw SQL because the table it names is not in our schema file and not in any of our 32 migrations. It belongs to Supabase Auth. Everything the account owns goes with it, and what goes and what stays is decided entirely by 24 foreign keys declared elsewhere.

I pulled all 24 out of the latest migration snapshot to see whether the rules were consistent. They are, more than I expected, so this is a census rather than a confession.

Twenty-four keys, two rules, no third rule

cascade    14
set null   10
restrict    0
no action   0  (on delete)
no action  24  (on update)

Every delete rule in the schema is one of two choices, and no primary key in this app is ever updated, so the update rule is uniform by default rather than by decision.

The split is not arbitrary. Reading down the list, the rule follows from one question: is the child row a part of the parent, or a record of something that happened?

Parts cascade (eight of the 14):

Child	Parent	Rule
profiles.id	users	cascade
subscriptions.user_id	users	cascade
campaigns.user_id	users	cascade
keywords.campaign_id	campaigns	cascade
campaign_channels.campaign_id	campaigns	cascade
outreach_emails.thread_id	threads	cascade
support_messages.ticket_id	tickets	cascade
videos.channel_id	channels	cascade

A profile with no user is nothing. A message with no ticket is nothing. There is no version of the product where you would want to keep it.

Records of events set null (all ten of them):

Child	Parent	Rule
audit_log.user_id	users	set null
email_preferences.user_id	users	set null
notice_sends.sent_by	users	set null
suppressions.thread_id	threads	set null
outreach_threads.campaign_id	campaigns	set null
usage_events.campaign_id	campaigns	set null
support_tickets.campaign_id	campaigns	set null
jobs.run_id	runs	set null
searches.run_id	runs	set null
channels.place_id	places	set null

Each of those is a thing that happened, and it still happened after the row that caused it is gone. An audit entry saying an account was deleted must survive the account being deleted. An opt-out must survive the conversation it came from, because its whole job is to stop another conversation starting later.

That last one is published, not just implemented. The privacy notice says, of a deleted campaign, that we keep "a short record with your account" of who was written to and "no second try for 6 months after they say no". The set null on suppressions.thread_id is that sentence as a schema constraint. The same page's "deleting your account removes your profile, campaigns, conversations and plan straight away" is the six cascades in the first table. You can read our delete rules off a page written for people who do not know what a foreign key is, which is the most useful thing a privacy notice has ever done for me as a developer.

A check worth running on your own schema

A set null rule on a NOT NULL column is a delete that fails at runtime, every time, for the whole statement. Postgres lets you declare it, because the constraint and the column are separate objects. I checked all ten:

audit_log.user_id            nullable
email_preferences.user_id    nullable
notice_sends.sent_by         nullable
suppressions.thread_id       nullable
outreach_threads.campaign_id nullable
usage_events.campaign_id     nullable
support_tickets.campaign_id  nullable
jobs.run_id                  nullable
searches.run_id              nullable
channels.place_id            nullable

Ten for ten. That is one of those checks that is boring when it passes and is a production incident when it does not, and it takes one pass over a snapshot file to answer.

The reverse is also worth noting. Two of the 14 cascades are on nullable columns, which means an orphan is a legal state rather than an impossible one: a campaign can exist with no owner at all (it is how a campaign created from the command line waits to be claimed), and a queued job can exist with no campaign.

Nine of them point outside the migrations

auth.users is Supabase's table. Nine foreign keys reference it, six cascading and three nulling, and none of the 32 migrations in this repository created it.

That has a few consequences I had to get used to:

• the largest fan-out in the schema is triggered by code I did not write, whenever a user is deleted through the auth provider's dashboard or API, not only by my own action;
• so the cascade rules are the entire protection against orphans, because there is no application code in the path at all;
• and the audit row has to be written before the delete, which is exactly what the action does, because after it there is no user to attribute it to.

The order in the account deletion is the part I would keep if I rewrote the feature. Cancel the paid plan and remove the saved card at the payment provider first, and bail out with a message if that fails, because the one unrecoverable mistake here is deleting the account and leaving a subscription that still bills. Then write the audit row. Then the one line of SQL. Then sign out and redirect, which is why the public home page has a notice that reads a ?deleted=1 query parameter.

Four tables with no foreign key at all

Out of 25 tables, four have no key in either direction: an inbound mail staging table, a log of place imports, and two tables that track outside services' daily usage. They are islands by design, each one written by one job and read by one other, and the two usage tables deliberately have no owner because a provider's limit is not a user's property.

One more absence is worth naming because it was a deliberate trade rather than an oversight. The table holding creators and businesses uses a single text column as its primary key, with a prefix deciding what kind of ID it is, and the conversation table stores that ID without a foreign key to it. Reference data gets purged on a retention schedule while conversations are kept, so a key would either block the purge or delete the history. The honest cost is that the ID in a conversation row can point at nothing, and nothing in the database stops it.

Postgres does not index the referencing side, and it shows

A foreign key creates an index on the parent (it needs the unique key it points at) and nothing at all on the child. Deleting a parent therefore scans every child table that references it, unless you indexed the referencing column yourself.

I checked which of the 24 referencing columns are the leading column of some index, unique constraint or primary key:

COVERED  22
BARE      2

The two bare ones are email_preferences.user_id and notice_sends.sent_by, and both are reached by the account delete above. So the single most consequential statement in the application triggers a sequential scan on two tables, every time.

In practice both tables are tiny and both are small on purpose: email preferences are keyed by address rather than by user, because consent belongs to the address, and the notice table holds one row per account that has been sent a particular notice. The 22 that are covered are covered for a reason that has nothing to do with deletes: they are the columns the app filters by, so the index was there before any delete needed it.

Which is the general lesson. Index coverage of foreign keys is nearly free if your access patterns run along the same edges as your ownership graph, and the two gaps in this schema are precisely the two tables that are never queried by user.

The whole delete surface, counted

Besides that one raw statement, the application contains six db.delete(...) calls, all of them retention work rather than user intent: finished jobs and runs older than their keep window, purged reference data, a usage event being refunded, an inbound email removed after it is parsed. Campaign deletion is one transaction that removes conversations that never sent anything, then the campaign, and lets the rules above decide the rest.

Twenty-four keys, two rules, six deletes and one line of SQL for the one that matters. If I were starting again the only thing I would change is the two missing indexes, and I would still not add a restrict anywhere: a rule that refuses a delete moves the decision to the moment a person is trying to leave, which is the worst possible time to discover you have an opinion about their data.

What the app does with all of this, in words rather than DDL, is on how it works, and the retention windows that drive most of the deletes are on the privacy notice and in the terms.