spb/fetcha

1import "server-only";2import { getDb, apiKeys, projects, fetchRequests, usageEvents, auditLogs, legalAcceptances, eq, and, or, desc, gte, sql, isNull } from "@fetcha/db";3import type { ApiKey, AuditLog } from "@fetcha/db";45/** First instant of the current calendar month (UTC). */6export function monthStart(): Date {7  const d = new Date();8  return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), 1));9}1011export type ApiKeyStatus = "active" | "revoked" | "expired";1213export function apiKeyStatus(k: Pick<ApiKey, "revokedAt" | "expiresAt">): ApiKeyStatus {14  if (k.revokedAt) return "revoked";15  if (k.expiresAt && k.expiresAt.getTime() < Date.now()) return "expired";16  return "active";17}1819export interface ApiKeyRow {20  id: string;21  name: string;22  keyPrefix: string;23  last4: string;24  mode: string;25  scopes: string[];26  projectId: string;27  projectName: string;28  createdAt: Date;29  lastUsedAt: Date | null;30  expiresAt: Date | null;31  revokedAt: Date | null;32  status: ApiKeyStatus;33}3435/** All keys of the organization (optionally one project), newest first. Never returns the hash. */36export async function listApiKeys(organizationId: string, projectId?: string | null): Promise<ApiKeyRow[]> {37  const db = getDb();38  const rows = await db39    .select({40      id: apiKeys.id,41      name: apiKeys.name,42      keyPrefix: apiKeys.keyPrefix,43      last4: apiKeys.last4,44      mode: apiKeys.mode,45      scopes: apiKeys.scopes,46      projectId: apiKeys.projectId,47      projectName: projects.name,48      createdAt: apiKeys.createdAt,49      lastUsedAt: apiKeys.lastUsedAt,50      expiresAt: apiKeys.expiresAt,51      revokedAt: apiKeys.revokedAt,52    })53    .from(apiKeys)54    .innerJoin(projects, eq(apiKeys.projectId, projects.id))55    .where(projectId ? and(eq(apiKeys.organizationId, organizationId), eq(apiKeys.projectId, projectId)) : eq(apiKeys.organizationId, organizationId))56    .orderBy(desc(apiKeys.createdAt));57  return rows.map((r) => ({ ...r, scopes: r.scopes ?? [], status: apiKeyStatus(r) }));58}5960export interface MonthlyUsage {61  requests: number;62  successful: number;63  /** Bytes in + out across all attempts. */64  bytes: number;65  spendUsd: number;66}6768/** Requests (fetch_requests), bandwidth and estimated spend (usage_events.cost_usd) for the current month. */69export async function monthlyUsage(organizationId: string, projectId?: string | null): Promise<MonthlyUsage> {70  const db = getDb();71  const since = monthStart();72  const reqWhere = projectId73    ? and(eq(fetchRequests.organizationId, organizationId), eq(fetchRequests.projectId, projectId), gte(fetchRequests.createdAt, since))74    : and(eq(fetchRequests.organizationId, organizationId), gte(fetchRequests.createdAt, since));75  const usageWhere = projectId76    ? and(eq(usageEvents.organizationId, organizationId), eq(usageEvents.projectId, projectId), gte(usageEvents.createdAt, since))77    : and(eq(usageEvents.organizationId, organizationId), gte(usageEvents.createdAt, since));78  const [[req], [usage]] = await Promise.all([79    db80      .select({81        n: sql<number>`count(*)::int`,82        ok: sql<number>`count(*) filter (where ${fetchRequests.status} = 'success')::int`,83        bytes: sql<number>`coalesce(sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut}), 0)::float8`,84      })85      .from(fetchRequests)86      .where(reqWhere),87    db.select({ spend: sql<number>`coalesce(sum(${usageEvents.costUsd}), 0)::float8` }).from(usageEvents).where(usageWhere),88  ]);89  return { requests: Number(req?.n ?? 0), successful: Number(req?.ok ?? 0), bytes: Number(req?.bytes ?? 0), spendUsd: Number(usage?.spend ?? 0) };90}9192export interface ProjectStats {93  requests: number;94  spendUsd: number;95  activeKeys: number;96}9798/** Per-project month-to-date requests, spend and active key counts for the whole organization. */99export async function projectStatsByProject(organizationId: string): Promise<Map<string, ProjectStats>> {100  const db = getDb();101  const since = monthStart();102  const [reqRows, spendRows, keyRows] = await Promise.all([103    db104      .select({ projectId: fetchRequests.projectId, n: sql<number>`count(*)::int` })105      .from(fetchRequests)106      .where(and(eq(fetchRequests.organizationId, organizationId), gte(fetchRequests.createdAt, since)))107      .groupBy(fetchRequests.projectId),108    db109      .select({ projectId: usageEvents.projectId, spend: sql<number>`coalesce(sum(${usageEvents.costUsd}), 0)::float8` })110      .from(usageEvents)111      .where(and(eq(usageEvents.organizationId, organizationId), gte(usageEvents.createdAt, since)))112      .groupBy(usageEvents.projectId),113    db114      .select({ projectId: apiKeys.projectId, n: sql<number>`count(*)::int` })115      .from(apiKeys)116      .where(and(eq(apiKeys.organizationId, organizationId), isNull(apiKeys.revokedAt)))117      .groupBy(apiKeys.projectId),118  ]);119  const map = new Map<string, ProjectStats>();120  const get = (id: string) => {121    let s = map.get(id);122    if (!s) {123      s = { requests: 0, spendUsd: 0, activeKeys: 0 };124      map.set(id, s);125    }126    return s;127  };128  for (const r of reqRows) get(r.projectId).requests = Number(r.n);129  for (const r of spendRows) if (r.projectId) get(r.projectId).spendUsd = Number(r.spend);130  for (const r of keyRows) get(r.projectId).activeKeys = Number(r.n);131  return map;132}133134/** Load one project of the organization (including archived ones), or null. */135export async function getOrgProject(organizationId: string, projectId: string) {136  const [p] = await getDb().select().from(projects).where(and(eq(projects.id, projectId), eq(projects.organizationId, organizationId))).limit(1);137  return p ?? null;138}139140export async function listAuditLogs(userId: string, organizationId: string, page: number, pageSize = 25): Promise<{ rows: AuditLog[]; total: number }> {141  const db = getDb();142  const where = or(eq(auditLogs.userId, userId), eq(auditLogs.organizationId, organizationId));143  const [rows, [total]] = await Promise.all([144    db145      .select()146      .from(auditLogs)147      .where(where)148      .orderBy(desc(auditLogs.createdAt))149      .limit(pageSize)150      .offset(Math.max(0, page - 1) * pageSize),151    db.select({ n: sql<number>`count(*)::int` }).from(auditLogs).where(where),152  ]);153  return { rows, total: Number(total?.n ?? 0) };154}155156export async function listLegalAcceptances(userId: string) {157  return getDb().select().from(legalAcceptances).where(eq(legalAcceptances.userId, userId)).orderBy(desc(legalAcceptances.acceptedAt));158}159160/** Onboarding state: does the org already have an active key / any request? */161export async function onboardingState(organizationId: string): Promise<{ hasKey: boolean; hasRequest: boolean }> {162  const db = getDb();163  const [[k], [r]] = await Promise.all([164    db.select({ id: apiKeys.id }).from(apiKeys).where(and(eq(apiKeys.organizationId, organizationId), isNull(apiKeys.revokedAt))).limit(1),165    db.select({ id: fetchRequests.id }).from(fetchRequests).where(eq(fetchRequests.organizationId, organizationId)).limit(1),166  ]);167  return { hasKey: Boolean(k), hasRequest: Boolean(r) };168}169