openAgent

1import { Database, and, eq, sql } from "../src/drizzle/index.js"
2import { AuthTable } from "../src/schema/auth.sql.js"
3import { UserTable } from "../src/schema/user.sql.js"
4import {
5  BillingTable,
6  PaymentTable,
7  SubscriptionTable,
8  BlackPlans,
9  UsageTable,
10  LiteTable,
11} from "../src/schema/billing.sql.js"
12import { WorkspaceTable } from "../src/schema/workspace.sql.js"
13import { KeyTable } from "../src/schema/key.sql.js"
14import { BlackData } from "../src/black.js"
15import { centsToMicroCents } from "../src/util/price.js"
16import { getWeekBounds } from "../src/util/date.js"
17import { ModelTable } from "../src/schema/model.sql.js"
18 
19// get input from command line
20const identifier = process.argv[2]
21const verbose = process.argv[process.argv.length - 1] === "-v"
22if (!identifier) {
23  console.error("Usage: bun lookup-user.ts <email|workspaceID|apiKey> [-v]")
24  process.exit(1)
25}
26 
27// loop up by workspace ID
28if (identifier.startsWith("wrk_")) {
29  await printWorkspace(identifier)
30}
31// lookup by API key ID
32else if (identifier.startsWith("key_")) {
33  const key = await Database.use((tx) =>
34    tx
35      .select()
36      .from(KeyTable)
37      .where(eq(KeyTable.id, identifier))
38      .then((rows) => rows[0]),
39  )
40  if (!key) {
41    console.error("API key not found")
42    process.exit(1)
43  }
44  await printWorkspace(key.workspaceID)
45}
46// lookup by API key value
47else if (identifier.startsWith("sk-")) {
48  const key = await Database.use((tx) =>
49    tx
50      .select()
51      .from(KeyTable)
52      .where(eq(KeyTable.key, identifier))
53      .then((rows) => rows[0]),
54  )
55  if (!key) {
56    console.error("API key not found")
57    process.exit(1)
58  }
59  await printWorkspace(key.workspaceID)
60}
61// lookup by email
62else {
63  const authData = await Database.use(async (tx) =>
64    tx.select().from(AuthTable).where(eq(AuthTable.subject, identifier)),
65  )
66  if (authData.length === 0) {
67    console.error("Email not found")
68    process.exit(1)
69  }
70  if (authData.length > 1) console.warn("Multiple users found for email", identifier)
71 
72  // Get all auth records for email
73  const accountID = authData[0].accountID
74  await printTable("Auth", (tx) => tx.select().from(AuthTable).where(eq(AuthTable.accountID, accountID)))
75 
76  // Get all workspaces for this account
77  const users = await printTable("Workspaces", (tx) =>
78    tx
79      .select({
80        userID: UserTable.id,
81        workspaceID: UserTable.workspaceID,
82        workspaceName: WorkspaceTable.name,
83        role: UserTable.role,
84        black: SubscriptionTable.timeCreated,
85        lite: LiteTable.timeCreated,
86      })
87      .from(UserTable)
88      .rightJoin(WorkspaceTable, eq(WorkspaceTable.id, UserTable.workspaceID))
89      .leftJoin(SubscriptionTable, eq(SubscriptionTable.userID, UserTable.id))
90      .leftJoin(LiteTable, eq(LiteTable.userID, UserTable.id))
91      .where(eq(UserTable.accountID, accountID))
92      .then((rows) =>
93        rows.map((row) => ({
94          userID: row.userID,
95          workspaceID: row.workspaceID,
96          workspaceName: row.workspaceName,
97          role: row.role,
98          black: formatDate(row.black),
99          lite: formatDate(row.lite),
100        })),
101      ),
102  )
103 
104  for (const user of users) {
105    await printWorkspace(user.workspaceID)
106  }
107}
108 
109async function printWorkspace(workspaceID: string) {
110  const workspace = await Database.use((tx) =>
111    tx
112      .select()
113      .from(WorkspaceTable)
114      .where(eq(WorkspaceTable.id, workspaceID))
115      .then((rows) => rows[0]),
116  )
117 
118  printHeader(`Workspace "${workspace.name}" (${workspace.id})`)
119 
120  await printTable("Users", (tx) =>
121    tx
122      .select({
123        authEmail: AuthTable.subject,
124        inviteEmail: UserTable.email,
125        role: UserTable.role,
126        timeSeen: UserTable.timeSeen,
127        monthlyLimit: UserTable.monthlyLimit,
128        monthlyUsage: UserTable.monthlyUsage,
129        timeDeleted: UserTable.timeDeleted,
130        fixedUsage: SubscriptionTable.fixedUsage,
131        rollingUsage: SubscriptionTable.rollingUsage,
132        timeFixedUpdated: SubscriptionTable.timeFixedUpdated,
133        timeRollingUpdated: SubscriptionTable.timeRollingUpdated,
134        timeSubscriptionCreated: SubscriptionTable.timeCreated,
135        subscription: BillingTable.subscription,
136      })
137      .from(UserTable)
138      .innerJoin(BillingTable, eq(BillingTable.workspaceID, workspace.id))
139      .leftJoin(AuthTable, and(eq(UserTable.accountID, AuthTable.accountID), eq(AuthTable.provider, "email")))
140      .leftJoin(SubscriptionTable, eq(SubscriptionTable.userID, UserTable.id))
141      .where(eq(UserTable.workspaceID, workspace.id))
142      .then((rows) =>
143        rows.map((row) => {
144          const subStatus = getSubscriptionStatus(row)
145          return {
146            email: (row.timeDeleted ? "❌ " : "") + (row.authEmail ?? row.inviteEmail),
147            role: row.role,
148            timeSeen: formatDate(row.timeSeen),
149            monthly: formatMonthlyUsage(row.monthlyUsage, row.monthlyLimit),
150            subscribed: formatDate(row.timeSubscriptionCreated),
151            subWeekly: subStatus.weekly,
152            subRolling: subStatus.rolling,
153            rateLimited: subStatus.rateLimited,
154            retryIn: subStatus.retryIn,
155          }
156        }),
157      ),
158  )
159 
160  await printTable("Billing", (tx) =>
161    tx
162      .select({
163        balance: BillingTable.balance,
164        customerID: BillingTable.customerID,
165        reload: BillingTable.reload,
166        blackSubscriptionID: BillingTable.subscriptionID,
167        blackSubscription: {
168          plan: BillingTable.subscriptionPlan,
169          booked: BillingTable.timeSubscriptionBooked,
170          enrichment: BillingTable.subscription,
171        },
172        timeBlackSubscriptionSelected: BillingTable.timeSubscriptionSelected,
173        liteSubscriptionID: BillingTable.liteSubscriptionID,
174      })
175      .from(BillingTable)
176      .where(eq(BillingTable.workspaceID, workspace.id))
177      .then(
178        (rows) =>
179          rows.map((row) => ({
180            balance: `$${(row.balance / 100000000).toFixed(2)}`,
181            reload: row.reload ? "yes" : "no",
182            customerID: row.customerID,
183            GO: row.liteSubscriptionID,
184            Black: row.blackSubscriptionID
185              ? [
186                  `Black ${row.blackSubscription.enrichment!.plan}`,
187                  row.blackSubscription.enrichment!.seats > 1
188                    ? `X ${row.blackSubscription.enrichment!.seats} seats`
189                    : "",
190                  row.blackSubscription.enrichment!.coupon
191                    ? `(coupon: ${row.blackSubscription.enrichment!.coupon})`
192                    : "",
193                  `(ref: ${row.blackSubscriptionID})`,
194                ].join(" ")
195              : row.blackSubscription.booked
196                ? `Waitlist ${row.blackSubscription.plan} plan${row.timeBlackSubscriptionSelected ? " (selected)" : ""}`
197                : undefined,
198          }))[0],
199      ),
200  )
201 
202  await printTable("Payments", (tx) =>
203    tx
204      .select({
205        amount: PaymentTable.amount,
206        paymentID: PaymentTable.paymentID,
207        invoiceID: PaymentTable.invoiceID,
208        customerID: PaymentTable.customerID,
209        timeCreated: PaymentTable.timeCreated,
210        timeRefunded: PaymentTable.timeRefunded,
211      })
212      .from(PaymentTable)
213      .where(eq(PaymentTable.workspaceID, workspace.id))
214      .orderBy(sql`${PaymentTable.timeCreated} DESC`)
215      .limit(100)
216      .then((rows) =>
217        rows.map((row) => ({
218          ...row,
219          amount: `$${(row.amount / 100000000).toFixed(2)}`,
220          paymentID: row.paymentID
221            ? `https://dashboard.stripe.com/acct_1RszBH2StuRr0lbX/payments/${row.paymentID}`
222            : null,
223        })),
224      ),
225  )
226 
227  if (verbose) {
228    await printTable("28-Day Usage", (tx) =>
229      tx
230        .select({
231          date: sql<string>`DATE(${UsageTable.timeCreated})`.as("date"),
232          requests: sql<number>`COUNT(*)`.as("requests"),
233          inputTokens: sql<number>`SUM(${UsageTable.inputTokens})`.as("input_tokens"),
234          outputTokens: sql<number>`SUM(${UsageTable.outputTokens})`.as("output_tokens"),
235          reasoningTokens: sql<number>`SUM(${UsageTable.reasoningTokens})`.as("reasoning_tokens"),
236          cacheReadTokens: sql<number>`SUM(${UsageTable.cacheReadTokens})`.as("cache_read_tokens"),
237          cacheWrite5mTokens: sql<number>`SUM(${UsageTable.cacheWrite5mTokens})`.as("cache_write_5m_tokens"),
238          cacheWrite1hTokens: sql<number>`SUM(${UsageTable.cacheWrite1hTokens})`.as("cache_write_1h_tokens"),
239          cost: sql<number>`SUM(${UsageTable.cost})`.as("cost"),
240        })
241        .from(UsageTable)
242        .where(
243          and(
244            eq(UsageTable.workspaceID, workspace.id),
245            sql`${UsageTable.timeCreated} >= DATE_SUB(NOW(), INTERVAL 28 DAY)`,
246          ),
247        )
248        .groupBy(sql`DATE(${UsageTable.timeCreated})`)
249        .orderBy(sql`DATE(${UsageTable.timeCreated}) DESC`)
250        .then((rows) => {
251          const totalCost = rows.reduce((sum, r) => sum + Number(r.cost), 0)
252          const mapped = rows.map((row) => ({
253            ...row,
254            cost: `$${(Number(row.cost) / 100000000).toFixed(2)}`,
255          }))
256          if (mapped.length > 0) {
257            mapped.push({
258              date: "TOTAL",
259              requests: null as any,
260              inputTokens: null as any,
261              outputTokens: null as any,
262              reasoningTokens: null as any,
263              cacheReadTokens: null as any,
264              cacheWrite5mTokens: null as any,
265              cacheWrite1hTokens: null as any,
266              cost: `$${(totalCost / 100000000).toFixed(2)}`,
267            })
268          }
269          return mapped
270        }),
271    )
272    await printTable("Disabled Models", (tx) =>
273      tx
274        .select({
275          model: ModelTable.model,
276          timeCreated: ModelTable.timeCreated,
277        })
278        .from(ModelTable)
279        .where(eq(ModelTable.workspaceID, workspace.id))
280        .orderBy(sql`${ModelTable.timeCreated} DESC`)
281        .then((rows) =>
282          rows.map((row) => ({
283            model: row.model,
284            timeCreated: formatDate(row.timeCreated),
285          })),
286        ),
287    )
288  }
289}
290 
291function formatMicroCents(value: number | null | undefined) {
292  if (value === null || value === undefined) return null
293  return `$${(value / 100000000).toFixed(2)}`
294}
295 
296function formatDate(value: Date | null | undefined) {
297  if (!value) return null
298  return value.toISOString().split("T")[0]
299}
300 
301function formatMonthlyUsage(usage: number | null | undefined, limit: number | null | undefined) {
302  const usageText = formatMicroCents(usage) ?? "$0.00"
303  if (limit === null || limit === undefined) return `${usageText} / no limit`
304  return `${usageText} / $${limit.toFixed(2)}`
305}
306 
307function formatRetryTime(seconds: number) {
308  const days = Math.floor(seconds / 86400)
309  if (days >= 1) return `${days} day${days > 1 ? "s" : ""}`
310  const hours = Math.floor(seconds / 3600)
311  const minutes = Math.ceil((seconds % 3600) / 60)
312  if (hours >= 1) return `${hours}hr ${minutes}min`
313  return `${minutes}min`
314}
315 
316function getSubscriptionStatus(row: {
317  subscription: {
318    plan: (typeof BlackPlans)[number]
319  } | null
320  timeSubscriptionCreated: Date | null
321  fixedUsage: number | null
322  rollingUsage: number | null
323  timeFixedUpdated: Date | null
324  timeRollingUpdated: Date | null
325}) {
326  if (!row.timeSubscriptionCreated || !row.subscription) {
327    return { weekly: null, rolling: null, rateLimited: null, retryIn: null }
328  }
329 
330  const black = BlackData.getLimits({ plan: row.subscription.plan })
331  const now = new Date()
332  const week = getWeekBounds(now)
333 
334  const fixedLimit = black.fixedLimit ? centsToMicroCents(black.fixedLimit * 100) : null
335  const rollingLimit = black.rollingLimit ? centsToMicroCents(black.rollingLimit * 100) : null
336  const rollingWindowMs = (black.rollingWindow ?? 5) * 3600 * 1000
337 
338  // Calculate current weekly usage (reset if outside current week)
339  const currentWeekly =
340    row.fixedUsage && row.timeFixedUpdated && row.timeFixedUpdated >= week.start ? row.fixedUsage : 0
341 
342  // Calculate current rolling usage
343  const windowStart = new Date(now.getTime() - rollingWindowMs)
344  const currentRolling =
345    row.rollingUsage && row.timeRollingUpdated && row.timeRollingUpdated >= windowStart ? row.rollingUsage : 0
346 
347  // Check rate limiting
348  const isWeeklyLimited = fixedLimit !== null && currentWeekly >= fixedLimit
349  const isRollingLimited = rollingLimit !== null && currentRolling >= rollingLimit
350 
351  let retryIn: string | null = null
352  if (isWeeklyLimited) {
353    const retryAfter = Math.ceil((week.end.getTime() - now.getTime()) / 1000)
354    retryIn = formatRetryTime(retryAfter)
355  } else if (isRollingLimited && row.timeRollingUpdated) {
356    const retryAfter = Math.ceil((row.timeRollingUpdated.getTime() + rollingWindowMs - now.getTime()) / 1000)
357    retryIn = formatRetryTime(retryAfter)
358  }
359 
360  return {
361    weekly: fixedLimit !== null ? `${formatMicroCents(currentWeekly)} / $${black.fixedLimit}` : null,
362    rolling: rollingLimit !== null ? `${formatMicroCents(currentRolling)} / $${black.rollingLimit}` : null,
363    rateLimited: isWeeklyLimited || isRollingLimited ? "yes" : "no",
364    retryIn,
365  }
366}
367 
368function printHeader(title: string) {
369  console.log()
370  console.log("─".repeat(title.length))
371  console.log(`${title}`)
372  console.log("─".repeat(title.length))
373}
374 
375function printTable(title: string, callback: (tx: Database.TxOrDb) => Promise<any>): Promise<any> {
376  return Database.use(async (tx) => {
377    const data = await callback(tx)
378    console.log(`\n== ${title} ==`)
379    if (data.length === 0) {
380      console.log("(no data)")
381    } else {
382      console.table(data)
383    }
384    return data
385  })
386}
387