The Trilogy Times

## Summary

Adds nine deterministic QuickBooks financial marts to the existing mart_education

schema, published atomically by a single Redshift stored procedure that a dedicated

Lambda runner invokes. The runner is wired to the platform's existing

triggers.on_pipeline_success subscription to quickbooks-raw-sync, but success alone

is not treated as readiness — raw-sync intentionally reports successful checkpoint

outcomes. Before touching Redshift, the mart runner reads raw-sync's durable S3 state,

requires an idle active state with no pending sync or clean publication, fetches the

exact versioned completion manifest, and verifies its size and SHA-256. Only a complete

backfill or sync manifest is accepted; anything else returns a cheap skipped. The

accepted manifest's identity is pinned into mart lineage, and the procedure rejects any

required raw publication newer than the accepted completion. This is a build-and-

validate PR — legacy QuickBooks tables are untouched and no consumers (Aerie, Klair,

Core) are repointed; consumer cutover is explicitly out of scope.

The nine marts and their sources:

| Mart | Source |

|---|---|

| quickbooks_ap_transactions | raw_bill, raw_vendorcredit |

| quickbooks_bill_payments | raw_billpayment |

| quickbooks_bills | raw_bill |

| quickbooks_deposits | raw_deposit, raw_account |

| quickbooks_expense_transactions | raw_purchase |

| agg_quickbooks_financial_metrics | Same-refresh expense candidate |

| quickbooks_journal_entries | raw_journalentry |

| quickbooks_purchases | raw_purchase |

| quickbooks_vendor_credits | raw_vendorcredit |

Five non-deterministic legacy outputs (quickbooks_expense_reports,

quickbooks_pl_data, quickbooks_pl_monthly, qb_cost_opportunities,

qb_vendor_classifications) are deliberately excluded. All sources are assumed

populated by PR #759 under staging_education_quickbooks.

## Refresh architecture

quickbooks-raw-sync  (Step Function SUCCEEDED, any outcome)
→ EventBridge starts mart-education-quickbooks-refresh via on_pipeline_success
→ runner reads raw-sync durable S3 state
· requires idle active state, no pending sync / clean publication
· fetches exact versioned completion manifest, verifies size + SHA-256
· accepts only a complete backfill / sync manifest, else skipped
· skips a manifest already present in mart lineage (dedupe)
→ Lambda calls one Redshift procedure, pinning the accepted manifest identity
→ all nine marts publish atomically, or the whole refresh rolls back

- The refresh consumes the platform's standard success trigger — **no custom event bus,

no events:PutEvents, no bespoke on_data_ready trigger type.** Readiness is derived

and verified from durable state, not from event payload.

- backfill_progress, sync_progress, and skipped_overlap executions start an

inexpensive run that returns skipped; they cannot refresh marts from a mid-cycle

staging state.

- as_of_date defaults to the manifest's UTC completion date; an operator may override

it for a deliberate recovery run.

- At-least-once EventBridge delivery and manual recovery are safe: exact manifest

identity is recorded and deduplicated, so a manifest publishes at most once.

### Changes

Completion-manifest readiness gate *(new)*

- src/completion_state.py *(new)* — Reads raw-sync's durable S3 state, requires an

idle active state with no pending/checkpointed work, resolves the exact versioned

backfill_manifest / last_incremental_manifest, verifies size and SHA-256 against

the immutable reference, validates schema versions and extraction-id/key agreement,

and returns a verified CompletionManifest or an expected safe skip.

- src/handler.py — Loads the verified completion, short-circuits to skipped when

staging is not ready or the manifest was already consumed, defaults as_of_date to

the manifest completion date, passes the pinned manifest identity into the procedure,

and asserts the committed audit pins that exact manifest and timestamp.

- pipeline.json — Switches the trigger to on_pipeline_success, adds

UPSTREAM_STATE_S3_BUCKET / UPSTREAM_STATE_S3_KEY, and grants read-only

s3:GetObject/s3:GetObjectVersion on the exact state object and the production

quickbooks/manifests/* prefix only.

Stored procedure & lineage

- ddl/sp_refresh_quickbooks_financial_marts.sql — Adds seven completion-manifest

parameters, validates them, dedupes on manifest identity (concurrent duplicate call

returns without changing marts), rejects any required raw ledger publication newer

than the accepted completion timestamp, and writes the pinned manifest into every

audit row. Procedure signature widened accordingly.

- ddl/refresh_run_lineage.sql — Adds the seven NOT NULL completion-lineage

columns to the audit table.

- ddl/migrations/2026-07-21_completion_manifest_lineage.sql *(new)* — Adds the

seven completion-lineage columns (nullable, since historical rows predate pinning).

- ddl/migrations/2026-07-21_remove_unguarded_procedure.sql *(new)* — Drops the

obsolete two-argument procedure, applied only after the guarded signature exists so

there is no callable gap.

Marts, runner, reconciliation *(unchanged foundation of this PR)*

- Nine per-mart DDLs + agg_quickbooks_financial_metrics.sql, the warehouse-owned

naming migration, the Redshift Data API client (src/redshift_client.py) with

deadline-bound cancellation, the read-only legacy reconciliation tooling

(scripts/reconcile_legacy.py, RECONCILIATION.md), and owner registration.

Docs

- README.md — Documents the success-trigger readiness gate, the S3 state /

manifest verification, the skip semantics, and the ordered migration/deploy steps.

### What was removed vs. the initial approach

An earlier iteration of this branch published a bespoke Pipeline Data Ready EventBridge

event and added an on_data_ready CDK trigger type. That was replaced with the

durable-state readiness gate above. This PR therefore reverts all of it, and the net

diff against main contains no CDK changes and no quickbooks-raw-sync changes:

- Removed the on_data_ready trigger type, its EventBridge rules, schema, resource-name

helper, and CDK tests.

- Removed custom event publishing from quickbooks-raw-sync/src/main.py, its

events:PutEvents IAM grant, and its event tests.

### Design Decisions

- Success is not readiness. Because raw-sync legitimately succeeds on checkpoint

outcomes, the mart proves readiness from durable state and a checksum-verified

immutable manifest rather than trusting the trigger. Unready executions cost one cheap

skipped run.

- Manifest identity is the idempotency key. Both the handler and the procedure dedupe

on the exact (bucket, key, version_id, sha256), so at-least-once delivery and manual

replay publish each completion at most once.

- Fail closed on races. The procedure rejects any required raw publication newer than

the accepted completion timestamp, so a delayed trigger cannot publish against a later,

partially-checkpointed extraction.

- Atomic, all-or-nothing publication. All nine marts are replaced in one transaction,

each verified against its candidate before commit; any failure rolls the refresh back.

- Warehouse-owned naming. Columns follow warehouse conventions

(txn_date → transaction_date, doc_number → document_number,

credit_flag → is_credit, adjustment → is_adjustment, currency →

currency_code, qb_created_time → quickbooks_created_at, generic IDs →

bill_id/purchase_id/journal_entry_id/…); the aggregate is

agg_quickbooks_financial_metrics. The rename migration preserves existing rows.

### Known exceptions & limitations

- Expense account mappings 140/93 → 63210/63220 are hardcoded rather than governed

through a queryable mapping table.

- The procedure reads staging raw_* JSON because the clean projections do not

preserve every required field.

- Runtime uses the shared CQL_download_OM Redshift identity.

- Surtr deployment does not automatically apply warehouse DDL in a fresh environment;

migrations are applied manually in the documented order.

- Currency handling was intentionally not expanded.

- Consumer cutover is out of scope for this PR.

## Test Plan

- [x] Mart runner tests — 70 passed (readiness gate, manifest size/SHA-256 verification,

skip semantics, handler input/audit validation, Data API timeout cancellation,

reconciliation safety, read-only SQL enforcement, mart SQL contracts, atomic publication)

- [x] quickbooks-raw-sync tests — 83 passed

- [x] CDK tests — 641 passed / 17 suites (count dropped from 648 with on_data_ready

tests removed)

- [x] Ruff lint + format clean; CDK TypeScript build passes

- [x] Net diff against main contains no CDK or quickbooks-raw-sync changes; no

remaining on_data_ready references

- [x] Warehouse naming migration, DDL, guarded procedure, and one atomic refresh applied

to production Redshift; nine marts populated and reconciled with zero unexplained

differences (8 marts accepted expected_snapshot_drift;

quickbooks_expense_transactions accepted non_reconcilable_historical). No tables

dropped.

- [ ] Reviewer: confirm no legacy QuickBooks table or downstream consumer is repointed

## Summary

Enables the three HubSpot raw-sync lane schedules (crm, marketing, events), which have been shipped disabled since #914. All three lanes have now run green in production and been verified end-to-end before flipping them on:

| Lane | Run | Duration | Verified |

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

| events | 24df2bb1 | 18m | 3/3 scoped; steady-state occurrences via watermark |

| marketing | 5740b713 | 3h04m | 13/13 scoped; association-skip guard held live |

| crm | 489a21a0 | 1.85h | 20/20 scoped; emails delta=1,570 via corrected merge SQL |

State-pointer partition is clean: 489a21a0→20, 5740b713→13, 24df2bb1→3 = 36, each lane advancing only its own resources.

## Changes

Flips enabled: false → true on all three schedules, and converts crm from rate(6 hours) to cron so every schedule fires at a known wall-clock time (rate() fires relative to rule creation — unobservable, and awkward to validate the first fire). Cron minutes avoid the :00/:30 stampede and are staggered:

| Lane | Expression | Fires (UTC) | Rationale |

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

| crm | cron(17 0/6 * * ? *) | 00:17 / 06:17 / 12:17 / 18:17 | 6h; ~1.85h run |

| events | cron(53 0/6 * * ? *) | :53 every 6h | 18m run, lands ~36m into a crm block — every cycle exercises the disjoint-lane lock overlap |

| marketing | cron(11 3 * * ? *) | daily 03:11 | in a crm-idle gap so the 3h run mostly runs alone, avoiding a 3-way heavy overlap on the shared quota |

## Safety

- Schedules only ever fire in prod — CDK gates enabled && env === 'prod', so dev/staging are unaffected.

- Per-lane locks make disjoint-lane overlap safe (proven: 3 independent lane runs, clean partition); the shared portal-quota table governs total request pressure across any concurrent lanes.

- Overlapping runs of the *same* lane fail fast at AcquireRunLock (intended — a scheduled cadence skips rather than degrading to partial).

## Testing

- real-pipeline-configs.test.ts (406) validates the edited pipeline.json against the schema.

- CDK schema + ecs-pipeline construct tests (125) pass — named-schedule params + rule wiring.

## After merge / rollout

Deploy arms the EventBridge rules. Recommend watching the first scheduled fire of each lane complete on its own trigger (events at the next :53, crm at the next :17, marketing at 03:11) before considering it hands-off — and observing the first natural crm+events overlap exercise the lock in production.

🐦‍⬛ Generated by a very good bot

## Problem

collections-weekly-forecast-sync-v2 resolves its three anchor rows with _find_row_by_label, which returns the first column-B match across the whole grid. The three IgniteTech + Khoros weekly files stack three blocks (: IgniteTech, : Khoros, : Total) over identical week columns, so every run since the runner shipped has parsed block 1 and silently discarded the rest.

staging_finance_gsheets.collections_weekly_forecast_actual has zero Khoros rows in any quarter. Klair's IgniteTech weekly trend and Q1/Q2 locked-quarter cards have therefore been Khoros-exclusive, out of step with the Top Sheet.

Requested via GDoc "Collections x Klair", Comments §1 (Rishap / Haider).

## Fix

Segment first, then anchor. Split the grid on column-B block headers, then run the existing label-anchored logic *within each block*. No hardcoding — structure drives the parse:

- One unnamed block → BU from the filename (unchanged; 12 of 15 files).

- Named suffix → BU via normalize_bu; an unrecognized name fails loud.

- : Total is skipped — it is the sheet's own sum of the blocks above it, and

the DDL grain is one row per (quarter, business_unit, week_ending). Loading it would double-count every SUM() Klair runs.

Three further changes fell out of building it:

1. Closed-quarter accounting dash → $0. A $ - in an *actual* cell means

"not collected yet" while a quarter is open, but a real $0 once it closes. Q2's Khoros 30-Jun cell is $ - against IgniteTech's $73,434; under Klair's strict merge the parser fix alone would have dropped $73,434 from Q2's locked-quarter Actual. Derived from the folder's quarter, never a row date, so a re-run never retroactively rewrites data.

2. Two-check duplicate guard. One file now legitimately yields two BUs. The

guard is split into an intra-file (BU, week) check and a cross-file BU-set check — a single pass cannot catch both the stray Copy of … file and the same BU repeated in two blocks.

3. Fail loud on an orphaned block. If a block loses its column-B header, its

rows get absorbed into the preceding segment and silently dropped. This one found a real problem — see below.

## Verification

Read-only dry run against the live sheets (run_local.py --dry-run, exit 0, zero writes):

| | Before | After |

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

| Rows | 252 | 294 |

| (quarter, BU) groups | 18 | 21 |

| Khoros rows | none | Q1, Q2, Q3 — 14 weeks each |

Folded IgniteTech + Khoros, matching the source sheets' own Total blocks exactly:

| Quarter | Forecast | Actual |

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

| 2026-Q1 | $49,842,447 | $53,166,380 |

| 2026-Q2 | $31,153,450 | $31,400,854 |

Both closed quarters reconcile to the dollar. Q2's actual is correct *because of* the closed-quarter dash rule — without it that figure lands $73,434 light.

2026-Q3 is the open quarter and moved during verification (Finance posted the 26-Jul week mid-session); the parser reproduces the sheet's current totals exactly.

Tests: 44 passing — the 23 pre-existing tests are untouched and remain the regression guard for the 12 single-BU files. ruff format / ruff check clean.

## Source-data issue this surfaced

The orphan-block guard fired on Q1 '26 Collections Forecast vs Actual: GFI, which carried two complete data blocks under one header — the second hidden in the UI, so invisible to anyone reading the sheet. Actuals matched across all 14 weeks; the 25-Jan forecast differed by exactly $1,000,000 ($1,216,161 vs $216,161).

Haider confirmed the $1M addition is intentional and block 1 ($7,436,569 — "the $1.5M beat") is authoritative, which is what Redshift already held. He relabelled the stale block's anchor to [SUPERSEDED] Week Ending — pre-$1M adjustment, leaving all values and cell addresses intact so linked files are unaffected.

The pipeline had been loading the correct block by luck. It is now verified rather than accidental.

## Deploy

No ordering constraint. Klair's fold soft-fails to today's behaviour while zero Khoros rows exist — it is already merged on the Klair side and needs no redeploy when these rows land. Merging here does not deploy; that is a separate main → production PR.

Post-deploy check:

SELECT quarter, business_unit, COUNT(*) n, SUM(forecast) f, SUM(actual) a
FROM staging_finance_gsheets.collections_weekly_forecast_actual
GROUP BY 1, 2 ORDER BY 1, 2;

Expect 21 groups / 294 rows, every pre-existing group unchanged.

## Notes for review

- full_replace is atomic (DELETE + COPY in one transaction, EmptyParseError

before any write), so a bad parse fails without wiping the table.

- collections_forecast_summary / _breakup use the opposite Khoros

convention (natively Khoros-inclusive) and are deliberately untouched — see §6 of the spec. Do not "fix" them into consistency.

- One open question with Haider, non-blocking: whether $ - in a closed quarter

should read as $0 (our reading, and what his Total block implies) or as unavailable. Q2's quarter total is $31,400,854 either way; only whether the 30-Jun bar renders changes.

Spec: docs/superpowers/specs/2026-07-27-collections-weekly-multiblock-parse-design.md Plan: docs/superpowers/plans/2026-07-27-collections-weekly-multiblock-parse.md

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

Productizes the weekly NHC overspend-alert email system end-to-end.

Linear: KLAIR-3019

## What's in this PR

Phase A — productization

- Weekly fan-out orchestrator (run_all_overspend_alerts): one run sends the consolidated Finance email + one per BU + one per CF (EDU consolidated-only); suppresses no-overspend / no-recipients entities; per-entity failure isolation; surfaces soft SES failures.

- DynamoDB recipient store (overspend_email_recipients) + super-admin CRUD API (routers/overspend_emails.py); failure-notify recipients in GLOBAL.notify.

- Weekly cron (weekly_overspend_alerts_cron.py) replacing the daily cron; no in-app enable gate (the EventBridge rule is the switch); --entity manual override; best-effort notify_failure.

Per-email ledger migration

- mart_finance.overspend_alert_runs reshaped from per-vendor-line to per-email grain (vendor detail → alerts JSON), ~231 rows/run → ~21. Idempotency via ledger_has_sent; DECIMAL-overflow + trigger_source width fixes; prod DML grant to team_engineers.

Phase A.5 — admin recipients screen

- React super-admin screen (OverspendEmailRecipients, fork of QtdEmailRecipients) for GLOBAL TO/CC/BCC/Notify + per-BU/CF recipients, with dual missing-recipient warnings. New overspendEmailApi.ts; route + System-nav entry.

## Testing & verification

- Backend: 111 overspend tests green; ruff + pyright clean.

- Frontend: 7 screen tests green; tsc + eslint clean.

- Live send confirmed via ECS end-to-end (SES + prod-role ledger writes, to a test recipient).

- The alerts have been confirmed as fine with Ravi (stakeholder).

## Infra (applied out-of-band)

- Redshift mart_finance.overspend_alert_runs created (+ grant); DynamoDB table provisioned.

- EventBridge rule klair-overspend-alerts-weekly-prod created DISABLED (cron(0 13 ? * MON *)).

- ECR klair/scheduled-jobs image rebuilt (:latest, main-inclusive).

## Remaining after merge (go-live gates)

- Enable the EventBridge rule (single on/off switch).

- Swap the test recipient for real BU/CF leaders + Finance (admin screen / seed script).

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

## Why

Keval spotted two "AI Engineering" BUs in the API Keys explorer: AI Engineering & Builder (16 directory people) and a ghost Ai Engineering Builder holding a single TrueFoundry key (product-auto-im-prod, $0.36).

Root cause: the TF canonical-BU joins de-slug with space/dash replacement only. A directory name with punctuation can never match its gateway slug — AI Engineering & Builder ≠ ai-engineering-builder — so the join misses and the title-cased INITCAP fallback mints a ghost BU. Same bug hits Learnwith.AI (slug learnwith-ai, ~$1.1k July TF spend showing under ghost "Learnwith Ai").

## What

Fold both sides of both joins to lowercase alphanumerics (REGEXP_REPLACE(LOWER(x), '[^a-z0-9]', '')):

- ai_costs_service._get_tf_canonical_bu_join (feeds TF_EFFECTIVE_BU → the Anthropic + OpenAI gateway re-attribution by BU, budget rollups, time series). Also gains the GROUP BY/MIN anti-fan-out guard the mart join already had.

- ai_costs_mart_service._TF_BU_JOIN dbu fold (feeds the explorer stack rank / BU filters).

Verified zero fold collisions across all directory BU names. TU / SaaS Ops Support / Ephor etc. remain INITCAP fallbacks — those are genuinely absent from the ESW directory (rollups-admin territory, not this bug).

SQLite harness registers a REGEXP_REPLACE shim so the executable FR10 tie-out test keeps running the real production SQL.

## Verified live (read-only)

Post-fix _tf_openai_by_bu over Jun–Jul: ghost BUs gone; spend resolves to canonical AI Engineering & Builder and Learnwith.AI. 396 tests pass, ruff + pyright clean (one pre-existing pydantic false positive).

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

## Why

The stakeholder recipient list puts Andy Price (CFO) and Arthur (COO) in the GLOBAL Cc column. But compose() unions GLOBAL cc into every per-unit email, so as written they would have been Cc'd on the consolidated digest plus all ~20 per-BU/CF emails.

They should receive only the consolidated digest, and as a genuine Cc — they're C-level, so header position matters (moving them to to would have worked functionally but reads wrong).

## What

Two new GLOBAL-only fields, consolidated_cc and consolidated_bcc, read only by compose_consolidated().

GLOBAL row:
to                -> consolidated digest TO
cc / bcc          -> ALL emails (unchanged — still fan out to units)
consolidated_cc   -> consolidated digest CC only    <-- NEW
consolidated_bcc  -> consolidated digest BCC only   <-- NEW
notify            -> run-failure alerts (unchanged)

compose() is deliberately untouched — its pre-existing tests pass unmodified, which is the regression guard proving per-unit behavior didn't drift.

Purely additive. No migration: DynamoDB is schemaless, absent attributes default to [], so the already-seeded live GLOBAL row keeps working.

## UI/UX

"Global" meant three different things in one flat 6-field grid — TO is digest-only, CC/BCC really are every-email, Notify is neither. The header *"Global team (applied to every unit)"* was false for 4 of the 6 fields.

Regrouped so scope is structural rather than decoded per label:

| Group | Fields | Scope |

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

| Consolidated digest only | TO, CC, BCC | never on per-BU/CF emails |

| Every email | CC, BCC | digest + all per-BU/CF |

| Operational | Run-failure alerts | not an alert recipient |

Also fixed the dead gutter (subgroups were capped at maxWidth: 1100px while the table below runs full width) and extracted RecipientField from six near-identical 20-line blocks.

Storage names unchanged (to/cc/bcc/consolidated_cc/consolidated_bcc/notify) — labels only, so nothing is coupled to the live seeded row.

## Verification

Live end-to-end test — real fan-out with test addresses in the GLOBAL row (klairtest.one in consolidated_cc, klairtest.two in consolidated_bcc):

| Email | Cc | Bcc | Test addrs |

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

| Consolidated digest | klairtest.one | klairtest.two | ✅ both |

| IgniteTech (BU) | — | — | ✅ absent |

| SaaS (CF) | — | — | ✅ absent |

3 sent, 0 failed, all delivered. Confirmed three ways: resolution against the live DynamoDB row, the ledger's stored recipients JSON per email, and actual inbox delivery.

Layout measured in a real browser: at 1900px the digest's TO/CC/BCC share one row and the two small groups sit side by side, spanning the full 1852px section; at 800px it reflows with no horizontal overflow.

Tests: 139 backend (tests/overspend_alerts/ + router), 11 frontend spec (was 8). ruff check clean from repo-root CWD, tsc --noEmit and ESLint clean. Also removes a pre-existing React shorthand/longhand style warning.

## Rollout order (matters)

1. Merge this

2. Rebuild + push the klair/scheduled-jobs image and deploy the API

3. Then seed DynamoDB with Andy/Arthur in consolidated_cc (not cc)

4. Enable klair-overspend-alerts-weekly-prod

Seeding before step 2 would put them in a field nothing reads yet — they'd silently receive nothing.

## Screenshot

<img width="1885" height="369" alt="image" src="https://github.com/user-attachments/assets/772d925f-7977-499a-a217-e19903c75adf" />

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

## Problem

Two warehouse surfaces report the same GL account over the same window with materially different values. For account 62101 Depreciation/Amortization-Renovation/Furniture over SY25/26 (verified live 2026-07-27):

| Surface | SY25/26 | Notes |

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

| mart_education.agg_pl_transactions_by_account | $3,018,974 | ✅ full 12-month coverage, snapshot 2026-07-27 |

| core_education.fct_pl | $1,189,736 | ❌ understated ~2.5× |

fct_pl is understated for every school, drops Alpha Tampa's $546K entirely, and carries a phantom "Nova Bastrop" class. It reads staging_education.quickbooks_pl_data, whose source rows last changed 1 Jul — while its own snapshot_date reflects the rebuild clock, so it *looks* fresh.

Why this matters: fct_pl is the intuitive pick — it's in the core_ layer, it's named fct_pl, and its snapshot date looks current. An agent asked "what was depreciation?" reaches for it and hands the CFO a number understated ~2.5×, with no visible signal anything is wrong.

## Change

One rule appended to guidance.educationFinance.rules:

- Steers account-level Education P&L reads (depreciation, rent, any single GL account by school) to mart_education.agg_pl_transactions_by_account.

- Explicitly rules out core_education.fct_pl, with the reason.

- Records that staging_education.quickbooks_pl_monthly retains only a short rolling window (currently ~2 months) — it can confirm recent months but cannot reconcile a full school year.

> Note on that last point: an earlier draft of this guidance told agents to reconcile against quickbooks_pl_monthly. Re-verification on 2026-07-27 showed that feed returned $1,775,227 (May–Jun only) vs $2,983,413 four days earlier — it's a rolling window, so that instruction was dropped rather than shipped.

## Scope / risk

- Guidance-only. No table, pipeline, or query behaviour changes. Nothing is written to Redshift.

- No skill-version bump needed. SKILL_VERSION hashes skill/education-finance/ only (src/utils/skill-hash.ts); the ontology isn't an input. /meta is read fresh per session, so this reaches all agents on deploy with no client action.

- Does not repair fct_pl — it routes around it. That's SURTR-401 / SURTR-404; when those land, this rule should be re-pointed at the canonical P&L relation.

## Verification

Ran locally (OAuth mode, throwaway DATA_API_KEYS — the Data API doesn't mount in plain HTTP mode; /meta needs no DB):

- npm run typecheck — clean

- npx jest tests/unit/routes — 34 passed / 5 suites, incl. skill-version-sync (confirms no bump required)

- lint:check — 510 pre-existing problems; verified identical count with the change stashed, so none are from this PR

- A/B local vs prod /meta: prod = 6 rules (no fct_pl); local = 7 rules; delta is exactly this rule, rest of payload byte-identical (workflows 13/13, skill version 790afdb4 both)

## Post-deploy check

Deploy is automatic on merge (mcp-deploy.yml, path klair-misc/klair-mcp-ts/). After rollout, asking the Data API for SY25/26 depreciation by school should return ~$3.0M** (not $1.19M), name agg_pl_transactions_by_account, and state the annual-only caveat — postings are lumpy (Alpha Tampa books its full $546K in a single month), so a monthly run-rate isn't supported.

Covers CFO-50 Q21. Context: SURTR-380.

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

## Problem

Marketing attribution in core_education.fct_admissions_contact looks complete and is not. Among 1,623 enrolled records (verified live 2026-07-27):

| Field | Coverage | Reality |

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

| source_channel | 100.0% | ⚠️ 99.5% is the single value Offline |

| hs_analytics_source | 100.0% | ⚠️ mirrors it — 99.5% OFFLINE |

| normalized_lead_source | 0.6% | 99.4% blank |

| utm_source | 0.0% | empty |

Two fields on the same rows tell opposite stories. An agent checking source_channel sees full coverage, concludes attribution is complete, and ranks channels off a catch-all bucket. That's the dangerous read — and the more likely one, because 100% coverage looks authoritative.

For SY25/26 specifically, all 490 date-stamped enrollments are Offline — zero digital signal. Paid channels account for 8 enrolled students all-time (Paid Search 2, Direct 4, Organic 2) against ~$14M of paid media, so no paid CPL or CAC can be derived from these fields.

Separately, core_education.fct_admissions_funnel has converted_contacts and new_contacts summing to zero across all 29,933 rows and 10 channels, while contact_count does carry data (111,155). A zeroed measure is worse than a missing one — it reads as a measured *"this channel converted nobody."*

## Change

Two rules appended to guidance.educationOperations.rules:

1. Treat the attribution fields as unreliable; report the Offline share explicitly, don't rank channels by enrollment, and don't derive CPL/CAC from them.

2. Treat converted_contacts / new_contacts as missing, not as measured zeros.

## Scope / risk

- Guidance-only. No table, pipeline, or query behaviour changes.

- No workflow added → the toHaveLength workflow assertion is untouched, no test edit required.

- No skill-version bump — SKILL_VERSION hashes skill/education-finance/ only; /meta is read fresh per session, so this reaches all agents on deploy.

- Placed in educationOperations (admissions/enrollment populations) rather than educationFinance, matching where this data lives.

## What this does NOT fix

Attribution itself. That's a capture-side problem — UTM/source discipline at lead creation in HubSpot — and it is forward-only: SY25/26 and earlier attribution is unrecoverable regardless of any future fix. The question stays RED, correctly. Tracked under SURTR-379 / SURTR-427.

Worth noting for whoever picks that up: Offline at 99.5% is not necessarily *junk* — admissions genuinely runs tours, showcases and referrals. But an undifferentiated bucket is useless. fct_admissions_contact already carries tour_date, showcase_date, tour_status and showcase_status, so a first-pass offline breakdown may be derivable today without waiting on capture changes. Worth a spike.

## Verification

- npm run typecheck — clean

- npx jest tests/unit/routes — 34 passed / 5 suites (incl. skill-version-sync, confirming no bump needed)

- A/B local vs prod /meta: prod educationOperations = 6 rules; local = 8; delta is exactly these 2 rules, rest of payload byte-identical (educationFinance rules unchanged, workflow counts 13/2 both sides, skill version 790afdb4 both)

## Post-deploy check

Asking "which channels drive enrollments?" should return an explicit "attribution is not reliable" answer stating the Offline share — not a channel ranking — and should not cite source_channel's 100% population as evidence of coverage.

Covers CFO-50 Q32.

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

<!-- CURSOR_AGENT_PR_BODY_BEGIN -->

## Summary

When an implementer opens its PR via a path the Cursor SDK does not observe (e.g. gh pr create), the harness now recovers the pr_opened artifact through a GitHub-backed ladder before declaring failure — and when it cannot uniquely resolve the PR, it parks an explicit pr-artifact-lost (needs human) outcome with a non-zero exit instead of exit=0 after silently skipping reviewer / addresser / Mercy / mark-ready.

## Why It's Needed

Observed live on AI-159 / PR #95: the implementer opened the PR and named the URL in its final message, but SDK metadata had no prUrl. Recovery nudged, re-read the same empty field, printed still no PR URL while quoting the live URL twice, then exited 0. The entire review gate was skipped; the only tell was wall-clock. Unattended dispatch cannot trust a success signal that also means "review never ran."

## Changes

- Add AI-180 recovery ladder in src/artifact-recovery.ts (runs after the existing nudge, before unrecoverable):

1. Tier 0 — re-read SDK target metadata (Agent.getRun); log + thread errors on failure

2. Tier 1 — unique open PR whose head branch ends with -<last4(agentId)>, corroborated by run window (createdAt required)

3. Tier 2 — extract PR URL from resultSummary, verify open + branch suffix on GitHub (missing vs error discriminated)

4. Tier 3 — park pr-artifact-lost (never guess under ambiguity); emit terminal run_failed (PR_ARTIFACT_LOST) for enrich

- Wire non-zero exit via exitCodeForImplementerRun in runner.ts

- Stamp parkOutcome on run receipts + dispatch fired[] entries

- Linear honesty: renderLinearCommentBody renders parked runs as Drone run parked (pr-artifact-lost) — not the success-shaped Drone run completed body

- Review follow-ups (this address pass):

- Ladder recovery after nudge throw/error restores status: "finished" on the receipt and is covered by tests

- Tier-2 missing classification uses anchored gh error prefixes (isGhPrMissingError) so "HTTP 502 not found in cache" stays error

- Park diagnostics trim multi-line gh stderr; malformed gh pr list rows WARN; text URL extract tolerates markdown punctuation; Tier-1/2 ambiguous candidates merge

### Contract-surface

| Symbol | Change |

| --- | --- |

| PR_ARTIFACT_LOST / PrArtifactParkOutcome | new exported park token |

| PR_ARTIFACT_OPEN_PR_LIST_LIMIT | named Tier-1 gh pr list ceiling |

| resolveLostPrOpenedArtifact(...) | new ladder entrypoint |

| FetchPullResult / fetchPullForRepo | discriminated ok / missing / error |

| isGhPrMissingError / shortExecErrorLine | Tier-2 missing classifier + park-detail trim |

| listOpenPullsForRepo | read-only GitHub helper (injectable) |

| exitCodeForImplementerRun(...) | exit-code mapper (pr-artifact-lost → 1) |

| ImplementerRecoveryInput | +fallbackRepoUrl, +injectable ladder seams |

| ImplementerRecoveryResult | +optional parkOutcome |

| DroneRunRecord.parkOutcome | optional "pr-artifact-lost" (no schema bump) |

| DispatchFiredEntry.parkOutcome | PrArtifactParkOutcome on receipt |

| renderLinearCommentBody | parked receipts render park headline/outcome |

## Breaking Changes

None for happy-path callers. Degraded runs that previously exited 0 with a missing prUrl now exit 1 with parkOutcome: "pr-artifact-lost" and a terminal run_failed event — intentional honesty. Linear comments for those parks no longer say "completed".

## Test Plan

- [x] pnpm typecheck — clean (tsc --noEmit)

- [x] pnpm exec vitest run src/artifact-recovery.test.ts — green (31 tests)

- [x] Branch-correlated recovery with injected GitHub stub (single open PR resolves)

- [x] Two correlator candidates → ambiguous park (does not guess)

- [x] Observed-case regression: recovery text with PR URL resolves via Tier 2 (never "still no PR URL")

- [x] Tier-2 rejects wrong-suffix OPEN and CLOSED/MERGED text URLs

- [x] Unresolved → non-zero exit + pr-artifact-lost + terminal run_failed

- [x] Ladder recovers when sendNudge throws / waitRun returns error, keeps status: "finished"

- [x] isGhPrMissingError("HTTP 502 not found in cache") is false

- [x] Linear parked body does not contain Drone run completed / finished (no PR produced)

- [x] Tier order asserted: SDK → branch → verified text

## Verification Artifact

$ pnpm typecheck
> tsc --noEmit
(exit 0)
$ pnpm exec vitest run src/artifact-recovery.test.ts
✓ src/artifact-recovery.test.ts (31 tests)

<!-- CURSOR_AGENT_PR_BODY_END -->

<div><a href="https://cursor.com/agents/bc-4495bc96-9706-4976-9aa8-abb971947874"><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-4495bc96-9706-4976-9aa8-abb971947874"><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>

## Summary

- add an isolated ECS/Fargate netsuite-raw runner for direct SuiteTalk REST SuiteQL ingestion

- define a 66-table manifest: 38 incremental targets and 28 full-refresh targets

- immutably land exact source pages, partition receipts, and extraction manifests in S3 before publication

- make partition extraction resumable by stable run ID; completed receipts are validated and reused after interruption

- use bounded partition concurrency of three workers per table, with independent NetSuite and S3 clients

- validate payload shape, configured fields, pagination, keys, counts, empty extracts, and material volume shortfalls

- publish through private Redshift candidate tables and atomic target/ingestion_ledger transactions

- support daily incremental processing, Sunday reconciliation, manual table subsets, source-boundary backfills, and local dry runs

- use readable snake_case target columns such as last_modified_date

## Scope decisions

- targets finance_dw.staging_finance_netsuite

- leaves the existing NetSuite pipelines and staging_netsuite objects unchanged

- includes 66 source tables: 38 incremental and 28 full refresh

- keeps Redshift publication serialized per table while parallelizing source partition extraction

- does not add the tentative transaction-application link table because the required bounded Next-vs-Previous comparison has not been recorded

## Live end-to-end verification

Validated locally against NetSuite, S3, and Redshift using the same runner code in this PR.

- Redshift ingestion_ledger contains successful published outcomes for all 66 manifest tables

- target schema/table DDL and table comments were applied and checked in staging_finance_netsuite

- dry-run validation confirmed extraction without Redshift publication

- actual runs independently checked target row counts, source_run_id, ledger outcome, and published_count

- resumability was demonstrated by interrupting a large extraction, preserving receipts, and reusing completed partitions with the same run ID

- the read-only SuiteQL concurrency benchmark supported three partition workers, which is now the local and pipeline default

### Large-table results

| Table | Validation | Rows |

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

| raw_journal_entry | full extraction; 27/27 partition receipts; Redshift and ledger verified | 1,458,859 |

| raw_charge | full extraction with three workers; Redshift and ledger verified | 3,334,016 |

| raw_price_plan | full extraction with three workers; Redshift and ledger verified | 4,330,779 |

| raw_revenue_plan | full extraction; 98/98 receipts; 52 reused after credential interruption; Redshift and ledger verified | 6,816,702 |

| raw_subscription_line | legacy bootstrap plus keyed incremental catch-up; 210 changed rows verified | 3,778,902 total |

| raw_transaction | legacy bootstrap plus keyed incremental catch-up; 9,314 changed rows verified | 5,812,954 total |

| raw_transaction_line | legacy bootstrap plus keyed incremental catch-up; 25,269 changed rows verified | 136,135,641 total |

| raw_transaction_accounting_line | legacy bootstrap plus keyed incremental catch-up; 25,240 changed rows verified | 136,112,710 total |

The final four-table catch-up used run ID remaining-incremental-catchup-20260720. Each run-specific target count exactly matched its ledger fetched_count and published_count, and each outcome was published.

<details>

<summary>38 incremental tables verified</summary>

- raw_account

- raw_accounting_period

- raw_billing_account

- raw_charge

- raw_classification

- raw_contact

- raw_currency

- raw_customer

- raw_customer_deposit

- raw_customer_payment

- raw_customer_subsidiary_relationship

- raw_department

- raw_employee

- raw_entity

- raw_inventory_number

- raw_item

- raw_item_location_configuration

- raw_journal_entry

- raw_location

- raw_payment_method

- raw_price_book

- raw_revenue_plan

- raw_revenue_recognition_rule

- raw_subscription

- raw_subscription_change_order

- raw_subscription_line

- raw_subscription_plan

- raw_subsidiary

- raw_systemnote_customer_name_changes

- raw_term

- raw_transaction

- raw_transaction_accounting_line

- raw_transaction_line

- raw_units_type

- raw_vendor

- raw_vendor_bill

- raw_vendor_payment

- raw_vendor_subsidiary_relationship

</details>

<details>

<summary>28 full-refresh tables verified</summary>

- raw_account_type

- raw_amortization_schedule

- raw_amortization_template

- raw_approval_status

- raw_billing_schedule

- raw_billing_schedule_type

- raw_charge_billing_mode_type

- raw_charge_type

- raw_consolidated_exchange_rate

- raw_consolidated_rate_type

- raw_country

- raw_custom_list_channel_sales_tier

- raw_custom_list_customer_purchase_order_status

- raw_custom_list_quality_control_review_value

- raw_custom_list_zuora_yes_no

- raw_frequency_type

- raw_item_type

- raw_nexus

- raw_note

- raw_partner_system

- raw_price_model_type

- raw_price_plan

- raw_subscription_change_order_status

- raw_subscription_status

- raw_subscription_term

- raw_subscription_term_unit

- raw_transaction_name

- raw_units_type_unit_of_measure

</details>

## Legacy-compatible auxiliary objects

The reviewed Redshift DDL and one-time bootstrap now cover the four non-SuiteQL auxiliary objects, while the fifth object remains a normal manifest ingestion:

| Destination | Ownership | Verified rows |

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

| staging_finance_netsuite.fct_accounting_book_subsidiaries | manual static legacy mapping; the service token cannot query the source record | 490 |

| staging_finance_netsuite_metadata.raw_data_dictionary | curated metadata, bootstrapped from legacy | 2,034 |

| staging_finance_netsuite_metadata.raw_job_runs | legacy history plus future atomic Surtr publication records | 4,158 legacy rows |

| staging_finance_netsuite.table_join_relationship | curated join metadata, bootstrapped from legacy | 818 |

| staging_finance_netsuite.raw_systemnote_customer_name_changes | direct incremental SuiteQL ingestion | 70,283 |

All five destinations were checked bidirectionally against their corresponding legacy tables: source-minus-target and target-minus-source were both zero. The bootstrap SQL is versioned at ddl/bootstrap_legacy_auxiliary_tables.sql. Curated/manual objects remain outside the 66-table SuiteQL manifest.

## Review feedback addressed

- replace the identifier-only fallback with explicit legacy-compatible source-field contracts for all 66 tables (951 configured fields)

- fail before source extraction when a target is missing any required contracted column

- enable the daily production schedule by explicit owner direction; follow-up reconciliation and legacy cleanup planning are tracked in [SURTR-340](https://linear.app/builder-team/issue/SURTR-340/reconcile-netsuite-raw-tables-and-plan-legacy-staging-cleanup)

- bound manifest key/watermark widths and generate DISTKEY/SORTKEY migration SQL for all 38 incremental targets

- extend Redshift polling to a configurable five-hour ceiling, request cancellation on timeout, and avoid candidate cleanup until server termination is known

- make publications retry-idempotent by run/table/manifest and isolate candidate names by run

- require exact preflight/extracted/postflight reconciliation for offset-paginated sources

- remove TRUNCATECOLUMNS so oversized values fail closed

- attempt every requested table, retain structured partial failures, exit ECS nonzero, and write best-effort failed warehouse history

- regenerate all 66 comment statements from the canonical source and merge legacy job history without deleting Surtr rows

- validate strict parameters, manifest keys/watermarks, watermark overlap behavior, collision handling, duplicate keys, candidate counts, and timeout states

## Automated validation

- uv run --project pipelines/runners/netsuite-raw pytest pipelines/runners/netsuite-raw/tests -q — 122 passed

- all 66 expanded projections passed live zero-row SuiteQL parsing

- scoped Ruff checks passed for the modified Python files

- python3 -m json.tool pipelines/runners/netsuite-raw/pipeline.json

- git diff --check

## Operational safeguards

The previously reported merge-time and timeout risks are now addressed in code and deployment state:

- schedule.enabled is true; the EventBridge rule runs daily at cron(0 5 * * ? *) after deployment to production

- REDSHIFT_POLL_TIMEOUT_SECONDS is 18,000 seconds inside the six-hour ECS budget

- the runner can cancel timed-out Data API statements and preserves candidates when server termination is ambiguous

- ddl/apply_incremental_physical_design.sql applies bounded key/watermark types plus merge-aligned distribution and sort keys to all incremental targets

- source publications and warehouse histories are idempotent for the same run/table/manifest

- concurrent runs use isolated candidate table names

- non-dry runs verify every contracted destination column before making a source request

The generated physical-design migration and source-field reconciliation must be applied and verified as part of [SURTR-340](https://linear.app/builder-team/issue/SURTR-340/reconcile-netsuite-raw-tables-and-plan-legacy-staging-cleanup). The fail-closed target-column preflight prevents an unmigrated table from silently discarding contracted fields.

## Impact

This PR introduces the standalone runner and canonical DDL source with its daily production schedule enabled by explicit owner direction. The validation Redshift schema/tables have been created and populated manually. [SURTR-340](https://linear.app/builder-team/issue/SURTR-340/reconcile-netsuite-raw-tables-and-plan-legacy-staging-cleanup) owns the complete legacy-vs-new reconciliation, discrepancy remediation, dependency audit, and table-by-table cleanup plan; no legacy table is removed by this PR.

Linear: [SURTR-303](https://linear.app/builder-team/issue/SURTR-303/plan-standalone-netsuite-raw-ingestion-pipeline-in-surtr)

## Summary

The first Alpha recovery replay (hubspot-raw-alpha-replay-20260724T061401Z) failed fail-closed on its first resource:

ValueError: replay row count mismatch for custom_object_schemas: expected 12, rebuilt 23

Root cause: the live collection dedups objects returned by both the active and archived listings (active wins, retained exactly once — collector.py's logical_key_archive_states map) and records the post-dedup row_count in the resource manifest, while the immutable pages retain every raw record. For custom_object_schemas that's 11 active + 12 archived with one overlapping objectTypeId → manifest says 12. The replay path rebuilt every raw page record with no dedup → 23 → hard mismatch. This affects any resource collected in two archive states with at least one overlapping object; it was never exercised because this is the first production replay of a two-archive-state resource.

Fix: replay now applies the identical logical-key archive-state dedup while streaming pages (active anchor retained exactly once; a duplicate within one archive state stays a hard error), reproducing the exact candidate the live run published.

Blast radius of the failed replay: zero. It failed during candidate rebuild, before any Redshift call — verified: no is_replay ledger rows, raw_emails still 0, run record FAILED (eb4e89ff). The pinned manifest coordinate is unchanged and the replay is simply re-runnable after this deploys.

## Testing

- New regression test rebuilds the production shape (two archive-state pages, overlapping logical key) end-to-end: live collect → 22 rows, replay → 22 rows, overlap keeps only its active-listing row.

- Full runner suite: 177 passed. Ruff 0.15.22 check+format clean.

## After merge

Promote through the standard release path, redeploy, and re-run the Alpha replay with the same pinned manifest coordinates.

🐦‍⬛ Generated by a very good bot

## Problem

collections-tracker-sync-v2 failed twice in prod today (08:35 and 08:56 UTC) with:

StructuralError: Tracker 'GFI': missing required header(s) ['invoice_balance', 'payment_status']
in the left block (found: ['Customer Name', 'Class', 'Invoice Number', 'Actual Due Date',
'Expected Collection Date', 'Old Expected Date'])

The Sheets API intermittently returns the Invoice Balance header cell as '' on an

otherwise-successful HTTP 200 read. _left_block_width() finds the left/right block boundary by

scanning for the first blank header cell, so the block truncates at that index and every header past

it is reported missing.

Reproduced live 2026-07-27 09:02:20Z by polling all 8 tabs every ~8s:

COLLAPSE 09:02:20 tab=Khoros width=5     (expected 10)

Rare — ~1 blank in ~150 reads, which matches 2 failures in 86 runs.

## It's the column, not an index

| Tab | Blank index | What lives there |

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

| GFI | 6 | Invoice Balance |

| JigTree | 6 | Invoice Balance |

| Khoros | 5 | Invoice Balance — no Class column, so it shifts left |

So this must not be fixed by special-casing a tab or an index. The "different tab each run" pattern

is just the handler aborting at the first bad tab while walking TRACKER_TABS in order.

## Why the existing retry didn't help

read_with_retry only retries APIError on 429/5xx. This read returns HTTP 200 — no exception

is raised, and the corruption is only visible *after* parsing. 30 days of logs contain zero

retryable warnings. The Step Function likewise retries only infra faults

(Lambda.ServiceException, …), so an in-handler StructuralError went straight to

UpdateRunFailed with no retries at all.

## Fix

read_parse_with_retry() — re-READ the tab and re-parse on StructuralError

(3 attempts, 2s/4s backoff). Retrying the read is the point: re-parsing the same bad grid can only

fail again.

The header validation is deliberately left as-is. Loosening it would turn a page into silent bad

data on a genuine column rename.

## Verification

- 4 new tests (TDD — written failing first), incl. a Khoros index-5 case that guards against an

index-6 special-case fix, and a genuine-rename case asserting it still raises.

- Replayed the real captured Khoros grid with the exact observed transient → logs the warning,

then recovers to the correct 296 rows.

- 174 tests pass across all 5 touched runners.

- ruff check + ruff format --check clean over pipelines.

- Worst case (all 8 tabs exhausting retries) ≈ 59s vs the 300s timeout. Happy path unchanged:

one read per tab.

## Scope

- Both tracker runners (parsers.py is byte-identical; the non-v2 one is schedule-disabled but kept

in sync).

- google_client.py is vendored verbatim across the collections runners, so the helper is synced to

all five copies that were already identical.

- CollectIQ is unaffected — it uses parse_collectiq, which has no left-block logic.

## Note

This shrinks the failure window rather than closing it: a rare double-blip could still fail. For a

2-hourly idempotent job that's the right trade — retry-until-success would mask real drift.

No data was ever at risk: both failures hit fetch_and_parse (handler.py:35) before

RedshiftLoader is constructed (line 37), so no DELETE ran.

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

## Demo

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/c32cdbbb-ccca-4a58-8e60-25861d03273c" />

<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/2d4bced0-437f-42b6-8594-790ce73f9761" />

## Summary

- add an independent Snowball Variances period selector that defaults to MTD while Maintenance Summary defaults to TTM

- show the active period on all four Snowball Variances cards and keep graphical charts on current/prior MTD

- pass MTD/QTD/YTD/TTM through AppSync to the Redshift query with input validation

- expose downsellsubcategory and add TTM Downsell tabs for All plus the six live categories, including counts and filtered totals

- document the page API calls and Redshift lineage

## Why

The Snowball Variances tables were hardcoded to MTD and could not independently select other reporting periods. The existing resolver also omitted the Redshift Downsell subcategory field, preventing TTM category filtering.

Linear: [KLAIR-3016](https://linear.app/builder-team/issue/KLAIR-3016/share-maintenance-summary-period-picker-with-snowball-variances)

## Deployment

Deploy klair-udm before the frontend because this PR changes the AppSync schema and Redshift Lambda response.

## Validation

- pnpm build

- pnpm tsc --noEmit

- ESLint on modified frontend files

- focused Vitest suites for page period state, fetching, period controls, and Downsell category filters

- npm run test:redshift — 90 tests passed

- Node syntax checks for modified Redshift modules

## What

Three references to staging_budgets_gsheets.netsuite_account_mapping repointed to staging_gsheets.netsuite_account_mapping:

- data-api-ontology.ts — the Education marketing-spend workflow (served via /api/v1/meta)

- query-budget-vs-actuals.ts + definitions.ts — the query_budget_vs_actuals tool allowlist

- canonical-table-cutover.test.ts — expectations flipped to the new canonical name

Plus one new MFR-workflow ontology note: cross-budget-version comparisons (forecast accuracy, version-over-version variance) must filter by the Education BU allowlist derived from Education-tagged rows, because entity_type='Education' exists only on the newest budget version and Actuals — older versions carry the legacy BU/CF/Other/3P taxonomy. Includes the reclassification caveat. This is the recipe that unblocked CFO50 Q27 (SURTR-372).

## Why

staging_budgets_gsheets no longer exists in the warehouse — verified live via the Data API (2026-07-23 and 07-25): /api/v1/schemas does not list the schema, and netsuite_account_mapping is served from staging_gsheets. The Tables v3 rehoming was later reversed by the schema-cleanup waves, but these code references were never updated, so agents following the marketing-spend workflow or using the budget-vs-actuals tool hit a nonexistent table. Found during the CFO50 audit (ontology table-reference sweep: 33 of 34 refs live, this was the one failure).

## Verification

- npx jest tests/unit/tools/canonical-table-cutover.test.ts tests/unit/utils/table-source-comments-ddl.test.ts — 26/26 pass

- npm run typecheck — clean

- prettier --check on all changed files — clean

- Skill pack untouched → no skill-version bump needed (thin-skill pattern; ontology serves live from /meta)

## Deploy note

Takes effect on the next MCP deploy — can ride along with the per-person API-key deploy Benji has planned.

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