TypeScript 97.5%
SQL 1.4%
Python 0.8%
1import "server-only";2import { getDb, fetchRequests, requestAttempts, apiKeys, projects, proxySessions, usageEvents, eq, and, desc, sql, gte, lt, isNull, isNotNull, count, ilike, gt } from "@fetcha/db";3import type { SQL } from "drizzle-orm";4import { REQUEST_NETWORKS, filterWindow, type RequestFilters } from "@/lib/requests-filters";56/**7 * Drizzle queries backing the customer dashboard (Overview, Requests, Sessions, Usage, Analytics).8 * Every query is scoped to the caller's organization AND current project. Upstream data9 * (`costUsd`, `upstreamCostUsd`, attempt `provider`/`errorDetail`, session `provider`/`stickyKey`)10 * is never selected unless explicitly allowed by `organization.providerVisibility`.11 */1213export interface Scope {14 organizationId: string;15 projectId: string;16}1718const num = (v: unknown): number => (v === null || v === undefined ? 0 : Number(v));19const numOrNull = (v: unknown): number | null => (v === null || v === undefined ? null : Number(v));2021function scoped(scope: Scope): SQL {22 return and(eq(fetchRequests.organizationId, scope.organizationId), eq(fetchRequests.projectId, scope.projectId))!;23}2425function monthStart(d = new Date()): Date {26 return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), 1));27}2829// ---------------------------------------------------------------------------30// Aggregates31// ---------------------------------------------------------------------------3233export interface RequestStats {34 total: number;35 successful: number;36 failed: number;37 pending: number;38 successRate: number | null;39 bytes: number;40 spendUsd: number;41 avgLatencyMs: number | null;42 p50: number | null;43 p95: number | null;44 p99: number | null;45 avgAttempts: number | null;46}4748/** Aggregate stats for a time window (inclusive from, exclusive to). */49export async function getRequestStats(scope: Scope, from: Date, to: Date | null = null): Promise<RequestStats> {50 const db = getDb();51 const [row] = await db52 .select({53 total: count(),54 successful: sql`count(*) filter (where ${fetchRequests.status} = 'success')`.mapWith(num),55 failed: sql`count(*) filter (where ${fetchRequests.status} = 'failed')`.mapWith(num),56 pending: sql`count(*) filter (where ${fetchRequests.status} = 'pending')`.mapWith(num),57 bytes: sql`coalesce(sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut}), 0)`.mapWith(num),58 spendUsd: sql`coalesce(sum(${fetchRequests.priceUsd}), 0)`.mapWith(num),59 avgLatencyMs: sql`avg(${fetchRequests.latencyMs})`.mapWith(numOrNull),60 p50: sql`percentile_cont(0.5) within group (order by ${fetchRequests.latencyMs})`.mapWith(numOrNull),61 p95: sql`percentile_cont(0.95) within group (order by ${fetchRequests.latencyMs})`.mapWith(numOrNull),62 p99: sql`percentile_cont(0.99) within group (order by ${fetchRequests.latencyMs})`.mapWith(numOrNull),63 avgAttempts: sql`avg(${fetchRequests.attempts}) filter (where ${fetchRequests.attempts} > 0)`.mapWith(numOrNull),64 })65 .from(fetchRequests)66 .where(and(scoped(scope), gte(fetchRequests.createdAt, from), to ? lt(fetchRequests.createdAt, to) : undefined));67 const r = row!;68 const decided = r.successful + r.failed;69 return { ...r, successRate: decided > 0 ? (r.successful / decided) * 100 : null };70}7172export async function getMonthStats(scope: Scope, month = monthStart()): Promise<RequestStats> {73 const next = new Date(Date.UTC(month.getUTCFullYear(), month.getUTCMonth() + 1, 1));74 return getRequestStats(scope, month, next);75}7677export interface SeriesPoint {78 /** ISO timestamp of the bucket start (UTC). */79 t: string;80 success: number;81 failed: number;82 p50: number | null;83 p95: number | null;84}8586/** Requests + latency percentiles bucketed by hour or day, with empty buckets filled in. */87export async function getRequestSeries(scope: Scope, from: Date, bucket: "hour" | "day", to: Date = new Date()): Promise<SeriesPoint[]> {88 const db = getDb();89 // `bucket` is a closed union, inlined (not bound) so SELECT and GROUP BY use the identical expression.90 const trunc = sql`date_trunc(${sql.raw(bucket === "hour" ? "'hour'" : "'day'")}, ${fetchRequests.createdAt} at time zone 'UTC')`;91 const rows = await db92 .select({93 t: sql<string>`to_char(${trunc}, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')`,94 success: sql`count(*) filter (where ${fetchRequests.status} = 'success')`.mapWith(num),95 failed: sql`count(*) filter (where ${fetchRequests.status} = 'failed')`.mapWith(num),96 p50: sql`percentile_cont(0.5) within group (order by ${fetchRequests.latencyMs})`.mapWith(numOrNull),97 p95: sql`percentile_cont(0.95) within group (order by ${fetchRequests.latencyMs})`.mapWith(numOrNull),98 })99 .from(fetchRequests)100 .where(and(scoped(scope), gte(fetchRequests.createdAt, from), lt(fetchRequests.createdAt, to)))101 .groupBy(trunc)102 .orderBy(trunc);103 const byKey = new Map(rows.map((r) => [r.t, r]));104 const step = bucket === "hour" ? 3_600_000 : 86_400_000;105 const start = bucket === "hour" ? Date.UTC(from.getUTCFullYear(), from.getUTCMonth(), from.getUTCDate(), from.getUTCHours()) : Date.UTC(from.getUTCFullYear(), from.getUTCMonth(), from.getUTCDate());106 const out: SeriesPoint[] = [];107 for (let ts = start; ts < to.getTime(); ts += step) {108 const key = new Date(ts).toISOString().replace(/\.\d{3}Z$/, "Z");109 const r = byKey.get(key);110 out.push({ t: key, success: r?.success ?? 0, failed: r?.failed ?? 0, p50: r?.p50 ?? null, p95: r?.p95 ?? null });111 }112 return out;113}114115export interface NetworkShare {116 network: (typeof REQUEST_NETWORKS)[number];117 requests: number;118 percent: number;119}120121/** Distribution of resolved network classes. Always returns all four classes (0 % rows included). */122export async function getNetworkDistribution(scope: Scope, from: Date): Promise<{ shares: NetworkShare[]; resolved: number; unresolved: number }> {123 const db = getDb();124 const rows = await db125 .select({ network: fetchRequests.network, requests: count() })126 .from(fetchRequests)127 .where(and(scoped(scope), gte(fetchRequests.createdAt, from)))128 .groupBy(fetchRequests.network);129 const byNet = new Map<string | null, number>(rows.map((r) => [r.network, r.requests]));130 const resolved = rows.filter((r) => r.network && (REQUEST_NETWORKS as readonly string[]).includes(r.network)).reduce((a, r) => a + r.requests, 0);131 const unresolved = rows.filter((r) => !r.network || !(REQUEST_NETWORKS as readonly string[]).includes(r.network)).reduce((a, r) => a + r.requests, 0);132 const shares = REQUEST_NETWORKS.map((network) => {133 const requests = byNet.get(network) ?? 0;134 return { network, requests, percent: resolved > 0 ? (requests / resolved) * 100 : 0 };135 });136 return { shares, resolved, unresolved };137}138139export async function getActiveSessionCount(scope: Scope): Promise<number> {140 const db = getDb();141 const [row] = await db142 .select({ n: count() })143 .from(proxySessions)144 .where(and(eq(proxySessions.organizationId, scope.organizationId), eq(proxySessions.projectId, scope.projectId), eq(proxySessions.status, "active"), gt(proxySessions.expiresAt, new Date())));145 return row?.n ?? 0;146}147148export interface OnboardingState {149 hasApiKey: boolean;150 hasRequest: boolean;151}152153export async function getOnboardingState(scope: Scope): Promise<OnboardingState> {154 const db = getDb();155 const [key] = await db156 .select({ id: apiKeys.id })157 .from(apiKeys)158 .where(and(eq(apiKeys.organizationId, scope.organizationId), eq(apiKeys.projectId, scope.projectId), isNull(apiKeys.revokedAt)))159 .limit(1);160 const [req] = await db.select({ id: fetchRequests.id }).from(fetchRequests).where(scoped(scope)).limit(1);161 return { hasApiKey: Boolean(key), hasRequest: Boolean(req) };162}163164/** True when the project has at least one request ever (used to tell "no data yet" from "no match"). */165export async function projectHasRequests(scope: Scope): Promise<boolean> {166 const db = getDb();167 const [req] = await db.select({ id: fetchRequests.id }).from(fetchRequests).where(scoped(scope)).limit(1);168 return Boolean(req);169}170171// ---------------------------------------------------------------------------172// Request log173// ---------------------------------------------------------------------------174175const requestListColumns = {176 id: fetchRequests.id,177 createdAt: fetchRequests.createdAt,178 completedAt: fetchRequests.completedAt,179 url: fetchRequests.url,180 finalUrl: fetchRequests.finalUrl,181 domain: fetchRequests.domain,182 method: fetchRequests.method,183 source: fetchRequests.source,184 status: fetchRequests.status,185 httpStatus: fetchRequests.httpStatus,186 errorCode: fetchRequests.errorCode,187 requestedNetwork: fetchRequests.requestedNetwork,188 network: fetchRequests.network,189 country: fetchRequests.country,190 region: fetchRequests.region,191 city: fetchRequests.city,192 sessionId: fetchRequests.sessionId,193 latencyMs: fetchRequests.latencyMs,194 attempts: fetchRequests.attempts,195 bytesIn: fetchRequests.bytesIn,196 bytesOut: fetchRequests.bytesOut,197 priceUsd: fetchRequests.priceUsd,198 cached: fetchRequests.cached,199};200201export interface RequestListRow {202 id: string;203 createdAt: Date;204 completedAt: Date | null;205 url: string;206 finalUrl: string | null;207 domain: string;208 method: string;209 source: string;210 status: string;211 httpStatus: number | null;212 errorCode: string | null;213 requestedNetwork: string;214 network: string | null;215 country: string | null;216 region: string | null;217 city: string | null;218 sessionId: string | null;219 latencyMs: number | null;220 attempts: number;221 bytesIn: number;222 bytesOut: number;223 priceUsd: number;224 cached: boolean;225}226227function escapeLike(s: string): string {228 return s.replace(/[\\%_]/g, (c) => `\\${c}`);229}230231function requestFilterWhere(scope: Scope, f: RequestFilters): SQL {232 const { from, to } = filterWindow(f);233 const parts: Array<SQL | undefined> = [scoped(scope), gte(fetchRequests.createdAt, from), to ? lt(fetchRequests.createdAt, to) : undefined];234 if (f.status) parts.push(eq(fetchRequests.status, f.status));235 if (f.domain) parts.push(ilike(fetchRequests.domain, `%${escapeLike(f.domain)}%`));236 if (f.network) parts.push(eq(fetchRequests.network, f.network));237 if (f.country) parts.push(eq(fetchRequests.country, f.country));238 if (f.httpStatus) parts.push(eq(fetchRequests.httpStatus, f.httpStatus));239 if (f.requestId) parts.push(eq(fetchRequests.id, f.requestId));240 if (f.source) parts.push(eq(fetchRequests.source, f.source));241 return and(...parts)!;242}243244export async function listRequests(scope: Scope, f: RequestFilters, pageSize: number): Promise<{ rows: RequestListRow[]; total: number; page: number; pageCount: number }> {245 const db = getDb();246 const where = requestFilterWhere(scope, f);247 const [{ total }] = await db.select({ total: count() }).from(fetchRequests).where(where);248 const pageCount = Math.max(1, Math.ceil(total / pageSize));249 const page = Math.min(f.page, pageCount);250 const rows = await db251 .select(requestListColumns)252 .from(fetchRequests)253 .where(where)254 .orderBy(desc(fetchRequests.createdAt), desc(fetchRequests.id))255 .limit(pageSize)256 .offset((page - 1) * pageSize);257 return { rows: rows as RequestListRow[], total, page, pageCount };258}259260/** Batched reader for CSV export (offset-based, deterministic ordering). */261export async function listRequestsBatch(scope: Scope, f: RequestFilters, offset: number, limit: number): Promise<RequestListRow[]> {262 const db = getDb();263 const rows = await db264 .select(requestListColumns)265 .from(fetchRequests)266 .where(requestFilterWhere(scope, f))267 .orderBy(desc(fetchRequests.createdAt), desc(fetchRequests.id))268 .limit(limit)269 .offset(offset);270 return rows as RequestListRow[];271}272273export async function getRecentRequests(scope: Scope, limit = 8): Promise<RequestListRow[]> {274 const db = getDb();275 const rows = await db.select(requestListColumns).from(fetchRequests).where(scoped(scope)).orderBy(desc(fetchRequests.createdAt)).limit(limit);276 return rows as RequestListRow[];277}278279// ---------------------------------------------------------------------------280// Request detail281// ---------------------------------------------------------------------------282283export interface RequestAttemptView {284 id: string;285 attemptNo: number;286 network: string;287 country: string | null;288 outcome: string;289 httpStatus: number | null;290 errorCode: string | null;291 blockReason: string | null;292 durationMs: number;293 bytesIn: number;294 bytesOut: number;295 routingScore: number | null;296 timing: Record<string, number> | null;297 createdAt: Date;298 /** Only populated when the organization has provider visibility enabled. */299 provider: string | null;300}301302export interface RequestDetail extends RequestListRow {303 organizationId: string;304 projectId: string;305 projectName: string;306 apiKey: { id: string; name: string; prefix: string; last4: string; revoked: boolean } | null;307 browser: boolean;308 format: string;309 errorMessage: string | null;310 requestHeaders: Record<string, string> | null;311 responseHeaders: Record<string, string> | null;312 timing: Record<string, number> | null;313 attemptRows: RequestAttemptView[];314}315316/** Loads one request for the organization (any project of the org), or null. */317export async function getRequestDetail(organizationId: string, id: string, opts: { providerVisibility: boolean }): Promise<RequestDetail | null> {318 const db = getDb();319 const [row] = await db320 .select({321 ...requestListColumns,322 organizationId: fetchRequests.organizationId,323 projectId: fetchRequests.projectId,324 projectName: projects.name,325 browser: fetchRequests.browser,326 format: fetchRequests.format,327 errorMessage: fetchRequests.errorMessage,328 requestHeaders: fetchRequests.requestHeaders,329 responseHeaders: fetchRequests.responseHeaders,330 timing: fetchRequests.timing,331 keyId: apiKeys.id,332 keyName: apiKeys.name,333 keyPrefix: apiKeys.keyPrefix,334 keyLast4: apiKeys.last4,335 keyRevokedAt: apiKeys.revokedAt,336 })337 .from(fetchRequests)338 .innerJoin(projects, eq(projects.id, fetchRequests.projectId))339 .leftJoin(apiKeys, eq(apiKeys.id, fetchRequests.apiKeyId))340 .where(and(eq(fetchRequests.id, id), eq(fetchRequests.organizationId, organizationId)))341 .limit(1);342 if (!row) return null;343 const attempts = await db344 .select({345 id: requestAttempts.id,346 attemptNo: requestAttempts.attemptNo,347 network: requestAttempts.network,348 country: requestAttempts.country,349 outcome: requestAttempts.outcome,350 httpStatus: requestAttempts.httpStatus,351 errorCode: requestAttempts.errorCode,352 blockReason: requestAttempts.blockReason,353 durationMs: requestAttempts.durationMs,354 bytesIn: requestAttempts.bytesIn,355 bytesOut: requestAttempts.bytesOut,356 routingScore: requestAttempts.routingScore,357 timing: requestAttempts.timing,358 createdAt: requestAttempts.createdAt,359 provider: opts.providerVisibility ? requestAttempts.provider : sql<string | null>`null`,360 })361 .from(requestAttempts)362 .where(eq(requestAttempts.requestId, id))363 .orderBy(requestAttempts.attemptNo);364 const { keyId, keyName, keyPrefix, keyLast4, keyRevokedAt, ...rest } = row;365 return {366 ...(rest as Omit<RequestDetail, "apiKey" | "attemptRows">),367 apiKey: keyId && keyName && keyPrefix && keyLast4 ? { id: keyId, name: keyName, prefix: keyPrefix, last4: keyLast4, revoked: Boolean(keyRevokedAt) } : null,368 attemptRows: attempts.map((a) => ({ ...a, provider: a.provider ?? null })),369 };370}371372// ---------------------------------------------------------------------------373// Sessions374// ---------------------------------------------------------------------------375376export interface SessionRow {377 id: string;378 label: string | null;379 projectId: string;380 projectName: string;381 network: string;382 country: string | null;383 region: string | null;384 city: string | null;385 status: "active" | "expired" | "closed";386 requestCount: number;387 lastUsedAt: Date | null;388 expiresAt: Date;389 createdAt: Date;390}391392export async function listProjectSessions(scope: Scope, limit = 200): Promise<SessionRow[]> {393 const db = getDb();394 const rows = await db395 .select({396 id: proxySessions.id,397 label: proxySessions.label,398 projectId: proxySessions.projectId,399 projectName: projects.name,400 network: proxySessions.network,401 country: proxySessions.country,402 region: proxySessions.region,403 city: proxySessions.city,404 status: proxySessions.status,405 requestCount: proxySessions.requestCount,406 lastUsedAt: proxySessions.lastUsedAt,407 expiresAt: proxySessions.expiresAt,408 createdAt: proxySessions.createdAt,409 })410 .from(proxySessions)411 .innerJoin(projects, eq(projects.id, proxySessions.projectId))412 .where(and(eq(proxySessions.organizationId, scope.organizationId), eq(proxySessions.projectId, scope.projectId)))413 .orderBy(desc(proxySessions.createdAt))414 .limit(limit);415 const now = Date.now();416 return rows.map((r) => ({417 ...r,418 status: r.status === "closed" ? "closed" : r.status === "expired" || r.expiresAt.getTime() <= now ? "expired" : "active",419 }));420}421422// ---------------------------------------------------------------------------423// Usage424// ---------------------------------------------------------------------------425426export interface UsageMonth {427 month: string; // YYYY-MM428 from: Date;429 to: Date;430 requests: number;431 successful: number;432 bytes: number;433 bandwidthBytes: number;434 residentialBytes: number;435 mobileBytes: number;436 browserSeconds: number;437 spendUsd: number;438 daily: Array<{ day: string; requests: number; bandwidthBytes: number; spendUsd: number }>;439 ledger: Array<{ id: string; metric: string; quantity: number; unit: string; priceUsd: number; requestId: string | null; createdAt: Date }>;440}441442export function parseMonth(input: string | undefined, now = new Date()): { key: string; from: Date; to: Date } {443 let y = now.getUTCFullYear();444 let m = now.getUTCMonth();445 if (input && /^\d{4}-\d{2}$/.test(input)) {446 const [yy, mm] = input.split("-").map(Number);447 if (mm! >= 1 && mm! <= 12) {448 y = yy!;449 m = mm! - 1;450 }451 }452 const from = new Date(Date.UTC(y, m, 1));453 const to = new Date(Date.UTC(y, m + 1, 1));454 return { key: `${y}-${String(m + 1).padStart(2, "0")}`, from, to };455}456457export function lastMonths(n: number, now = new Date()): Array<{ key: string; label: string }> {458 const out: Array<{ key: string; label: string }> = [];459 for (let i = 0; i < n; i++) {460 const d = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() - i, 1));461 out.push({ key: `${d.getUTCFullYear()}-${String(d.getUTCMonth() + 1).padStart(2, "0")}`, label: new Intl.DateTimeFormat("en-US", { month: "short", year: "numeric", timeZone: "UTC" }).format(d) });462 }463 return out;464}465466export async function getUsageMonth(scope: Scope, monthInput?: string): Promise<UsageMonth> {467 const db = getDb();468 const { key, from, to } = parseMonth(monthInput);469 const usageScope = and(eq(usageEvents.organizationId, scope.organizationId), eq(usageEvents.projectId, scope.projectId), gte(usageEvents.createdAt, from), lt(usageEvents.createdAt, to));470471 const [reqStats, [totals], daily, ledger] = await Promise.all([472 getRequestStats(scope, from, to),473 db474 .select({475 bandwidthBytes: sql`coalesce(sum(${usageEvents.quantity}) filter (where ${usageEvents.metric} = 'bandwidth'), 0)`.mapWith(num),476 residentialBytes: sql`coalesce(sum(${usageEvents.quantity}) filter (where ${usageEvents.metric} = 'residential_bandwidth'), 0)`.mapWith(num),477 mobileBytes: sql`coalesce(sum(${usageEvents.quantity}) filter (where ${usageEvents.metric} = 'mobile_bandwidth'), 0)`.mapWith(num),478 browserSeconds: sql`coalesce(sum(${usageEvents.quantity}) filter (where ${usageEvents.metric} = 'browser_seconds'), 0)`.mapWith(num),479 spendUsd: sql`coalesce(sum(${usageEvents.costUsd}), 0)`.mapWith(num),480 })481 .from(usageEvents)482 .where(usageScope),483 db484 .select({485 day: sql<string>`to_char(date_trunc('day', ${usageEvents.createdAt} at time zone 'UTC'), 'YYYY-MM-DD')`,486 requests: sql`coalesce(sum(${usageEvents.quantity}) filter (where ${usageEvents.metric} = 'request'), 0)`.mapWith(num),487 // `bandwidth` is the total; `residential_bandwidth`/`mobile_bandwidth` are class breakdowns of the same bytes.488 bandwidthBytes: sql`coalesce(sum(${usageEvents.quantity}) filter (where ${usageEvents.metric} = 'bandwidth'), 0)`.mapWith(num),489 spendUsd: sql`coalesce(sum(${usageEvents.costUsd}), 0)`.mapWith(num),490 })491 .from(usageEvents)492 .where(usageScope)493 .groupBy(sql`1`)494 .orderBy(sql`1 desc`),495 db496 .select({497 id: usageEvents.id,498 metric: usageEvents.metric,499 quantity: usageEvents.quantity,500 unit: usageEvents.unit,501 priceUsd: usageEvents.costUsd,502 requestId: usageEvents.requestId,503 createdAt: usageEvents.createdAt,504 })505 .from(usageEvents)506 .where(usageScope)507 .orderBy(desc(usageEvents.createdAt))508 .limit(100),509 ]);510511 return {512 month: key,513 from,514 to,515 requests: reqStats.total,516 successful: reqStats.successful,517 bytes: reqStats.bytes,518 bandwidthBytes: totals?.bandwidthBytes ?? 0,519 residentialBytes: totals?.residentialBytes ?? 0,520 mobileBytes: totals?.mobileBytes ?? 0,521 browserSeconds: totals?.browserSeconds ?? 0,522 // Prefer the immutable ledger; fall back to billed request prices when no usage events were recorded.523 spendUsd: totals && totals.spendUsd > 0 ? totals.spendUsd : reqStats.spendUsd,524 daily,525 ledger,526 };527}528529// ---------------------------------------------------------------------------530// Analytics531// ---------------------------------------------------------------------------532533export interface NetworkSuccess {534 network: string;535 requests: number;536 successful: number;537 successRate: number | null;538}539540export async function getSuccessByNetwork(scope: Scope, from: Date): Promise<NetworkSuccess[]> {541 const db = getDb();542 const rows = await db543 .select({544 network: fetchRequests.network,545 requests: count(),546 successful: sql`count(*) filter (where ${fetchRequests.status} = 'success')`.mapWith(num),547 failed: sql`count(*) filter (where ${fetchRequests.status} = 'failed')`.mapWith(num),548 })549 .from(fetchRequests)550 .where(and(scoped(scope), gte(fetchRequests.createdAt, from), isNotNull(fetchRequests.network)))551 .groupBy(fetchRequests.network);552 const byNet = new Map(rows.map((r) => [r.network, r]));553 return REQUEST_NETWORKS.map((network) => {554 const r = byNet.get(network);555 const decided = (r?.successful ?? 0) + (r?.failed ?? 0);556 return { network, requests: r?.requests ?? 0, successful: r?.successful ?? 0, successRate: decided > 0 ? ((r?.successful ?? 0) / decided) * 100 : null };557 });558}559560export interface DomainStat {561 domain: string;562 requests: number;563 successful: number;564 successRate: number | null;565 avgLatencyMs: number | null;566 blocked: number;567}568569export async function getTopDomains(scope: Scope, from: Date, limit = 10): Promise<DomainStat[]> {570 const db = getDb();571 const rows = await db572 .select({573 domain: fetchRequests.domain,574 requests: count(),575 successful: sql`count(*) filter (where ${fetchRequests.status} = 'success')`.mapWith(num),576 failed: sql`count(*) filter (where ${fetchRequests.status} = 'failed')`.mapWith(num),577 avgLatencyMs: sql`avg(${fetchRequests.latencyMs})`.mapWith(numOrNull),578 blocked: sql`count(*) filter (where ${fetchRequests.errorCode} = 'TARGET_BLOCKED')`.mapWith(num),579 })580 .from(fetchRequests)581 .where(and(scoped(scope), gte(fetchRequests.createdAt, from)))582 .groupBy(fetchRequests.domain)583 .orderBy(desc(count()))584 .limit(limit);585 return rows.map((r) => {586 const decided = r.successful + r.failed;587 return { domain: r.domain, requests: r.requests, successful: r.successful, successRate: decided > 0 ? (r.successful / decided) * 100 : null, avgLatencyMs: r.avgLatencyMs, blocked: r.blocked };588 });589}590591export interface ErrorCodeStat {592 code: string;593 requests: number;594 percent: number;595}596597export async function getErrorBreakdown(scope: Scope, from: Date, limit = 8): Promise<ErrorCodeStat[]> {598 const db = getDb();599 const rows = await db600 .select({ code: fetchRequests.errorCode, requests: count() })601 .from(fetchRequests)602 .where(and(scoped(scope), gte(fetchRequests.createdAt, from), eq(fetchRequests.status, "failed"), isNotNull(fetchRequests.errorCode)))603 .groupBy(fetchRequests.errorCode)604 .orderBy(desc(count()))605 .limit(limit);606 const total = rows.reduce((a, r) => a + r.requests, 0);607 return rows.map((r) => ({ code: r.code ?? "UNKNOWN", requests: r.requests, percent: total > 0 ? (r.requests / total) * 100 : 0 }));608}609610export interface CountryStat {611 country: string | null;612 requests: number;613 successful: number;614 successRate: number | null;615 avgLatencyMs: number | null;616}617618export async function getCountries(scope: Scope, from: Date, limit = 10): Promise<CountryStat[]> {619 const db = getDb();620 const rows = await db621 .select({622 country: fetchRequests.country,623 requests: count(),624 successful: sql`count(*) filter (where ${fetchRequests.status} = 'success')`.mapWith(num),625 failed: sql`count(*) filter (where ${fetchRequests.status} = 'failed')`.mapWith(num),626 avgLatencyMs: sql`avg(${fetchRequests.latencyMs})`.mapWith(numOrNull),627 })628 .from(fetchRequests)629 .where(and(scoped(scope), gte(fetchRequests.createdAt, from)))630 .groupBy(fetchRequests.country)631 .orderBy(desc(count()))632 .limit(limit);633 return rows.map((r) => {634 const decided = r.successful + r.failed;635 return { country: r.country, requests: r.requests, successful: r.successful, successRate: decided > 0 ? (r.successful / decided) * 100 : null, avgLatencyMs: r.avgLatencyMs };636 });637}638