Mirror tubafrenzy ROTATION_RELEASE.DISCOGS_RELEASE_ID into rotation for dj-site parity

[jakebromberg]

Problem

The dj-site rotation entry-mode picker (the dropdown of tracks that appears when a DJ selects a rotation release from the Heavy/Medium/Light/Singles/New bins) is empty in prod, even though the same dropdown is the user-visible deliverable of the cross-cache-identity work. The on-air DJ sees no tracks, has to retype the title, and the experience does not match the classic tubafrenzy site (https://www.wxyc.info/playlists) which has populated this dropdown via RotationReleaseTracksServlet for years.

Diagnosis

apps/backend/services/library.service.ts:341 (getDiscogsReleaseIdByRotationId, used by /library/rotation/:rotation_id/tracks from BS#940) currently composes the release ID by joining rotation to library_identity via album_id. That path returns null for ~100% of prod rotation rows because library_identity.discogs_release_id is structurally NULL for every row jobs/library-identity-consumer (BS#802) has written.

The relevant comment from jobs/library-identity-consumer/writer.ts:79-94:

Release/recording-level columns: not in the LML contract today. Per the BS#800 pivot, these are LML's to compose if/when it surfaces them.

So the column was always going to be NULL until BS#801 extends the LML contract with release-level resolution. The picker can't wait — the data exists, just one upstream hop away.

Solution

Tubafrenzy already carries the canonical Discogs release ID on every rotation row via RotationReleaseServlet.applyDiscogsReleaseIdParam (the paste-URL prefill flow). The column is ROTATION_RELEASE.DISCOGS_RELEASE_ID (bigint in MySQL).

The existing jobs/rotation-etl/ bridge already SELECTs and upserts rotation_release rows; mirroring this one extra column needs nine lines of code, zero new endpoints, and zero new dependencies. After mirror, the read path consults the local column first, falling back to library_identity for post-turndown rows that will be sourced via dj-site directly once BS#801 lands.

Scope

Single PR, six files, ~110 net lines (most of the line count is the auto-generated 0077_snapshot.json):

• Migration 0077_rotation-discogs-release-id.sql — ALTER TABLE rotation ADD COLUMN discogs_release_id integer (nullable, DDL-only, instant on PG 11+)
• shared/database/src/schema.ts — add column to Drizzle table
• jobs/rotation-etl/fetch-legacy.ts — extend SELECT, parser, and LegacyRotationRow type
• jobs/rotation-etl/job.ts — thread through the incremental UPSERT (both insert and on-conflict update)
• apps/backend/services/library.service.ts — simplify getDiscogsReleaseIdByRotationId to read direct + fallback
• tests/unit/jobs/rotation-etl/fetch-legacy.test.ts + tests/unit/services/library.service.test.ts — pin parse + precedence

Post-deploy

Run rotation-etl once with LEGACY_SINCE_MS=0 to backfill all ~21,563 existing rotation rows. The modification-tracked incremental sync would otherwise only catch rows touched since the last lastRun timestamp.

Out of scope (filed separately)

• Consolidate getDiscogsReleaseIdBy* bridge functions into a single resolveDiscogsReleaseId service #981 — eventually consolidate getDiscogsReleaseIdBy* siblings into a single resolveDiscogsReleaseId service. The simplified function added here is the third sibling and a candidate for that consolidation.

Related

• Add /library/rotation/:rotation_id/tracks — tracks dropdown for rotation entry mode #940 — /library/rotation/:id/tracks endpoint (the read consumer that was returning [] in prod)
• [§4 step 2 post-pivot] Consume LML POST /api/v1/identity/bulk-resolve-libraries #802 — library-identity-consumer (the upstream that doesn't fill release-level columns)
• plans: cross-cache-identity architecture pivot decision record #800 — cross-cache-identity pivot decision record
• [Followup] Generalize compilation_track_artist → library_track (cross-cache-identity §3.2 step 2) #801 — open epic to extend LML for release-level resolution (would let post-turndown dj-site rows skip tubafrenzy entirely)

Acceptance

• Migration 0077 applied to prod
• rotation-etl post-deploy backfill run; spot-check 5 rotation rows have populated discogs_release_id
• /library/rotation/:rotation_id/tracks returns a non-empty tracks array for a Heavy rotation row in prod
• dj-site rotation entry-mode picker shows tracks for a Heavy release (verified live on dj.wxyc.org)