konnosDashboard
mavi-pay.ts336 lines · main
1/**
2 * Mavi Pay (backed by Polar.sh) — the superadmin billing seam.
3 *
4 * Phase 3 wires REAL numbers from the control-plane `subscriptions` table,
5 * which the Polar webhook keeps in sync (upsertSubscriptionFromPolar). That
6 * local table — not a live Polar call — is the source of truth for who is
7 * subscribed, so the cockpit renders instantly without hitting Polar on every
8 * load.
9 *
10 * Honesty rules (unchanged from Phase 2): every number is REAL or explicitly
11 * null. The web layer renders "—" for nulls rather than a fake zero. Only MRR
12 * can degrade to null — it needs the per-tier monthly price, which lives on
13 * the Polar products. With no Polar token / product ids configured (e.g. this
14 * dev worktree) the price lookup returns null and MRR shows "—"; the counts,
15 * plan mix, and churn still come back real from the local DB.
16 *
17 * Definitions (documented here so the cockpit and tests agree):
18 * - subscribers : COUNT of subscriptions whose status is NOT 'canceled'
19 * (i.e. 'trialing' | 'active' | 'past_due'). These are the
20 * org-level paid subscriptions the webhook has recorded.
21 * Subscriptions only ever carry a paid tier (pro|team), so
22 * this equals paidSubscribers(planMix).
23 * - planMix.pro / planMix.team : those non-canceled subscriptions grouped by
24 * tier. (Source: subscriptions table.)
25 * - planMix.free: non-deleted projects on the free tier — the Phase-2 free
26 * count source, kept as-is. Free users never have a
27 * subscription row, so the free bucket comes from projects,
28 * not subscriptions. (Source: projects table — heterogeneous
29 * with pro/team on purpose; documented.)
30 * - churn30d : COUNT of subscriptions with status='canceled' whose row was
31 * last updated within the last 30 days. APPROXIMATION: keyed
32 * off updatedAt as the "when it churned" timestamp, so a
33 * later edit to a long-canceled row would re-count it. Good
34 * enough for a glance; a precise churn metric would need a
35 * dedicated cancellation-event log.
36 * - mrr : sum over the non-canceled paid subscriptions of that tier's
37 * monthly price, fetched from the Polar product. null when
38 * Polar isn't configured or the fetch fails — NEVER faked.
39 * - currency : ISO currency code of the MRR figure (from Polar), or null
40 * when MRR is null.
41 */
42import { and, desc, eq, gte, inArray, isNull, sql } from 'drizzle-orm';
43
44import { getDb } from '../../db/client.js';
45import {
46 organizations,
47 projects,
48 projectTier,
49 subscriptions,
50 users,
51 type ProjectTier,
52 type SubscriptionStatus,
53} from '../../db/schema.js';
54import { env } from '../../env.js';
55import { log } from '../../lib/logger.js';
56
57export type PlanMix = Record<ProjectTier, number>;
58
59export interface BillingTotals {
60 /** Count of non-canceled subscriptions (trialing | active | past_due). */
61 subscribers: number | null;
62 /** Monthly recurring revenue in `currency`, or null when Polar is unconfigured / unreachable. */
63 mrr: number | null;
64 /** ISO currency code for `mrr`, or null when mrr is null. */
65 currency: string | null;
66 /** free = non-deleted free-tier projects; pro/team = non-canceled subs by tier. */
67 planMix: PlanMix | null;
68 /** Count of subscriptions canceled (by updatedAt) in the last 30 days. */
69 churn30d: number | null;
70}
71
72/** Statuses that count as an active (non-canceled) subscription. */
73export const ACTIVE_SUB_STATUSES = ['trialing', 'active', 'past_due'] as const;
74
75/** Churn lookback window, in days. */
76export const CHURN_WINDOW_DAYS = 30;
77
78/**
79 * Pure helper: paid subscribers are everything that isn't on the free tier.
80 * Kept separate from the DB query so the rule is unit-testable without a
81 * database. A change here without its test shows up red.
82 */
83export function paidSubscribers(planMix: PlanMix): number {
84 return planMix.pro + planMix.team;
85}
86
87/**
88 * Pure helper: assemble the plan mix from the free-tier project count plus the
89 * per-tier counts of non-canceled subscriptions. Unit-testable in isolation.
90 */
91export function buildPlanMix(
92 freeCount: number,
93 activeSubsByTier: Array<{ tier: ProjectTier; count: number }>,
94): PlanMix {
95 const mix = Object.fromEntries(projectTier.map((t) => [t, 0])) as PlanMix;
96 mix.free = freeCount;
97 for (const row of activeSubsByTier) {
98 if (row.tier === 'pro' || row.tier === 'team') mix[row.tier] = row.count;
99 }
100 return mix;
101}
102
103/**
104 * Pure helper: MRR = (pro subs × pro price) + (team subs × team price). Prices
105 * are per-month, in major currency units. Unit-testable without Polar or a DB.
106 */
107export function computeMrr(
108 counts: { pro: number; team: number },
109 prices: { pro: number; team: number },
110): number {
111 return counts.pro * prices.pro + counts.team * prices.team;
112}
113
114/**
115 * Pure helper: is this subscription a churn within the window? True only when
116 * the sub is canceled AND its row was last updated within `windowDays`.
117 */
118export function isChurnWithinWindow(
119 sub: { status: SubscriptionStatus; updatedAt: Date },
120 now: Date,
121 windowDays: number = CHURN_WINDOW_DAYS,
122): boolean {
123 if (sub.status !== 'canceled') return false;
124 const cutoff = now.getTime() - windowDays * 24 * 60 * 60 * 1000;
125 return sub.updatedAt.getTime() >= cutoff;
126}
127
128/** Pure helper: count churned subs within the window. */
129export function countChurnWithinWindow(
130 subs: Array<{ status: SubscriptionStatus; updatedAt: Date }>,
131 now: Date,
132 windowDays: number = CHURN_WINDOW_DAYS,
133): number {
134 return subs.filter((s) => isChurnWithinWindow(s, now, windowDays)).length;
135}
136
137/* ─── Polar price lookup (the only live Polar call; cached) ──────────────── */
138
139interface TierPrices {
140 pro: number;
141 team: number;
142 currency: string;
143}
144
145interface PriceCacheEntry {
146 value: TierPrices | null;
147 expiresAt: number;
148}
149
150const PRICE_CACHE_TTL_MS = 60 * 60 * 1000; // 1h — the dashboard must not hit Polar every load.
151let priceCache: PriceCacheEntry | null = null;
152
153/** Shape of the subset of the Polar product response we read. */
154interface PolarProductResponse {
155 prices?: Array<{
156 price_amount?: number | null;
157 price_currency?: string | null;
158 recurring_interval?: string | null;
159 is_archived?: boolean | null;
160 }>;
161}
162
163/**
164 * Fetch one Polar product's recurring MONTHLY price. Returns the amount in
165 * major units (Polar gives cents) + ISO currency, or null on any failure /
166 * missing monthly price. Callers treat null as "MRR unknown".
167 */
168async function fetchTierMonthlyPrice(
169 productId: string,
170): Promise<{ amount: number; currency: string } | null> {
171 const res = await fetch(`${env.BRIVEN_POLAR_API_BASE}/v1/products/${productId}`, {
172 headers: { authorization: `Bearer ${env.BRIVEN_POLAR_ACCESS_TOKEN}` },
173 signal: AbortSignal.timeout(6000),
174 });
175 if (!res.ok) {
176 log.warn('mavi_pay_price_fetch_failed', { productId, status: res.status });
177 return null;
178 }
179 const data = (await res.json()) as PolarProductResponse;
180 const price = (data.prices ?? []).find(
181 (p) =>
182 !p.is_archived &&
183 p.recurring_interval === 'month' &&
184 typeof p.price_amount === 'number',
185 );
186 if (!price || typeof price.price_amount !== 'number') return null;
187 return {
188 amount: price.price_amount / 100,
189 currency: (price.price_currency ?? 'usd').toUpperCase(),
190 };
191}
192
193/**
194 * Get the pro + team monthly prices, cached in-memory for 1h. Returns null
195 * (degrade gracefully) when the token / product ids aren't set or either fetch
196 * fails. NEVER throws into the totals path.
197 */
198async function getTierMonthlyPrices(): Promise<TierPrices | null> {
199 if (priceCache && priceCache.expiresAt > Date.now()) return priceCache.value;
200
201 const value = await loadTierMonthlyPrices();
202 priceCache = { value, expiresAt: Date.now() + PRICE_CACHE_TTL_MS };
203 return value;
204}
205
206async function loadTierMonthlyPrices(): Promise<TierPrices | null> {
207 const proId = env.BRIVEN_POLAR_PRO_PRODUCT_ID;
208 const teamId = env.BRIVEN_POLAR_TEAM_PRODUCT_ID;
209 // Degrade to null (UI shows "—") rather than throwing when Polar isn't wired
210 // in this environment.
211 if (!env.BRIVEN_POLAR_ACCESS_TOKEN || !proId || !teamId) return null;
212 try {
213 const [pro, team] = await Promise.all([
214 fetchTierMonthlyPrice(proId),
215 fetchTierMonthlyPrice(teamId),
216 ]);
217 if (!pro || !team) return null;
218 if (pro.currency !== team.currency) {
219 // Mixed-currency products can't be summed into one MRR figure honestly.
220 log.warn('mavi_pay_price_currency_mismatch', {
221 pro: pro.currency,
222 team: team.currency,
223 });
224 return null;
225 }
226 return { pro: pro.amount, team: team.amount, currency: pro.currency };
227 } catch (err) {
228 log.warn('mavi_pay_price_load_failed', {
229 message: err instanceof Error ? err.message : String(err),
230 });
231 return null;
232 }
233}
234
235/** Test seam: clear the in-memory price cache. */
236export function __resetPriceCacheForTests(): void {
237 priceCache = null;
238}
239
240/* ─── public API ────────────────────────────────────────────────────────── */
241
242export async function getBillingTotals(): Promise<BillingTotals> {
243 const db = getDb();
244
245 // (1) Non-canceled subscriptions grouped by tier — the real subscriber base.
246 const subRows = await db
247 .select({ tier: subscriptions.tier, count: sql<number>`count(*)::int` })
248 .from(subscriptions)
249 .where(inArray(subscriptions.status, [...ACTIVE_SUB_STATUSES]))
250 .groupBy(subscriptions.tier);
251
252 // (2) Free bucket: non-deleted projects on the free tier (Phase-2 source).
253 const [freeRow] = await db
254 .select({ count: sql<number>`count(*)::int` })
255 .from(projects)
256 .where(and(isNull(projects.deletedAt), eq(projects.tier, 'free')));
257 const freeCount = freeRow?.count ?? 0;
258
259 // (3) Churn: subscriptions canceled (by updatedAt) within the window.
260 const since = new Date(Date.now() - CHURN_WINDOW_DAYS * 24 * 60 * 60 * 1000);
261 const [churnRow] = await db
262 .select({ count: sql<number>`count(*)::int` })
263 .from(subscriptions)
264 .where(and(eq(subscriptions.status, 'canceled'), gte(subscriptions.updatedAt, since)));
265 const churn30d = churnRow?.count ?? 0;
266
267 const planMix = buildPlanMix(freeCount, subRows);
268
269 // (4) MRR — the only number that can degrade to null (needs Polar prices).
270 const prices = await getTierMonthlyPrices();
271 let mrr: number | null = null;
272 let currency: string | null = null;
273 if (prices) {
274 mrr = computeMrr({ pro: planMix.pro, team: planMix.team }, prices);
275 currency = prices.currency;
276 }
277
278 return {
279 subscribers: paidSubscribers(planMix),
280 mrr,
281 currency,
282 planMix,
283 churn30d,
284 };
285}
286
287export interface SubscriberRow {
288 orgId: string;
289 orgName: string;
290 /**
291 * Owner's email — operator-only triage. The cockpit is the admin's own
292 * back-office (not the public site), so showing the owner email for triage
293 * is allowed; the UI keeps it secondary to the org name.
294 */
295 ownerEmail: string | null;
296 tier: ProjectTier;
297 status: SubscriptionStatus;
298 /** Period end / next renewal, ISO string or null. */
299 currentPeriodEnd: string | null;
300 /** When the subscription was first recorded (createdAt), ISO string. */
301 since: string;
302}
303
304/**
305 * The non-canceled subscriber list for the cockpit table — joins subscriptions
306 * → organizations → owner user. Matches the `subscribers` total: only
307 * non-canceled subs (trialing | active | past_due), newest first.
308 */
309export async function listSubscribers(): Promise<SubscriberRow[]> {
310 const db = getDb();
311 const rows = await db
312 .select({
313 orgId: subscriptions.orgId,
314 orgName: organizations.name,
315 ownerEmail: users.email,
316 tier: subscriptions.tier,
317 status: subscriptions.status,
318 currentPeriodEnd: subscriptions.currentPeriodEnd,
319 since: subscriptions.createdAt,
320 })
321 .from(subscriptions)
322 .innerJoin(organizations, eq(organizations.id, subscriptions.orgId))
323 .leftJoin(users, eq(users.id, organizations.createdBy))
324 .where(inArray(subscriptions.status, [...ACTIVE_SUB_STATUSES]))
325 .orderBy(desc(subscriptions.createdAt));
326
327 return rows.map((r) => ({
328 orgId: r.orgId,
329 orgName: r.orgName,
330 ownerEmail: r.ownerEmail ?? null,
331 tier: r.tier,
332 status: r.status,
333 currentPeriodEnd: r.currentPeriodEnd ? r.currentPeriodEnd.toISOString() : null,
334 since: r.since.toISOString(),
335 }));
336}