Re-emit liveFs SSE event after metadata enrichment UPDATE lands

[jakebromberg]

Context

Both flowsheet write paths fire LML metadata enrichment as a floating Promise after the row is inserted/upserted, via the shared fireAndForgetMetadataForRow in apps/backend/services/metadata/enrichment.service.ts:

• apps/backend/controllers/flowsheet.controller.ts (dj-site addEntry)
• apps/backend/routes/internal.route.ts — tubafrenzy webhook, gated on xmax=0 so only fresh inserts enrich (Trigger LML metadata enrichment for tubafrenzy webhook upserts #627, merged 2026-04-28)

Each path also synchronously emits a liveFs SSE refetch event before the enrichment promise resolves. Connected SSE consumers receive the refetch signal before the metadata UPDATE lands, so they pull the row with artwork_url, spotify_url, artist_bio, etc. all still null. ~1–2s later (bounded — see below) the enrichment UPDATE commits, but no second SSE fires, so consumers never see the metadata until a user-driven refetch.

Latency bound on enrichment

Worst-case wall-clock from row insert → enrichment UPDATE committed:

• LML lookup is hard-bounded by TIMEOUT_MS = 5000 in apps/backend/services/lml/lml.client.ts (AbortController). On timeout, lmlFetch throws LmlClientError(504).
• The UPDATE flowsheet WHERE id=… is bounded by DB_STATEMENT_TIMEOUT_MS=5000 and runs in well under 100ms in practice (PK lookup, single row).
• Total p100 ≈ 5.1s. Typical happy path is 200–800ms.

On the LML-timeout path, metadata.service.ts:fetchMetadata still returns a partial result (synthesized YouTube/Bandcamp/Soundcloud search URLs) and an UPDATE still happens — but artwork_url, spotify_url, apple_music_url, discogs_url, release_year, artist_bio, artist_wikipedia_url all stay null. So a "second emit on UPDATE-resolved" gated only on UPDATE success would fire even when no user-visible field changed, triggering a wasted refetch. The second emit should be gated on at least one user-visible field becoming non-null.

User-visible impact

iOS users see new tracks appear with no album art for the first ~1–2s after they begin playing. The art never appears for that entry until the user pulls to refresh, or until a new track gets added and triggers its own refetch. dj-site users on the now-playing surface see the same thing.

Affected client paths

iOS has two reader modes selectable in-app: a tubafrenzy-source mode (today's default, 100% of users) and a Backend-Service-source mode (rolling out, 0% today, scaling up). Old/stuck app versions will remain on tubafrenzy mode indefinitely, so the tubafrenzy-mode fix has to be production-grade and stay maintained — it isn't throwaway alongside tubafrenzy.

dj-site

Does not subscribe to backend's SSE. Uses RTK Query with a 60-second polling interval on the flowsheet and now-playing surfaces (src/hooks/flowsheetHooks.ts:67, :75, :243; src/widgets/NowPlaying/index.tsx:43). The adding DJ's screen renders new entries from the addEntry HTTP response (which carries no artwork yet on the free-form path), then waits up to 60s for the next poll to fetch the enriched row. A second serverEventsMgr.broadcast from fireAndForgetMetadataForRow.then() reaches no consumer here today — worth emitting as zero-cost future-proofing for an eventual dj-site SSE migration, but it isn't what closes the user-visible bug. Closing it on dj-site is out of scope for this issue (see Deferred).

iOS — tubafrenzy mode (100% today + long tail forever)

Reads through apps/backend/services/playlist-proxy.service.ts, which:

1. Subscribes to tubafrenzy's SSE at /playlists/recentStream.
2. Maintains an in-memory store of the current playlist.
3. On every created / updated event, calls enrichSinglePlaycut() which does ONE SELECT flowsheet.artwork_url WHERE … and caches the result.
4. Never re-enriches an entry once it's been processed.

The tubafrenzy created event arrives ~simultaneously with backend's INSERT (or, on the webhook path, tubafrenzy is the originator), so the proxy's read loses the race against fireAndForgetMetadataForRow's UPDATE essentially every time, caches undefined, and serves undefined until the entry rolls off the 200-entry window.

A backend liveFs SSE broadcast does not reach this proxy — they're independent SSE pipelines. But the proxy is in-process with the enrichment service, so the fix is an in-process call from fireAndForgetMetadataForRow.then() into the proxy's "re-enrich one row" path, keyed by lookup key (artist+album).

iOS — Backend-Service mode (0% today, scaling up)

Hits a backend REST endpoint that reads flowsheet directly. No proxy cache to invalidate; freshness is bounded by whatever pull cadence the iOS app uses, or unbounded if the app fetches once and caches client-side. iOS does not currently subscribe to backend liveFs SSE.

The right BS-mode fix is for iOS to subscribe to liveFs (same wire as dj-site), at which point the second-emit fix below covers BS-mode iOS for free with no new backend work. That iOS-side subscription is out of scope for this issue — see Deferred.

Proposed fix surfaces

Status update 2026-04-28: order has shifted. Surface 2 is now the load-bearing first step. Surface 1 is implemented in #643 but held in draft, gated on the library-side artwork backfill #637 — without that, denormalization writes ≈zero rows in practice (population baseline: 0.2%, see issuecomment-4338014422). Surface 3 remains deferred until BS-mode iOS rollout warrants it.

Three layers. Original ordering preserved below as written; current sequencing is 2 → #637 backfill → 1 → 3.

1. Bin-pick denormalization (highest leverage; eliminates the race on the common path on every client mode at once)

flowsheet.controller.ts:addEntry's bin-pick branch (body.album_id !== undefined) already has the album row in hand via getAlbumFromDB, but that helper at flowsheet.service.ts:601 does not select library.artwork_url — even though the column exists (migration 0045). Add artwork_url (and any other denormalizable columns LML would otherwise re-fetch) to the SELECT, stamp them onto the flowsheet row at INSERT time, and bin-picks no longer have a metadata race for those fields. Free-form entries (no album_id) still need LML and still need the second-emit fix.

This is the smallest patch with the largest in-show win, because most adds during a show are bin-picks. Precondition: library-side artwork backfill #637 (population is 0.2% today — #643 is held in draft pending that). The column exists; population is a separate question worth answering with one query before this lands.

2. Second emit at UPDATE resolution (covers dj-site + tubafrenzy-mode iOS)

Inject a callback into fireAndForgetMetadataForRow that fires after the UPDATE commits with at least one user-visible field non-null. The callsite (in app.ts or wherever routes are mounted) wires that callback to two consumers:

1. playlist-proxy.service.ts.reEnrich(lookupKey) — re-runs enrichSinglePlaycut for any in-memory entries with a matching artist+album lookup key. This is the load-bearing consumer today — it covers 100% of iOS users (tubafrenzy mode) plus the long-tail of stuck old-version installs.
2. serverEventsMgr.broadcast(Topics.liveFs, { type: 'metadata-updated', payload: { flowsheetId, fields: { artwork_url, spotify_url, ... } } }) — zero-cost future-proofing. No consumer today (dj-site polls, BS-mode iOS isn't subscribed yet); reaches dj-site / iOS once they migrate to SSE.

A single notify point drives both consumers. No CDC/WebSocket/SSE-round-trip is needed for the proxy — they're in the same process.

The richer payload (type='metadata-updated', flowsheetId, fields) lets dj-site patch one row instead of doing a full RTK Query refetch, and is strictly better than a generic refetch event for both consumers.

3. iOS BS-mode SSE subscription (deferred)

When BS-mode rolls out to non-trivial traffic, iOS should subscribe to liveFs and consume the same metadata-updated events as dj-site. Tracked separately — see Deferred.

Alternatives considered and rejected

• CDC subscription: the proxy could subscribe to onCdcEvent (in-process, exposed by shared/database/src/cdc-listener.ts) and re-enrich on flowsheet UPDATE. Works, but reaches over the enrichment service and ties the proxy to the CDC pipeline for a problem that has a more direct fix.
• Periodic polling: trivially safe, ugly, never want to ship.
• Reorder the broadcast to fire only after enrichment: eliminates the race but trades it for an availability problem. If LML is slow or down, tracks don't appear in dj-site / iOS until LML times out (5s) — strictly worse than "appears instantly with null artwork."

Acceptance criteria

• Unit test: fireAndForgetMetadataForRow invokes the resolved-callback after the UPDATE commits, with the set of fields that became non-null. Mock the callback and assert it's called once on enrichment success with non-empty fields.
• Unit test: callback NOT invoked on the LML-no-match path that yields zero user-visible fields, on the error path, or when LML returns metadata identical to what's already on the row.
• Integration test: with the playlist-proxy connected to a stub tubafrenzy SSE, a created event followed by a backend INSERT + enrichment UPDATE results in the proxy's in-memory store reflecting post-enrichment artwork within 1s of the UPDATE (i.e., the in-process notify ran).
• Smoke test on prod, tubafrenzy-mode iOS only (100% of today's iOS users): live show, new track plays, iOS app shows artwork within ≤3s of the LML-success path without manual refresh. LML-timeout path: the row appears immediately and the artwork-empty state is acceptable (no second emit, no refetch storm).
• Bin-pick denormalization (separable from above): getAlbumFromDB returns artwork_url, the bin-pick INSERT writes it to flowsheet, and a unit test confirms flowsheet.artwork_url is non-null at the moment addEntry returns when the album row has it.

Deferred (not in this issue)

• dj-site free-form-entry latency — tracked at Reduce metadata visibility latency on free-form flowsheet entries (60s polling) dj-site#476.
• iOS BS-mode liveFs subscription — tracked at Subscribe to Backend-Service liveFs SSE for metadata updates (BS-mode) wxyc-ios-64#227 (gated on BS-mode rollout).
• Linkage path race — tracked at Re-emit on LML linkage UPDATE (album_id race; sibling of #628) #644 (same shape, different field set; will reuse the resolved-callback mechanism from this issue).

Out of scope

• Structural enrichment wiring on the webhook path — done in Trigger LML metadata enrichment for tubafrenzy webhook upserts #627.
• Webhook-update re-enrichment (skipped via xmax=0 gate in Trigger LML metadata enrichment for tubafrenzy webhook upserts #627).
• Backfill of historical NULL rows — covered by [Epic] Historical metadata backfill for ~1.86M flowsheet rows #631.

Cross-repo links

• WXYC/dj-site — consumes backend liveFs SSE on the now-playing / flowsheet surfaces. Will get the richer metadata-updated payload for free; can be patched to apply a one-row update instead of a full refetch.
• WXYC/wxyc-ios-64 — tubafrenzy-mode reads through apps/backend/services/playlist-proxy.service.ts; BS-mode hits backend REST directly (no SSE today). The deferred BS-mode SSE work lands in this repo.
• apps/backend/services/playlist-proxy.service.ts — enrichSinglePlaycut is the iOS-tubafrenzy-mode fix surface.
• apps/backend/services/metadata/enrichment.service.ts — fireAndForgetMetadataForRow is where the resolved-callback gets injected.
• apps/backend/services/flowsheet.service.ts — getAlbumFromDB is the bin-pick denormalization surface.

[jakebromberg]

Revised the body with findings from a closer look at the code. Summary of what changed:

• Latency bound is concrete. LML has a hard 5000ms AbortController in lml.client.ts, and the UPDATE is bounded by DB_STATEMENT_TIMEOUT_MS=5000 (well under 100ms in practice — PK lookup). On LML timeout, fetchMetadata still returns a partial result (synthesized search URLs only) and an UPDATE still happens, leaving artwork_url and the streaming/bio fields null. So a "second emit on UPDATE success" gated only on UPDATE success would fire when nothing user-visible changed. Gating the emit on at least one user-visible field becoming non-null avoids a wasted refetch on every LML miss.

• iOS has two reader modes, selectable in-app: tubafrenzy-source (today, 100% of users) and Backend-Service-source (rolling out, 0% today). Old/stuck installs will remain on tubafrenzy indefinitely, so the proxy fix has to be production-grade and maintained — it isn't throwaway alongside tubafrenzy.

• In-process notify collapses old options A and B2. playlist-proxy.service.ts is in-process with fireAndForgetMetadataForRow, so a single resolved-callback at UPDATE-commit time can drive both serverEventsMgr.broadcast(liveFs, …) (for dj-site, and for BS-mode iOS once it subscribes) and enrichSinglePlaycut re-runs (for tubafrenzy-mode iOS). One mechanism, one test surface. CDC and polling are demoted to "rejected alternatives."

• Bin-pick denormalization is the highest-leverage patch. getAlbumFromDB doesn't select library.artwork_url even though the column exists (migration 0045). Adding it to the SELECT and stamping it onto the flowsheet row at INSERT time eliminates the race for bin-picks (the common case during an active show) on every client mode at once. Free-form entries still need the second-emit fix. Precondition: confirm library.artwork_url is actually populated in prod with one query before shipping.

• Trigger LML metadata enrichment for tubafrenzy webhook upserts #627 has merged, so the webhook-path race is live in main today — not pending.

• AC reframed to scope the smoke test to tubafrenzy-mode iOS (only mode with users) and defer the BS-mode liveFs subscription to its own follow-up.

[jakebromberg]

Precondition check on fix surface 1 (bin-pick denormalization): currently blocked on populating library.artwork_url.

Production count as of 2026-04-28:

SELECT count(*) AS total,
       count(*) FILTER (WHERE artwork_url IS NOT NULL) AS with_art,
       round(count(*) FILTER (WHERE artwork_url IS NOT NULL)::numeric / nullif(count(*),0) * 100, 1) AS pct
FROM wxyc_schema.library;

Returns: total=64163, with_art=155, pct=0.2.

Column exists (migration 0045) and getAlbumFromDB could trivially be extended to select it, but only 0.2% of library rows have artwork to denormalize. Two paths:

• Re-scope surface 1 to "wire the SELECT now, denormalize zero rows today, future-proof against a library-side artwork backfill" — small commit, no immediate user-visible win.
• Re-prioritize: surface 2 (second-emit) is the actual highest-leverage path until library.artwork_url gets populated. Land surface 2 first, then surface 1 lands cheaply once the library backfill ships.

Worth filing the library-side artwork backfill as its own follow-up since it's a precondition for surface 1's value.

[jakebromberg]

Correction to the body, after looking at dj-site:

dj-site does not subscribe to backend's SSE. It uses RTK Query with a 60-second polling interval on the flowsheet/now-playing surfaces (src/hooks/flowsheetHooks.ts:67, :75, :243; src/widgets/NowPlaying/index.tsx:43). Grep finds no EventSource / live-fs-topic / text/event-stream consumer anywhere in dj-site/src.

What this means for the design:

• The user-visible delay on dj-site is bounded by the 60s poll interval, not 1–2s. The adding DJ's screen renders the new entry from the addEntry HTTP response (without artwork), then sees artwork appear at the next poll cycle.
• A second serverEventsMgr.broadcast(liveFs, …) from fireAndForgetMetadataForRow.then() reaches no consumer today. dj-site polls; tubafrenzy-mode iOS uses the playlist-proxy (in-process); BS-mode iOS hits REST without SSE.
• The broadcast is still worth emitting (zero-cost future-proofing for whenever dj-site or iOS migrates to SSE), but it isn't what closes the user-visible bug today.
• The actually load-bearing parts of the fix are:
  1. Bin-pick denormalization — eliminates the race for the common in-show path on every client mode at once, including dj-site (because addEntry's response then carries artwork_url).
  2. In-process notify into the playlist-proxy — covers 100% of today's iOS users (tubafrenzy mode).
  3. dj-site free-form entries still wait up to 60s. Options for that path are separate from this issue: drop the polling interval, wire a client-side targeted refetch ~2s after addEntry, or migrate dj-site onto liveFs SSE. None are required by Re-emit liveFs SSE event after metadata enrichment UPDATE lands #628's scope; flagging for a follow-up.

Will revise the body to reflect this and to demote the "Option A reaches dj-site" framing.