CFO Finance Dashboard

Route: /dashboards/finance (manage portal) Status: Option A [Live Apr 2026] · Option B [To Build] Audience: Super-admins acting as CFO; platform-level financial health

Option A — Live (Direct DB queries, no snapshots)

The current implementation (apps/manage/src/app/(manage)/dashboards/finance/page.tsx) runs the following queries on every page load:

Limitations of Option A:

• No historical trend charts (no time-series data; would require scanning all invoices by month)
• Heavy queries on every load — fine for tens of tenants, will degrade beyond ~1,000 subscriptions
• MRR and LLM cost are in different currencies (INR vs USD); gross margin % is not shown because it requires a live forex rate
• Outstanding AR is fetched without a row limit — acceptable now, needs snapshotting at scale

Option B — To Build (Pre-computed snapshots)

Concept

A nightly job (run by the scheduler server) writes a PlatformFinancialSnapshot record capturing all key metrics at a point in time. The CFO dashboard reads from the snapshot table instead of computing live. Historical snapshots enable trend charts.

Proposed Schema

model PlatformFinancialSnapshot {
  id            String   @id @default(cuid())
  date          DateTime @db.Date         // snapshot date (midnight UTC)
 
  // Revenue
  mrrByCurrency Json                      // { INR: 499000, USD: 0 } — smallest unit
  arrByurrency  Json                      // mrrByurrency × 12
  activeSubCount Int
 
  // Collections (calendar month)
  collectedThisMonth  Int                 // sum grossAmount of paid invoices, smallest unit
  totalIssuedThisMonth Int
  collectionRatePct   Int                 // 0-100
 
  // Receivables
  outstandingArTotal  Int                 // sum netPayable pending+overdue, smallest unit
  outstandingCount    Int
  overdueCount        Int
 
  // Costs
  llmCostUsd30d  Float                   // sum costUsd last 30 days
 
  // Breakdowns (JSON blobs — decoded in the UI)
  tierBreakdown      Json                 // [{ tier, label, count, mrr }]
  topRevenueTenants  Json                 // [{ tenantId, name, mrr, planName }] top 10
  topLlmTenants      Json                 // [{ tenantId, name, costUsd }] top 10
  topOutstanding     Json                 // [{ tenantId, name, amount, status, dueAt }] top 10
 
  createdAt     DateTime @default(now())
 
  @@unique([date])
  @@map("platform_financial_snapshot")
}

Snapshot Worker

Add a finance-snapshot job to the scheduler server (apps/servers/scheduler).

Schedule: Daily at 02:00 UTC (after billing cycles complete)

Logic:

1. Compute all metrics (same queries as Option A)
2. Fetch a forex rate for INR/USD from an open API (or use a configurable static rate stored in PlatformSetting)
3. Compute gross margin % = (mrrInrEquivalentUsd − llmCostUsd30d) / mrrInrEquivalentUsd
4. Write PlatformFinancialSnapshot with date = today

Key point: Step 3 requires the forex rate. Recommended approach: store INR_USD_RATE in the PlatformSetting table (key-value store) and update it manually or via a daily fetch from an open rates API (e.g., exchangerate-api.com free tier). The snapshot records the rate used.

Updated Dashboard Behaviour

With snapshots in place, the CFO dashboard changes to:

1. Hero KPIs — still live (same Option A queries, fast on any scale because they use indexed fields)
2. Trend charts — read from last 12 PlatformFinancialSnapshot records; render MRR, LLM cost, and gross margin as line charts over time
3. Plan distribution + top tenants — read from today’s snapshot (fallback to live if snapshot is missing for today)

Implementation Steps

• Add PlatformFinancialSnapshot model to packages/db/prisma/schema.prisma
• Add INR_USD_RATE to PlatformSetting seeds and manage settings UI
• Write financeSnapshot() service function in apps/api/src/services/
• Register a daily ScheduledTask row: type: "finance_snapshot", scheduledFor: 02:00 UTC, recurring
• Add handler in apps/servers/scheduler for finance_snapshot task type
• Update CFO dashboard page to read trend data from snapshot table
• Add 12-month MRR trend line chart (recharts or a simple SVG sparkline)
• Add gross margin % KPI card (only shown when forex rate is configured)
• Add help topic at slug finance-dashboard in the manage help center

Why Not Build Option B Now?

Option A is sufficient for a company with < 200 tenants. At that scale:

• The heaviest query (outstanding AR) returns < 500 rows
• Page load completes in < 200ms
• No historical trend data is needed yet

Revisit when: MRR trend charts are specifically requested by the CFO, or when the outstanding AR query starts taking > 1s (visible in Prisma query logs).