## Demo
<img width="2624" height="1636" alt="image" src="https://github.com/user-attachments/assets/6e624471-7bcc-44f5-9a96-2dd5cdf69033" />
## Summary
Retires the monthly maint-report release pipeline for the ARR Retention Reports page. PS Revenue Impact moves from a manually-generated S3 JSON blob to a new Redshift table served via AppSync GraphQL, the dead <PlatinumOrPrimeProgress /> block (which has been silently failing for ~5 months because the source JSON dropped primeProgress) is hidden, and MAX_REPORT_DATE becomes a UTC-aware getMaxReportDate() function so the default selected month advances automatically at month rollover instead of requiring a manual constant bump. The generate.py / prepare.py / gsheet.py scripts, the maint-report-release Claude skill, and the /arr-reports-by-bu FastAPI endpoint are all deleted in the same change.
Linear: [KLAIR-2769 — Retire monthly maint-report release for ARR Retention Reports](https://linear.app/builder-team/issue/KLAIR-2769)
---
## What changed by spec
### Spec 01 — Hide <PlatinumOrPrimeProgress />
Commented out (reversibly) the <PlatinumOrPrimeProgress /> render block, its surrounding {processedProgressData && (...)} guard, the two useMemo hooks (calculatedPlatinumProgress, processedProgressData), the aggregateByBusinessUnit / toMillionsRounded helpers, the PlatinumProgress / PrimeProgress type imports, the default import, the useMemo named import, the platinumOrPrimeProgressData prop on PlatinumReportTablesProps, and the corresponding prop pass-through in index.tsx. <PlatinumARRTable> continues to render unchanged. Also commented out the orphan usePlatinumARRData(prevReportDate) call inside PlatinumReportTables.tsx, which was making a wasted AppSync round-trip for a value nothing consumed.
### Spec 02 — PS Revenue Impact: S3 JSON → Redshift via AppSync
Created the core_finance.maint_report_ps_revenue_impact Redshift table (DDL + sort key on (report_date, business_unit), "Total ESW Group" stored as a regular row), an idempotent backfill script (klair-misc/maint-scripts/backfill_ps_revenue_impact.py) that reads every monthly S3 JSON from Nov 2024 → Apr 2026 and upserts into the new table, and a full AppSync GraphQL surface on klair-udm — schema (getPsRevenueImpact(reportDate: String!)), query builder, response transformer, validator, routing case in app.mjs, and resolver entry in template.yaml. On the frontend, added a new usePSRevenueImpact hook (modelled on useFinancials, AppSync x-api-key auth) and wired it into ARRRetentionReports/index.tsx. The /arr-reports-by-bu FastAPI endpoint and get_arr_reports_data() helper are deleted. useBUReportData.ts is commented out (reversible) and useBUReportData.spec.ts is deleted (Vitest rejects empty spec files; recoverable from git history).
### Spec 03 — Retire monthly maint-report release
Deleted klair-misc/maint-scripts/generate.py, prepare.py, gsheet.py, and the README, plus the entire .claude/skills/maint-report-release/ directory. ARRGap v1 stays alive — the original plan to delete screens/ARRGap/ was wrong because ARRGapV2/index.tsx:20 lazy-imports it as the 2025 historical tab. CI caught the regression on the first push; the spec and FEATURE.md were updated to reflect the correction.
### Spec 04 — Auto-advance max report date
Replaced the manually-bumped MAX_REPORT_DATE constant with a getMaxReportDate() function in three frontend files (constants.ts, MonthPicker.tsx, ARRRetentionReports/index.tsx). The function returns the last day of the most recently completed UTC month, so the default selected month advances automatically at UTC month rollover. Also rewrote two MonthPicker tooltips that were misleading after the auto-advance change.
---
## Cross-cutting refactors
- formatDateForAPI extracted. The helper used to live in useBUReportData.ts. Since that hook is being commented out, formatDateForAPI moved to klair-client/src/utils/dateUtils.ts and the one cross-file consumer (useRenewalEventRetention.ts) was updated to import from the new location.
- ARRGap v1 correction. Mid-CI, the build broke because screens/ARRGap/ was scheduled for deletion in spec 03 — but ARRGapV2/index.tsx:20 lazy-imports it as the historical 2025 tab. Restored screens/ARRGap/ and updated spec 03 + FEATURE.md to document that v1 stays alive.
---
## Test coverage
11 new unit tests across 4 files:
- klair-udm (7 tests): queryBuilder for getPsRevenueImpact, responseTransformer field mapping, and validators covering reportDate shape + error paths.
- klair-client (4 tests): constants.spec.ts for getMaxReportDate() using vi.useFakeTimers() to assert correct UTC month rollover behavior across month/year boundaries.
Existing test suites for ARR Retention Reports, useRenewalEventRetention, and related hooks were re-run and stay green.
---
## Manual steps required before / after merge (USER-EXECUTED)
> The PR contains the DDL and the backfill script, but does not run them. Run these in order:
1. Apply Redshift DDL. Run klair-misc/maint-scripts/sql/create_maint_report_ps_revenue_impact.sql against the prod Redshift cluster to create core_finance.maint_report_ps_revenue_impact.
2. Run the one-time backfill. From klair-misc/, run uv run maint-scripts/backfill_ps_revenue_impact.py once. It reads every monthly S3 JSON from Nov 2024 → Apr 2026 and upserts rows idempotently — safe to re-run if it errors midway.
3. Deploy klair-udm. From klair-udm/, run sam build && sam deploy. This ships the new getPsRevenueImpact AppSync query.
4. Smoke-test the deployed endpoint. Hit the deployed AppSync URL with the x-api-key header against a known reportDate (e.g. 2026-04-30) and confirm the JSON shape matches what the frontend expects.
5. Verify /arr-gap still loads. Open the route and click into the 2025 historical tab inside ARRGapV2 — that tab is the ARRGap v1 component, which we intentionally kept alive.
Until steps 1–3 are done, the /arr-retention-reports page will fail to load PS Revenue Impact in prod (the old /arr-reports-by-bu endpoint is gone).
---
## Test plan
- [ ] CI green on the PR (lint, typecheck, frontend tests, backend tests, klair-udm tests)
- [ ] Reviewer pulls the branch and runs pnpm tsc --noEmit + pnpm lint:pr from klair-client/
- [ ] Reviewer confirms /arr-retention-reports renders without the <PlatinumOrPrimeProgress /> block (only <PlatinumARRTable> should be visible in that section)
- [ ] Reviewer confirms the default selected month on /arr-retention-reports is the last day of the most recently completed UTC month
- [ ] Reviewer confirms /arr-gap still loads and the 2025 historical tab (ARRGap v1) renders
- [ ] After merge: reviewer or release captain runs the 5 manual steps above in order, then re-verifies the PS Revenue Impact section on /arr-retention-reports
---
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>