[Followup] Generalize compilation_track_artist → library_track (cross-cache-identity §3.2 step 2)

[jakebromberg]

Summary

Step 2 of the two-step plan in compilation-track-schema-design.md. Generalize compilation_track_artist → library_track so the track model covers all releases (not just V/A), supports a release-identity correction UI for compilations, and accommodates the librarian-artist link as a first-class concept.

Blocked by #792 (step 1 — library_track_identity_source sibling). Do not start until #792 ships and the sibling identity table is exercised in production.

Aligned with the cross-cache-identity architecture pivot (2026-05-09). Per BS#800, Backend doesn't read LML's PG; identity flows in via POST /api/v1/identity/bulk-resolve-libraries. The "non-V/A track data" deliverable below uses bulk-resolve-libraries' kind: single_artist track listings rather than a new direct-write LML script. The legacy match_compilations.py / merge_cta.py scripts retire under pivot Phase 4-5; the per-track matcher work moves into LML (library-metadata-lookup#271).

Why later, not now

Today's compilation_track_artist covers only V/A library rows (~6,300 of 64,676), was machine-populated by an offline LML backfill (not librarian-curated), and uses a unique key on (library_id, artist_name, track_title) — wrong for the future where track_position is the right key but is currently nullable in some rows.

The librarian directive (2026-05-09) opens onto a UI plan we can't reach without restructuring:

1. Tracklisting view for compilations.
2. Search artists + tracks across all releases, not just comps.
3. Release-identity correction UI for compilations — at the release level, not the track level. Two failure modes: (a) LML matched the comp to the wrong upstream release; (b) the correct release isn't captured in any upstream system at all.

Goals 1 and partial 2 work for V/A on current data once #792 lands. Goal 2 for non-V/A and goal 3 require this ticket.

Scope (tracking-level; sub-issues to be carved out closer to execution)

Schema

• Rename compilation_track_artist → library_track, OR keep the V/A table and add a parallel library_track for non-V/A. Decide in the design phase.
• Add track_artist_id integer REFERENCES wxyc_schema.artists(id) (nullable) for internal canonicalization — the "library-artist link" question from the design doc.
• Add track_artist_link_confidence real CHECK (>= 0 AND <= 1) and track_artist_link_method text for the link itself.
• Tighten track_position to NOT NULL where data permits; backfill or null-segregate the rest before adding the constraint.
• Update the unique index. Current index is (library_id, artist_name, track_title). The forward-compatible key — and the one [E2] Compilations / Various Artists treatment under library_identity (§3.2 design decision) #792's library_track_identity_source sibling uses — is (library_id, track_position).
• Refresh the CDC trigger (currently cdc_compilation_track_artist from migration 0046) to cover the renamed/replacement table.

Population — non-V/A track data

• Under the pivot, non-V/A per-track data flows through bulk-resolve-libraries: extend the response shape so kind: single_artist results carry tracks too (when the caller asks for them). LML matches per-track; Backend writes the response into library_track. The pre-pivot framing of "new direct-write LML script mirroring match_compilations.py" is obsolete.
• Cardinality estimate: 64K library rows × N tracks ≈ 500K–1M new track rows.
• Backend-side: a one-shot backfill job (cf. jobs/library-canonical-entity-backfill/ or jobs/library-artwork-url-backfill/ patterns) that pages through library rows, calls bulk-resolve-libraries, and UPSERTs the tracks block into library_track (and identity into library_track_identity_source).

library-etl reconciliation

• Today's .onConflictDoNothing() keyed on (library_id, artist_name, track_title) (Backend-Service/jobs/library-etl/job.ts:735-743) creates orphan rows when upstream artist_name is corrected. Fine today (no upstream correction UI), real bug once corrections land.
• Either grow real upsert semantics keyed on (library_id, track_position) (requires the NOT NULL change above), or detect upstream deletions and propagate.
• Decide cascade semantics for library_track_identity_source when the underlying track is deleted vs. when an artist_name is corrected (true delete → cascade is right; correction → identity may still be valid, depends on whether the correction changed the artist).

Reader updates

• Backend-Service/apps/backend/services/suggest.service.ts:30-100 reads compilation_track_artist as a secondary autocomplete source. Update to read library_track.
• Catalog views in dj-site that display tracklists (don't exist yet; add as part of this work or as a downstream UI ticket).
• Any flowsheet → library track resolution that needs the per-track artist link.

Release-identity correction UI

The librarian-facing correction is at the release level, not the track level. When LML matches a comp to a Discogs/MB release, the tracklist is downstream of that release pointer; if the pointer is wrong, the entire tracklist is wrong. Track-level corrections aren't the primary need.

Two failure modes the UI must handle:

1. Wrong upstream release matched. LML picked Discogs#X for this comp, but Discogs#Y (or a different MBID, or "none of these") is the correct one. The librarian re-points the comp's release-identity pointer; the tracklist + per-track external identity gets re-fetched and replaced wholesale (sidecar rows from [E2] Compilations / Various Artists treatment under library_identity (§3.2 design decision) #792 deleted and re-inserted against the new release).
2. No upstream release exists. The comp exists in WXYC's catalog but isn't captured in Discogs / MB / any upstream system. The librarian needs to declare this — a positive "looked, doesn't exist, won't exist" state — distinct from "LML hasn't tried yet" and from "LML tried and failed." For these cases the tracklist would have to be librarian-entered (or stay empty); they'd be the only librarian-curated rows in library_track.

Schema implications:

• library_identity (the parent library_id's row) needs a state for case 2. Likely a library_identity_source row with method = 'manual_no_upstream' or similar — captures the librarian's positive declaration so the matcher won't keep retrying.
• library_track rows for case-2 comps would have track_artist_link_method = 'librarian' (vs. 'lml_backfill' for the default machine-populated case). The dual-author concern that was framed away under [E2] Compilations / Various Artists treatment under library_identity (§3.2 design decision) #792 returns here, but only for a small minority of rows.
• library_track_identity_source (the step-1 sidecar) stays empty for case-2 comps — there's no upstream identity to source.
• The CDC trigger and sync interactions need to recognize "release pointer changed" as a wholesale-replace event for the dependent track + identity rows.

Open question: lives in tubafrenzy/webapps/wxycdb (extending the existing JSP catalog) or a new dj-site / BS-served surface? Either path has different sync implications. Decide before implementing.

Tubafrenzy schema

• Open question: does step 2 require schema change in tubafrenzy MySQL, or BS-side-only?
  • BS-only is cleaner but means BS becomes the source of truth for track data, inverting today's tubafrenzy → BS direction.
  • Tubafrenzy-also keeps the source-of-truth direction but means tubafrenzy needs the same schema and the librarian UI gets the new fields.

Hard constraints (carried forward from #792)

The librarian invariants still apply — V/A filing conventions stay invariant from the librarian's perspective. See #792's "Hard constraint" section and the design doc. Specifically:

• library.artist_name / library.album_artist rendering for V/A rows is unchanged.
• Z-prefixed call letters file as today.
• Catalog navigation surfaces bucket-specific shelves, not a conflated __VARIOUS__ page.

The step 1 sidecar (library_track_identity_source) keys on (library_id, track_position, source); step 2 must preserve that keying or migrate the sidecar in lockstep.

End state

• library_track (renamed or parallel) covers all library rows.
• Per-track artist link to artists, nullable + fuzzy + with confidence/method.
• Non-V/A track data populated from a new LML pull.
• library-etl handles upstream corrections without orphaning rows.
• Release-identity correction UI for compilations exists somewhere (TBD which repo).
• Step 1 sidecar continues to work unchanged or is migrated cleanly.
• Plan §3.2 in the parent canonicalization doc updated to reflect the post-step-2 model.

Acceptance (tracking-level)

Per-deliverable acceptance lives with each sub-issue when this gets broken down. At the tracking-issue level:

• All deliverables above either shipped or explicitly carved out with follow-ups.
• No regression in librarian-facing V/A behavior (cf. project_librarian_va_invariant).
• Track search across all releases works in dj-site or whichever consumer is the target.
• The librarian-correction UI handles the wrong-release-match scenario the design doc names.

Open questions (must resolve before breaking into sub-issues)

1. Where does the librarian-correction UI live — tubafrenzy wxycdb or a new BS-served / dj-site surface?
2. Population strategy for non-V/A track data — fold into library-etl or run as a separate one-shot backfill?
3. library-etl upsert reconciliation: (library_id, track_position)-keyed upsert, or upstream-deletion propagation, or both?
4. Tubafrenzy MySQL schema impact: also-update or BS-only?
5. Rename vs parallel-table: structural choice for the schema migration.

Related

• Design doc (full analysis): WXYC/wiki/plans/compilation-track-schema-design.md.
• Blocked by: [E2] Compilations / Various Artists treatment under library_identity (§3.2 design decision) #792 (step 1 — library_track_identity_source sibling).
• Parent plan: library-hook-canonicalization.md §3.2.
• Source-of-truth schema: tubafrenzy/docs/database-schema.md (LIBRARY_CODE / LIBRARY_RELEASE / COMPILATION_TRACK_ARTIST).
• Current BS code touched:
  • Backend-Service/jobs/library-etl/job.ts:701-748 — etl.
  • Backend-Service/apps/backend/services/suggest.service.ts:30-100 — reader.
  • Backend-Service/shared/database/src/migrations/0037_etl-schema-sync.sql — existing schema.
  • Backend-Service/shared/database/src/migrations/0046_cdc_notify_triggers.sql — CDC trigger.
• LML scripts that wrote the original V/A data:
  • library-metadata-lookup/scripts/match_compilations.py
  • library-metadata-lookup/scripts/merge_cta.py

[jakebromberg]

Pivot 2026-05-09 — verify scope

The cross-cache-identity project pivoted on 2026-05-09 (WXYC/Backend-Service#800). Under the pivot, V/A handling moved into LML's bulk-resolve response as a kind: compilation | various_artists discriminator, with track-level identity populated by LML's matcher cascade. The new schema in wxyc-shared#103 defines that discriminator; LML implementation in library-metadata-lookup#272 (and library-metadata-lookup#271 for the V/A-specific cascade).

Worth verifying whether this ticket's BS-side schema generalization (compilation_track_artist → library_track) is still required as a Backend mirror of LML's compilation verdict, or whether the LML-side handling makes the BS-side change redundant. Cross-link with library-metadata-lookup#271 either way.

[jakebromberg]

Decision pending (2026-05-19 triage) — surfacing the 2026-05-09 verify-scope question that hasn't been acted on for 10 days:

The pivot comment poses: "Worth verifying whether this ticket's BS-side schema generalization (compilation_track_artist → library_track) is still required as a Backend mirror of LML's compilation verdict, or whether the LML-side handling makes the BS-side change redundant."

Per LML#272 (closed 2026-05-10) and the §3.2.5 cross-source agreement rules, LML now emits compilation verdict + per-track identity in the bulk-resolve response. Backend reads that response and writes via the substrate from #790.

Specific question for the next reader: does Backend still need its own library_track table independent of what's in LML's response, or does library_identity + the bulk-resolve payload cover the use case? If covered, close. If not, the body should call out the BS-side use case that LML can't serve.

[jakebromberg]

Design direction (2026-05-19 owner sign-off): performance is a primary constraint, not a refactor afterthought.

Recording this so the eventual sub-issue decomposition (the 5 open questions in the body) inherits the constraint:

Read-path performance is load-bearing

library_track is the substrate that Catalog Track Search v2 (Project #30) reads. The dj-site E6 picker (dj-site#501/#502) and the classic SearchForm track-aware help text both hit this surface. Latency budget for catalog search is interactive-feel (sub-300 ms p95); a 500K–1M row table without the right indexes won't hit it.

Implications for the schema design phase:

• Unique key (library_id, track_position) — already in the body. Required for upsert correctness AND for the most common read pattern (release → tracks).
• Track-title search index — partial-prefix queries ("viscose poise" → Confield) need either a tsvector + GIN, or a trigram (pg_trgm) index on track_title. Pick one and commit to it; don't ship without either. LML's discogs-cache uses trigram (LML#269 just proved 97× speedup with trigram); BS's flowsheet uses tsvector. Pick the one whose query plan matches the dj-site picker's actual query shape.
• Per-track artist link track_artist_id — a B-tree index for the "all tracks by this artist across all releases" query, since that's goal 2 in the body. Without it, dj-site falls back to seq scans.
• CDC trigger overhead — the existing CDC pipeline doubles flowsheet write cost (~30%, per memory). On a 500K–1M row table doing bulk inserts during the LML population pass, that overhead is significant. Either disable CDC during the one-shot backfill (and re-snapshot after), or stage the load through a different write path.
• Partial indexes for the common filters — e.g., WHERE track_artist_link_method = 'librarian' is going to be a small minority of rows; if reads filter on it, partial index. Don't over-index — each one taxes inserts.

Write-path performance during the population pass

500K–1M LML round-trips at LML's current cold-cache rates (9–17 s per uncached lookup post-2026-05-13, per BS#873) is multi-day even with parallelism. Implications:

• Don't run the population pass until LML Epic A (LML#338) lands. A cold-cache pass at today's rate doesn't fit a sensible wall-clock budget.
• Pre-warm via bulk-resolve-libraries with the existing library_id set first. Discogs/MB/WD lookups will hit local trigram indexes and resolve fast. Only the unmatchable tail falls back to slow paths.
• Backfill needs to share the partition pattern with the existing one-shot jobs (jobs/library-canonical-entity-backfill/, jobs/flowsheet-metadata-backfill/). That's exactly the extractable surface area being narrowed in BS#651 (shared/job-utils/partition.ts). Land BS#651 before this issue's backfill ships.

Don't design for the librarian-correction UI until the data is in

The body lists release-identity correction UI as one of the four deliverables. That's a real product driver, but it touches the slowest moving piece (librarian workflow). Decouple: data + read-path indexes ship first, correction UI ships when there's data to correct against. Don't gate this issue's progress on the UI design.

Suggested sub-issue carve

When this gets broken down, the performance-shaped boundary suggests:

1. D-perf-1: Schema migration + indexing strategy (the structural choices, no data yet)
2. D-perf-2: One-shot population via bulk-resolve-libraries (waits on LML#338 Epic A)
3. D-perf-3: library-etl reconciliation (upsert + delete semantics)
4. D-perf-4: Reader updates (suggest.service.ts + dj-site picker integration)
5. D-correct-ui: Librarian release-identity correction UI (parallel track, no blocking on the perf path)

Open questions 1 (UI location) and 4 (tubafrenzy schema impact) can defer to D-correct-ui.

[jakebromberg]

Verify question resolved (2026-05-20) — answering the 2026-05-09 "is this still required" pivot question now that I've actually checked BS#802's state.

BS#802 shipped 2026-05-11 via PR #807, confirming the post-pivot data flow: Backend's library-identity-consumer UPSERTs LML's bulk-resolve verdicts into library_identity + library_identity_source atomically per library_id. Backend persists LML's verdict — it doesn't pass through.

This issue's compilation_track_artist → library_track generalization is the same pattern but for tracks. Backend will persist per-library track-level data from LML's response, with track_artist_id / track_artist_link_confidence / track_artist_link_method mirroring the identity columns. The four deliverables in the body all survive intact:

1. Schema — rename + add per-track artist link + confidence/method + key change (per the 2026-05-19 design-for-perf direction).
2. Population — non-V/A track data via bulk-resolve-libraries' kind: single_artist track-list extension. The pattern is settled; LML#272's response shape already carries the data.
3. library-etl reconciliation — upsert + delete semantics; same shape as the identity-consumer's UPSERT.
4. Release-identity correction UI — independent track, can land in parallel with the data work.

Net: this issue is actionable now. Recommended next move per the 2026-05-19 design-for-perf comment: carve into the 5 sub-issues (D-perf-1 schema + indexing, D-perf-2 population, D-perf-3 etl reconciliation, D-perf-4 readers, D-correct-ui in parallel) and start with D-perf-1.