Deleting one user row empties 21 of our 29 tables, and the delete statement names one of them

The whole of CogniPrep sits on one Drizzle schema file
of 1,439 lines. Counted from the newest migration snapshot rather than by eye,
that is 29 tables, 311 columns, 64 indexes, 15 unique constraints, 36 CHECK
constraints and 30 foreign keys.

Two numbers in that list are zero, and they are the interesting ones:

$ grep -c "varchar(" lib/db/schema.ts
0
$ grep -rl "CREATE TYPE" lib/db/migrations | wc -l
0

52 migrations, no CREATE TYPE in any of them. Here is what the schema uses
instead, and what the absence of a declared type costs when somebody asks us to
delete their account.

311 columns in eight types

The histogram, read out of the snapshot JSON:

type	columns
text	177
timestamp	74
integer	30
jsonb	11
boolean	9
numeric(5, 2)	7
text[]	2
date	1

All 29 primary keys are text(). There is no uuid column in the database, and
yet the ids in it are UUIDs: crypto.randomUUID() is called at 39 sites in
lib/, and the result is stored as text. That is a decision you only notice
later, and I will come back to the one place it leaves a cast.

The seven numeric(5, 2) columns are scores and percentiles. The single date
column is application_events.occurs_on, which is a date because an interview
on the 3rd is on the 3rd in whatever zone the candidate reads it in, and a
timestamp would have made that a question rather than a fact.

No enum types, 36 CHECK constraints

Every closed set of values in the schema is text plus a constraint:

check('users_role_valid', sql`${table.role} IN ('user','admin')`),
check('users_plan_tier_valid', sql`${table.plan_tier} IN ('free','premium')`),
check('commission_status_valid', sql`${table.status} IN ('accrued','reversed','paid')`),
check('nurture_codes_discount_pct_range', sql`${table.discount_percent} BETWEEN 1 AND 100`),

The value lists are not written twice. They live in the module that owns the
rules, as a readonly tuple with a type derived from it, and the constraint is
the database restating the same list so a stale client cannot write a value the
code no longer understands. The comment at the top of one of those modules puts
the contract plainly:

 * The id lists below are mirrored by CHECK constraints on the applications and
 * application_events tables in lib/db/schema.ts. Adding a value means changing
 * both, and the constraint is what stops a stale client writing a value the
 * code no longer understands.

The trade is the familiar one. Adding a value is an ALTER TABLE ... DROP
CONSTRAINT plus an ADD CONSTRAINT in one migration, instead of
ALTER TYPE ... ADD VALUE. What you get back is that a constraint does not have
to be a value list, and 15 of ours are not: 13 are ranges or non-negativity
checks, and two compare one column on the row against another, like this one on
the tracker's dates, where only a custom entry is allowed to carry a title.

("application_events"."kind" = 'custom') = ("application_events"."title" IS NOT NULL)

21 of the 36, then, are the closed value lists an enum type would have covered.
The other 15 are things CREATE TYPE could not have expressed at all.

21 of the 30 foreign keys point at one table

26 of the 30 specify onDelete: 'cascade' and 4 specify set null. 21 of them
reference users_table: 20 cascading, and one deliberately not.

Walking the cascade graph out of the snapshot gives the number that matters:

tables emptied by deleting one user: 21
direct children: 19
second level: support_tickets -> support_messages, applications -> application_events
untouched: affiliate_codes, affiliate_invites, affiliate_payouts,
           interview_questions, population_stats, school_discount_domains,
           stripe_webhook_events

Seven tables survive a user deletion, and each one is a different reason. Three
are not about a user at all (the question pool, the population statistics, the
registered-domain list). One is a webhook ledger keyed by the payment
provider's event id. The remaining three are the affiliate programme's own
records, and they survive because of the single set null on the user link:

owner_user_id: text().references(() => usersTable.id, { onDelete: 'set null' }),

An affiliate who closes their account should not take their code, their past
payouts and the commission rows that balance them out of the books. The row
stays, the person stops being attached to it, and the accounting still adds up.
Every other set null in the schema is the same shape: a payout batch that can
be undone without destroying the commissions that pointed at it.

The delete statement names one table

The deletion routes do not walk any of this. They call the auth provider, and a
trigger does the rest:

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;
$$;

There is the cast the text primary keys bought us. auth.users.id is a real
uuid, ours is text, there is no foreign key between the two schemas, and so
the only link in the system from an auth record to the application's data graph
is this AFTER DELETE trigger plus ::text. It also cannot be a Drizzle
migration, because Drizzle does not manage objects on the auth schema, so it
is a hand-applied file kept in the repository as the source of truth for a
restore.

Two referencing columns with no index

Postgres needs an index on the referencing side to make a cascade cheap. The
parent's primary key index does nothing for it: when you delete a parent row,
the server has to find the children, and without an index on the child's
referencing column that is a sequential scan per deleted row.

A pass over all 30 foreign keys against the 64 indexes and 15 unique
constraints found two columns with no index leading on them:

public.referrals.code_id            -> referral_codes   cascade
public.affiliate_invites.used_code_id -> affiliate_codes set null

referrals carries five indexes. Two of its three foreign keys are covered,
one as the leading column of a composite and one by a unique index. The third,
the link to the code that produced the referral, is not covered by any of them,
so deleting a referral code scans referrals. The invites case is the milder
one, because set null is an update rather than a delete, but it is the same
missing index.

Neither is a production incident today: both tables are small, and referral
codes are not deleted in normal operation. It is still the kind of thing that
is invisible until the first time somebody prunes a parent table and wonders
why one statement took a minute. Two one-line index additions, and the reason
to publish it is that the audit is three lines of script over a snapshot file
you already have in the repository: every foreignKeys entry, every indexes
entry, and a set difference.

See it

The public face of all this is the retention list on
cogniprep.app/privacy. Every line there that
ends "or immediately when you delete your account" is one of the 21 cascading
tables, written in English:

curl -s https://cogniprep.app/privacy \
  | sed 's/<[^>]*>/ /g' \
  | grep -o "immediately when you delete[^.]*" | head

Section 12 names the tracked applications and their dates, which are the two
tables at the second level of the cascade, and section 8 names the onboarding
answers with their own two-year clock. If you want to see what the graph looks
like from the inside, the account that owns it is free to make at
cogniprep.app/signup, and the delete button in
settings is the one statement above.