Dashboard is permanently empty: daily rollup crashes on GROUP BY hybrid page_type (+ misleading 'As of' and confusing Refresh/Rollup buttons)

[apjaphet]

Summary

The admin Statistics & Diagnostics dashboard shows no metrics even after uploading and processing several documents. Investigation found the daily rollup crashes on every run, so the stats_daily_rollup table is never populated — the dashboard is permanently empty, not merely stale. Two related UX defects compound the confusion.

Root cause (the empty dashboard)

app/services/stats_aggregator._rollup_content runs:

select(WikiPage.page_type, func.count(WikiPage.id)).group_by(WikiPage.page_type)

WikiPage.page_type is a hybrid_property whose SQL form is a CASE over slug with the bucket labels as literals. Under asyncpg those literals compile to bind parameters, so the CASE in the SELECT ($1..$9) and the identical CASE in the GROUP BY ($10..$18) occupy different parameter slots. Postgres cannot match them and raises:

column "wiki_pages.slug" must appear in the GROUP BY clause or be used in an aggregate function

This exception aborts the rollup transaction, and because run_daily_rollup shares one session across sections, every section then fails with current transaction is aborted (observed: {content:-1, contribution:-1, usage:-1, gaps:-1, audit:-1}). Net effect: the nightly daily_stats_rollup_cron has never once succeeded, and every dashboard KPI renders as —.

Secondary UX defects

1. Misleading "As of". GET /admin/stats/overview returned as_of = to_date (= today), implying the numbers were current, when the underlying rollup covers yesterday. The rollup rows carry a real computed_at that was never surfaced.
2. Confusing controls. Two buttons — "Refresh" (re-fetches aggregated rows) and "Rollup {date}" (recomputes the rollup table) — with no indication of the difference, and the rollup targeted to_date (yesterday), so today's activity could never appear even after clicking it.

Repro

1. Upload/process documents so wiki pages exist (verified: 9 sources, 47 wiki pages present).
2. Open the admin dashboard → every KPI shows —.
3. Trigger the rollup → all sections return -1; server logs show the GROUP BY/InFailedSQLTransactionError cascade.

Fixes applied locally

• Rollup crash: bucket page_type in Python (there are only tens of pages; reuses the hybrid's Python branch) instead of grouping by the parameterized CASE in SQL. All five sections now succeed: {content:10, contribution:8, usage:9, gaps:1, audit:2}, and wiki.pages.total = 47 lands with a real computed_at.
• Honest "As of": OverviewResponse.as_of now returns the freshest computed_at across the KPI rows (a UTC datetime, or null if never computed).
• Clear controls: renamed the buttons to "Refresh Page" (page/data reload only) and "Refresh Statistics" (recompute the rollup as of now, targeting today so same-day activity is captured, then reload). The overview footer now reads "Statistics as of <local time> (<HH:MM> UTC)".

Known gap (not fixed here)

The aggregator tracks wiki pages / revisions / drafts / MCP usage but has no document/source-count metric — uploaded documents are never a first-class dashboard number even after this fix. Flagging for a follow-up if a "documents ingested" KPI is desired.