The Trilogy Times

## Problem

mercy could not review PRs larger than ~3-4k lines. The diff-size cap was 200 KB (~60K tokens), so a larger PR was truncated into a partial review — which surfaced to users as "I can't review this." We also want over-capacity PRs to be left for a human rather than getting a half-review or an approval.

## What this does (uniform default for every consumer — no per-repo .mercy.yml edits)

- mercy_config.DEFAULT_MAX_DIFF_BYTES 200 KB → 600 KB — the full-review window: the diff size mercy embeds and reviews in full. ~180K tokens, sized for the 1M-token context of the default model (Opus 4.8) with room left for the files the agent Reads for context (the prompt has it read changed files in full, typically 2-3× the diff), the rules, adaptive thinking, and output. ~12-15k changed lines.

- harness/size_gate.py (new) — resolves the cap from .mercy.yml (or the default) and decides whether a diff is too large. One source of truth, unit-tested.

- Workflow: Size-gate + Decline steps — a diff over the cap is no longer truncated into a partial review. The model / decide / submit steps are gated off and mercy posts a single, idempotent "📏 Too large to review — a human reviews" notice (per-SHA hidden marker; dry-run → job summary). No model run, no review, no approval.

A consumer can still override max_diff_bytes per-repo in its .mercy.yml; this only moves the shared default.

## Behaviour

| Diff size | Before | After |

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

| ≤ 200 KB | full review | full review |

| 200–600 KB | truncated partial review, approve withheld | full review |

| > 600 KB | truncated partial review, approve withheld | declined: no review, no approval, "too large" notice |

## Testing

- 123 harness tests pass (117 + 6 new size_gate tests); existing default-config assertion updated.

- ruff clean; workflow YAML parses; size_gate.py CLI smoke-tested (too-large true/false, default 600 KB, per-repo + explicit override).

## Rollout

- Surtr (rides @main) picks this up on merge.

- Klair / Aerie / Sindri / Trilogy-Drones (pinned @v1) pick it up when the v1 tag is moved to the merged commit.

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

## Problem

The public read API /v1 — the contract external consumers (Aerie/Klair) pull from — returns 404 in prod for every route, because the Clerk middleware allowlist (isPublicRoute) includes /internal and /api/health but not /v1. Signed-out M2M requests die at Clerk's protect-rewrite *before* /v1's own Bearer auth runs.

Verified live: GET https://surtr.klair.ai/v1/education/schools → 404, x-clerk-auth-reason: protect-rewrite, signed-out.

## Fix

Add /v1/(.*) to the allowlist, exactly alongside /internal/(.*) — both are M2M and enforce their own bearer auth in the Hono backend. /v1 stays protected by SURTR_API_KEYS (fail-closed in prod); this only removes the Clerk gate in front of it.

One line + comment. Not reproducible locally (Clerk gating is prod-only) — takes effect on the next prod deploy, after which external consumers can pull /v1 with their Bearer key.

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

## Demo

Proves the fix against real Redshift (read-only): queries work through the new checkout path, TCP keepalive is applied at the socket, a reaped socket is detected and recovered, and the exact endpoint that 500'd now returns data.

Backend — stale-connection recovery (the bug). Ran uv run python /tmp/demo-redshift-pool.py, which imports RedshiftHandler and calls it directly (no HTTP):

1. Baseline: real query through pool checkout
tuning: validate_after=60s  max_idle=300s  max_retry=3
ok         db
1  finance_dw
2. Option 2 — TCP keepalive applied at the socket level
SO_KEEPALIVE enabled: True
keepalive idle timer: 60s
3. Options 1+3 — reaped socket detected on checkout and recovered
[before fix] dead socket handed to query -> InterfaceError: connection is closed
classified as connection error: True
[fix] _ping(dead socket) -> False  (=> recycled)
INFO  Pooled connection idle 65s failed validation; replacing
[after fix] query succeeded: [{'recovered': 1}]
connection recycled: True (dead replaced with fresh)

This reproduces the prod failure — a reaped socket raises a connection-class error (locally connection is closed; in prod BrokenPipe: server socket closed) — and shows checkout pre-ping replacing it so the query still succeeds.

Backend — the exact endpoint that 500'd, end to end:

4. AWSSpendService.get_sync_info()   (the GET /sync-info call)
latest_quarter_with_data: 2026-Q2
latest_data_date:         2026-06-22
available_quarters:       ['2026-Q2', '2026-Q1', '2025-Q4']

Real data through the shared singleton handler — the dashboard's first request. (latest_data_date 2026-06-22 matches the "Jun 22" sync chip in the working screenshot.)

Most at risk from this change: the shared _get_connection checkout path (every Redshift query, reads + writes) and pool integrity after the _PooledConn swap. Verified via the scoped suite — validate/recycle/retry, pool-size preservation on query error *and* create failure, retry-then-succeed, retry exhaustion:

tests/test_redshift_handler_pool.py  ............  12 passed in 0.07s

---

## What

Fixes the AWS Spend dashboard 500ing on prod first-load after an idle period.

Root cause: RedshiftHandler's connection pool handed out long-idle sockets

that the network path (NLB/firewall/Redshift) had already reaped, without

validating them — surfacing as BrokenPipe: server socket closed. The single

retry couldn't recover when multiple pooled connections were stale at once.

## Why

The pool's connections are created once at worker startup and reused for hours.

redshift_connector enables SO_KEEPALIVE but leaves the idle timer at the OS

default (~2h), so idle sockets get reaped well before any keepalive probe fires.

Checkout did no liveness check, so dead sockets reached live queries.

## Changes (klair-api/utils/redshift_handler.py)

- Validate-on-checkout (pre-ping): SELECT 1 for connections idle ≥ REDSHIFT_VALIDATE_AFTER_SECONDS (default 60s); replace if dead.

- Recycle by max-idle: connections idle ≥ REDSHIFT_MAX_IDLE_SECONDS (default 300s) are recycled outright, no round-trip.

- TCP keepalive tuning: tcp_keepalive idle/interval/count set to keep sockets warm below the reap window.

- Bounded retry loop: read paths retry up to REDSHIFT_MAX_RETRY_ATTEMPTS (default 3) instead of once.

- Pool integrity: size is always preserved on query error or create failure.

- Hot path (recently-used connections) pays zero extra round-trips.

## Testing

- New tests/test_redshift_handler_pool.py — 12 tests covering hot-path skip-ping, gray-zone ping (keep/replace), max-idle recycle, pool-size preservation (query error + create failure), retry-then-succeed, retry exhaustion, keepalive kwargs. All pass.

- ruff format/check clean; pyright adds zero new issues (pre-existing _is_fresh errors only).

- Pre-existing failures in test_aws_spend_service/test_unblended_budget_submit confirmed identical on base — unrelated.

Linear: https://linear.app/builder-team/issue/KLAIR-2918

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

## Summary

The Google Docs add-on sidebar (KLAIR-2913, P5.9 Slice 3+4) goes from a thin baseline to a Budget-Bot-style review experience: split review/chat panes, per-finding actions, and Claire chat with applicable tool-proposal cards.

## Frontend (Sidebar.html / Code.gs)

- Split layout — review pane (top, scrolls) + chat pane (bottom, pinned input, grows to 50vh then scrolls). Chat is always visible; adds a \u201c\u2026thinking\u2026\u201d placeholder during the round-trip.

- Findings grouped by severity with Address with Claire (seeds chat via inding_id + ocused_section_id) and Propose fix (getProposal(section_id) \u2192 preview \u2192 apply). Kills the old hardcoded propose('financials') placeholder.

- Chat proposal cards:

ewrite_section applies directly;

egenerate_section \u2192 \u201cDraft this fix\u201d (regenerate with Claire\u2019s feedback) \u2192 preview \u2192 Approve & apply; other tools render read-only (tracked in P5.9e).

- Code.gs: getChat(message, focusedSectionId, findingId), getProposal(sectionId, feedback), ^GpplyChatProposal + sectionTitleForId_ (resolve section_id \u2192 heading title for apply).

## Backend (oard_doc_router.py / wizard_orchestrator.py) \u2014 add-on-scoped, in-app untouched

- /addon/propose accepts optional eedback (folds Claire\u2019s

egenerate_section guidance into the regeneration).

- handle_chat gains a orce_register_tools override; ^Gddon_chat always sets it so free chat can propose for any section the user names (matches Budget Bot, whose chat always carries a focused section). In-app callers default to the old behavior.

## Docs / tracking

- Bakes the add-on \u2194 Budget Bot parity matrix into BACKLOG.md (the durable parity driver) + the P5.9a\u2013g slice rows; sharpens P5.10 (port/native/drop).

## Test plan

- [x] pytest tests/board_doc/test_addon_chat.py tests/board_doc/test_addon_propose.py (31 pass; pins free-chat orce_register_tools)

- [x] Live: Address with Claire \u2192 reply + proposal card; regenerate Draft\u2192preview\u2192Approve applies; free-chat \u201crewrite the PQR section\u201d now proposes (was the blocker)

- [x] clasp push the FE for split-pane UX

Refs KLAIR-2913 (+ P5.9a\u2013g: KLAIR-2921/2922/2923/2924/2925/2926/2927).

Linear: KLAIR-2928 (https://linear.app/builder-team/issue/KLAIR-2928)

## Summary

New standalone /collections-summary page reproducing the per-BU weekly "Collections Review" Top Sheet (currently a manual Google Sheet) from Tesorio data landed in sandbox_finance. POC scope: Skyvera.

This is the POC for the Collections automation initiative (Rishap / Haider Khan). It proves the end-to-end path — Tesorio CSV → Redshift → Klair page — and reproduces the manual sheet faithfully.

## What's included

- Backend (klair-api/utils/collections_summary.py + 2 endpoints in fast_endpoint.py, gated by require_collections_access):

- GET /collections-summary — AR-by-Category × Aging matrix + summary block

- GET /collections-summary/invoices — invoice-level drill-down (filterable by category / aging / quarter)

- Frontend (klair-client/src/features/collections-summary/): standalone page with the summary block, category × aging matrix, invoice drill-down table, per-line tooltips (definition + data source), and a global "current quarter only" toggle.

## Data sources

| Field | Source |

|---|---|

| AR matrix, line A/B | sandbox_finance.tesorio_open_invoices (Tesorio open invoices) |

| Target (X), Collected-QTD (Y) | sandbox_finance.collections_targets (CollectIQ sheet) |

| Forecasted invoicing (D) | sandbox_finance.collections_invoicing_forecast (date-gated rule) |

| Promise-to-pay (C) | Defaults to 0 — manual collector input, pending a Klair UI |

| Net Beat/(Miss) | Computed [(A−B)+(C+D)] − (X−Y) |

## Validation

- Category derivation matches the source sheet's computed column 337/337 rows (0 mismatches).

- Matrix math reproduces the sheet's published matrix to $0.50.

- Verified live: Total A/R $19,607,701; quarter-only $12,140,755; Net −$496,580.

## Review hardening (2nd commit)

A high-effort code review surfaced and this PR fixes: NULL/non-canonical aging buckets are normalized so totals always reconcile, NULL-due invoices stay visible in the quarter view, the cache keys on report_date (no stale snapshots), and the Net formula docstring is corrected.

## Notes / follow-ups (Step 4, post-feedback)

- The sandbox_finance.* tables are populated by one-shot POC loaders today; the durable ingestion (Tesorio email→S3→Redshift + sheet syncs) will live in the Surtr pipeline.

- Deferred: Klair manual-input UI for collector statuses (C, Blocked), multi-BU rollout, the 3 narrative "Outstanding Items".

## Screenshot

http://localhost:3001/collections-summary

<img width="1010" height="739" alt="image" src="https://github.com/user-attachments/assets/3afaeebd-3e5c-499a-824d-e7fc79c6d874" />

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

## Demo

Proof that the AERIE-439 surfaces work: the vendor-breakdown reducer (spec 24) was run directly against fixtures and its real output is embedded below; the Opus 4.8 commentary action and the live Redshift actions (specs 24/25) are not invoked in CI (mutation-free rule — Anthropic + Redshift calls), so their reproduction commands are embedded instead; the drill-down panel + commentary UI (spec 26) get reviewer click-through steps with a screenshot placeholder.

Backend — vendor-breakdown reducer (spec 24, reduceVendorBreakdownForCell)

Ran a throwaway vitest spec that imports reduceVendorBreakdownForCell from chat/convex/finance/dashboards/perSchoolReducers.ts and calls it directly with fixtures (no DB, no network, no mutation):

=== HAPPY PATH (1 cell, 4 txns → 3 vendor groups + residual) ===
{
"lineTotal": 1000,
"sumItemized": 950,
"unitemizedAmount": 50,
"prefixWarning": false,
"vendors": [
{ "vendorKey": "acme co",        "vendorName": "Acme Co",        "total": 500, "txnCount": 2 },
{ "vendorKey": "beta llc",       "vendorName": "Beta LLC",       "total": 400, "txnCount": 1 },
{ "vendorKey": "unknown vendor", "vendorName": "Unknown vendor", "total": 50,  "txnCount": 1 }
]
}
--- INVARIANTS ---
Σ vendors[].total + unitemizedAmount = 1000 (== lineTotal 1000)
vendors sorted by total desc: Acme Co $500 (2 txns); Beta LLC $400 (1 txns); Unknown vendor $50 (1 txns)
casing/whitespace collapse → Acme Co txnCount = 2
vendorless → "Unknown vendor" present: true

This shows the core contract: matched txns grouped by normalized vendor ("Acme Co" + "ACME CO" collapse into one group, txnCount 2), a vendorless txn lands in "Unknown vendor", rows sorted by total desc, and the tie-out invariant Σ vendors + unitemizedAmount === lineTotal holds (a $50 residual folds into unitemizedAmount).

Most at risk from this change — the edge branches the diff disturbs (prefix-warning, empty-account throw, no-matched-txns), driven directly and held:

=== BLAST RADIUS 1 — no numeric prefix in accountName ===
prefixWarning: true | vendors: []
=== BLAST RADIUS 2 — empty accountNames throws ===
thrown message: "accountNames must be non-empty"
=== BLAST RADIUS 3 — no matched txns ===
vendors: [] | unitemizedAmount: 750 (== lineTotal 750)

Regression net — the committed test suites for the touched backend modules pass:

✓ convex/finance/dashboards/perSchoolReducers.test.ts        (22 tests)
✓ convex/finance/dashboards/modelCoverageCommentaryShared.test.ts (15 tests)
Test Files  2 passed (2) · Tests  37 passed (37)

Backend — live Redshift + Opus actions (specs 24/25) — NOT executed in CI

These hit Redshift and the Anthropic API and so are not run here (read-only/non-mutating rule + no prod target). Reproduction (local dev shell, with .env + ANTHROPIC_API_KEY set):

# Vendor breakdown for a real Programs cell (spec 24 live action):
npx convex run finance/dashboards/financialLive:getVendorBreakdownForCellLive \
'{"school":"Alpha Miami","period":"2026-Q1","accountNames":["70100 Curriculum"]}'
# AI commentary digest → Opus 4.8 (spec 25 action; force-bypass cache):
npx convex run finance/dashboards/financialLive:generateModelCoverageCommentary \
'{"period":"2026-Q1","force":true}'

Expected: the vendor action returns the same shape proved above over live Redshift rows (auth via requireSchoolPlAccess first); the commentary action returns { source: "claude", bullets: [...5–6...] } — or { source: "fallback", bullets: [<one bullet>] } when ANTHROPIC_API_KEY is absent (never throws).

UI — drill-down panel + AI commentary (spec 26)

1. Open Dashboards › Financials › Schools – Actual vs Model, select "All Schools" (consolidated view).

2. At the top, the Model coverage & variance band now shows an AI commentary block (Sparkles header) with a source badge — Opus 4.8, cached, or unavailable — and a flat 5–6 bullet executive summary (a 5-line skeleton appears while it generates).

3. In the "Model coverage by school" table, hover a row — it now shows a pointer cursor + chevron; click it.

4. A drill-down side panel slides in on the right (in-flow, not over the header), listing that school's biggest annualized cost line items across Headcount / Programs / Facilities, each with a magnitude bar and a "Top" badge on the top 3.

5. Expand a Programs/Facilities line → it shows aggregated vendor rows (name · $ · txn count, "Unknown vendor" handled). Expand a Headcount (Contracted-Labor) line → it shows aggregated contractor rows with "Unattributed" pinned last. The leaf is terminal (no per-transaction drill).

6. Press Escape or the X to close the panel; press Refresh — the P&L, the coverage band, and the commentary all re-run.

> _Screenshot: consolidated Actual vs Model page showing the AI commentary block in the band + the drill-down side panel expanded to vendor/contractor rows — _<!-- paste screenshot here -->

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/4bad9bee-56d1-425f-ac83-dc3c1e63ceb1" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/9ef32097-72b1-4cf0-b4d9-f1b1ac8882bb" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/56e66b85-433c-4689-a2ab-d22dc2a52481" />

---

## AERIE-439 — Consolidated Financials: Model-coverage drill-down panel + AI commentary

Two additions to the consolidated "Actual vs Model (Schools)" page (schools-avm, isConsolidated branch), both follow-ons to [AERIE-437](https://linear.app/builder-team/issue/AERIE-437) (model coverage & variance band):

1. Line-item drill-down side panel — clicking a row in the "Model coverage by school" table opens an in-flow side panel listing that school's biggest annualized cost line items across the three modeled sections (Headcount, Programs, Facilities/Support), annualized ×12/monthsLoaded, sorted descending, with a magnitude bar and a "Top" badge on the top three. Each line item expands to an aggregated terminal leaf (no per-transaction drill): vendors for Programs/Facilities, contractors (via the xoContractorIdentity alias dictionary) for Headcount/Contracted-Labor accounts.

2. AI commentary — an Opus 4.8 executive summary (flat 5–6 bullets, ~2-minute read) rendered inside the variance band, auto-generated on load and cached per period (24h TTL + variance-fingerprint invalidation). The digest feeds the model the same aggregated rollups (top vendors / top contractors for the single biggest line item), not raw transactions; falls back to a single explanatory bullet when ANTHROPIC_API_KEY is absent.

Linear: [AERIE-439](https://linear.app/builder-team/issue/AERIE-439) — Consolidated Financials: Model-coverage drill-down panel + AI commentary (vendor / contractor aggregated)

### Status — implemented + tested (ready for review)

All three specs are implemented, tested, and self-reviewed. The feature doc changelog and each spec's metadata Status are marked Completed.

### Specs (all Completed)

- 24 · vendor-breakdown-reducer-and-action _(backend, no deps)_

New pure, runtime-free reduceVendorBreakdownForCell in perSchoolReducers.ts — sibling of reduceContractorBreakdownForCell, minus the xoContractorIdentity identity dim. Same period + account-number-prefix matching and lineTotal math; groups matched transactions by normalized vendor (name = (vendorName ?? "Unknown").replace(/\s+/g, " ").trim(), key = lowercase; null/empty → "Unknown vendor"), returns { lineTotal, sumItemized, unitemizedAmount, prefixWarning, vendors: [{ vendorKey, vendorName, total, txnCount }] } (vendors sorted by total desc; unitemizedAmount tolerance-suppressed within UNITEMIZED_TOLERANCE_USD). New "use node" live action getVendorBreakdownForCellLive({ school, period, accountNames }) — twin of getContractorBreakdownForCellLive minus the identity read: requireSchoolPlAccess (auth before any Redshift connection) → parseDrilldownPeriod → fetchPlMonthlyRowsForSchool + fetchPlTransactionsForSchool → reducer. Compile-time AssignableTo parity guard added in financials-view.tsx. No new Redshift table/SQL.

- 25 · ai-commentary _(backend, no deps)_

New runtime-free modelCoverageCommentaryShared.ts (digest type carrying topContributors?: { name, amount, txnCount }[] + a vendors-vs-contractors flag; buildModelCoverageCommentaryPrompt; parseCommentaryBullets; fingerprintCommentary; COMMENTARY_MODEL = "claude-opus-4-8") + modelCoverageCommentaryStore.ts (internalQuery/internalMutation cache pair, 24h TTL, delete-then-insert upsert) + the generateModelCoverageCommentary Opus 4.8 action. The digest feed is switched to aggregated rollups: for the single biggest line item it loads top contractors (Contracted-Labor account) else top vendors; missing ANTHROPIC_API_KEY (or any Opus/parse failure) degrades to a single "fallback" bullet — never throws. New modelCoverageCommentary schema table { period, bullets[], source, fingerprint, generatedAt } indexed by_period; founding-family policy text fed in qualitatively via an internal query; npx convex codegen run (generate-only — no schema push).

- 26 · drilldown-panel-and-commentary-ui _(frontend, depends on 24 + 25)_

New model-coverage-line-item-panel.tsx (in-flow side panel matching the MappingsSidePanel / UnitemizedBreakdownSidePanel pattern — desktop flex child w-2/5, mobile MobileOverlayPanel gated by useMobileUI, wrapped in AnimatePresence, Escape + X close; per-account HC→contractor vs else→vendor terminal leaf with per-group loading/error/empty states) + new presentational model-coverage-commentary.tsx (Sparkles header + source badge: Opus 4.8 / cached / unavailable; 5-line skeleton; error/empty fallbacks; bullet list). model-coverage-by-school-table.tsx gains an optional onSelectSchool (clickable rows + chevron when present); model-coverage-variance-band.tsx gains an optional commentary? slot; financials-view.tsx holds drilldownSchool state, conditionally mounts the panel under AnimatePresence, fires the commentary action via useLiveSection, folds the commentary refetch into the consolidated Refresh, and adds parity guards. New frontend-safe isContractedLaborAccount in @bran/contracts/headcount-account-roles (regex /^\d+\s+Contracted Labor\b/) so the panel does not import the backend isHeadcountAccount.

### Implementation summary

- Backend (specs 24 + 25): one new pure reducer + one new live action (vendor breakdown), one new Opus 4.8 commentary action, two new runtime-free/shared modules (modelCoverageCommentaryShared.ts, modelCoverageCommentaryStore.ts), one new schema table (modelCoverageCommentary) + codegen, one new founding-family internal query. Both new live actions authorize via requireSchoolPlAccess before any Redshift connection; all "use node" DB I/O routes through internal query/mutation (an action has no ctx.db); reducers and shared helpers are runtime-free.

- Frontend (spec 26): two new components, three modified (model-coverage-by-school-table.tsx, model-coverage-variance-band.tsx, financials-view.tsx), one new frontend-safe predicate in @bran/contracts. All additions are backward-compatible (the band/table keep their original layout when the new optional props are omitted).

### Test coverage — 47 new tests, all green

- Spec 24: +9 in perSchoolReducers.test.ts (22 total) — grouping + sum + txnCount, vendor normalization (casing/whitespace collapse, "Unknown vendor"), sort, tie-out Σ vendors + unitemizedAmount === lineTotal, prefix-warning + empty-account throw.

- Spec 25: 15 in modelCoverageCommentaryShared.test.ts — prompt prints the aggregated rollup line with the correct vendors/contractors wording, omits it cleanly when absent; parseCommentaryBullets (well-formed / fenced / malformed-safe-degrade); fingerprintCommentary stability.

- Spec 26: +23 across 4 component test files (model-coverage-line-item-panel.test.tsx 14 new, model-coverage-commentary.test.tsx 9 new, plus band + table extensions), 45 total — HC vs non-HC leaf branch, terminal leaf (no further expand), loading/error/empty per group, source badge per source, fallbacks, bullet list, table click fires onSelectSchool, band commentary slot.

pnpm typecheck clean across all 5 workspaces; biome check clean on changed files.

### Self-review

No CRITICAL / security / Convex-correctness issues found:

- Both new live actions call requireSchoolPlAccess before any Redshift connection (per docs/endpoint-hardening.md); no new capability key (docs/capabilities.md).

- "use node" DB I/O goes through internalQuery / internalMutation (modelCoverageCommentaryStore.ts); the action has no direct ctx.db.

- Reducers and shared helpers are runtime-free (no Convex/Next/React runtime imports), per the architecture guardrails.

- Opus config correct (COMMENTARY_MODEL = "claude-opus-4-8", system + user prompt); missing key / Opus failure degrades to a "fallback" bullet rather than throwing.

- One MINOR comment fixed (commit 4189333a — corrected the max_tokens rationale comment).

### Resolved design decision

Concern B — network-wide commentary vs the scoped Miami + NY + Austin band. ✅ Resolved.

generateModelCoverageCommentary computes its digest network-wide (the action takes only { period }). That matches the unfiltered All Schools consolidated view, but would mismatch the AERIE-438 Miami + NY + Austin 3-school filter (AI prose describing the whole network beside a 3-school band).

Decision (requester): hide the AI commentary on the scoped 3-school view rather than re-scope the action. Commit de7b651f gates it via modelCommentaryEnabled = canQueryFinancials && schoolsAvmActive && isConsolidated && !consolidatedSchoolFilter — commentary is shown (and queried) only on the unfiltered All-Schools consolidated view, and is hidden (not queried; the band keeps its original single-row layout) under the Miami + NY + Austin filter. Two tests cover both states.

### Dependency chain

24 (vendor reducer+action) ─┐
├─► 26 (drill-down panel + commentary UI)
25 (AI commentary action)  ─┘

### Architecture notes

- Pure reducers stay runtime-free; live actions call requireSchoolPlAccess before opening any Redshift connection; cost-section gates reused so totals tie out to the P&L.

- New public Convex action follows docs/endpoint-hardening.md; cache store functions are internal* (implementation-only).

- modelCoverageCommentary cache is schema-defined (codegen only; no schema push).

- The frontend imports a runtime-free isContractedLaborAccount predicate from @bran/contracts/headcount-account-roles, not the backend isHeadcountAccount.

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

Aligns Surtr's education ontology to the live Aerie contract (edu-ops.klair.ai) so Aerie and other consumers can read the same entity/edge model from Surtr — sourced entirely from Redshift (Surtr stays the serving layer; no Aerie/Rhodes runtime dependency). Supersedes https://github.com/AI-Builder-Team/Surtr/pull/512 (it tagged the School→Program link at the wrong grain).

Verified against the live Aerie API + Aerie's Convex source: Aerie anchors School on dim_school (the Wrike folder id), the building is a separate Site, and edges are first-class Relationships with a confidence enum. Surtr's own primitives (objects / identity_aliases / relationships) map onto that 1:1.

## Phase 1 — School↔Program (marketedAs)

- School = dim_school (the master grain — not the inverted grain split that was explored and dropped).

- SCHOOL_SQL emits top_ties; deriveProgramConfidence() buckets the name-prefix link to Aerie's enum: unique winner → derived, tie → unverified.

- Verified live: 100 schools → 61 derived / 0 unverified / 39 unmatched.

## Phase 2 — Site + School↔Site (operatesAt)

- Site = core_education.fct_fto_site (the Wrike Fit-To-Open mart) — a 1:1 mirror of the Rhodes/portfolio site data already in Redshift, keyed by folder_id (== dim_school.school_id). Carries stage, lease dates, legal entity, zoning/AHJ/food-permit, lat/lng, capacity, grades, drive folder.

- /v1/education/sites(/{id}) + School↔Site cross-includes.

- ?include=relationships emits the operatesAt (School→Site) edge — an identity link by folder id, so authoritative.

- Verified live: 100 sites, folder_id joins 100/100 to School. Site folder_id == Aerie school's dim_school/schoolId alias (e.g. Alpha Armonk = MQAAAAEJ7eJE) — grain aligned end-to-end with Aerie.

## Deliberately deferred

- Metro — Redshift's Wrike Market == fct_fto_site.market (identical, 22≡22); there is no separate metro grain in the warehouse (Aerie's metro is Rebl-sourced). Building one would be invention.

- Rhodes slug / multi-building grain — fct_fto_site is folder-grained (1:1 with School); Aerie's Rhodes slug can be finer. folder_id is the join key here; the slug is simply absent in Redshift.

## API shape (mirrors Aerie within Surtr's domain-namespaced convention)

GET /v1/education/schools/{id}?include=relationships → [{from,to,relationship,source,confidence}]

- marketedAs School→Program (derived/unverified)

- operatesAt School→Site (authoritative)

## Verification

tsc clean · biome clean · 463/463 unit tests · live Redshift confirms both the 61/100 program coverage and the 100/100 site join.

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

## Summary

Completes the renewals_v2 → renewals_v3 cutover for the consumers PR #2811 left out of scope, and removes the env-gated v2 fallback so staging_salesforce.renewals_v2 can be safely dropped from Redshift. renewals_v2 last refreshed 2026-04-07 (dead); renewals_v3 is the source of truth.

## Why renewals_v3 needs care (the structural difference)

renewals_v2 was a single forward ~1-year snapshot (one row per contract). renewals_v3 is a continuous table spanning ~2024→2028 with every renewal cycle. Two duplication mechanisms, verified on live Redshift:

- SF-opportunity fan-out — one subscription, multiple SF opps on the same renewal_date. In every case only one row carries current_arr > 0; the rest are $0 dead opps. Self-neutralizing for sums.

- Cross-period physical-contract duplication (the dangerous one) — the same physical contract renews each cycle with a different parent_subscription_id, both rows ARR-bearing. 912 contracts / $316M exposure. ID dedup cannot catch this — the only guard is a ~360-day renewal_date window when summing.

## Changes

Consumer cutover (commit 1):

- routers/income_statement.py — SF customer search → renewals_v3 via a ranked_renewals CTE: filters current_arr > 0 AND renewal_date IS NOT NULL and a stage-priority ROW_NUMBER keeps one row per contract. No aggregation, so no ARR risk; parameterization unchanged.

- budget_goal_miper/renewals.py — table swap to renewals_v3. Its (year, quarter) + non-null filter is a ≤90-day window that already neutralizes both duplication mechanisms.

- Both sites document the 360-day windowing invariant for any future aggregation over renewals_v3.

Decommission the v2 fallback (commit 2):

- renewals/redshift_renewals.py — RENEWALS_TABLE is now a hard constant "renewals_v3" (removed the RENEWALS_TABLE=renewals_v2 env switch).

- renewals/renewal_details.py — collapsed to the v3 SSOT path; dropped the legacy renewal_data_sources branch and env switch.

- fast_endpoint.py — /renewals + /renewals/invalidate-cache cache keys always v3; removed the dead version param.

## Validation

- Live Redshift, budget_goal_miper 2026 Q2: raw SUM(current_arr) == dedup SUM == $25,523,472.34 (IgniteTech) — zero inflation within the quarter window. Other BUs: row count == sub count.

- ruff format + ruff check clean; pyright 0 errors on changed files.

- pytest tests/budget_goal_miper/test_renewals.py → 10 passed. (Some renewals/router tests error at collection on missing AWS/Google creds — pre-existing on main, unrelated to this diff.)

## Table-drop coverage

After this PR, no klair-api code path can target renewals_v2. Remaining references are the renewals_v2.csv cron (naming coincidence — never reads the table) and doc-comments/tests.

⚠️ Out of repo: the klair-mcp-ts package still lists staging_salesforce.renewals_v2 as a queryable table (query-renewals-dashboard.ts, definitions.ts, DDL table_source_comments.sql). Clean that alongside the physical table drop.

## Not in scope

Fionn-fallback join in the renewals_v3 producer lives in Surtr (opportunity_router.py), tracked separately.

🤖 Draft — pending diff review + full test run before marking ready.

## Summary

Two correctness fixes to the add-on's applySection renderer, both surfaced by the first live end-to-end test (Skyvera scratch doc, KLAIR-2909):

1. Parse markdown ATX headings. ### MIP 1: ... now renders as a bold paragraph instead of leaking the literal ### into the doc.

2. Replace the full section span. endIdx stopped at the *first* non-NORMAL paragraph, so a section whose existing content used real sub-headings (old MIP sub-headers are HEADING3) ended early — nothing got deleted and the new content landed above the stale content (doubled MIPs). The section now runs to the next heading at the same-or-higher level (headingRank_), and the delete set includes LIST_ITEM as well as PARAGRAPH so native tables (the P&L) are preserved.

## Design notes

- Headings render as bold paragraphs, not real Docs headings. A non-NORMAL heading would itself become a section boundary and re-introduce the early-endIdx bug on the next apply.

- headingRank_ ranks TITLE/SUBTITLE/HEADING1-6/NORMAL by prominence; a boundary is any non-NORMAL heading whose rank is <= the section heading's rank. In-section sub-headings (lower prominence) are skipped.

## Known gap (tracked, not in scope here)

Table-bearing sections (Financials) still mangle, because /addon/propose returns the section as markdown and the P&L is a markdown table (pipe rows) that this prose renderer flattens to text. The native Docs table itself is untouched. Right fix is to skip table blocks in the proposal / leave the native table alone — KLAIR-2915.

## Test plan

- [ ] clasp push to the test deployment

- [ ] Draft a fix for MIPs -> Approve & apply -> confirm: no ### leak, sub-headers bold, old MIPs gone (no doubling)

- [ ] Re-apply a second time -> confirm the full section is replaced (no truncation at the first bold sub-header)

Refs KLAIR-2915.

## Demo

Proves the fix against real Redshift (read-only): queries work through the new checkout path, TCP keepalive is applied at the socket, a reaped socket is detected and recovered, and the exact endpoint that 500'd now returns data.

Backend — stale-connection recovery (the bug). Ran uv run python /tmp/demo-redshift-pool.py, which imports RedshiftHandler and calls it directly (no HTTP):

1. Baseline: real query through pool checkout
tuning: validate_after=60s  max_idle=300s  max_retry=3
ok         db
1  finance_dw
2. Option 2 — TCP keepalive applied at the socket level
SO_KEEPALIVE enabled: True
keepalive idle timer: 60s
3. Options 1+3 — reaped socket detected on checkout and recovered
[before fix] dead socket handed to query -> InterfaceError: connection is closed
classified as connection error: True
[fix] _ping(dead socket) -> False  (=> recycled)
INFO  Pooled connection idle 65s failed validation; replacing
[after fix] query succeeded: [{'recovered': 1}]
connection recycled: True (dead replaced with fresh)

This reproduces the prod failure — a reaped socket raises a connection-class error (locally connection is closed; in prod BrokenPipe: server socket closed) — and shows checkout pre-ping replacing it so the query still succeeds.

Backend — the exact endpoint that 500'd, end to end:

4. AWSSpendService.get_sync_info()   (the GET /sync-info call)
latest_quarter_with_data: 2026-Q2
latest_data_date:         2026-06-22
available_quarters:       ['2026-Q2', '2026-Q1', '2025-Q4']

Real data through the shared singleton handler — the dashboard's first request. (latest_data_date 2026-06-22 matches the "Jun 22" sync chip in the working screenshot.)

Most at risk from this change: the shared _get_connection checkout path (every Redshift query, reads + writes) and pool integrity after the _PooledConn swap. Verified via the scoped suite — validate/recycle/retry, pool-size preservation on query error *and* create failure, retry-then-succeed, retry exhaustion:

tests/test_redshift_handler_pool.py  ............  12 passed in 0.07s

---

## What

Fixes the AWS Spend dashboard 500ing on prod first-load after an idle period.

Root cause: RedshiftHandler's connection pool handed out long-idle sockets

that the network path (NLB/firewall/Redshift) had already reaped, without

validating them — surfacing as BrokenPipe: server socket closed. The single

retry couldn't recover when multiple pooled connections were stale at once.

## Why

The pool's connections are created once at worker startup and reused for hours.

redshift_connector enables SO_KEEPALIVE but leaves the idle timer at the OS

default (~2h), so idle sockets get reaped well before any keepalive probe fires.

Checkout did no liveness check, so dead sockets reached live queries.

## Changes (klair-api/utils/redshift_handler.py)

- Validate-on-checkout (pre-ping): SELECT 1 for connections idle ≥ REDSHIFT_VALIDATE_AFTER_SECONDS (default 60s); replace if dead.

- Recycle by max-idle: connections idle ≥ REDSHIFT_MAX_IDLE_SECONDS (default 300s) are recycled outright, no round-trip.

- TCP keepalive tuning: tcp_keepalive idle/interval/count set to keep sockets warm below the reap window.

- Bounded retry loop: read paths retry up to REDSHIFT_MAX_RETRY_ATTEMPTS (default 3) instead of once.

- Pool integrity: size is always preserved on query error or create failure.

- Hot path (recently-used connections) pays zero extra round-trips.

## Testing

- New tests/test_redshift_handler_pool.py — 12 tests covering hot-path skip-ping, gray-zone ping (keep/replace), max-idle recycle, pool-size preservation (query error + create failure), retry-then-succeed, retry exhaustion, keepalive kwargs. All pass.

- ruff format/check clean; pyright adds zero new issues (pre-existing _is_fresh errors only).

- Pre-existing failures in test_aws_spend_service/test_unblended_budget_submit confirmed identical on base — unrelated.

Linear: https://linear.app/builder-team/issue/KLAIR-2918

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

<!-- CURSOR_AGENT_PR_BODY_BEGIN -->

## Summary

Brings the Google Docs add-on chat (POST /board-doc/addon/chat) to parity with the in-app Claire: (a) tool-calling — when the sidebar is focused on a section, Claire can now propose section rewrites/edits that come back as structured proposal cards; and (b) "address this finding" seeding — clicking a finding kicks off a focused chat turn seeded with that finding's context server-side. The add-on is non-streamed (Apps Script UrlFetchApp can't consume SSE), so the agentic loop runs server-side and the structured proposals are returned in the response. Sub-issue of KLAIR-2906; feeds the rich sidebar KLAIR-2913. Rebased on the merged P5.9a (KLAIR-2921, finding_id).

## Why It's Needed

Today the add-on calls handle_chat(session, body.message) with no focused_section_id, so per its own contract it registers no tools — the turn is plain Q&A. The sidebar therefore can't surface proposal cards, and there's no way to seed a chat turn from a review finding the way the in-app review→chat flow does. This wires the existing focused-section tool path and finding-context builder into the add-on so the sidebar can reach feature parity with the in-app editor.

## Changes

- AddonChatRequest — adds optional focused_section_id (max_length=200, mirrors WizardChatRequest) and finding_id (bounds + character-class mirror the finding_id Path-param on wizard_update_finding_status).

- addon_chat endpoint:

- When finding_id is set, resolves the finding via the shared _resolve_session_finding helper (404 if missing, 409 if no review has run) and builds a finding-context seed message server-side, prepended to the user's message so a typed note isn't dropped.

- The seeded finding's own section_id is authoritative for tool registration: it drives focused_section_id (defaulting it when the client omits it, overriding a divergent client value, and clearing it for a doc-level finding), mirroring the in-app useFindingAddressalQueue. This prevents a section-scoped finding from producing a proposal-less turn, and stops a doc-level finding from proposing against an unrelated section.

- With neither field set, behavior is byte-for-byte the original text-only Q&A (no tools, empty proposals).

- Finding seed = byte-for-byte FE parity: _build_finding_seed_message mirrors the in-app buildFindingChatMessage exactly — same lead-in, same addresses_finding_ids directive (emitted unconditionally, including doc-level, matching the FE), same ordering (lead-in → directive → body), and a plain-text (no markdown bold) body via _render_finding_seed_body mirroring the FE findingBody. (Deliberately not the orchestrator's _render_finding_for_chat, which emits bold labels for the SYSTEM prompt; a seed is a USER message where bold would leak literal ** and nudge Claire to echo bold into an add_comment body.)

- AddonChatResponse — adds proposals: list[AddonChatProposal] (default []). AddonChatProposal = {tool, title, tool_use_id, section_id?, truncated_fields, input} and carries the full validated tool input plus truncated_fields verbatim from ToolCall.model_dump(), so the projection is lossless for every propose-only tool — exactly the shape the in-app ChatToolProposal reads off call.input. The word-level diff for a rewrite is computed client-side (diffWords) from input["new_content"], so the BE ships no word_diff.

- Non-streaming proposal-collection (investigation result): proposals are not delivered via emit (that only carries delta/status); both the streamed done payload and the non-streaming response build cards from StepResponse.data["tool_calls"]. The add-on reads that same return value (no emit shim) via _addon_proposals_from_tool_calls.

- DRY: the 409/404 finding-lookup contract is shared with wizard_update_finding_status via _resolve_session_finding (one source of truth). No tool logic or agent loop is reimplemented.

- Existing 529→503 mapping and persist path are unchanged.

## Breaking Changes

None — additive optional request fields (focused_section_id, finding_id) + an additive proposals response field. Plain chat (neither field set) is unchanged behavior; the existing Apps Script consumer that reads only .reply is unaffected.

## Test Plan

uv run pytest tests/board_doc/test_addon_chat.py

17 passed, covering: focused-section proposal projection (lossless input + tool_use_id + truncated_fields); section-less / non-rewrite / unknown-tool projection branches incl. truncated_fields passthrough; finding-seed threading + section coupling (client-omits, divergent-client-override, doc-level clear); doc-level written-guidance lead-in; 404/409 finding-resolution; and the unchanged plain-chat / access-control / 529→503 paths.

Regression-checked shared helper + rename: tests/board_doc/test_finding_status_endpoint.py, test_chat_focused_findings.py, test_b7_5_tables_only_and_doc_wide_findings.py, test_wizard_orchestrator.py all pass. ruff format + ruff check + pyright routers/board_doc_router.py all clean.

## Verification Artifact

$ uv run pytest tests/board_doc/test_addon_chat.py -q
.................                                                        [100%]
17 passed, 1 warning in 1.30s
$ uv run pyright routers/board_doc_router.py
0 errors, 0 warnings, 0 informations

<!-- CURSOR_AGENT_PR_BODY_END -->

<div><a href="https://cursor.com/agents/bc-0deeb498-b1c6-4c08-9d81-62e0a29cc2b9"><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-0deeb498-b1c6-4c08-9d81-62e0a29cc2b9"><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>