Compress github.Payload storage and extend retention to 30 days

The github table stores every GitHub webhook delivery’s full JSON body in a Payload longtext column, cleaned up after 10 days (database.cleanUp.github in Src/config/config.json). GitHub payloads compress extremely well, and the table’s storage footprint (several gigabytes for a 10-day window) is disproportionate to what it retains. We’re switching Payload to a ZSTD-compressed LONGBLOB and extending retention to 30 days, since compression keeps the resulting storage growth manageable.

This proceeds despite [0002-defer-bot-interface-schema-split](0002-defer-bot-interface-schema-split.md) flagging this repo for retirement “within the next few months.” That ADR was about the risk of a live schema split (moving tables to a separate database) not being worth it for a repo winding down — a different, higher-risk kind of change than compressing a column in place. Uncontrolled storage growth is an active, ongoing cost regardless of the repo’s eventual retirement date, so it’s still worth fixing now.

To avoid downtime or an irreversible one-shot cutover, the migration is phased:

1. Add columns, dual-write, fallback read. Add PayloadCompressed LONGBLOB, PullRequestNumber, PullRequestTitle (Sql/0028...). New inserts write both the legacy Payload and the compressed PayloadCompressed (saveGitHubNotificationToDatabase() in Src/lib/database.php). Every reader (Src/api/v1/github.details.php, Src/api/v1/repositories.php) decompresses PayloadCompressed when present, falling back to legacy Payload otherwise, via decompressGitHubPayload().
2. Backfill. Src/backfill-github-payload.php is a one-shot, resumable CLI script that compresses every historical row still missing PayloadCompressed.
3. Drop the legacy column, bump retention. Once backfill is confirmed complete (SELECT COUNT(*) FROM github WHERE PayloadCompressed IS NULL AND Payload IS NOT NULL returns 0), drop Payload (Sql/0029...), simplify the read/write code to drop the legacy fallback, delete the backfill script, and bump database.cleanUp.github from "10 DAY" to "30 DAY".

Src/api/v1/repositories.php previously ran JSON_EXTRACT()/LIKE directly on Payload in SQL — not possible against compressed bytes (MySQL/MariaDB have no built-in ZSTD SQL function). Its number/ pull_request.title lookups moved to the new sidecar columns (populated at write time) so they stay pure SQL; its full-text PR-mention scan (which matches arbitrary event payloads, not just pull_request events, so it can’t be reduced to a fixed column) moved to decompress + preg_match in PHP.

PHP’s ext-zstd (PECL, not bundled with core PHP) is now required everywhere the app runs: Src/composer.json (ext-zstd), the Dockerfile (pecl install zstd), CI (.github/workflows/build.yml), and the production VPS (scripts/setup-vps.sh now fails fast if it’s missing — needs manual installation there before Phase 1 deploys, since VPS PHP provisioning happens outside this repo).

Rollout

Deploy Phase 1 → confirm new rows get PayloadCompressed populated → run php Src/backfill-github-payload.php on the VPS → verify the backfill query above returns 0 → deploy Phase 3.

Phase 3 must not be merged to main before the backfill is validated. deploy.yml runs pending Sql/ migrations on every push to main, so merging the drop-column migration before the backfill script has been run against production would permanently drop any payload that hadn’t been backfilled yet.

Consequences

• Until Phase 3 ships, the github table carries both Payload and PayloadCompressed for new rows, so storage temporarily grows before it shrinks.
• Rollback before Phase 3 is trivial: PayloadCompressed/the sidecar columns are additive and unused by anything that isn’t deployed; reverting the app code is enough. Rollback after Phase 3 requires re-adding a Payload longtext column and reverse-backfilling it via zstd_uncompress(PayloadCompressed).
• repositories.php’s PR-mention lookup no longer pre-filters in SQL via LIKE; it fetches by Owner/Repository (already indexed) and filters in PHP instead. The previous LIKE '%pull%N%' was already an unindexed scan for a leading-wildcard pattern, so this doesn’t introduce a new cost class for that route.