The Trilogy Times

## Summary

New daily pipeline that ingests per-user-per-day spend and token usage from the Claude Enterprise Analytics API (user_cost_report + user_usage_report, grouped by product) into core_finance.ai_spend_claude_ai_chat_usage. Finalizes Spec 3 Part A.

- Per-day loop with 1-day windows (the per-user endpoints don't time-bucket / reject bucket_width).

- Cost stored verbatim from the API (amount/list_amount ÷100) — discount is not recomputed. Verified against live data: effective discount is exactly 10%/row, and per-user sums reconcile to the org cost_report total.

- 7-day trailing re-pull + is_provisional flag (API has ~3-day lag, reconciles ~30 days).

- BU left NULL on ingest, attributed downstream via email→dim_user.

## Verification

- ruff check clean, 16 unit tests pass.

- Shapes/units/grain confirmed against org org_0193Pqkb… (2026-05-12→18) — see spec A.7.

## Deploy checklist (do in order)

- [ ] Create the Redshift table — run scripts/sql/create_ai_spend_claude_ai_chat_usage.sql against finance_dw / core_finance before deploy (and GRANT SELECT to the consuming role/MCP_user if it'll be queried).

- [ ] Create the secret surtr/claude-ai-analytics-key (JSON {"api_key":"..."}, scope read:analytics, minted at claude.ai/analytics/api-keys by a Primary Owner).

- [ ] Confirm src/requirements.txt present (bundling rule) — ✅ included.

- [ ] CI green (ruff + pytest).

- [ ] Deploy: promote main → production (CD only deploys on the production branch).

- [ ] Post-deploy smoke test: single-day sync invoke, verify rows land + per-user sum matches org cost_report.

- [ ] Then rely on the daily cron(0 6 * * ? *) schedule.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

The deployed TF gateway pipeline failed every run on the Redshift COPY, and the non-atomic delete-then-COPY wiped the day it was loading (2026-05-20 went to zero). Root causes — all parquet-COPY incompatibilities our mocked tests never exercised against live Redshift:

1. Type mismatch: Athena exposes request_date_utc as varchar / request_hour_utc as int, but the table had DATE/SMALLINT. Redshift parquet COPY rejects varchar→DATE and int32→SMALLINT. → cast request_date_utc+report_date to DATE in the UNLOAD; request_hour_utc → INTEGER.

2. DEFAULT columns: reconciled/ingested_at were NOT NULL DEFAULT and omitted from the COPY column list — parquet COPY can't apply a DEFAULT for an omitted column. → made nullable, no default.

3. Non-atomic load: autocommit=True meant the DELETE committed and a failed COPY couldn't roll it back. → DELETE+COPY in one transaction, commit only after COPY succeeds.

## Validation

- Live end-to-end test: UNLOAD-with-casts → COPY into a DATE/INTEGER clone succeeded (670 rows, request_date_utc date, hours 0–23).

- ruff clean, 38 tests pass (added an atomic-rollback regression test).

## Prod state (already hotfixed via Data API — this PR makes the repo match)

- Table altered: request_hour_utc→INTEGER, reconciled/ingested_at nullable-no-default. Zero views touched (the 4 stacked savings views keep their DATE usage_date).

- 2026-05-20 re-ingested (table back to 5,134 rows).

- After merge + redeploy, the daily COPY will succeed.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

- Add a bulk Aerie read endpoint for per-operating-site P2 freshness

- Persist durable WUG/task activity timestamps on create/update/status changes

- Document the new /sync/aerie/listSiteFreshness contract

## Why

Aerie Accountability needs to classify operating sites as Current, Aging, Stale, or Never without N+1 detail calls. The freshness source must be Rhodes-owned P2 Quality Bar execution activity, not site metadata, notes, P1/buildout work, or request fetch time.

## Business Value

Aerie can now render an accurate portfolio freshness dashboard from one Rhodes read call, with freshness derived from durable operational activity timestamps.

## Test plan

- [x] CONVEX_DEPLOYMENT=dev:kindly-rook-978 npx convex codegen --typecheck disable

- [x] npx tsx --test convex/aerieHttpHelpers.test.ts convex/aerieProvisionPayload.test.ts convex/qualityBars.test.ts (55/55 passing)

- [x] git diff --check

- [ ] Full tsc currently has pre-existing unrelated failures in convex/rebl3DdReconciliation.ts and convex/scripts/backfillWrikeCurrentCapacity.ts

## Summary

- Source Operating Accountability freshness from Rhodes' listSiteFreshness endpoint

- Join freshness by Rhodes site id and remove the old chat-local status-update dependency

- Keep the dashboard export label aligned to P2 activity freshness

## Why

The Accountability view was showing every site as never updated because it was reading Aerie-local site status updates. For the Operating P2 team, "last update" should reflect durable activity under Rhodes Quality Bar work unit groups and tasks.

## Business Value

Operators can now see accurate site freshness in the Accountability view, with stale/aging/never buckets driven by the same Rhodes WUG/task activity the Operating team monitors.

## Test plan

- [x] pnpm --filter @bran/chat test lib/__tests__/rhodes-operating-server.test.ts app/api/operating-sites/__tests__/route.test.ts lib/__tests__/operating-sites.test.ts components/dashboards/school-ops/__tests__/school-ops-view.test.tsx

- [x] pnpm --filter @bran/chat typecheck

- [x] git diff --check

- [x] Browser spot check: Operating Accountability loads with All Sites 25, Never 0, and real last-update dates

- [x] Rhodes spot check: freshness endpoint returned 26 rows, with the extra row excluded by Aerie's existing test-site filter

## Summary

Builds a dedicated klair-scheduled-jobs AWS Fargate platform for klair-api batch jobs (QTD reports + future scheduled jobs), fully isolated from the live API on EC2. Adds --bu and --skip-refresh flags to enable fast single-BU dev iterations (~1.5 min vs ~15 min full fan-out).

Linear: [KLAIR-2661](https://linear.app/builder-team/issue/KLAIR-2661/migrate-qtd-report-crons-to-dedicated-klair-scheduled-jobs-ecs) (Budget v Actuals Reports project)

## What's in this PR

### Container platform

- klair-api/scripts/cron_entrypoint.py — Python entrypoint that fetches ENV_API_PROD from Secrets Manager, writes /app/.env, then os.execvps python <args>. Includes defensive backfill of MONTHLY_QTD_CRON_USER_EMAIL.

- klair-api/Dockerfile.jobs + klair-api/.dockerignore — image build (python:3.11-slim, uv-managed deps, RDS CA bundle baked in)

- klair-api/crons/README.md — runtime contract, how to add new scheduled jobs

### Operability flags (production behavior unchanged when flags absent)

- --bu <comma-separated> on monthly + weekly crons + bu_filter: set[str] | None on run_scheduled_reports(). Filter to specific BU/CF names. Raises on unknown name. Validates empty input early to avoid wasted Redshift refresh.

- --skip-refresh on monthly + weekly crons + skip_refresh: bool on run_scheduled_reports(). Skip the upstream Redshift sproc when set. EventBridge production rules don't pass this; intended for dev/smoke iterations only.

### Tests

- tests/scripts/test_cron_entrypoint.py — 7 tests, full unit coverage of entrypoint

- tests/monthly_qtd_report/test_orchestrator.py — 8 new tests for bu_filter + skip_refresh

- tests/monthly_qtd_report/test_cron_entrypoint.py — 7 new tests for monthly cron flags

- tests/monthly_qtd_report/test_weekly_cron_entrypoint.py — 7 new tests for weekly cron flags

Suite: 509 → 537 passing (+28). ruff format, ruff check, pyright all clean.

## AWS infrastructure (already created via CLI; not in this PR)

| Resource | Identifier | State |

|---|---|---|

| ECR repo | klair/scheduled-jobs | 3 image tags |

| ECS cluster | klair-scheduled-jobs | ACTIVE, FARGATE + FARGATE_SPOT |

| Task definition | klair-scheduled-jobs:1 | ARM64 / Graviton, 1024 CPU / 2048 mem |

| Task role | KlairScheduledJobsTaskRole | SM + KMS + DDB + Redshift + SES (least-privilege) |

| EB invoke role | EventBridgeScheduledJobsInvokeRole | ecs:RunTask + iam:PassRole |

| Log group | /ecs/klair-scheduled-jobs | 30-day retention |

| Monthly rule | klair-qtd-monthly-prod | cron(0 13 2 * ? *) — DISABLED |

| Weekly rule | klair-qtd-weekly-prod | cron(0 13 ? * MON *) — DISABLED |

IaC promotion deferred — current AWS state captured in the Linear ticket and the plan doc at docs/superpowers/plans/2026-05-15-scheduled-jobs-platform.md.

## Validation runs (all PASSED, exit 0)

| # | Run | Runtime | Outcome |

|---|---|---|---|

| 1 | EC2 SSM smoke (monthly, --period 2026-04) | 12 min | generated=21 failed=0 no_data=5 orphan_doc=0 |

| 2 | ECS smoke (monthly, full fan-out, after KMS perm fix) | 15 min | Same as #1 |

| 3 | ECS smoke (--bu IgniteTech --skip-refresh) | 1m 21s | generated=1 failed=0 |

| 4 | EventBridge-triggered fire (weekly, single-BU, skip-refresh) | 1m 25s | generated=1 failed=0 — proves EB → ECS path |

## Why ARM64

Docker --platform=linux/amd64 builds on Apple Silicon segfault inside QEMU during uv pip install. ARM64 (Graviton) Fargate avoids the cross-build pain, is ~20% cheaper, and the local image runs natively.

## Out of scope

- klair-prod ECS cluster cleanup (different purpose; deliberately not touched)

- Migrating live API to ECS (separate, larger project)

- Refactoring qtd_email_resend.py for ECS (manual ad-hoc tool)

- Path-filtered CI auto-rebuild on every main push (deferred; manual rebuild documented in crons/README.md)

## Test plan

- [x] pnpm test / pytest — 537 passing locally

- [x] ruff format + ruff check clean

- [x] pyright clean on changed files

- [x] Docker build succeeds locally

- [x] ECS RunTask end-to-end smoke (full fan-out)

- [x] ECS RunTask single-BU + skip-refresh smoke

- [x] EventBridge-triggered run end-to-end

- [ ] CI checks green (this PR)

- [ ] Post-merge: flip EventBridge rules to ENABLED when operationally ready

- [ ] Post-merge: add MONTHLY_QTD_CRON_USER_EMAIL to ENV_API_PROD Secrets Manager (entrypoint has a defensive fallback, but cleaner to land in the secret)

## Summary

Builds on [#2834](https://github.com/AI-Builder-Team/Klair/pull/2834) so the Monthly Financial Reporting Budget columns (Income Statement, EBITDA Reconciliation, Cash Flows — all three entities) read correct values directly from core_budgets.consolidated_budgets_and_actuals, eliminating the quarterly CSV upload as the steady-state source for Budget data.

Two query-shape changes do the heavy lifting:

- Income Statement / EBITDA version selector now uses a per-period MAX(budget_cycle_start) instead of a per-(business_unit, class, entity_type) MAX. The old per-tuple selector resurrected 2026-Q1 / 2025-Q4 / 2025-Q3 rows whenever Finance dropped a dimension between quarterly versions, inflating Apr-26 Group Recurring Revenue by ~$3M and PS/Other by ~$2.4M.

- Seven _apply_software_* helpers (Education cost-allocation back-add, IC revenue reclassification, bad-debt addback, acquisition addback, tax override, G&A addition, interest adjustment, other income) are extended to operate on Budget rows. They could only ever match Budget rows after #2834 populated account_name on the Budget side.

Cash Flow Net Income, Adjustments-to-NI (≈ Budget D&A), Cash start/end, and Change in Cash are populated for the Budget column too. The export's End-of-period and Change-in-Cash rendering now mirrors the UI's BS-direct logic (preferring the captured END_CASH record over the start+section-sum derivation) so the Google Doc matches the on-screen table.

## Business Value

Finance no longer needs to upload a Budget CSV every quarter to populate the MFR Budget columns. Group IS Apr-26 QTD Bud now matches Finance's expected screenshot exactly on Recurring Revenue ($32,512k), Professional services ($9,427k), Bad debt ($167), and D&A ($5,500k), with OpEx lines within $200/line. Software IS Apr-26 QTD Bud no longer shows the previous negative S&M / PE&S / G&A values that signaled an obviously broken Budget column. The on-screen Cash Flow Statement and the exported Google Doc now show consistent values for Change in Cash and End of Period — previously they could differ by ~$200 because the export used the section-sum formula while the UI used End − Start.

## Changes

- services/financial_data_service.py

- fetch_pnl_data Budget branch: dropped the three IS NOT DISTINCT FROM predicates on business_unit, class, entity_type so the MAX(budget_cycle_start) is per-period not per-tuple. Inline comment explains the legacy intent and why it backfired.

- Added _df_to_period_ds_map helper, sibling to _df_to_period_map, keyed by (reporting_period, data_source) so adjustment helpers can produce both Actual and Budget series in a single query.

- _apply_edu_cost_allocation_adjustment, _apply_software_ic_revenue_reclassification, _apply_software_edu_gaap_addback, _apply_software_ga_addition: removed WHERE data_source = 'Actual' and split writes across (rp, 'Actual', gaap) and (rp, 'Budget', gaap) keys in pnl_map.

- _apply_software_tax_override: all three sub-queries (Group tax, Edu Net Income, PI P&L) now return both data sources; the g_tax − e_ni × 21% − pi_pnl × 21% formula re-applies per (rp, data_source).

- _apply_software_interest_adjustment: CBA-side 71102 query extended to Budget; GL-side 71100/YYYYY query stays Actuals-only (the GL table has no Budget). Comments document the asymmetry.

- _fetch_software_other_income: extended to Budget — produces $0 for Budget because no Budget rows carry the PI account_names this helper filters on.

- services/cash_flow_service.py

- _compute_net_income: sums Budget rows over the current QTD window and returns budget in the result dict; legacy callers that only read current_period / prior_year continue to work.

- New _compute_da_budget_qtd helper extracts the Budget D&A QTD total from pnl_data; used as the Budget value for the new Adjustments-to-NI row.

- Emits an Adjustments to Net Income for non-cash items record (operating, display_order=1.5) with current_period/prior_year = None and budget = D&A Budget. Apr-26 produces $5,500k vs Finance's $5,450k — ~$50k attributable to non-D&A items not modeled; documented as approximation.

- START_CASH and END_CASH records now populate budget from cash_start_current. Start mirrors Actual (a single fact regardless of source); End mirrors Start so derived Change in Cash = $0 — matching Finance's balanced-budget convention until per-period Budget BS / loan-schedule sources arrive in Redshift.

- aggregate_cf_records captures the END_CASH_LINE_ITEM record alongside START_CASH_LINE_ITEM and uses it when present. Change in Cash is now derived from End − Start (per-field) when End is captured, matching the UI's computeChange logic. Section-sum formula remains the fallback for fields with no End record (preserves Software/Education entity-skeleton behavior).

- fetch_cash_flow_data Net Income row now reads net_income["budget"] instead of hardcoded None.

- Tests

- tests/test_cash_flow_service.py: replaced test_budget_is_always_null with test_budget_population_contract pinning the new contract (Budget populated for NI / Adjustments-to-NI / Start Cash / End Cash, null elsewhere).

- tests/reports_service/test_cf_helpers.py: added end_cash to the tracked-line-items guard and separated its derived-fallback assertion from the captured-line-item value-zero assertions.

## Testing

- cd klair-api && uv run pytest tests/test_cash_flow_service.py tests/reports_service/ tests/mfr/

- cd klair-api && uv run ruff format --check services/cash_flow_service.py services/financial_data_service.py

- cd klair-api && uv run ruff check services/cash_flow_service.py services/financial_data_service.py

- cd klair-api && uv run pyright services/cash_flow_service.py services/financial_data_service.py

- Live Redshift simulation of the post-fix fetch_pnl_data Group IS Apr-26 QTD Bud reproduces Finance's screenshot to the dollar on Recurring Revenue / PS-Other / Bad debt / D&A and within $200/line on the OpEx GAAP buckets.

- Manual: open /monthly-financial-reporting for Apr-26, confirm Group IS Bud column matches Finance's ESW Group screenshot, Software IS Bud column has no negative S&M/PE&S/G&A, Group Cash Flow Bud column shows Start = End = $11,094 and Change = $0, and the exported Google Doc Cash Flow Statement matches the on-screen view for both Actuals (Change = $8,092, End = $19,186) and Budget.

## Known follow-ups (out of scope, not blocking)

Both are source-sheet items flagged for Finance to address upstream; no code change involved:

- Software IS Budget Interest expense reads $(3,287)k vs Finance's expected $(1,400)k. The ~$1.9M gap is the PI-brokerage interest portion (71102), which the Software helper correctly excludes on the Actuals side. The Budget sheet currently has a single combined "Interest Expense" line — splitting it into its own row in the Budget sheet would let the helper reconcile Budget Interest automatically.

- Group Cash Flow Budget column populates only Net Income, Adjustments-to-NI (≈ D&A), Mgmt restructuring & import, and Cash start/end. The rest of the rows (AR, Prepaid, AP, Deferred revenue, DTA/DTL, Other current liabilities, Other LT assets, Purchase business combinations, Loans, Debt reserve, Interest paid, Capital contribution, FX) show as $- because the Budget sheet doesn't carry per-line Budget values for them. Adding those to the Budget sheet would fully populate the Cash Flow Budget column.

### Summary

The EBITDA memo's AI narrative was instructed to "generate plausible" revenue/cost splits per BU and to invent vendor names for the Central Functions detail bullet, which produced numbers and driver names that didn't match the source data. This change extends fetch_bu_details with Recurring / Non-Recurring revenue and HC / NHC / CF cost sub-aggregations, adds a new fetch_central_vendor_variances query for vendor-level Central Functions cost variances, threads both through the prompt builder, deterministic narrative builders, and the provenance panel, and rewrites the prompt guidance so the LLM cites real components instead of fabricating them. Also clarifies an ambiguity in section_2_question where "X% margin" and "X% pt delta" were being confused with the unrelated section_1_question "75% − unadjusted" expectation miss.

### Business Value

The monthly EBITDA memo is a CFO-facing artifact — when the AI narrative invents vendor names ("Engine Yard", "Khoros") or splits that don't reconcile to Finance's source data, authors waste time rewriting the memo and lose trust in the tool. Grounding every cited dollar amount and driver name in Redshift makes the first-pass memo defensible and shortens the author's edit cycle.

### Changes

- [financial_data_service.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/financial_data_service.py): extend fetch_bu_details query and BUDetailsRow TypedDict with Actual/Budget Recurring & Non-Recurring revenue and HC / NHC / CF cost columns (bad-debt excluded to match the "Actual excl BD" margin definition)

- [financial_data_service.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/financial_data_service.py): add fetch_central_vendor_variances(period, threshold_usd, top_n) — QTD vendor-level Actual vs Budget cost across Central Functions BUs, filtered by |variance| >= threshold and sorted by absolute variance

- [ebitda_memo.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_memo.py): thread the new split fields through _bu_row_from_fetch, _null_bu_row, _total_row, and the margin-mix synthetic plug via new _BU_DETAIL_SPLIT_FIELDS / _BU_DETAIL_SPLIT_SOURCE_KEYS constants

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): best-effort fetch_central_vendor_variances call in both _fetch_ebitda_data (upload path) and _fetch_ebitda_data_from_redshift (live path), with warning + empty-list fallback so a vendor-query failure doesn't block memo generation

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): new _format_bu_split_lines helper emits a Revenue / Cost split block under each BU detail line in the LLM prompt; new CENTRAL FUNCTIONS VENDOR VARIANCES block lists top vendor variances with explicit "do not invent vendor names" guidance

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): new _dominant_split_driver identifies the component (recurring revenue, non-recurring revenue, HC / NHC / CF cost) responsible for at least 60% of a BU's miss_v_budget with matching sign — used by _build_bu_variance_bullets to phrase the deterministic miss/beat sentence with the real driver instead of "revenue shortfall and overspend on costs"

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): _build_central_variance_bullets now cites the top one or two vendor names with their real variance amounts when available; _build_investment_variance_bullets attributes net Investment variance to the dominant entity (XO / XO 3rd Party / TU / CloudFix) by per-entity miss_v_budget; _build_affiliates_variance_bullets includes the real Virtasant revenue / cost split when live data is present

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): prompt guidance rewritten — replaces "generate plausible splits" with explicit NUMBER SOURCING RULES that forbid inventing splits or vendor names and require [TBD] when data is missing

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): section_2_question Pattern clarified — "X% margin" is now the Core margin (from the bridge), "Y% pt delta" is the margin delta from target, both explicitly distinguished from the section_1_question 75%-vs-unadjusted "Expectation miss"

- [ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/ebitda_defaults.py): provenance panel (_build_ebitda_provenance) now surfaces the per-BU split components and per-vendor Actual / Budget / Variance lines so the details panel matches what the LLM was given

- [test_ebitda_defaults.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/tests/test_ebitda_defaults.py): update fixture expectation to include the new central_vendor_variances: [] key on the live-path return shape

### Testing

- [ ] cd klair-api && pytest tests/test_ebitda_defaults.py — unit tests pass with updated fixture

- [ ] cd klair-api && uv run ruff format services/docx_reports/memo_data/ebitda_defaults.py services/docx_reports/memo_data/ebitda_memo.py services/financial_data_service.py tests/test_ebitda_defaults.py

- [ ] cd klair-api && uv run ruff check services/docx_reports/memo_data/ebitda_defaults.py services/docx_reports/memo_data/ebitda_memo.py services/financial_data_service.py tests/test_ebitda_defaults.py

- [ ] cd klair-api && uv run pyright services/docx_reports/memo_data/ebitda_defaults.py services/docx_reports/memo_data/ebitda_memo.py services/financial_data_service.py

- [ ] Generate an EBITDA memo for a recent live-data period and verify: per-BU bullets cite a real dominant driver (e.g. "non-recurring revenue miss of $1.5M") not generic phrasing; Central Functions detail bullet names actual vendors from the variance list; Investment bullet attributes net variance to the dominant entity; provenance panel shows the same split values that fed the prompt

- [ ] Generate an EBITDA memo for an upload-only period and verify the memo still renders with generic phrasing and [TBD] for vendor-level attribution (no crash when fetch_central_vendor_variances returns empty)

- [ ] Verify section 2 question now reads "Why did we hit X% margin? What contributed to the Y% pt delta?" and the narrative uses Core margin % vs target margin delta, not the section 1 "75% − unadjusted" figure

http://localhost:3001/monthly-financial-reporting

## Summary

Adds query_max20x_savings — read-only SELECT access to the AI-savings views, mirroring query_truefoundry_spend:

- vw_ai_spend_max_migration_candidates — heavy metered users not on Max → projected monthly savings (deduped; leonardo-style merges via ai_spend_subject_identity).

- vw_ai_spend_max_savings — realized monthly Max savings (roster-free, derived from gateway routing).

- vw_ai_spend_negotiated_savings — list vs actual vs savings per source (claude.ai exact; TF derived via list prices + the 10% input+output discount).

- vw_ai_spend_claude_atomic — base per-(usage_date, user_email, route) rollup.

Gated under the existing AI Spend permission. Includes wrapper test, DEF-T5 assertion, permission snapshot. Local: tsc clean, 1022 tests pass. Requires a klair-mcp-ts redeploy + the views to exist in Redshift (Surtr PR).

## Summary

The savings layer over the TF-gateway + claude.ai data — both Max20x and negotiated-discount savings, as Redshift reference tables + views. Consumed via the Klair query_max20x_savings MCP tool (separate PR).

Roster-free — Max access is read from gateway routing (claude-max-group/claude-pro-group), so realized + potential savings come from the data and auto-track who has/loses 20x. Only "seat held, zero usage" is invisible.

### New tables (manual seeds)

- ai_spend_subject_identity — supplementary slug→email override; unmapped users pass through "dumb"; seeded with leonardo only, add rows by hand.

- ai_spend_list_prices — public $/MTok per model/token_type (Anthropic published rates, effective-dated).

- ai_spend_discount_rules — discount % per provider/token_type (Anthropic 10% input+output, cache 0%).

### New views

- vw_ai_spend_claude_atomic, vw_ai_spend_max_migration_candidates, vw_ai_spend_max_savings, vw_ai_spend_negotiated_savings.

### Validated on prod data

leonardo merges to #1 ($12.7k); realized Max ~$8k/mo; claude.ai negotiated 9.99%; TF 0.98%.

### Deploy checklist

- [x] Tables + views applied to Redshift (Data API) — done in build session.

- [x] GRANT SELECT to MCP_user + ediechitac — done.

- [ ] Re-run artifacts/*.sql in other environments as needed (idempotent).

- [ ] Maintain ai_spend_list_prices / ai_spend_discount_rules when rates/rules change (row edits).

- [ ] Klair query_max20x_savings PR (#2848) merged + MCP redeployed.

⚠️ Redshift gotcha captured in the view: divide token counts by 1000000.0, not 1e6 (integer division).

## Summary

- New top-level Financials tab in the Dashboards sidebar.

- Renders a QuickBooks-sourced Revenue Breakdown table by quarter + YTD: Net Tuition, Discounts, Stripe fees, *Net Tuition calculated*, Lunch, Other Revenue, Total Revenue.

- School dropdown filtered to Miami for this POC (default-selected).

- Backend: new Convex queries getRevenueBreakdown (aggregates plRecords with a school-agnostic line-item classifier) and getPlSchools (distinct school names from plRecords).

## Test plan

- [ ] Visit /dashboards?tab=financials and confirm the Financials tab renders without console errors.

- [ ] Confirm the school dropdown shows only Miami and is pre-selected.

- [ ] Cross-check Miami Q1/Q2 values against QuickBooks for Net Tuition, Discounts, Stripe fees, Lunch, Other Revenue.

- [ ] Verify Net Tuition calculated = Net Tuition − Discounts − Stripe fees and Total Revenue sums match.

- [ ] Click Export CSV and confirm rows match the rendered table (sign-flipped for Discounts/Stripe).

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Demo

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/a2c63e9e-2f35-49f2-8f26-c8e7970c07d8" />

## Overview

Add cell-level commenting to dashboard tables. Comments anchor to a logical {surfaceKey, rowKey, columnKey} cell — not a snapshot — so they survive ETL value refreshes. v1 wires five Financials tables; the primitives in chat/components/comments/ are table-agnostic and ready for future dashboards (Portfolio Operating, etc.).

Parent issue: AI-Builder-Team/Aerie#215 · Linear: [AERIE-243](https://linear.app/builder-team/issue/AERIE-243)

This PR is stacked on feat/financials-tab-poc.

## What shipped

| # | Spec | Issue | Commits |

|---|------|-------|---------|

| 01 | convex-schema-and-module | #216 | e671e42 |

| 02 | shared-comment-components | #217 | 78f3bf0 |

| 03 | wire-pl-breakdown-table | #218 | cf31650 |

| 04 | wire-unit-econ-comparison-table | #219 | 2038c17 |

| 05 | wire-hc-by-role-table | #220 | 33dbafa |

| 06 | wire-unit-econ-model-and-enrollment-forecast | #221 | b1a105e |

Spec docs live under features/dashboards/dashboard-cell-comments/.

## Architecture

- Persistence — cellComments table in chat/convex/schema.ts with by_anchor, by_parent, by_surface indexes; module chat/convex/cellComments.ts exposes create, resolve, listForAnchor, listForSurface. All four require ctx.auth.getUserIdentity(). listForSurface aggregates per anchor in a single index scan — no N+1.

- Shared UI primitives — new chat/components/comments/ directory: CommentCardShell, CommentableCell (a true <td>), CellCornerIndicator, CellCommentContextMenu, CommentThreadPanel, CommentThread, CommentComposer, plus useCommentSurface, useCommentPanel, useCellComments hooks. Anchor keys serialize with ::. Table-agnostic — no Financials imports.

- Per-table wiring — five table files updated to declare a SURFACE_KEY and wrap data <td>s with <CommentableCell>. Surface keys: pl_breakdown_v1, unit_econ_comparison_v1, hc_by_role_v1, unit_econ_model_v1, enrollment_forecast_v1.

## Behavior (v1)

- Right-click any data cell → context menu → "Comment" → composer opens, focused

- Top-right folded-page triangle (amber) appears on cells with unresolved threads

- Left-click triangle → panel opens, composer not focused, thread visible

- Inline panel lives inside the table's card (~60/40 split)

- One panel per card; multiple cards can have panels open simultaneously

- Resolved threads hidden by default; "Show resolved" toggle inside the panel

- Visible to all authenticated users (no RBAC for v1)

## Out of scope (v1)

- @-mentions, notifications, RBAC, rename-tolerant anchors

- Comments on chart points / KPI tiles (schema supports it; UI doesn't)

- Edit / delete comments (only create + resolve)

## Test coverage

- Convex — 8 tests in chat/convex/cellComments.test.ts: empty-content rejection, parent-anchor mismatch rejection + match acceptance, resolve toggle (false→true→false) + explicit set, listForSurface aggregation across multiple anchors + surface isolation, unauthenticated rejection on both queries and mutations.

- Shared keys — 7 tests in chat/components/comments/keys.test.ts: separator constant, buildAnchorKey join behavior, edge cases (empty strings, embedded ::, special chars), determinism, order sensitivity.

- Table wiring (specs 03–06) has no pure logic to unit-test; cross-spec contracts (import path, anchorLabel shape, surfaceKey uniqueness, Convex API alignment) were verified by the Phase 5 alignment audit.

## Self-review

- CRITICAL — Unit Econ Model Tuition row collision (Inputs vs Outputs bands shared the same rowKey). Fixed in 456406c by namespacing all rowKeys in that file with band prefixes (inputs:, outputs:, total:, margin:). Spec 06 FR4 updated.

- All other contracts (auth on every Convex function, 'skip' gating on useCellComments, ConvexError on validation failures, no XSS surface, <td> semantics preserved on every CommentableCell, surface keys + labels matching spec strings byte-for-byte) — verified clean.

## CI / verification

- pnpm lint (architecture boundaries + biome) — PASS

- pnpm typecheck (all 4 workspaces) — PASS

- pnpm vitest run for spec-01 + spec-02 tests — 15/15 PASS

CI does not fire on this PR while it's stacked on feat/financials-tab-poc (CI is gated on PRs targeting main/production). The checks above cover the same surface CI would run.

## Closes

Closes #215

Closes #216

Closes #217

Closes #218

Closes #219

Closes #220

Closes #221

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Demo

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/c0bdc4da-60d9-4edd-90dc-3f124b85a4ee" />

## Summary

Expands the SaaS Budgeting tab with three substantial additions plus two correctness fixes. Branch grew well beyond the original Excluded-Accounts scope; grouping themes below.

---

### 1. AWS Spend — Excluded AWS Accounts band + exclusion registry

Adds a 3rd table to the SaaS Budgeting → AWS Spend tab surfacing every account hidden by the new hardcoded exclusion registry so the methodology stays auditable. Also fixes the Enterprise SaaS account (446735561331) that was missing from the canonical mapping spine despite real Gomembers / IgniteTech spend.

Backend (klair-api/)

- services/saas_budgeting_aws_spend_exclusions.py (new) — registry of ~47 accounts keyed by an ExclusionReason enum (saas_central / engine_yard / wine_cellar / untagged_central / tag_conflict) + ADDITIONAL_MAPPINGS override list.

- services/saas_budgeting_aws_spend_service.py — filters excluded accounts out of the Mapped + Unmapped bands; applies the override; adds get_excluded_accounts_table for the new band.

- routers/saas_budgeting_router.py — new GET /api/aws-spend/saas-budgeting/aws-net-amortized-excluded endpoint.

Frontend (klair-client/)

- services/awsSpendApi.ts + hooks/useSaaSBudgetingAWSExcluded.ts — types, fetcher, multi-quarter merge hook.

- awsSpendCardTransform.ts / AWSSpendCard.tsx — section builder grouping rows by exclusion reason with sort order; new band with per-category subtotals + methodology info callout; CSV export integration.

> Bug fix during impl: initial cut used aws_account_number = ANY(%s) — redshift-connector does not expand Python lists for ANY(%s) and threw ArrayContentNotSupportedError. Switched to explicit IN (%s, %s, ...) placeholders matching the jotform_redshift_service.py pattern.

---

### 2. AWS Spend — DB Servers card (replaces DB Units)

Builds a full BU → Class → DB hierarchy with week-driven cost allocation on the DB Servers card, then retires the parallel DB Units table now that DB Servers covers the same surface. Shared helpers (databaseUnitsTransform, databaseUnitsCostAllocation, centralDbCostAllocation) remain — DB Servers depends on them.

- Metric cards row — Total + per-engine (Postgres / MySQL / MSSQL / Oracle) cards, each with a 4-segment bar (assigned storage | assigned compute | unassigned storage | unassigned compute) summing to total cost. Header shows assigned % and unassigned $ next to the Total figure.

- Full hierarchy + allocation — multi-quarter week picker, Apply gating, shared allocateLeavesForServer helper used by both trees; cost columns sit immediately after instance count; attach-to-budget path + scroll target on the mappings card.

- Unassigned Cost column — slice of each server's totalCost that doesn't roll up to a complete BU+Class pair. Rendered as $X (Y%) with amber tint when non-zero so reviewers see allocation gaps without opening diagnostics.

- Diagnostics popover — click-triggered popover on the server name (AlertTriangle prefix + dotted underline signals problem servers). Replaces the under-table diagnostics block. Added an additive wrapName hook on ColumnDef to decorate the sticky-first-column name without overriding ExpandableRowRenderer.

- Instance-breakdown tooltip — moved into its own info column (the first column ignores column.render when accordionRows: true, so the original placement was dead code). Drops row expansion in favor of the info-icon tooltip on servers with instanceCount > 1.

- All-quarter cost windows — dropped the applied-quarter filter on the window selector; appliedQuarter is no longer user-controlled so the filter only ever exposed the latest quarter.

- Natural sort — server rows by friendlyName, instance children by dbName (so aurora-2 precedes aurora-10).

- Oracle / Non-Oracle attach split — Central DB allocation splits into Oracle vs Non-Oracle subtotals at the BU/Class level (Pricing F6 Oracle Subscription is 10× F7 Non-Oracle); wired through extractBuClassCentralDbCosts, simulatedBudgetMerge, useSimulatedBudget, SimulatedBudgetCard, the CSV export, and quarterProjection.

- Cleanup — removes the DB Units table and hides the Mappings Collapse All affordance.

---

### 3. SaaS Budgeting — Non-Central DBs (new ingest + tab)

End-to-end addition surfacing the $1,000/quarter non-central-DB subscription charge per linked account so Finance can audit who is being billed and why.

Ingest (aws-saas-budget-scripts/pipeline/non_central_dbs_ingest.py)

- Iterates every master payer, pulls net-amortized RDS spend per linked account from Cost Explorer, applies EY / Wine Cellar consolidation, joins to aws_spend_budget_account_mapping for class/BU.

- Writes one row per account per window to core_finance.saas_budgeting_non_central_db_charges.

- Supports --start-date/--end-date and --quarters YYYY-Qn,... for multi-quarter runs.

- Billable rule: any non-zero RDS spend AND NOT in skip-list. The Finance-managed core_finance.saas_budgeting_non_central_db_skip_list is read fresh on every run so Finance can opt accounts out without a code change.

- Bootstrap DDL seeds the skip-list with 75 rows derived from the spreadsheet's billable=No column.

Backend

- routers/non_central_db_charges_router.py + services/non_central_db_charges_service.py — read latest-window rows.

- New endpoint GET /api/aws-spend/saas-budgeting/non-central-db-charges.

Frontend

- New NonCentralDBTab in SaaSBudgetingSection with a BU → Class → Account hierarchy (BUs expanded by default; class rows reveal accounts on demand), billable-only filter (default on) hiding empty classes/BUs, and a $-charge summary.

- Adds a nonCentralDb slot to the simulated-budget rollup so the tab snapshots its billable BU/Class charges (always billable regardless of on-screen filter — the simulated budget represents what Finance actually bills).

---

### 4. Adjustments — two regressions fixed

- success field on write responses — frontend reads response.data.success on upsert/delete and throws Adjustment upsert failed / Adjustment delete failed when missing. Commit 3312958be removed success: bool = True believing it was unused; that assumption skipped the client contract, so every save/delete had been surfacing a misleading error toast since. Restored on both models; updated the delete happy-path test that codified the broken {} body.

- Editor seeding race — on initial mount the registry and adjustments fetch in parallel. If the registry resolved first while savedAdjustments was still [], the seeding effect latched hasSeededRef on the empty state and ignored the eventual adjustments payload — leaving the table empty until a quarter switch reset the latch.

---

## Test plan

- [x] Backend: pytest tests/services/test_saas_budgeting_aws_spend_service.py tests/routers/test_saas_budgeting_aws_spend_router.py tests/non_central_db_charges/ — all green.

- [x] Ingest: pytest aws-saas-budget-scripts/tests/test_non_central_dbs_ingest.py aws-saas-budget-scripts/tests/test_redshift_writer.py aws-saas-budget-scripts/tests/test_main.py — all green.

- [x] Frontend: pnpm test:run on AWSSpendCard, transform, NonCentralDBTab, DatabaseServersTable, engineMetrics, databaseServersHierarchyTransform, useSaaSBudgetingAWSExcluded, simulatedBudgetMerge, simulatedBudgetCsvExport, useAdjustmentRowsEditor — all green.

- [x] pnpm tsc --noEmit, pnpm lint:pr, pnpm prettier --check on changed FE files — clean.

- [x] uv run ruff format/check + uv run pyright on changed BE files — clean.

- [ ] Manual QA: AWS Spend → SaaS Budgeting tab — verify Excluded band renders with subtotals + methodology callout; DB Servers metric cards show 4-segment bar with assigned/unassigned split; DB Servers hierarchy applies week selection and attaches to the simulated budget; diagnostics popover opens from the server name; Non-Central DB tab renders BU/Class/Account hierarchy with billable-only filter; adjustments editor seeds on first load and save/delete no longer surface error toasts.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Demo

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/591f4231-8d55-414e-93a8-289434dab306" />

## Summary

- ARRGapV2 → toggling Include Twitter on the Renewals view 500'd with AttributeError: 'TwitterImpactService' object has no attribute 'get_impacted_subscription_ids'.

- Root cause: the gsheet ingest migration (#2734) deleted TwitterImpactService.get_impacted_subscription_ids (it used to fetch from S3), but ARRGapRenewalsService._get_excluded_subscription_ids still calls it.

- Fix: re-added the method, sourced from the new core_finance.arr_gap_twitter_impact Redshift table using the same filter the Twitter Impact view uses (impact IS NOT NULL AND impact != 0). Cached via the existing twitter_impact_cache. Also dropped the stale "from S3" wording in the renewals service's RuntimeError.

## Test plan

- [x] uv run ruff format + uv run ruff check clean on the two touched files

- [x] uv run pytest tests/arr_gap/ — 251 passed (covers TestGetExcludedSubscriptionIds + test_twitter_impact_service.py)

- [ ] Manual: open ARR Gap V2, toggle Include Twitter on, confirm Renewals table loads and the request returns 200

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

Adds a query_claude_ai_spend MCP tool that exposes Claude.ai Enterprise per-user usage + cost (core_finance.ai_spend_claude_ai_chat_usage) for read-only SELECT queries, mirroring the existing query_truefoundry_spend / query_aws_spend pattern.

- Grain: (usage_date, user_email, product); description documents the load-bearing semantics (cost_usd_actual/cost_usd_list stored verbatim from the Analytics API — savings = list − actual; product for surface filtering; is_provisional for the ~3-day lag; bu via dim_user).

- Gated under the existing AI Spend page permission.

- Includes the wrapper test, DEF-T4 definitions assertion, and the TOOL_PAGE_REQUIREMENTS snapshot update (so CI is green on first run).

Local: tsc --noEmit clean, full suite 1022 passing. Requires a klair-mcp-ts redeploy to go live.