obs(selfhost): monitor Cloudflare D1 database size and add bounded dedup retention for signal_snapshots

[JSONbored]

Context

Found during the 2026-07-06 incident audit (parent: #1667). The central Cloudflare D1 database (shared by the cloud gittensory-api / Orb) hit its ~10GB size cap today, because signal_snapshots (a contributor/repo scoring signal cache, migrations/0003_data_spine.sql) had zero deduplication — a new snapshot is generated every 6 hours per key with no "keep only latest" logic, accumulating 342,243 rows for only 2,183 distinct keys before the cap was hit. This caused real D1 writes (including an Orb relay registration handshake) to start failing/timing out, contributing to today's outage. It was manually cleaned up today (down to ~3.35GB) as an emergency stopgap, but nothing prevents it recurring.

Grepped the whole repo (src/**, prometheus/, grafana/) for any D1-size signal and found zero — self-host's own Prometheus stack has no scrape target that could see it (D1 size is a central-cloud resource, not self-host-observable), and there's no scheduled check anywhere. Nothing would have caught this before writes started failing in production.

Requirements

• Add a bounded retention/dedup job for signal_snapshots: keep only the latest row per (signal_type, target_key), since the table only needs current state per key, not full history. This is the actual root-cause fix — without it, the table refills toward the cap again regardless of monitoring.
• Add a scheduled probe that fetches current D1 database size (and row counts for high-growth tables) via the Cloudflare API, since size isn't queryable from inside a D1 query itself.
• Expose the result as a new gauge metric (e.g. gittensory_d1_database_size_bytes, gittensory_d1_table_row_count{table=...}).
• Add a Prometheus alert for D1 size approaching the cap (warn ~70%, critical ~90%) and for unbounded per-key row growth in tables like signal_snapshots.
• Add a corresponding Grafana panel.

Deliverables

• Dedup/retention job or migration for signal_snapshots (bounded batches — a naive single-statement window-function delete over the full table will exceed D1's per-statement CPU limit, confirmed empirically today; batch by signal_type or similar).
• src/selfhost/metrics.ts: new gauge metric definitions.
• New scheduled probe wired into the existing scheduled-loop or its own Sentry monitor.
• prometheus/rules/alerts.yml + grafana/dashboards/gittensory.json: new rule + panel.

Expected outcome

signal_snapshots (and similar tables) stay bounded in size going forward instead of accumulating indefinitely, and an operator sees D1 size trending toward the cap in Grafana well before writes start failing — instead of discovering it only after a production outage.

[linear-code]

JSO-2842

[JSONbored]

Advances #3810 via #3857, which ships the dedup/retention job — the issue's own "actual root-cause fix" — for signal_snapshots. Batched per signal_type (mirroring the manual incident remediation) so each DELETE stays within D1's per-statement CPU budget, wired into the existing daily prune-retention job and the /v1/internal/retention/preview route.

Not included in that PR, still open here:

• Scheduled probe fetching D1 database size via the Cloudflare Management API — this repo currently has no CLOUDFLARE_API_TOKEN/CLOUDFLARE_ACCOUNT_ID credentials or Management API integration anywhere, so this needs new secret plumbing before any probe code can be written.
• New gittensory_d1_database_size_bytes / gittensory_d1_table_row_count{table=...} gauges.
• Prometheus alert rule (warn/critical) + Grafana panel — these depend on the metric existing first, and on deciding how self-host's own Prometheus/Grafana stack (which has no visibility into the cloud D1 database) would consume a metric computed inside the Cloudflare Worker. There's no existing bridge for that today.

Left this split intentionally rather than bundling a higher-risk, harder-to-verify infra change into the same PR as the clean root-cause fix. Happy to pick up the probe/metrics/alerting half as a follow-up once there's a call on where the Cloudflare API credentials should live.