konnosDashboard
admin.ts365 lines · main
1import { NotFoundError, ValidationError } from '@briven/shared';
2import { and, desc, eq, inArray, isNull, sql } from 'drizzle-orm';
3
4import { getDb } from '../db/client.js';
5import {
6 auditLogs,
7 orgMembers,
8 organizations,
9 projects,
10 sessions,
11 users,
12 type ProjectTier,
13} from '../db/schema.js';
14import { softDeleteAccount } from './account-deletion.js';
15
16export interface AdminUserRow {
17 id: string;
18 email: string;
19 name: string | null;
20 emailVerified: boolean;
21 isAdmin: boolean;
22 suspendedAt: Date | null;
23 createdAt: Date;
24 projectCount: number;
25}
26
27export async function listUsers(limit = 200): Promise<AdminUserRow[]> {
28 const db = getDb();
29 // One query with a correlated subselect for project count — acceptable at
30 // Phase 3 scale (< few thousand users).
31 const rows = await db
32 .select({
33 id: users.id,
34 email: users.email,
35 name: users.name,
36 emailVerified: users.emailVerified,
37 isAdmin: users.isAdmin,
38 suspendedAt: users.suspendedAt,
39 createdAt: users.createdAt,
40 projectCount: sql<number>`(
41 SELECT count(*)::int FROM projects p
42 INNER JOIN org_members m ON m.org_id = p.org_id
43 WHERE m.user_id = users.id AND p.deleted_at IS NULL
44 )`,
45 })
46 .from(users)
47 .where(isNull(users.deletedAt))
48 .orderBy(desc(users.createdAt))
49 .limit(limit);
50 return rows;
51}
52
53export interface AdminProjectRow {
54 id: string;
55 slug: string;
56 name: string;
57 orgId: string;
58 tier: string;
59 createdAt: Date;
60}
61
62export async function listProjects(limit = 500): Promise<AdminProjectRow[]> {
63 const db = getDb();
64 return db
65 .select({
66 id: projects.id,
67 slug: projects.slug,
68 name: projects.name,
69 orgId: projects.orgId,
70 tier: projects.tier,
71 createdAt: projects.createdAt,
72 })
73 .from(projects)
74 .where(isNull(projects.deletedAt))
75 .orderBy(desc(projects.createdAt))
76 .limit(limit);
77}
78
79export interface AdminProjectDetail {
80 id: string;
81 slug: string;
82 name: string;
83 orgId: string;
84 tier: ProjectTier;
85 suspendedAt: Date | null;
86 suspendReason: string | null;
87 deletedAt: Date | null;
88 createdAt: Date;
89}
90
91/**
92 * Single-project admin getter — the drill-down counterpart to listProjects.
93 * Includes suspend/delete state so the admin project-detail page can render
94 * the same "suspended"/"deleted" badges the users page shows. Returns null
95 * (not throwing) when no project matches, so the route can emit a clean 404.
96 */
97export async function getProjectForAdmin(projectId: string): Promise<AdminProjectDetail | null> {
98 const db = getDb();
99 const [row] = await db
100 .select({
101 id: projects.id,
102 slug: projects.slug,
103 name: projects.name,
104 orgId: projects.orgId,
105 tier: projects.tier,
106 suspendedAt: projects.suspendedAt,
107 suspendReason: projects.suspendReason,
108 deletedAt: projects.deletedAt,
109 createdAt: projects.createdAt,
110 })
111 .from(projects)
112 .where(eq(projects.id, projectId))
113 .limit(1);
114 return row ?? null;
115}
116
117/**
118 * Superadmin plan-tier override for ONE project. Returns the previous tier
119 * (so the route can audit old→new) or null when the project doesn't exist.
120 * Does NOT touch the in-process tier cache — the route calls
121 * invalidateTierCache() after so the change takes effect on the next request.
122 */
123export async function setProjectTier(
124 projectId: string,
125 tier: ProjectTier,
126): Promise<{ previousTier: ProjectTier } | null> {
127 const db = getDb();
128 const [before] = await db
129 .select({ tier: projects.tier })
130 .from(projects)
131 .where(eq(projects.id, projectId))
132 .limit(1);
133 if (!before) return null;
134 await db.update(projects).set({ tier, updatedAt: new Date() }).where(eq(projects.id, projectId));
135 return { previousTier: before.tier };
136}
137
138/**
139 * Bulk plan-tier override for EVERY non-deleted project the calling admin
140 * OWNS. Ownership is strict: a project is "owned" only when its org was
141 * created by this admin (organizations.created_by = ownerUserId). The WHERE
142 * clause below can never reach a project in an org the admin didn't create,
143 * so this is safe to expose as a self-service "upgrade all my projects"
144 * action — it is structurally impossible to upgrade someone else's project.
145 * Returns the ids of the projects that were changed so the caller can
146 * invalidate each one's tier cache.
147 */
148export async function setProjectsTierForOwner(
149 ownerUserId: string,
150 tier: ProjectTier,
151): Promise<{ changedProjectIds: string[] }> {
152 const db = getDb();
153 // Orgs this admin literally created — the tightest ownership signal.
154 const ownedOrgs = await db
155 .select({ id: organizations.id })
156 .from(organizations)
157 .where(and(eq(organizations.createdBy, ownerUserId), isNull(organizations.deletedAt)));
158 const ownedOrgIds = ownedOrgs.map((o) => o.id);
159 if (ownedOrgIds.length === 0) return { changedProjectIds: [] };
160
161 const changed = await db
162 .update(projects)
163 .set({ tier, updatedAt: new Date() })
164 .where(and(inArray(projects.orgId, ownedOrgIds), isNull(projects.deletedAt)))
165 .returning({ id: projects.id });
166 return { changedProjectIds: changed.map((r) => r.id) };
167}
168
169export async function suspendUser(userId: string): Promise<void> {
170 const db = getDb();
171 const [row] = await db.select().from(users).where(eq(users.id, userId)).limit(1);
172 if (!row) throw new NotFoundError('user', userId);
173 await db.update(users).set({ suspendedAt: new Date() }).where(eq(users.id, userId));
174 // Invalidate every live session for the suspended user.
175 await db.delete(sessions).where(eq(sessions.userId, userId));
176}
177
178export async function unsuspendUser(userId: string): Promise<void> {
179 const db = getDb();
180 await db.update(users).set({ suspendedAt: null }).where(eq(users.id, userId));
181}
182
183/**
184 * Admin-initiated account deletion. Guarded so a slip can't wipe the
185 * wrong person: ONLY a suspended, non-admin account can be deleted. Reuses
186 * the GDPR soft-delete (30-day reversible grace window; the
187 * account-deletion-gc worker hard-purges after that). Keeps the users
188 * dashboard clean without a hard, irreversible wipe.
189 */
190export async function deleteUserAsAdmin(userId: string): Promise<void> {
191 const db = getDb();
192 const [row] = await db
193 .select({ suspendedAt: users.suspendedAt, isAdmin: users.isAdmin, deletedAt: users.deletedAt })
194 .from(users)
195 .where(eq(users.id, userId))
196 .limit(1);
197 if (!row) throw new NotFoundError('user', userId);
198 if (row.deletedAt) throw new ValidationError('this account is already scheduled for deletion');
199 if (row.isAdmin) throw new ValidationError('cannot delete an admin account — revoke admin first');
200 if (!row.suspendedAt) throw new ValidationError('suspend the account before deleting it');
201 await softDeleteAccount({ userId, reason: 'admin-deleted' });
202}
203
204/**
205 * Signs out every session for a user immediately — does NOT flip the
206 * suspended flag. Used when rotating a compromised session.
207 */
208export async function forceSignOut(userId: string): Promise<number> {
209 const db = getDb();
210 const result = await db
211 .delete(sessions)
212 .where(eq(sessions.userId, userId))
213 .returning({ id: sessions.id });
214 return result.length;
215}
216
217export async function grantAdmin(userId: string): Promise<void> {
218 const db = getDb();
219 await db.update(users).set({ isAdmin: true }).where(eq(users.id, userId));
220}
221
222export async function revokeAdmin(userId: string): Promise<void> {
223 const db = getDb();
224 await db.update(users).set({ isAdmin: false }).where(eq(users.id, userId));
225}
226
227export interface AdminUserDetail {
228 user: AdminUserRow;
229 orgs: Array<{ id: string; name: string; slug: string; personal: boolean; role: string }>;
230 projects: Array<{ id: string; slug: string; name: string; tier: string; orgId: string; createdAt: Date }>;
231 recentAudit: Array<{ id: string; action: string; createdAt: Date; metadata: Record<string, unknown> | null }>;
232}
233
234/**
235 * Drilldown for the admin user-detail page. Loads basics + every org
236 * the user is a member of + every project owned by those orgs + the
237 * last 50 audit rows where they're the actor. Single fan-out, parallel
238 * queries — at Phase 3 scale this is one round-trip burst.
239 */
240export async function getUserDetailForAdmin(userId: string): Promise<AdminUserDetail> {
241 const db = getDb();
242 const [userRow] = await db
243 .select({
244 id: users.id,
245 email: users.email,
246 name: users.name,
247 emailVerified: users.emailVerified,
248 isAdmin: users.isAdmin,
249 suspendedAt: users.suspendedAt,
250 createdAt: users.createdAt,
251 projectCount: sql<number>`(
252 SELECT count(*)::int FROM projects p
253 INNER JOIN org_members m ON m.org_id = p.org_id
254 WHERE m.user_id = ${users.id} AND p.deleted_at IS NULL
255 )`,
256 })
257 .from(users)
258 .where(and(eq(users.id, userId), isNull(users.deletedAt)))
259 .limit(1);
260 if (!userRow) throw new NotFoundError('user', userId);
261
262 const [orgRows, auditRows] = await Promise.all([
263 db
264 .select({
265 id: organizations.id,
266 name: organizations.name,
267 slug: organizations.slug,
268 personal: organizations.personal,
269 role: orgMembers.role,
270 })
271 .from(orgMembers)
272 .innerJoin(organizations, eq(organizations.id, orgMembers.orgId))
273 .where(and(eq(orgMembers.userId, userId), isNull(organizations.deletedAt))),
274 db
275 .select({
276 id: auditLogs.id,
277 action: auditLogs.action,
278 createdAt: auditLogs.createdAt,
279 metadata: auditLogs.metadata,
280 })
281 .from(auditLogs)
282 .where(eq(auditLogs.actorId, userId))
283 .orderBy(desc(auditLogs.createdAt))
284 .limit(50),
285 ]);
286
287 const orgIds = orgRows.map((o) => o.id);
288 const projectRows = orgIds.length === 0
289 ? []
290 : await db
291 .select({
292 id: projects.id,
293 slug: projects.slug,
294 name: projects.name,
295 tier: projects.tier,
296 orgId: projects.orgId,
297 createdAt: projects.createdAt,
298 })
299 .from(projects)
300 .where(and(inArray(projects.orgId, orgIds), isNull(projects.deletedAt)))
301 .orderBy(desc(projects.createdAt));
302
303 return {
304 user: userRow,
305 orgs: orgRows,
306 projects: projectRows,
307 // jsonb columns surface as `unknown` from drizzle; the consumer
308 // (admin user-detail page) renders metadata.action only, so cast
309 // to the documented Record<string, unknown> | null shape here.
310 recentAudit: auditRows.map((r) => ({
311 ...r,
312 metadata: r.metadata as Record<string, unknown> | null,
313 })),
314 };
315}
316
317export async function adminStats(): Promise<{
318 users: number;
319 projects: number;
320 deployments: number;
321 signups24h: number;
322 openMigrations: number;
323 openAbuseReports: number;
324 suppressions: number;
325}> {
326 const db = getDb();
327 // ISO string, not Date — postgres.js can't serialize a raw Date param in
328 // sql`` templates under Bun (see services/function-logs.ts).
329 const since24h = new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString();
330 const [u] = await db
331 .select({ c: sql<number>`count(*)::int` })
332 .from(users)
333 .where(isNull(users.deletedAt));
334 const [p] = await db
335 .select({ c: sql<number>`count(*)::int` })
336 .from(projects)
337 .where(and(isNull(projects.deletedAt)));
338 // deployments table doesn't have a soft-delete; sum all
339 const [d] = await db.execute<{ c: number }>(sql`SELECT count(*)::int AS c FROM deployments`);
340 // Operator-glance rollups for the /dashboard/admin landing page.
341 // Each one is a single COUNT(*) on a small or indexed scope, so the
342 // overall admin home stays sub-50ms at Phase 3 scale.
343 const [s24] = await db.execute<{ c: number }>(
344 sql`SELECT count(*)::int AS c FROM users WHERE created_at >= ${since24h}::timestamptz AND deleted_at IS NULL`,
345 );
346 const [om] = await db.execute<{ c: number }>(
347 sql`SELECT count(*)::int AS c FROM migration_requests WHERE status NOT IN ('completed', 'cancelled')`,
348 );
349 const [oa] = await db.execute<{ c: number }>(
350 // abuseStatus enum is ['open','triaged','resolved'] only
351 sql`SELECT count(*)::int AS c FROM abuse_reports WHERE status IN ('open', 'triaged')`,
352 );
353 const [sup] = await db.execute<{ c: number }>(
354 sql`SELECT count(*)::int AS c FROM email_suppressions`,
355 );
356 return {
357 users: u?.c ?? 0,
358 projects: p?.c ?? 0,
359 deployments: d?.c ?? 0,
360 signups24h: s24?.c ?? 0,
361 openMigrations: om?.c ?? 0,
362 openAbuseReports: oa?.c ?? 0,
363 suppressions: sup?.c ?? 0,
364 };
365}