A two party record, and a one party delete

Take a row that records a tip split at the end of a restaurant shift. One person ran the split. A different person earned the money. Both facts live in the same row, in two different columns, and that is the shape that makes account deletion much harder than it looks.

The obvious implementation deletes every row where the departing user appears in either column. The first half of that is correct. The second half reaches into records belonging to everyone they ever paid.

-- before: both sides of a two-party recordDELETE FROM public.tipout_results WHERE recipient_id = uid    OR calculated_by = uid;   -- other people's rows -- after: only what the departing user earned.-- calculated_by already had ON DELETE SET NULL,-- so the schema had answered this all along.DELETE FROM public.tipout_results WHERE recipient_id = uid;

So a head server who ran the Friday split for a year takes a year of everybody else's records with them on the way out. Those people were never asked and would never be told, and what they lost is the record of shifts they actually worked, which is the record they would reach for at tax time.

Why the clause was there at all

This is the part worth more than the bug itself, because a statement like that is rarely carelessness in the moment. It is usually there to paper over a contradiction already sitting in the schema.

The foreign key on the calculating side declares ON DELETE SET NULL. That is the schema already answering the question: the split survives, and the author reference is cleared. But if the column is also NOT NULL, the two cannot both hold, and Postgres raises an error the moment the profile goes. Deleting the rows first makes the error disappear, which is why the contradiction never shows up as a failure. It shows up as somebody else's missing data.

“A delete written to resolve an ambiguity is a sign the ambiguity is still in the schema.”

The fix, and the thing worth generalising

Deleting only the rows where the departing user is the recipient is the whole fix. Their own earnings go. Splits they ran for other people stay, with the author nulled, exactly as the foreign key always said.

There is a way to prove this on real data without being brave about it: a transaction that aborts itself. Delete a profile, assert that the other party's row survived and that the author reference was cleared, then roll the whole thing back and check the row counts are unchanged. It is the only method I trust for a destructive migration, because a staging database never has the row that surprises you.

The generalisation is about deletion in any app where records describe relationships rather than possessions. The question is not what belongs to this user. It is what would be destroyed if this row went, and whether everyone that would affect has agreed to it. A row with two user ids in it belongs to two people, and only one of them is asking to leave.

Every app eventually grows this button, usually because a store requires one. It is worth treating it as a data-modelling exercise rather than a settings row, because it is the one operation you cannot apologise your way out of afterwards. The check is quick: for every table the delete touches, count the user ids in a row. If there is more than one, you are deciding on behalf of somebody who is not in the room.