konnosDashboard
auth-mau.ts153 lines · main
1import { runInProjectDatabase } from '../db/data-plane.js';
2
3import { getProjectTier, TIERS } from './tiers.js';
4
5/**
6 * Briven auth analytics — MAU, DAU, provider breakdown, signup velocity,
7 * and session revocation metrics. Pulled at read time (dashboard hits these
8 * endpoints when the Auth → Usage / Analytics pages load).
9 */
10
11export interface AuthMauStats {
12 /** Distinct active users in the trailing 30 days. */
13 count: number;
14 /** Plan ceiling for the current tier (`TIERS[tier].authMauPerMonth`). */
15 ceiling: number;
16 /** Tier as it currently stands in `projects.tier`. */
17 tier: 'free' | 'pro' | 'team';
18 /** Window start (now - 30 days), ISO-8601. */
19 windowStart: string;
20 /** Window end (now), ISO-8601. */
21 windowEnd: string;
22 /** Fraction `count / ceiling`, clamped to [0, 2]. Soft-cap UI rendering. */
23 usageFraction: number;
24}
25
26export interface AuthAnalyticsOverview {
27 mau: AuthMauStats;
28 dau: { count: number; date: string }[];
29 newSignups: { count: number; date: string }[];
30 totalUsers: number;
31 activeSessions: number;
32}
33
34export interface ProviderBreakdown {
35 email: number;
36 magicLink: number;
37 otp: number;
38 oauth: number;
39 passkey: number;
40}
41
42interface CountRow {
43 count: number | string;
44}
45
46const WINDOW_DAYS = 30;
47
48export async function getAuthMauStats(projectId: string): Promise<AuthMauStats> {
49 const tier = (await getProjectTier(projectId)) ?? 'free';
50 const ceiling = TIERS[tier].authMauPerMonth;
51
52 const now = new Date();
53 const windowStart = new Date(now.getTime() - WINDOW_DAYS * 24 * 60 * 60 * 1000);
54
55 const rows = await runInProjectDatabase<CountRow[]>(projectId, async (tx) => {
56 return (await tx.unsafe(
57 `SELECT COUNT(DISTINCT user_id)::bigint AS count
58 FROM "_briven_auth_sessions"
59 WHERE created_at > now() - interval '${WINDOW_DAYS} days'`,
60 )) as CountRow[];
61 });
62
63 const raw = rows[0]?.count ?? 0;
64 const count = typeof raw === 'string' ? Number.parseInt(raw, 10) : raw;
65 const usageFraction = ceiling > 0 ? Math.min(2, count / ceiling) : 0;
66
67 return {
68 count,
69 ceiling,
70 tier,
71 windowStart: windowStart.toISOString(),
72 windowEnd: now.toISOString(),
73 usageFraction,
74 };
75}
76
77export async function getAuthAnalyticsOverview(projectId: string): Promise<AuthAnalyticsOverview> {
78 const mau = await getAuthMauStats(projectId);
79
80 const [dauRows, signupRows, totalUsersRow, activeSessionsRow] = await runInProjectDatabase<
81 [Array<{ date: string; count: number | string }>, Array<{ date: string; count: number | string }>, CountRow[], CountRow[]]
82 >(projectId, async (tx) => {
83 const dau = (await tx.unsafe(
84 `SELECT DATE(created_at) AS date, COUNT(DISTINCT user_id)::bigint AS count
85 FROM "_briven_auth_sessions"
86 WHERE created_at > now() - interval '30 days'
87 GROUP BY DATE(created_at)
88 ORDER BY date DESC
89 LIMIT 30`,
90 )) as Array<{ date: string; count: number | string }>;
91
92 const signups = (await tx.unsafe(
93 `SELECT DATE(created_at) AS date, COUNT(*)::bigint AS count
94 FROM "_briven_auth_users"
95 WHERE created_at > now() - interval '30 days'
96 GROUP BY DATE(created_at)
97 ORDER BY date DESC
98 LIMIT 30`,
99 )) as Array<{ date: string; count: number | string }>;
100
101 const total = (await tx.unsafe(
102 `SELECT COUNT(*)::bigint AS count FROM "_briven_auth_users"`,
103 )) as CountRow[];
104
105 const sessions = (await tx.unsafe(
106 `SELECT COUNT(*)::bigint AS count FROM "_briven_auth_sessions" WHERE expires_at > now()`,
107 )) as CountRow[];
108
109 return [dau, signups, total, sessions];
110 });
111
112 const parseCount = (v: number | string) => (typeof v === 'string' ? Number.parseInt(v, 10) : v);
113
114 return {
115 mau,
116 dau: dauRows.map((r) => ({ date: r.date, count: parseCount(r.count) })),
117 newSignups: signupRows.map((r) => ({ date: r.date, count: parseCount(r.count) })),
118 totalUsers: parseCount(totalUsersRow[0]?.count ?? 0),
119 activeSessions: parseCount(activeSessionsRow[0]?.count ?? 0),
120 };
121}
122
123export async function getProviderBreakdown(projectId: string): Promise<ProviderBreakdown> {
124 return runInProjectDatabase<ProviderBreakdown>(projectId, async (tx) => {
125 const email = (await tx.unsafe(
126 `SELECT COUNT(*)::bigint AS count FROM "_briven_auth_accounts" WHERE provider_id = 'credential'`,
127 )) as CountRow[];
128 const oauth = (await tx.unsafe(
129 `SELECT COUNT(DISTINCT user_id)::bigint AS count FROM "_briven_auth_accounts" WHERE provider_id != 'credential'`,
130 )) as CountRow[];
131 const passkey = (await tx.unsafe(
132 `SELECT COUNT(DISTINCT user_id)::bigint AS count FROM "_briven_auth_passkeys"`,
133 )) as CountRow[];
134
135 const parseCount = (v: number | string) => (typeof v === 'string' ? Number.parseInt(v, 10) : v);
136
137 // magic-link and OTP are harder to distinguish from email; estimate from audit log.
138 const magic = (await tx.unsafe(
139 `SELECT COUNT(*)::bigint AS count FROM "_briven_auth_audit_log" WHERE action = 'signin' AND metadata->>'provider' = 'magic-link'`,
140 )) as CountRow[];
141 const otp = (await tx.unsafe(
142 `SELECT COUNT(*)::bigint AS count FROM "_briven_auth_audit_log" WHERE action = 'signin' AND metadata->>'provider' = 'otp'`,
143 )) as CountRow[];
144
145 return {
146 email: parseCount(email[0]?.count ?? 0),
147 magicLink: parseCount(magic[0]?.count ?? 0),
148 otp: parseCount(otp[0]?.count ?? 0),
149 oauth: parseCount(oauth[0]?.count ?? 0),
150 passkey: parseCount(passkey[0]?.count ?? 0),
151 };
152 });
153}