konnosDashboard
auth-users.ts391 lines · main
1import { ValidationError } from '@briven/shared';
2
3import { runInProjectDatabase } from '../db/data-plane.js';
4
5/**
6 * Briven auth users — list + detail with hard redaction per CLAUDE.md §5.1
7 * and BUILD_PLAN.md §4 (admin user-list response). Full email + raw IP
8 * NEVER leave the api; the dashboard sees only:
9 * - domain hint (chars after `@`)
10 * - first character of `name`
11 * - linked provider ids
12 * - last-seen timestamp (max(sessions.created_at))
13 * - created_at
14 *
15 * Search-by-email is supported as a hashed lookup (caller hashes the
16 * email client-side or server-side; the api compares against a stored
17 * hash — that path lands when we add the search index. v0 just lists).
18 */
19
20const MAX_LIMIT = 200;
21const DEFAULT_LIMIT = 50;
22
23export interface ListUsersOpts {
24 readonly limit?: number;
25 readonly cursor?: string | null;
26}
27
28export interface RedactedUser {
29 /** ULID. Stable identifier safe to surface in dashboard. */
30 id: string;
31 /** Domain portion of email — `gmail.com` from `alice@gmail.com`. */
32 emailDomainHint: string;
33 /** First character of `name`, or null when name is unset. */
34 nameInitial: string | null;
35 /** Linked OAuth + native provider ids (`['google','passkey','email']`). */
36 providerIds: string[];
37 /** ISO-8601 timestamp of the most recent session. Null when never signed in. */
38 lastSeenAt: string | null;
39 /** ISO-8601 timestamp the user row was created. */
40 createdAt: string;
41}
42
43export interface UserListResult {
44 items: RedactedUser[];
45 nextCursor: string | null;
46}
47
48/**
49 * Per-user detail view — used by `/auth/users/[userId]/page.tsx`. Same
50 * redaction rules as the list view: no raw email, no raw IP, name reduced
51 * to its first character. `domainHint` is kept so the admin has *some*
52 * identifying signal beyond the opaque id.
53 */
54export interface UserDetail {
55 id: string;
56 emailDomainHint: string;
57 nameInitial: string | null;
58 createdAt: string;
59 sessions: ReadonlyArray<{
60 id: string;
61 createdAt: string;
62 expiresAt: string;
63 /** First 8 chars of the stored sha-256 ip hash — enough to identify
64 * the device row without exposing the underlying ip. */
65 ipHashHint: string | null;
66 /** First 96 chars of the user agent, single-line, no rewriting. */
67 userAgent: string | null;
68 }>;
69 accounts: ReadonlyArray<{
70 id: string;
71 providerId: string;
72 providerAccountId: string;
73 createdAt: string;
74 }>;
75 audit: ReadonlyArray<{
76 id: string;
77 action: string;
78 occurredAt: string;
79 metadata: Record<string, unknown>;
80 }>;
81}
82
83interface RawUserJoinRow {
84 id: string;
85 email: string;
86 name: string | null;
87 created_at: Date;
88 last_seen_at: Date | null;
89 provider_ids: string[] | null;
90}
91
92interface RawUserRow {
93 id: string;
94 email: string;
95 name: string | null;
96 created_at: Date;
97}
98
99interface RawSessionRow {
100 id: string;
101 created_at: Date;
102 expires_at: Date;
103 // Better-Auth session column (S2.1b). IP tracking is disabled, so null.
104 ip_address: string | null;
105 user_agent: string | null;
106}
107
108interface RawAccountRow {
109 id: string;
110 provider_id: string;
111 // Better-Auth's natural account key (was provider_account_id) — S2.1b.
112 account_id: string;
113 created_at: Date;
114}
115
116interface RawAuditRow {
117 id: string;
118 action: string;
119 occurred_at: Date;
120 metadata: Record<string, unknown> | null;
121}
122
123/**
124 * Pure redaction step — visible for tests. Same shape as the postgres
125 * row returned by `listProjectUsers`'s SELECT, minus the timestamps
126 * already serialised to ISO-8601 strings.
127 */
128export function redactUserRow(row: {
129 id: string;
130 email: string;
131 name: string | null;
132 createdAt: string;
133 lastSeenAt: string | null;
134 providerIds: string[];
135}): RedactedUser {
136 return {
137 id: row.id,
138 emailDomainHint: domainHintFromEmail(row.email),
139 nameInitial: nameInitialFrom(row.name),
140 providerIds: row.providerIds,
141 lastSeenAt: row.lastSeenAt,
142 createdAt: row.createdAt,
143 };
144}
145
146export function domainHintFromEmail(email: string): string {
147 const at = email.indexOf('@');
148 if (at < 0 || at === email.length - 1) return '?';
149 return email.slice(at + 1).toLowerCase();
150}
151
152export function nameInitialFrom(name: string | null): string | null {
153 if (!name) return null;
154 const trimmed = name.trim();
155 if (trimmed.length === 0) return null;
156 // First character — handles unicode by taking the first grapheme. JS
157 // string[0] would split a surrogate pair; Array.from breaks at code-point
158 // boundaries which is good enough for a dashboard initial.
159 return Array.from(trimmed)[0] ?? null;
160}
161
162/**
163 * List redacted users for a project. Cursor pagination on `(created_at, id)`
164 * to avoid OFFSET scans on growing tables. Cursor format: `<iso>__<id>`
165 * (URL-safe; the dashboard treats it as opaque).
166 */
167export async function listProjectUsers(
168 projectId: string,
169 opts: ListUsersOpts = {},
170): Promise<UserListResult> {
171 const limit = clamp(opts.limit ?? DEFAULT_LIMIT, 1, MAX_LIMIT);
172
173 let cursorClause = '';
174 const params: unknown[] = [];
175
176 if (opts.cursor) {
177 const parsed = parseCursor(opts.cursor);
178 cursorClause =
179 'AND (u.created_at < $1::timestamptz OR (u.created_at = $1::timestamptz AND u.id < $2))';
180 params.push(parsed.createdAt, parsed.id);
181 }
182
183 // Pull `limit + 1` to detect "has more" without a separate count.
184 params.push(limit + 1);
185 const limitPlaceholder = `$${params.length}`;
186
187 const rows = await runInProjectDatabase<RawUserJoinRow[]>(projectId, async (tx) => {
188 // DoltGres leaks a hidden expression-index column
189 // ("!hidden!_briven_auth_users_email_uniq!…") into query scope whenever a
190 // correlated subquery runs against `_briven_auth_users` — which has a
191 // UNIQUE INDEX on `lower(email)`. The old single-query form (correlated
192 // MAX + ARRAY subqueries) tripped exactly that and 500'd. So we fetch the
193 // user page with a plain SELECT, then enrich last-seen + providers with
194 // two simple `IN (…)` companion queries and stitch in JS. No correlated
195 // subquery touches the users table. (Verified on DoltGres.)
196 const userRows = (await tx.unsafe(
197 `
198 SELECT u.id, u.email, u.name, u.created_at
199 FROM "_briven_auth_users" u
200 WHERE 1=1 ${cursorClause}
201 ORDER BY u.created_at DESC, u.id DESC
202 LIMIT ${limitPlaceholder}
203 `,
204 params as never[],
205 )) as Array<{ id: string; email: string; name: string | null; created_at: Date }>;
206
207 if (userRows.length === 0) return [];
208
209 const ids = userRows.map((r) => r.id);
210 const placeholders = ids.map((_, i) => `$${i + 1}`).join(', ');
211
212 // last-seen per user = newest session (plain GROUP BY, no correlation).
213 const sessionRows = (await tx.unsafe(
214 `
215 SELECT user_id, MAX(created_at) AS last_seen
216 FROM "_briven_auth_sessions"
217 WHERE user_id IN (${placeholders})
218 GROUP BY user_id
219 `,
220 ids as never[],
221 )) as Array<{ user_id: string; last_seen: Date | null }>;
222 const lastSeenById = new Map<string, Date | null>();
223 for (const s of sessionRows) lastSeenById.set(s.user_id, s.last_seen);
224
225 // providers per user — deduped + sorted in JS (replaces DISTINCT/ORDER BY).
226 const accountRows = (await tx.unsafe(
227 `
228 SELECT user_id, provider_id
229 FROM "_briven_auth_accounts"
230 WHERE user_id IN (${placeholders})
231 `,
232 ids as never[],
233 )) as Array<{ user_id: string; provider_id: string }>;
234 const providersById = new Map<string, Set<string>>();
235 for (const a of accountRows) {
236 let set = providersById.get(a.user_id);
237 if (!set) {
238 set = new Set<string>();
239 providersById.set(a.user_id, set);
240 }
241 set.add(a.provider_id);
242 }
243
244 return userRows.map((u) => ({
245 id: u.id,
246 email: u.email,
247 name: u.name,
248 created_at: u.created_at,
249 last_seen_at: lastSeenById.get(u.id) ?? null,
250 provider_ids: providersById.has(u.id)
251 ? [...providersById.get(u.id)!].sort()
252 : [],
253 })) as unknown as RawUserJoinRow[];
254 });
255
256 const hasMore = rows.length > limit;
257 const page = hasMore ? rows.slice(0, limit) : rows;
258
259 const items = page.map((r) =>
260 redactUserRow({
261 id: r.id,
262 email: r.email,
263 name: r.name,
264 createdAt: r.created_at.toISOString(),
265 lastSeenAt: r.last_seen_at ? r.last_seen_at.toISOString() : null,
266 providerIds: r.provider_ids ?? [],
267 }),
268 );
269
270 const nextCursor = hasMore
271 ? formatCursor({
272 createdAt: page[page.length - 1]!.created_at.toISOString(),
273 id: page[page.length - 1]!.id,
274 })
275 : null;
276
277 return { items, nextCursor };
278}
279
280const USER_ID_RE = /^[a-zA-Z0-9_-]{1,64}$/;
281const SESSIONS_CAP = 50;
282const AUDIT_CAP = 50;
283const USER_AGENT_CAP = 96;
284
285/**
286 * Detail view of a single user. Returns `null` when the user id is not
287 * present in this project's schema — callers translate to 404. Sessions
288 * are filtered to currently-active (`expires_at > now()`) and capped at
289 * 50; audit is capped at the 50 most recent entries.
290 */
291export async function getProjectUserDetail(
292 projectId: string,
293 userId: string,
294): Promise<UserDetail | null> {
295 if (!USER_ID_RE.test(userId)) {
296 throw new ValidationError('invalid user id', { userId });
297 }
298
299 return runInProjectDatabase<UserDetail | null>(projectId, async (tx) => {
300 const userRows = (await tx.unsafe(
301 `SELECT id, email, name, created_at
302 FROM "_briven_auth_users"
303 WHERE id = $1
304 LIMIT 1`,
305 [userId] as never[],
306 )) as RawUserRow[];
307
308 const user = userRows[0];
309 if (!user) return null;
310
311 const sessionRows = (await tx.unsafe(
312 `SELECT id, created_at, expires_at, ip_address, user_agent
313 FROM "_briven_auth_sessions"
314 WHERE user_id = $1
315 AND expires_at > now()
316 ORDER BY created_at DESC
317 LIMIT $2`,
318 [userId, SESSIONS_CAP] as never[],
319 )) as RawSessionRow[];
320
321 const accountRows = (await tx.unsafe(
322 `SELECT id, provider_id, account_id, created_at
323 FROM "_briven_auth_accounts"
324 WHERE user_id = $1
325 ORDER BY created_at DESC`,
326 [userId] as never[],
327 )) as RawAccountRow[];
328
329 const auditRows = (await tx.unsafe(
330 `SELECT id, action, occurred_at, metadata
331 FROM "_briven_auth_audit_log"
332 WHERE user_id = $1
333 ORDER BY occurred_at DESC
334 LIMIT $2`,
335 [userId, AUDIT_CAP] as never[],
336 )) as RawAuditRow[];
337
338 return {
339 id: user.id,
340 emailDomainHint: domainHintFromEmail(user.email),
341 nameInitial: nameInitialFrom(user.name),
342 createdAt: user.created_at.toISOString(),
343 sessions: sessionRows.map((s) => ({
344 id: s.id,
345 createdAt: s.created_at.toISOString(),
346 expiresAt: s.expires_at.toISOString(),
347 // IP tracking is disabled (privacy), so ip_address is null and this
348 // hint is null. DTO key kept for dashboard stability.
349 ipHashHint: s.ip_address ? s.ip_address.slice(0, 8) : null,
350 userAgent: s.user_agent ? s.user_agent.slice(0, USER_AGENT_CAP) : null,
351 })),
352 accounts: accountRows.map((a) => ({
353 id: a.id,
354 providerId: a.provider_id,
355 // DTO key kept stable for the dashboard; sourced from Better-Auth's
356 // account_id column now.
357 providerAccountId: a.account_id,
358 createdAt: a.created_at.toISOString(),
359 })),
360 audit: auditRows.map((r) => ({
361 id: r.id,
362 action: r.action,
363 occurredAt: r.occurred_at.toISOString(),
364 metadata: r.metadata ?? {},
365 })),
366 };
367 });
368}
369
370function clamp(n: number, lo: number, hi: number): number {
371 if (!Number.isFinite(n)) return lo;
372 return Math.min(hi, Math.max(lo, Math.floor(n)));
373}
374
375function formatCursor(args: { createdAt: string; id: string }): string {
376 return `${args.createdAt}__${args.id}`;
377}
378
379function parseCursor(raw: string): { createdAt: string; id: string } {
380 const sepAt = raw.indexOf('__');
381 if (sepAt <= 0) {
382 throw new ValidationError('invalid cursor', { cursor: raw });
383 }
384 const createdAt = raw.slice(0, sepAt);
385 const id = raw.slice(sepAt + 2);
386 if (!id) throw new ValidationError('invalid cursor', { cursor: raw });
387 if (Number.isNaN(Date.parse(createdAt))) {
388 throw new ValidationError('invalid cursor', { cursor: raw });
389 }
390 return { createdAt, id };
391}