## Demo
<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/12b1c582-c96e-4426-bb38-89322d259507" />
## Summary
Adds a new Education P&L page to the Financials dashboard — a tree-style income statement (budget vs actual, monthly + quarterly columns) covering all Education-entity activity except Physical Private Schools and Core Education. Modeled after Klair's Performance Review > Income Statement and built on Aerie's Convex + Redshift sync architecture, so consolidated numbers reconcile against Klair's Performance Review for the matching BU/class scope.
Sits alongside the existing per-school P&L surface (relabeled "School P&L" in the same PR). The new page is consolidated (no per-school selector), pulled from core_budgets.consolidated_budgets_and_actuals via a new daily sync, and rendered as a fixed 18-row income statement with drilldown by business_unit → class.
## Linear tickets
- [AERIE-341 — Education P&L page in Financials dashboard](https://linear.app/builder-team/issue/AERIE-341) (parent)
- [AERIE-343 — Education P&L polish: M1+M2 subtotal column + Financials sub-tab URL routing](https://linear.app/builder-team/issue/AERIE-343)
- [AERIE-344 — Financial sync hardening: pl-transactions NULL filter + mfr-line-items OCC race fix](https://linear.app/builder-team/issue/AERIE-344)
## Specs delivered
| Spec | Status | Description |
|------|--------|-------------|
| [01-sync-and-schema](features/dashboards/education-pl-page/specs/01-sync-and-schema/spec.md) | Completed | New mfrLineItems Convex table + 3 indexes; new Redshift reader with entity_type='Education' filter and Zod validation; daily refresh module wired into the financial worker. |
| [02-convex-query](features/dashboards/education-pl-page/specs/02-convex-query/spec.md) | Completed | chat/convex/dashboards/educationPL.ts — getIncomeStatement({year, quarter}) returning TreeNode[] with server-side derived totals; BU exclusion at query time; Provision for Bad Debt derived row; variance % zero-budget handling; getLastSyncedAt helper. |
| [03-ui-page-and-relabel](features/dashboards/education-pl-page/specs/03-ui-page-and-relabel/spec.md) | Completed | New Education P&L page (education-pl-table.tsx + education-pl-period-picker.tsx); School P&L relabel; default-quarter walk-back; CSV export. |
## What landed in each layer
### Sync (sync/)
- sync/src/redshift/mfr-line-items.ts — Zod-validated read against core_budgets.consolidated_budgets_and_actuals with entity_type='Education' filter (~12.3M → ~285K rows).
- sync/src/analytics/mfr-line-items-refresh.ts — Batched Convex upsert keyed on the natural composite, daily cadence.
- sync/src/financial-worker/index.ts — Registered the new task on the daily financial-worker cycle.
### Convex (chat/convex/)
- chat/convex/financialSchema.ts — New mfrLineItems table + 3 indexes (by_reportingPeriod, by_period_dataSource, by_natural_key).
- chat/convex/dashboards/educationPL.ts — getIncomeStatement + getLastSyncedAt queries (both canViewFinancials-gated) plus the upsertMfrLineItems mutation. Server-side derivation of all 8 computed rows (Total Revenue / Total COGS / Gross Profit / Gross Margin % / Total Expenses / Net Profit / Net Margin %), divide-by-zero protection, Provision for Bad Debt synthesized from isProvisionForBadDebt=true rows.
### UI (chat/components/dashboards/financials/)
- education-pl-page.tsx, education-pl-table.tsx, education-pl-period-picker.tsx, education-pl-helpers.ts — Fixed 18-row income statement, monthly column groups + Quarter Total (Actual / Budget / Var$ / Var%), collapse-by-default tree (line item → BU → class), CSV export, dedicated period picker with default-quarter walk-back.
- financials-view.tsx — Reads the active sub-tab from useDashboardTabs (URL-routed) instead of owning local state.
## Follow-up work added to this branch
After the initial three specs landed, four commits were added to address polish items and reliability fixes discovered while running the dashboard in the dev environment. Filed retroactively as AERIE-343 and AERIE-344 for tracking — both included in this PR.
### AERIE-343 — UI polish (2 commits)
- 5497d3c3 feat(dashboards): add M1+M2 subtotal column to Education P&L — New two-month aggregation column between M3 and Quarter Total. Header reads e.g. Jan + Feb 2026 (derived from quarter context, self-describing in CSV exports / screenshots). monthsThruTwoActual / monthsThruTwoBudget carried on the server TreeNode because margin rows need safePct(num.m1+m2, den.m1+m2) from the underlying numerator / denominator pair — a client-side mean of two percentages would have been wrong.
- 5d1ae524 refactor(dashboards): move Financials sub-tab into URL-routed state — Promotes the Financials → School P&L / Education P&L toggle from local useState into URL-routed state (?sub=school|education) via useDashboardTabs. Financials becomes an expandable parent in the sidebar dashboards context panel, matching the Admissions / Edu Joe nav pattern. Deep-linking works; refresh preserves the active sub-tab.
### AERIE-344 — Sync hardening (2 commits)
- 4177f040 fix(sync): remove mfr-line-items boot backfill to avoid OCC race — The boot backfill and the scheduler tick were both writing to the same Convex mfrLineItems rows on worker startup. The resulting OCC contention near-stalled the upsert phase in dev. Boot backfill removed (scheduler tick fires immediately on boot, so end state is identical). Adds breadcrumbs (redshift fetch start/end/fail, upsert progress every 50/N batches) so hung loops surface in dev.log instead of looking like silence.
- 78a135fa fix(sync): exclude NULL class_name rows from pl-transactions query — Adds AND <alias>.class_name IS NOT NULL to all four UNION branches (qb_pl, qb_bills, qb_journal_entries, qb_vendor_credits). Rows without class_name carry no school identity and could never surface in the drill-down, but were tripping the non-nullable Zod schema downstream — a silent-failure path emitting parse warnings without anyone noticing.
## Tests
75 + 8 tests across the feature, all passing:
| Layer | Tests | File(s) |
|-------|-------|---------|
| Sync | 19 | mfr-line-items.test.ts (11), mfr-line-items-refresh.test.ts (8) |
| Convex queries | 30 | educationPL.test.ts — upsert mutation, tree-builder helpers, 18-row contract, divide-by-zero, BU exclusion, Provision for Bad Debt handling, M1+M2 derived-field invariant |
| UI helpers | 26 | education-pl-helpers.test.ts — variance, percent calculations, default-quarter walk, CSV export (5-group with M1+M2), color tone |
| Sidebar nav (AERIE-343) | 8 | dashboards-context-panel.test.tsx — Financials expandable parent, sub-tab nav |
## Pre-existing finding noted
The public upsertMfrLineItems mutation has no auth gate restricting it to a sync service principal. This follows the existing codebase pattern (same as upsertPlTransactions, upsertXoContractorIdentity, and other sync-callable mutations) and is out of scope for this PR — it would need a coordinated change across all sync-callable mutations. Documented here for awareness.
## CI
All 7 checks passing on this PR.
## Test plan
- [ ] Visual sanity check on the Education P&L page — header, period picker, last-synced timestamp, 18 rows in canonical order, expense rows render with invertColors.
- [ ] Drilldown behavior — top-level row expands to business_unit; BU expands to class; collapse restores the previous state.
- [ ] CSV export emits the flat income statement (5 column groups: M1 / M2 / M3 / M1+M2 / Quarter Total) for the selected quarter.
- [ ] Sidebar nav: Financials is an expandable parent — ?sub=school and ?sub=education round-trip through URL on refresh / deep-link; switching tabs preserves the active sub-tab.
- [ ] M1+M2 column appears between M3 and Quarter Total with a header like Jan + Feb 2026; margin / percent rows in that column show the correct combined-base percentage (not a client-side mean of two percentages).
- [ ] Reconciliation: pick a sample quarter, compare Aerie Education P&L totals against Klair's Performance Review > Income Statement for the same BU/class scope.
- [ ] Run the sync against a real Redshift connection — verify mfrLineItems populates, that the natural-key upsert is idempotent on re-run, and that the refresh module fires on the daily cadence. Confirm only the scheduler tick runs on boot (no parallel boot backfill).
- [ ] Spot-check pl-transactions sync: confirm NULL class_name rows are absent from the four UNION branches and the Zod-parse warning count drops.