Skip to content

Enable SQLite foreign_keys pragma and clarify migration story

jedmund requested to merge jedmund/db-foreign-keys into main

Fourth of six cleanup MRs. The schema declares `ON DELETE CASCADE` foreign keys but they have been silently inert because SQLite does not enforce them without a per-connection pragma.

What changes

  • `src/lib/server/db/index.ts` — add `_sqlite.pragma('foreign_keys = ON')` next to the existing WAL pragma, with a comment explaining the SQLite default and the consequence.
  • `src/lib/server/services/sortedIndexer.ts` — the prune block had an explicit `delete sortedAlbumFiles` workaround for the missing FK enforcement. With the pragma on, cascade fires correctly; the workaround is now dead code and is removed. Logic simplifies from two DELETE statements to one.
  • `drizzle.config.ts` — comment noting the `out` directory is unused at runtime (migrations are hand-written in `db/index.ts`).
  • `CLAUDE.md` — replace the one-line stack mention of schema with an explicit "schema and migrations" section. Drizzle migrations are NOT wired up; `schema.ts` and `db/index.ts` must be kept in sync manually.

Pre-check on production data

Ran `PRAGMA foreign_key_check` against a copy of the production DB on the NUC before enabling enforcement. Zero violations across all three FK relationships:

  • `album_files.album_id → albums.id` (9118 child rows)
  • `recommendations.album_id → albums.id` (190 child rows)
  • `sorted_album_files.sorted_album_id → sorted_albums.id` (31011 child rows)

No orphan-row cleanup script is needed.

Behavior

After deploy, deletes that previously orphaned child rows will now cascade as the schema intended. `pnpm run build` passes. Smoke test plan after merge: deploy to NUC, exercise an unsorted album delete and confirm the corresponding `album_files` rows disappear automatically.

Builds on MR !47 (merged), !48 (merged), !49 (merged).

Merge request reports