TypeScript 97.5%
SQL 1.4%
Python 0.8%
1import "server-only";2import type { SQL } from "drizzle-orm";3import { alias } from "drizzle-orm/pg-core";4import {5 getDb,6 users,7 sessions,8 organizations,9 organizationMembers,10 projects,11 apiKeys,12 fetchRequests,13 requestAttempts,14 usageEvents,15 subscriptions,16 billingEvents,17 providerConfigs,18 providerHealth,19 domainProfiles,20 routingMetrics,21 auditLogs,22 abuseEvents,23 featureFlags,24 statusIncidents,25 proxySessions,26 webhooks,27 signupAllowlist,28 eq,29 and,30 or,31 desc,32 asc,33 sql,34 gte,35 lte,36 ilike,37 inArray,38 count,39} from "@fetcha/db";40import { PLAN_LIMITS, PLANS, normalizePlan } from "@fetcha/core";4142/**43 * Admin-only data access. Everything here may expose upstream provider names,44 * upstream costs and internal error details — never import from customer-facing code.45 */4647// ---------------------------------------------------------------------------48// Helpers49// ---------------------------------------------------------------------------50export type Range = "24h" | "7d" | "30d";51export const RANGES: Range[] = ["24h", "7d", "30d"];5253export function parseRange(v: string | string[] | undefined): Range {54 return v === "7d" || v === "30d" ? v : "24h";55}56export function rangeStart(r: Range): Date {57 const ms = r === "24h" ? 86_400_000 : r === "7d" ? 7 * 86_400_000 : 30 * 86_400_000;58 return new Date(Date.now() - ms);59}60export function rangeLabel(r: Range): string {61 return r === "24h" ? "Last 24 hours" : r === "7d" ? "Last 7 days" : "Last 30 days";62}6364const num = (e: SQL) => sql<number>`coalesce(${e}, 0)`.mapWith(Number);65const sumCase = (cond: SQL) => num(sql`sum(case when ${cond} then 1 else 0 end)`);66const pct = (a: number, b: number) => (b > 0 ? (a / b) * 100 : null);67const toDate = (v: unknown): Date | null => (v ? new Date(v as string) : null);68const first = (v: string | string[] | undefined): string | undefined => (Array.isArray(v) ? v[0] : v);69export const str = (v: string | string[] | undefined): string => (first(v) ?? "").trim();70export function pageNum(v: string | string[] | undefined): number {71 const n = Number.parseInt(first(v) ?? "1", 10);72 return Number.isFinite(n) && n > 0 ? n : 1;73}7475export const PROVIDER_IDS = ["oxylabs", "decodo", "soax", "direct"] as const;76export const CONCRETE_NETWORKS = ["datacenter", "residential", "isp", "mobile"] as const;7778export const PLAN_OPTIONS = PLANS.map((p) => ({ value: p, label: PLAN_LIMITS[p].label }));7980// ---------------------------------------------------------------------------81// Overview82// ---------------------------------------------------------------------------83export interface PlatformKpis {84 requests: number;85 successes: number;86 failed: number;87 successRate: number | null;88 activeOrgs: number;89 revenue: number;90 cost: number;91 margin: number;92 marginPct: number | null;93 costPerSuccess: number | null;94 avgAttempts: number | null;95 avgLatency: number | null;96 bytes: number;97 newUsers: number;98 newOrgs: number;99}100101export async function getPlatformKpis(since: Date): Promise<PlatformKpis> {102 const db = getDb();103 const [r] = await db104 .select({105 requests: count(),106 successes: sumCase(sql`${fetchRequests.status} = 'success'`),107 failed: sumCase(sql`${fetchRequests.status} = 'failed'`),108 activeOrgs: num(sql`count(distinct ${fetchRequests.organizationId})`),109 revenue: num(sql`sum(${fetchRequests.priceUsd})`),110 cost: num(sql`sum(${fetchRequests.costUsd})`),111 attempts: num(sql`sum(${fetchRequests.attempts})`),112 avgLatency: num(sql`avg(${fetchRequests.latencyMs})`),113 bytes: num(sql`sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut})`),114 })115 .from(fetchRequests)116 .where(gte(fetchRequests.createdAt, since));117 const [u] = await db.select({ n: count() }).from(users).where(gte(users.createdAt, since));118 const [o] = await db.select({ n: count() }).from(organizations).where(gte(organizations.createdAt, since));119 const requests = r?.requests ?? 0;120 const successes = r?.successes ?? 0;121 const revenue = r?.revenue ?? 0;122 const cost = r?.cost ?? 0;123 return {124 requests,125 successes,126 failed: r?.failed ?? 0,127 successRate: pct(successes, requests),128 activeOrgs: r?.activeOrgs ?? 0,129 revenue,130 cost,131 margin: revenue - cost,132 marginPct: revenue > 0 ? ((revenue - cost) / revenue) * 100 : null,133 costPerSuccess: successes > 0 ? cost / successes : null,134 avgAttempts: requests > 0 ? (r?.attempts ?? 0) / requests : null,135 avgLatency: requests > 0 ? r?.avgLatency ?? null : null,136 bytes: r?.bytes ?? 0,137 newUsers: u?.n ?? 0,138 newOrgs: o?.n ?? 0,139 };140}141142export interface ProviderStat {143 provider: string;144 requests: number;145 successes: number;146 blocked: number;147 successRate: number | null;148 blockedRate: number | null;149 avgLatency: number | null;150 bytes: number;151 cost: number;152 costPerSuccess: number | null;153}154155/** Per-provider attempt statistics from `request_attempts` (admin only). */156export async function getProviderStats(since: Date): Promise<ProviderStat[]> {157 const db = getDb();158 const rows = await db159 .select({160 provider: requestAttempts.provider,161 requests: count(),162 successes: sumCase(sql`${requestAttempts.outcome} = 'success'`),163 blocked: sumCase(sql`${requestAttempts.outcome} = 'blocked'`),164 avgLatency: num(sql`avg(${requestAttempts.durationMs})`),165 bytes: num(sql`sum(${requestAttempts.bytesIn} + ${requestAttempts.bytesOut})`),166 cost: num(sql`sum(${requestAttempts.costUsd})`),167 })168 .from(requestAttempts)169 .where(gte(requestAttempts.createdAt, since))170 .groupBy(requestAttempts.provider)171 .orderBy(desc(count()));172 return rows.map((r) => ({173 ...r,174 successRate: pct(r.successes, r.requests),175 blockedRate: pct(r.blocked, r.requests),176 avgLatency: r.requests ? r.avgLatency : null,177 costPerSuccess: r.successes ? r.cost / r.successes : null,178 }));179}180181export async function getRecentSignups(limit = 8) {182 return getDb()183 .select({ id: users.id, email: users.email, name: users.name, emailVerified: users.emailVerified, createdAt: users.createdAt })184 .from(users)185 .orderBy(desc(users.createdAt))186 .limit(limit);187}188189export async function getRecentFailures(limit = 10) {190 return getDb()191 .select({192 id: fetchRequests.id,193 domain: fetchRequests.domain,194 errorCode: fetchRequests.errorCode,195 httpStatus: fetchRequests.httpStatus,196 attempts: fetchRequests.attempts,197 createdAt: fetchRequests.createdAt,198 orgName: organizations.name,199 organizationId: fetchRequests.organizationId,200 })201 .from(fetchRequests)202 .leftJoin(organizations, eq(organizations.id, fetchRequests.organizationId))203 .where(eq(fetchRequests.status, "failed"))204 .orderBy(desc(fetchRequests.createdAt))205 .limit(limit);206}207208// ---------------------------------------------------------------------------209// Users210// ---------------------------------------------------------------------------211export interface Paged<T> {212 rows: T[];213 total: number;214 page: number;215 pageSize: number;216 pages: number;217}218219function paged<T>(rows: T[], total: number, page: number, pageSize: number): Paged<T> {220 return { rows, total, page, pageSize, pages: Math.max(1, Math.ceil(total / pageSize)) };221}222223export async function listUsers(opts: { q?: string; page?: number; pageSize?: number }) {224 const db = getDb();225 const page = opts.page ?? 1;226 const pageSize = opts.pageSize ?? 25;227 const where = opts.q ? or(ilike(users.email, `%${opts.q}%`), ilike(users.name, `%${opts.q}%`), eq(users.id, opts.q)) : undefined;228 const rows = await db229 .select({230 id: users.id,231 email: users.email,232 name: users.name,233 emailVerified: users.emailVerified,234 role: users.role,235 banned: users.banned,236 createdAt: users.createdAt,237 orgs: sql<number>`(select count(*) from ${organizationMembers} where ${organizationMembers.userId} = ${users.id})`.mapWith(Number),238 lastLogin: sql<string | null>`(select max(${sessions.createdAt}) from ${sessions} where ${sessions.userId} = ${users.id})`,239 })240 .from(users)241 .where(where)242 .orderBy(desc(users.createdAt))243 .limit(pageSize)244 .offset((page - 1) * pageSize);245 const [t] = await db.select({ total: count() }).from(users).where(where);246 return paged(247 rows.map((r) => ({ ...r, lastLogin: toDate(r.lastLogin) })),248 t?.total ?? 0,249 page,250 pageSize,251 );252}253254export async function getUserDetail(id: string) {255 const db = getDb();256 const [user] = await db.select().from(users).where(eq(users.id, id)).limit(1);257 if (!user) return null;258 const memberships = await db259 .select({ organizationId: organizations.id, name: organizations.name, plan: organizations.plan, role: organizationMembers.role, suspended: organizations.suspended, joinedAt: organizationMembers.createdAt })260 .from(organizationMembers)261 .innerJoin(organizations, eq(organizations.id, organizationMembers.organizationId))262 .where(eq(organizationMembers.userId, id));263 const orgIds = memberships.map((m) => m.organizationId);264 const [proj] = orgIds.length ? await db.select({ n: count() }).from(projects).where(inArray(projects.organizationId, orgIds)) : [{ n: 0 }];265 const [keys] = orgIds.length ? await db.select({ n: count() }).from(apiKeys).where(inArray(apiKeys.organizationId, orgIds)) : [{ n: 0 }];266 const [reqs] = orgIds.length267 ? await db.select({ n: count() }).from(fetchRequests).where(and(inArray(fetchRequests.organizationId, orgIds), gte(fetchRequests.createdAt, rangeStart("30d"))))268 : [{ n: 0 }];269 const audit = await db.select().from(auditLogs).where(eq(auditLogs.userId, id)).orderBy(desc(auditLogs.createdAt)).limit(50);270 const userSessions = await db271 .select({ id: sessions.id, createdAt: sessions.createdAt, expiresAt: sessions.expiresAt, ipAddress: sessions.ipAddress, userAgent: sessions.userAgent })272 .from(sessions)273 .where(eq(sessions.userId, id))274 .orderBy(desc(sessions.createdAt))275 .limit(10);276 return { user, memberships, projectsCount: proj?.n ?? 0, keysCount: keys?.n ?? 0, requests30d: reqs?.n ?? 0, audit, sessions: userSessions };277}278279// ---------------------------------------------------------------------------280// Organizations281// ---------------------------------------------------------------------------282export async function listOrganizations(opts: { q?: string; plan?: string; page?: number; pageSize?: number }) {283 const db = getDb();284 const page = opts.page ?? 1;285 const pageSize = opts.pageSize ?? 25;286 const since = rangeStart("30d");287 const conds: SQL[] = [];288 if (opts.q) conds.push(or(ilike(organizations.name, `%${opts.q}%`), ilike(organizations.slug, `%${opts.q}%`), eq(organizations.id, opts.q), ilike(users.email, `%${opts.q}%`))!);289 if (opts.plan) conds.push(eq(organizations.plan, opts.plan));290 const where = conds.length ? and(...conds) : undefined;291 const rows = await db292 .select({293 id: organizations.id,294 name: organizations.name,295 slug: organizations.slug,296 plan: organizations.plan,297 ownerEmail: users.email,298 suspended: organizations.suspended,299 providerVisibility: organizations.providerVisibility,300 createdAt: organizations.createdAt,301 projects: sql<number>`(select count(*) from ${projects} where ${projects.organizationId} = ${organizations.id})`.mapWith(Number),302 requests30d: sql<number>`(select count(*) from ${fetchRequests} where ${fetchRequests.organizationId} = ${organizations.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),303 spend30d: sql<number>`(select coalesce(sum(${fetchRequests.priceUsd}),0) from ${fetchRequests} where ${fetchRequests.organizationId} = ${organizations.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),304 })305 .from(organizations)306 .leftJoin(users, eq(users.id, organizations.ownerUserId))307 .where(where)308 .orderBy(desc(organizations.createdAt))309 .limit(pageSize)310 .offset((page - 1) * pageSize);311 const [t] = await db.select({ total: count() }).from(organizations).leftJoin(users, eq(users.id, organizations.ownerUserId)).where(where);312 return paged(rows, t?.total ?? 0, page, pageSize);313}314315export async function getOrganizationDetail(id: string) {316 const db = getDb();317 const [org] = await db.select().from(organizations).where(eq(organizations.id, id)).limit(1);318 if (!org) return null;319 const [owner] = await db.select({ id: users.id, email: users.email, name: users.name }).from(users).where(eq(users.id, org.ownerUserId)).limit(1);320 const members = await db321 .select({ userId: users.id, email: users.email, name: users.name, role: organizationMembers.role, joinedAt: organizationMembers.createdAt })322 .from(organizationMembers)323 .innerJoin(users, eq(users.id, organizationMembers.userId))324 .where(eq(organizationMembers.organizationId, id));325 const since = rangeStart("30d");326 const projectRows = await db327 .select({328 id: projects.id,329 name: projects.name,330 environment: projects.environment,331 archivedAt: projects.archivedAt,332 createdAt: projects.createdAt,333 keys: sql<number>`(select count(*) from ${apiKeys} where ${apiKeys.projectId} = ${projects.id} and ${apiKeys.revokedAt} is null)`.mapWith(Number),334 requests30d: sql<number>`(select count(*) from ${fetchRequests} where ${fetchRequests.projectId} = ${projects.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),335 })336 .from(projects)337 .where(eq(projects.organizationId, id))338 .orderBy(asc(projects.createdAt));339 const keys = await db340 .select({341 id: apiKeys.id,342 name: apiKeys.name,343 keyPrefix: apiKeys.keyPrefix,344 last4: apiKeys.last4,345 mode: apiKeys.mode,346 scopes: apiKeys.scopes,347 projectName: projects.name,348 lastUsedAt: apiKeys.lastUsedAt,349 revokedAt: apiKeys.revokedAt,350 expiresAt: apiKeys.expiresAt,351 createdAt: apiKeys.createdAt,352 })353 .from(apiKeys)354 .leftJoin(projects, eq(projects.id, apiKeys.projectId))355 .where(eq(apiKeys.organizationId, id))356 .orderBy(desc(apiKeys.createdAt))357 .limit(100);358 const recentRequests = await db359 .select({360 id: fetchRequests.id,361 domain: fetchRequests.domain,362 status: fetchRequests.status,363 httpStatus: fetchRequests.httpStatus,364 errorCode: fetchRequests.errorCode,365 network: fetchRequests.network,366 country: fetchRequests.country,367 latencyMs: fetchRequests.latencyMs,368 priceUsd: fetchRequests.priceUsd,369 costUsd: fetchRequests.costUsd,370 createdAt: fetchRequests.createdAt,371 })372 .from(fetchRequests)373 .where(eq(fetchRequests.organizationId, id))374 .orderBy(desc(fetchRequests.createdAt))375 .limit(25);376 const [stats] = await db377 .select({378 requests: count(),379 successes: sumCase(sql`${fetchRequests.status} = 'success'`),380 revenue: num(sql`sum(${fetchRequests.priceUsd})`),381 cost: num(sql`sum(${fetchRequests.costUsd})`),382 bytes: num(sql`sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut})`),383 })384 .from(fetchRequests)385 .where(and(eq(fetchRequests.organizationId, id), gte(fetchRequests.createdAt, since)));386 const audit = await db.select().from(auditLogs).where(eq(auditLogs.organizationId, id)).orderBy(desc(auditLogs.createdAt)).limit(30);387 return { org, owner: owner ?? null, members, projects: projectRows, keys, recentRequests, stats30d: stats!, audit };388}389390// ---------------------------------------------------------------------------391// Projects392// ---------------------------------------------------------------------------393export async function listProjects(opts: { q?: string; page?: number; pageSize?: number; includeArchived?: boolean }) {394 const db = getDb();395 const page = opts.page ?? 1;396 const pageSize = opts.pageSize ?? 25;397 const since = rangeStart("30d");398 const conds: SQL[] = [];399 if (opts.q) conds.push(or(ilike(projects.name, `%${opts.q}%`), ilike(organizations.name, `%${opts.q}%`), eq(projects.id, opts.q))!);400 if (!opts.includeArchived) conds.push(sql`${projects.archivedAt} is null`);401 const where = conds.length ? and(...conds) : undefined;402 const rows = await db403 .select({404 id: projects.id,405 name: projects.name,406 environment: projects.environment,407 organizationId: projects.organizationId,408 orgName: organizations.name,409 orgPlan: organizations.plan,410 archivedAt: projects.archivedAt,411 createdAt: projects.createdAt,412 keys: sql<number>`(select count(*) from ${apiKeys} where ${apiKeys.projectId} = ${projects.id} and ${apiKeys.revokedAt} is null)`.mapWith(Number),413 requests30d: sql<number>`(select count(*) from ${fetchRequests} where ${fetchRequests.projectId} = ${projects.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),414 spend30d: sql<number>`(select coalesce(sum(${fetchRequests.priceUsd}),0) from ${fetchRequests} where ${fetchRequests.projectId} = ${projects.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),415 })416 .from(projects)417 .innerJoin(organizations, eq(organizations.id, projects.organizationId))418 .where(where)419 .orderBy(desc(projects.createdAt))420 .limit(pageSize)421 .offset((page - 1) * pageSize);422 const [t] = await db.select({ total: count() }).from(projects).innerJoin(organizations, eq(organizations.id, projects.organizationId)).where(where);423 return paged(rows, t?.total ?? 0, page, pageSize);424}425426// ---------------------------------------------------------------------------427// Requests explorer428// ---------------------------------------------------------------------------429export interface RequestFilters {430 org?: string;431 project?: string;432 domain?: string;433 status?: string;434 provider?: string;435 network?: string;436 country?: string;437 source?: string;438 errorCode?: string;439 from?: string;440 to?: string;441 id?: string;442 page?: number;443 pageSize?: number;444}445446export function requestFiltersFromParams(sp: Record<string, string | string[] | undefined>): RequestFilters {447 return {448 org: str(sp.org) || undefined,449 project: str(sp.project) || undefined,450 domain: str(sp.domain) || undefined,451 status: str(sp.status) || undefined,452 provider: str(sp.provider) || undefined,453 network: str(sp.network) || undefined,454 country: str(sp.country).toUpperCase() || undefined,455 source: str(sp.source) || undefined,456 errorCode: str(sp.errorCode) || undefined,457 from: str(sp.from) || undefined,458 to: str(sp.to) || undefined,459 id: str(sp.id) || undefined,460 page: pageNum(sp.page),461 };462}463464function requestConditions(f: RequestFilters): SQL | undefined {465 const conds: SQL[] = [];466 if (f.id) conds.push(eq(fetchRequests.id, f.id));467 if (f.org) conds.push(eq(fetchRequests.organizationId, f.org));468 if (f.project) conds.push(eq(fetchRequests.projectId, f.project));469 if (f.domain) conds.push(ilike(fetchRequests.domain, `%${f.domain}%`));470 if (f.status) conds.push(eq(fetchRequests.status, f.status));471 if (f.network) conds.push(eq(fetchRequests.network, f.network));472 if (f.country) conds.push(eq(fetchRequests.country, f.country));473 if (f.source) conds.push(eq(fetchRequests.source, f.source));474 if (f.errorCode) conds.push(eq(fetchRequests.errorCode, f.errorCode));475 if (f.from) {476 const d = new Date(f.from);477 if (!Number.isNaN(d.getTime())) conds.push(gte(fetchRequests.createdAt, d));478 }479 if (f.to) {480 const d = new Date(f.to);481 if (!Number.isNaN(d.getTime())) conds.push(lte(fetchRequests.createdAt, d));482 }483 if (f.provider) {484 conds.push(inArray(fetchRequests.id, getDb().select({ id: requestAttempts.requestId }).from(requestAttempts).where(eq(requestAttempts.provider, f.provider))));485 }486 return conds.length ? and(...conds) : undefined;487}488489export async function listRequests(f: RequestFilters) {490 const db = getDb();491 const page = f.page ?? 1;492 const pageSize = f.pageSize ?? 50;493 const where = requestConditions(f);494 const rows = await db495 .select({496 id: fetchRequests.id,497 organizationId: fetchRequests.organizationId,498 orgName: organizations.name,499 projectId: fetchRequests.projectId,500 projectName: projects.name,501 source: fetchRequests.source,502 domain: fetchRequests.domain,503 method: fetchRequests.method,504 status: fetchRequests.status,505 httpStatus: fetchRequests.httpStatus,506 errorCode: fetchRequests.errorCode,507 requestedNetwork: fetchRequests.requestedNetwork,508 network: fetchRequests.network,509 country: fetchRequests.country,510 attempts: fetchRequests.attempts,511 latencyMs: fetchRequests.latencyMs,512 bytesIn: fetchRequests.bytesIn,513 costUsd: fetchRequests.costUsd,514 priceUsd: fetchRequests.priceUsd,515 createdAt: fetchRequests.createdAt,516 providers: sql<string | null>`(select string_agg(${requestAttempts.provider}, ',' order by ${requestAttempts.attemptNo}) from ${requestAttempts} where ${requestAttempts.requestId} = ${fetchRequests.id})`,517 })518 .from(fetchRequests)519 .leftJoin(organizations, eq(organizations.id, fetchRequests.organizationId))520 .leftJoin(projects, eq(projects.id, fetchRequests.projectId))521 .where(where)522 .orderBy(desc(fetchRequests.createdAt))523 .limit(pageSize)524 .offset((page - 1) * pageSize);525 const [t] = await db.select({ total: count() }).from(fetchRequests).where(where);526 return paged(rows, t?.total ?? 0, page, pageSize);527}528529export async function getRequestDetail(id: string) {530 const db = getDb();531 const [req] = await db.select().from(fetchRequests).where(eq(fetchRequests.id, id)).limit(1);532 if (!req) return null;533 const [org] = await db.select({ id: organizations.id, name: organizations.name, plan: organizations.plan, providerVisibility: organizations.providerVisibility }).from(organizations).where(eq(organizations.id, req.organizationId)).limit(1);534 const [project] = await db.select({ id: projects.id, name: projects.name, environment: projects.environment }).from(projects).where(eq(projects.id, req.projectId)).limit(1);535 const key = req.apiKeyId536 ? (await db.select({ id: apiKeys.id, name: apiKeys.name, keyPrefix: apiKeys.keyPrefix, last4: apiKeys.last4, mode: apiKeys.mode }).from(apiKeys).where(eq(apiKeys.id, req.apiKeyId)).limit(1))[0] ?? null537 : null;538 const attempts = await db.select().from(requestAttempts).where(eq(requestAttempts.requestId, id)).orderBy(asc(requestAttempts.attemptNo));539 const usage = await db.select().from(usageEvents).where(eq(usageEvents.requestId, id)).orderBy(asc(usageEvents.createdAt));540 const session = req.sessionId ? (await db.select().from(proxySessions).where(eq(proxySessions.id, req.sessionId)).limit(1))[0] ?? null : null;541 return { req, org: org ?? null, project: project ?? null, key, attempts, usage, session };542}543544/** Distinct filter values for the explorer selects. */545export async function getRequestFilterOptions() {546 const db = getDb();547 const orgs = await db.select({ id: organizations.id, name: organizations.name }).from(organizations).orderBy(asc(organizations.name)).limit(500);548 const errorCodes = await db549 .select({ code: fetchRequests.errorCode })550 .from(fetchRequests)551 .where(sql`${fetchRequests.errorCode} is not null`)552 .groupBy(fetchRequests.errorCode)553 .orderBy(asc(fetchRequests.errorCode));554 return { orgs, errorCodes: errorCodes.map((e) => e.code!).filter(Boolean) };555}556557// ---------------------------------------------------------------------------558// Usage / unit economics559// ---------------------------------------------------------------------------560export interface EconRow {561 key: string;562 requests: number;563 successes: number;564 revenue: number;565 cost: number;566 margin: number;567 marginPct: number | null;568 bytes: number;569}570571function econ<T extends { key: string; requests: number; successes: number; revenue: number; cost: number; bytes: number }>(r: T): EconRow {572 const margin = r.revenue - r.cost;573 return { key: r.key, requests: r.requests, successes: r.successes, revenue: r.revenue, cost: r.cost, margin, marginPct: r.revenue > 0 ? (margin / r.revenue) * 100 : null, bytes: r.bytes };574}575576const econFields = {577 requests: count(),578 successes: sumCase(sql`${fetchRequests.status} = 'success'`),579 revenue: num(sql`sum(${fetchRequests.priceUsd})`),580 cost: num(sql`sum(${fetchRequests.costUsd})`),581 bytes: num(sql`sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut})`),582};583584export async function getEconomics(range: Range) {585 const db = getDb();586 const since = rangeStart(range);587 const unit = range === "24h" ? "hour" : "day";588 const bucket = sql<string>`date_trunc(${sql.raw(`'${unit}'`)}, ${fetchRequests.createdAt})`;589 const byBucketRaw = await db590 .select({ key: sql<string>`to_char(${bucket}, 'YYYY-MM-DD"T"HH24:MI:SS')`, ...econFields })591 .from(fetchRequests)592 .where(gte(fetchRequests.createdAt, since))593 .groupBy(bucket)594 .orderBy(bucket);595 const byPlanRaw = await db596 .select({ key: organizations.plan, ...econFields })597 .from(fetchRequests)598 .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))599 .where(gte(fetchRequests.createdAt, since))600 .groupBy(organizations.plan);601 const byNetworkRaw = await db602 .select({ key: sql<string>`coalesce(${fetchRequests.network}, 'unresolved')`, ...econFields })603 .from(fetchRequests)604 .where(gte(fetchRequests.createdAt, since))605 .groupBy(sql`coalesce(${fetchRequests.network}, 'unresolved')`);606 const byProviderRaw = await db607 .select({608 key: requestAttempts.provider,609 attempts: count(),610 successes: sumCase(sql`${requestAttempts.outcome} = 'success'`),611 cost: num(sql`sum(${requestAttempts.costUsd})`),612 bytes: num(sql`sum(${requestAttempts.bytesIn} + ${requestAttempts.bytesOut})`),613 })614 .from(requestAttempts)615 .where(gte(requestAttempts.createdAt, since))616 .groupBy(requestAttempts.provider);617 const providerRevenue = await db618 .select({ key: requestAttempts.provider, revenue: num(sql`sum(${fetchRequests.priceUsd})`) })619 .from(requestAttempts)620 .innerJoin(fetchRequests, eq(fetchRequests.id, requestAttempts.requestId))621 .where(and(gte(requestAttempts.createdAt, since), eq(requestAttempts.outcome, "success")))622 .groupBy(requestAttempts.provider);623 const revMap = new Map(providerRevenue.map((r) => [r.key, r.revenue]));624 const topCustomersRaw = await db625 .select({ key: fetchRequests.organizationId, name: organizations.name, plan: organizations.plan, ...econFields })626 .from(fetchRequests)627 .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))628 .where(gte(fetchRequests.createdAt, since))629 .groupBy(fetchRequests.organizationId, organizations.name, organizations.plan)630 .orderBy(desc(num(sql`sum(${fetchRequests.priceUsd})`)), desc(count()))631 .limit(20);632 return {633 unit,634 byBucket: byBucketRaw.map(econ),635 byPlan: byPlanRaw.map(econ).sort((a, b) => PLANS.indexOf(normalizePlan(a.key)) - PLANS.indexOf(normalizePlan(b.key)) || a.key.localeCompare(b.key)),636 byNetwork: byNetworkRaw.map(econ),637 byProvider: byProviderRaw.map((r) => ({638 provider: r.key,639 attempts: r.attempts,640 successes: r.successes,641 cost: r.cost,642 bytes: r.bytes,643 attributedRevenue: revMap.get(r.key) ?? 0,644 costPerSuccess: r.successes ? r.cost / r.successes : null,645 })),646 topCustomers: topCustomersRaw.map((r) => ({ ...econ(r), name: r.name, plan: r.plan })),647 };648}649650// ---------------------------------------------------------------------------651// Providers652// ---------------------------------------------------------------------------653export async function getProviderConfigs() {654 return getDb().select().from(providerConfigs).orderBy(asc(providerConfigs.id));655}656657export async function getProviderHealthHistory(limit = 50) {658 return getDb().select().from(providerHealth).orderBy(desc(providerHealth.checkedAt)).limit(limit);659}660661// ---------------------------------------------------------------------------662// Routing analytics663// ---------------------------------------------------------------------------664export interface RouteMatrixRow {665 provider: string;666 network: string;667 requests: number;668 successes: number;669 blocked: number;670 errors: number;671 successRate: number | null;672 avgLatency: number | null;673 bytes: number;674 cost: number;675 costPerSuccess: number | null;676}677678export async function getRoutingAnalytics(range: Range) {679 const db = getDb();680 const since = rangeStart(range);681 const matrixRaw = await db682 .select({683 provider: routingMetrics.provider,684 network: routingMetrics.network,685 requests: num(sql`sum(${routingMetrics.requests})`),686 successes: num(sql`sum(${routingMetrics.successes})`),687 blocked: num(sql`sum(${routingMetrics.blocked})`),688 errors: num(sql`sum(${routingMetrics.errors})`),689 latencySum: num(sql`sum(${routingMetrics.latencySumMs})`),690 bytes: num(sql`sum(${routingMetrics.bytes})`),691 cost: num(sql`sum(${routingMetrics.costUsd})`),692 })693 .from(routingMetrics)694 .where(gte(routingMetrics.bucket, since))695 .groupBy(routingMetrics.provider, routingMetrics.network)696 .orderBy(asc(routingMetrics.provider), asc(routingMetrics.network));697 const matrix: RouteMatrixRow[] = matrixRaw.map((r) => ({698 provider: r.provider,699 network: r.network,700 requests: r.requests,701 successes: r.successes,702 blocked: r.blocked,703 errors: r.errors,704 successRate: pct(r.successes, r.requests),705 avgLatency: r.requests ? r.latencySum / r.requests : null,706 bytes: r.bytes,707 cost: r.cost,708 costPerSuccess: r.successes ? r.cost / r.successes : null,709 }));710 const unit = range === "24h" ? "hour" : "day";711 const bucket = sql<string>`date_trunc(${sql.raw(`'${unit}'`)}, ${routingMetrics.bucket})`;712 const seriesRaw = await db713 .select({714 t: sql<string>`to_char(${bucket}, 'YYYY-MM-DD"T"HH24:MI:SS')`,715 provider: routingMetrics.provider,716 requests: num(sql`sum(${routingMetrics.requests})`),717 successes: num(sql`sum(${routingMetrics.successes})`),718 cost: num(sql`sum(${routingMetrics.costUsd})`),719 })720 .from(routingMetrics)721 .where(gte(routingMetrics.bucket, since))722 .groupBy(bucket, routingMetrics.provider)723 .orderBy(bucket);724 const providers = Array.from(new Set(seriesRaw.map((s) => s.provider))).sort();725 const byT = new Map<string, Record<string, number | string>>();726 for (const s of seriesRaw) {727 const row = byT.get(s.t) ?? { t: s.t };728 row[`${s.provider}:requests`] = s.requests;729 row[`${s.provider}:successes`] = s.successes;730 row[`${s.provider}:cost`] = s.cost;731 byT.set(s.t, row);732 }733 return { matrix, series: Array.from(byT.values()), providers, unit };734}735736// ---------------------------------------------------------------------------737// Domains738// ---------------------------------------------------------------------------739export const DOMAIN_SORTS = ["requests", "domain", "successRate", "blockRate", "captchaRate", "avgLatencyMs", "lastSeenAt"] as const;740export type DomainSort = (typeof DOMAIN_SORTS)[number];741742export async function listDomains(opts: { q?: string; sort?: string; dir?: string; page?: number; pageSize?: number }) {743 const db = getDb();744 const page = opts.page ?? 1;745 const pageSize = opts.pageSize ?? 50;746 const sort: DomainSort = (DOMAIN_SORTS as readonly string[]).includes(opts.sort ?? "") ? (opts.sort as DomainSort) : "requests";747 const dir = opts.dir === "asc" ? "asc" : "desc";748 const where = opts.q ? ilike(domainProfiles.domain, `%${opts.q}%`) : undefined;749 const successRate = sql<number>`case when ${domainProfiles.requests} > 0 then ${domainProfiles.successes}::float / ${domainProfiles.requests} * 100 else null end`;750 const blockRate = sql<number>`case when ${domainProfiles.requests} > 0 then ${domainProfiles.blocks}::float / ${domainProfiles.requests} * 100 else null end`;751 const captchaRate = sql<number>`case when ${domainProfiles.requests} > 0 then ${domainProfiles.captchas}::float / ${domainProfiles.requests} * 100 else null end`;752 const sortExpr: Record<DomainSort, SQL | typeof domainProfiles.domain> = {753 requests: sql`${domainProfiles.requests}`,754 domain: domainProfiles.domain,755 successRate,756 blockRate,757 captchaRate,758 avgLatencyMs: sql`${domainProfiles.avgLatencyMs}`,759 lastSeenAt: sql`${domainProfiles.lastSeenAt}`,760 };761 const orderBy = dir === "asc" ? sql`${sortExpr[sort]} asc nulls last` : sql`${sortExpr[sort]} desc nulls last`;762 const rows = await db763 .select({764 domain: domainProfiles.domain,765 requests: domainProfiles.requests,766 successes: domainProfiles.successes,767 blocks: domainProfiles.blocks,768 captchas: domainProfiles.captchas,769 browserRequired: domainProfiles.browserRequired,770 avgLatencyMs: domainProfiles.avgLatencyMs,771 preferredNetwork: domainProfiles.preferredNetwork,772 preferredProvider: domainProfiles.preferredProvider,773 policy: domainProfiles.policy,774 lastSeenAt: domainProfiles.lastSeenAt,775 successRate: successRate.mapWith((v) => (v === null ? null : Number(v))),776 blockRate: blockRate.mapWith((v) => (v === null ? null : Number(v))),777 captchaRate: captchaRate.mapWith((v) => (v === null ? null : Number(v))),778 })779 .from(domainProfiles)780 .where(where)781 .orderBy(orderBy)782 .limit(pageSize)783 .offset((page - 1) * pageSize);784 const [t] = await db.select({ total: count() }).from(domainProfiles).where(where);785 return { ...paged(rows, t?.total ?? 0, page, pageSize), sort, dir };786}787788export async function getDomainProfile(domain: string) {789 const [row] = await getDb().select().from(domainProfiles).where(eq(domainProfiles.domain, domain)).limit(1);790 if (!row) return null;791 const since = rangeStart("7d");792 const recent = await getDb()793 .select({794 id: fetchRequests.id,795 status: fetchRequests.status,796 httpStatus: fetchRequests.httpStatus,797 errorCode: fetchRequests.errorCode,798 network: fetchRequests.network,799 attempts: fetchRequests.attempts,800 latencyMs: fetchRequests.latencyMs,801 createdAt: fetchRequests.createdAt,802 orgName: organizations.name,803 })804 .from(fetchRequests)805 .leftJoin(organizations, eq(organizations.id, fetchRequests.organizationId))806 .where(and(eq(fetchRequests.domain, domain), gte(fetchRequests.createdAt, since)))807 .orderBy(desc(fetchRequests.createdAt))808 .limit(20);809 return { profile: row, recent };810}811812// ---------------------------------------------------------------------------813// Billing814// ---------------------------------------------------------------------------815export async function getBillingOverview() {816 const db = getDb();817 const subs = await db818 .select({819 id: subscriptions.id,820 organizationId: subscriptions.organizationId,821 orgName: organizations.name,822 plan: subscriptions.plan,823 status: subscriptions.status,824 stripeSubscriptionId: subscriptions.stripeSubscriptionId,825 currentPeriodStart: subscriptions.currentPeriodStart,826 currentPeriodEnd: subscriptions.currentPeriodEnd,827 cancelAtPeriodEnd: subscriptions.cancelAtPeriodEnd,828 createdAt: subscriptions.createdAt,829 })830 .from(subscriptions)831 .leftJoin(organizations, eq(organizations.id, subscriptions.organizationId))832 .orderBy(desc(subscriptions.createdAt))833 .limit(100);834 const events = await db835 .select({ id: billingEvents.id, organizationId: billingEvents.organizationId, orgName: organizations.name, type: billingEvents.type, stripeEventId: billingEvents.stripeEventId, createdAt: billingEvents.createdAt })836 .from(billingEvents)837 .leftJoin(organizations, eq(organizations.id, billingEvents.organizationId))838 .orderBy(desc(billingEvents.createdAt))839 .limit(100);840 const dist = await db.select({ plan: organizations.plan, n: count() }).from(organizations).groupBy(organizations.plan);841 // Single plan: every stored value (including legacy ones) normalizes to it, so nothing is "unknown".842 const distribution = PLANS.map((p) => {843 const n = dist.filter((d) => normalizePlan(d.plan) === p).reduce((s, d) => s + Number(d.n ?? 0), 0);844 const price = PLAN_LIMITS[p].price_usd_month;845 return { plan: p, label: PLAN_LIMITS[p].label, orgs: n, price, mrr: n * price };846 });847 /** Legacy plan values still present in `organizations.plan` (should be empty after migration 0001). */848 const unknown = dist.filter((d) => !(PLANS as readonly string[]).includes(d.plan)).map((d) => ({ plan: d.plan, n: Number(d.n ?? 0) }));849 const mrr = distribution.reduce((s, d) => s + d.mrr, 0);850 const [stripe] = await db.select({ n: count() }).from(organizations).where(sql`${organizations.stripeCustomerId} is not null`);851 const since = rangeStart("30d");852 const [usage30] = await db853 .select({ revenue: num(sql`sum(${fetchRequests.priceUsd})`), cost: num(sql`sum(${fetchRequests.costUsd})`) })854 .from(fetchRequests)855 .where(gte(fetchRequests.createdAt, since));856 return { subs, events, distribution, unknown, mrr, stripeCustomers: stripe?.n ?? 0, usageRevenue30d: usage30?.revenue ?? 0, usageCost30d: usage30?.cost ?? 0, stripeConfigured: Boolean(process.env.STRIPE_SECRET_KEY) };857}858859// ---------------------------------------------------------------------------860// Access (signup allowlist)861// ---------------------------------------------------------------------------862export type AllowlistStatus = "account" | "invited" | "pending";863864export interface AllowlistRow {865 email: string;866 note: string | null;867 invitedByUserId: string | null;868 invitedByEmail: string | null;869 invitedAt: Date | null;870 usedAt: Date | null;871 userId: string | null;872 /** Email of the account that consumed the invitation (join on users), when it exists. */873 accountEmail: string | null;874 accountBanned: boolean | null;875 createdAt: Date;876 status: AllowlistStatus;877}878879/** Whole allowlist (it is small by construction), newest first, with inviter and account information. */880export async function listAllowlist(opts: { q?: string } = {}): Promise<AllowlistRow[]> {881 const db = getDb();882 const inviter = alias(users, "inviter");883 const account = alias(users, "account");884 const where = opts.q ? or(ilike(signupAllowlist.email, `%${opts.q}%`), ilike(signupAllowlist.note, `%${opts.q}%`)) : undefined;885 const rows = await db886 .select({887 email: signupAllowlist.email,888 note: signupAllowlist.note,889 invitedByUserId: signupAllowlist.invitedByUserId,890 invitedByEmail: inviter.email,891 invitedAt: signupAllowlist.invitedAt,892 usedAt: signupAllowlist.usedAt,893 userId: signupAllowlist.userId,894 accountEmail: account.email,895 accountBanned: account.banned,896 createdAt: signupAllowlist.createdAt,897 })898 .from(signupAllowlist)899 .leftJoin(inviter, eq(inviter.id, signupAllowlist.invitedByUserId))900 .leftJoin(account, eq(account.id, signupAllowlist.userId))901 .where(where)902 .orderBy(desc(signupAllowlist.createdAt))903 .limit(2000);904 return rows.map((r) => ({905 ...r,906 status: r.userId || r.usedAt ? "account" : r.invitedAt ? "invited" : "pending",907 }));908}909910export async function getAllowlistCounts(): Promise<{ total: number; accounts: number; invited: number; pending: number }> {911 const [r] = await getDb()912 .select({913 total: count(),914 accounts: sumCase(sql`${signupAllowlist.userId} is not null or ${signupAllowlist.usedAt} is not null`),915 invited: sumCase(sql`${signupAllowlist.userId} is null and ${signupAllowlist.usedAt} is null and ${signupAllowlist.invitedAt} is not null`),916 })917 .from(signupAllowlist);918 const total = r?.total ?? 0;919 const accounts = r?.accounts ?? 0;920 const invited = r?.invited ?? 0;921 return { total, accounts, invited, pending: Math.max(0, total - accounts - invited) };922}923924// ---------------------------------------------------------------------------925// Abuse926// ---------------------------------------------------------------------------927export async function listAbuseEvents(opts: { resolved?: "all" | "open" | "resolved"; limit?: number }) {928 const db = getDb();929 const where = opts.resolved === "open" ? eq(abuseEvents.resolved, false) : opts.resolved === "resolved" ? eq(abuseEvents.resolved, true) : undefined;930 return db931 .select({932 id: abuseEvents.id,933 organizationId: abuseEvents.organizationId,934 orgName: organizations.name,935 projectId: abuseEvents.projectId,936 requestId: abuseEvents.requestId,937 kind: abuseEvents.kind,938 severity: abuseEvents.severity,939 detail: abuseEvents.detail,940 resolved: abuseEvents.resolved,941 createdAt: abuseEvents.createdAt,942 })943 .from(abuseEvents)944 .leftJoin(organizations, eq(organizations.id, abuseEvents.organizationId))945 .where(where)946 .orderBy(asc(abuseEvents.resolved), desc(abuseEvents.createdAt))947 .limit(opts.limit ?? 200);948}949950export async function getSuspiciousActivity() {951 const db = getDb();952 const since = rangeStart("24h");953 const ssrf = await db954 .select({955 organizationId: fetchRequests.organizationId,956 orgName: organizations.name,957 plan: organizations.plan,958 total: count(),959 notAllowed: sumCase(sql`${fetchRequests.errorCode} = 'URL_NOT_ALLOWED'`),960 })961 .from(fetchRequests)962 .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))963 .where(gte(fetchRequests.createdAt, since))964 .groupBy(fetchRequests.organizationId, organizations.name, organizations.plan)965 .having(sql`count(*) >= 5 and sum(case when ${fetchRequests.errorCode} = 'URL_NOT_ALLOWED' then 1 else 0 end)::float / count(*) > 0.3`)966 .orderBy(desc(count()))967 .limit(20);968 const limited = await db969 .select({970 organizationId: fetchRequests.organizationId,971 orgName: organizations.name,972 plan: organizations.plan,973 rateLimited: sumCase(sql`${fetchRequests.errorCode} = 'RATE_LIMITED'`),974 concurrency: sumCase(sql`${fetchRequests.errorCode} = 'CONCURRENCY_LIMIT'`),975 total: count(),976 })977 .from(fetchRequests)978 .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))979 .where(and(gte(fetchRequests.createdAt, since), inArray(fetchRequests.errorCode, ["RATE_LIMITED", "CONCURRENCY_LIMIT"])))980 .groupBy(fetchRequests.organizationId, organizations.name, organizations.plan)981 .orderBy(desc(count()))982 .limit(20);983 const unverified = await db984 .select({985 userId: users.id,986 email: users.email,987 organizationId: organizations.id,988 orgName: organizations.name,989 playground: count(),990 createdAt: users.createdAt,991 })992 .from(fetchRequests)993 .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))994 .innerJoin(users, eq(users.id, organizations.ownerUserId))995 .where(and(gte(fetchRequests.createdAt, since), eq(fetchRequests.source, "playground"), eq(users.emailVerified, false)))996 .groupBy(users.id, users.email, organizations.id, organizations.name, users.createdAt)997 .having(sql`count(*) >= 20`)998 .orderBy(desc(count()))999 .limit(20);1000 return {1001 ssrf: ssrf.map((r) => ({ ...r, ratio: pct(r.notAllowed, r.total) })),1002 limited,1003 unverified,1004 };1005}10061007// ---------------------------------------------------------------------------1008// System1009// ---------------------------------------------------------------------------1010export async function getTableCounts() {1011 const db = getDb();1012 const [r] = await db.select({1013 users: sql<number>`(select count(*) from ${users})`.mapWith(Number),1014 sessions: sql<number>`(select count(*) from ${sessions})`.mapWith(Number),1015 organizations: sql<number>`(select count(*) from ${organizations})`.mapWith(Number),1016 projects: sql<number>`(select count(*) from ${projects})`.mapWith(Number),1017 api_keys: sql<number>`(select count(*) from ${apiKeys})`.mapWith(Number),1018 fetch_requests: sql<number>`(select count(*) from ${fetchRequests})`.mapWith(Number),1019 request_attempts: sql<number>`(select count(*) from ${requestAttempts})`.mapWith(Number),1020 proxy_sessions: sql<number>`(select count(*) from ${proxySessions})`.mapWith(Number),1021 usage_events: sql<number>`(select count(*) from ${usageEvents})`.mapWith(Number),1022 domain_profiles: sql<number>`(select count(*) from ${domainProfiles})`.mapWith(Number),1023 routing_metrics: sql<number>`(select count(*) from ${routingMetrics})`.mapWith(Number),1024 provider_health: sql<number>`(select count(*) from ${providerHealth})`.mapWith(Number),1025 audit_logs: sql<number>`(select count(*) from ${auditLogs})`.mapWith(Number),1026 abuse_events: sql<number>`(select count(*) from ${abuseEvents})`.mapWith(Number),1027 webhooks: sql<number>`(select count(*) from ${webhooks})`.mapWith(Number),1028 subscriptions: sql<number>`(select count(*) from ${subscriptions})`.mapWith(Number),1029 billing_events: sql<number>`(select count(*) from ${billingEvents})`.mapWith(Number),1030 feature_flags: sql<number>`(select count(*) from ${featureFlags})`.mapWith(Number),1031 status_incidents: sql<number>`(select count(*) from ${statusIncidents})`.mapWith(Number),1032 }).from(sql`(select 1) as one`);1033 const [size] = await db.select({ size: sql<string>`pg_size_pretty(pg_database_size(current_database()))`, version: sql<string>`version()` }).from(sql`(select 1) as one`);1034 return { counts: r!, dbSize: size?.size ?? "—", pgVersion: size?.version?.split(" on ")[0] ?? "—" };1035}10361037export async function getFeatureFlags() {1038 return getDb().select().from(featureFlags).orderBy(asc(featureFlags.key));1039}10401041export async function getIncidents(limit = 50) {1042 return getDb().select().from(statusIncidents).orderBy(sql`${statusIncidents.resolvedAt} is not null`, desc(statusIncidents.startedAt)).limit(limit);1043}10441045/** Boolean presence of env vars in the web process. Never returns values. */1046export function getEnvPresence(): Array<{ group: string; key: string; present: boolean }> {1047 const spec: Array<[string, string[]]> = [1048 ["Upstream providers", ["OXYLABS_USERNAME", "OXYLABS_PASSWORD", "DECODO_USERNAME", "DECODO_PASSWORD", "SOAX_USERNAME", "SOAX_PASSWORD"]],1049 ["Core", ["DATABASE_URL", "REDIS_URL", "AUTH_SECRET", "INTERNAL_SERVICE_TOKEN", "API_URL", "NEXT_PUBLIC_SITE_URL", "ADMIN_EMAILS"]],1050 ["Email", ["RESEND_API_KEY", "EMAIL_FROM", "EMAIL_FROM_TRANSACTIONAL"]],1051 ["Anti-bot", ["TWOCAPTCHA_API_KEY", "FETCHA_BROWSER_ENABLED", "FETCHA_BROWSER_CHANNEL", "FETCHA_BROWSER_HEADLESS"]],1052 ["Billing", ["STRIPE_SECRET_KEY", "STRIPE_WEBHOOK_SECRET"]],1053 ];1054 return spec.flatMap(([group, keys]) => keys.map((key) => ({ group, key, present: Boolean(process.env[key]?.trim()) })));1055}1056