music_library/backlog/tasks/ml-169.8 - Fix-StatsLive.Index-10-sync-queries-blocking-TTFB.md

id, title, status, assignee, created_date, updated_date, labels, dependencies, references, modified_files, parent_task_id, priority, ordinal

id	title	status	assignee	created_date	updated_date	labels	dependencies	references	modified_files	parent_task_id	priority	ordinal
ML-169.8	Fix: StatsLive.Index 10 sync queries blocking TTFB	Done	pi	2026-05-19 11:48	2026-05-25 06:30	perf fix		backlog/documents/doc-27 - Phase-4-Performance-Audit-Low-Hanging-Fruit-Findings.md	lib/music_library_web/live/stats_live/index.ex lib/music_library/collection.ex test/music_library_web/live/stats_live/index_test.exs	ML-169	high	29000

Description

Fix performance issue where StatsLive.Index.mount/3 executes a block of synchronous Ecto queries before returning, blocking the initial render (TTFB) on the home page.

Problem

The query trace at /tmp/queries.sql confirms the Stats mount/helper block at lines 14-63. It executes 10 synchronous application queries before the page is ready:

1. Collection.get_latest_record() — ORDER BY purchased_at LIMIT 1
2. Collection.count_records_by_artist(limit: 20) — json_each + GROUP BY
3. Collection.count_records_by_genre(limit: 20) — json_each + GROUP BY + excluded-genre subquery
4. Collection.count_records_by_release_year(limit: 20) — substr + GROUP BY
5. Collection.get_records_on_this_day(date) — strftime + ORDER BY
6. Collection.count_records_by_format() — GROUP BY format
7. Collection.count_records_by_type() — GROUP BY type
8. Wishlist.count() — count over the FTS-backed SearchIndex with purchased_at IS NULL
9. ListeningStats.recent_activity(timezone) — 3 top-level correlated scalar subqueries per returned row
10. ListeningStats.scrobble_count() — simple COUNT

The trace reported about 35.6ms total query time for these 10 queries on the captured run. recent_activity was not the slowest query in that trace, but it remains structurally risky because its correlated subqueries scale with the 100-row activity limit.

Impact

The home page does non-critical data loading before the initial render. The existing async pattern used by StatsLive.TopAlbums and StatsLive.TopArtists should be extended, but the implementation must preserve LiveView stream conventions and use true scalar counters for the immediately visible badges.

Files

• lib/music_library_web/live/stats_live/index.ex — mount/render async orchestration
• lib/music_library/collection.ex — add a direct scalar collection count if format/type grouped stats move async
• test/music_library_web/live/stats_live/index_test.exs — update page tests to wait for async sections where needed

Fix

Keep only true scalar badge counters synchronous: collection count, wishlist count, and scrobble count. Do not derive collection_count from count_records_by_format/0 if format stats move async; add Collection.count/0 or an equivalent direct aggregate.

Move non-critical sections out of synchronous mount work:

• get_latest_record
• count_records_by_artist / count_records_by_genre / count_records_by_release_year
• count_records_by_format / count_records_by_type if they are no longer needed to derive the badge count
• get_records_on_this_day
• recent_activity

Use assign_async for value assigns rendered through <.async_result> with loading and failed states. For scrobble activity, preserve the existing @streams.recent_tracks and @streams.recent_albums contract: use start_async + handle_async to stream both lists, or use stream_async carefully if it can support both streams without converting them to list assigns.

Remember that LiveView async tasks only start once the socket is connected. The disconnected render must have safe initial assigns/loading states and must not render helpers that expect plain lists or records against %AsyncResult{} values.

Show skeleton/loading placeholders for async sections and graceful failed states without flashing empty data containers.

Acceptance Criteria

• #1 StatsLive.Index initial mount runs only true scalar synchronous queries needed for immediately visible counters and returns under 200ms on the audited environment
• #2 Counter badges for collection, wishlist, and scrobbles render immediately from scalar counts
• #3 Latest purchase, format/type stats, collection charts, on-this-day records, and scrobble activity load asynchronously with skeleton/loading placeholders
• #4 Scrobble activity async loading preserves LiveView streams for recent tracks and recent albums rather than converting stream-backed UI to plain list assigns
• #5 Async sections render graceful failed or nil states and do not flash empty data containers
• #6 Tests cover immediate counter rendering and use render_async() for async-loaded Stats page content
• #7 A follow-up query trace or equivalent query-budget check confirms the synchronous Stats mount query count is reduced from the original 10-query block

Implementation Plan

1. Add or identify a direct scalar collection count so collection_count can stay synchronous without depending on grouped format stats.
2. Split Stats mount assigns into synchronous scalar counters plus explicit async loading state for each non-critical section.
3. Wrap non-stream async value sections in <.async_result> with loading and failed slots; update helpers so they receive plain loaded values only.
4. Load scrobble activity asynchronously while preserving @streams.recent_tracks and @streams.recent_albums, and keep last_updated_uts updates compatible with TopAlbums/TopArtists.
5. Update Stats LiveView tests to assert counters before async completion and async sections after render_async().
6. Re-run the targeted tests and capture or inspect the Stats mount query block to verify the sync query budget dropped.

Implementation Notes

Review correction from /tmp/queries.sql: the trace supports the 10 synchronous Stats queries claim, but not the statement that recent_activity was the slowest query in that run. It also shows collection_count currently depends on grouped format stats, so the task needs a direct scalar collection count before moving format/type grouped stats async.

Implemented async loading for StatsLive.Index home page:

• Added Collection.count/0 — direct scalar COUNT for the collection badge (avoids deriving from grouped format counts)

• Refactored mount/3: only 3 scalar counters stay synchronous (collection_count, wishlist_count, scrobble_count) down from 10 queries

• Moved latest_record, collection_summary (format/type/charts), and on_this_day_records to assign_async with skeleton loading states via <.async_result>

• Moved scrobble activity to start_async + handle_async, preserving @streams.recent_tracks and @streams.recent_albums contract

• Added handle_async/3 with three clauses: {:ok, {:ok, data}}, {:ok, {:error, reason}}, {:exit, reason}

• Added skeleton/loading and failed states for all async sections

• Separated on_this_day_records into its own async key to support interactive date changes (uses AsyncResult.ok/2 in event handler)

• Updated all 11 StatsLive tests: added render_async() before assertions on async content; wishlist counter test verifies immediate rendering

• Full test suite: 1135 tests pass, no regressions

Final Summary

Summary

Reduced synchronous StatsLive.Index mount queries from 10 to 3 by moving non-critical data loading to async tasks with skeleton/loading placeholders.

What changed

lib/music_library/collection.ex — Added Collection.count/0, a direct scalar COUNT(*) WHERE purchased_at IS NOT NULL for the collection badge, replacing the previous Enum.sum_by(count_records_by_format()) derivation.

lib/music_library_web/live/stats_live/index.ex — Major refactor:

• mount/3: Only 3 sync queries remain — Collection.count(), Wishlist.count(), ListeningStats.scrobble_count(). All other data loads via assign_async (latest_record, collection_summary, on_this_day_records) and start_async (scrobble_activity).
• render/1: Each async section wrapped in <.async_result> with :loading (skeleton placeholders with animate-pulse) and :failed (graceful error states) slots.
• handle_async/3: Three clauses for scrobble activity — success (streams tracks/albums, sets last_updated_uts), function error (logs), task exit (logs).
• on_this_day_records separated into its own async key to support interactive date changes via AsyncResult.ok/2 in the event handler.
• Scrobble activity preserves @streams.recent_tracks and @streams.recent_albums contract — streams are seeded with empty lists on mount and populated via handle_async.
• Removed dead helpers: assign_counts/1, record_stats/1 (inlined into template).

test/music_library_web/live/stats_live/index_test.exs — Added render_async() after visit("/") for all tests asserting on async-loaded content (latest purchase, format/type stats, on-this-day sections). Counter badge tests verify rendering before async completion.

Sync query budget

Before	After
10 sync queries blocking mount	3 sync queries (collection_count, wishlist_count, scrobble_count)

The 7 non-critical queries now run in background tasks after the initial render.

Tests

• StatsLive.Index: 11/11 pass
• Full test suite: 1135/1135 pass (both partitions)
• Credo: no issues
• Mix format: compliant

Risks / Follow-ups

• If the async tasks fail silently (logged but not user-visible), users see skeleton states replaced by empty "failed" slots. The failed slots are minimal (error icon for latest record, text for on-this-day, empty for format/type/charts). Consider improving failed-state UI messaging in a future iteration.
• The handle_info PubSub path still calls assign_scrobble_activity/1 synchronously for live updates — this is acceptable since it fires on events rather than page load.