RFC: Analysis-oriented database schema

Related: #1383

## Problem

The dashboard must support historical and comparative analysis, not only current-state browsing. Today, large `builds` and `tests` tables mix stable definition data with per-run execution data. Important fields also live in JSON blobs, which limits indexing and aggregation.

## Proposal (direction, not final design)

Move toward a **star-schema-style** layout: narrow fact tables (runs) keyed to shared dimensions (commits, definitions). Early POC work suggests this can shrink hot rows and speed long-range queries.

| Surface | Current schema avg | Proposed schema avg | Faster |
| --- | --- | --- | --- |
| tree_listing_60d | 2.839s | 0.362s | proposed, ~7.8× |
| tree_by_checkout_5 | 0.058s | 0.033s | proposed, ~1.8× |
| notification_history | 13.018s | 4.900s | proposed, ~2.7× |
| hardware_tree_heads | 0.192s | 0.142s | proposed, ~1.35× |
| hardware_listing | 6.508s | 2.254s | proposed, ~2.9× |
| hardware_details_summary | 0.026s | 0.031s | current, marginal |
| hardware_details_full | 0.013s | 0.017s | current, marginal |

*Proposed schema* = star-schema POC read path (`build_runs` / `test_runs` fact tables), not the current monolithic `builds` / `tests` tables.

* It is important to point this is just a small experiment, and more analysis could bring further improvement for slower queries (some of those queries today, are pre cached to avoid slow compute).

We expect to migrate incrementally (expand → backfill → validate → cut over), not in one breaking change.

### 1. Split definitions from runs

- **Build/test definitions** — relatively static fields (e.g. config grain, test path).
- **Build/test runs** — execution facts: status, timestamps, lab/platform, mostly foreign keys.

`tests` in particular can grow very large; separating definition from run should keep fact tables smaller and cheaper to scan.

**Open:** exact definition grain. Community input welcome before we lock migrations.

### 2. Materialize structured fields

Promote frequently queried data out of JSON (`environment_misc`, large `misc` blobs) into typed columns or explicit relations where it measurably helps queries—especially hardware and platform listing.

**Open:** which extractions are worth the ingestion and storage cost; we will gate optional normalizations on query benchmarks, not assumptions.

### 3. Commit as a first-class dimension

Introduce a `commits` dimension (tree + repository + branch + hash) and link checkouts to it, so revision-scoped analysis does not depend on denormalized checkout text fields.

### 4. Daily rollups on runs

Pre-aggregate build and test runs by day (and existing rollup tables where they already help) to accelerate wide time-range dashboards.

**Trade-off:** rollups add ingestion work. If cost grows, we can reconcile periodically instead of on every write—feedback on acceptable latency vs query speed is useful.

## Risks

- **Ingestion:** more tables and enrichment (e.g. commit metadata) may increase per-submission cost. We will monitor ingest throughput and keep git/network work outside DB transactions.
- **Migration:** legacy APIs and denormalized tables (`tree_listing`, `hardware_status`, rollups) must keep working until new paths are validated.

## Feedback requested

Please comment on:

1. Definition vs run split.
2. Definition keys — checkout-scoped vs commit-scoped build grouping; test path + environment handling.
3. JSON materialization — which fields we need filterable or aggregatable?
4. Rollups — which time grains and dimensions matter for workflows? Is daily aggregation enough?

Alternative layouts are welcome. The goal is a schema that serves real analysis questions.

Start with related issue #1383 and the proposal's current build/test schema, then review how legacy APIs and denormalized tables such as tree_listing, hardware_status, and rollups are used. The issue names no files or tests and leaves definition grain, JSON extractions, and rollup behavior open; done would require an agreed design, validated benchmarks, and an incremental migration plan.

Written by the indexing model from the issue text.