When cascades do not cascade: deleting user data on libSQL

A user asks us to delete their account. Under GDPR-style expectations that means gone, not soft-deleted: their diaries, entries, tags, letters, likes, comments, devices, activity logs, and the auth record itself. Our Drizzle schema declares .onDelete("cascade") on the foreign keys that connect all of these tables to the user, so on paper a single DELETE FROM authUsers WHERE id = ? should ripple through the whole graph.

It does not. libSQL — the SQLite fork that Turso runs — only enforces foreign key constraints, cascades included, when PRAGMA foreign_keys is turned on for the connection. Turso does not turn it on by default. Every onDelete("cascade") in packages/database/src/schemas/*.ts is real TypeScript, generates a real REFERENCES ... ON DELETE CASCADE clause in the migration SQL, and does nothing when a row is actually deleted.

This post is about packages/database/src/utils/delete-user-data.ts, the function that does the cascading by hand: 26 explicit delete statements across 25 tables, in dependency order, inside one transaction, shared by two callers that would otherwise have to keep their own copies of the same list in sync.

TL;DR

• libSQL/Turso does not enable PRAGMA foreign_keys, so declared .onDelete("cascade") FKs never fire at runtime — the schema documents intent, the deletion code enforces it.
• deleteUserData(db, userId) runs 26 tx.delete(...) calls across 25 tables, child-to-parent, inside a single db.transaction(...) — a mid-sequence failure rolls back the whole thing instead of leaving a half-deleted account.
• letterReports is deleted twice: once for reports the user filed, once for reports on deliveries the user received (found via an inArray subquery over letterDeliveries), because neither FK cascade fires.
• When a child table only references another child table (not the user directly), we delete it first using an inArray(...) subquery — see documentTags before tags.
• Better Auth's authSessions and authAccounts are deleted before authUsers, both to guarantee token/credential removal and to stay correct if cascade enforcement is ever turned on.
• The function lives in packages/database, not apps/web, because two callers need it: the web self-serve deletion path and a packages/jobs cron that cannot import from apps/web.

The cascade assumption that doesn't hold on libSQL

SQLite has supported foreign key enforcement since 3.6.19, but it is opt-in per connection via PRAGMA foreign_keys = ON. libSQL inherits this behavior, and Turso's managed connections do not flip that pragma on for us. The doc comment on deleteUserData states the consequence directly:

// packages/database/src/utils/delete-user-data.ts (excerpt)
/**
 * ...
 * NOTE: libSQL/Turso does not enable PRAGMA foreign_keys by default, so
 * .onDelete("cascade") FK chains are inert at runtime. Every table must be
 * deleted explicitly. Wrapped in a transaction so a mid-sequence failure does
 * not leave the account in a half-deleted state.
 */

Nothing is wrong with the schema. diaryEntries.userId really does reference authUsers.id with onDelete("cascade"), and that reference is useful — it documents the relationship, it drives Drizzle's inferred types, and it would matter if we ever ran on a database that enforces it. It just cannot be relied on for actually removing rows today. If we had deleted only the authUsers row and called it done, every diary, tag, note, and letter the user ever created would still be sitting in the database, orphaned and unreachable from the UI but fully present in exports, backups, and analytics — exactly what an account-deletion request is supposed to prevent.

Mapping every table a user owns

Before writing the delete calls, the harder part was enumerating every table with a foreign key back to the user — directly or transitively. The doc comment on deleteUserData is effectively that map, numbered in deletion order:

// packages/database/src/utils/delete-user-data.ts (excerpt)
/**
 * Deletion order (child→parent):
 *   1.  letterReports     — reporterUserId + deliveryId cascade (inert) × 2 passes
 *   2.  letterDeliveries  — recipientUserId
 *   3.  diaryLetters      — senderUserId
 *   4.  letterPreferences — userId
 *   5.  likes             — userId
 *   6.  comments          — authorId
 *   ...
 *   21. privateProfiles   — id (= userId)
 *   22. publicProfiles    — id (= userId)
 *   23. authSessions      — userId (session tokens; cascade from authUsers is inert)
 *   24. authAccounts      — userId (OAuth tokens / password hash; cascade is inert)
 *   25. authUsers         — id (the user record itself)
 */

Counting the actual tx.delete(...) calls in the function body gives 26 statements across 25 distinct tables — one table, letterReports, is deleted twice, because it has two independent ways to belong to this user:

// packages/database/src/utils/delete-user-data.ts (excerpt)
// 1a. letterReports filed BY this user.
await tx.delete(letterReports).where(eq(letterReports.reporterUserId, userId));
// 1b. letterReports on deliveries RECEIVED by this user.
//     deliveryId→letterDeliveries FK cascade is inert, so must be explicit.
await tx
  .delete(letterReports)
  .where(
    inArray(
      letterReports.deliveryId,
      tx
        .select({ id: letterDeliveries.id })
        .from(letterDeliveries)
        .where(eq(letterDeliveries.recipientUserId, userId))
    )
  );

A moderation report row can name the user as the reporter (reporterUserId) or point at a letter delivery the user received (deliveryId, which in turn has recipientUserId). Neither cascade fires, so both passes are required, and the second one needs a subquery because letterReports itself has no recipientUserId column — it only knows about a deliveryId.

Most tables are simpler: one userId-shaped column, one eq(...) filter, one delete call — letterPreferences, likes, comments (via authorId), userActivityLog, diaryViewLogs (via viewerId), userDevices, notificationLogs, monthlyReports, emailQueue, emailLogs, emailRateLimits, and tenantMembers all fall into this bucket. The two profile tables are a variant: privateProfiles and publicProfiles use the user's own id as their primary key rather than a separate foreign-key column, so the filter is eq(privateProfiles.id, userId) instead of a userId field.

Deleting children before parents, by hand

Because nothing enforces referential integrity for us, the order of the 26 statements is not cosmetic — it is the only thing standing between "clean delete" and a foreign-key-shaped orphan. The rule is simple: leaf tables first, tables they reference next, all the way up to authUsers last.

The clearest example is documentTags, which references tags, which references the user — documentTags itself has no userId column, only a tagId:

// packages/database/src/utils/delete-user-data.ts (excerpt)
// 19. documentTags — tagId→tags cascade is inert; must delete before tags.
await tx
  .delete(documentTags)
  .where(
    inArray(
      documentTags.tagId,
      tx.select({ id: tags.id }).from(tags).where(eq(tags.userId, userId))
    )
  );
// 20. Tags.
await tx.delete(tags).where(eq(tags.userId, userId));

The inArray subquery walks one hop of the graph at delete time: "give me the ids of this user's tags, then delete every documentTags row pointing at one of them." Only after that is it safe to delete the tags rows themselves. Get the order backwards — delete tags first — and the documentTags subquery above would return nothing, because the tags it needed to match against are already gone.

The same child-before-parent discipline applies to Better Auth's tables, deleted at the very end of the sequence:

// packages/database/src/utils/delete-user-data.ts (excerpt)
// 24-25. Better Auth child rows — FK cascade is inert (PRAGMA foreign_keys OFF).
//        Must delete explicitly BEFORE authUsers to avoid FK constraint errors
//        if enforcement is ever turned on, and to guarantee token/credential removal.
await tx.delete(authSessions).where(eq(authSessions.userId, userId));
await tx.delete(authAccounts).where(eq(authAccounts.userId, userId));
// 26. Auth user record (parent row — deleted last).
await tx.delete(authUsers).where(eq(authUsers.id, userId));

authSessions holds session tokens and authAccounts holds OAuth tokens and password hashes — exactly the rows you most want gone on an account deletion request, and exactly the rows a silent cascade failure would otherwise leave behind. Deleting them before authUsers also means the code stays correct even in a hypothetical future where PRAGMA foreign_keys gets turned on: the statements would work whether or not the database enforces the constraint.

All 26 statements run inside a single await db.transaction(async (tx) => { ... }) block. Without it, a failure on, say, statement 14 of 26 would leave the first 13 tables' rows deleted and the rest intact — a half-deleted account that is worse than either fully-deleted or fully-intact. The transaction makes the whole operation all-or-nothing.

One function, two callers

deleteUserData lives in packages/database, not in apps/web where the account-deletion feature is user-facing. That placement is deliberate: two independent code paths need the exact same table list, and one of them cannot import from the other.

// packages/database/src/utils/delete-user-data.ts (excerpt)
/**
 * Shared by `apps/web/lib/db/queries/account.ts` (`accountQueries.deleteAccount`,
 * the self-serve account deletion path) and `packages/jobs` (the anonymous-user
 * GC cron, which cannot import from `apps/web`). Do not duplicate this table
 * list elsewhere — extend it here and both callers stay in sync.
 */

The web app calls it when a user deletes their own account through settings. packages/jobs calls it from a scheduled cron that garbage-collects anonymous accounts that were never claimed. Both need to remove the same 25 tables' worth of data for a given user id, and both would drift out of sync if each maintained its own copy — one gets a new table added to the schema, the other doesn't, and now one deletion path is more thorough than the other for reasons nobody remembers six months later. Putting the table list in packages/database, a package both apps/web and packages/jobs already depend on, removes that failure mode by construction: there is only one list to update.

The maintenance cost we accepted

The honest trade-off here is that this list is maintained by hand, in two places, and nothing but a code comment enforces it:

// packages/database/src/utils/delete-user-data.ts (excerpt)
/**
 * @important 新テーブル追加時はこのリストと transaction 内の削除も更新すること。
 * Also keep apps/web/lib/db/queries/account.ts's `migrateAccountData` in sync —
 * it mirrors this function's 1–20 (userId-owned data only) for the guest-link
 * migration path. See that function's doc comment for the full cross-reference.
 */

Every new table with a user-owned row has to be added to deleteUserData's transaction by hand — there is no schema-driven "delete everything that references this user" helper, because without cascade enforcement there is no reliable way to discover the full reference graph at runtime. And it's not even one list: apps/web/lib/db/queries/account.ts has a second function, migrateAccountData, used for the guest-link account-migration flow, which mirrors steps 1–20 of this same list (the userId-owned tables only, not the profile/auth rows that don't apply to a migration). A new table means updating both.

This is not a solved problem, it's an accepted cost. We could reach for a runtime schema walker that introspects Drizzle's relations and deletes transitively, but that would trade an explicit, reviewable, ordered list for a generic mechanism that has to get the child-before-parent ordering right for every future table shape — including subquery cases like documentTags and two-column cases like letterReports. For now, an @important comment and code review are what stand between adding a table and forgetting to wire up its deletion.

Closing / takeaways

• Declared FK cascades in a Drizzle schema are a statement of intent, not a runtime guarantee, on any database where PRAGMA foreign_keys isn't enforced — check this assumption explicitly rather than trusting the schema file.
• Explicit, ordered, transactional deletes are more code than a cascading DELETE, but they are auditable: every table a user can own is named once, in one file, in the order it must be removed.
• Sharing that file between two callers that can't share an import path (apps/web and packages/jobs) is what keeps both deletion paths honest — and it's why the function sits in packages/database instead of next to either caller.

For the schema side of this — how we model these relations and why cascades are declared at all — see Database schema design. For how the same tables move through production migrations without downtime, see Drizzle migrations in production.