FK ON DELETE drifted: flowsheet, rotation, reviews mismatch schema vs DB

[jakebromberg]

Problem

Five FK constraints on wxyc_schema.flowsheet, wxyc_schema.rotation, and wxyc_schema.reviews were created with ON DELETE NO ACTION in 0000_rare_prima.sql and recreated unchanged by 0016_nervous_hydra.sql. The Drizzle schema source declares them as SET NULL (flowsheet) and CASCADE (rotation, reviews), and the most-recent snapshot (meta/0082_snapshot.json) records the schema-source values. No subsequent migration patches the production DB to match.

0048_fix-fk-on-delete-set-null.sql patched the analogous drift for schedule.assigned_dj_id, schedule.assigned_dj_id2, shift_covers.cover_dj_id, and shows.primary_dj_id (see #433). The five constraints below were missed.

This is data-integrity-critical: deleting a row in library or shows raises an FK violation the application code is written to expect would be auto-handled per the schema source. Because the snapshot already encodes the desired state, drizzle-kit generate does not propose a fix migration — the drift is invisible to the normal authoring loop.

Table	Column	References	Migration says	schema.ts / snapshot says
flowsheet	show_id	shows.id	NO ACTION	SET NULL
flowsheet	album_id	library.id	NO ACTION	SET NULL
flowsheet	rotation_id	rotation.id	NO ACTION	SET NULL
rotation	album_id	library.id	NO ACTION	CASCADE
reviews	album_id	library.id	NO ACTION	CASCADE

Evidence

shared/database/src/migrations/0000_rare_prima.sql:148-194 — original constraint creation, all ON DELETE no action:

ALTER TABLE "wxyc_schema"."flowsheet" ADD CONSTRAINT "flowsheet_show_id_shows_id_fk" FOREIGN KEY ("show_id") REFERENCES "wxyc_schema"."shows"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "wxyc_schema"."flowsheet" ADD CONSTRAINT "flowsheet_album_id_library_id_fk" FOREIGN KEY ("album_id") REFERENCES "wxyc_schema"."library"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "wxyc_schema"."flowsheet" ADD CONSTRAINT "flowsheet_rotation_id_rotation_id_fk" FOREIGN KEY ("rotation_id") REFERENCES "wxyc_schema"."rotation"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "wxyc_schema"."reviews" ADD CONSTRAINT "reviews_album_id_library_id_fk" FOREIGN KEY ("album_id") REFERENCES "wxyc_schema"."library"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "wxyc_schema"."rotation" ADD CONSTRAINT "rotation_album_id_library_id_fk" FOREIGN KEY ("album_id") REFERENCES "wxyc_schema"."library"("id") ON DELETE no action ON UPDATE no action;

shared/database/src/migrations/0016_nervous_hydra.sql:34-56 — these four were dropped and recreated unchanged (still ON DELETE no action).

shared/database/src/migrations/0048_fix-fk-on-delete-set-null.sql:1-15 — patched only schedule, shift_covers, shows. Comment notes the drift came from migration 0021 for those tables; the flowsheet/rotation/reviews drift from migration 0000/0016 was not addressed.

shared/database/src/schema.ts:546-548:

show_id: integer('show_id').references(() => shows.id, { onDelete: 'set null' }),
album_id: integer('album_id').references(() => library.id, { onDelete: 'set null' }),
rotation_id: integer('rotation_id').references(() => rotation.id, { onDelete: 'set null' }),

shared/database/src/schema.ts:480:

album_id: integer('album_id').references(() => library.id, { onDelete: 'cascade' }),

shared/database/src/schema.ts:910-915:

album_id: integer('album_id')
  .references(() => library.id, { onDelete: 'cascade' })
  .notNull()
  .unique(),

shared/database/src/migrations/meta/0082_snapshot.json records the schema-source values for all five constraints ("onDelete": "set null" for the three flowsheet FKs, "onDelete": "cascade" for rotation and reviews), masking the drift from drizzle-kit generate.

Reproduction

npm run db:start
psql -h 127.0.0.1 -U wxyc -d wxyc_db -c "
  SELECT conname, confdeltype
  FROM pg_constraint
  WHERE conname IN (
    'flowsheet_show_id_shows_id_fk',
    'flowsheet_album_id_library_id_fk',
    'flowsheet_rotation_id_rotation_id_fk',
    'rotation_album_id_library_id_fk',
    'reviews_album_id_library_id_fk'
  );"

Expected after fix: confdeltype = 'n' (SET NULL) for the three flowsheet rows and 'c' (CASCADE) for rotation_album_id_library_id_fk and reviews_album_id_library_id_fk. Currently all five return 'a' (NO ACTION).

A negative reproduction:

-- Pick a library row that has flowsheet entries pointing at it.
DELETE FROM wxyc_schema.library WHERE id = <some id with flowsheet refs>;
-- Today: raises 23503 foreign_key_violation (NO ACTION).
-- After fix: flowsheet.album_id is set to NULL (SET NULL).

Acceptance criteria

• New migration drops + recreates the five FKs above with the correct ON DELETE action, matching the pattern in 0048_fix-fk-on-delete-set-null.sql.
• drizzle-kit generate against a fresh DB after the migration applies produces no further diff for these constraints.
• psql query above confirms confdeltype values match expectations on the dev DB.
• Migration follows project conventions: comment header explaining the drift origin and link back to this issue + FK constraints on schedule, shift_covers, and shows use NO ACTION instead of SET NULL #433; journal when bumped per docs/migrations.md.
• Add a verification step or test that asserts the live constraint definition matches what schema.ts declares for at least these five FKs, so a future divergence would be caught.

Related

• FK constraints on schedule, shift_covers, and shows use NO ACTION instead of SET NULL #433 — predecessor: same drift pattern fixed for schedule, shift_covers, shows.primary_dj_id via migration 0048. This issue is the missed remainder.
• docs/migrations.md — journal/snapshot lockstep rule. The drift is invisible to drizzle-kit generate because the snapshot encodes the desired state, so a snapshot-only contract is insufficient to catch DB-vs-schema divergence.

[jakebromberg]

PR opened: #1411