Skip to content

MRR Dashboard: incremental per-customer Stripe refresh

Context

Services/MrrDashboardService.cs powers the CRM Customers page (/Customer/MrrTransactions). Today it rebuilds the entire payload in one shot every 10 minutes via Raklet.WebJobs.Secondary/CrmDashboard/MrrDashboardWarmFunctions.cs. Three follow-ups in docs/payments/mrr-dashboard-prewarm.md were explicitly deferred:

  1. Per-customer Stripe data caching with longer TTL (incremental refresh).
  2. Pre-warming for days ≠ 30.
  3. Webhook-driven cache invalidation.

This plan covers (1) — the incremental refresh — because the all-or-nothing rebuild has shown two problems in production after the warm job shipped:

  • One bad Stripe customer or revoked Connect account causes the warm cycle to re-query Stripe and re-throw on every 10-minute tick. The previous PR (this branch's predecessor, fix/crm-mrr-stale-stripe-cleanup) added a 24h denylist that hides the noise, but the denylist is a band-aid: the data for those orgs is still missing from the payload, and a customer whose Stripe state legitimately changes within 24h won't be picked up until the denylist expires.
  • The cap CrmMrrDashboard-MaxLifetimeCustomers=1000 is enforced every cycle, so any customer past the cap re-falls-back to local TotalProfit repeatedly even though their Stripe LTV has never changed. A per-customer cache with a much longer TTL than the dashboard payload makes the cap irrelevant for steady-state — only newly-seen customers need a Stripe round-trip.

Goal: every customer row on the dashboard reads from a per-customer cache entry that is refreshed by a background queue, one customer at a time, on a schedule that decouples from the user-facing dashboard rebuild.

Approach

Three pieces, each fits in the existing patterns.

1. Per-customer Redis entries

One key per Stripe customer (and one per Connect account, same shape):

crm-mrr-customer:{stripeCustomerId}      → MrrCustomerCacheEntry { LifetimeRevenue, LastFetchedAtUtc, LastError, LastErrorAtUtc, CapHit }
crm-mrr-connect:{stripeAccountId}        → MrrConnectCacheEntry { ApplicationFeeAmount, TotalCollectedAmount, TransactionCount, LastFetchedAtUtc, LastError, ... }

TTL: 7 days. Entries are written whenever the background worker fetches them; the warm cycle reads but never writes. LastError/LastErrorAtUtc replace the current denylist — a customer that 404s gets LastError="resource_missing" and is skipped by the worker for a backoff window (e.g. 24h), but the entry still exists so the warm cycle has something to show.

MrrCustomerCacheEntry and MrrConnectCacheEntry are new POCOs alongside MrrTxCachePayload, serialised via Newtonsoft like the existing payload.

2. New queue-driven worker MrrCustomerRefreshFunctions

Sits next to MrrDashboardWarmFunctions in Raklet.WebJobs.Secondary/CrmDashboard/. Two queue triggers:

  • trigger-mrr-customer-refresh — message body is a MrrCustomerRefreshMsg { StripeCustomerId, Reason }. Handler fetches Stripe invoices for that one customer, writes crm-mrr-customer:{id}. No locks needed since each message is for one customer; concurrent runs across customers are safe.
  • trigger-mrr-connect-refresh — same shape for Connect accounts.

Each handler uses the existing WebJobUtil.IsDeploymentTime guard pattern, the same StripeException classification helpers introduced on the cleanup branch, and on any Stripe-side "this is permanently broken" error writes the failure into the cache entry instead of re-throwing.

3. Scheduler that enqueues refresh work

Two ways to feed the queue:

  • Periodic sweep (Scheduled5mins): a single dispatcher entry trigger-mrr-customer-refresh-sweep runs every 5 minutes. It loads all Organisation rows with a non-null StripeCustomerId, ranks them by (IsLikelyPaying desc, LastFetchedAtUtc asc nulls first, LifetimeRevenue desc), and enqueues the top N (config CrmMrrDashboard-RefreshBatchSize, default 100) as individual trigger-mrr-customer-refresh messages. At 100/sweep × 6 sweeps/hour = 600 customers refreshed/hour; a 6000-customer base completes a full refresh roughly every 10 hours, paying ones every cycle.
  • On-demand (later): a Stripe webhook handler (POST /CustomerWebhook/InvalidateMrrCache) can enqueue a single trigger-mrr-customer-refresh for the affected customer when an invoice / subscription event fires. Out of scope for this PR — webhook delivery infrastructure decision pending.

The dispatcher itself is cheap: a single DB read + N queue inserts, no Stripe calls.

4. MrrDashboardService.BuildPayload reads from per-customer cache

GetCustomerLifetimeRevenueMap becomes a Redis read instead of a Stripe loop. For each StripeCustomerId in the org list:

  1. Read crm-mrr-customer:{id}.
  2. If present and LastError is null → use LifetimeRevenue.
  3. If present and LastError is set (recent failure) → use 0 or fall back to local TotalProfit and surface _lifetimeRevenueStatus = "stripeError" on the row.
  4. If absent → fall back to local TotalProfit, enqueue a one-off trigger-mrr-customer-refresh for that customer so the next dashboard load is warm.

Net effect: the warm cycle does zero Stripe calls in steady state. The same change applies to GetConnectAccountMetricsMap.

Files to modify

New files

  • Services/MrrCustomerCacheEntry.cs + Services/MrrConnectCacheEntry.cs — POCOs.
  • Raklet.WebJobs.Secondary/CrmDashboard/MrrCustomerRefreshFunctions.cs — three handlers (TriggerMrrCustomerRefresh, TriggerMrrConnectRefresh, TriggerMrrCustomerRefreshSweep).
  • Models/Queues/MrrCustomerRefreshMsg.cs (and MrrConnectRefreshMsg.cs) — queue payloads.

Edited files

  • Services/MrrDashboardService.cs — replace inline Stripe loops with cache reads; remove the stale-denylist code from the cleanup branch (the new cache entries supersede it).
  • Raklet.WebJobs.Scheduled5mins/Functions.cs — add {"trigger-mrr-customer-refresh-sweep", new[] { 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55 }}.
  • Services/Services.csproj + Raklet.WebJobs.Secondary/<csproj> + Models/<csproj> — register new .cs files (per CLAUDE.md non-SDK rule).

Cache key + TTL design

Key TTL Writer Reader
crm-mrr-customer:{id} 7 days refresh worker warm cycle + on-demand controller fallback
crm-mrr-connect:{id} 7 days refresh worker warm cycle
crm-mrr-summary:days:30 12 minutes warm cycle (unchanged) controller
~~crm-mrr-stale-customer-ids~~ (removed) — superseded by LastError on per-customer entry
~~crm-mrr-stale-connect-account-ids~~ (removed) — same

A 7-day TTL handles the case where a customer is deleted and we want to eventually forget them; once the worker stops succeeding, the entry expires naturally.

Verification

After implementing:

  1. Local Services build: scripts/dev/build-fast.ps1 -Projects Services.
  2. Controller cold path: with Redis empty, https://crm.raklet.local/Customer/MrrTransactionSummary?days=30&bypassCache=true returns within request timeout, every row's LifetimeRevenue falls back to local TotalProfit, and the queue contains ~N trigger-mrr-customer-refresh messages (one per org with a StripeCustomerId).
  3. Refresh worker: start Secondary WebJob console; watch entries get written to crm-mrr-customer:*; confirm a deliberate cus_doesnotexist enqueue records LastError="resource_missing" instead of throwing.
  4. Steady state: bypass cache twice in a row 1 minute apart; second call should perform zero Stripe API calls (verify via Stripe dashboard request log).
  5. Failure modes: stop Redis → existing fallback in CacheService.Get returns null → controller falls back to local TotalProfit, dashboard still renders (slower, no Stripe LTV). Stop the refresh WebJob → existing entries serve until they expire (7 days).

Out of scope (defer further)

  • The Stripe webhook → /CustomerWebhook/InvalidateMrrCache endpoint for event-driven freshness. Add when product needs sub-hour LTV updates.
  • Pre-warming for days ≠ 30. Add only if dashboard analytics show meaningful usage of other ranges.
  • Cosmos / SQL persistence of per-customer LTV (would survive Redis flushes and let us query historical LTV). Premature; 7-day Redis TTL + sweep refresh is enough.

Risks

  • Sweep DB read becomes expensive at scale. Mitigation: project only Id, StripeCustomerId, StripePlanId, TotalProfit, LastFetchedAt (the latter requires a new Redis index or a small MrrCustomerRefreshHint table — decide at implementation time). Current org count is well under what a simple SELECT … WHERE StripeCustomerId IS NOT NULL can handle in a 5-minute window.
  • Queue depth runaway if a sweep enqueues faster than handlers drain. Mitigation: dispatcher checks queue depth before enqueueing and skips if above CrmMrrDashboard-RefreshQueueMaxDepth (default 500). Same pattern as EmailFunctions.TriggerEmailStats.
  • Cache and DB drift if a customer's Stripe state changes but the entry is still warm. Acceptable at 7-day TTL with sweep-driven refresh prioritising oldest first. Webhook invalidation fixes this fully, but the dashboard is internal-only so multi-hour staleness on a deleted customer is fine.