konnosDashboard
admin-revenue.ts227 lines · main
1import { desc, inArray, isNull, sql } from 'drizzle-orm';
2
3import { getDb } from '../db/client.js';
4import { organizations, projects, subscriptions, usageEvents } from '../db/schema.js';
5import { env } from '../env.js';
6
7/**
8 * DEEP admin revenue. CONTRACT with the web revenue page. Every number comes
9 * from REAL tables (subscriptions + usage_events); MRR is null until live
10 * payments flow through Mavi Pay (Polar). meteredUsage + monthlyTimeline are
11 * the numbers that WILL bill once the payment layer is wired — this is the
12 * honest "what's about to be charged" view, not fabricated revenue.
13 */
14export interface AdminRevenue {
15 connected: boolean;
16 currency: 'EUR';
17 mrr: number | null;
18 planMix: { free: number; pro: number; team: number };
19 activeSubscriptions: Array<{
20 orgId: string;
21 orgName: string | null;
22 tier: string;
23 status: string;
24 since: string | null;
25 currentPeriodEnd: string | null;
26 }>;
27 meteredUsage: Array<{
28 metric: string;
29 period: string;
30 quantity: number;
31 unit: string;
32 pushStatus: string;
33 }>;
34 monthlyTimeline: Array<{ month: string; invocations: number; storageRows: number }>;
35 note: string;
36}
37
38const NOTE =
39 'live payment capture runs through Mavi Pay, not yet connected — these are the metered numbers that will bill once it is.';
40
41/** Human unit label per usage metric — for the "what will bill" table. */
42function unitFor(metric: string): string {
43 switch (metric) {
44 case 'invocations':
45 return 'invocation';
46 case 'storage_bytes':
47 return 'byte';
48 case 'connection_seconds':
49 return 'second';
50 case 'auth_mau':
51 return 'monthly-active-user';
52 case 'auth_sso_connections':
53 return 'sso-connection';
54 case 'auth_sso_signins':
55 return 'sso-sign-in';
56 default:
57 return 'unit';
58 }
59}
60
61/** Mavi Pay (Polar) is "connected" only when a token AND a product are set. */
62function isPaymentConnected(): boolean {
63 return Boolean(
64 env.BRIVEN_POLAR_ACCESS_TOKEN &&
65 (env.BRIVEN_POLAR_PRO_PRODUCT_ID || env.BRIVEN_POLAR_TEAM_PRODUCT_ID),
66 );
67}
68
69/** Live count of non-deleted projects grouped by tier. */
70async function planMixFromProjects(): Promise<{ free: number; pro: number; team: number }> {
71 const db = getDb();
72 const rows = await db
73 .select({ tier: projects.tier, count: sql<number>`count(*)::int` })
74 .from(projects)
75 .where(isNull(projects.deletedAt))
76 .groupBy(projects.tier);
77 const mix = { free: 0, pro: 0, team: 0 };
78 for (const r of rows) {
79 if (r.tier === 'free' || r.tier === 'pro' || r.tier === 'team') mix[r.tier] = r.count;
80 }
81 return mix;
82}
83
84export async function getAdminRevenue(): Promise<AdminRevenue> {
85 const db = getDb();
86
87 // Active subscriptions (anything not canceled), joined to org name.
88 const subRows = await db
89 .select({
90 orgId: subscriptions.orgId,
91 tier: subscriptions.tier,
92 status: subscriptions.status,
93 since: subscriptions.createdAt,
94 currentPeriodEnd: subscriptions.currentPeriodEnd,
95 })
96 .from(subscriptions)
97 .orderBy(desc(subscriptions.createdAt));
98 const activeSubs = subRows.filter((s) => s.status !== 'canceled');
99
100 const orgIds = [...new Set(activeSubs.map((s) => s.orgId))];
101 const orgNameById = new Map<string, string>();
102 if (orgIds.length > 0) {
103 const orgRows = await db
104 .select({ id: organizations.id, name: organizations.name })
105 .from(organizations)
106 .where(inArray(organizations.id, orgIds));
107 for (const o of orgRows) orgNameById.set(o.id, o.name);
108 }
109
110 // Metered usage — the last 100 rows the meter-push worker tracks. This is
111 // literally "what WILL bill" once Mavi Pay is connected.
112 const usageRows = await db
113 .select({
114 metric: usageEvents.metric,
115 periodStart: usageEvents.periodStart,
116 value: usageEvents.value,
117 pushStatus: usageEvents.polarPushStatus,
118 })
119 .from(usageEvents)
120 .orderBy(desc(usageEvents.periodStart))
121 .limit(100);
122
123 const planMix = await planMixFromProjects();
124
125 // MRR = active paid subscriptions × their tier's published monthly price.
126 // Prices MUST match the pricing page (apps/web/.../pricing-section.tsx):
127 // free €0 · pro €29 · team €99 (approved by Jürgen 2026-06-15). €0 when
128 // there are no paid subs yet; null only when payments aren't connected.
129 const TIER_MRR_EUR: Record<string, number> = { free: 0, pro: 29, team: 99 };
130 const mrrValue = activeSubs.reduce((sum, s) => sum + (TIER_MRR_EUR[s.tier] ?? 0), 0);
131
132 return {
133 connected: isPaymentConnected(),
134 currency: 'EUR',
135 mrr: isPaymentConnected() ? mrrValue : null,
136 planMix,
137 // Null-tolerant mapping throughout: the LIVE table can predate the
138 // schema's notNull constraints (migrations don't run on deploy), and one
139 // legacy null must degrade to a placeholder — never crash the cockpit.
140 activeSubscriptions: activeSubs.map((s) => ({
141 orgId: s.orgId,
142 orgName: orgNameById.get(s.orgId) ?? null,
143 tier: s.tier ?? 'free',
144 status: s.status ?? 'active',
145 since: s.since ? s.since.toISOString() : null,
146 currentPeriodEnd: s.currentPeriodEnd ? s.currentPeriodEnd.toISOString() : null,
147 })),
148 meteredUsage: usageRows.map((u) => ({
149 metric: u.metric ?? 'unknown',
150 period: u.periodStart ? u.periodStart.toISOString() : '',
151 quantity: Number(u.value) || 0,
152 unit: unitFor(u.metric ?? ''),
153 pushStatus: u.pushStatus ?? 'pending',
154 })),
155 monthlyTimeline: await monthlyUsageTimeline(),
156 note: NOTE,
157 };
158}
159
160/**
161 * usage_events grouped by month for the last 6 months, zero-filled.
162 * invocations = SUM(value) for the 'invocations' metric; storageRows is the
163 * latest storage sample per month (storage is a gauge, so a SUM would be
164 * meaningless — we take MAX in-month as the representative figure).
165 *
166 * GOTCHA: raw drizzle sql`` templates CRASH on JS Date params under Bun —
167 * pass .toISOString() strings + ::timestamptz casts (see function-logs.ts).
168 */
169async function monthlyUsageTimeline(): Promise<
170 Array<{ month: string; invocations: number; storageRows: number }>
171> {
172 const db = getDb();
173 // Start of the month 5 months ago → 6 buckets including the current month.
174 const start = new Date();
175 start.setUTCDate(1);
176 start.setUTCHours(0, 0, 0, 0);
177 start.setUTCMonth(start.getUTCMonth() - 5);
178 const since = start.toISOString();
179
180 const rows = (await db.execute(sql`
181 WITH months AS (
182 SELECT generate_series(
183 date_trunc('month', ${since}::timestamptz),
184 date_trunc('month', now()),
185 interval '1 month'
186 ) AS month
187 ),
188 usage_by_month AS (
189 SELECT
190 date_trunc('month', period_start) AS month,
191 coalesce(sum((value::numeric)) FILTER (WHERE metric = 'invocations'), 0) AS invocations,
192 coalesce(max((value::numeric)) FILTER (WHERE metric = 'storage_bytes'), 0) AS storage_rows
193 FROM usage_events
194 WHERE period_start >= ${since}::timestamptz
195 GROUP BY 1
196 )
197 SELECT
198 months.month AS month,
199 coalesce(usage_by_month.invocations, 0) AS invocations,
200 coalesce(usage_by_month.storage_rows, 0) AS storage_rows
201 FROM months
202 LEFT JOIN usage_by_month ON usage_by_month.month = months.month
203 ORDER BY months.month
204 `)) as Array<{
205 month: string | Date;
206 invocations: number | string;
207 storage_rows: number | string;
208 }>;
209
210 // Some drivers return the result rows directly, others wrap them in
211 // `{ rows }` — accept both, and drop any bucket whose month won't parse
212 // rather than letting toISOString() throw the whole endpoint down.
213 const list = Array.isArray(rows)
214 ? rows
215 : ((rows as unknown as { rows?: typeof rows }).rows ?? []);
216 return list.flatMap((r) => {
217 const d = r.month instanceof Date ? r.month : new Date(r.month);
218 if (Number.isNaN(d.getTime())) return [];
219 return [
220 {
221 month: d.toISOString().slice(0, 7),
222 invocations: Number(r.invocations) || 0,
223 storageRows: Number(r.storage_rows) || 0,
224 },
225 ];
226 });
227}