[Epic]: Multi-source usage ingestion — one store, a hypertable per grain, source is a dimension

### Executive Summary

Extend the usage store and its APIs from one source (the AI gateway's Envoy access logs) to every governance source (Claude Code, Codex, OpenCode, GitHub Copilot, Microsoft Foundry, and the rest of governance RFC-0003's taxonomy) — one hypertable family per **grain** (request / execution / day / seat), `source` as a dimension column, a per-source normalizer registry on an authenticated ingest surface, and one closed typed query endpoint per grain.

### The Plan (cross-repo tracking)

> This epic is the single tracking point for BOTH repos. Governance-side items are listed here with full refs; gov#196/gov#197 link back to this epic. Check items off here as they land.

```mermaid
flowchart TD
P0["Phase 0 — Decision ✅\nADR-0027 + gov ADR-0014 merged"]
IMG["Timescale-capable image\non usage CNPG tenant (#489)"]
REQ["Request-grain rewrite\nP0s + F-fixes (#489/#549 under #491)"]
G582["#582 execution grain"]
G583["#583 day/seat grain"]
G84["gov#84 collector validates\nauthz issuer"]
G585["#585 authenticated ingest\n+ edge fan-out"]
G589["#589 raw S3 archive\n+ generic replay"]
G584["#584 normalizer registry\n(+ opencode)"]
G196["gov#196 governance-ctl\nemits OTLP via collector"]
G586["#586 per-grain query APIs\n(closes #570, #578)"]
G587["#587 continuous aggregates"]
G588["#588 cutover: migrate,\nassert counts, decommission"]
G197["gov#197 backlog re-scope"]
CONS["Consumers: console #508,\nseeding #528, dashboards"]
P0 --> IMG
P0 --> G84
IMG --> REQ
IMG --> G582
IMG --> G583
G84 --> G585
G585 --> G589
G585 --> G584
G582 --> G584
G583 --> G196
G585 --> G196
G582 --> G586
G583 --> G586
G586 --> G587
G584 --> G588
G196 --> G588
G587 --> G588
G588 --> G197
G586 --> CONS
```

**Phase 0 — Decision** ✅ done 2026-08-31
- [x] ADR-0027 merged (#579; closed #535)
- [x] Governance mirror ADR-0014 merged (ADORSYS-GIS/lightbridge-governance#198; closed ADORSYS-GIS/lightbridge-governance#182)

**Phase 1 — Storage foundation (authz).** Gate: the Timescale-capable image on the usage CNPG tenant (#489's infra half) — decide once, everything reuses it.
- [ ] Timescale image decided + deployed to the usage tenant (#489)
- [ ] Request-grain rewrite folding the P0s and F-fixes: real hypertable, 90d retention, compression, `cost_micro_usd BIGINT NULL`, dedup key, allowlisted tail, `usage_identities` (#489, #549, under #491)
- [ ] #582 — execution-grain hypertables
- [ ] #583 — day/seat-grain generalized tables

#582/#583 run in parallel once the image lands.

**Phase 2 — The authenticated door (cross-repo).** Starts in parallel with Phase 1 (the auth chain needs no tables); normalizers land once their target grain exists.
- [ ] ADORSYS-GIS/lightbridge-governance#84 — collector validates the authz issuer (unblocks the CLI leg; first live 200)
- [ ] #585 — authenticated multi-source ingest, edge fan-out topology (collector → Alloy ∥ authz ∥ archive)
- [ ] #589 — raw OTLP S3 archive on the collector + generic replay job
- [ ] #584 — normalizer registry (port claude_code/codex/foundry, write opencode) — after #582
- [ ] ADORSYS-GIS/lightbridge-governance#196 — governance-ctl emits day-grain reports as OTLP through the collector — after #583 + #585

**Phase 3 — Query surface (authz).** After Phase 1.
- [ ] #586 — per-grain query APIs, `source` dimension, grain guard (also closes #570 ownership check and #578 multi-scope)
- [ ] #587 — continuous aggregates per KPI measure, refresh proven

**Phase 4 — Cutover and decommission.** Last, deliberately.
- [ ] #588 — migrate governance-store rows in (one-time governance-ctl S3 NDJSON backfill — the only source of pre-collector history), counts asserted, gov telemetry tables dropped
- [ ] ADORSYS-GIS/lightbridge-governance#197 — backlog re-scope dispositions across the gov epics
- [ ] Consumers repointed: console usage graphs (#508), seeding via real ingest (#528), Grafana/dashboard successors (ai-helm#879/#880)

**Exit criteria** (same as Success metrics below): a second source lands with zero DDL in CI; every grain table verified in `timescaledb_information.hypertables` in the target cluster with a retention policy that has run; ADORSYS-GIS/lightbridge-governance#36 (spend per engineer across all sources) answerable from this store alone; new vendor-specific connectors written: 0.

### Strategic Intent

ADR-0027 resolves #535 as consolidation: `lightbridge-authz-usage` is the system of record for all AI-usage telemetry, and `lightbridge-governance` keeps the collectors as clients. The product this serves is the governance reporting scaffold — headline query: total AI spend per engineer across all sources (lightbridge-governance#36) — from **one** store with one money discipline (integer micro-USD, NULL = unknown).

### Problem Statement

Today `usage_events` is a single mixed-grain table fed by one unauthenticated in-cluster emitter, with two open P0s (#489 not-a-hypertable/prod-lacks-Timescale, #549 no retention). The governance repo built a second, competing store (executions/model_calls/tool_calls + vendor-named Copilot day tables) on an equally Timescale-less cluster. Neither can answer the cross-source questions; both were about to build the same foundation twice.

### Desired Outcome

- Four grain hypertable families, all asserted-not-assumed hypertables with compression + 90d retention, fail-loud migrations (no `EXCEPTION WHEN OTHERS`).
- Adding a source = one normalizer + one registry row, **zero schema change** (governance#167's acceptance criterion, held estate-wide).
- Developer-attributed telemetry only enters through authenticated ingest; source identity from credential, never payload (governance ADR-0013 invariant 2).
- One closed typed query endpoint per grain, `source` filterable/groupable, cross-grain aggregation unrepresentable; continuous aggregates per KPI measure.
- Governance store's existing rows migrated in, counts asserted, old tables dropped.

### Scope (In / Out)

**In:** grain tables + request-grain rewrite (folds F1–F6 fixes), execution-grain hierarchy, generalized day/seat grain, `usage_identities` PII isolation, normalizer registry (claude_code, codex, microsoft_foundry, opencode), authenticated ingest, per-grain query APIs (+ #570 ownership check, #578 multi-scope), continuous aggregates, governance-store migration.
**Out:** acceptance-telemetry schema and vendor connectors beyond the four push sources (governance#95/#96–#105 stay in the governance repo as collector work); gateway auth rework beyond current posture; any content capture (permanently rejected per #491).

### Source of truth (links)

ADR-0027 (PR #579) · #535 · #491 · #489 · #549 · governance ADR-0013 · governance#167 · governance#30 · `docs/research/2026-08-25-genai-usage-ingestion.md`

### Stakeholders

Repo owner (Stephane); lightbridge-governance maintainers (collectors become clients); converse-frontends#327 dashboards; ai-helm observability.

### Key Assumptions

1. The usage CNPG tenant gets a Timescale-capable image (#489's open infra question — answered once, reused by every grain table).
2. Per-collector projected ServiceAccount tokens (governance#169 pattern) are usable for in-cluster ingest auth without waiting on authz-idp M2M (#534).
3. Copilot raw NDJSON in S3 replays cleanly through the new day-grain path (governance-ctl `replay` exists and works).

### Constraints

Integer micro-USD everywhere, NULL = unknown, never 0 (budget's `Spend::Unavailable` contract). The budget spend-reader seam (`/usage/v1/spend/query`, `Option` micro-USD) must stay byte-stable through the cutover. Hard cutover per house style — no dormant parallel paths.

### Risks

- The request-grain rewrite touches the outage-adjacent table (#549) — land retention/compression in the same change, not after.
- Silent-success migrations are the house failure mode — every hypertable assertion gets a sabotage test.
- Cross-repo sequencing: the governance mirror ADR and re-scope must land with ADR-0027, or the two repos re-diverge.

### Success metrics

- A second source lands in an existing grain table with zero schema change, demonstrated in CI.
- `timescaledb_information.hypertables` lists every grain table **in the target cluster**; a retention policy has demonstrably dropped a chunk.
- governance#36's query answered from this store alone.
- New vendor-specific connectors written: 0 (governance#30's metric).

### Child User Stories

- #582 — Execution-grain hypertables (executions, model_calls, tool_calls) in the usage store
- #583 — Generalized day-grain and seat-grain tables — adding a source needs no schema change
- #584 — Push-connector normalizer registry keyed by source — port claude_code/codex/foundry, add opencode
- #585 — Authenticated multi-source ingest — source identity from credential, never payload
- #586 — Per-grain query APIs with source as a dimension (grain guard; closes the scope-ownership and multi-scope gaps)
- #587 — Continuous aggregates per KPI measure, refresh proven, aggregates never span grains
- #588 — Cutover: migrate the governance store's rows in, verify counts, decommission
- #589 — Raw OTLP archive on the collector — S3 exporter plus one generic replay job

### Design addendum (2026-08-31): ingest topology and archive

Settled with the repo owner: push ingest rides the authenticated edge OTEL collector, fanning out at the edge to Alloy/observability, the governed usage store, and a raw OTLP object-storage archive (#589) that makes field promotion backfillable for every source. The observability stores are explicitly NOT the archive (Mimir strips high-cardinality attributes; Tempo/Loki hold short observability retention with no re-emit path). Pull sources converge on the same door by emitting OTLP through the collector (governance#196). Allowlists are seeded generously (the 9-of-55 promotion datum reflects suppressed demand while Grafana was primary, not a ceiling); grain hypertables are space-partitioned by `source`.

### Human accountable owner

Stephane Segning Lambou

### AI Usage
AI-assisted (Claude): research and drafting. Reviewed and owned by Stephane Segning Lambou.