yonlu/yellowjacket
1
1
Fork 0
Code Issues 55 Pull Requests 6 Actions Packages Projects Releases 12 Wiki Activity

play_history is written and never read — recency-weighted play stats #185

New Issue
Open
opened 2026-08-21 14:22:50 +00:00 by yonlu · 0 comments
yonlu commented 2026-08-21 14:22:50 +00:00
Owner
Copy Link
Copy Source

Report

play_history records one row per play (audio_file_id, played_at)
and nothing reads it for recency. Every "what do I listen to"
surface in the app uses audio_files.play_count, which is a lifetime
counter: an album played fifty times in 2023 and never since outranks
one played daily this month, permanently.

Findings

  • Schema: backend/database/sql/schemas/play_history.sql, indexed on
    audio_file_id. There is no index on played_at, which a windowed
    query would want.
  • home.sql's shelves use SUM(af.play_count)
    (HomeMostPlayedAlbums) and MAX(af.last_played)
    (HomeRecentlyPlayedAlbums) — the two things a history table exists
    to do better than.
  • A lifetime counter also cannot answer "played 3 times last week"
    versus "played 3 times ever", which is the distinction that makes a
    recency shelf worth having.

Direction

A windowed aggregate — plays per entity in the last N days — behind one
query, plus the played_at index. Then decide what consumes it. Three
candidates, and they are not the same piece of work:

  1. A home shelf ("On repeat lately"), which is where this most obviously
    belongs and would want home's existing duplicate guard.
  2. Fuel for #45 (auto-DJ), whose whole problem is choosing what to
    queue next.
  3. A sort key, which is the weakest of the three — it is a question
    about a period, and a column header cannot say which period.

Deliberately not folded into #32/#46: those are a projection widening
plus comparators, and this is a new query with a windowing decision and
a retention question (does play_history grow forever? nothing prunes
it today).

**Report** `play_history` records one row per play (`audio_file_id`, `played_at`) and **nothing reads it for recency**. Every "what do I listen to" surface in the app uses `audio_files.play_count`, which is a lifetime counter: an album played fifty times in 2023 and never since outranks one played daily this month, permanently. **Findings** - Schema: `backend/database/sql/schemas/play_history.sql`, indexed on `audio_file_id`. There is no index on `played_at`, which a windowed query would want. - `home.sql`'s shelves use `SUM(af.play_count)` (`HomeMostPlayedAlbums`) and `MAX(af.last_played)` (`HomeRecentlyPlayedAlbums`) — the two things a history table exists to do better than. - A lifetime counter also cannot answer "played 3 times last week" versus "played 3 times ever", which is the distinction that makes a recency shelf worth having. **Direction** A windowed aggregate — plays per entity in the last N days — behind one query, plus the `played_at` index. Then decide what consumes it. Three candidates, and they are not the same piece of work: 1. A home shelf ("On repeat lately"), which is where this most obviously belongs and would want `home`'s existing duplicate guard. 2. Fuel for #45 (auto-DJ), whose whole problem is choosing what to queue next. 3. A sort key, which is the weakest of the three — it is a question about a period, and a column header cannot say which period. Deliberately **not** folded into #32/#46: those are a projection widening plus comparators, and this is a new query with a windowing decision and a retention question (does `play_history` grow forever? nothing prunes it today).
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Area/Design
Area/Downloads
Area/Explore
Area/Library-UI
Area/Metadata
Area/Packaging
Area/Player
Area/Queue
Area/Settings
Area/Shell-Nav
Compat/Breaking
Breaking change that won't be backward compatible
 Kind/Bug
Something is not working
 Kind/Documentation
Documentation changes
 Kind/Enhancement
Improve existing functionality
 Kind/Feature
New functionality
 Kind/Security
This is security issue
 Kind/Testing
Issue or pull request related to testing
 Platform/Android
Platform/Desktop
Priority
Critical
1
The priority is critical
Priority
High
2
The priority is high
Priority
Medium
3
The priority is medium
Priority
Low
4
The priority is low
Reviewed
Confirmed
1
Issue has been confirmed
Reviewed
Duplicate
2
This issue or pull request already exists
Reviewed
Invalid
3
Invalid issue
Reviewed
Won't Fix
3
This issue won't be fixed
Status
Blocked
1
Something is blocking this issue or pull request
Status
Need More Info
2
Feedback is required to reproduce issue or to continue work
Status
Abandoned
3
Somebody has started to work on this but abandoned work
Status
In Progress
Somebody is actively working on this right now
No labels Area/Library-UI Kind/Feature Platform/Android Platform/Desktop
Priority
Low
4
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
logan yonlu
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: yonlu/yellowjacket#185