Skip to content

MRR Dashboard — Decision Doc (supersedes prior draft)

Branch: feat/mrr-snapshot-plan Author: gercek (CEO) + Claude Date: 2026-05-20 Status: Draft — replaces the Table-Storage-from-scratch plan after discovering prior work + review findings

Why this doc was rewritten

Original draft proposed building a new Azure Table Storage mirror of Stripe state from scratch. Two findings forced a rewrite:

  1. The Redis-based incremental-refresh architecture is already shipped — PR #13728 (merged 2026-05-15), 580 LoC across 11 files. The pattern we wanted to build already exists.
  2. The Codex + plan-eng reviews found a fatal precision bug in the Table draft (Microsoft.WindowsAzure.Storage 9.3.2 has no EdmType.Decimal — every monetary decimal would silently round-trip through Double). Even ignoring finding #1, the schema as drafted was broken.

Net: don't build Table Storage. Extend the existing Redis pattern to cover the one path it didn't.

What's already in production (PR #13728)

Component File What it does
Per-customer cache POCO Services/MrrCustomerCacheEntry.cs Redis-stored: LifetimeRevenue, LastFetchedAtUtc, LastError, CapHit
Per-Connect-account cache POCO Services/MrrConnectCacheEntry.cs Connect metrics
Queue worker Raklet.WebJobs.Secondary/CrmDashboard/MrrCustomerRefreshFunctions.cs 3 handlers: per-customer, per-Connect, sweep dispatcher
Paying-first ranking Same file, EnqueueCustomerRefreshes OrderByDescending(StripePlanId != "" || LifetimeRevenue > 0).ThenBy(LastFetchedAtUtc).ThenByDescending(LifetimeRevenue)
Sweep schedule Raklet.WebJobs.Scheduled5mins/Functions.cs 12×/hour
Queue-depth throttle MrrCustomerRefreshFunctions.cs line 84 Skips sweep if >500 messages pending
Reader integration MrrDashboardService.cs (parts of the 488-line refactor) LTV + Connect read from cache, fall back to local TotalProfit on miss

This addresses the lifetime revenue / Connect metrics path. It does not address subscription / add-on / product lookups.

The remaining gap (cause of current GlitchTip warnings)

Services/MrrDashboardService.cs:386-505 — GetSubscriptionAggregateMaps:

var allDistinctCustomerIds = organisations.Select(x => x.StripeCustomerId)...
var maxSubscriptionLookups = GetConfigInt("CrmMrrDashboard-MaxSubscriptionLookups", 1000);
var customerIds = allDistinctCustomerIds.Take(maxSubscriptionLookups).ToList();
...
Parallel.ForEach(customerIds, parallelOptions, customerId =>
{
    var subscription = stripeService.GetSubscriptionPlan(customerId, includePassives: true);
    var addOnSubscriptions = stripeService.GetSubscriptionAddons(customerId, includePassives: true);
    ...
});

Still uncached, still live-Stripe, still arbitrary .Take(1000). 4,384 of 5,384 customers silently drop subscription/add-on/product columns on every dashboard render. This is what's emitting RAKLET-4ET.

The lifetime ApplicationFees aggregation in GetLifetimeApplicationFeeAggregates (line 745) is a separate problem: capped at 50,000 fees globally, undercounting LifetimeTransactionRevenue across all accounts (RAKLET-4ES).

Proposed wedge — extend the existing pattern

Wedge A: Cache subscription / add-on / product data

1. Add fields to MrrCustomerCacheEntry: 

public string SubscriptionStatus { get; set; }
public string PlanLookupKey { get; set; }
public decimal BaseSubscriptionMrr { get; set; }
public decimal AddOnMrr { get; set; }
public string SubscribedProductsCsv { get; set; }   // CSV of product names
public string PrimaryCurrencyCode { get; set; }     // ISO-4217; reject cross-currency aggregation
(Decimal is fine here — Redis serialization uses Newtonsoft JSON which preserves decimal as JSON number.)

2. Extend MrrDashboardService.RefreshCustomerCache to also fetch GetSubscriptionPlan + GetSubscriptionAddons and persist into the same cache entry. Same Stripe calls that currently happen live; just now they happen in the queue worker and the result is persisted.

3. Change GetSubscriptionAggregateMaps to read from the cache rather than running the live Parallel.ForEach Stripe loop. Cache miss path: fall back to live Stripe for that customer only (not 1000-cap), and enqueue a refresh. After ~1 hour of warm-up, miss rate → near zero.

4. Delete CrmMrrDashboard-MaxSubscriptionLookups cap and the truncation warning.

Wedge B (separate PR, optional, fixes the second warning)

Pagination via starting_after=LastFeeIdSeen for GetLifetimeApplicationFeeAggregates:

  • Add to MrrConnectCacheEntry: LifetimeTransactionRevenueCents (Int64 — avoids decimal/double roundtrip on a hot summed value), LastFeeIdSeen
  • Each refresh cycle pages only fees with created > last seen, accumulates into the cents counter
  • 50k cap is removed structurally; refresh is constant-time per cycle regardless of total fee count

Plan-eng's recommendation here is well-grounded — Stripe IDs are time-ordered so starting_after is a stable cursor.

Reconciliation of review findings

Review finding Applies to extended Redis path? Action
decimal round-trips through Double (Codex #1, plan-eng) No — Redis serializes via Newtonsoft, preserves decimal Skip (was a Table-only bug)
Seed source must be Organisations not customers.list (Codex #2) N/A — no seed needed; existing sweep already iterates Organisations Skip
Mixed RowKey schema (Codex #3) N/A — Redis is flat keyspace Skip
Backfill stalest-first starvation (Codex #4) Partially N/A — existing sweep has 24h error-skip; but no exponential back-off Add NextEligibleSyncUtc field; Wedge A
Multi-instance seed race (plan-eng) N/A — no seed step in extended plan Skip
Missing PrimaryCurrencyCode (plan-eng) Applies — even existing cache lacks it Add to entry; Wedge A
Pure function for testability (plan-eng) Applies — extract MrrCustomerCacheBuilder.Build(stripeSub, addons) → MrrCustomerCacheEntry Wedge A
Dry-run switch (plan-eng) Applies — flag MrrSnapshot-DryRun=true for read-only validation before flipping reader path Wedge A
Structured per-cycle log line (plan-eng) Applies — existing sweep logs minimal; emit rows_refreshed, rows_skipped, stripe_429s Wedge A
LastFeeIdSeen cursor for ApplicationFees (plan-eng) Applies to Wedge B Wedge B

Scope — Wedge A (this PR)

In scope: - ✅ Extend MrrCustomerCacheEntry with 6 new fields (subscription columns + currency) - ✅ Add NextEligibleSyncUtc for exponential back-off on errors - ✅ Extract pure MrrCustomerCacheBuilder.Build(...) function - ✅ Extend MrrDashboardService.RefreshCustomerCache to fetch + persist subscription/addon - ✅ Switch GetSubscriptionAggregateMaps to read from cache (with single-customer live-fallback on miss) - ✅ Delete CrmMrrDashboard-MaxSubscriptionLookups cap + truncation warning - ✅ Structured log line per sweep cycle - ✅ MrrSnapshot-DryRun flag for read-only validation - ✅ Unit test for the pure builder; integration test against in-memory cache

Explicit non-goals: - ❌ ApplicationFee aggregation fix (deferred to Wedge B) - ❌ Stripe webhook handlers - ❌ Cache TTL changes - ❌ Multi-tenant sharding - ❌ Migration off Microsoft.WindowsAzure.Storage 9.3.2

Estimated size: ~150 LoC across 4-5 files. Significantly smaller than original draft (~315 LoC) because most of the infrastructure already exists.

Rollout

  1. Land Wedge A with MrrSnapshot-DryRun=true — sweep populates new fields but reader still uses live Stripe path
  2. Watch GlitchTip + the new structured log line for 24-48h; verify cache convergence
  3. Flip dry-run off — reader switches to cache, cap removed
  4. Watch for 1 week; if numbers match live within tolerance, delete the live Parallel.ForEach path in a follow-up
  5. Start Wedge B in parallel for the second warning

Open questions

  1. Should Wedge A and Wedge B be one PR or two? (Recommend two — Wedge B is independent and the deploys are safer when changes are bounded.)
  2. Is there appetite for the Stripe webhook integration as Wedge C, or do we wait until product asks for sub-hour freshness?
  3. The Decimal.Round(.., 2) precision question from the original plan: plan-eng recommended Int64 cents. For Redis-stored Wedge A fields, decimal works correctly — keep it. For Wedge B's LifetimeTransactionRevenueCents (a summed value across thousands of rows), use Int64 cents per plan-eng. Inconsistent but defensible.