## Summary
This branch modernizes the Education aggregate marts to comply with
WAREHOUSE_CONVENTIONS.md and PIPELINE_CONVENTIONS.md, and adds two new
disabled pipelines. The core theme is safe atomic publication: every
in-scope writer now builds a target-shaped candidate, validates it, and
publishes atomically — no TRUNCATE, fail-closed on error, last known-good data
left intact. It also rebuilds the two P&L transaction aggregates from
authoritative atomic QuickBooks staging (staging_education_quickbooks) instead
of the legacy Reports-API-derived staging_education.quickbooks_pl_monthly,
while preserving their table names, schemas, grains, and consumer contracts.
This is a writer-modernization PR. No consumer (Aerie, Klair, Core) is
repointed, and the four core_education-dependent school marts and their runner
are untouched. Cloud/warehouse mutation (applying DDL, replacing procedures,
refreshing production) is not part of this PR and requires separate explicit
approval.
### Changes
quickbooks-raw-sync — CDC correctness
- src/qb_client.py — Resolves the one proven Intuit CDC duplicate shape
(an inactive list record plus a deleted marker for the same source Id) by
retaining the inactive record for the whitelisted list entities; every other
duplicate pattern fails closed as ambiguous. Adds a CDC_MAX_RECORDS cap so a
potentially truncated oversized CDC response raises instead of silently
publishing partial data.
- tests/test_qb_client.py — Covers the inactive+deleted resolution,
ambiguous-duplicate rejection, and the oversized-response guard.
hc-forecast-refresh — atomic publication *(refactor)*
- ddl/sp_refresh_agg_hc_by_teamroom.sql — Rewrites the writer to build a
candidate, validate it (grain uniqueness, non-null dimensions, HC cap,
statement-line vocabulary, source reconciliation), and publish atomically;
removes TRUNCATE. Preserves the seven-part grain and metric logic.
- ddl/agg_hc_by_teamroom.sql — Non-destructive DDL with Purpose/Grain/Key
COMMENT ON TABLE metadata; drops DROP ... CASCADE.
- ddl/migrations/2026-07-21_hc_mart_atomic_publication.sql *(new)* —
Non-destructive live migration.
- tests/test_sql_contracts.py *(new)*, README.md *(new)* — SQL
contract tests and pipeline docs.
mart-education-mfr-line-items-refresh — consolidated atomic publication *(refactor)*
- ddl/sp_refresh_agg_mfr_line_items.sql *(new)* — Single procedure that
builds and validates both the summary and vendor candidates, then publishes
the sibling pair in one atomic transaction so they can never represent
different runs. Replaces the two separate sp_refresh_agg_mfr_line_items_*.sql
procedures *(deleted)*. Preserves the SURTR-41 Education filter, the
data_source = 'Budget' restriction, vendor case-insensitive grouping, and the
'-' fallback.
- ddl/agg_mfr_line_items_{summary,by_vendor}.sql — Non-destructive DDL with
Redshift table metadata.
- ddl/migrations/2026-07-21_mfr_atomic_publication.sql *(new)*,
tests/test_sql_contracts.py *(new)*, README.md *(new)*.
- src/handler.py, tests/test_handler.py — Handler invokes the single
consolidated procedure; post-run checks kept as observability only.
mart-education-quickbooks-refresh — P&L transaction aggregates *(feat)*
- ddl/agg_pl_transactions_by_account.sql, agg_pl_transactions_by_vendor.sql
*(new)* — Canonical source-controlled DDL for the two previously live-only
tables (their only prior writer was a deployed core_education procedure).
- ddl/agg_quickbooks_profit_and_loss_by_{month,quarter}.sql *(new)* — P&L
compatibility marts built from the atomic ledger.
- ddl/sp_refresh_quickbooks_profit_and_loss_shadow.sql *(new)* — Shadow
refresh procedure that rebuilds P&L from staging_education_quickbooks,
handling both AccountBasedExpenseLineDetail and ItemBasedExpenseLineDetail
line shapes, PostingType-based journal-entry sign derivation, and the atomic
Account vocabulary (Expense, Other Expense, Cost of Goods Sold) that the
legacy Reports-API filter missed.
- ddl/sp_refresh_quickbooks_financial_marts.sql — Extends the atomic
thirteen-table publication to include the P&L aggregates with vendor
losslessness and exact candidate-to-target validation.
- src/pnl_id.py *(new)*, tests/test_pnl_id.py *(new)* — Deterministic
P&L row identity derivation.
- ddl/migrations/2026-07-21_adopt_pl_transaction_aggregates.sql,
2026-07-21_remove_as_of_date_procedure.sql *(new)* — Ordered migrations.
- scripts/reconcile_legacy.py, tests/test_reconcile_legacy.py,
RECONCILIATION.md — Read-only legacy-vs-new differential extended to the
P&L aggregates.
- README.md, src/handler.py, tests/* — Docs and handler updates.
qb-aerie-pl-reconciliation — retire deployable runner *(chore)*
- pipeline.json *(deleted)*, README.md — Converts the directory to a
local-only read-only CLI; the obsolete deployed stack must not be invoked.
Removing that stack is a separate cloud mutation requiring approval.
aerie-expense-report-definitions-sync *(new pipeline)*
- Convex-backed snapshot of Aerie expense-report definitions into
mart_education.aerie_expense_report_definitions. Extraction, transforms,
Redshift loader with atomic landing, and full test suite.
quickbooks-expense-ai-generation *(new pipeline)*
- Provenance-first Anthropic pipeline producing
mart_education.quickbooks_vendor_classifications and
quickbooks_cost_opportunities, with completion-state gating, evidence
capture, publication, and a classification-baseline importer.
pipelines/owners.json — Registers owners for the two new pipelines.
### Design Decisions
- Atomic candidate-then-publish everywhere. Every in-scope writer validates a
target-shaped candidate before touching the live table and publishes
atomically; no path uses TRUNCATE (Redshift commits it implicitly and it
cannot be rolled back). Correctness gates run inside the writer, not as
post-publication monitoring.
- P&L from atomic source, not Reports API. The rebuilt aggregates read
staging_education_quickbooks and use the QuickBooks Account object for
account name/type. This surfaces atomic Other Expense accounts the legacy
report filter dropped, so the journal-entry P&L filter uses all three atomic
account types and is reconciled explicitly.
- Contract preservation over convenience. Table names, schemas, grains, and
columns are unchanged for all five existing marts; agg_ prefix retained. New
P&L work keeps the current transaction scope, sign rules, and Deposit omission.
- New AI/Convex pipelines ship disabled. They add contracts without
repointing any consumer or retiring any legacy pipeline.
## Test Plan
- [x] quickbooks-raw-sync unit tests pass (CDC dedup, ambiguous rejection,
oversized-response guard)
- [x] hc-forecast-refresh handler + SQL contract tests pass
- [x] mart-education-mfr-line-items-refresh handler + SQL contract tests pass
- [x] mart-education-quickbooks-refresh handler, pnl_id, reconciliation, and
SQL contract tests pass
- [x] New pipeline test suites (aerie-expense-report-definitions-sync,
quickbooks-expense-ai-generation) pass
- [x] Ruff lint + format clean
- [ ] Reviewer: confirm no in-scope mart changes its name/schema/grain and no
consumer (Aerie/Klair/Core) is repointed
- [ ] Reviewer: confirm no production DDL/procedure/refresh is applied by this PR
(cutover is a separate approved cloud mutation)