AI usage analytics: make cost/token reporting trustworthy, then make it sliceable

## Summary

MJ's AI run logging is rich — `AIPromptRun` alone carries token classes, cache read/write, per-phase latency, failover and validation counters, and a properly-designed pricing chain through `AIModelCost` → `BasePriceUnitType`. What sits on top of it does not do that data justice: **every figure on the AI Analytics dashboard today is wrong, in both directions, and looks authoritative.**

Separately, the dimensions people actually want to slice by — per user, per team/role, per tenant, per application — are **not computable at all**, because `AIPromptRun` carries no user key and no agent-run key.

This plan does two things, in order:

1. **Make the numbers we already show true.** Non-negotiable, and worth doing even if we never build anything new.
2. **Make usage sliceable by any dimension**, using infrastructure MJ already owns (`@memberjunction/materialization`, `MJ: Queries`, the scheduling engine) rather than a new pipeline.

Everything below was verified against `next` at the time of writing; file:line references are included so each claim can be re-checked rather than taken on trust.

---

## Part 0 — Evidence: what is broken today

### 0.1 Every dashboard number is silently capped at 1,000 rows

All seven analytics components each run their own `RunView` against raw `MJ: AI Prompt Runs` and aggregate client-side. None passes `MaxRows` or `IgnoreMaxRows`:

- `packages/Angular/Explorer/dashboards/src/AI/services/ai-instrumentation.service.ts:205-231`
- `.../analytics/cost-budget/cost-budget.component.ts:715-740`
- plus `model-performance`, `usage-patterns`, `error-analysis`, `prompt-runs`, `agent-runs`

`IgnoreMaxRows` defaults to `false` (`packages/GraphQLDataProvider/src/graphQLDataProvider.ts:914`), so the entity's `UserViewMaxRows` applies — and that is **1000** for `MJ: AI Prompt Runs` (baseline `Entity` row, `migrations/v5/B202607091514__v5.46.x__Baseline.sql:34952`).

Worse: two of the four instrumentation queries have **no `ORDER BY`**, so it is an arbitrary 1,000 rows. "This Month's Spend" on any busy instance is a sample of unknown composition presented as a total.

### 0.2 The agent panel double-counts every sub-agent

`AIAgentRun.TotalCost` is **subtree-inclusive**, not own-cost. `calculateTokenStats()` adds `step.SubAgentRun.TotalCost` for every `Sub-Agent` step:

```
packages/AI/Agents/src/base-agent.ts:13918-13926
} else if (step.StepType === 'Sub-Agent' && step.SubAgentRun) {
...
totalCost += step.SubAgentRun.TotalCost || 0;
```

The agent-run panel then sums `TotalCost` across **all** agent runs in the window with no `ParentRunID IS NULL` filter (`.../analytics/agent-runs/agent-run-analysis.component.ts:703`). A three-deep agent tree is counted three times.

So we currently ship a prompt panel that under-reports (0.1) next to an agent panel that over-reports (0.2). They will never reconcile, and there is no way for a user to tell which is closer to the truth.

### 0.3 Unpriced runs are silently counted as free

`BaseAIEngine.CalculateCost` is carefully written to **refuse** rather than write a confident zero — four separate guards, each logging why, each returning `null` (`packages/AI/BaseAIEngine/src/BaseAIEngine.ts:660-727`). `Cost IS NULL` therefore means "we could not price this"; `Cost = 0` means "genuinely free". That is a good distinction and it is thrown away one layer up:

- `.../cost-budget/cost-budget.component.ts:831-832, 918, 948, 971, 1014` — `s + (r.Cost ?? r.TotalCost ?? 0)`
- `metadata/queries/SQL/calculate-ai-agent-run-cost.sql:31` — `COALESCE(SUM(prc.TotalCost), 0)`

A month in which no model had an active rate row renders identically to a month that cost nothing.

### 0.4 The `*Rollup` columns are usually NULL

`TotalCostRollup` / `TotalTokensUsedRollup` / `TotalPromptTokensUsedRollup` / `TotalCompletionTokensUsedRollup` on `AIAgentRun` are **never written by `BaseAgent`**. The only production writer is task-graph settlement:

- `packages/TaskGraph/src/TaskGraphDispatcher.ts:1406` (`rollUpCostToSubmittingRun`)
- → `packages/TaskGraph/src/TaskClaimStore.ts:153-169` (column-scoped raw `UPDATE`)

For ordinary agent runs they are NULL. `SUM(TotalCostRollup)` under-reports to near zero. Correct consumers use `TotalCostRollup ?? TotalCost`.

**Feedback-loop hazard, already documented in the repo:** `metadata/queries/SQL/get-agent-run-tree.sql:20-31` warns that this query is what *writes* the rollup, so reading the rollup from it would make the column an input to its own computation — *"every re-settlement would fold the previous total back in and inflate it, compounding, with no error and no visible symptom until someone questions a bill."* Any aggregate built here must respect the same rule.

### 0.5 `RunType='ParallelParent'` is never written

Repo-wide, the only two occurrences are in a display `switch` (`packages/Angular/Explorer/core-entity-forms/src/lib/custom/AIPromptRuns/ai-prompt-run-form.component.ts:436,451`). The parallel coordinator writes `ParallelChild` and `ResultSelector`; the consolidated parent keeps the `'Single'` column default.

Consequence: **parallel parents are indistinguishable from ordinary runs.** You cannot exclude them by `RunType`; the only way to identify one is "has children pointing at me". Since the consolidated parent takes the selected child's cost as its own (`packages/AI/Prompts/src/AIPromptRunner.ts:1221`), a naive `SUM(Cost)` double-counts the winning arm of every parallel group. (`JudgeID` and `JudgeScore` are likewise never written.)

### 0.6 Three classes of spend are invisible, not merely unpriced

- **Batch pricing is unreachable.** `ProcessingType` is hardcoded to `'Realtime'` — `packages/MJCoreEntitiesServer/src/custom/MJAIPromptRunEntityServer.server.ts:234`, comment *"For now, assume all prompt runs are realtime"*. Batch rate rows (typically ~50% cheaper) can never be selected, so batch work is over-reported.
- **Non-token modalities record nothing.** `UsageTypeID` / `InputUnitsUsed` / `OutputUnitsUsed` landed in `migrations/v6/V202608301800__v6.1.x__AIPromptRun_Continuous_Units.sql` with the full costing and guard path built — but have **no production writers**. Every transcription and image run is uncosted.
- **Action spend never reaches any total.** `ActionExecutionLog` has no cost, token, or duration column, and `calculateTokenStats()` only sums `Prompt` / `Compaction` / `Sub-Agent` steps — an `Actions` step contributes exactly zero.

### 0.7 MJ's own result-cache savings are unmeasurable

`CacheHit` is set to `false` at all three prompt-run creation sites and **never set to `true`** anywhere; `CacheKey` is never written at all. MJ's result cache lives in a separate entity and does not write back. So "cache savings" today reflects *provider* prompt caching only (which is genuinely tracked, via `TokensCacheRead/Write`).

### 0.8 There is no user, tenant, or application dimension

- `AIPromptRun` has **no `UserID`** and **no `AgentRunID`**. `AgentRunID` was deliberately dropped by `migrations/v5/V202607241645__v5.50.x__Break_CodeGen_Cycle_Remove_PromptRun_AgentRunID.sql` to break a CodeGen cycle: `AIAgentRun.ConversationDetailID → ConversationDetail.SummaryPromptRunID → AIPromptRun.AgentRunID → AIAgentRun`.
**That cycle no longer exists.** `ConversationDetail.SummaryPromptRunID` has since been replaced by the `ConversationCompactionRun` join table, and `ConversationDetail` today has no FK to `MJ: AI Prompt Runs` at all. The documented blocker is gone — re-test rather than assume, but this looks recoverable.
- Attribution therefore runs through `AIAgentRunStep.TargetLogID` where `StepType='Prompt'` — an **untyped polymorphic column with no FK**. A prompt run not invoked by an agent has no user or conversation attribution at any remove.
- `AIAgentRun.CompanyID` exists but is documented for memory scoping and **does not cascade to sub-agents** (`base-agent.ts:7248-7282` omits it), so subtrees are tenant-blank even when the root is not.
- There is **no `EnvironmentID`** on any run entity. (`MJ: API Key Usage Logs` has an `ApplicationID`; AI runs have no equivalent.)

### 0.9 The read path is shaped wrong for analytics

`vwAIAgentRuns` ends in two per-row recursive TVF calls:

```
migrations/v5/V202606122000__v5.41.x__Agent_InFlight_Memory_Writes.sql:3765-3768
OUTER APPLY [fnAIAgentRunParentRunID_GetRootID]([a].[ID], [a].[ParentRunID]) AS root_ParentRunID
OUTER APPLY [fnAIAgentRunLastRunID_GetRootID]([a].[ID], [a].[LastRunID]) AS root_LastRunID
```

Fine for a detail screen; pathological for a scan. Indexes are operational, not analytical: `AIPromptRun` has `(AgentID, RunAt)`, `(PromptID, RunAt)`, `TestRunID` — nothing on `ModelID`, `VendorID`, `UserID`, or `RunAt` alone. `AIAgentRun` has nothing on `UserID` or `CompanyID`.

There are **no aggregate SQL views** over AI runs anywhere in `migrations/` (verified by scanning every view body containing `AIPromptRun`/`AIAgentRun` with a `GROUP BY` — zero hits), **no rollup tables**, and exactly **one** saved AI cost query, which is single-run and parameterized. The `AI` query category exists and is empty.

### 0.10 Precision is inconsistent on the same row

| Column | Type |
|---|---|
| `AIPromptRun.Cost` | `decimal(19,8)` |
| `AIPromptRun.TotalCost`, `.DescendantCost` | `decimal(18,6)` |
| `AIAgentRun.TotalCost` | `decimal(18,6)` |
| `AIAgentRun.TotalCostRollup` | `decimal(19,8)` |

`TotalCost = Cost + DescendantCost` is stored two decimal places coarser than its own input. At sub-cent per-call costs this rounds systematically toward zero.

---

## Part 1 — Settle a cost basis (blocks everything else)

The codebase currently holds four mutually inconsistent answers to *"what did this cost?"*:

| Column | Semantics | Written by |
|---|---|---|
| `AIPromptRun.Cost` | own spend; NULL when unpriceable | `MJAIPromptRunEntityServer.Save()` |
| `AIPromptRun.TotalCost` | `Cost + DescendantCost` | same |
| `AIAgentRun.TotalCost` | **subtree-inclusive** | `BaseAgent` on terminal save |
| `AIAgentRun.TotalCostRollup` | subtree-inclusive, **usually NULL** | `TaskGraphDispatcher` only |

The existing saved query sums `pr.TotalCost`; the instrumentation service reads `Cost`. Neither is wrong alone; together they guarantee two dashboards that disagree.

**Decision to make and then write down:** the additive basis is **own-cost at the prompt-run grain**. Every rollup above it is either inclusive (so `SUM` double-counts) or unwritten. Rollups get **derived from the hierarchy at query time**, never summed from stored inclusive columns.

**Deliverable:** a short doctrine doc — probably `guides/AI_USAGE_ANALYTICS_GUIDE.md` — stating the basis, the `NULL`-means-unpriced rule, and the "never read a rollup column into an aggregate" rule (cross-referencing the `get-agent-run-tree.sql` warning). Every later workstream cites it.

---

## Part 2 — Fix the numbers we already show

Independent of any new feature. Do this first.

- Bound or server-aggregate all seven components' row pulls. At minimum an explicit `MaxRows` with a visible "showing a sample of N" state; the real fix is Part 5/6, but **a visibly-capped number beats a silently-capped one**.
- Add `ParentRunID IS NULL` to the agent-run aggregate (0.2), or switch it to a derived subtree rollup.
- Stop collapsing `NULL` cost to `0`. Carry unpriced separately (Part 3).
- Exclude parallel parents from `SUM(Cost)` (0.5). Until `ParallelParent` is written, this means `NOT EXISTS (SELECT 1 FROM AIPromptRun c WHERE c.ParentID = p.ID)`.
- Fix `RunType='ParallelParent'` at the write site in `ParallelExecutionCoordinator` so the cheap filter becomes available going forward, and backfill.

**Acceptance:** on a seeded database with >10k prompt runs, the prompt-run panel and the agent-run panel report the same total cost for the same window, and that total matches a hand-written `SUM` over own-cost.

---

## Part 3 — Priced coverage as a first-class metric

A cost dashboard that cannot state its own coverage is not trustworthy. Surface three buckets alongside every cost figure:

- **Unpriced** — `Cost IS NULL`: no active `AIModelCost` row for that model/vendor/measure. (`BaseAIEngine` already logs each occurrence.)
- **Unmeasured** — non-token modality with no recorded units (0.6): runs whose true cost is structurally unknowable today.
- **Unattributed** — action spend (0.6): known to exist, never priced.

Render as "covers 94% of runs / 97% of tokens" next to the total. If coverage drops, that is an alert, not a footnote.

A useful hook already exists: `packages/TestingFramework/integration-test-suite/src/checks/ai-cost.checks.ts` includes an uncosted-run measure audit.

---

## Part 4 — Close the dimension gaps (migration)

Additive and backfillable.

**On `AIPromptRun`:**
- `AgentRunID` — restore the real FK (0.8). Highest-leverage single change: turns agent→prompt attribution from a polymorphic filtered join into one hop. **Re-run CodeGen's cycle detector first**; there is a pre-existing `AIAgentRun ↔ ConversationDetail` two-cycle it evidently tolerates, so confirm empirically rather than reasoning from the old migration header. If it still objects, fall back to a non-FK indexed column plus a documented join contract.
- `UserID` — without it, per-user is impossible for any non-agent prompt run.
- Tenant key — see open decision below.

**On `AIAgentRun`:**
- Fix the `CompanyID` sub-agent cascade in `ExecuteSubAgent` and backfill existing subtrees from their root.

**Both:**
- Align cost precision on `decimal(19,8)` (0.10).
- Analytics indexes: `AIPromptRun (RunAt)`, `(ModelID, RunAt)`, `(VendorID, RunAt)`, `(UserID, RunAt)`; `AIAgentRun (UserID, StartedAt)`, `(CompanyID, StartedAt)`.

**Backfill:** `AgentRunID` from `AIAgentRunStep` where `StepType IN ('Prompt','Compaction')`; `UserID` from the resolved agent run.

Standard MJ migration rules apply — CodeGen tail in the same migration, apply-time `Sequence`, `sp_addextendedproperty` on every business column, no `__mj_*` columns, no hand-written FK indexes.

---

## Part 5 — One AI usage semantic layer

A single narrow fact view, `vwAIUsageFacts`, one row per prompt run, built over the **base tables** (not the base views — see 0.9), carrying only:

- **Time:** `RunAt`, plus hour and day bucket keys
- **Dimensions:** agent, agent type, agent root, prompt, model, vendor, user, tenant, configuration, source kind
- **Measures:** prompt / completion / cache-read / cache-write tokens, input & output units, own cost, currency, latency (queue / prompt / completion / first-token)
- **Flags that carry semantics no column currently expresses:**
- `IsPriced` — so unpriced never silently becomes zero
- `IsParallelParent` — derived, because `RunType` cannot tell you (0.5)
- `SourceKind` — collapse `ScheduledJobRunID` / `ConversationID` / `TestRunID` into one enum so *"how much of our bill is tests?"* is one filter. That question alone usually reprices people's understanding of their spend.

**Derived dimensions — no new capture needed:** role and team via `UserRole → Role`; org rollup via `User.EmployeeID → Employee.Supervisor`.

All seven components, exports, Skip questions and agent self-reporting read this one thing. Today they each fetch overlapping raw rows independently.

---

## Part 6 — Materialize the aggregates (reuse, don't build)

MJ already owns the whole mechanism — this workstream is mostly configuration:

- `@memberjunction/materialization` — snapshot tables, atomic shadow-swap, `FullRebuild` / `Incremental` / `DirtyGroupRecompute`, watermarks, `KeyColumns`
- `MJ: Materialized Results` + the `DataSource: 'Materialized'` read redirect with `Live` fallback
- `MJ: Queries` — `IsMaterialized`, parameters, permissions (`UserCanRun` checks role **and** entity `CanRead` **and** composition dependencies), dialect variants, `{{query:"..."}}` composition
- `@memberjunction/scheduling-engine` — the cron refresh driver

Define parameterized, time-bucketed aggregate Queries in the (currently empty) **`AI` query category**:

| Grain | Dimensions | Strategy |
|---|---|---|
| Hourly | agent, prompt, model, vendor, user, tenant, configuration, source kind | `Incremental` on a `RunAt` watermark |
| Daily | above + agent type + agent hierarchy root | `DirtyGroupRecompute` |

Measures: run count, success/fail, tokens by class, cache read/write, own cost, unpriced count, p50/p95 latency, time-to-first-token.

Hourly incremental is nearly free — prompt runs are append-only and never backdated. Pre-aggregation makes "last 90 days by user by model" a few hundred rows server-side instead of an unbounded client fetch.

**This also protects us from retention.** `packages/Archiving` does field-level archiving, and the run tables carry very large `NVARCHAR(MAX)` columns (`Messages`, `Result`, `AgentState`, `PayloadAtStart/End`). Once those start being archived or purged, pre-computed aggregates are the only history that survives. Better to have them before we need them.

Follow `.claude/skills/scaffold-mj-dashboard/SKILL.md` conventions; note `RunQueryParams.SQL` must never be set from user or agent input — stored queries by name/ID only.

---

## Part 7 — One pivot surface instead of seven bespoke panels

Once the grain is uniform: pick **measure** (cost / tokens / runs / latency / cache-hit / unpriced %) × **group-by** (any dimension) × **secondary split** × **time grain** × **window + comparison period**.

Drill path: aggregate → agent run → run tree → prompt run → messages.

Every new dimension added to the fact layer then appears automatically rather than needing a new panel.

**Reuse, do not rebuild:** `AIInstrumentationService`'s `DashboardKPIs` / `TrendData` / `ChartData` contracts, `cache-metrics.ts`, `charts/time-series-chart`, `charts/performance-heatmap`, `widgets/kpi-card`, the existing treemap and anomaly detection, CSV export, and `UserInfoEngine`-backed filter persistence.

CI gates that will fail if skipped: design tokens (no hardcoded hex), `mjButton`, `@if`/`@for`/`@switch`, chrome trio, `OnPush`, no `new Metadata()` / `new RunView()`, PascalCase public members, and the `SetAgentContext` / `SetAgentClientTools` wiring every dashboard needs.

---

## Part 8 — Budgets

There is no budget, quota, or spend-limit entity anywhere in the schema. The "Cost & Budget" panel has budget in its name and nothing to compare against.

The hard half is already done: `MJ: AI Agents` carries `MaxCostPerRun`, `MaxTokensPerRun`, `MaxIterationsPerRun`, `MaxTimePerRun`, enforced mid-run via `checkExecutionLimits` (`base-agent.ts:4808`). What is missing is the **periodic** dimension: a budget scoped to (agent | user | role | tenant | application) × period, with a threshold and an action (notify / throttle / block).

Once the hourly aggregate exists, evaluation is a cheap scheduled job against it rather than a scan.

---

## Open decisions (need an answer before Part 4 lands)

1. **Tenant key.** Promote `AIAgentRun.CompanyID` to a real tenant dimension (fix the cascade, backfill, index, denormalize onto prompt runs) — or introduce a separate first-class tenant/environment key? `CompanyID` is currently documented as memory-scoping and is sparsely populated, so promoting it means committing to populating it everywhere.
2. **Application/environment dimension.** Worth adding to runs now, or derive from `ConversationID → Conversation.EnvironmentID` where present and accept the gaps?
3. **Retroactive repricing.** Cost is computed once at save time and frozen, and `ShouldCalculateCost()` short-circuits on `Cost > 0` (`MJAIPromptRunEntityServer.server.ts:200`), so a provider-reported cost permanently wins over the rate table. Frozen cost is right for auditability. Do we also want an explicit, separately-recorded reprice job for corrected rate rows — or is "the price then was the price" the final answer?

---

## Sequencing

1. **Part 1** — cost basis doctrine (small, unblocks everything)
2. **Part 2 + Part 3** — fix today's numbers and surface coverage — *do these regardless of whether the rest proceeds*
3. **Part 4** — migration: dimension keys, cascade fix, precision, indexes
4. **Part 5** — `vwAIUsageFacts`
5. **Part 6** — materialized aggregates on the existing refresh driver
6. **Part 7** — pivot surface
7. **Part 8** — budgets & alerts

Parts 1–3 are worth doing on their own merits: the figures on screen today are confidently wrong in both directions, and the cost of believing them is real. Parts 4–8 are the actual slice-and-dice.

---

## Out of scope

- Wiring producers for `UsageTypeID` / `InputUnitsUsed` / `OutputUnitsUsed` (transcription/image actions) — should be its own issue; this plan only needs to *report* the gap honestly.
- Adding cost/token columns to `ActionExecutionLog` — same; tracked here only as a named coverage bucket.
- Making `CacheHit` / `CacheKey` meaningful for MJ's own result cache.
- PostgreSQL migration counterparts (release-time toolchain concern, per `migrations/CLAUDE.md`).

## Definition of done

- Prompt and agent panels agree with each other and with a hand-written `SUM` on a >10k-run database.
- Cost figures are accompanied by a coverage percentage, and unpriced is visibly distinct from free.
- "Cost by user, by model, last 90 days" is answerable in the UI and returns in under a second.
- No analytics path reads `vwAIAgentRuns` / `vwAIPromptRuns` for aggregation, and no aggregate reads a `*Rollup` column.
- Unit + deterministic integration tiers pass; `ai-cost.checks.ts` extended to cover the new basis.