konnosDashboard
users.ts486 lines · main
1/**
2 * briven-engine users on Doltgres — list, detail, hold, archive, delete.
3 */
4
5import { getEnginePool } from './db.js';
6import { isAuthCoreInitialized } from './engine.js';
7import { revokeAllForUser } from './native-session.js';
8import { recordBrivenEngineAudit } from './audit.js';
9
10export type BrivenEngineUserStatus = 'active' | 'held' | 'archived';
11
12export type BrivenEngineUserSummary = {
13 id: string;
14 emails: string[];
15 phoneNumbers: string[];
16 tenantId: string;
17 timeJoined: number;
18 status: BrivenEngineUserStatus;
19 heldAt: string | null;
20 heldReason: string | null;
21 archivedAt: string | null;
22 archivedReason: string | null;
23 engine: 'briven-engine';
24 storage: 'doltgres';
25};
26
27export type BrivenEngineUserDetail = BrivenEngineUserSummary & {
28 emailVerified: boolean;
29 metadata: Record<string, unknown>;
30 roles: string[];
31 linkedLogins: Array<{
32 id: string;
33 provider: string;
34 providerUserId: string;
35 createdAt: string;
36 }>;
37 sessions: Array<{
38 handle: string;
39 expiresAt: string;
40 createdAt: string;
41 }>;
42 passkeyCount: number;
43 totpCount: number;
44};
45
46type UserRow = {
47 id: string;
48 email: string | null;
49 phone: string | null;
50 tenant_id: string;
51 time_joined: Date | string;
52 email_verified?: boolean;
53 metadata_json?: string;
54 held_at?: Date | string | null;
55 held_reason?: string | null;
56 archived_at?: Date | string | null;
57 archived_reason?: string | null;
58};
59
60function toIso(v: Date | string | null | undefined): string | null {
61 if (v == null) return null;
62 return new Date(v).toISOString();
63}
64
65function statusOf(row: UserRow): BrivenEngineUserStatus {
66 if (row.archived_at) return 'archived';
67 if (row.held_at) return 'held';
68 return 'active';
69}
70
71function mapSummary(u: UserRow): BrivenEngineUserSummary {
72 return {
73 id: u.id,
74 emails: u.email ? [u.email] : [],
75 phoneNumbers: u.phone ? [u.phone] : [],
76 tenantId: u.tenant_id,
77 timeJoined: new Date(u.time_joined).getTime(),
78 status: statusOf(u),
79 heldAt: toIso(u.held_at),
80 heldReason: u.held_reason ?? null,
81 archivedAt: toIso(u.archived_at),
82 archivedReason: u.archived_reason ?? null,
83 engine: 'briven-engine',
84 storage: 'doltgres',
85 };
86}
87
88export async function listBrivenEngineUsers(opts?: {
89 limit?: number;
90 paginationToken?: string;
91 tenantId?: string;
92}): Promise<{
93 users: BrivenEngineUserSummary[];
94 nextPaginationToken?: string;
95 engine: 'briven-engine';
96 storage: 'doltgres';
97}> {
98 if (!isAuthCoreInitialized()) {
99 return { users: [], engine: 'briven-engine', storage: 'doltgres' };
100 }
101 const limit = opts?.limit ?? 50;
102 const pool = getEnginePool();
103 const res = opts?.tenantId
104 ? await pool.query(
105 `SELECT id, email, phone, tenant_id, time_joined,
106 held_at, held_reason, archived_at, archived_reason
107 FROM be_users
108 WHERE tenant_id = $1
109 ORDER BY time_joined DESC LIMIT $2`,
110 [opts.tenantId, limit],
111 )
112 : await pool.query(
113 `SELECT id, email, phone, tenant_id, time_joined,
114 held_at, held_reason, archived_at, archived_reason
115 FROM be_users
116 ORDER BY time_joined DESC LIMIT $1`,
117 [limit],
118 );
119 const users = (res.rows as UserRow[]).map(mapSummary);
120 return { users, engine: 'briven-engine', storage: 'doltgres' };
121}
122
123export async function getBrivenEngineUser(
124 userId: string,
125 opts?: { tenantId?: string },
126): Promise<BrivenEngineUserDetail | null> {
127 if (!isAuthCoreInitialized()) return null;
128 const pool = getEnginePool();
129 const res = opts?.tenantId
130 ? await pool.query(
131 `SELECT id, email, phone, tenant_id, time_joined, email_verified, metadata_json,
132 held_at, held_reason, archived_at, archived_reason
133 FROM be_users WHERE id = $1 AND tenant_id = $2 LIMIT 1`,
134 [userId, opts.tenantId],
135 )
136 : await pool.query(
137 `SELECT id, email, phone, tenant_id, time_joined, email_verified, metadata_json,
138 held_at, held_reason, archived_at, archived_reason
139 FROM be_users WHERE id = $1 LIMIT 1`,
140 [userId],
141 );
142 const row = res.rows[0] as UserRow | undefined;
143 if (!row) return null;
144
145 let metadata: Record<string, unknown> = {};
146 try {
147 metadata = JSON.parse(row.metadata_json ?? '{}') as Record<string, unknown>;
148 } catch {
149 metadata = {};
150 }
151
152 const [rolesRes, linksRes, sessionsRes, passkeysRes, totpRes] =
153 await Promise.all([
154 pool.query(
155 `SELECT role_name FROM be_user_roles WHERE user_id = $1 ORDER BY role_name`,
156 [userId],
157 ),
158 pool.query(
159 `SELECT id, third_party_id, third_party_user_id, created_at
160 FROM be_third_party_links WHERE user_id = $1 ORDER BY created_at DESC`,
161 [userId],
162 ),
163 pool.query(
164 `SELECT session_handle, expires_at, created_at
165 FROM be_sessions
166 WHERE user_id = $1 AND expires_at > NOW()
167 ORDER BY created_at DESC`,
168 [userId],
169 ),
170 pool.query(
171 `SELECT COUNT(*)::int AS n FROM be_webauthn_credentials WHERE user_id = $1`,
172 [userId],
173 ),
174 pool.query(
175 `SELECT COUNT(*)::int AS n FROM be_totp_devices WHERE user_id = $1 AND verified = TRUE`,
176 [userId],
177 ),
178 ]);
179
180 return {
181 ...mapSummary(row),
182 emailVerified: Boolean(row.email_verified),
183 metadata,
184 roles: (rolesRes.rows as Array<{ role_name: string }>).map((r) => r.role_name),
185 linkedLogins: (
186 linksRes.rows as Array<{
187 id: string;
188 third_party_id: string;
189 third_party_user_id: string;
190 created_at: Date | string;
191 }>
192 ).map((l) => ({
193 id: l.id,
194 provider: l.third_party_id,
195 providerUserId: l.third_party_user_id,
196 createdAt: new Date(l.created_at).toISOString(),
197 })),
198 sessions: (
199 sessionsRes.rows as Array<{
200 session_handle: string;
201 expires_at: Date | string;
202 created_at: Date | string;
203 }>
204 ).map((s) => ({
205 handle: s.session_handle,
206 expiresAt: new Date(s.expires_at).toISOString(),
207 createdAt: new Date(s.created_at).toISOString(),
208 })),
209 passkeyCount: Number(
210 (passkeysRes.rows[0] as { n?: number } | undefined)?.n ?? 0,
211 ),
212 totpCount: Number((totpRes.rows[0] as { n?: number } | undefined)?.n ?? 0),
213 };
214}
215
216/**
217 * Returns null when the user may use Auth. Otherwise a machine code for 403.
218 * Held / archived users cannot sign in or keep using sessions.
219 */
220export async function getUserAccessBlock(
221 userId: string,
222): Promise<'held' | 'archived' | 'not_found' | null> {
223 if (!isAuthCoreInitialized()) return null;
224 const pool = getEnginePool();
225 const res = await pool.query(
226 `SELECT held_at, archived_at FROM be_users WHERE id = $1 LIMIT 1`,
227 [userId],
228 );
229 const row = res.rows[0] as
230 | { held_at: Date | string | null; archived_at: Date | string | null }
231 | undefined;
232 if (!row) return 'not_found';
233 if (row.archived_at) return 'archived';
234 if (row.held_at) return 'held';
235 return null;
236}
237
238export async function getBrivenEngineUserMetadata(
239 userId: string,
240): Promise<Record<string, unknown> | null> {
241 if (!isAuthCoreInitialized()) return null;
242 const pool = getEnginePool();
243 const res = await pool.query(
244 `SELECT metadata_json FROM be_users WHERE id = $1 LIMIT 1`,
245 [userId],
246 );
247 const row = res.rows[0] as { metadata_json: string } | undefined;
248 if (!row) return {};
249 try {
250 return JSON.parse(row.metadata_json) as Record<string, unknown>;
251 } catch {
252 return {};
253 }
254}
255
256export async function updateBrivenEngineUserMetadata(
257 userId: string,
258 metadata: Record<string, unknown>,
259): Promise<boolean> {
260 if (!isAuthCoreInitialized()) return false;
261 const pool = getEnginePool();
262 const res = await pool.query(
263 `UPDATE be_users SET metadata_json = $2 WHERE id = $1`,
264 [userId, JSON.stringify(metadata)],
265 );
266 return (res.rowCount ?? 0) > 0;
267}
268
269async function assertUserInTenant(
270 userId: string,
271 tenantId?: string,
272): Promise<boolean> {
273 if (!tenantId) return true;
274 const pool = getEnginePool();
275 const res = await pool.query(
276 `SELECT 1 FROM be_users WHERE id = $1 AND tenant_id = $2 LIMIT 1`,
277 [userId, tenantId],
278 );
279 return (res.rowCount ?? 0) > 0;
280}
281
282/** Put account on hold — cannot sign in / use sessions; data kept. */
283export async function holdBrivenEngineUser(
284 userId: string,
285 opts?: { reason?: string; tenantId?: string },
286): Promise<boolean> {
287 if (!isAuthCoreInitialized()) return false;
288 if (!(await assertUserInTenant(userId, opts?.tenantId))) return false;
289 const pool = getEnginePool();
290 const res = await pool.query(
291 `UPDATE be_users
292 SET held_at = NOW(), held_reason = $2
293 WHERE id = $1 AND archived_at IS NULL`,
294 [userId, opts?.reason?.trim() || null],
295 );
296 const ok = (res.rowCount ?? 0) > 0;
297 if (ok) {
298 void recordBrivenEngineAudit({
299 action: 'user.held',
300 userId,
301 tenantId: opts?.tenantId ?? null,
302 metadata: { reason: opts?.reason ?? null },
303 });
304 }
305 return ok;
306}
307
308export async function unholdBrivenEngineUser(
309 userId: string,
310 opts?: { tenantId?: string },
311): Promise<boolean> {
312 if (!isAuthCoreInitialized()) return false;
313 if (!(await assertUserInTenant(userId, opts?.tenantId))) return false;
314 const pool = getEnginePool();
315 const res = await pool.query(
316 `UPDATE be_users SET held_at = NULL, held_reason = NULL WHERE id = $1`,
317 [userId],
318 );
319 const ok = (res.rowCount ?? 0) > 0;
320 if (ok) {
321 void recordBrivenEngineAudit({
322 action: 'user.unheld',
323 userId,
324 tenantId: opts?.tenantId ?? null,
325 metadata: {},
326 });
327 }
328 return ok;
329}
330
331/** Archive — hidden/blocked, data kept, can restore. */
332export async function archiveBrivenEngineUser(
333 userId: string,
334 opts?: { reason?: string; tenantId?: string },
335): Promise<boolean> {
336 if (!isAuthCoreInitialized()) return false;
337 if (!(await assertUserInTenant(userId, opts?.tenantId))) return false;
338 const pool = getEnginePool();
339 const res = await pool.query(
340 `UPDATE be_users
341 SET archived_at = NOW(), archived_reason = $2,
342 held_at = NULL, held_reason = NULL
343 WHERE id = $1`,
344 [userId, opts?.reason?.trim() || null],
345 );
346 const ok = (res.rowCount ?? 0) > 0;
347 if (ok) {
348 // Archived users should not keep live sessions.
349 await revokeAllForUser(userId);
350 void recordBrivenEngineAudit({
351 action: 'user.archived',
352 userId,
353 tenantId: opts?.tenantId ?? null,
354 metadata: { reason: opts?.reason ?? null },
355 });
356 }
357 return ok;
358}
359
360export async function unarchiveBrivenEngineUser(
361 userId: string,
362 opts?: { tenantId?: string },
363): Promise<boolean> {
364 if (!isAuthCoreInitialized()) return false;
365 if (!(await assertUserInTenant(userId, opts?.tenantId))) return false;
366 const pool = getEnginePool();
367 const res = await pool.query(
368 `UPDATE be_users
369 SET archived_at = NULL, archived_reason = NULL
370 WHERE id = $1`,
371 [userId],
372 );
373 const ok = (res.rowCount ?? 0) > 0;
374 if (ok) {
375 void recordBrivenEngineAudit({
376 action: 'user.unarchived',
377 userId,
378 tenantId: opts?.tenantId ?? null,
379 metadata: {},
380 });
381 }
382 return ok;
383}
384
385/**
386 * GDPR-style data export for one end-user (JSON package).
387 * Operator-triggered; no raw IP columns.
388 */
389export async function exportBrivenEngineUserGdpr(
390 userId: string,
391 opts?: { tenantId?: string },
392): Promise<{
393 ok: true;
394 exportedAt: string;
395 engine: 'briven-engine';
396 package: Record<string, unknown>;
397} | null> {
398 const detail = await getBrivenEngineUser(userId, opts);
399 if (!detail) return null;
400 const pool = getEnginePool();
401 const [pw, codes] = await Promise.all([
402 pool.query(
403 `SELECT COUNT(*)::int AS n FROM be_password_hashes WHERE user_id = $1`,
404 [userId],
405 ),
406 pool.query(
407 `SELECT COUNT(*)::int AS n FROM be_passwordless_codes WHERE user_id = $1`,
408 [userId],
409 ).catch(() => ({ rows: [{ n: 0 }] })),
410 ]);
411 void recordBrivenEngineAudit({
412 action: 'user.gdpr_export',
413 userId,
414 tenantId: detail.tenantId,
415 metadata: {},
416 });
417 return {
418 ok: true,
419 exportedAt: new Date().toISOString(),
420 engine: 'briven-engine',
421 package: {
422 subject: {
423 id: detail.id,
424 emails: detail.emails,
425 phoneNumbers: detail.phoneNumbers,
426 emailVerified: detail.emailVerified,
427 tenantId: detail.tenantId,
428 timeJoined: detail.timeJoined,
429 heldAt: detail.heldAt ?? null,
430 archivedAt: detail.archivedAt ?? null,
431 },
432 metadata: detail.metadata,
433 roles: detail.roles,
434 linkedLogins: detail.linkedLogins,
435 sessions: detail.sessions.map((s) => ({
436 handle: s.handle,
437 expiresAt: s.expiresAt,
438 createdAt: s.createdAt,
439 })),
440 credentials: {
441 hasPassword: Number((pw.rows[0] as { n?: number })?.n ?? 0) > 0,
442 passkeyCount: detail.passkeyCount,
443 totpCount: detail.totpCount,
444 passwordlessCodeRows: Number((codes.rows[0] as { n?: number })?.n ?? 0),
445 },
446 note: 'Password hashes and raw secrets are never included in GDPR export packages.',
447 },
448 };
449}
450
451/**
452 * Hard delete — remove user + credentials + sessions + links.
453 * Email becomes free for a new signup.
454 */
455export async function deleteBrivenEngineUser(
456 userId: string,
457 opts?: { tenantId?: string },
458): Promise<boolean> {
459 if (!isAuthCoreInitialized()) return false;
460 if (!(await assertUserInTenant(userId, opts?.tenantId))) return false;
461 const pool = getEnginePool();
462
463 // Best-effort cascade (no FKs on all tables in Doltgres engine).
464 await Promise.all([
465 pool.query(`DELETE FROM be_sessions WHERE user_id = $1`, [userId]),
466 pool.query(`DELETE FROM be_password_hashes WHERE user_id = $1`, [userId]),
467 pool.query(`DELETE FROM be_third_party_links WHERE user_id = $1`, [userId]),
468 pool.query(`DELETE FROM be_user_roles WHERE user_id = $1`, [userId]),
469 pool.query(`DELETE FROM be_totp_devices WHERE user_id = $1`, [userId]),
470 pool.query(`DELETE FROM be_webauthn_credentials WHERE user_id = $1`, [userId]),
471 pool.query(`DELETE FROM be_webauthn_challenges WHERE user_id = $1`, [userId]),
472 pool.query(`DELETE FROM be_oidc_consents WHERE user_id = $1`, [userId]),
473 ]);
474
475 const res = await pool.query(`DELETE FROM be_users WHERE id = $1`, [userId]);
476 const ok = (res.rowCount ?? 0) > 0;
477 if (ok) {
478 void recordBrivenEngineAudit({
479 action: 'user.deleted',
480 userId,
481 tenantId: opts?.tenantId ?? null,
482 metadata: {},
483 });
484 }
485 return ok;
486}