Twelve tables, 21 foreign keys, and not one ON DELETE CASCADE, so "delete my history" is four statements in a fixed order

The PubTrivia schema has twelve tables and 21 foreign key references. The number of them that specify onDelete is zero.

export const answersTable = pgTable('answers', {
    id: uuid('id').primaryKey().defaultRandom(),
    session_id: uuid('session_id').notNull().references(() => gameSessionsTable.id),
    question_id: uuid('question_id').notNull().references(() => questionsTable.id),
    table_id: uuid('table_id').notNull().references(() => tablesTable.id),
    participant_id: uuid('participant_id').notNull().references(() => tableParticipantsTable.id),
    // ...
})

No cascade, no set null, so every one of them is Postgres's default of NO ACTION. Delete a parent row with children and the database refuses.

I want to argue that this is the right default for an application database, and then show what it costs, because it does cost something and the cost is visible in one function.

A cascade is a delete you cannot see at the call site

ON DELETE CASCADE is attractive exactly once: when you are writing the delete. One statement, the rows go, done.

The problem is that it moves an unbounded amount of destruction into a declaration that nobody reads at the moment they do the deleting. delete from tables where id = $1 looks like one row. With cascades configured four levels down it is also every answer, every leaderboard entry and every participant from every quiz that table ever played in. The code at the call site is identical in both worlds. The blast radius is not.

Without cascades, a wrong delete is a foreign key violation, which is a loud, immediate, harmless failure. That is the property I actually want: the database refuses incorrect orders rather than enacting correct-looking ones.

So the order lives in the code

Here is what "delete all my session history" actually is, from the privacy panel in settings:

if (sessionIds.length > 0) {
    // Delete in dependency order (children before parents)
    await db.delete(leaderboardEntriesTable).where(inArray(leaderboardEntriesTable.session_id, sessionIds))
    await db.delete(answersTable).where(inArray(answersTable.session_id, sessionIds))
    await db.delete(tableParticipantsTable).where(inArray(tableParticipantsTable.session_id, sessionIds))
    await db.delete(gameSessionsTable).where(inArray(gameSessionsTable.id, sessionIds))
}

Four statements, and the order is read off the references. Answers and leaderboard entries both point at participants and at sessions; participants point at sessions. So the two leaf tables go first, participants next, and the session last. Get it wrong and the second statement throws. You find out in development, once, and then it is right forever.

Two details in that block matter more than the order.

The inArray takes session ids that were selected one query earlier, filtered to status = 'completed'. A quiz that is currently being played in a pub is not in the list, so a host tidying up their history at 8:55pm cannot delete the session the room is sitting in.

And those four statements are not in a transaction. They should be. A failure between the second and the third leaves a session with no answers but with its participants, which is not a foreign key violation and not a state any reader expects. It has not happened, the window is milliseconds, and it is still the thing in this file I would fix first. db.transaction around the block, and the four statements commit or none of them do.

Then it writes down that it happened

await auditLog(user.id, 'session_history_deleted', { session_count: count })

Which lands in a table whose comment is the most load-bearing sentence in the schema:

// Privacy Audit Log
// Immutable append-only record of privacy-relevant actions a user has taken.
// Required for GDPR accountability obligations (Art. 5(2)).

The row carries the event type, a JSONB detail blob, and the request IP taken off the first element of x-forwarded-for. The count goes in the detail, not the session ids, because recording the ids of the sessions somebody asked you to delete is not a deletion.

The export includes the log of the exports

The other button is data portability. It is nine reads: three in one Promise.all, five in a second wave that needs the session ids from the first, and one more for the questions of any custom packs. The result is a single JSON string:

const exportPayload = {
    exported_at: new Date().toISOString(),
    account: { /* id, name, email, member_since */ },
    venue: /* ... */,
    tables: venueTables,
    sessions,
    participants,
    answers,
    leaderboard_entries: leaderboard,
    custom_packs: customPacks,
    custom_questions: customQuestions,
    privacy_audit_log: auditLogs,
}

That last line is the one I like. Your data export contains the audit log of your data exports. The current export is not in it, because the audit row for it is written after the payload is built, so the first export shows an empty log and the second one shows the first. That is not a bug, it is what "a record of what we did with your data" has to mean when the record is itself your data.

The part that does not resolve cleanly

Here is the contradiction I have not solved, and I suspect anyone who has built this has met it.

user_id: text('user_id').notNull().references(() => usersTable.id),

The audit log is append-only and references the user. So deleting a user account requires deleting the audit log rows that document what was done with that account, because with no cascade the database will refuse the delete until they are gone. "Immutable" and "erase everything about me" cannot both be absolutely true.

The schema comment lists three event types: data_export, session_history_deleted, account_deleted. Only the first two are emitted by any code in the repository. The third exists in the comment and nowhere else, which is an accurate description of the state of things rather than an oversight: the self-serve buttons cover export and history deletion, and our privacy policy handles erasure as an emailed request answered within 30 days, with account data kept up to 90 days after deletion for disputes and legal obligations.

That is a process answer to a schema problem, and I would rather state it that way than pretend a DELETE cascading through twelve tables is a privacy programme. When the automated path gets built, the ordering problem is the easy half. The hard half is deciding which rows are evidence that you complied and therefore survive, and that is a question for the policy, not for the ORM.

The one table that is deleted differently

Everything above hard deletes. One table does not:

// Soft delete - set is_active to false
await db
    .update(tablesTable)
    .set({ is_active: false })
    .where(and(eq(tablesTable.id, tableId), eq(tablesTable.venue_id, venues[0].id)))

A pub rearranges its furniture and removes table 12. Every answer row from every quiz table 12 ever played in still points at it. Hard deleting would be refused by the foreign keys, and cascading would silently erase six months of results because somebody moved a bench. So the row stays, flagged inactive, the plan's table limit counts only active rows, and the session history can still print the name of a table that no longer exists in the room.

Which gives the schema two deletion philosophies, and they are both correct for the same reason. A table is referenced by history that has to outlive it, so it is soft deleted. A session is history, so when the owner asks for it to go, it goes.

The short version

Leave onDelete off by default and let the foreign keys police your ordering. Write the order once, in one function, with a comment saying it is an order. Put that function in a transaction, which I have not done yet. And decide per table whether deletion means the row disappears or the row is marked, because the answer is different for a thing that has history and a thing that is history.

Comments

[Cenk KURTOĞLU]

The no-cascade default is a genuinely good argument, and the RLS angle on the same schema is worth adding, because “delete my history” is also an authorization question, not just an ordering one. With twelve tables and no cascade, the erase path is four statements in a fixed order — which means whoever can call those four statements can delete another tenant’s history if the row scoping is not enforced at the database. Two things to pin:

• RLS DELETE policies, not only app checks. If the parent delete runs through a client/anon path, add for delete using (owner_id = auth.uid()) (or a tenant predicate) so a wrong id is a zero-row delete, not a foreign-key error after three successful child deletes.
• The append-only audit table you mention: if it is readable, an erasure request creates an exposure. Keep it non-client-readable (no anon grant, RLS deny), and delete-own only where the schema actually intends it.

The state I would want to be impossible: a half-completed erase (child rows gone, parent refused) reached by the wrong user. A two-account probe for the delete path is here if useful: github.com/cekuu35/supabase-rls-le...