A contribution leaderboard showed `used: 0` for every row and `coverage.lesson_authors: 0`, so the board read as "nobody has retrieved anything yet". The underl…
A contribution leaderboard showed used: 0 for every row and coverage.lesson_authors: 0, so the board read as "nobody has retrieved anything yet". The underlying table held 424 author rows and 17k retrieval rows; a direct SELECT COUNT(*) proved the number was false.
Three independent mechanisms produced the same all-zero display, and each one alone was enough to make the board lie:
coverage.lesson_authors field in the response object was literally 0 — a value the panel then printed as fact. The count had never been wired to a query.lesson_authors.path against search_signals.top_id, but top_id is written as a lesson's own id (a bare filename stem) — measured across 15,290 non-empty rows, zero of them were paths. The join matched nothing, so the compute path produced 0 honestly but uselessly.The trap is that 0 is a legitimate value for all three: a fresh install really has no rows. So nothing looks broken — the failure mode is indistinguishable from an empty dataset, and it stays invisible until someone counts in the database instead of reading the dashboard.
: 0).null, not 0, so "unread" and "none" are different values end to end; give the renderer a separate sentence for each.GROUP BY over the source column before trusting an equality. Here: SELECT CASE WHEN top_id LIKE '%.md' THEN 'path' WHEN top_id LIKE '%/%' THEN 'other' ELSE 'bare-stem' END, COUNT(*) ... — that one query is what revealed top_id was never a path.{count} lessons have a recorded author), so it cannot describe a state the API no longer reports.SELECT COUNT(*) on the same table before believing any dashboard zero.GET /api/leaderboard/ladder?window=all returned coverage.lesson_authors: 424 and used: 945 after the fix, where both read 0 before; a second window (?window=week) independently reported used: 411, matching a per-day recount. The join's coverage was also quantified rather than assumed: only 7.6% of retrieval rows belong to lessons with a recorded author, and that ceiling is a property of the data, not of the query.
After the fix, coverage.lesson_authors reads 424 (was a hardcoded 0) and the top row's used reads 945 (was 0) on the all-time window; the week window independently reports 411.