"use server" import { Database, and, eq, isNull, sql } from "@opencode-ai/console-core/drizzle/index.js" import { AuthTable } from "@opencode-ai/console-core/schema/auth.sql.js" import { UserTable } from "@opencode-ai/console-core/schema/user.sql.js" import { BillingTable, PaymentTable, SubscriptionTable, BlackPlans, UsageTable, LiteTable, } from "@opencode-ai/console-core/schema/billing.sql.js" import { WorkspaceTable } from "@opencode-ai/console-core/schema/workspace.sql.js" import { KeyTable } from "@opencode-ai/console-core/schema/key.sql.js" import { ModelTable } from "@opencode-ai/console-core/schema/model.sql.js" import { BlackData } from "@opencode-ai/console-core/black.js" import { LiteData } from "@opencode-ai/console-core/lite.js" import { Subscription } from "@opencode-ai/console-core/subscription.js" import { centsToMicroCents } from "@opencode-ai/console-core/util/price.js" import { getWeekBounds } from "@opencode-ai/console-core/util/date.js" export type LookupResult = { identifier: string auth?: Record[] accountWorkspaces?: Record[] workspaces: WorkspaceSection[] } export type WorkspaceSection = { workspaceID: string title: string users: Record[] billing: Record | null go: Record[] payments: Record[] usage: Record[] disabledModels: Record[] } export async function lookup(identifier: string): Promise { if (!identifier) throw new Error("Identifier is required") if (identifier.startsWith("wrk_")) { const workspace = await loadWorkspace(identifier) return { identifier, workspaces: [workspace] } } if (identifier.startsWith("key_")) { const key = await Database.use((tx) => tx .select() .from(KeyTable) .where(eq(KeyTable.id, identifier)) .then((rows) => rows[0]), ) if (!key) throw new Error("API key not found") const workspace = await loadWorkspace(key.workspaceID) return { identifier, workspaces: [workspace] } } if (identifier.startsWith("sk-")) { const key = await Database.use((tx) => tx .select() .from(KeyTable) .where(eq(KeyTable.key, identifier)) .then((rows) => rows[0]), ) if (!key) throw new Error("API key not found") const workspace = await loadWorkspace(key.workspaceID) return { identifier, workspaces: [workspace] } } // Treat as email const authData = await Database.use((tx) => tx.select().from(AuthTable).where(eq(AuthTable.subject, identifier))) if (authData.length === 0) throw new Error("Email not found") const accountID = authData[0].accountID const auth = await Database.use((tx) => tx.select().from(AuthTable).where(eq(AuthTable.accountID, accountID))) const accountWorkspaces = await Database.use((tx) => tx .select({ userID: UserTable.id, workspaceID: UserTable.workspaceID, workspaceName: WorkspaceTable.name, balance: BillingTable.balance, role: UserTable.role, black: SubscriptionTable.timeCreated, lite: LiteTable.timeCreated, }) .from(UserTable) .rightJoin(WorkspaceTable, eq(WorkspaceTable.id, UserTable.workspaceID)) .leftJoin(BillingTable, eq(BillingTable.workspaceID, WorkspaceTable.id)) .leftJoin(SubscriptionTable, eq(SubscriptionTable.userID, UserTable.id)) .leftJoin(LiteTable, eq(LiteTable.userID, UserTable.id)) .where(eq(UserTable.accountID, accountID)) .then((rows) => rows.map((row) => ({ workspaceName: row.workspaceID ? { __link: `#workspace-${row.workspaceID}`, label: row.workspaceName } : row.workspaceName, userID: row.userID, workspaceID: row.workspaceID, balance: formatMicroCents(row.balance) ?? "$0.00", role: row.role, black: formatDate(row.black), lite: formatDate(row.lite), })), ), ) const workspaces: WorkspaceSection[] = [] for (const w of accountWorkspaces) { if (!w.workspaceID) continue workspaces.push(await loadWorkspace(w.workspaceID)) } return { identifier, auth: auth.map((row) => ({ provider: row.provider, subject: row.subject, accountID: row.accountID, })), accountWorkspaces, workspaces, } } async function loadWorkspace(workspaceID: string): Promise { const workspace = await Database.use((tx) => tx .select() .from(WorkspaceTable) .where(eq(WorkspaceTable.id, workspaceID)) .then((rows) => rows[0]), ) if (!workspace) throw new Error(`Workspace ${workspaceID} not found`) const users = await Database.use((tx) => tx .select({ authEmail: AuthTable.subject, inviteEmail: UserTable.email, role: UserTable.role, timeSeen: UserTable.timeSeen, monthlyLimit: UserTable.monthlyLimit, monthlyUsage: UserTable.monthlyUsage, timeDeleted: UserTable.timeDeleted, fixedUsage: SubscriptionTable.fixedUsage, rollingUsage: SubscriptionTable.rollingUsage, timeFixedUpdated: SubscriptionTable.timeFixedUpdated, timeRollingUpdated: SubscriptionTable.timeRollingUpdated, timeSubscriptionCreated: SubscriptionTable.timeCreated, subscription: BillingTable.subscription, }) .from(UserTable) .innerJoin(BillingTable, eq(BillingTable.workspaceID, workspace.id)) .leftJoin(AuthTable, and(eq(UserTable.accountID, AuthTable.accountID), eq(AuthTable.provider, "email"))) .leftJoin(SubscriptionTable, eq(SubscriptionTable.userID, UserTable.id)) .where(eq(UserTable.workspaceID, workspace.id)) .then((rows) => rows.map((row) => { const subStatus = getSubscriptionStatus(row) return { email: (row.timeDeleted ? "[deleted] " : "") + (row.authEmail ?? row.inviteEmail), role: row.role, timeSeen: formatDate(row.timeSeen), monthly: formatMonthlyUsage(row.monthlyUsage, row.monthlyLimit), subscribed: formatDate(row.timeSubscriptionCreated), subWeekly: subStatus.weekly, subRolling: subStatus.rolling, rateLimited: subStatus.rateLimited, retryIn: subStatus.retryIn, } }), ), ) const billing = await Database.use((tx) => tx .select({ balance: BillingTable.balance, customerID: BillingTable.customerID, reload: BillingTable.reload, blackSubscriptionID: BillingTable.subscriptionID, blackSubscription: { plan: BillingTable.subscriptionPlan, booked: BillingTable.timeSubscriptionBooked, enrichment: BillingTable.subscription, }, timeBlackSubscriptionSelected: BillingTable.timeSubscriptionSelected, liteSubscriptionID: BillingTable.liteSubscriptionID, }) .from(BillingTable) .where(eq(BillingTable.workspaceID, workspace.id)) .then( (rows) => rows.map((row) => ({ balance: `$${(row.balance / 100000000).toFixed(2)}`, reload: row.reload ? "yes" : "no", customerID: row.customerID, GO: row.liteSubscriptionID, Black: row.blackSubscriptionID ? [ `Black ${row.blackSubscription.enrichment!.plan}`, row.blackSubscription.enrichment!.seats > 1 ? `X ${row.blackSubscription.enrichment!.seats} seats` : "", row.blackSubscription.enrichment!.coupon ? `(coupon: ${row.blackSubscription.enrichment!.coupon})` : "", `(ref: ${row.blackSubscriptionID})`, ].join(" ") : row.blackSubscription.booked ? `Waitlist ${row.blackSubscription.plan} plan${row.timeBlackSubscriptionSelected ? " (selected)" : ""}` : undefined, }))[0] ?? null, ), ) const liteLimits = LiteData.getLimits() const go = await Database.use((tx) => tx .select({ userID: LiteTable.userID, userEmail: UserTable.email, authEmail: AuthTable.subject, rollingUsage: LiteTable.rollingUsage, weeklyUsage: LiteTable.weeklyUsage, monthlyUsage: LiteTable.monthlyUsage, timeRollingUpdated: LiteTable.timeRollingUpdated, timeWeeklyUpdated: LiteTable.timeWeeklyUpdated, timeMonthlyUpdated: LiteTable.timeMonthlyUpdated, timeCreated: LiteTable.timeCreated, useBalance: BillingTable.lite, }) .from(LiteTable) .innerJoin(BillingTable, eq(BillingTable.workspaceID, LiteTable.workspaceID)) .leftJoin(UserTable, eq(UserTable.id, LiteTable.userID)) .leftJoin(AuthTable, and(eq(UserTable.accountID, AuthTable.accountID), eq(AuthTable.provider, "email"))) .where(and(eq(LiteTable.workspaceID, workspace.id), isNull(LiteTable.timeDeleted))) .then((rows) => rows.map((row) => { const rolling = Subscription.analyzeRollingUsage({ limit: liteLimits.rollingLimit, window: liteLimits.rollingWindow, usage: row.rollingUsage ?? 0, timeUpdated: row.timeRollingUpdated ?? new Date(), }) const weekly = Subscription.analyzeWeeklyUsage({ limit: liteLimits.weeklyLimit, usage: row.weeklyUsage ?? 0, timeUpdated: row.timeWeeklyUpdated ?? new Date(), }) const monthly = Subscription.analyzeMonthlyUsage({ limit: liteLimits.monthlyLimit, usage: row.monthlyUsage ?? 0, timeUpdated: row.timeMonthlyUpdated ?? new Date(), timeSubscribed: row.timeCreated, }) return { email: row.authEmail ?? row.userEmail ?? row.userID, subscribed: formatDate(row.timeCreated), useBalance: row.useBalance?.useBalance ? "yes" : "no", rolling: formatLiteUsage(rolling), weekly: formatLiteUsage(weekly), monthly: formatLiteUsage(monthly), } }), ), ) const payments = await Database.use((tx) => tx .select({ amount: PaymentTable.amount, paymentID: PaymentTable.paymentID, invoiceID: PaymentTable.invoiceID, customerID: PaymentTable.customerID, timeCreated: PaymentTable.timeCreated, timeRefunded: PaymentTable.timeRefunded, }) .from(PaymentTable) .where(eq(PaymentTable.workspaceID, workspace.id)) .orderBy(sql`${PaymentTable.timeCreated} DESC`) .limit(100) .then((rows) => rows.map((row) => ({ amount: `$${(row.amount / 100000000).toFixed(2)}`, paymentID: row.paymentID ? `https://dashboard.stripe.com/acct_1RszBH2StuRr0lbX/payments/${row.paymentID}` : null, invoiceID: row.invoiceID, customerID: row.customerID, timeCreated: formatDate(row.timeCreated), timeRefunded: formatDate(row.timeRefunded), })), ), ) const planExpr = sql`JSON_UNQUOTE(JSON_EXTRACT(${UsageTable.enrichment}, '$.plan'))` const usage = await Database.use((tx) => tx .select({ date: sql`DATE(${UsageTable.timeCreated})`.as("date"), freeRequests: sql`SUM(CASE WHEN ${UsageTable.cost} = 0 THEN 1 ELSE 0 END)`.as("free_requests"), goRequests: sql`SUM(CASE WHEN ${planExpr} = 'lite' THEN 1 ELSE 0 END)`.as("go_requests"), goCost: sql`SUM(CASE WHEN ${planExpr} = 'lite' THEN ${UsageTable.cost} ELSE 0 END)`.as("go_cost"), apiRequests: sql`SUM(CASE WHEN ${planExpr} IS NULL AND ${UsageTable.cost} > 0 THEN 1 ELSE 0 END)`.as( "api_requests", ), apiCost: sql`SUM(CASE WHEN ${planExpr} IS NULL AND ${UsageTable.cost} > 0 THEN ${UsageTable.cost} ELSE 0 END)`.as( "api_cost", ), }) .from(UsageTable) .where( and( eq(UsageTable.workspaceID, workspace.id), sql`${UsageTable.timeCreated} >= DATE_SUB(NOW(), INTERVAL 28 DAY)`, ), ) .groupBy(sql`DATE(${UsageTable.timeCreated})`) .orderBy(sql`DATE(${UsageTable.timeCreated}) DESC`) .then((rows) => { const totals = rows.reduce( (acc, r) => ({ freeRequests: acc.freeRequests + Number(r.freeRequests), goRequests: acc.goRequests + Number(r.goRequests), goCost: acc.goCost + Number(r.goCost), apiRequests: acc.apiRequests + Number(r.apiRequests), apiCost: acc.apiCost + Number(r.apiCost), }), { freeRequests: 0, goRequests: 0, goCost: 0, apiRequests: 0, apiCost: 0 }, ) const mapped: Record[] = rows.map((row) => ({ date: row.date, freeRequests: Number(row.freeRequests), goRequests: Number(row.goRequests), goCost: formatMicroCents(Number(row.goCost)) ?? "$0.00", apiRequests: Number(row.apiRequests), apiCost: formatMicroCents(Number(row.apiCost)) ?? "$0.00", })) if (mapped.length > 0) { mapped.push({ date: "TOTAL", freeRequests: totals.freeRequests, goRequests: totals.goRequests, goCost: formatMicroCents(totals.goCost) ?? "$0.00", apiRequests: totals.apiRequests, apiCost: formatMicroCents(totals.apiCost) ?? "$0.00", }) } return mapped }), ) const disabledModels = await Database.use((tx) => tx .select({ model: ModelTable.model, timeCreated: ModelTable.timeCreated, }) .from(ModelTable) .where(eq(ModelTable.workspaceID, workspace.id)) .orderBy(sql`${ModelTable.timeCreated} DESC`) .then((rows) => rows.map((row) => ({ model: row.model, timeCreated: formatDate(row.timeCreated), })), ), ) return { workspaceID: workspace.id, title: `Workspace "${workspace.name}" (${workspace.id})`, users, billing, go, payments, usage, disabledModels, } } function formatLiteUsage(usage: { status: "ok" | "rate-limited"; usagePercent: number; resetInSec: number }) { const reset = formatResetTime(usage.resetInSec) const status = usage.status === "rate-limited" ? " [limited]" : "" return `${usage.usagePercent}% (resets in ${reset})${status}` } function formatResetTime(seconds: number) { if (seconds <= 0) return "now" const days = Math.floor(seconds / 86400) if (days >= 1) return `${days}d` const hours = Math.floor(seconds / 3600) if (hours >= 1) { const minutes = Math.floor((seconds % 3600) / 60) return minutes > 0 ? `${hours}h ${minutes}m` : `${hours}h` } const minutes = Math.max(1, Math.ceil(seconds / 60)) return `${minutes}m` } function formatMicroCents(value: number | null | undefined) { if (value === null || value === undefined) return null return `$${(value / 100000000).toFixed(2)}` } function formatDate(value: Date | null | undefined) { if (!value) return null return value.toISOString().split("T")[0] } function formatMonthlyUsage(usage: number | null | undefined, limit: number | null | undefined) { const usageText = formatMicroCents(usage) ?? "$0.00" if (limit === null || limit === undefined) return `${usageText} / no limit` return `${usageText} / $${limit.toFixed(2)}` } function formatRetryTime(seconds: number) { const days = Math.floor(seconds / 86400) if (days >= 1) return `${days} day${days > 1 ? "s" : ""}` const hours = Math.floor(seconds / 3600) const minutes = Math.ceil((seconds % 3600) / 60) if (hours >= 1) return `${hours}hr ${minutes}min` return `${minutes}min` } function getSubscriptionStatus(row: { subscription: { plan: (typeof BlackPlans)[number] } | null timeSubscriptionCreated: Date | null fixedUsage: number | null rollingUsage: number | null timeFixedUpdated: Date | null timeRollingUpdated: Date | null }) { if (!row.timeSubscriptionCreated || !row.subscription) { return { weekly: null, rolling: null, rateLimited: null, retryIn: null } } const black = BlackData.getLimits({ plan: row.subscription.plan }) const now = new Date() const week = getWeekBounds(now) const fixedLimit = black.fixedLimit ? centsToMicroCents(black.fixedLimit * 100) : null const rollingLimit = black.rollingLimit ? centsToMicroCents(black.rollingLimit * 100) : null const rollingWindowMs = (black.rollingWindow ?? 5) * 3600 * 1000 const currentWeekly = row.fixedUsage && row.timeFixedUpdated && row.timeFixedUpdated >= week.start ? row.fixedUsage : 0 const windowStart = new Date(now.getTime() - rollingWindowMs) const currentRolling = row.rollingUsage && row.timeRollingUpdated && row.timeRollingUpdated >= windowStart ? row.rollingUsage : 0 const isWeeklyLimited = fixedLimit !== null && currentWeekly >= fixedLimit const isRollingLimited = rollingLimit !== null && currentRolling >= rollingLimit const retryIn = isWeeklyLimited ? formatRetryTime(Math.ceil((week.end.getTime() - now.getTime()) / 1000)) : isRollingLimited && row.timeRollingUpdated ? formatRetryTime(Math.ceil((row.timeRollingUpdated.getTime() + rollingWindowMs - now.getTime()) / 1000)) : null return { weekly: fixedLimit !== null ? `${formatMicroCents(currentWeekly)} / $${black.fixedLimit}` : null, rolling: rollingLimit !== null ? `${formatMicroCents(currentRolling)} / $${black.rollingLimit}` : null, rateLimited: isWeeklyLimited || isRollingLimited ? "yes" : "no", retryIn, } }