Materialize GitHub webhook counts into 6-hour buckets

Dashboard statistics need historical GitHub webhook volume, but the github table is a raw event log intended for troubleshooting, not analytics — it’s cleaned up after database.cleanUp.github (30 days) and counting rows per time bucket on demand would mean scanning the raw log every time the dashboard loads. github_statistics_6h (Sql/0032.create_github_statistics_6h_table.sql) materializes hit counts into fixed 6-hour buckets so dashboard queries stay cheap and history survives past the raw table’s retention window.

Bucket calculation

Each day is divided into four fixed buckets, aligned to 00:00, 06:00, 12:00, 18:00, in the app’s configured local timezone (date.timezone in config/config.php — the same timezone every other DATETIME column on github is already written in via PHP’s date(), e.g. DeliveryTime). getGitHubStatisticsBucket() (Src/lib/database.php) computes the pair for a given Unix timestamp:

$bucketStartHour = intdiv((int) date("G", $timestamp), 6) * 6;   // 0, 6, 12, or 18
$day = date("Y-m-d", $timestamp);
// bucket_start = "$day $bucketStartHour:00:00"
// bucket_end   = "$day " . ($bucketStartHour + 5) . ":59:59"

bucket_start is the table’s primary key, so one row exists per bucket per day; bucket_end is stored (rather than derived) purely so dashboard queries don’t need to recompute it.

Aggregation

There is a single code path that inserts rows into github: saveGitHubNotificationToDatabase(). Rather than a scheduled job re-aggregating the raw log, the bucket is upserted inline, immediately after the github insert succeeds, reusing the same connection:

if (!$stmt->execute()) {
    throw new Exception(...);
}

incrementGitHubStatisticsBucket($mysqli, $receivedTime);

incrementGitHubStatisticsBucket() runs:

INSERT INTO github_statistics_6h (bucket_start, bucket_end, hits) VALUES (?, ?, 1)
ON DUPLICATE KEY UPDATE hits = hits + 1

INSERT ... ON DUPLICATE KEY UPDATE is atomic in MySQL/MariaDB — concurrent webhook consumers racing to create or increment the same bucket can’t lose an update or create duplicate rows for one bucket_start, so no separate locking is needed. The statement is run through executeWithDeadlockRetry() (already used elsewhere in this file for the same reason) since every webhook landing in the same 6-hour window contends for the same row — that’s the expected steady-state here, not an edge case.

Because this runs inside saveGitHubNotificationToDatabase()’s existing try/finally, a failure here throws like every other statement in that function — it does not swallow errors, so a broken github_statistics_6h write surfaces the same way a broken github write would.

Consequences

• One row is written or updated per webhook delivery, in addition to the existing github insert — negligible extra cost (a single-row atomic upsert against an indexed primary key).
• github_statistics_6h is intentionally not covered by database.cleanUp.github’s retention policy — its whole purpose is to outlive the raw log.
• The dashboard now reads this table instead of aggregating github live — see docs/adr/0005-dashboard-reads-github-statistics-6h.md.