Three related changes to the weekly NHC overspend alerts, all driven by stakeholder feedback.
## 1. Schedule split — Finance ahead of BU/CF
Everything previously went out from a single 14:00 UTC run, so BU leaders saw their numbers at the same moment Finance did. Adds a --scope {all,consolidated,entities,preview} selector so two EventBridge rules can drive the same script at different times.
## 2. Preview send — Finance reviews before anyone else
Ravi asked for the consolidated digest twice: a [PREVIEW] copy to him first, then the real copy with the per-unit emails, so he can correct source data in between.
| Time (Mon) | Scope | Sends |
|---|---|---|
| 13:00 UTC | preview | [PREVIEW] digest → preview_to only |
| 16:00 UTC | all | Real digest → full list, plus every per-BU/CF email |
The refresh in between is deliberately manual and Ravi-owned — if nothing needs correcting, he does nothing. No automated refresh and no "stale data" warning: a warning cannot distinguish "forgot to refresh" from "nothing needed changing" (the common case), so it would fire most weeks and become noise.
Idempotency: both consolidated sends carry entity IS NULL, so a new send_variant column ('preview'/'final') on mart_finance.overspend_alert_runs joins the key. The variant is a property of the send, not the run — per-unit targets always key 'final', so --scope all and --scope entities cannot double-send a unit.
## 3. Exclude the "Customer / Bank Charges" GL bucket
Feedback: *"These are not actual vendor names. BUs do not have control over these… in Crossover this is incorrect. Due to the mapping rule, this was mapped to Customer/bank charges."*
It's a GL account, not a vendor — the NetSuite export writes the account description into the vendor column when no vendor is attached. Bank charges are incurred centrally (no BU can act), and Crossover's $16,682 vs $0 → Unbudgeted line is a mapping artefact, i.e. a false positive.
Excluded estate-wide via EXCLUDE_VENDORS (which shipped empty until now), not per-BU like AWS, because the bucket appears in 14 BUs. Alert-only — the spend stays in the Total NHC position so the header still reconciles against NetSuite.
Measured on live July 2026 QTD data, 246 → 244 alerts:
- Central Finance · OPEX — $58,155 vs $40,000 (large tier)
- Crossover · COGS — $16,682 vs $0 (the reported false positive)
Every position total is byte-identical; Crossover's Total NHC is unchanged.
## Defects caught in review
Worth listing, since several were silent:
1. --scope all --entity <BU> reached a live send. argparse's mutually-exclusive group ignores an explicitly-passed value equal to the argument's own default. Fixed with default=None + post-parse resolution.
2. No test pinned send_variant on the status='sent' ledger write. Deleting the argument left all 181 tests passing — in production the preview would record as 'final' and permanently suppress the real 16:00 digest.
3. Scope-naming failed on the raise path. A broken pipeline sent two identical scope=unknown alerts 3h apart.
4. seed_overspend_recipients.py silently wiped preview_to and notify (whole-row put_item).
5. Runbook smoke-tested on ECS before applying the DDL the smoke test depends on.
6. A test that would hang 300s instead of failing on the regression it guards.
## Verification
- Backend 213 passing (tests/overspend_alerts/, tests/routers/test_overspend_emails_router.py, tests/scripts/test_seed_overspend_recipients.py); frontend suite green; ruff / pyright / eslint / tsc clean.
- Key behaviours confirmed by mutation testing — the exclusion, the send_variant pin, and the per-unit variant each fail when the behaviour is broken.
- A real [PREVIEW] email was delivered end-to-end (to the author only), with send_variant='preview' and exclude_vendors: ['customer bank charges'] recorded in the ledger.
## Already applied to production data stores
- ✅ Redshift: ALTER TABLE mart_finance.overspend_alert_runs ADD COLUMN send_variant VARCHAR(16) — additive, not backfilled (existing rows read as 'final' via COALESCE, which is the contract).
- ✅ DynamoDB: preview_to set on the GLOBAL row.
Neither affects current production behaviour: the deployed code builds its INSERT from an explicit column list that omits send_variant, and has no preview scope.
## Deploy steps after merge (in order)
1. Build + push the klair/scheduled-jobs image — before repointing any rule; an older image rejects --scope and exits 2, sending nothing.
2. Dry-run smoke-test both scopes on ECS, then delete the status='dry_run' ledger rows.
3. Repoint klair-overspend-alerts-weekly-prod → cron(0 13 ? * MON *) with --scope preview.
4. Create klair-overspend-alerts-entities-weekly-prod → cron(0 16 ? * MON *) with --scope all (does not exist yet).
Full commands, rollback, and the weekly cycle are in docs/superpowers/overspend-alerts-deploy-runbook.md.
⚠️ Testing note: this feature emails the real Finance/BU/CF distribution lists and there is no staging SES identity. Never run the cron without --dry-run.
## Screenshot/Test Mail
- Preview email setting:
<img width="1876" height="456" alt="image" src="https://github.com/user-attachments/assets/54c747d7-1a09-469c-97bf-ff431e2dc963" />
- Mail that gets sent (verified and approved by stakeholder (Ravi)) - [DevFactory Mail - [PREVIEW] [Klair] NHC Overspend Alerts — 244 vendor lines over budget (July 2026, QTD).pdf](https://github.com/user-attachments/files/30532357/DevFactory.Mail.-.PREVIEW.Klair.NHC.Overspend.Alerts.244.vendor.lines.over.budget.July.2026.QTD.pdf)
🤖 Generated with [Claude Code](https://claude.com/claude-code)