Three read paths still source the dead library.plays column — including an artist autocomplete that has never ranked

[jakebromberg]

Problem

library.plays is a physical column nothing maintains — it is 0 for every row, and the increments that would have kept it current are commented out (flowsheet.service.ts:906, :952). Real per-album counts live in the album_plays materialized view.

#1489 established the fix (join the MV, COALESCE(album_plays.plays, 0)) and applied it to GET /library/query. #2397 applies it to GET /library/info. Three call sites still read the dead column, two of them shipping the zero to clients and one silently breaking an ordering.

The three

1. GET /library/rotation ships plays: 0 — library.service.ts:406, inside getRotationFromDB:

${library.plays} AS plays,

Declared on the wire (api.yaml:2921) and surfaced as Rotation.plays (library.service.ts:305). dj-site declares the field (lib/features/rotation/types.ts:54) and never reads it, which is why nobody has noticed.

2. GET /library/ ships plays: 0 — via LIBRARY_VIEW_PROJECTION (library.service.ts:1763), serialized at library.controller.ts:319. LibraryArtistViewResponse does not Omit plays, so the zero goes out against a spec that declares the field (api.yaml:9747 → AlbumSearchResult).

Note library.controller.ts:2336's docstring claims /library/query "adds … plays" relative to GET /library/ — wrong on both the runtime shape and the spec. Fix it with the code.

GET /library/search reads the same projection but is masked by omission: viewRowToLibraryResult (library.service.ts:3380-3393) drops plays before serializing. Leave it dropped unless there is a caller for it.

3. GET /flowsheet/suggest/artists is not ordered at all — suggest.service.ts:23:

SELECT artist_name, SUM(COALESCE(library.plays, 0)) AS total_plays
...
ORDER BY total_plays DESC
LIMIT 5

total_plays is identically zero for every group, so ORDER BY is a no-op and Postgres returns whatever the plan yields. The function's own docstring says "ordered by total plays descending". This is the only one of the three with a user-visible symptom today: the artist autocomplete has never ranked by anything, so its five suggestions are effectively arbitrary among all prefix matches — and with LIMIT 5 on a 24k-row artist table, arbitrary means the right artist is often absent.

Desired end state

No read path returns or orders by library.plays. Each of the three either sources from album_plays or drops the field.

Suggested approach

(1) and (3) are local edits: add the album_plays LEFT JOIN and swap the expression, exactly as library-search.service.ts:105-106 does.

(2) is the one with a cascade, and it is why #2397 deliberately left it alone. LIBRARY_VIEW_PROJECTION is constrained by

} as const satisfies Record<keyof LibraryArtistViewEntry, Column>;

Column, not Column | SQL — so substituting a COALESCE expression is a compile error until the constraint is widened (contrast library-search.service.ts:150, already Column | SQL). And libraryViewQuery only joins the MV when withPlays (:1848), while LIBRARY_VIEW_JOINS_RAW (:1793) has no such join at all — so the alias UNION ALL branches, searchByArtist and the CTA windowed subquery would fail to resolve the column. Widening the satisfies, making the join unconditional, adding it to the raw chain, and re-verifying the CTA arm is the actual scope.

Cheaper alternative for (2), worth pricing first: if no client reads plays off GET /library/, Omit it from LibraryArtistViewResponse the way /library/search already does, and drop the field from the spec. Shipping nothing is better than shipping a wrong number, and it avoids the projection cascade entirely.

Constraints

• Read-only changes. No migration, no write path.
• album_plays refreshes hourly (album-plays-refresh.service.ts:47), verified running in prod 2026-09-08. Every surface sourcing from it will agree with the others and be at most an hour stale — which is the point.

Acceptance criteria

• GET /library/rotation returns real counts, or the field is dropped from response and spec.
• GET /library/ likewise — decide join-vs-omit and state the reasoning.
• suggestArtists ranks by real play counts; test that a high-play artist outranks a low-play one for a shared prefix. Today any such test would pass or fail at random.
• library.controller.ts:2336's docstring corrected.
• A grep for library.plays in apps/backend returns only the commented-out increments and the schema definition.

Related

• GET /library/query?sort=plays sorts by the dead library.plays column (all zeros) #1489 — same defect on GET /library/query; the template.
• GET /library/info reports plays from the dead library.plays column: album detail shows "0 plays" for a release search reports 135 #2397 — same defect on GET /library/info; deliberately scoped to that endpoint and deferred the projection cascade to here.
• Catalog export ships plays = 0; popularity ranking needs attribution-corrected play data #1486 — the separate Phase-2 attribution epic (free-text tail, master collapse). Not this; the catalog-export leg was already fixed by bec7db3c.