Dashboard reads GitHub chart data from github_statistics_6h, not the raw log

GET /github (GitHubDashboardController::getDashboardData()) builds two chart datasets: statistics (all sources except GitHub) and statistics_github (GitHub only). Both were built by StatisticsService running a GROUP BY DATE(...), HOUR(...) DIV 6 query per source table, once per dashboard load. For github specifically, that meant scanning the entire raw webhook log — the exact table ADR-0004 introduced github_statistics_6h to stop having to scan.

StatisticsService::getGitHubStatistics() now reads pre-aggregated rows straight out of github_statistics_6h instead:

SELECT CONVERT_TZ(bucket_start, ?, ?) AS d, hits
FROM github_statistics_6h
WHERE bucket_start >= DATE_SUB(NOW(), INTERVAL 10 DAY)
ORDER BY bucket_start

No COUNT(1)/GROUP BY — hits is already the count. statistics (the other nine sources) is untouched; it still aggregates their raw tables live, since none of them got a materialized counterpart. Its query loop no longer iterates github either — getStatistics() already discarded github’s results before returning (they’re excluded from that method’s output, github has its own chart), so querying it there was pure waste; the table is now filtered out before the query runs instead of after.

Output shape is unchanged, byte for byte

statistics_github is still [["Date", "GitHub"], [label, count], ...], label is still "d/m HH:00-HH:59", and buckets with zero hits still don’t appear as rows (they never did — the old query’s “gap fill” logic was dead code for the GitHub-only case, since its key set was built from GitHub’s own result rows, so every key already existed in GitHub’s own map). Nothing about the openapi.json DashboardData schema changes; it was already typed as a schema-less object.

The one real trade-off: bucket alignment across timezones

The old query converted the raw Date (a TIMESTAMP) via CONVERT_TZ(Date, @@session.time_zone, ?) and then grouped into 6-hour buckets — so a viewer’s X-timezone-offset header (getTimeZoneOffset()) reshaped the bucket boundaries themselves. A viewer three hours off from the server saw buckets aligned to their own midnight/6am/noon/6pm.

github_statistics_6h.bucket_start is a DATETIME, not a TIMESTAMP — it’s the literal wall-clock value getGitHubStatisticsBucket() computed in the app’s configured timezone (date.timezone, Europe/Dublin) at write time, with no timezone attached. The bucketing has already happened by the time this query runs; it can’t be redone in the viewer’s timezone without the original per-event rows, which is exactly the scan this change exists to avoid.

getGitHubStatistics() still applies CONVERT_TZ for display, so the chart’s clock-hour labels still shift with the viewer’s offset — but the 6-hour windows themselves stay aligned to Dublin’s grid. A viewer whose offset isn’t a whole multiple of Dublin’s own offset sees accurately-labeled, genuinely-6-hour windows (e.g. 18/08 02:00-07:59) that just don’t land on their midnight/6am/ noon/6pm. Same data, same window width, occasionally different-looking boundaries than before for non-Dublin viewers. For a Dublin-timezone viewer (or any viewer at a whole-multiple-of-6-hours offset from it), output is identical to the old query.

CONVERT_TZ’s source argument is computed in PHP as a fresh UTC offset ((new DateTime("now", new DateTimeZone(date_default_timezone_get())))->format("P")) rather than passed as the literal string 'Europe/Dublin', so this doesn’t depend on the MySQL/MariaDB server having its named-timezone tables loaded (CONVERT_TZ silently returns NULL for an unrecognized named zone) — offset strings like +01:00 always work, and computing it per-request keeps DST transitions correct without needing to duplicate that logic elsewhere.

Consequences

Dashboard chart loads no longer scan the raw github log at all for GitHub’s own chart.
github_statistics_6h retention (per ADR-0004, effectively indefinite) now governs how far back the GitHub chart could show data, though the query still caps the window at 10 days to match the previous chart’s behavior exactly.
If a future change wants viewer-timezone-aligned bucket boundaries again, it needs either per-viewer-timezone buckets stored alongside the fixed ones, or accepting the scan this table was built to eliminate.