epic: scale — materialized analytics rollups + time-partitioned run history

Analytics (#107) is computed from raw run/deploy history. Today every Analytics load aggregates terminal history over a trailing window. That's correct and — with the partial indexes (DORA 00057, runs 00060 from #127) — right-sized for gocdnext's current internal scale. As history grows into the millions of runs, two structural levers are needed. They are complementary and should land in this order; neither belongs in a dashboard PR.

Where we are

• Live aggregation over runs / deployment_revisions filtered by a trailing finished_at window, grouped by project label.
• Partial indexes serve the window scans: idx_deployment_revisions_dora_window (00057), idx_runs_reliability_window (00060). This is the current, sufficient fix — not superseded by this epic until volume actually hurts.

Phase 1 (scale) — materialized analytics rollups (the real Analytics win)

A daily rollup table (or a few), keyed by pipeline / project / label-value, holding the per-day aggregates (deploys success/fail + lead-time samples; runs success/fail + duration/queue samples). Refreshed on run/deploy terminal (LISTEN/NOTIFY or the existing scheduler tick), with a backfill.

• Read path swaps "scan raw history" for "sum N daily buckets" → Analytics becomes O(days), not O(runs).
• Already anticipated by the #107 architecture note ("server-side aggregation … materialized/cached … NOT the client-side load-all-runs pattern").
• Percentiles are the wrinkle: medians don't sum across buckets. Options — store t-digest/quantile sketches per bucket, or keep p50 as an approximate read over a bounded recent window. Decide here.

Phase 2 (scale) — time-partition the history tables (storage / retention, not a dashboard optimization)

RANGE-partition runs (and job_runs, deployment_revisions) monthly on created_at.

• The crux — identity contract, not "just create partitions": Postgres requires the partition key in every UNIQUE/PK. runs PK id → (id, created_at); UNIQUE(pipeline_id, counter) → must include created_at. FKs from children to runs(id) lose their target (no lone unique on id) → children must denormalize created_at and reference (id, created_at). This ripples through job_runs, deployment_revisions, and anything else FK'd to runs.
• Cheap greenfield, painful later. Doing this before prod data = a clean migration; after = an online repartition. Capture now while it's fresh; do not pull into the current release.
• Wins once done: partition pruning on window scans; retention via DETACH + DROP (vs DELETE) — aligns with the existing retention sweeper; per-partition VACUUM/index bloat.
• Needs partition automation (pre-create next month — pg_partman or a scheduler task).

Non-goals

• Custom BI / arbitrary report builder.
• Blocking the current release. The #127 index is the right-sized fix for now.

Order

1. Index + env note + OpenAPI — shipping in #127 (#107 phase 3).
2. Phase 1: materialized daily rollups — immediate Analytics win.
3. Phase 2: time partitioning — structural storage/retention change; do while greenfield to avoid the PK/FK rework on live data.

Related: #107 (analytics epic).

Start by reading related issue #107 and the schema or migration definitions for runs, job_runs, and deployment_revisions, including the index work referenced in #127. Define the Phase 1 rollup design and Phase 2 partitioning identity and foreign-key changes; done means the ordering, percentile approach, retention strategy, and required migration work are documented without pulling either phase into the current release.