[{"_1":2,"_264":-5,"_265":-5},"loaderData",{"_3":4,"_91":92,"_198":199},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":22,"_29":22,"_30":22,"_31":32,"_33":22},"auth",{"_9":10,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":-5,"_20":-5,"_21":22},"tokenType","session_token","sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","d481f3d28ac6dd3f9b89bb10f2a0c9798df3020a","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":22,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":22,"_90":22},"__clerk_ssr_state",{"_9":10,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":-5,"_20":-5,"_21":22},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":22,"_21":22,"_82":72,"_83":84,"_85":72,"_86":45,"_87":22,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/_app.x.$handle.$name",{"_93":94,"_95":96,"_97":43,"_98":99,"_100":-7,"_101":102,"_148":149,"_179":180,"_181":182,"_187":188,"_162":189,"_192":-5,"_193":-5,"_194":22,"_195":22,"_196":197},"handle","std","name","OpenAIUsage","branchName","path","main.tsx","version","project",{"_103":104,"_95":96,"_105":106,"_107":-5,"_108":109,"_110":111,"_112":-5,"_113":-5,"_14":114,"_115":-5,"_116":109,"_117":-5,"_118":32,"_16":119,"_120":121,"_122":123,"_124":123,"_125":123,"_126":123,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_147":22},"id","be580a1e-25fe-11f0-b0b8-569c3dd06744","createdAt",["D",1746043649383],"deletedAt","legacyEnvVars",true,"privacy","public","description","imageUrl","c9143560-63b6-444f-8f9f-e2647fabe09f","pinnedFileId","pinPreview","screenshotVersion","likeCount","c606f87f-4d7e-48c8-9c39-ef5ab94aec49","userPerm","r","valPerm","rw","blobPerm","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJVaVhpQ1BId3dscVpBbkdZVWlMNjJtcjkxWS5wbmcifQ","org",{"_93":94,"_134":131},"avatar","branch",{"_100":137,"_95":43,"_103":138,"_139":104,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":96,"_146":94},9,"be584498-25fe-11f0-b0b8-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","tree",[150,170],{"_103":151,"_95":152,"_153":154,"_155":-5,"_156":138,"_157":158,"_159":109,"_105":160,"_100":32,"_161":162,"_163":164,"_165":22,"_166":-5,"_167":168,"_98":169},"be5a4df6-25fe-11f0-b0b8-569c3dd06744","README.md","fileId","be5a4e6e-25fe-11f0-b0b8-569c3dd06744","parentId","branchId","branchVersion",1,"latest",["D",1717617062336],"fileType","file","content","# OpenAI Proxy Metrics\n\nWe write openAI usage data to a `openai_usage` sqlite table. This script val is imported into the openai proxy. Use this val to run administrative scripts: https://www.val.town/v/std/OpenAIUsageScript\n\nMigrated from folder: openai/OpenAIUsage","deleted","label","children",[],[152],{"_103":171,"_95":99,"_153":172,"_155":-5,"_156":138,"_157":137,"_159":109,"_105":173,"_100":174,"_161":175,"_163":176,"_165":22,"_166":-5,"_167":177,"_98":178},"be7ea5ac-25fe-11f0-b0b8-569c3dd06744","be5a490a-25fe-11f0-b0b8-569c3dd06744",["D",1730471231627],8,"script","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nexport class OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async recentGpt4Usage(userId: string) {\n const now = new Date();\n now.setHours(now.getHours() - 24);\n let resp = await sqlite.execute({\n sql: `SELECT count(*) \nFROM openai_usage \nWHERE (\n (model LIKE 'gpt-4%' AND model NOT LIKE '%mini%')\n OR model LIKE 'o1-preview%'\n) \nAND user_id = ? \nAND timestamp > ?`,\n args: [userId, now.toISOString()],\n });\n return resp.rows[0][0];\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}",[],[99],"files",[150,170],"counts",{"_183":32,"_184":158,"_185":32,"_186":32},"pulls","branches","forks","envVars","contents",[],{"_103":171,"_95":99,"_153":172,"_155":-5,"_156":138,"_157":137,"_159":109,"_105":190,"_100":174,"_161":175,"_163":176,"_165":22,"_166":-5,"_98":191},["D",1730471231627],[99],"upstreamProject","upstreamBranch","emptyEnvVars","isNew","flags",[],"routes/_app.x.$handle.$name.branch.$branch.history_.$",{"_93":94,"_95":96,"_97":43,"_98":99,"_101":200,"_162":205,"_208":209,"_249":250,"_261":-7,"_262":32,"_263":22},{"_103":104,"_95":96,"_105":201,"_107":-5,"_108":109,"_110":111,"_112":-5,"_113":-5,"_14":114,"_115":-5,"_116":109,"_117":-5,"_118":32,"_16":119,"_120":121,"_122":123,"_124":123,"_125":123,"_126":123,"_127":121,"_128":121,"_23":202,"_132":203,"_135":204,"_147":22},["D",1746043649383],{"_93":94,"_130":131},{"_93":94,"_134":131},{"_100":137,"_95":43,"_103":138,"_139":104,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":96,"_146":94},{"_103":171,"_95":99,"_153":172,"_155":-5,"_156":138,"_157":137,"_159":109,"_105":206,"_100":174,"_161":175,"_163":176,"_165":22,"_166":-5,"_98":207},["D",1730471231627],[99],"history",[210,212,217,222,227,232,237,242,246],{"_103":171,"_156":138,"_153":172,"_95":99,"_163":176,"_161":175,"_100":174,"_155":-5,"_105":211,"_157":137,"_166":-5},["D",1730471231627],{"_103":213,"_156":138,"_153":172,"_95":99,"_163":214,"_161":175,"_100":215,"_155":-5,"_105":216,"_157":174,"_166":-5},"be7abeec-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nexport class OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async recentGpt4Usage(userId: string) {\n const now = new Date();\n now.setHours(now.getHours() - 24);\n let resp = await sqlite.execute({\n sql: `select count(*) from openai_usage where model LIKE 'gpt-4%' and model NOT LIKE '%mini%' and user_id = ? and timestamp > ?`,\n args: [userId, now.toISOString()],\n });\n return resp.rows[0][0];\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}",7,["D",1723667076655],{"_103":218,"_156":138,"_153":172,"_95":99,"_163":219,"_161":175,"_100":220,"_155":-5,"_105":221,"_157":215,"_166":-5},"be76cc38-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nexport class OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async recentGpt4Usage(userId: string) {\n const now = new Date();\n now.setHours(now.getHours() - 24);\n let resp = await sqlite.execute({\n sql: `select count(*) from openai_usage where model LIKE 'gpt-4%' and user_id = ? and timestamp > ?`,\n args: [userId, now.toISOString()],\n });\n return resp.rows[0][0];\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}",6,["D",1718044821315],{"_103":223,"_156":138,"_153":172,"_95":99,"_163":224,"_161":175,"_100":225,"_155":-5,"_105":226,"_157":220,"_166":-5},"be70029a-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nexport class OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}",5,["D",1717617391269],{"_103":228,"_156":138,"_153":172,"_95":99,"_163":229,"_161":175,"_100":230,"_155":-5,"_105":231,"_157":225,"_166":-5},"be6bfcea-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nexport class OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}\n\nconst client = new OpenAIUsage();\nawait client.migrate();\n// await client.drop();\n// await client.writeUsage({\n// userId: \"af90sd7\",\n// handle: \"test\",\n// tokens: 50,\n// tier: \"free\",\n// model: \"gpt-4.0\",\n// });\n\n// const stats = await client.usageStats({\n// start: \"2024-05-29\",\n// end: \"2024-06-06\",\n// intervalMinutes: HOUR,\n// timeZone: \"America/New_York\",\n// });\n\n// console.log(stats);\n\n// console.log(await client.query(\"select * from openai_usage\"));",4,["D",1717617363935],{"_103":233,"_156":138,"_153":172,"_95":99,"_163":234,"_161":175,"_100":235,"_155":-5,"_105":236,"_157":230,"_166":-5},"be67b586-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nexport class OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}\n\nconst client = new OpenAIUsage();\nawait client.migrate();\nawait client.drop();\n// await client.writeUsage({\n// userId: \"af90sd7\",\n// handle: \"test\",\n// tokens: 50,\n// tier: \"free\",\n// model: \"gpt-4.0\",\n// });\n\n// const stats = await client.usageStats({\n// start: \"2024-05-29\",\n// end: \"2024-06-06\",\n// intervalMinutes: HOUR,\n// timeZone: \"America/New_York\",\n// });\n\n// console.log(stats);\n\n// console.log(await client.query(\"select * from openai_usage\"));",3,["D",1717617314341],{"_103":238,"_156":138,"_153":172,"_95":99,"_163":239,"_161":175,"_100":240,"_155":-5,"_105":241,"_157":235,"_166":-5},"be62faa0-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nclass OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}\n\nconst client = new OpenAIUsage();\nawait client.migrate();\n\nawait client.writeUsage({\n userId: \"af90sd7\",\n handle: \"test\",\n tokens: 50,\n tier: \"free\",\n model: \"gpt-4.0\",\n});\n\nconst stats = await client.usageStats({\n start: \"2024-05-29\",\n end: \"2024-06-06\",\n intervalMinutes: HOUR,\n timeZone: \"America/New_York\",\n});\n\nconsole.log(stats);\n\nconsole.log(await client.query(\"select * from openai_usage\"));",2,["D",1717617128552],{"_103":243,"_156":138,"_153":172,"_95":99,"_163":244,"_161":175,"_100":158,"_155":-5,"_105":245,"_157":240,"_166":-5},"be5e25a2-25fe-11f0-b0b8-569c3dd06744","import { DateTime } from \"https://cdn.skypack.dev/luxon@2.3.2\";\nimport { InStatement, sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\n\nconst createStatsQuery = (hasUserID: boolean) =>\n `WITH params AS (\n SELECT\n ? AS interval_minutes, \n ? AS start_time,\n ? AS end_time${hasUserID ? \", ? AS user_id\" : \"\"}\n )\n SELECT\n strftime('%Y-%m-%d %H:%M:00', datetime(\n (strftime('%s', timestamp) / (interval_minutes * 60)) * (interval_minutes * 60), 'unixepoch'\n )) AS bucket,\n SUM(tokens) AS tokens,\n model,\n COUNT(*) AS count\n FROM\n openai_usage,\n params\n WHERE\n timestamp BETWEEN start_time AND end_time\n ${hasUserID ? \"AND user_id = ?\" : \"\"}\n GROUP BY bucket, model\n ORDER BY bucket`;\n\nconst STATS_QUERY = createStatsQuery(false);\nconst USER_SCOPED_STATS_QUERY = createStatsQuery(true);\nconst HOUR = 60;\n\ntype UsageRow = {\n userId: string;\n handle: string;\n tier: string;\n timestamp?: Date;\n tokens: number;\n model: string;\n};\n\nclass OpenAIUsage {\n constructor() {}\n async migrate() {\n await sqlite.batch([`CREATE TABLE IF NOT EXISTS openai_usage (\n id INTEGER PRIMARY KEY,\n user_id TEXT NOT NULL,\n handle TEXT NOT NULL,\n tier TEXT NOT NULL,\n timestamp TEXT NOT NULL DEFAULT (datetime('now')),\n tokens INTEGER NOT NULL,\n model TEXT NOT NULL\n )`]);\n }\n async drop() {\n await sqlite.batch([`DROP TABLE IF EXISTS openai_usage`]);\n }\n async writeUsage(ur: UsageRow) {\n sqlite.execute({\n sql: \"INSERT INTO openai_usage (user_id, handle, tier, tokens, model) VALUES (?, ?, ?, ?, ?)\",\n args: [ur.userId, ur.handle, ur.tier, ur.tokens, ur.model],\n });\n }\n async query(statement: InStatement): Promise {\n let rows = await sqlite.execute(statement);\n // TODO: use the column names in the response so that this is not brittle to custom\n // return values or table changes.\n return rows.rows.map(r => ({\n userId: r[1],\n handle: r[2],\n tier: r[3],\n timestamp: new Date(r[4]),\n tokens: r[5],\n model: r[6],\n }));\n }\n async usageStats(\n { user_id, start, end, intervalMinutes, timeZone }: {\n user_id?: string;\n start: string;\n end: string;\n intervalMinutes: number;\n timeZone: string;\n },\n ): Promise<{ bucket: string; count: number }[]> {\n const startDate = DateTime.fromISO(start, { zone: \"utc\" }).setZone(timeZone);\n const endDate = DateTime.fromISO(end, { zone: \"utc\" }).setZone(timeZone);\n const startString = startDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const endString = endDate.toFormat(\"yyyy-MM-dd HH:mm:ss\");\n const args = [intervalMinutes, startString, endString];\n user_id && args.push(user_id);\n let resp = await sqlite.execute({\n sql: user_id ? USER_SCOPED_STATS_QUERY : STATS_QUERY,\n args,\n });\n return resp.rows.map(r => ({ bucket: r[0], tokens: r[1], model: r[2], count: r[3] }));\n }\n}\n\nconst client = new OpenAIUsage();\nawait client.migrate();\n\nawait client.writeUsage({\n userId: \"af90sd7\",\n handle: \"test\",\n tokens: 50,\n tier: \"free\",\n model: \"gpt-4.0\",\n});\n\nconst stats = await client.usageStats({\n start: \"2024-05-29\",\n end: \"2024-06-04\",\n intervalMinutes: HOUR,\n timeZone: \"America/New_York\",\n});\n\nconsole.log(stats);\n\nconsole.log(await client.query(\"select * from openai_usage\"));",["D",1717617118775],{"_103":247,"_156":138,"_153":172,"_95":99,"_163":72,"_161":175,"_100":32,"_155":-5,"_105":248,"_157":158,"_166":-5},"be5a4838-25fe-11f0-b0b8-569c3dd06744",["D",1717617062336],"versions",[251,253,254,255,256,257,258,259,260],{"_162":210,"_252":212},"previous",{"_162":212,"_252":217},{"_162":217,"_252":222},{"_162":222,"_252":227},{"_162":227,"_252":232},{"_162":232,"_252":237},{"_162":237,"_252":242},{"_162":242,"_252":246},{"_162":246,"_252":-5},"versionNumber","page","hasNext","actionData","errors"]

History for main.tsx