konnosDashboard
admin-user-detail.ts206 lines · main
1import { NotFoundError } from '@briven/shared';
2import { and, desc, eq, inArray, isNull } from 'drizzle-orm';
3
4import { getDb } from '../db/client.js';
5import { auditLogs, orgMembers, organizations, projects, sessions, users } from '../db/schema.js';
6import { lookupIp } from '../lib/geoip.js';
7import { listStorageUsage } from './storage-admin.js';
8
9/**
10 * DEEP per-user admin detail. CONTRACT with apps/web/(admin)/admin/users/[id].
11 * Enriches the basic user record with: full profile + company block, the
12 * geo-resolved last sign-in (raw IP never surfaced — only a nearBy city/region/
13 * country from the GeoIP lookup), every project the user's orgs own (with REAL
14 * storage rows/tables + effective limits), totals, and the newest 50 audit
15 * rows where they were the actor. Real numbers or null — never fabricated.
16 */
17export interface AdminUserDeepDetail {
18 user: {
19 id: string;
20 email: string;
21 name: string | null;
22 legalName: string | null;
23 isAdmin: boolean;
24 emailVerified: boolean;
25 suspendedAt: Date | null;
26 createdAt: Date;
27 timezone: string | null;
28 dateOfBirth: string | null;
29 countryOfBirth: string | null;
30 company: { name: string | null; vatId: string | null; country: string | null };
31 lastSignIn: {
32 at: Date;
33 ipAddress: string | null;
34 userAgent: string | null;
35 nearBy: { city: string | null; region: string | null; country: string | null } | null;
36 } | null;
37 };
38 projects: Array<{
39 id: string;
40 name: string;
41 slug: string;
42 tier: string;
43 region: string;
44 createdAt: Date;
45 rows: number | null;
46 tables: number | null;
47 rowLimit: number;
48 tableLimit: number;
49 }>;
50 totals: { projectCount: number; totalRows: number; totalTables: number };
51 activity: Array<{ at: string; action: string; detail: string | null }>;
52}
53
54/** Collapse an audit metadata blob into a short human detail string, or null. */
55function auditDetail(metadata: unknown): string | null {
56 if (metadata == null || typeof metadata !== 'object') return null;
57 const entries = Object.entries(metadata as Record<string, unknown>);
58 if (entries.length === 0) return null;
59 return entries
60 .slice(0, 4)
61 .map(([k, v]) => `${k}=${typeof v === 'object' ? JSON.stringify(v) : String(v)}`)
62 .join(', ');
63}
64
65export async function getUserDeepDetailForAdmin(userId: string): Promise<AdminUserDeepDetail> {
66 const db = getDb();
67
68 const [userRow] = await db
69 .select({
70 id: users.id,
71 email: users.email,
72 name: users.name,
73 legalName: users.legalName,
74 isAdmin: users.isAdmin,
75 emailVerified: users.emailVerified,
76 suspendedAt: users.suspendedAt,
77 createdAt: users.createdAt,
78 timezone: users.timezone,
79 dateOfBirth: users.dateOfBirth,
80 countryOfBirth: users.countryOfBirth,
81 companyName: users.companyName,
82 vatId: users.vatId,
83 addressCountry: users.addressCountry,
84 })
85 .from(users)
86 .where(and(eq(users.id, userId), isNull(users.deletedAt)))
87 .limit(1);
88 if (!userRow) throw new NotFoundError('user', userId);
89
90 const [orgRows, auditRows, lastSession] = await Promise.all([
91 db
92 .select({ id: organizations.id })
93 .from(orgMembers)
94 .innerJoin(organizations, eq(organizations.id, orgMembers.orgId))
95 .where(and(eq(orgMembers.userId, userId), isNull(organizations.deletedAt))),
96 db
97 .select({
98 action: auditLogs.action,
99 createdAt: auditLogs.createdAt,
100 metadata: auditLogs.metadata,
101 })
102 .from(auditLogs)
103 .where(eq(auditLogs.actorId, userId))
104 .orderBy(desc(auditLogs.createdAt))
105 .limit(50),
106 db
107 .select({
108 createdAt: sessions.createdAt,
109 ipAddress: sessions.ipAddress,
110 userAgent: sessions.userAgent,
111 })
112 .from(sessions)
113 .where(eq(sessions.userId, userId))
114 .orderBy(desc(sessions.createdAt))
115 .limit(1),
116 ]);
117
118 const orgIds = orgRows.map((o) => o.id);
119 const projectRows =
120 orgIds.length === 0
121 ? []
122 : await db
123 .select({
124 id: projects.id,
125 slug: projects.slug,
126 name: projects.name,
127 tier: projects.tier,
128 region: projects.region,
129 createdAt: projects.createdAt,
130 })
131 .from(projects)
132 .where(and(inArray(projects.orgId, orgIds), isNull(projects.deletedAt)))
133 .orderBy(desc(projects.createdAt));
134
135 // Real per-project storage from the SAME source the /v1/admin/storage route
136 // uses. listStorageUsage returns every live project; index it by id so each
137 // of this user's projects gets its actual rows/tables + effective limits.
138 const projectIds = new Set(projectRows.map((p) => p.id));
139 const usageById = new Map(
140 projectIds.size === 0
141 ? []
142 : (await listStorageUsage())
143 .filter((u) => projectIds.has(u.id))
144 .map((u) => [u.id, u] as const),
145 );
146
147 let totalRows = 0;
148 let totalTables = 0;
149 const projectsOut = projectRows.map((p) => {
150 const usage = usageById.get(p.id);
151 const rows = usage ? usage.rowCount : null;
152 const tables = usage ? usage.tableCount : null;
153 if (rows != null) totalRows += rows;
154 if (tables != null) totalTables += tables;
155 return {
156 id: p.id,
157 name: p.name,
158 slug: p.slug,
159 tier: p.tier,
160 region: p.region,
161 createdAt: p.createdAt,
162 rows,
163 tables,
164 rowLimit: usage ? usage.maxRows : 0,
165 tableLimit: usage ? usage.maxTables : 0,
166 };
167 });
168
169 const nearBy = lastSession[0] ? await lookupIp(lastSession[0].ipAddress) : null;
170
171 return {
172 user: {
173 id: userRow.id,
174 email: userRow.email,
175 name: userRow.name,
176 legalName: userRow.legalName,
177 isAdmin: userRow.isAdmin,
178 emailVerified: userRow.emailVerified,
179 suspendedAt: userRow.suspendedAt,
180 createdAt: userRow.createdAt,
181 timezone: userRow.timezone,
182 dateOfBirth: userRow.dateOfBirth,
183 countryOfBirth: userRow.countryOfBirth,
184 company: {
185 name: userRow.companyName,
186 vatId: userRow.vatId,
187 country: userRow.addressCountry,
188 },
189 lastSignIn: lastSession[0]
190 ? {
191 at: lastSession[0].createdAt,
192 ipAddress: lastSession[0].ipAddress,
193 userAgent: lastSession[0].userAgent,
194 nearBy,
195 }
196 : null,
197 },
198 projects: projectsOut,
199 totals: { projectCount: projectsOut.length, totalRows, totalTables },
200 activity: auditRows.map((r) => ({
201 at: r.createdAt.toISOString(),
202 action: r.action,
203 detail: auditDetail(r.metadata),
204 })),
205 };
206}