§4 step 2 sub-PR 2.1 — S2 LML entity.identity reader + cross-ref pre-index

[jakebromberg]

Add the LML/discogs-cache PG leg as the second source for the library-identity-backfill (cross-cache-identity §4 step 2). Triggers cross-source agreement detection: library rows that have BOTH a discogs_release from S1 AND e.g. a discogs_artist + mb_artist from S2 cross-ref via Wikidata's discogs_mapping → main row method='cross_source_agreement', confidence=0.95.

Plan: plans/library-hook-canonicalization/section-4-step-2-backfill-plan.md sub-PR 2.1.

Scope

• New module jobs/library-identity-backfill/sources/lml-entity-identity.ts. Connects to DATABASE_URL_DISCOGS (read-only). Bulk-reads entity.identity joined to a Backend-side mapping (library.artist_id → artists.artist_name).
• Each entity.identity row produces up to 6 per-source rows in library_identity_source (one per non-NULL external ID column: discogs_artist, mb_artist, wikidata, spotify, apple_music, bandcamp).
• §5.2 cross-ref pre-index: TS in-memory Map<discogs_id, Set<mb_id|wikidata_qid>> built at job start from wikidata-cache + musicbrainz-cache. Per-row cross-ref check is O(1).
• §3.2.5 cross_ref_present(s1, s2) helper. Fires when discogs_release_id (from S1) + discogs_artist_id (from S2) cross-reference, OR when discogs_artist_id + mb_artist_mbid + wikidata_qid cross-reference per Wikidata's discogs_mapping.
• Granularity fanout: S2 is artist-level; the new schema is library-row-level. Resolution: a library row with artist_id=N and any release_id receives the same six artist-level external IDs as every other library row with the same artist. Document the fanout in the resolver — it's correct because identity (Spotify ID, MB UUID) is artist-level, not release-level.

Confidence (locked, conservative interim)

method='alias_match', confidence=0.85 for all six external-ID fields, tagged notes='backfill:S2,trust=lml-aggregate'. Rationale: see plan sub-PR 2.1 details + §5.1.1. Above the 0.70 audit threshold but below name_variation's 0.90+ floor — the right "trust the aggregate, don't promote to exact" tier.

Upside path: once LML adds per-row method+confidence to entity.identity (see WXYC/library-metadata-lookup#270), a follow-up sub-PR re-derives S2 confidences per-row and supersedes the blanket 0.85 via §3.2.2 Rule 4 (existing < new → supersede).

Acceptance

• DRY_RUN=true reports per-source row counts as JSON without writing.
• Real run populates 6 library_identity_source rows per entity.identity row × library_id fanout.
• Cross-source agreement triggers on rows that S1 already wrote: main row method becomes cross_source_agreement, confidence=0.95, agreement_sources populated. Verified by SELECTing a known-corroborated library_id post-run.
• Unit tests for the cross-ref pre-index (build + lookup) against fixture data.
• Integration test (or SQL contract test, per Restore end-to-end TS-modules integration tests for library-identity-backfill #791) for the per-source UPSERT shape.
• §3.2.3 gate-check query reports lower truly_unresolved_rows than after 2.0.

Estimated LOC

~400 (reader + cross-ref function + tests) per plan §4 sub-PR 2.1.

Refs

• Parent epic: [Epic] E2 — Canonical identity record (library_identity) + LML write contract #663
• Predecessor: §4 step 2 sub-PR 2.0 — library-identity-backfill skeleton + S1 source #789 (sub-PR 2.0, merged via feat(jobs): library-identity-backfill skeleton + S1 source (§4 step 2 sub-PR 2.0) #790)
• Pre-index design: plan §5.2
• LML upstream: Add per-row method + confidence columns to entity.identity library-metadata-lookup#270 (per-row method+confidence)

[jakebromberg]

Design correction during 2.1 prep — read confidence from entity.reconciliation_log

While investigating LML's entity.identity schema for the cross-ref pre-index design, found that per-row (source, external_id, method, confidence) already exists in entity.reconciliation_log (library-metadata-lookup/scripts/entity_resolution/store.py:109-112). The "locked at alias_match 0.85" interim that this issue was originally scoped around is not needed.

Revised confidence approach

For each entity.identity row that S2 reads, the reader joins to entity.reconciliation_log and selects the latest log entry per (identity_id, source) tuple:

SELECT DISTINCT ON (l.identity_id, l.source)
       l.identity_id, l.source, l.external_id, l.method, l.confidence
FROM entity.reconciliation_log l
ORDER BY l.identity_id, l.source, l.created_at DESC

Each result row maps to one library_identity_source row with the actual (method, confidence) from the log — exact_match 1.0 where LML had a clean direct hit, name_variation / alias_match / member_group for the various heuristic paths, all with their real confidence.

Knock-on effects

• Add per-row method + confidence columns to entity.identity library-metadata-lookup#270 — closed. The data exists; that ticket was scoped against a wrong premise.
• The "locked at 0.85" tag (notes='backfill:S2,trust=lml-aggregate') is gone. Notes becomes 'backfill:S2' matching the S1 pattern.
• Cross-source agreement detection (§3.2.5 / plan §5.2) is unchanged — it still fires on cross-ref between S1's discogs_release and S2's discogs_artist + wikidata + mb_artist.
• The backfill is no longer "interim awaiting LML upgrade"; it's the steady-state contract from the start.
• One row in entity.identity may have NULL external_id columns where reconciliation never succeeded for that source. The reader filters those out — only (library_id, source) pairs with a real external_id get a library_identity_source row.
• If a source legitimately has no log entries (rare but possible — e.g., the column was hand-edited), the reader falls back to method='alias_match', confidence=0.85 for that one source/row only. This narrow fallback replaces the blanket interim.

I'll update the implementation plan accordingly during 2.1 PR prep.

[jakebromberg]

Superseded by the architecture pivot in #800.

Backend will no longer implement source-leg backfill readers (S1–S4) directly. Identity resolution moves entirely to LML, with Backend consuming a single bulk-resolve endpoint (POST /api/v1/identity/bulk-resolve-libraries).

Sub-PR 2.0 (S1, merged in #790) and the in-flight S2 reader (closed #797) become reference implementations rather than production code paths. A new BS-side ticket for the post-pivot consumer will be filed in Wave 2 of the rollout.

See #800 for the decision record and migration plan.