Five foreign keys declare an ON DELETE action the database does not have (drizzle-kit cannot see it)

[jakebromberg]

Problem

Five foreign keys declare a referential action in shared/database/src/schema.ts that the database does not have. The deployed constraints are all plain NO ACTION. This is invisible to drizzle-kit generate — the snapshots agree with schema.ts, so no catch-up migration is ever emitted, and the drift will not surface on its own.

Verified against both the CI database built from the full migration chain (a ci:db-init from empty) and production on 2026-08-21, which rules out a local-only artifact. pg_constraint.confdeltype = 'a' in both:

constraint	schema.ts declares	database has
show_djs_show_id_shows_id_fk	onDelete: 'cascade'	NO ACTION
artist_library_crossreference_artist_id_artists_id_fk	onDelete: 'cascade'	NO ACTION
genre_artist_crossreference_artist_id_artists_id_fk	onDelete: 'cascade'	NO ACTION
genre_artist_crossreference_genre_id_genres_id_fk	onDelete: 'cascade'	NO ACTION
schedule_specialty_id_specialty_shows_id_fk	onDelete: 'set null'	NO ACTION

The audit that produced the table (run against either database):

SELECT c.conname, c.confdeltype, cl.relname AS child, fcl.relname AS parent
FROM pg_constraint c
JOIN pg_class cl ON cl.oid = c.conrelid
JOIN pg_class fcl ON fcl.oid = c.confrelid
JOIN pg_namespace n ON n.oid = cl.relnamespace
WHERE c.contype = 'f' AND c.confdeltype = 'a' AND n.nspname IN ('wxyc_schema', 'public')
ORDER BY cl.relname, c.conname;

Ten other FKs also come back NO ACTION from that query (library.*, bins.album_id, flowsheet.label_id, library_identity*, shift_covers.schedule_id, shows.specialty_id). Those are correct — schema.ts declares no action for them either. Only the five above disagree.

How it was found

Writing the teardown for tests/integration/flowsheet-open-shows.spec.js in #2238. DELETE FROM shows raised

PostgresError: update or delete on table "shows" violates foreign key constraint
"show_djs_show_id_shows_id_fk" on table "show_djs"

against rows that schema.ts says should have cascaded. The spec now deletes show_djs explicitly, with a comment pointing here.

Why it matters

A declared-but-absent ON DELETE is worse than no declaration, because callers are written against the declaration:

• Deletes fail that look like they should succeed. Any DELETE FROM shows with live show_djs rows raises instead of cascading. That is now a live path: POST /flowsheet/shows/:id/force-end (Operator close for an abandoned show: GET /flowsheet/open-shows + force-end (tubafrenzy parity, dies 8/31) #2235) does not delete shows, but the operator tooling around abandoned shows is the area where someone will reach for one.
• The artist crossreference FKs are the sharper edge. jobs/artist-unicode-dedup (Unicode-form artist duplication at the catalog write boundary (artistIdFromName) #1897) repoints every artist_id FK explicitly before deleting a duplicate artist, so it is safe because it does not rely on the cascade. Anything that does rely on it — a future artist-merge path, a DELETE /library/:id extension — will fail at runtime against a constraint the schema promised would clean up after it.
• A reader of schema.ts is being told something false. That file is the reference every job and service is written from.

Desired end state

schema.ts and the database agree, in one direction or the other, for all five. Either is defensible per constraint — the point is that the two stop disagreeing silently.

Suggested approach

Decide direction per constraint before writing anything. They are not one decision:

• show_djs.show_id — cascade is almost certainly right. A show_djs row has no meaning without its show.
• genre_artist_crossreference.*, artist_library_crossreference.artist_id — cascade is right for a junction table, but deleting an artist row is not currently a routine operation, and artist-unicode-dedup already handles its own repointing. Worth confirming nothing depends on the absence of the cascade as a safety brake before adding one.
• schedule.specialty_id — SET NULL on a deleted specialty show. Check whether schedule readers tolerate a NULL there.

Then either:

1. Align the database — a migration issuing ALTER TABLE … DROP CONSTRAINT … ; ALTER TABLE … ADD CONSTRAINT … ON DELETE … per constraint. Read docs/migrations.md first; a constraint-adding migration wants a precondition guard, and scripts/validate-migrations.mjs check 8 warns without one. Note the re-add takes a lock and validates existing rows.
2. Align schema.ts — drop the onDelete clauses so the file describes what is actually deployed, and fix any caller that was relying on the promise.

A guard is worth more than the fix. The reason this sat undetected is that drizzle-kit does not diff referential actions, so nothing in CI can see it. A scripts/-level check comparing pg_constraint.confdeltype against the onDelete clauses parsed out of schema.ts — run in the migrate-dryrun job, which already has a migrated database in hand — would catch the next one at PR time. Same shape as scripts/check-auth-tables-doc.mjs. Consider splitting that into its own follow-up if the per-constraint decisions above take longer than the guard.

Acceptance criteria

• A direction is chosen and recorded for each of the five constraints
• schema.ts and a freshly-migrated database agree on confdeltype for all five
• Production verified to match after deploy (the audit query above returns none of the five)
• Any caller relying on a cascade that is being removed — rather than added — is fixed in the same PR
• Ideally: a CI check that fails on the next referential-action drift
• tests/integration/flowsheet-open-shows.spec.js's teardown comment updated or removed if show_djs gains its cascade

Related

• feat(flowsheet): operator close for an abandoned show (#2235) #2238 — the PR that surfaced it; its integration teardown carries the workaround
• Operator close for an abandoned show: GET /flowsheet/open-shows + force-end (tubafrenzy parity, dies 8/31) #2235 / [Epic] Go Live must not silently co-host — takeover prompt, sign-out close, operator close #2232 — the operator-close work that led here
• Unicode-form artist duplication at the catalog write boundary (artistIdFromName) #1897 — artist-unicode-dedup, which repoints artist FKs by hand and is therefore unaffected
• Enforce CLAUDE.md auth-tables list against Drizzle schema in CI #1573 — check-auth-tables-doc.mjs, the precedent for a schema-vs-documentation CI guard

[jakebromberg]

Direction decided, and it is the opposite of what this issue's "Suggested approach" leans toward. Recording the reasoning, because the repo will now hold two different resolutions of the same question.

Decision: make schema.ts match the database — drop the five declarations

Not: add the five ON DELETE actions to the database.

Why. Nothing in production deletes these parent rows. Audited across apps/ and shared/ — no Drizzle .delete(shows | artists | genres | specialty_shows) and no raw DELETE FROM against any of them, outside tests.

So the two directions are not symmetric:

• De-declaring is a zero-runtime-change edit. It ends the falsehood immediately and forces any future delete path to make its cleanup explicit, in the PR that actually needs it.
• Adding the cascades arms five destructive cascades that no current caller exercises, across tables holding decades of flowsheet and library history, to fix a discrepancy that has never caused a production failure. That cuts directly against the standing data-safety rule in the org CLAUDE.md.

The show_djs.show_id case is the one with genuinely clear semantics — a show_djs row is meaningless without its show — and if a show-delete path is ever built, that PR should add the cascade deliberately. Today it would be arming a gun for a caller that does not exist.

This diverges from BS#1126, deliberately

tests/integration/fk-on-delete-flowsheet-rotation-reviews.spec.js documents the identical bug class, found in 2026 via migrations 0094/0097:

Five FK constraints drifted between the Drizzle schema source ... and the actual migration history ... Because the latest snapshot already records the schema-source values, drizzle-kit generate produced no fix migration — the drift was invisible to the normal authoring loop.

That resolution went the other way: it patched the database to match schema.ts. This one goes the other way for the reason above. The inconsistency is intentional; it should not be "corrected" later without re-reading this comment.

The guard must be general, not another allowlist

The BS#1126 spec hard-codes its own five constraints. A different five drifted underneath it anyway — which is empirical evidence that a hand-listed regression spec does not prevent recurrence of this class.

So: a single test that enumerates every FK in the CI database and compares each pg_constraint.confdeltype against what schema.ts declares for that constraint. Any future divergence fails, whether or not anyone thought to add it to a list.

This matters because the normal authoring loop cannot see the drift: the Drizzle snapshot agrees with schema.ts, so drizzle-kit generate emits nothing. The database is the only witness, and the CI database built from the full migration chain is where to ask it.

Acceptance criteria (revised)

• The five onDelete declarations removed from shared/database/src/schema.ts; no migration needed, since the database already has NO ACTION.
• A general FK guard asserting every constraint's confdeltype against its schema.ts declaration.
• The guard fails if either side of any FK is changed without the other.
• The existing BS#1126 spec left intact — it also exercises real delete behaviour, which the general guard does not replace.
• This divergence from BS#1126 noted in the code, near the de-declared constraints.

[jakebromberg]

PR open: #2246

Implements the direction decided above — schema.ts corrected to match the database, no migration — plus the general pg_constraint.confdeltype guard rather than another allowlist. #1126's spec is left intact, and each de-declared FK in schema.ts carries a comment recording the deliberate divergence.