The Trilogy Times

## What

Removes the cascading stamp_grainne_tracking call on PAIN_POINTS_TABLE from push_pain_points_unpushed in pipelines/runners/renewal-action-hub/scripts/push_to_grainne.py. Theme-shaped pushes now only stamp the theme row itself; pain-point tracking is owned exclusively by the individual pain-point push path.

## Why

This is a port of a real bug fix that lived on the legacy klair-udm branch 339-rah-sf-writeback-grainne (commit 7af9b6518, authored 2026-04-09) and never made it into Surtr because the CDK-managed pipeline was migrated here (commit 88ed773a7 cleanup: remove CDK-managed pipelines migrated to Surtr) before the fix branch landed in KLAIR main.

push_pain_points_unpushed groups unpushed pain points under their parent theme and POSTs to Grainne via the theme-shaped endpoint. After the POST it was also stamping grainne_job_id / grainne_pushed_at on every pain point in the batch. That cascade is wrong.

## Why

The Portfolio Kanban is becoming the SLT's primary working surface. Sort + display alone isn't enough; Arthur's first concrete ask was Alpha vs non-Alpha, with general filtering needed as the view's footprint grows. This PR adds composable filtering and wires brand through from Rhodes (which the schema was silently dropping until now).

## What

### Filters

- Brand, Stage, and Site Owner, exposed in a single Add filter popover next to the existing Sort & display.

- Filters compose: AND across dimensions, OR within a dimension.

- State persists per browser via localStorage["portfolio-filters"], mirroring the existing portfolio-view-mode and portfolio-list-sections keys.

### Linear-style UX

- Top-level popover: search field + drillable category list (Brand · Stage · Owner). Each category shows the count of active filter values when it has any.

- Top-level search hits both axes. Typing alpha surfaces Brand: Alpha · 19 as a direct toggle without drilling. Typing stage shows the Stage drill-in row. Typing a string that matches both a category name and a value (e.g. ow → matches Owner category and Owen Owens value) shows both, separated by a divider. This was an iteration after a UX bug where the original implementation only filtered category names.

- Sub-view per category: search + checkbox list with per-value counts. Counts respect search + every filter dimension *except* the one being shown — so the number tells you what would survive if you toggled that value, which is what you actually care about (Linear pattern).

- Active filter chips below the toolbar — one chip per active value (Brand: Alpha · ×); click anywhere on the chip to remove it.

- A count badge on the Add filter trigger summarises total active filters; Clear all lives in the popover header when any filter is active.

### Brand wired through from Rhodes

- Added brand: z.string().optional() to rhodesPortfolioSiteSchema and to RhodesPortfolioSite. Until this PR, .passthrough() was preserving brand on the parsed object but the typed shape never declared it, so derivePortfolioSiteSummary was reading a value that didn't exist on its input type.

- derivePortfolioBrand(name) (the previous name-prefix heuristic) is gone. Aerie no longer synthesises brand from site name. Brand is either the Rhodes value or null — no fallbacks to stale data.

- Free-form string | null end-to-end. The earlier "alpha" | "other" enum is removed. The Brand filter section renders distinct values from the dataset (mirroring the Owner section), so any future Rhodes brand-taxonomy change is a no-op for Aerie.

- localStorage validation: stage stays a closed-enum allowlist; brand and owner are non-empty strings (a value that no longer matches any site simply produces no matches, rather than silently rewriting the user's saved state).

### Per-browser persistence

localStorage["portfolio-filters"] is read on mount, with malformed-JSON / unknown-stage fallback to "no filters" (no throws). Hydration writes through on every change.

## Verification

I probed the Rhodes /sync/aerie/listSites endpoint directly using the env vars in .env.local to confirm Rhodes is shipping brand today. Of 125 sites returned:

| Brand | Sites |

|---|---|

| Alpha | 19 |

| Sports Academy | 2 |

| Nova Academy | 2 |

| Montessorium | 1 |

| Waypoint Academy | 1 |

| GT School | 1 |

| _(null/missing/empty)_ | 99 |

So Rhodes ships 6 distinct brand strings — validates the design call to use free-form pass-through rather than an alpha/other binary. ~80% of sites have no brand — that's upstream data hygiene, not blocking this PR, but worth a follow-up against whoever owns the Rhodes/Wrike brand backfill. The filter behaves correctly: those 99 sites show up by default, and disappear when any brand filter is active (intentional — there's no (Unbranded) sentinel today; if SLT wants one, that's a one-row UI extension).

### Tests

100/100 portfolio tests passing (was 23 before this PR; +77 net):

- chat/lib/__tests__/portfolio-sites.test.ts — 26 tests, including new coverage for derivePortfolioSiteSummary brand pass-through, null/empty normalisation, and an explicit guard against the previous heuristic ("does not synthesise brand from the site name").

- chat/components/dashboards/portfolio/__tests__/portfolio-toolbar.test.tsx — 20 tests, covering the root drill-in flow, sub-view value rendering with counts, the cross-category root search, chip-row rendering and removal, count badge math, and Clear all.

- chat/components/dashboards/portfolio/__tests__/portfolio-view.test.tsx — 12 tests, covering filter composition (AND across dimensions, OR within), localStorage hydration, malformed-JSON fallback, persistence write-through, and brand: null exclusion when a brand filter is active.

Typecheck clean. Lint clean (3 pre-existing warnings in unrelated files outside this PR's scope).

## Two things to know when testing locally

1. Clear localStorage["portfolio-filters"] before testing if you tested an earlier iteration of this branch — the old form persisted "alpha" / "other" strings, which won't match the real Rhodes brand values ("Alpha", etc.) and would make the filter look broken. One-time cleanup; real users post-merge are unaffected.

2. The Brand filter section will look quiet. Only 26 of 125 sites have a brand value to filter by. That's the upstream data state, not a bug.

## Out of scope (logged for follow-ups)

- Backfill brand on Rhodes for the unbranded ~99 sites. Not an Aerie change.

- Milestone-level filter (the 9-value enum). Stage covers SLT's coarse use case; adding milestone is a one-row addition under the new pattern.

- (Unbranded) filter sentinel — surface sites with brand: null when filtering. v2 if SLT wants it.

- Cascading sub-popovers — Linear's true side-by-side cascade. We use mode-swap inside one popover (90% of the UX, much less Radix machinery). Promote later if filter dimensions grow.

## Overview

Lands the per-field inline editing surface for the Portfolio Details panel ([AERIE-238](https://linear.app/builder-team/issue/AERIE-238)). Replaces PR #154's category-grouped form card (the audit-framing approach that didn't earn adoption) with operator-first inline edits across every detail card. Treats the panel as a daily working tool: subtle pencil-on-hover affordances, panel-scoped confirm dialog with read-fresh-on-open conflict detection, soft-advisory presence (no locks).

Spec + research artifacts: [features/portfolio/details-editing/](https://github.com/AI-Builder-Team/Aerie/tree/feat/portfolio-details-editing/features/portfolio/details-editing).

## Specs landed

| # | Spec | Notes |

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

| 01 | presence-foundation | Convex sitePresence table + heartbeat hook + cron prune + <PortfolioPresenceProvider> + stacked-avatar header. |

| 02 | field-primitive-and-registry-integration | <PortfolioField> primitive (idle/editing/confirming FSM), usePortfolioFields() provider, panel-mounted <SaveConfirmDialog>. LeafKind extended with stringArray + user. PortfolioSiteRow widened with 30+ catalog fields. PR #154's SiteFieldsEditorCard deleted. |

| 03 | fact-sheet-edits | Fact Sheet card hosts ~26 editable cells (Identity + Model + Capacity + Tuition merged). Status chip inline edit on the header. Capacity-rename dual-read. |

| 04 | pipeline-milestones-composite | 9-milestone composite editor; reuses MILESTONES_DESCRIPTOR + flatten/nest. Single-open invariant; sibling-state preservation. |

| 05 | personnel-roles | DRIs become user references via active-user picker; sitePOC + vendorTeam as strings. |

| 06 | lease-opening-dates | Card renamed Lease & Purchase → Lease & Opening; 8 date/string cells. |

| 07 | compliance-card | NEW main-column card. 14 fields incl. stringArray (sevisOfficials, educationCounsel). Owns the zoning relocation from Fact Sheet. |

| 08 | legal-entity-card | NEW main-column card. 7 mechanical fields. Adds opt-in multiline prop on <PortfolioField> for legalCounselContactInfo. |

| 09 | quality-bars-card | NEW right-rail readonly card. 13 RAG bars from existing Rhodes qualityBars payload. |

| 10 | dd-writeback-integration | Portfolio-side DD editing routes through canonical-sites writeDueDiligence action (REBL3 SoR + Rhodes cache + schoolFieldWriteLog audit). New writePortfolioDueDiligence slug-based action wraps the shared pipeline; existing admin path untouched. Catalog flips dueDiligence from readonly. Pre-gated via new canEditDueDiligence API field. |

## Post-spec UX polish (final commit)

Driven by JC's first-pass operator review of the landed feature:

- Visible Save / Cancel buttons on <PortfolioField> editing render. Subtle stone-dim resting tone (matches the pencil); hover reveals accent on save, coral on cancel. Keyboard parity (Enter / Escape) preserved.

- Brand activation — Rhodes coverage shipped; flipped from <MutedDash /> placeholder to live editable.

- "Pipeline Statuses" → "Milestones" card title (right rail).

- RagDot vertical alignment — mt-1.5 nudge for multi-line milestone rows; items-center for single-line quality bars.

- Field-level presence WRITER — Spec 02 shipped readers without writers, leaving other viewers blind to active edits. useSitePresence({ enabled }) gate + new useFieldPresenceClaim(fieldId, enabled) hook; wired into <PortfolioField>, <MilestoneRow>, <DueDiligenceEditor>.

- Shared <PresenceAvatar> with Radix tooltip ({name} is editing/viewing this field). Replaces 4 duplicated inline avatar implementations.

- Drive / Wrike / Matterport linkBuilder — opt-in prop on <PortfolioField>; idle shows View Drive / View in Wrike / View walkthrough, edit shows raw ID. Drops the clunky inline PortfolioField + ExternalLink Matterport hack.

- /fields route soft-fail for listActiveUsers — was conflating upstream auxiliary failure with "site not found" 404. Now logs and degrades to empty user picker; listSites failures still hard-fail.

## Out of scope (deferred follow-ups)

- Audit hover / edit history — Rhodes has its own audit; revisit if operators ask.

- Lock enforcement on field-level presence — chose advisory.

- Optimistic UI / rollback — replaced by pessimistic confirm-dialog gating.

- Validation beyond catalog — buildRhodesSitePatch covers types/enums/required.

- Real-time co-edit / cursors — overkill for team size.

- DD permission loosening — DD writes stay gated on canManageSchoolFields (separate from portfolio's broader canEdit); operators with portfolio rights but without DD permission see the card readonly. Real policy decision deferred.

- EditableStatusChip field-level claim — EnumPopover open state isn't externally exposed and the edit window is sub-2-second; skipped pending an API lift if requested.

- Quality Bars editing — derived values from work-unit groups + tasks; structurally readonly.

- Per-WUG drilldown on Quality Bars — explicitly out of scope per Spec 09 Decision 5.

## Verification

| Check | Result |

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

| cd chat && pnpm exec tsc --noEmit -p tsconfig.json | clean |

| pnpm -F @bran/contracts test | 91 / 91 |

| Portfolio surface vitest sweep | 459 / 459 across 36 files |

| Biome (staged files) | clean |

| Convex codegen | chat/convex/_generated/api.d.ts was hand-augmented for sitePresence (live npx convex dev against the dev deployment will normalise on first run) |

## How to test locally

1. pnpm install && pnpm -F chat dev

2. Open any portfolio site at /dashboards/portfolio/[siteSlug]. Cards render at rest; pencil shows on hover for editable fields when you have edit rights.

3. Inline edit: click any pencil → input + visible Save (✓) / Cancel (✗) icons trail the input. Enter/Save commits, Escape/Cancel drops.

4. Save dialog: pessimistic confirm — re-reads the field fresh on open and shows the diff.

5. Presence: open the same site in two browsers as different users. Doc-level avatars appear in the header; field-level avatars appear next to the field actively being edited (~30s heartbeat). Hover any avatar for the tooltip.

6. DD editing: requires canManageSchoolFields. Pencil sits on the DD card header next to the status chips. Editor opens inline; save round-trips to REBL3 + Rhodes + audit log.

7. Drive / Wrike / Matterport: hover the row in Fact Sheet → click pencil → raw ID appears for edit. Save → idle re-renders as a clickable link.

## Commits

50 commits structured as one wave-execution-per-spec (10 specs × spec doc + tests + impl + checklist marker) plus the final UX polish commit. Branch is rebased on main.

## Local follow-ups noted (not blockers)

- chat/convex/_generated/api.d.ts hand-augmented for sitePresence (transient — running npx convex dev against the dev deployment normalises).

- Date idle render shows raw ISO instead of localised string (Spec 02 ergonomic gap).

- User picker requires Enter to commit after option click (Spec 02 ergonomic gap).

- multiline is currently a card-local PortfolioField prop; promote to catalog kind once a third multi-line string field needs it.

- PORTFOLIO_MILESTONE_LABELS vs MILESTONE_LABELS divergence on 5 keys flagged for separate contracts patch.

## Demo

<img width="2191" height="1549" alt="image" src="https://github.com/user-attachments/assets/93f6c37e-28c1-4500-b680-ddb7422c423e" />

<img width="2207" height="1614" alt="image" src="https://github.com/user-attachments/assets/ef6209f0-31e6-4508-856c-f4eb8755941c" />

## Linear

- [KLAIR-2605: Ingest SaaS budgeting source sheets into Redshift (core_finance.saas_budgeting_*)](https://linear.app/builder-team/issue/KLAIR-2605)

## Overview

Extends the operator-driven SaaS budgeting pipeline at aws-saas-budget-scripts/pipeline/ to cover two additional Google source surfaces alongside the existing Docker ingest. The weekly Central Services Units - W<NN>'<YYYY> databases tab and the mappings database mapping tab now land in Redshift under core_finance.saas_budgeting_*, behind the same python -m pipeline.main entrypoint, sharing the existing secrets / connection helpers and idempotency contract. Data-tier only — no frontend, no API surface.

## What was built

- Spec 01 — databases tab ingest: New databases_ingest.py lists weekly snapshot files in folder 1HuPt4CGkErK7JHRw2vKth9RpaI-_dpF5, picks newest-modified per (year, week) (discards logged), validates the databases tab header, projects (product, db_engine, db_server, db_name, cpu_hours, storage_gb) with hard-fail on unparseable numerics, and writes via DELETE+INSERT keyed on (snapshot_year, snapshot_week).

- Spec 02 — mapping tab ingest: New mappings_ingest.py reads the single mapping tab of sheet 1cyf34vYt8l_RPYYe8St4WFVKkovM2-dM_WzwyI2uxm0, validates the locked four-column header (database, db_engine, product, l5), rejects non-string cells, captures Drive modifiedTime, and full-replaces the table inside one transaction. Additive helpers added to drive_client.py and redshift_writer.py.

- Spec 03 — CLI multi-ingest dispatcher: Existing Docker logic extracted verbatim into docker_ingest.py; main.py rewritten as a thin argparse dispatcher with --ingest docker|databases|mappings|all. Per-ingest flag validation rejects --reingest / --quarter for mappings with a clear stderr message. Shared calendar utilities extracted to _calendar.py to break a circular import. --ingest all runs in fixed order (mappings → databases → docker) and aborts on first failure.

## Tables created

| Table | DDL | Idempotency |

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

| core_finance.saas_budgeting_database_units | scripts/sql/create_saas_budgeting_database_units.sql | DELETE+INSERT per (snapshot_year, snapshot_week) |

| core_finance.saas_budgeting_database_mapping | scripts/sql/create_saas_budgeting_database_mapping.sql | Full-replace in single transaction (sheet is one global snapshot) |

## CLI surface

python -m pipeline.main --ingest {docker,databases,mappings,all}
[--reingest YYYY-WWW[,YYYY-WWW,...]]
[--quarter YYYY-Qn]
[--dry-run]

- Default --ingest all runs mappings → databases → docker in that order.

- --reingest / --quarter apply to docker and databases. They are rejected with a non-zero exit and clear stderr message when --ingest mappings is selected, since the mappings sheet is a single global snapshot and week-keying does not apply.

- --ingest all combined with --reingest / --quarter emits a stderr advisory and routes the flags to the week-aware ingests (mappings ignores them).

- Behavioral parity preserved for the Docker ingest: --ingest docker --reingest 2026-W18 is byte-identical to today's invocation.

## Test coverage

- 124 tests passing (59 prior + 65 new): 18 for databases ingest, 27 for mappings ingest, 16 for the dispatcher, plus 4 new tests covering the self-review fixes below.

- ruff format + ruff check clean on changed files.

- pyright clean on changed files.

## Self-review

Three findings addressed in-PR:

1. Timezone bug in _parse_modified_time — astimezone(None) was relying on local-time-zone resolution; replaced with explicit astimezone(timezone.utc) so Drive modifiedTime always lands as UTC regardless of operator machine.

2. _coerce_numeric accepted booleans — Python's bool is a subclass of int, so a stray TRUE cell would silently coerce to 1.0. Added an isinstance(raw, bool) reject branch with a ValueError naming the column and sheet id.

3. Cross-ingest semantics in main.py — Added a comment block explaining the mappings → databases → docker ordering rationale (reference data first; cheapest feedback loops before the most expensive ingest) so future contributors understand why the order is fixed.

Four minor findings cleared without code changes:

1. Pluralizing mapping to mappings_ingest — flagged but kept; matches the --ingest mappings CLI flag and the FEATURE.md naming, breaking the parity would force a doc churn for cosmetic reasons.

2. Skip-when-unchanged optimization for mappings — explicitly deferred in spec 02; the source_modified_time column is already persisted so a future spec can flip the dispatcher without a schema change.

3. Centralizing chunked-INSERT logic — write_databases_snapshot and write_mapping_snapshot each repeat the chunked-INSERT loop. Acceptable duplication for two call sites; consolidation would couple table-shape-specific column lists and is premature.

4. docker_ingest.run exit-code alignment with mappings_ingest.run dataclass return — adapter lives in main.py's dispatch loop. Changing mappings_ingest.run to return int would shed the typed MappingIngestResult that callers (and tests) rely on.

## Files changed

- Implementation modules (4): databases_ingest.py (new), mappings_ingest.py (new), docker_ingest.py (new — extracted), _calendar.py (new — shared calendar utils), plus additive edits to drive_client.py, redshift_writer.py, and a near-total rewrite of main.py.

- DDL (2): scripts/sql/create_saas_budgeting_database_units.sql, scripts/sql/create_saas_budgeting_database_mapping.sql.

- Tests (3): tests/test_databases_ingest.py, tests/test_mappings_ingest.py, tests/test_main_dispatch.py.

- Specs (4): FEATURE.md + three spec files under features/aws-spend/saas-budgeting-source-sheets-ingest/specs/.

## Test plan

- [ ] Apply DDL to Redshift sandbox cluster (create_saas_budgeting_database_units.sql, create_saas_budgeting_database_mapping.sql)

- [ ] python -m pipeline.main --ingest databases --dry-run against staging Drive folder

- [ ] python -m pipeline.main --ingest mappings --dry-run against staging sheet

- [ ] Behavioral parity check: python -m pipeline.main --ingest docker --reingest 2026-W18 produces identical output to today's python -m pipeline.main --reingest 2026-W18

- [ ] python -m pipeline.main --ingest mappings --reingest 2026-W18 exits non-zero with clear stderr message

- [ ] python -m pipeline.main --help lists docker, databases, mappings, all

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

### Summary

The "Prior FY Cash Flows" upload feature was repurposed to surface a Year-to-Date current-vs-prior comparison instead of two FY end-of-year snapshots. The old behavior keyed off has_prior_fy_data and fell back to the most recent upload across all periods, which is incorrect for YTD semantics (Apr-26 YTD differs from May-26 YTD). This PR makes the lookup strictly period-specific on both client and server, updates the CSV parser to the new MMM-YY YTD header format, gates the comparison table to past-Q1 memos, and prevents a single-format CSV upload from wiping the counterpart QTD/YTD record at the same period.

### Business Value

Group memo exports now show an accurate YTD cash-flow comparison aligned to the report period, eliminating a confusing data-mismatch where a stale prior-period upload could appear in a current memo. Finance users can independently maintain QTD and YTD uploads for the same period without one overwriting the other, reducing rework and the risk of lost data during the monthly close cycle.

### Changes

- Backend ([memo_data/group.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/memo_data/group.py)): replaced _get_prior_fy_upload (cross-period fallback) with _get_ytd_upload(period) doing an exact period lookup; hoisted cash_flow_upload_service import to module level.

- Backend ([reports/group.py](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-api/services/docx_reports/reports/group.py)): added _is_past_q1 helper (months 4-12); gated the comparison table on past-Q1 periods; renamed table title/headers from "Prior Fiscal Years" / "FY Current/Prior" to "Year-to-Date" / "YTD Current/Prior" while preserving the legacy prior_fy_items storage slot for DynamoDB compatibility.

- Frontend ([parseCashFlowCsv.ts](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-client/src/features/monthly-financial-reporting/utils/parseCashFlowCsv.ts)): detects the new MMM-YY YTD header format, derives period from the first YTD column, ignores variance/noise columns, throws explicit errors for legacy FY YYYY headers and unknown month abbreviations; type renamed prior-fy → ytd.

- Frontend ([parseCashFlowCsv.spec.ts](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-client/src/features/monthly-financial-reporting/utils/parseCashFlowCsv.spec.ts), new): comprehensive coverage of YTD detection, period derivation, value scaling, subtotal-row dropping, variance tolerance, legacy-FY rejection, QTD regression, and unknown-format rejection.

- Frontend ([transformFinancialStatements.ts](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-client/src/features/monthly-financial-reporting/utils/transformFinancialStatements.ts) + spec): exported isPastQ1(period) helper with malformed-input warning and parametrized boundary tests; updated YTD section labels.

- Frontend ([useCashFlowUpload.ts](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-client/src/features/monthly-financial-reporting/hooks/useCashFlowUpload.ts)): removed cross-period FY fallback; load now uses exact entity+period match; status-aware error toasts (401/403, 5xx).

- Frontend ([CashFlowUploadView.tsx](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-client/src/features/monthly-financial-reporting/components/CashFlowUploadView.tsx)): merge-aware save preserves the existing counterpart QTD/YTD slot at the same period; dynamic button label ("Overwrite QTD/YTD & Save" / "Add QTD/YTD & Save" / "Save"); merge-vs-overwrite toast; updated CSV template link and "Prior FY" → "YTD" labels.

- Frontend ([GroupMemoView.tsx](vscode-webview://15qdonnjjcq9q3pcmufmg5fa0asnqc6qnceup60m8cm6igoedkcj/klair-client/src/features/monthly-financial-reporting/components/GroupMemoView.tsx)): YTD table hidden in Q1 (Jan-Mar) via isPastQ1; source-panel labels updated.

### Testing

- [ ] pnpm test in klair-client/ — new parseCashFlowCsv.spec.ts and transformFinancialStatements.spec.ts isPastQ1 suite pass

- [ ] pnpm lint:pr in klair-client/

- [ ] pnpm tsc --noEmit in klair-client/

- [ ] uv run ruff format + uv run ruff check on changed Python files in klair-api/

- [ ] uv run pyright on changed Python files

- [ ] pytest tests/docx_reports/ in klair-api/

- [ ] Upload a YTD CSV (MMM-YY YTD headers) for an Apr-Dec period and verify the comparison table appears with "Year-to-Date" subtitle in the Group memo export

- [ ] Upload a YTD CSV for a Jan-Mar period and verify the comparison table is hidden

- [ ] Upload a QTD CSV at a period that already has a YTD record — confirm the YTD record is preserved (and vice versa); button label shows "Add YTD/QTD & Save"

- [ ] Re-upload at the same format slot — confirm button label shows "Overwrite YTD/QTD & Save" and toast reflects overwrite

- [ ] Upload a legacy FY YYYY CSV — confirm explicit error is shown

- [ ] Confirm the YTD comparison no longer appears when there is no upload for the exact memo period (no cross-period fallback)

http://localhost:3001/monthly-financial-reporting

<img width="1907" height="822" alt="image" src="https://github.com/user-attachments/assets/502aa4d6-aad4-47f1-a988-85b753954ca9" />

## Summary

- Add a "Use marketing names" toggle to the Portfolio Kanban's Sort & display popover (board view only)

- Card titles swap between site.name and site.marketingName, falling back to site.name per-site when marketing is missing or equal

- Mode persists per-browser via localStorage; sort-by-name follows the visible name

## Why

Internal users share the Portfolio view with people outside the SLT (sales, HoS, etc.). The internal slug-style names are noisy in those contexts — toggling to marketing names makes the same view shareable without maintaining a parallel surface. Arthur explicitly asked for this. Resolves [AERIE-240](https://linear.app/builder-team/issue/AERIE-240).

The ticket lists a "blocked by name normalisation" dependency. In practice both name and marketingName are already distinct fields on PortfolioSiteSummary from Rhodes — this PR just swaps two known-good fields, so the dependency is "may look weird until normalisation lands" rather than a hard block.

## Test plan

- [x] 36 portfolio tests / 3180 chat tests / typecheck / biome — all green

- [ ] Manual: open Sort & display on the Kanban board → flip Use marketing names → confirm card titles swap, sort-by-name follows the visible name, hard refresh persists the choice

- [ ] Sites without a distinct marketing name keep their internal name in marketing mode (no blank cards)

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

## Why

SLT flagged the Portfolio "Sort & display" popover: three static options with directions baked into the labels (Time in milestone (longest first), Target open date (soonest first), Site name (A–Z)) — no way to flip ascending↔descending without picking a different field, no consistency with how every other dashboard sorts.

## What I considered

The codebase pattern for sort is header-click toggle (SortHeader + cycleSortConfig in chat/components/dashboards/shared/sort-header.tsx), used by Admissions, Community, FTO, PMO, School Ops. The natural instinct was "make Portfolio match." I pushed back on this — three structural mismatches the other dashboards don't have:

1. Board view has no headers. Header-click solves zero of the Kanban's sort needs; we'd still need a separate control just for Board, ending up with two affordances.

2. The list is grouped by ready-to-open bucket (Open / Aug 2026 / Jan 2027 / later / unknown — see portfolio-list.tsx). Global header-click sort either fights the grouping or means "sort within section" — a different mental model from every other dashboard, defeating the consistency argument.

3. The default sort isn't a column. days_in_phase is the default but the list shows Site / Milestone / Diligence / Capacity / Enrollment / Projected — no "Time in milestone" column to click.

So I went with Linear's pattern (the screenshot Daisy referenced): inline Sort by row with a direction toggle + dropdown trigger. Symmetric across List and Board, doesn't fight the bucket grouping, lets sort options exist that aren't columns.

## What changed

Single popover section in portfolio-toolbar.tsx:

┌─ Sort & display ─────────────────────────────────┐
│ [List | Board]                                    │
│ ─────────                                         │
│ Sort by              [↓]  [Time in Milestone ▼]   │
│ ─────────                                         │
│ LIST SECTIONS  ☑ Open  ☑ Aug 2026  ...             │
└───────────────────────────────────────────────────┘

- Direction toggle (SortDirectionToggle) — lucide ArrowUp/ArrowDown, accessible label "Sort direction: Descending (click to flip)".

- Field dropdown (SortFieldDropdown) — nested Radix popover, options rendered with role="menuitem", current selection ticked. Trigger label is the active field so the user sees the current sort at a glance.

- Three fields, matching the list column header naming exactly (Site Name / Ready to Open Date / Time in Milestone) so the rendered order traces back to a value users can see on the row.

### Behaviour decisions

- Field change resets direction to the field's natural default. Picking Site Name always lands you on A→Z first; user flips to Z→A explicitly via the toggle. Avoids the surprise of a fresh field inheriting an unrelated direction.

- Null target dates always sort to the end regardless of direction. A site with no Ready-to-Open date is "less informative", not "lower" — flipping desc shouldn't pull unknowns to the top.

- Persistence. Both key and direction live in localStorage (portfolio-sort-key, portfolio-sort-direction), hydrated on mount with a typed validator (isPortfolioSortKey). Malformed values fall back to default — no throws.

- Auto-close on selection. Picking a field closes only the inner dropdown; the parent popover stays open so you can immediately flip the direction without reopening anything.

### Iteration history (worth knowing)

- v1 of this branch added Brand, Owner, Capacity as sort fields. Daisy correctly pushed back: those values aren't visible on Board cards (and Brand/Owner aren't visible on List rows either), so sorting by them produces orderings with no obvious explanation. Rolled back to the original three.

- Labels were originally sentence case (Site name); aligned to title case to match the list view column header (Milestone / Ready to Open Date).

## Tests

pnpm test components/dashboards/portfolio/ — 98/98 passing (was 93 on main after rebase onto #160; +5 net).

Toolbar (portfolio-toolbar.test.tsx):

- Renders all three sort fields in the dropdown.

- Dropdown trigger shows the current field label (Sort field: Ready to Open Date).

- Picking a field calls onSortChange and closes the dropdown.

- Direction toggle flips desc → asc, surfaces the right aria-label for both states.

View (portfolio-view.test.tsx):

- Direction toggle re-orders cards and writes the new direction to localStorage.

- Switching field resets direction to that field's default (saved name + desc → switch to Ready to Open Date → direction lands on asc).

- Hydrates persisted key + direction on mount (saved name + desc → cards render Z→A).

- Ignores malformed persisted sort key without throwing.

tsc --noEmit clean. biome check clean. Pre-commit hook (biome + typecheck-chat) clean.

## Out of scope (logged for later)

- Capacity is actually visible in the list view — it was the one removed sort field with a real claim to inclusion. Dropped because Board cards don't show it (asymmetric UX) and it wasn't in the scoped-down ask. Easy one-line add later if SLT asks.

- Multi-sort (shift+click adds a secondary sort) — the shared cycleSortConfig supports it, but the Linear-style single-field control doesn't. Not requested; a v2 if needed.

- Header-click on List as a power-user shortcut — could layer in cheaply later (the dropdown stays as primary). Holding off until usage tells us it's wanted.

## Demo

<img width="2191" height="1549" alt="image" src="https://github.com/user-attachments/assets/93f6c37e-28c1-4500-b680-ddb7422c423e" />

<img width="2207" height="1614" alt="image" src="https://github.com/user-attachments/assets/ef6209f0-31e6-4508-856c-f4eb8755941c" />

## Linear

- [KLAIR-2605: Ingest SaaS budgeting source sheets into Redshift (core_finance.saas_budgeting_*)](https://linear.app/builder-team/issue/KLAIR-2605)

## Overview

Extends the operator-driven SaaS budgeting pipeline at aws-saas-budget-scripts/pipeline/ to cover two additional Google source surfaces alongside the existing Docker ingest. The weekly Central Services Units - W<NN>'<YYYY> databases tab and the mappings database mapping tab now land in Redshift under core_finance.saas_budgeting_*, behind the same python -m pipeline.main entrypoint, sharing the existing secrets / connection helpers and idempotency contract. Data-tier only — no frontend, no API surface.

## What was built

- Spec 01 — databases tab ingest: New databases_ingest.py lists weekly snapshot files in folder 1HuPt4CGkErK7JHRw2vKth9RpaI-_dpF5, picks newest-modified per (year, week) (discards logged), validates the databases tab header, projects (product, db_engine, db_server, db_name, cpu_hours, storage_gb) with hard-fail on unparseable numerics, and writes via DELETE+INSERT keyed on (snapshot_year, snapshot_week).

- Spec 02 — mapping tab ingest: New mappings_ingest.py reads the single mapping tab of sheet 1cyf34vYt8l_RPYYe8St4WFVKkovM2-dM_WzwyI2uxm0, validates the locked four-column header (database, db_engine, product, l5), rejects non-string cells, captures Drive modifiedTime, and full-replaces the table inside one transaction. Additive helpers added to drive_client.py and redshift_writer.py.

- Spec 03 — CLI multi-ingest dispatcher: Existing Docker logic extracted verbatim into docker_ingest.py; main.py rewritten as a thin argparse dispatcher with --ingest docker|databases|mappings|all. Per-ingest flag validation rejects --reingest / --quarter for mappings with a clear stderr message. Shared calendar utilities extracted to _calendar.py to break a circular import. --ingest all runs in fixed order (mappings → databases → docker) and aborts on first failure.

## Tables created

| Table | DDL | Idempotency |

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

| core_finance.saas_budgeting_database_units | scripts/sql/create_saas_budgeting_database_units.sql | DELETE+INSERT per (snapshot_year, snapshot_week) |

| core_finance.saas_budgeting_database_mapping | scripts/sql/create_saas_budgeting_database_mapping.sql | Full-replace in single transaction (sheet is one global snapshot) |

## CLI surface

python -m pipeline.main --ingest {docker,databases,mappings,all}
[--reingest YYYY-WWW[,YYYY-WWW,...]]
[--quarter YYYY-Qn]
[--dry-run]

- Default --ingest all runs mappings → databases → docker in that order.

- --reingest / --quarter apply to docker and databases. They are rejected with a non-zero exit and clear stderr message when --ingest mappings is selected, since the mappings sheet is a single global snapshot and week-keying does not apply.

- --ingest all combined with --reingest / --quarter emits a stderr advisory and routes the flags to the week-aware ingests (mappings ignores them).

- Behavioral parity preserved for the Docker ingest: --ingest docker --reingest 2026-W18 is byte-identical to today's invocation.

## Test coverage

- 124 tests passing (59 prior + 65 new): 18 for databases ingest, 27 for mappings ingest, 16 for the dispatcher, plus 4 new tests covering the self-review fixes below.

- ruff format + ruff check clean on changed files.

- pyright clean on changed files.

## Self-review

Three findings addressed in-PR:

1. Timezone bug in _parse_modified_time — astimezone(None) was relying on local-time-zone resolution; replaced with explicit astimezone(timezone.utc) so Drive modifiedTime always lands as UTC regardless of operator machine.

2. _coerce_numeric accepted booleans — Python's bool is a subclass of int, so a stray TRUE cell would silently coerce to 1.0. Added an isinstance(raw, bool) reject branch with a ValueError naming the column and sheet id.

3. Cross-ingest semantics in main.py — Added a comment block explaining the mappings → databases → docker ordering rationale (reference data first; cheapest feedback loops before the most expensive ingest) so future contributors understand why the order is fixed.

Four minor findings cleared without code changes:

1. Pluralizing mapping to mappings_ingest — flagged but kept; matches the --ingest mappings CLI flag and the FEATURE.md naming, breaking the parity would force a doc churn for cosmetic reasons.

2. Skip-when-unchanged optimization for mappings — explicitly deferred in spec 02; the source_modified_time column is already persisted so a future spec can flip the dispatcher without a schema change.

3. Centralizing chunked-INSERT logic — write_databases_snapshot and write_mapping_snapshot each repeat the chunked-INSERT loop. Acceptable duplication for two call sites; consolidation would couple table-shape-specific column lists and is premature.

4. docker_ingest.run exit-code alignment with mappings_ingest.run dataclass return — adapter lives in main.py's dispatch loop. Changing mappings_ingest.run to return int would shed the typed MappingIngestResult that callers (and tests) rely on.

## Files changed

- Implementation modules (4): databases_ingest.py (new), mappings_ingest.py (new), docker_ingest.py (new — extracted), _calendar.py (new — shared calendar utils), plus additive edits to drive_client.py, redshift_writer.py, and a near-total rewrite of main.py.

- DDL (2): scripts/sql/create_saas_budgeting_database_units.sql, scripts/sql/create_saas_budgeting_database_mapping.sql.

- Tests (3): tests/test_databases_ingest.py, tests/test_mappings_ingest.py, tests/test_main_dispatch.py.

- Specs (4): FEATURE.md + three spec files under features/aws-spend/saas-budgeting-source-sheets-ingest/specs/.

## Test plan

- [ ] Apply DDL to Redshift sandbox cluster (create_saas_budgeting_database_units.sql, create_saas_budgeting_database_mapping.sql)

- [ ] python -m pipeline.main --ingest databases --dry-run against staging Drive folder

- [ ] python -m pipeline.main --ingest mappings --dry-run against staging sheet

- [ ] Behavioral parity check: python -m pipeline.main --ingest docker --reingest 2026-W18 produces identical output to today's python -m pipeline.main --reingest 2026-W18

- [ ] python -m pipeline.main --ingest mappings --reingest 2026-W18 exits non-zero with clear stderr message

- [ ] python -m pipeline.main --help lists docker, databases, mappings, all

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

## Demo

<img width="2205" height="1644" alt="image" src="https://github.com/user-attachments/assets/5088de66-0d7b-4f6d-a89e-44ed1e88b6f1" />

## Summary

Adds prior-quarter actuals comparison to each (BU, Provider) leaf in the AI Spend Budget vs Actuals table. Two windows are compared side-by-side per leaf:

- Full prior quarter — the externally-quoted number

- QTD-equivalent slice of the prior quarter — apples-to-apples mid-quarter comparison (same number of elapsed days as current QTD)

Q1 wraps to the prior year's Q4. Unbudgeted-leaf emission iterates the union of (BU, Provider) keys across all three windows, so leaves with prev-Q-only spend surface as zero-current-spend rows — preserves the "we stopped spending on X" signal.

Spec: [features/ai-spend-and-adoption/ai-spend-budget-vs-actuals/specs/08-bva-prev-quarter-comparison/spec.md](features/ai-spend-and-adoption/ai-spend-budget-vs-actuals/specs/08-bva-prev-quarter-comparison/spec.md)

## What changed

Backend (klair-api/services/ai_spend_budget_service.py)

- New _compute_prev_quarter_windows(quarter, today) pure helper — Q1→prior-year-Q4 wrap, QTD-equivalent length matching.

- get_budget_vs_actuals now makes 3 get_by_bu(granularity="monthly") calls (current QTD + 2 prior windows) and joins via BUDGET_TO_ACTUALS_PROVIDER + case-insensitive keyword fallback (same logic for all three windows).

- Unbudgeted emission iterates the union of (BU, bucket) and (BU, kp_name) pairs across the three windows.

- New helpers: _build_actuals_by_key, _lookup_prev_actuals, _qoq_delta.

Models (klair-api/models/ai_costs_models.py)

- AISpendBvARow gains 6 float | None fields: prev_q_full_actuals_amount, prev_q_qtd_equiv_actuals_amount, plus QoQ Δ\$ + Δ% for each window. null distinguishes absent pair from present-but-zero; Δ% is also null on div-by-zero.

- AISpendBvAData envelope gains prev_quarter: str + prev_qtd_end_date: str.

Frontend (klair-client/src/screens/AIAdoptionV2/components/BudgetVsActuals/)

- 4 new columns under a two-row Prev Quarter (YYYY-Qn) group header: Full (cols 9–10) and QTD-equiv (cols 11–12). QTD-equiv sub-label has a tooltip showing the exact date range (e.g. \Jan 1 – Jan 30, 2026\).

- Cell renderers: absent pair → —, present-zero → \$0 / New (when current > 0), present-positive → formatted dollars / signed percent.

- buildBvASections rolls up prev-Q on section + grand-total rows (null-as-zero sum, all-null suppresses to —) and re-derives QoQ Δ% from rolled totals — never sums child Δ%.

- useAISpendBvA exposes prevQuarter + prevQtdEndDate.

## Out of scope

- Δ\$ visible columns (computed/emitted but not displayed — keeps table at 12 cols)

- Year-over-year comparison

- Forecast / projected EOQ

- Top-of-page metric-card changes

- Sortable headers on prev-Q columns

- New endpoint (same GET /api/ai-costs/budget-vs-actuals?quarter=..., fatter payload)

## Test plan

- [x] uv run pyright klair-api/services/ai_spend_budget_service.py klair-api/models/ai_costs_models.py — clean

- [x] pytest klair-api/tests/test_ai_spend_budget_service.py — 34/34 passing (existing test updated for new envelope fields)

- [x] pnpm tsc --noEmit — clean

- [x] pnpm test --run src/screens/AIAdoptionV2/components/BudgetVsActuals/ src/hooks/useAISpendBvA — 44/44 passing

- [x] pnpm lint on changed files — clean

- [ ] Manual: load BvA page on a mid-quarter date → verify 4 new columns render with correct group header, QTD-equiv tooltip shows the right date range

- [ ] Manual: expand a BU in BU mode → leaf rows show prev-Q values; section header re-aggregates correctly

- [ ] Manual: pick a quarter where some (BU, Provider) pairs have prev-Q-only spend → verify those surface as zero-current-spend unbudgeted leaves

- [ ] Manual: pick 2026-Q1 (or any Qn=1) → verify prev quarter shows as prior-year Q4

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

## Demo

Clicking the "Attach" button only attaches to that tabs slot (column) in the table

<img width="2152" height="1591" alt="image" src="https://github.com/user-attachments/assets/460dba20-d42e-4516-809a-6e351e4893ef" />

## Summary

Implements [KLAIR-2608](https://linear.app/builder-team/issue/KLAIR-2608/saas-budgeting-make-attach-button-tab-scoped-and-add-it-to-every-tab) as spec 13 of the existing features/aws-spend/saas-budgeting/ feature — natural continuation of specs 06 / 09 / 11 (original AWS Spend Attach, Docker Attach, Adjustments tab).

Generalises the SaaS Budgeting "Attach to Simulated Budget" mechanism so every tab — including Adjustments today and any new tab added later — has its own Attach button that snapshots only that tab's data into that tab's slot.

## What changed

- Snapshot store generalized from hard-coded { awsSpend?, docker? } → { slots: Record<SlotId, SimulatedBudgetSnapshot> }. Adding a future tab is a small local change in 4 colocated places (SlotId + SLOT_ORDER + SLOT_LABELS + TOAST_LABELS).

- localStorage v2 → v3 migration preserves existing awsSpend and docker snapshots; malformed payloads fall back gracefully to an empty slots map.

- Per-tab isolation: attachSlot(slotId, payload) writes only to the named slot. Sibling slots (populated or empty) are strictly untouched — empty slots stay empty.

- Adjustments tab gets an Attach button matching the styling/UX of AWS Spend and Docker tabs (emerald, Pin icon).

- Simulated Budget Card reads slots generically in stable order (AWS Spend → Docker → Adjustments). The Adjustments column is no longer fed live from the backend hook.

## ⚠️ UX behaviour change

The Adjustments column on the Simulated Budget card was live — it updated as the user edited adjustments. After this change, that column is snapshot-based, only updating when the user clicks Attach on the Adjustments tab. Intentional — brings Adjustments in line with AWS Spend / Docker. Worth flagging to anyone who used the live behaviour.

## Spec

[features/aws-spend/saas-budgeting/specs/13-tab-scoped-attach-button/spec.md](features/aws-spend/saas-budgeting/specs/13-tab-scoped-attach-button/spec.md) — 5 FRs, 19 checklist items, all complete.

## Files changed

12 files in klair-client/src/screens/AWSSpend/components/SaaSBudgeting/:

- useSimulatedBudget.ts + tests — generalized slots store + v2→v3 migration

- SimulatedBudgetCard.tsx — reads slots generically; no live adjustments prop

- SaaSBudgetingSection.tsx — handleAttach(slotId) factory + TOAST_LABELS map

- AdjustmentsTab.tsx — new Attach button

- AWSSpendCard.tsx + SaaSBudgetingTable.tsx — generic onAttach prop

- simulatedBudgetMerge.ts + tests — consumes snapshot-shape entries

- adjustmentRollup.ts + tests — consumes snapshot-shape entries

- AWSSpendCard.spec.tsx — existing card-behaviour tests preserved

## Tests

48 / 48 passing across 4 test files:

- useSimulatedBudget.spec.tsx — 10 (v3 seeding, v2 migration valid + malformed, per-slot isolation incl. empty siblings, clearAll, null-normalization, cross-tab storage event sync)

- simulatedBudgetMerge.spec.ts — 17

- adjustmentRollup.spec.ts — 4

- AWSSpendCard.spec.tsx — 17

## Self-review

No issues found. Per-tab isolation, v2→v3 migration, snapshot-based Adjustments column, stable column order, generalised slots map, and Clear-wipes-all are all correctly implemented and covered by tests.

## Test plan

- [x] Per-tab attach isolation — populated slot stays intact when other tab attaches

- [x] Per-tab attach isolation — empty slot stays empty when other tab attaches

- [x] Adjustments Attach round-trip — click → card column populates from snapshot

- [x] Clear button wipes all slots

- [x] localStorage v2 → v3 migration preserves existing slots

- [x] Malformed v2 payload falls back to empty slots map without crashing

- [x] Save-then-Attach on Adjustments captures the row in the snapshot (PR #2716 M3 regression — pinned by AdjustmentsTab.spec.tsx)

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

## Summary

start-services.sh only ran pnpm install when node_modules/.pnpm/ was missing entirely (i.e. fresh worktree). It silently skipped install when pnpm-lock.yaml had been updated since the last install — e.g. after pulling a branch that added a new dependency. The dev server then crashed at runtime with [plugin:vite:import-analysis] Failed to resolve import "<dep>".

Hit today with dompurify after pulling a branch that depended on PR #2689's new dep — the script start succeeded silently and Vite blew up on first request.

### Fix

Run pnpm install --frozen-lockfile unconditionally on every script start.

- Current node_modules → sub-second no-op (~800ms locally).

- Newer pnpm-lock.yaml → installs the missing packages.

- Fresh worktree → still works (frozen-lockfile installs from scratch).

The previous "skip install if .pnpm/ exists" optimisation isn't worth the silent breakage.

## Reproduced + verified

1. Repro: rm -rf klair-client/node_modules/dompurify klair-client/node_modules/.pnpm/dompurify@3.3.3 — .pnpm/ still present, so the old check would not have fired and the install would have been skipped.

2. Fix verified: ran pnpm install --frozen-lockfile in that broken state — pnpm correctly detected the missing package and reinstalled it (+ dompurify 3.3.3, done in 888ms). No-op run on the synced state completed in ~800ms.

## Out of scope

The backend uv sync block (~line 295) has the same logical issue — it only runs when .venv/bin/activate is missing, so a stale .venv after a backend dep bump won't get re-synced. Lower-risk because uv sync is itself fast and idempotent, and uv errors are usually loud at import time rather than silent. Worth a follow-up but kept out of this PR to keep the diff tight.

## Test plan

- [x] Repro the bug locally — confirmed old check doesn't fire when .pnpm/ exists but a sub-package is missing.

- [x] Run pnpm install --frozen-lockfile manually in the broken state — reinstalls cleanly.

- [x] Run pnpm install --frozen-lockfile on the synced state — sub-second no-op.

- [ ] One reviewer should run ./start-services.sh <port> from a fresh-pull worktree and confirm dev server boots cleanly with no manual pnpm install step.

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