GET /library/info reports plays from the dead library.plays column: album detail shows "0 plays" for a release search reports 135

[jakebromberg]

Problem

Kate Bailey, on the station Slack (2026-09-08):

Ok another small thing -- in the card catalog, it always seems to say 0 plays on the release popup window, e.g. dj.wxyc.org/dashboard/album/56696 (this release has 135 plays in search results)

The album detail modal reports 0 plays for every release. The same release in the search results reports its real count. This is BS#1489's defect, unfixed on a second endpoint.

Root cause

library.plays is a dead physical column that nothing maintains — it is 0 for every row. The real per-album count lives in the album_plays materialized view. BS#1489 fixed GET /library/query by joining the MV and sourcing the value from it:

const albumPlaysJoin = sql`LEFT JOIN ${album_plays} ON ${album_plays.album_id} = ${library_artist_view.id}`;
const playsColumn = sql`COALESCE(${album_plays.plays}, 0)`;

Backend-Service/apps/backend/services/library-search.service.ts

Lines 94 to 106 in 8e6175e

	// BS#1489: `library_artist_view.plays` projects the physical `library.plays`
	// column, which nothing maintains — it is 0 for every row, so `sort=plays`
	// ordered a constant (silently a no-op, falling through to the secondary
	// tiebreak). The real per-album play count lives in the `album_plays` MV
	// (migration 0059) — the same live `COUNT(*)` over flowsheet track entries the
	// catalog export and the tsvector ranker read. Scope a LEFT JOIN to it into the
	// search query and source `plays` from it, rather than redefining the view (the
	// view is read by several other code paths; this keeps the blast radius on the
	// one surface with the bug). The join is 1:1 (unique index on
	// `album_plays.album_id`), so it never changes the result set or `COUNT(*)`;
	// COALESCE 0 covers never-played albums, which have no MV row.
	const albumPlaysJoin = sql`LEFT JOIN ${album_plays} ON ${album_plays.album_id} = ${library_artist_view.id}`;
	const playsColumn = sql`COALESCE(${album_plays.plays}, 0)`;

getAlbumFromDB — the query behind GET /library/info — was not part of that change and still projects the dead column. Note it feeds five endpoints, not one: GET /library/info (library.controller.ts:1645), PATCH /library/:id (:1896, :1938), markMissing (:1996) and markFound (:2006). All five ship plays: 0 today, and all five are fixed by the same edit:

plays: library.plays,

Backend-Service/apps/backend/services/library.service.ts

Line 3192 in 8e6175e

plays: library.plays,

Route:

Backend-Service/apps/backend/routes/library.route.ts

Line 201 in 8e6175e

library_route.get('/info', requirePermissions({ catalog: ['read'] }), libraryController.getAlbum);

LIBRARY_VIEW_PROJECTION carries the same plays: library.plays — at library.service.ts:1763, not 3202 as this ticket first said. Leave it alone for this fix (investigated; see the comment below). It is a hand-written base-table mirror, not the view definition, and changing it means widening a satisfies Record<keyof …, Column> constraint that rejects an SQL expression, making the album_plays join unconditional, adding it to LIBRARY_VIEW_JOINS_RAW (which has none), and re-verifying the CTA arm's prepended joins. That is a different and larger change than the reported bug, and BS#1489 made exactly this argument in-repo (library-search.service.ts:100-103).

Desired end state

GET /library/info?album_id=56696 reports the same play count the search results report for the same release. No client change: dj-site already renders album.plays ?? 0 and will show the real number once the endpoint sends one.

Suggested approach

Add the album_plays LEFT JOIN to getAlbumFromDB and project COALESCE(album_plays.plays, 0) in place of library.plays. The join is on album_plays.album_id = library.id and is a left join on a unique key, so it cannot change the row count. getAlbumByLegacyId calls straight through to getAlbumFromDB, so the legacy-keyed permalink front door is fixed by the same edit.

Check the MV's refresh cadence (album-plays-refresh.service.ts) and state it in the PR — the detail modal will be as fresh as the search results and no fresher, which is fine, but it should be a known property rather than a surprise.

Constraints

• Widening the value is not a contract change, and the spec is already on the fix's side: AlbumDetail.plays is declared at wxyc-shared/api.yaml:2591-2593 as "Station play count for this release" — optional, non-nullable, no bounds. Shipping 0 is the implementation violating that description; shipping the real count brings it into compliance. There is no runtime validator in either package to trip.
• Consumers are safe. Only dj-site reads plays off a detail response — AlbumCard.tsx:226, {album.plays ?? 0} plays, no zero-branch and no sentinel. iOS and Android have zero readers (wxyc-ios-64 declares the field on generated models nothing references; the other three repos have no matches). The zero-special-casing that does exist (Results/Result.tsx:168,173, MobileResult.tsx:51) is on the search path and is not fed by /library/info.
• Read-only change. No migration, no write path.

Acceptance criteria

• GET /library/info?album_id=<id> and GET /library/query return the same plays for the same release.
• Verified against release 56696 specifically (135 plays per Kate's report).
• GET /library/info?legacy_release_id=<id> fixed by the same change (it routes through getAlbumFromDB).
• Test covering a release with a non-zero count and one with none (COALESCE → 0, not null).
• Decision recorded on whether LIBRARY_VIEW_PROJECTION.plays is changed or deliberately left. Investigated: leave it, track separately. See above.
• AlbumCard.tsx:226 hardcodes the plural, so a genuine count of 1 renders "1 plays" — unreachable today, reachable the moment this ships. Fix it in dj-site alongside, or file it; do not let the first correct value be a grammar bug.
• Note in the PR that this also repairs a pre-existing dj-site defect: patchSearchCaches.ts:137-146 splices the album-detail row wholesale into the search cache (:187-189), so classifying rotation from the modal currently inserts a plays: 0 row into a search page — rendering — at a wrong sort slot. The existing.plays ?? updated.plays guard at patchSearchResult.ts:59 does not cover that path.
• Confirm the refresh job is actually running in prod. It is — album-plays-refresh last ran 2026-09-08 18:14 PDT, 32 min before the check. The modal will be at most ~1h stale and exactly as stale as the search results it is compared against. The design flaw stands (first refresh at +1 interval, not at boot, and nothing reads cronjob_runs) but is not currently mis-serving anyone — worth a separate hardening ticket, not a blocker here.

Related

• Three read paths still source the dead library.plays column — including an artist autocomplete that has never ranked #2401 — the three remaining surfaces reading the dead column, including the LIBRARY_VIEW_PROJECTION cascade this ticket deliberately defers.
• Album detail renders "1 plays" — hardcoded plural, unreachable only until BS#2397 lands dj-site#1400 — the "1 plays" plural this fix makes reachable. Land together or land that first.
• GET /library/query?sort=plays sorts by the dead library.plays column (all zeros) #1489 — the same defect on GET /library/query?sort=plays; the fix there is the template for this one.
• Catalog export ships plays = 0; popularity ranking needs attribution-corrected play data #1486 — not the same defect, and not a blocker. The catalog-export leg was already fixed by bec7db3c ("source catalog-export plays from album_plays MV"); Catalog export ships plays = 0; popularity ranking needs attribution-corrected play data #1486 remains open as the Phase-2 attribution epic (the free-text tail and master collapse). If its body still says the export ships plays = 0, that text is stale.

[jakebromberg]

Investigation notes

Body updated with the conclusions. Recording the rest so it isn't re-derived.

What album_plays counts (migrations/0059_album-plays-materialized-view.sql:25-31, never redefined):

SELECT "album_id", count(*)::int AS "plays"
FROM "wxyc_schema"."flowsheet"
WHERE "entry_type" = 'track' AND "album_id" IS NOT NULL
GROUP BY "album_id";

Four properties worth knowing before a DJ asks:

• Library-linked only. ~43% of music plays are free-text with a null album_id and are invisible (catalog-export.service.ts:145-147).
• Per pressing, not per logical album — two library rows for the same record split the count.
• It counts tracks, not spins. flowsheet is one row per track, so five cuts off one LP in one show is 5. This is the number most likely to look wrong to someone who remembers playing a record twice.
• No date floor — retro-linked historical plays count, including ones predating the catalog add date. Known inflation source: ETL ghost rows neither ETL deletes (jobs/flowsheet-ghost-row-sweep/README.md; the prod sweep, BS#1083, is still open).

None of that blocks the fix — library_artist_view.id = library.id = album_plays.album_id, so joining the MV in getAlbumFromDB reproduces the search number exactly. 135 and 135, not 135 and 134. It just means the number is "linked track-plays per pressing", and if we ever want it to mean something else that is a separate conversation.

Refresh mechanics. REFRESH MATERIALIZED VIEW CONCURRENTLY (album-plays-refresh.service.ts:96), enabled by the unique index at 0059:31, so readers are never blocked. 1-hour timer (:47), started in-process from the API container (app.ts:199). It is the only REFRESH MATERIALIZED VIEW in the repo. Worst-case staleness is not one hour — it is unbounded, because the first refresh fires at +1 interval rather than at boot, so a container that restarts more often than hourly never refreshes at all. cronjob_runs is written but nothing reads it. Hence the new checklist item.

Doc drift found: docs/catalog-search/README.md:115 says the service "runs at backend startup". It does not.

album-popularity-refresh is a separate job against a plain table (album_popularity, migration 0107), not this MV. Only relevant if we ever want the master-collapsed number instead of the per-pressing one.

Two more surfaces still reading the dead column

Neither is what Kate reported, and neither is fixed by this ticket:

Site	Status
library.service.ts:406 ${library.plays} AS plays (getRotationFromDB)	Broken, exposed on GET /library/rotation; declared at api.yaml:2921. dj-site declares the field and never reads it, so nobody has noticed.
LIBRARY_VIEW_PROJECTION via GET /library/	Broken, exposed — LibraryArtistViewResponse does not Omit plays, so that endpoint ships 0 against a spec that declares the field. (library.controller.ts:2336's docstring claiming /library/query "adds plays" relative to GET /library/ is wrong on both the runtime shape and the spec.) Masked by omission on GET /library/search, which drops the field before serialization.

And one that is not a wire value but is a live bug: suggest.service.ts:23 sorts artist suggestions by SUM(COALESCE(library.plays, 0)) … ORDER BY total_plays DESC. That sum is constant zero, so GET /flowsheet/suggest/artists is effectively unordered — it has never ranked by anything.

I have not filed these; flagging for triage.

[jakebromberg]

Production check (read 2026-09-08, 18:46 PDT)

The refresh job is alive:

          job_name          |          last_run          |      age
----------------------------+----------------------------+----------------
 album-plays-refresh        | 2026-09-09 01:14:09.647+00 | 00:31:42
 album-popularity-refresh   | 2026-09-09 01:15:09.296+00 | 00:30:42

That is 2026-09-08 18:14 PDT, 32 minutes before the read. So the hourly timer is running and the unbounded-staleness failure mode — first refresh at +1 interval, container restarting more often than that — is not currently occurring.

What this means for the fix: once getAlbumFromDB sources from the MV, the album-detail modal will be at most ~1 hour stale, and exactly as stale as the search results it is being compared against. Kate's 135-vs-0 discrepancy becomes 135-vs-135, which is the whole point.

The design flaw is still worth the checklist item — nothing alarms if the job stops, and cronjob_runs is written but never read — but it is not blocking this fix, and it is not currently mis-serving anyone.

Incidental from the same read: flowsheet-etl and rotation-etl last ran 2026-08-08 22:00 PDT, 30 days ago, consistent with Phase 3 of the tubafrenzy decommission having unscheduled them.