The Trilogy Times

## Problem

The daily P&L-by-class sync only refreshed a 3-month rolling window (current + 2 prior). Any month older than that froze at whatever QuickBooks returned the day it left the window, and was never re-fetched. QuickBooks permits edits to closed periods — reclasses, late postings, name corrections — so the frozen quickbooks_pl_monthly snapshot silently diverged from QB while the transaction-level feed kept up.

### Concrete symptom

Alpha Anywhere Center's January 2026 60220 Contracted Labor – Campus Administration froze at $86,580.66 on Mar 31. QuickBooks later reclassified $62,306.89 of it from the *Alpha Anywhere Center* class to the *Alpha School LLC* class. The frozen row never picked up the correction, surfacing a phantom ~$62.3k "unitemized overage" downstream.

Per-month created_at proves the freeze — each month was last written on the last day it sat at the window's trailing edge, then never again:

| Month (2026) | AAC 60220 | Last written |

|---|--:|---|

| Jan | $86,580.66 (stale) | Mar 31 |

| Feb | $101,250.04 | Apr 30 |

| Mar | $101,250.17 | May 31 |

| Apr | $22,716.40 | Jun 16 |

| May | $19,860.56 | Jun 16 |

## Root cause is NOT the parser

A live re-fetch of the same January report returns the correct split — AAC $24,273.77 and ASL $62,306.89 as *separate* columns — and 0 of 45 report columns are dropped by _extract_columns. The parser is fine; the stale partition was the sole cause.

## Fix

Widen the default window to current + 5 prior months (6 months), configurable via PL_MONTHLY_PREVIOUS_MONTHS. Six months doubles the reconciliation horizon while staying within the single-invocation Lambda's 900s timeout (the 3-month run takes ~5 min; 6 months ≈ 10–11 min). The README's "all months from 2023-01" was never true in code.

## Testing

- uv run pytest → 51 passed (incl. new test_default_window_is_six_months); ruff check clean.

- End-to-end dry-run (real handler, dry_run=True, no writes) confirms the new window re-fetches January:

  DEFAULT_ROLLING_MONTHS = 5  ->  6-month window
window = ['2026-01-01' ... '2026-06-01']   January in window? True
[alpha] 6/6 months, 1996 records   (months_requested: 6)

The old 3-month window would not have fetched January at all.

## Reconciliation (Alpha Anywhere)

| | AAC Jan 60220 | AAC Q1 60220 |

|---|--:|--:|

| Before (frozen) | $86,580.66 | $289,080.87 |

| After backfill (expected) | $24,273.77 | $226,773.98 |

| QB report / txn feed | $24,273.77 | $226,773.93 |

A new Alpha School LLC Jan row of $62,306.89 is created; the overage drops to ~$0 (within tolerance).

## Applying to prod (manual — DB write, left to a human)

The code change prevents *future* freezes (within 6 months). To repair the already-frozen months now:

cd pipelines/runners/quickbooks-pl-monthly
ENVIRONMENT=prod uv run python run_local.py --full --company alpha     # or --all-companies

Or simply let the next daily run pick it up — June's run now covers Jan–Jun.

## Deferred

Corrections landing more than 6 months after a period still freeze. The complete fix is per-company fan-out in the Step Function (each company its own invocation), letting the window span full history within the 900s budget.

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

## Why

The public read API (/v1, shipped in #474) is unreachable in prod: the ALB fronts Next.js, whose rewrites() only proxied /trpc + /internal to the Hono backend. So /v1/* 404s on the app domain for external consumers and the in-app API console, and the console's Base URL fell back to localhost:4001. The API is also currently running open (no SURTR_API_KEYS in the task).

Verified live in prod: internal /v1/education/schools returns data, but it's not routed to the public domain.

## Changes

- next.config: proxy /v1/:path* → Hono backend (same pattern as /trpc, /internal).

- api-reference console: resolve Base URL from window.location.origin at runtime (no hardcoded host); show a clear *key-gated* state instead of a red "unreachable" when a 401/403 comes back.

- infra: wire SURTR_API_KEYS into the task-def secrets so /v1 is Bearer-key gated.

## Deploy note

The SURTR_API_KEYS value lives only in the SURTR_PROD_KEYS Secrets Manager secret — never in git/GH. It's already been added, so this deploys cleanly. On rollout: /v1 becomes reachable at the app domain and requires a Bearer key.

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

## Summary

quickbooks-ap-sync (which fetches bills / purchases / journal entries / vendor credits) iterates a hardcoded COMPANY_IDS list, while quickbooks-pl-monthly auto-discovers every company from the shared Secrets Manager secret. valenta_academy was in the P&L set but absent from this list, so its P&L report was synced while none of its transactions were ever fetched — 0 rows in every transaction staging table.

valenta_academy is the dedicated QB company file for Nova Academy Bastrop (Facilities/Support, Depreciation, Professional Fees ≈ $81k/yr). Because the dashboard computes unitemized = P&L line − Σ(itemized transactions), the entire valenta-sourced portion surfaced as a phantom "unitemized overage" on the Schools – Actual vs Model → P&L Breakdown view (~$45k Q1 / ~$36k Q2 2026 for Bastrop).

This one-line change adds the company so its transactions are fetched like every other realm's.

## Root cause / blast radius

- valenta_academy is the only one of 35 P&L companies missing from COMPANY_IDS.

- Nothing currently in COMPANY_IDS is absent from the P&L set, so coverage is now aligned and no company is dropped.

- Bastrop is the only school in the valenta_academy realm, so it's the only school affected today — but the hardcoded-list pattern will silently drop any future realm.

## Test plan

- [x] python -m py_compile src/handler.py — compiles

- [x] uv run pytest -q — 128 passed (no test pins the exact list/count)

- [x] uv run ruff check / ruff format --check — clean

- [ ] Prod backfill for valenta (no deploy needed — company_ids param override):

uv run python run_local.py --company valenta_academy --start 2026-01-01 --end 2026-06-30 --no-dry-run

- [ ] Confirm valenta rows land in quickbooks_{bills,purchases,journal_entries,vendor_credits}; financial-worker re-pulls into Convex plTransactions; Bastrop UNITEMIZED overage → ~$0.

## Follow-up (separate PR)

Consider switching the transaction pipelines to the same _discover_company_ids() secret auto-discovery quickbooks-pl-monthly uses, so company drift can't recur. That needs QUICKBOOKS_COMPANIES_SECRET wired into the ap-sync (and expense-sync) Lambdas via CDK, since they currently read per-company secrets only.

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

## Why

PARTIAL is a first-class terminal run status (from #273) — the run completed and wrote data, but some units failed. The observability layer was never taught about it; it only knew SUCCESS and FAILED. Two gaps fell out of that:

1. Observer skipped PARTIAL. The sweep pulls terminal runs with status IN ('SUCCESS','FAILED'), so partial runs were never evaluated — no verdict, no observation, no triage.

2. Dashboard hid PARTIAL. Stat tiles, the "At risk" list, and the failure-count query all key on FAILED exactly. A pipeline that silently drops data every run was not counted, not flagged at-risk, and never re-evaluated — its only signal was a *stale* verdict from an earlier SUCCESS/FAILED run, which decays as partials accumulate.

That defeats the point of PARTIAL ("complete as much as possible, never block data") — the "never block" was meant to be paired with "always surface what got dropped."

## What

Observer (observer-sweep-route.ts)

- Sweep filter now status IN ('SUCCESS','PARTIAL','FAILED') — only RUNNING is excluded.

- Fixes the sweep filter test, which was already failing on main (it asserted lowercase ('success','failed') while the query emits upper-case — a pre-existing casing drift). Updated to the new filter.

Dashboard — PARTIAL gets its own bucket (per product decision: not equated with a hard failure)

- New partial category ranked between failed and CRITICAL in categoryOrder — a concrete run outcome outranks the observer's heuristic verdict, the same principle already applied to failed.

- partialWithinWindow() helper + orange PARTIAL chip/tile (distinct from amber WARN / red CRITICAL).

- New Partial stat tile (5-tile grid), recentPartialCounts DB method + getRecentPartialCounts route (mirror the failure-count path), partial at-risk buckets, an N partial · 7d badge, and chip overrides in the at-risk + recently-evaluated rows.

- /all table + rail: partial filter chip / count / row chip. [id] page: partial run coloring + "N partial" in the success-rate breakdown.

## Verification

- tsc (src) clean · biome clean · 435 unit + 174 derive tests pass, including the updated sweep filter test.

- app/ standalone tsc shows only the pre-existing tRPC-proxy ("collides with a built-in method") noise that also hits untouched files and is tolerated via next.config.ts ignoreBuildErrors: true. No new type errors in the changed files.

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

## Summary

Extends the two Financial-Highlights-only features — drift detection (the amber "values changed" gutter rule + regenerate icon) and the generated→current value-diff drill-down — to the rest of the Group memo's AI-generated narrations: the Results section and all Notes (note-1, note-2-bullets, note-3…note-7, note-9, note-10, note-11, note-11-bullets).

Per-bullet regeneration was already wired for these sections, so it's untouched. Drift applies uniformly across all AI notes — qualitative notes with no numbers simply never flag (no false positives). Editorial/boilerplate sections (Business Outlook, About ESW, Non-GAAP) and embedded tables are out of scope (not AI-generated narration).

## Business Value

Finance previously could only see *whether a Group memo's Financial Highlights had drifted* from the live numbers and audit the change; the rest of the memo (Results + the numbered Notes) gave no such signal, so stale narration could ship unnoticed. This brings the entire AI-generated memo to parity: every AI bullet now flags when its underlying values move, shows exactly which values changed in the drill-down, and can be regenerated in place — reducing the risk of publishing out-of-date commentary.

## How it works

The machinery was already mostly generic; the FH-scoping lived in a few enumerators/gates.

Backend

- fingerprints.py: generic _all_bullet_texts / _compute_bullet_fingerprints iterate the full template-defaults dict (FH + Results + Notes); _bullet_fingerprint reads the all-section map.

- group_defaults.py: generation fingerprints every AI bullet; regenerate_bullet drops the is_fh gate so any section's fingerprint refreshes.

- Router: the stale-check fingerprints every AI bullet and returns current_provenance for every stale section (new StaleSectionProvenanceResponse with a plain-str section_key); the regenerate route persists the refreshed fingerprint for any section.

Frontend

- useGroupProvenancePanels: the Results + Note inspect handlers now pair a currentBullet (summary-first, index fallback) so the value diff renders — the FH handler was the template.

- GroupMemoView: generalized the FH stale-index split into staleIndicesBySection; passes staleIndices to Results and (via a staleIndicesByKey accessor) to each Note.

- MemoNotesSection: threads staleIndicesByKey to each note's body + bullets. EditableCommentary already rendered staleIndices + the regenerate icon generically.

## Back-compat / migration

Names and keys are intentionally kept (endpoint /group-memo-fh-stale-check, checkGroupMemoFhStale, useGroupMemoStaleBullets, the __fh_fingerprints__ DynamoDB key) — they're now slight misnomers but renaming would add churn and a needless data migration. The fingerprint map now spans all AI sections; no migration needed — the next generate/regenerate repopulates it, and a missing baseline reads as "not stale" (graceful). Backend + frontend ship together (monorepo).

## Testing

- Backend: pytest tests/mfr/ → 1801 passed. New: stale-check returns Results/Notes drift + current provenance; non-FH regen refreshes its fingerprint; _compute_bullet_fingerprints covers results/notes ids.

- Frontend: full MFR vitest dir → 1130 passed. New: Results/Notes currentBullet pairing (present/absent/summary-vs-index), Results + Notes stale-id routing, non-FH currentProvBySection keys.

- tsc --noEmit clean · eslint clean on touched files · ruff/pyright clean.

> Note: repo-wide pnpm test has pre-existing, unrelated RenewalsShell/FionnHandling failures that reproduce on a clean tree (no files touched there).

## Manual verification (pending)

- Generate a Group memo → mutate a Results/Note input upstream → reload → amber marker on the affected bullet.

- Inspect the bullet → drill-down shows the generated value struck-through with → current.

- Click regenerate → bullet updates, marker clears, drill-down reflects the new value.

- FH drift/diff + whole-memo Generate/Clear All unaffected.

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

Rebuilds the /sync Data Quality tab around the domains that actually have aggregate-vs-detail reconciliation, and fixes the access gate that left it empty for everyone.

## What changed

Pipeline reconciliation (new, real)

- _checkPipeline previously returned mismatches: [] always. It now reconciles pipelineFunnel.count per (programCode, stageId) against distinct dealId in pipelineStudents for the published funnel run.

- The roster is wide, so it's counted per program via _countPipelineDealsByStage and compared in the action — keeping each query under Convex's 16MB single-execution read limit.

Marketing checks retired (unsound)

- marketingEvents counts are COUNT(DISTINCT contact_id) per current funnel_state_code — mutually-exclusive current-state buckets, not cumulative funnel gates. No monotonicity invariant (attended ≤ registered, enrolled ≤ attended, …) holds; they only "passed" by coincidence. Proof: rows exist with attended=0, enrolled>0, impossible under cumulative semantics.

- Removed _checkMarketing and the invariant table. marketing is kept v.optional in the schema so historical rows still validate on read (no migration needed).

Access fix

- operations.sync.read was defined but never granted to any role/legacy permission/set, so getLatestConsistencyCheck returned null for everyone → perpetual "no check has run yet". Granted via the operations capability set (authenticated baseline), matching the Freshness tab's auth-only access on the same page. _(Isolated in its own commit for review.)_

UI reframe

- Header + single health verdict; rename the freshness card to Source Spread (de-collides with the new Freshness tab); add a Source Feeds table built from the previously-discarded latestByTable; demote orphaned program codes to a collapsible footnote. Cards are now a clean 3-up: Enrollment · Pipeline · Source Spread.

## Verification

- pnpm typecheck clean, biome clean (pre-commit hooks pass)

- 12 dataConsistency tests (incl. new per-program pipeline reconciliation test), 197 contracts tests

- Confirmed live on dev:fleet-goat-601: pipeline 0/690 reconciles, marketing false-positives eliminated, Enrollment 0/28, verdict Healthy

## Note for reviewers / deploy

The marketing-removal changes need a redeploy + consistency-check re-run on the dev deployment to reflect in the live tab (separate from merging).

🐦‍⬛ Generated by a very good bot

## What

A real /explorer for the ontology, with a top Data / Schema toggle.

- Data view (data-explorer.tsx) — entity tabs (School / Program / Market) with search, faceted filters (market/type/stage; status; toggles), a sortable table *and* a focused graph (toggle), and a detail panel with clickable relations to drill school ↔ market ↔ program.

- Schema view (ontology-graph.tsx) — the entity/relationship graph (entities as nodes, their properties, relationships as edges).

- Adds @xyflow/react (React Flow).

## Notes

- Reads the existing listTypeMetadata + list{Plural} procedures — no new backend.

- Independent of the other PRs (only package.json's *dependencies* section overlaps with the education PR's *scripts* section; auto-merges).

<img width="1515" height="799" alt="Screenshot 2026-06-16 at 7 16 53 PM" src="https://github.com/user-attachments/assets/1e7a3b39-a4d7-4c3c-b248-162098267eb0" />

<img width="1721" height="795" alt="Screenshot 2026-06-16 at 7 17 09 PM" src="https://github.com/user-attachments/assets/03b141bf-8ae5-4fe1-a1b6-a9835d40d00d" />

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

## What & why

Adds billed-accurate dollars per OpenAI API key and per key-owner ("Services") to the openai-usage-pipeline, for all 28 BU orgs — including the 11 where OpenAI's direct per-key cost API is gated. Motivated by the Klair /ai-adoption follow-up ("what about Services and API Keys?"). Full analysis in docs/superpowers/specs/2026-06-17-openai-services-apikeys-findings.md.

## How it works

1. Persist key owner — owner_type/owner_id (was fetched then dropped). Extracted from the nested owner[type].id shape.

2. Fetch billed line-item dollars — /v1/organization/costs?group_by=line_item (ungated, all orgs).

3. Allocate to keys by token share at (project, model, direction) grain — direction matters because output tokens are priced ~4x input. Validated live: 99.1% accurate vs ground truth on an ungated org.

4. Per-row split so billed_cost_dollars SUMs correctly (a per-key total would over-count once user_id multiplies rows).

5. Add user_id to the usage group_by -> 100% user attribution (was ~6%).

New Redshift columns on core_finance.ai_spend_openai_token_usage: owner_type, owner_id, billed_cost_dollars. (DDL already applied in prod; see runbook.)

## Testing

- 98/98 unit tests pass. Allocation math, direction-grain weighting, per-row sum-back invariant, owner extraction, idempotency all covered.

- Live verification: Postgres-in-Docker executed the real write path; smoke test + June 1-15 backfill run against prod Redshift — billed + user_id 100% populated, billed totals reconcile to the live line-item totals exactly.

## Reviewer notes / honest caveats

- billed_cost_dollars is a token-weighted allocation — exact at project level, modeled within a project (<=~5% per key). Label downstream as "allocated, not invoiced" (Finance sign-off pending).

- One upstream gap: project proj_93bCdkrc... (Central-Engineering "Default project") returns a persistent HTTP 500 on OpenAI's /api_keys endpoint — its rows have dollars+user but no key name/owner. Not fixable our side; accepted-and-labeled for now.

- Rollout ordering: the deployed daily Lambda must run this new code before backfilling the trailing ~2 days, or its cron re-clobbers them (it reverted June 15 once during rollout). Captured in docs/superpowers/runbooks/2026-06-17-openai-billed-dollars-per-key-rollout.md.

## Rollout status

Backup taken, schema migrated, June 1-15 backfilled (billed+user 100%). Remaining: deploy (this PR), older-history backfill, Finance sign-off.

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

<!-- CURSOR_AGENT_PR_BODY_BEGIN -->

## Summary

- Addresses reviewer findings on the P4.1 foundation slice: fixes cross-stage index invalidation in markdown_to_batch_requests, centralizes list presets, and hardens named-range seed/read helpers.

- Adds regression tests that simulate sequential request application and cover seed failure shapes.

## Why it's needed

- Review identified a contract bug where list-marker deletions invalidated heading/inline indices in a single batchUpdate batch.

- DRY and operability gaps (duplicated title index, silent named-range drops) needed correction before future /sync wiring.

## Changes

- Re-shift stages 5–7 indices after stage-4 list-marker deletions via _shift_after_deletions.

- Export GDOC_LIST_PRESETS / NAMED_RANGE_PREFIX; gdoc_service.format_bullet_lists imports shared presets.

- Extract build_spec_title_to_id in gdoc_sync.py; reuse in seed_section_named_ranges.

- seed_section_named_ranges: contextual reply-count errors, raise on missing namedRangeId, structured logging.

- read_named_range_sections: concatenate duplicate anchors, warn on missing range indices.

- Tests: sequential apply bounds checks, seed failure regressions, API nested namedRanges shape.

## Breaking changes

None

## Test plan

### Executed

- [x] uv run ruff format + ruff check + pyright on changed files

- [x] uv run pytest tests/board_doc/test_gdoc_batch.py -q (20 passed)

- [x] uv run pytest tests/board_doc/test_gdoc_sync.py -q (101 passed)

### Follow-up manual validation

- [ ] When wiring canonical writes into /sync, scope heading cleanup to inserted ranges instead of document-wide replaceAllText.

<!-- CURSOR_AGENT_PR_BODY_END -->

<div><a href="https://cursor.com/agents/bc-8756af04-11bd-43fe-89b3-fbe3a9a9670c"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/open-in-web-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/open-in-web-light.png"><img alt="Open in Web" width="114" height="28" src="https://cursor.com/assets/images/open-in-web-dark.png"></picture></a>&nbsp;<a href="https://cursor.com/background-agent?bcId=bc-8756af04-11bd-43fe-89b3-fbe3a9a9670c"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/open-in-cursor-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/open-in-cursor-light.png"><img alt="Open in Cursor" width="131" height="28" src="https://cursor.com/assets/images/open-in-cursor-dark.png"></picture></a>&nbsp;</div>

## Demo

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/6a1b0cfd-e86f-453b-b5a0-4129e21ffe92" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/2de7c447-9be1-4934-87e1-14dde6119292" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/4816f456-b22a-40e8-a7f6-350d4ee1e520" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/9cfec359-aa25-4201-b15e-c21c7765bdbe" />

Proof that the toggle now restates both Current and Past ARR date-aware (not the full −$34M from Current only), shown by importing and calling the real changed code directly — no HTTP, no DB.

Backend — getTwitterImpactSummary is now subscription-level (klair-udm)

Ran node /tmp/demo-klair2890-backend.mjs (imports the real buildSqlQuery + transformQueryResults):

=== Generated SQL (buildSqlQuery('getTwitterImpactSummary')) ===
WITH arr AS (
SELECT subsriptionid, MAX(subscriptionenddate) AS end_date
FROM core_finance.arr_detail_final
GROUP BY subsriptionid
)
SELECT i.impact, a.end_date AS subscription_end_date
FROM core_finance.arr_gap_twitter_impact i
LEFT JOIN arr a ON i.subsriptionid = a.subsriptionid
=== transformQueryResults() on a synthetic Redshift Data API payload ===
rows in -> subscriptions out : 4 -> 4   (1:1, no fan-out)
totalImpact === SUM(impact)   : -185 === -185  -> true
null end date passed through  : {"impact":-5,"subscriptionEndDate":null}   (not coerced)
doubleValue fallback parsed   : {"impact":-30,"subscriptionEndDate":"2026-04-15"}
empty records guard           : {"totalImpact":0,"subscriptions":[]}

MAX(subscriptionenddate) is aggregated per subscription in the CTE before the join, so detail-row fan-out can't double-count impact; LEFT JOIN keeps null-date rows; column order (impact, subscription_end_date) matches the transformer's row[0]/row[1].

Frontend — date-aware Current + Past restatement (klair-client)

Ran node /tmp/demo-klair2890-helper.mjs (imports the real applyKhorosTwitterImpact, transpiled verbatim) on a synthetic book whose end dates straddle each baseline, at report date 2026-05-31:

Full Twitter book (totalImpact) = -$230   |  raw currentArr=$1,000  pastArr=$900
type | pastBaselineCut | currentAdj | pastAdj | adjCurrent | adjPast | variance
MTD  |   2026-04-30    |   -$100    |  -$150  |    $900    |  $750   |   $150
QTD  |   2026-03-31    |   -$100    |  -$180  |    $900    |  $720   |   $180
YTD  |   2025-12-31    |   -$100    |  -$200  |    $900    |  $700   |   $200
TTM  |   2025-05-31    |   -$100    |  -$215  |    $900    |  $685   |   $215
OFF (includeTwitter=false): {"currentArr":1000,"pastArr":900,"variance":100,"currentAdjustment":0,"pastAdjustment":0}
-> byte-identical to raw -> true
strict-> boundary: sub endDate == report date -> currentAdjustment = 0 (excluded)

currentAdjustment is identical across all snowball types ("current is current", cut at the report date); pastAdjustment grows by horizon (MTD < QTD < YTD < TTM), each the date-filtered sum at its own baseline — never the full −$230 book. This is exactly the live-data shape from the acceptance table (Current −$5.84M ≪ TTM −$33.67M ≈ book −$34.09M).

UI — see it on the three surfaces (ARR & Retention Reports → Maintenance Summary)

1. Open the Maintenance Summary view; locate the "Include Twitter" page-level toggle.

2. Toggle it ON and watch the Khoros row/figures: both Current ARR and Past ARR drop (previously only Current moved), and DM / Variance recompute from the two adjusted figures.

3. Switch the snowball horizon MTD → QTD → YTD → TTM: the Current adjustment stays the same while the Past adjustment grows with the horizon.

4. Check the Key Metrics DM card (TTM) and the Acquisitions tab (TTM) — both now use the adjusted past as the DM denominator / variance baseline.

5. Toggle OFF → every Khoros figure returns to its raw value (exact no-op); retention columns (Gross/Net Retention, Churn, Up/Downsell, New Business) and non-Khoros classes never change.

> _Screenshot: Maintenance Summary with "Include Twitter" ON, showing Khoros Current and Past both adjusted — _<!-- paste screenshot here -->

Most at risk from this change — the #3044 OFF no-op, the strict->/baseline-date math, the no-fan-out backend shape, and the three caller surfaces. Verified via the scoped tests for exactly those (all encode the live golden numbers, the boundary, and byte-identical no-op):

frontend (vitest)  — applyKhorosTwitterImpact 22 + acquisitionsAdjustment 6 + useMaintenanceData 5 + KeyMetricsSummary 2  = 35 passed
backend  (node)    — klair-udm/tests/redshift (queryBuilder + responseTransformer)                                       = 78 passed

---

## fix(maint-report): Twitter toggle adjusts both current AND past ARR, date-aware

Linear ticket: KLAIR-2890

### Feature overview

The "Include Twitter" toggle on the ARR & Retention Reports Maintenance Summary restates Khoros figures to reflect the projected X/Twitter churn impact. The KLAIR-2884 implementation (PR #3044) had two bugs:

1. It subtracted the entire projected loss (≈ −$34M, the full Twitter book) from Current ARR only.

2. It left Past ARR untouched.

Both are wrong. The full-book subtraction is not date-aware: it counts subscriptions that have already expired as of the report date. And leaving Past unadjusted means Variance and DM mix an un-Twittered past with a Twittered current.

This PR makes the impact date-aware and applies it to both Current and Past ARR. For any ARR figure at reference date D, we subtract only the impact of subscriptions still alive at D:

adjustment(D) = SUM(impact WHERE subscriptionEndDate > D)   // strict >, impact is negative

- Current ARR uses D = report date (e.g. 2026-05-31), identical across all snowball types — "current is current" — so it subtracts only the remaining un-expired Twitter ARR (≈ −$5.84M at the live data point, not the full −$34M).

- Past ARR uses D = the per-snowball-type past baseline date (MTD → prior month-end, QTD → prior quarter-end, YTD → prior calendar year-end, TTM → −12 months), so Variance = adjustedCurrent − adjustedPast and DM = adjustedCurrent / adjustedPast exclude Twitter consistently in both numerator and denominator.

### Specs (both shipped in this PR)

- [03-twitter-impact-subscription-level-query](features/maint-report/arr-retention-twitter-toggle/specs/03-twitter-impact-subscription-level-query/spec.md) — backend (klair-udm): make getTwitterImpactSummary date-aware by returning per-subscription rows tagged with end dates instead of a single scalar.

- [04-date-aware-current-and-past-adjustment](features/maint-report/arr-retention-twitter-toggle/specs/04-date-aware-current-and-past-adjustment/spec.md) — frontend (klair-client): apply the date-aware restatement to BOTH current and past ARR across all three surfaces.

### Implementation summary

Backend data shape (klair-udm). buildTwitterImpactSummaryQuery() now JOINs core_finance.arr_gap_twitter_impact → core_finance.arr_detail_final on the (misspelled-on-both-tables) subsriptionid key. The subscription end date is aggregated as MAX(subscriptionenddate) per subscription in an arr CTE before the join, so detail-row fan-out cannot double-count impact. A LEFT JOIN preserves impact rows with no detail row / a null date. The transformer returns subscriptions[] of { impact, subscriptionEndDate } (ISO YYYY-MM-DD | null) and retains the scalar totalImpact = SUM(impact) for the OFF no-op guard. Wired through the standard five-file AppSync pattern (queryBuilder.mjs, responseTransformer.mjs, validators.mjs, app.mjs, gql/schema.graphql). The query stays argument-free; the 136-row book is filtered client-side.

Frontend date-aware application (klair-client). The pure helper applyKhorosTwitterImpact.ts is rebuilt to take the per-subscription rows + a current reference date + the snowball type, derive the per-type past baseline date via UTC date arithmetic (Date.UTC, no day-drift), filter with a strict-> lexicographic ISO comparison (null end dates excluded from both adjustments), and return adjusted current + adjusted past + variance = adjustedCurrent − adjustedPast + both adjustment deltas. useKhorosTwitterImpact.ts is widened to expose subscriptions[]. All three surfaces are wired and prop-threaded: Key Metrics DM (keyMetricsAggregate.ts / KeyMetricsSummary.tsx), Maintenance Summary by-BU (useMaintenanceData.ts), and the Acquisitions tab (acquisitionsAdjustment.ts). ~21 files changed.

### Golden-number acceptance (report date 2026-05-31, live 136-subscription book)

| Reference | Cut (endDate >) | Adjustment |

|-----------|-------------------|-----------:|

| Current (shared, all snowball types) | 2026-05-31 | −$5,840,045 |

| Past MTD | 2026-04-30 | −$6,666,222 |

| Past QTD | 2026-03-31 | −$10,500,447 |

| Past YTD | 2025-12-31 | −$19,795,377 |

| Past TTM | 2025-05-31 | −$33,667,003 |

Full book (totalImpact) = −$34,087,065. Past is the date-filtered sum per snowball type — it is never the full book magnitude.

### Test coverage

- Backend (klair-udm node): 78 pass (+5 new). Column-order contract, CTE-before-join no-fan-out, null/empty coalescing, and the totalImpact === SUM(impact) invariant.

- Frontend (vitest): 35 pass (+16 new). Golden numbers at report date 2026-05-31 (Current and all four past baselines above), per-type baseline-date derivation incl. mid-month / quarter / leap-year edges, strict-> boundary (endDate == D excluded), null-date exclusion, OFF / zero-impact / empty-subscriptions byte-identity, timezone safety under TZ=UTC+14 and UTC-11, and YYYY_MM_DD → YYYY-MM-DD underscore normalization.

### Self-review

No CRITICAL or IMPORTANT issues. One MINOR left as-is: SnowballType type-aliases the existing ARRSnowballType — defensible to keep the pure helper self-contained, zero behavior impact. Verified: date derivation correct across all edge cases (Jan / quarter / year / leap boundaries, Date.UTC no day-drift); the no-op is truly byte-identical including the DM denominator (no Infinity/NaN); the SQL has no fan-out and no injection surface (static query); the 'Acquisition' snowballType reaching useMaintenanceData is harmless (its output isn't rendered on the Acquisitions tab).

### Guarantees preserved

- OFF / totalImpact === 0 / empty subscriptions is an EXACT byte-for-byte no-op — Current, Past, variance, DM, sort order, and rollups all match the unadjusted path (no date math executed, DM denominator unchanged).

- Retention columns and non-Khoros classes are untouched. Gross/Net Retention, Churn, Downsell, Upsell, and New Business read raw backend values; only Khoros records are adjusted.

- Null end dates are excluded from both current and past adjustments (defensive "not alive" guard).

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

## Demo

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/0ada248c-3d92-4a35-8394-901906f01e1c" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/f89a0e94-37ee-4909-abbd-52972e78f224" />

## What

When the Include Twitter toggle is ON, restate the Khoros Churn line (and total variance) for the projected X/Twitter loss — not just the ARR figures — so Gross/Net Retention reflect the ex-Twitter book.

- Maintenance Summary by-BU table: Khoros Churn += (currentAdjustment − pastAdjustment), cascading Khoros → Total Organic → Grand Total

- Key Metrics card retention aggregate: same shift

- Acquisitions tab: intentionally unchanged (retention already uses the raw acquisition baseline on both sides → no artifact)

## Why

Follow-up to KLAIR-2890. The date-aware restatement shrank ARRPast (the retention denominator) but left raw Twitter churn in the numerator, so the same churn divided a smaller base and Gross/Net Retention fell artificially — a stakeholder asked *"why do gross & net retention go down when including Twitter?"*. It was a denominator-rebasing artifact (DM moved up while retention moved down).

The Twitter ARR that expired between a figure's past baseline and the report date now leaves the Churn line too. The shift is (currentAdjustment − pastAdjustment) ≥ 0 — the impact alive at the past baseline but expired by the report date, the same delta already applied to variance, so the snowball bridge stays balanced. Churn becomes smaller (less negative) and retention rises to the true non-Twitter level. Toggle OFF is an exact no-op.

## Testing

- vitest affected specs — 36/36 pass (new test asserts GRR/NRR rise when ON + churn cascade through Total Organic & Grand Total)

- tsc --noEmit, ESLint --max-warnings 0, Prettier — all clean

Linear: https://linear.app/builder-team/issue/KLAIR-2893/twitter-toggle-restate-churn-so-grossnet-retention-reflect-the-date

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