Two migrations 52 seconds apart: the account deletion that could never work

Homies, the bilingual React Native app I build privately on the side, lets you delete your account from inside the app. Apple requires that of any app with sign-up (App Store Review Guideline 5.1.1(v)). From the start of the app's git history until the fix, it could not succeed for anyone who had ever paid a shared expense. The request came back as a 500. As far as I know, nobody ever hit it.

A foreign key said set the payer to null, and a trigger stamped 52 seconds later said the payer can never change. The fix is one condition. Its test was red before anything ever ran it, and nothing noticed.

A foreign key and a trigger that cannot both hold

The first one decides what happens to a roommate's history when they leave:

ALTER TABLE public.expenses
  DROP CONSTRAINT IF EXISTS
    expenses_paid_by_fkey,
  ADD CONSTRAINT expenses_paid_by_fkey
    FOREIGN KEY (paid_by)
    REFERENCES auth.users(id)
    ON DELETE SET NULL;

The expense stays, and the app shows its payer as an ex-roommate. The second one makes sure nobody can rewrite who paid:

IF OLD.paid_by IS DISTINCT FROM
   NEW.paid_by THEN
  RAISE EXCEPTION
    'cannot change paid_by on an existing expense'
    USING ERRCODE = '42501';
END IF;

It runs BEFORE UPDATE on every expense row. The two files carry version stamps 52 seconds apart, and both arrive in the commit that imported the app into git two days later.

Each is right alone. Together they cannot both hold, because of how Postgres carries out ON DELETE SET NULL: as an update of the referencing row, and update triggers fire on it. Deleting a user set paid_by to null on their expenses, the trigger saw paid_by change, and it raised. The delete failed and rolled back.

What the user would have got

This part is a reading of the delete-account function. I did not run it.

The function has two steps. The first calls a database procedure that, for every household the user owns, hands ownership to the oldest co-member, or deletes the household if they are its only member, and then removes their membership. That call commits on its own. Between the steps the function deletes the user's RevenueCat customer, best effort. The second step deletes the auth user, and that is the step that failed. The function logs the detail and returns a generic "Failed to delete account" with status 500.

So a person who had paid an expense would have been left with an account that still existed, outside their household, and a retry would have failed the same way.

How it was found

The day the disposable Postgres harness from the local stack post reached main, an agent session auditing account deletion ran the two steps on it: the prepare step succeeded, and delete from auth.users failed with the trigger's message. The fix was committed eleven minutes after the issue was filed, also by a session.

The fix

One condition:

IF OLD.paid_by IS DISTINCT FROM
   NEW.paid_by
   AND NOT (OLD.paid_by IS NOT NULL
            AND NEW.paid_by IS NULL)
THEN
  RAISE EXCEPTION
    'cannot change paid_by on an existing expense'
    USING ERRCODE = '42501';
END IF;

A payer may become null, which is the shape of the foreign key's own cascade. Any other change is still rejected. The migration's header walks every other ON DELETE SET NULL column in the schema and finds no second lock trigger: the settlement and chore-history columns are protected by revoked privileges, which the cascade does not pass through.

I applied the migration to production.

The contract

The fix shipped with a SQL contract: user A pays an expense, user B settles part of the debt, A completes a chore, A's account is deleted. Case 1 says the delete succeeds. Case 2 says the expense, the settlement and the chore row survive with their actor columns null. Case 3 is a negative control on the trigger. It runs in one transaction that is rolled back, and its runner refuses any URL that is not a local disposable database.

The contract's header says it fails on the unpatched schema. I checked that by running it, in a scratch copy of the repo, against the harness replayed to two points:

• through the migration before the fix: it fails with cannot change paid_by on an existing expense;
• through the fix: all three cases pass.

What guards it, and what does not

What holds: the fix itself, which passes all three cases on the harness, and the other ON DELETE SET NULL columns, which revoked privileges protect.

What does not:

• The contract was red from its first run. Nothing invoked it until six days after the fix; no script, workflow or document named its runner before then. It had gone red the day before, when a migration applied that evening converted every household to a newer model. The contract builds its household with a direct insert, and after that migration its next step, inserting an expense as an authenticated user, is refused by row-level security. It dies in its fixture before any case runs. The fix is untouched. The test stopped being able to reach it.
• The gate that now exists skips it by name. scripts/test-sql-contracts.sh runs every SQL contract against disposable databases and lists nine runners as known red, this one first. The CI job runs when database files change, and the log I read printed pass=10 fail=0 and nine "not run" lines. When I wrote this, the job had never completed on main, and a repair of the contract sat on an unmerged branch.
• The negative control tests a different transition than it names. Case 3 says it rejects a swap to a different payer. It runs after the delete, when paid_by is already null, so what it exercises is null to a payer; its own success message ends "paid_by stayed NULL". A swap from one payer to another is never tried. I added that case to the scratch copy and the trigger rejected it.
• Nothing exercises the real path. No Maestro flow deletes an account: the delete-account flow is tagged read-only, types the wrong confirmation text, checks that the sheet is still there, and cancels. And the contract runs delete from auth.users against the harness's vendored auth schema, where production goes through the auth server's admin API. The cascade is the same, and the caller is not.

The rule I take from it: a BEFORE UPDATE lock on a column that a foreign key can SET NULL has to allow the null transition, because Postgres carries out the cascade as an update. And a negative control has to exercise the transition it names.