Run: csm-portfolio-snapshot

• branch: claude/csm-portfolio-snapshot-pipeline-tfrbjm
• pr: #514

Stub: CSM dashboard — daily portfolio snapshot (trend delta)

• feature-slug: csm-portfolio-snapshot
• epic: csm-dashboard-data
• personas: CSM
• initiative: Build the Bridge / objective: Q2 2026 Objective 3 — Validate Technical Infrastructure & Payout Flow
• depends-on: csm-activation-rollup
• sequence: 3 of 5

Problem

The ActivationStatusTable shows changeVsYesterday (positive = more accounts at risk than yesterday) — a day-over-day delta that is impossible to compute from live state alone: there is no record of what the portfolio looked like yesterday. Without a stored history the trend column can only ever be mock.

Proposed change

• Define a daily portfolio-snapshot model: per CSM (and region) per day, persist the key rollup metrics from csm-activation-rollup (at-risk count, RAG counts, total accounts, go-lives-next-7).
• Add a daily job (Vercel cron) that writes one snapshot per CSM per day.
• Compute changeVsYesterday as today's at-risk count minus the latest prior snapshot, and wire it into the activation table.

Acceptance criteria (rough)

• One snapshot per CSM per day is persisted with the portfolio rollup metrics.
• changeVsYesterday is the real delta of at-risk accounts vs the previous snapshot.
• First-ever day (no prior snapshot) renders as no-change rather than a misleading spike.
• The snapshot job is idempotent — re-running a day does not double-write.

Out of scope (this feature)

• Longer-range trend charts / history beyond the single day-over-day delta the table needs.
• Snapshotting any metric the activation table doesn't show.
• Backfilling historical snapshots — the trend starts accumulating from go-live.

Notes for Define

• Decide the snapshot grain (per CSM, or per CSM×region) to match exactly what the activation row keys on, so the delta lines up 1:1 with a displayed row.
• Decide the cron cadence/timezone (tenant-local "yesterday" vs UTC) — the delta's meaning depends on it.
• touches: packages/services/src/db/models (new snapshot model), packages/services/src/server (snapshot write + delta read), apps/web/app/api or a cron route (daily job), apps/web/components/dashboard/csm/activation-status-table.tsx (delta column).

Spec: CSM dashboard — daily portfolio snapshot (real trend delta)

• slug: csm-portfolio-snapshot
• personas: CSM
• touches: packages/services/src/db/models/csm-portfolio-snapshot.ts, packages/services/src/db/models/index.ts, packages/services/src/db/services/csm-portfolio/index.ts, packages/services/src/db/services/tenant-setting/index.ts, apps/web/app/api/cron/csm-portfolio-snapshot/route.ts, apps/web/vercel.json
• complexity: standard

Problem

The CSM activation-status table shows a changeVsYesterday column (positive = more accounts at risk than yesterday) for each CSM/region row, but it is impossible to compute from live state alone: nothing records what the portfolio looked like yesterday, so the trend can only ever be mock. csm-activation-rollup (feature 2) deliberately leaves changeVsYesterday absent/zero with a clean seam for exactly this work. This is sequence 3 of 5 in the csm-dashboard-data epic and the piece that makes the activation table's day-over-day signal real. It advances Build the Bridge / Q2 2026 Objective 3 — Validate Technical Infrastructure & Payout Flow by proving the platform can persist and resolve a real per-tenant, per-CSM time series (the first scheduled job in the repo) rather than fabricating a trend.

Proposed change

• Add a CsmPortfolioSnapshot model that persists one row per tenant × CSM × date — the same grain the activation table keys on (the merged csm-activation-rollup emits one row per CSM, with region the CSM's predominant/mode region), so each snapshot lines up 1:1 with a displayed row. Each row stores the portfolio rollup metrics from csm-activation-rollup: atRiskNow, RAG counts (green/amber/red), totalAccounts, and goLivesNext7Days, plus region as a display attribute. The row is keyed by a tenant-local calendar date (snapshotDate, a yyyy-mm-dd day bucket), tenant-isolated like every other model. A unique index on { tenantId, csm, snapshotDate } enforces one snapshot per CSM per day.
• Add a daily Vercel cron (apps/web/vercel.json → crons, a fixed daily UTC time) hitting a new protected route apps/web/app/api/cron/csm-portfolio-snapshot/route.ts. The route authenticates the Vercel cron caller via a CRON_SECRET bearer check (no Clerk session), then iterates every tenant and, per tenant, every CSM (and their regions), reuses the csm-activation-rollup portfolio rollup to compute today's metrics, and upserts one snapshot per row for the tenant-local date.
• Compute the date in the tenant's timezone. snapshotDate is the calendar date in the tenant's configured timezone (the existing timezone tenant setting, default Europe/Brussels) at fire time — so "yesterday" means the tenant's local yesterday, not a UTC boundary that splits a working day.
• Add snapshot read/write to the csm-portfolio service (@sustentus/services/server): a write method the cron calls (idempotent upsert per row/day) and a delta read that, for a given CSM/region/today, returns changeVsYesterday = today's atRiskNow minus the most recent prior snapshot's atRiskNow for that row. "Most recent prior" (not literally yesterday) keeps the delta meaningful across any gap day.
• Wire changeVsYesterday into the activation table. The activation-rollup output gains the real delta from the snapshot read; the ActivationStatusTable renders it (its existing up/down/flat rendering is unchanged). With no prior snapshot for a row (first-ever day, or a newly-owned CSM/region), the delta is 0 / no-change, never a misleading spike.

Acceptance criteria

• A CsmPortfolioSnapshot model persists one row per tenant × CSM × tenant-local date, holding atRiskNow, RAG counts, totalAccounts, goLivesNext7Days, and the CSM's display region; a unique index on { tenantId, csm, snapshotDate } allows exactly one snapshot per CSM per day.
• A daily Vercel cron writes, for each tenant and each of its CSM/region rows, one snapshot per day, computing the metrics from the same csm-activation-rollup rollup the table displays.
• The cron route rejects callers without the valid CRON_SECRET bearer credential and is never reachable via a normal Clerk-authenticated user session.
• changeVsYesterday on the activation table is the real delta of atRiskNow for that CSM/region row versus its most recent prior snapshot — no mock value.
• A row with no prior snapshot (first-ever day, or a newly-owned CSM/region) renders as no-change (0), not a spike.
• The cron is idempotent: re-running a given tenant-local day upserts the same rows rather than double-writing, and the resulting delta is unchanged.
• snapshotDate is the tenant-local calendar date (from the tenant timezone setting, default Europe/Brussels), so the day boundary follows the tenant, not UTC.

Out of scope

• Longer-range trend charts or any history view beyond the single day-over-day delta the activation table needs — only the latest delta is read.
• Snapshotting any metric the activation table / rollup does not surface (no arbitrary portfolio data).
• Backfilling historical snapshots — the trend starts accumulating from first cron run; pre-cron days have no snapshot and render as no-change.
• A per-CSM or per-user override of the snapshot timezone — the tenant timezone setting governs the whole tenant's day boundary this round.
• Realtime/websocket delivery of the delta — the activation table is server-fetched on load.
• Re-computing per-account health or the rollup itself — this reads csm-activation-rollup's portfolio rollup (which reads customer-dashboard-data/project-health-engine); it does not fork either.

Open questions

• none. (Build-order note, now resolved: the upstream chain — csm-portfolio-foundation, customer-dashboard-data/project-health-engine, and csm-activation-rollup — has merged to main. This feature reuses csmPortfolioService's rollup as the canonical source of each CSM's metrics rather than re-deriving the heuristic; the snapshot stores exactly what that rollup produces, and the rollup's changeVsYesterday: 0 seam is filled from the snapshot.)

Build notes: csm-portfolio-snapshot

• commits: feat: csm-portfolio-snapshot — daily snapshot model + cron + real changeVsYesterday delta

What changed

• packages/services/src/db/models/csm-portfolio-snapshot.ts: new CsmPortfolioSnapshot model — one row per tenant × CSM × snapshotDate (the tenant-local yyyy-mm-dd day bucket) holding the rollup metrics (atRiskNow, rag, totalAccounts, goLivesNext7Days) plus the CSM's display region. A unique index { tenantId, csm, snapshotDate } is the idempotency guarantee; a second { tenantId, csm, snapshotDate: -1 } index serves the "latest prior snapshot" delta read. Uses the standard schemaPlugin/softDeletePlugin/tenantPlugin like every model. Exported from db/models/index.ts.
• packages/services/src/db/services/csm-portfolio/index.ts:
  • Extracted the existing per-CSM aggregation into a private rollupGroups(tenantId, scope, csmUserId?) shared by the activation read and the snapshot write (carries csmId + leadIds). getActivationRollup is unchanged in behaviour except it now fills the real changeVsYesterday: today's atRiskNow minus each CSM's most recent snapshot strictly before the tenant-local today (no prior snapshot → 0).
  • latestPriorAtRiskByCsm(...): one aggregation returning, per CSM, the atRiskNow of the latest snapshot before a given date.
  • captureDailySnapshots(tenantId, snapshotDate?): idempotent upsert of one snapshot per CSM for the tenant-local day (keyed { tenantId, csm, snapshotDate }).
  • captureDailySnapshotsForAllTenants(): the cron entry — enumerates non-deleted tenants and captures each on its own tenant-local day; a single bad tenant is logged and skipped.
  • Added a tenantLocalDate(timeZone) helper (Intl.DateTimeFormat("en-CA", { timeZone }) → YYYY-MM-DD) so no new dependency is needed for timezone-aware day bucketing.
• packages/services/src/db/services/tenant-setting/index.ts: getTimezone(tenantId) — the tenant's IANA timezone from the timezone setting, falling back to the registry default (Europe/Brussels).
• apps/web/app/api/cron/csm-portfolio-snapshot/route.ts: new GET cron route. Authenticates via Authorization: Bearer <CRON_SECRET> (the header Vercel cron sends; a normal Clerk session never carries it), then calls captureDailySnapshotsForAllTenants() and returns { ok, tenants, snapshots }.
• apps/web/vercel.json: new — registers the daily cron 0 2 * * * → /api/cron/csm-portfolio-snapshot.

Acceptance criteria status

• CsmPortfolioSnapshot persists one row per tenant × CSM × tenant-local date with atRiskNow, RAG, totalAccounts, goLivesNext7Days, region; unique index { tenantId, csm, snapshotDate }.
• A daily Vercel cron writes one snapshot per CSM per tenant per day, computed from the same csmPortfolioService rollup (rollupGroups) the activation table displays.
• The cron route rejects callers without the valid CRON_SECRET bearer; unreachable from a Clerk session (which never sends that header).
• changeVsYesterday is the real delta of atRiskNow vs the CSM's most recent prior snapshot — the rollup's 0 seam is now filled from the snapshot read.
• A CSM with no prior snapshot renders as no-change (0) — latestPriorAtRiskByCsm omits them and the caller defaults the delta to 0.
• Idempotent: captureDailySnapshots upserts keyed { tenantId, csm, snapshotDate }, so re-running a day overwrites the same rows and the delta is unchanged.
• snapshotDate is the tenant-local calendar date via the tenant timezone setting (default Europe/Brussels).

Verify result

• mechanical checks (format · lint · typecheck · build) run in CI + the Vercel preview, not here. A scoped tsc of the services package + the web app was run locally to catch obvious type errors before pushing.

Notes for review

• Grain reconciled to per-CSM. The spec originally floated a tenant × CSM × region key; the merged csm-activation-rollup actually emits one row per CSM (region = the predominant/mode region). So the snapshot keys on { tenantId, csm, snapshotDate } and stores region as a display attribute — keying on the CSM is what makes the delta line up 1:1 with a displayed row and stays robust if a CSM's mode region drifts day to day. The spec + PR acceptance criteria were reconciled to match.
• No UI change needed. The rollup already exposes changeVsYesterday and the table already renders it; filling the delta inside getActivationRollup lights up the column with no page/component edit.
• Operational prerequisite: CRON_SECRET must be set as a Vercel env var for the cron to authorise (without it the route returns 401 by design). The trend accumulates from first cron run — pre-cron days have no snapshot and render as no-change (backfill is out of scope).
• Snapshots are captured for all tenants including demo tenants, so the demo dashboard's delta is real too.

See whether your go live risk is rising or falling

The trend arrow on your go live status table is now real, not a sample:

• Each row shows how your at-risk account count changed since yesterday — up, down, or steady — so you can tell at a glance whether your portfolio is improving or slipping.
• The comparison uses a daily snapshot taken in your own timezone, so "yesterday" means your yesterday.
• A brand-new row, or your first day, reads as no change rather than a misleading spike — the trend builds up naturally from here.

Live entry: apps/help/app/changelog/2026-06-23-csm-portfolio-snapshot/page.mdx

CSM go live trends are now real, day over day

Who it's for: Customer success managers What shipped: A daily job snapshots each CSM's portfolio, so the go live table's day-over-day trend is real, not sample data. Why it matters: Build the Bridge — validates technical infrastructure (Q2 2026 Objective 3) by proving we can persist a real per-tenant time series.

Dig deeper: https://github.com/sustentus/sustentus/pull/514 · https://help.sustentus.com/changelog/2026-06-23-csm-portfolio-snapshot

Release: csm-portfolio-snapshot

• pr: #514 · merged: yes — squash b8868dd on 2026-06-23
• CI: green (Format, Lint, Typecheck, Audit database, Migrate preview all success; Migrate production skipped as expected off-main). The first run failed at runner startup (transient GitHub Actions infra, ~2s aborts across all jobs); re-running the failed workflows passed green with no code change.
• technical docs: apps/docs/app/technical/deployment/page.mdx — added "Scheduled jobs (cron)" section + CRON_SECRET
• business docs: no business docs impact — makes one already-documented CSM dashboard column (the trend arrow) accurate; no new capability or journey step (consistent with the sibling csm-activation-rollup release)
• release notes: both — changelog entry (apps/help/app/changelog/2026-06-23-csm-portfolio-snapshot/page.mdx) + investor draft in this PR
• deploy: READY — production web (platform.sustentus.com) green on b8868dd; help (help.sustentus.com) and docs (docs.sustentus.com) green on the immediately-following main commits that carry this change (the b8868dd-specific help/docs builds were superseded by sibling #515 merging seconds later — normal Vercel behaviour; live production includes this change)
• sent: investor update sent to 2 recipients on 2026-06-23, after the green production deploy

Review summary

/code-review (high effort) on the feature diff. Findings triaged:

• Read path could crash on a malformed timezone — getTimezone returned the stored value unvalidated, so a padded/typo'd tenant timezone setting would make tenantLocalDate (Intl.DateTimeFormat) throw RangeError and break the whole activation table (the read path was unguarded). Fixed at the source: getTimezone now trims and validates the zone (isValidTimeZone), falling back to the registry default — every caller is safe.
• Soft-delete resurrection (edge) — an archived snapshot row re-captured by the idempotent upsert would stay isDeleted: true and be skipped by the delta read. Fixed defensively: the upsert $set now writes isDeleted: false (no archival path ships, but it's a one-line guarantee).
• Redundant tenant-local-date derivation in the cron loop — captureDailySnapshotsForAllTenants recomputed the date that captureDailySnapshots already derives. Fixed: the loop calls captureDailySnapshots(tenantId) so the derivation lives in one place (also halves the getTimezone reads).
• Cron timeout at scale (accepted) — maxDuration = 60 with a sequential per-tenant loop could time out with many tenants. Accepted for now: tenant count is small, and each tenant is isolated by a try/catch so one failure (or the cutoff) never aborts the rest. Revisit with bounded concurrency / a higher maxDuration when tenant count grows.
• Emptied-portfolio row disappears (accepted) — a CSM whose last active project closes has no rollup row, so a drop to zero at-risk is not shown as a negative delta. This is pre-existing activation-rollup behaviour (rows exist only for CSMs with active leads), not introduced here; out of scope.
• Cleared with no concrete failure: collation vs binary ordering on the yyyy-mm-dd key (identical for fixed-width digits), ObjectId casting in the aggregate (always wrapped), soft-delete/tenant filters in the aggregate (explicit), and the tenant-local day boundary off-by-one (read uses $lt today; verified across +/- UTC offsets).

Acceptance check (vs spec)

• CsmPortfolioSnapshot persists one row per tenant × CSM × tenant-local date with atRiskNow, RAG, totalAccounts, goLivesNext7Days, region; unique index { tenantId, csm, snapshotDate } — model + index verified.
• Daily Vercel cron writes one snapshot per CSM per tenant per day from the same rollupGroups the table uses — vercel.json cron + captureDailySnapshotsForAllTenants.
• Cron route rejects callers without the CRON_SECRET bearer; unreachable from a Clerk session — route returns 401 without the header.
• changeVsYesterday is the real atRiskNow delta vs the most recent prior snapshot — filled in getActivationRollup via latestPriorAtRiskByCsm.
• No prior snapshot → no-change (0) — absent CSMs default to 0.
• Idempotent — upsert keyed { tenantId, csm, snapshotDate }, re-run overwrites the same rows.
• snapshotDate is the tenant-local date from the timezone setting (default Europe/Brussels) — tenantLocalDate + hardened getTimezone.