Context
scripts/export-grafana-reporting-db.sh (run every GRAFANA_REPORTING_EXPORT_INTERVAL_SECONDS — default 30s — by reporting-exporter in docker-compose.yml) rebuilds the entire Grafana reporting SQLite mirror from scratch on every cycle: it drops the temp DB, re-exports the full pull_requests/review_audit-derived review_targets view and the full ai_usage_events table (no incremental cursor, no upsert), then atomically replaces the output file.
Measured live on edge-us-01 (docker stats): this container uses ~2MB of RAM but has generated 91.3GB of cumulative block I/O in 37 hours (~2.5GB/hour). ai_usage_events is an append-only log of every AI call, so this cost grows without bound as review volume grows — this is a real, currently-active scalability ceiling, not a hypothetical: at higher review volume the full-rebuild cost will eventually approach or exceed the export interval itself, causing overlapping runs to stack up.
Requirements
- Track a persisted export cursor (e.g. max
created_at/rowid already exported) instead of truncating and rebuilding the whole output DB every cycle.
- For
ai_usage_events (append-only): only export rows newer than the cursor, INSERT (not full reload) into the persistent output DB.
- For
review_targets (mutable — status/verdict change over time): only re-sync rows whose source updated_at is newer than the last export, via UPSERT/INSERT OR REPLACE keyed on (repo, number).
- Keep the existing PRAGMA quick_check integrity gate and the "preserve last good DB on source-read failure" behavior.
- Handle the schema-still-being-created / first-run case (no prior cursor) by falling back to a full export once.
Deliverables
- Rewritten
scripts/export-grafana-reporting-db.sh (or a companion script) using an incremental cursor for both the SQLite-source and Postgres-source code paths.
- A cursor-storage mechanism (e.g. a small marker table/file in the reporting output dir) that survives container restarts.
Acceptance criteria
- A representative before/after
docker stats/docker system df snapshot on a real self-host instance shows materially reduced block I/O per export cycle at steady state.
- Grafana dashboards reading the reporting DB show no missing/stale data versus the prior full-rebuild behavior.
- Existing reporting-exporter tests (if any) plus new tests cover the incremental cursor logic, including the first-run full-export fallback.
Parent: #1667
Context
scripts/export-grafana-reporting-db.sh(run everyGRAFANA_REPORTING_EXPORT_INTERVAL_SECONDS— default 30s — byreporting-exporterindocker-compose.yml) rebuilds the entire Grafana reporting SQLite mirror from scratch on every cycle: it drops the temp DB, re-exports the fullpull_requests/review_audit-derivedreview_targetsview and the fullai_usage_eventstable (no incremental cursor, no upsert), then atomically replaces the output file.Measured live on edge-us-01 (
docker stats): this container uses ~2MB of RAM but has generated 91.3GB of cumulative block I/O in 37 hours (~2.5GB/hour).ai_usage_eventsis an append-only log of every AI call, so this cost grows without bound as review volume grows — this is a real, currently-active scalability ceiling, not a hypothetical: at higher review volume the full-rebuild cost will eventually approach or exceed the export interval itself, causing overlapping runs to stack up.Requirements
created_at/rowid already exported) instead of truncating and rebuilding the whole output DB every cycle.ai_usage_events(append-only): only export rows newer than the cursor,INSERT(not full reload) into the persistent output DB.review_targets(mutable — status/verdict change over time): only re-sync rows whose sourceupdated_atis newer than the last export, viaUPSERT/INSERT OR REPLACEkeyed on(repo, number).Deliverables
scripts/export-grafana-reporting-db.sh(or a companion script) using an incremental cursor for both the SQLite-source and Postgres-source code paths.Acceptance criteria
docker stats/docker system dfsnapshot on a real self-host instance shows materially reduced block I/O per export cycle at steady state.Parent: #1667