lookup-user.ts 11 KB

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