va-apple-music-url-remediation: album_metadata invalidation binds a bare JS array into ANY(), so the phase has never written a row

[jakebromberg]

Problem

jobs/va-apple-music-url-remediation/orchestrate.ts's invalidateAlbumBatch binds its id list as a bare JS array:

WHERE "album_id" = ANY(${albumIds})   // albumIds: number[]

Drizzle expands a JS array inside a sql template into a comma-separated parameter list, so Postgres receives a row constructor rather than an array:

WHERE "album_id" = ANY(($1, $2, ... $202))

ANY requires an array on the right-hand side, so the statement is rejected at parse time — SQLSTATE 42809, op ANY/ALL (array) requires array on right side. It is dataset-independent: the shipped statement could never have invalidated a row, on any page, for any input.

The album_metadata phase of the BS#2000 remediation has therefore never once succeeded.

Evidence

Production run 2026-08-06 09:33 PDT, run_id 47dfff79-44d7-4768-8a18-3dd49278dc67:

{"level":"error","step":"write_failed","message":"album_metadata invalidation failed","after_id":0,
 "error_message":"Failed query: UPDATE \"wxyc_schema\".\"album_metadata\" SET \"apple_music_url\" = NULL, ... WHERE \"album_id\" = ANY(($1, $2, ... $202)) AND \"apple_music_url\" IS NOT NULL"}

Run summary for the phase:

album_metadata: {"candidates":206,"invalidated":0,"batches":1}

206 candidates found, 0 written, the first batch aborted the phase.

The flowsheet phase of the same run completed correctly (52 rows nulled, 56 re-verified and kept) — only the album_metadata arm is broken, and it is the DJ-visible one: apps/backend/services/flowsheet.service.ts serves coalesce(album_metadata.apple_music_url, flowsheet.apple_music_url), so for a linked row the value a DJ sees is the album_metadata one. Those ~206 rows are still serving Apple Music deep-links minted by LML's pre-LML#1139 V/A-blind track matcher.

Why no test caught it

Three layers each miss it for a different reason:

1. Unit tests never parse SQL. jest.unit.config.ts maps @wxyc/database to tests/mocks/database.mock.ts, and tests/__mocks__/drizzle-orm.ts stubs the sql tag as { sql: strings, values }. Nothing serializes a statement.
2. The suite's renderSql helper is blind to parameterization. It splices bound values inline, so ANY(${[7, 8]}) and ANY(${'{7,8}'}::int[]) are indistinguishable in its output. The existing invalidateAlbumBatch SQL test asserts the SET clause and the IS NOT NULL guard and stayed green through the entire broken run. (In fact renderSql renders the defective statement as ANY() — the array collapses to an empty string — and nobody looked.)
3. The existing integration spec pins the candidate net, not the write. tests/integration/va-apple-music-url-remediation-net.spec.js covers the fold_artist_name regex superset only.

Compounding this: npm run typecheck does not cover jobs/**, and a bare number[] in a sql template is well-typed anyway — the defect is invisible to the type system by construction.

The idiom it diverged from

At least six other jobs already bind a PG array literal string with an explicit cast, and one of them names the trap:

• jobs/album-critic-reviews-etl/antijoin.ts:52-61 — comment calls it "the BS#1068/BS#1071 trap"
• jobs/flowsheet-ghost-row-sweep/orchestrate.ts:241-244
• jobs/flowsheet-metadata-backfill/orchestrate.ts:748,782,815
• jobs/flowsheet-etl/job.ts:118
• jobs/album-reviews-etl/link.ts:137
• jobs/artist-unicode-dedup/merge.ts:313 (job-local intArrayLiteral helper)

const idArrayLiteral = `{${albumIds.join(',')}}`;
... WHERE "album_id" = ANY(${idArrayLiteral}::int[])

End state

Revised during review (the original text specified the array-literal-plus-::int[] fix, which review superseded). invalidateAlbumBatch sends a VALUES-join UPDATE, mirroring applyFlowsheetBatch in the same file: each id and each observed url is bound as its own parameter, so no JS array reaches the driver at all and nothing has to hand-roll a PG array literal. Review also required a compare-and-set on the observed apple_music_url (the album arm had none, only IS NOT NULL), which needs the per-row payload the VALUES join already carries.

Regression coverage:

• an integration spec that requires the compiled dist/orchestrate.cjs and runs the real invalidateAlbumBatch against Postgres — a hand-mirrored copy of the statement would pass with the defect restored, so it must not be one;
• unit assertions on the compare-and-set, the updated_at stamp, and the absence of any bare-array bind, scoped honestly to what the mock can observe.

Files

• jobs/va-apple-music-url-remediation/orchestrate.ts (the fix, line ~378)
• tests/integration/va-apple-music-url-remediation-invalidate.spec.js (new)
• tests/unit/jobs/va-apple-music-url-remediation/orchestrate.test.ts

Constraints

• The integration runner is babel-jest with no TypeScript support, so an integration spec cannot import the TS job — the statement must be mirrored in SQL, as the sibling net spec already does and documents.
• Do not change the phase ordering (flowsheet before album_metadata), the compare-and-set UPDATE on the flowsheet arm, or the rescue-rate detector.

Acceptance criteria

• No bare array reaches the driver from invalidateAlbumBatch.
• An integration test fails against the pre-fix statement shape and passes against the post-fix one — verified in both directions: with the bare-array bind restored, 5 of 6 tests fail with 42809 op ANY/ALL (array) requires array on right side.
• The album arm carries a compare-and-set on the observed url (review addition).
• The updated_at stamp is asserted — migration 0084's trigger is flowsheet-only (review addition).
• No other statement in jobs/va-apple-music-url-remediation/ has the same defect. (Audited: ANY( appears exactly once in the job; every other binding in orchestrate.ts is a scalar. No other file in the job contains SQL.)
• The album_metadata phase can be re-run and actually invalidates its candidates.

Related

• Remediate V/A flowsheet rows carrying apple_music_url values persisted by the pre-LML#1139 V/A-blind Apple track matcher #2000 — the V/A Apple-URL remediation this job implements. Closed, but its album_metadata arm has never run successfully; this bug blocks its completion.
• va-apple-music-url-remediation: rescue-rate detector misreads LML#706 async fills as throttle, checks after the write, and samples the recovery record #2006 — a separate already-filed follow-up on the same job's rescue-rate detector. Distinct defect, distinct fix; deliberately not folded together.

[jakebromberg]

Fix in #2008.

[jakebromberg]

The code review of PR #2008 surfaced three further defects, none of which belong in this fix. Filed separately:

• va-apple-music-url-remediation: the cooperative live-DJ pause never pauses, and a probe throw kills the run's summary #2009 — same job: the cooperative live-DJ pause is a no-op (checkLive is a detector whose boolean is discarded and which is called with an extra argument), a probe throw escapes both try/catch blocks and kills the run's summary + resume cursors, and the flowsheet phase probes once per row instead of once per page. Those two call sites are also the job's two TS2554 errors — it does not compile, and npm run typecheck skips jobs/**.
• Make the bare-JS-array-in-ANY() trap unwritable: one shared array-literal helper plus a lint rule #2010 — repo-wide: the bare-JS-array-in-ANY() trap behind this issue has now fired three times (album-level-backfill: resolveAlbums array binding produces 'cannot cast type record to integer[]' on first batch #1068, album-level-backfill: BARE ANY(${array}) also splats; revert to PG-array-literal binding #1071, this one). One shared helper plus a lint rule, so the defective form stops being writable.
• Integration specs clean up only in afterAll, so a crashed run leaves fixtures that match a job's production candidate net #2011 — the integration spec added by PR fix(va-apple-music-url-remediation): bind the album_metadata id list as a PG array literal #2008 inherits a teardown-only cleanup from its donor; a crashed run leaves fixture rows that satisfy this job's own production candidate net.

Note PR #2008 ultimately removed the array bind from this job rather than repairing it — the compare-and-set added in review turned the statement into a VALUES join, so each id and url is its own parameter. This issue's end state and acceptance criteria were updated accordingly.