22 * Read-only OpenCode usage database access.
33 *
44 * OpenCode stores one usage-bearing JSON object per assistant message in its
5- * global SQLite database. Only the scalar fields needed for usage reporting are
6- * selected; prompts, responses, and tool output never leave the database.
5+ * global SQLite database. Both its legacy `message` projection and current
6+ * `session_message` projection are supported. Only the scalar fields needed for
7+ * usage reporting are selected; prompts, responses, and tool output never leave
8+ * the database.
79 *
810 * @module usageOpenCode
911 */
@@ -16,7 +18,13 @@ const BUN_SQLITE_MODULE_ID: string = "bun:sqlite";
1618const OPEN_CODE_STABLE_DATABASE = "opencode.db" ;
1719const OPEN_CODE_CHANNEL_DATABASE = / ^ o p e n c o d e - [ a - z A - Z 0 - 9 . _ - ] + \. d b $ / ;
1820
19- const OPEN_CODE_USAGE_QUERY = `
21+ const OPEN_CODE_MESSAGE_TABLES_QUERY = `
22+ SELECT name
23+ FROM sqlite_master
24+ WHERE type = 'table' AND name IN ('session_message', 'message')
25+ ` ;
26+
27+ const OPEN_CODE_LEGACY_USAGE_QUERY = `
2028 SELECT
2129 id AS messageId,
2230 session_id AS sessionId,
@@ -35,12 +43,52 @@ const OPEN_CODE_USAGE_QUERY = `
3543 AND json_extract(data, '$.role') = 'assistant'
3644` ;
3745
38- const OPEN_CODE_MALFORMED_QUERY = `
46+ const OPEN_CODE_LEGACY_MALFORMED_QUERY = `
3947 SELECT COUNT(*) AS records
4048 FROM message
4149 WHERE time_created >= ? AND NOT json_valid(data)
4250` ;
4351
52+ const OPEN_CODE_CURRENT_USAGE_QUERY = `
53+ SELECT
54+ id AS messageId,
55+ session_id AS sessionId,
56+ time_created AS timestampMs,
57+ json_extract(data, '$.model.providerID') AS providerId,
58+ json_extract(data, '$.model.id') AS modelId,
59+ json_extract(data, '$.tokens.input') AS inputTokens,
60+ json_extract(data, '$.tokens.output') AS outputTokens,
61+ json_extract(data, '$.tokens.reasoning') AS reasoningTokens,
62+ json_extract(data, '$.tokens.cache.read') AS cacheReadTokens,
63+ json_extract(data, '$.tokens.cache.write') AS cacheWriteTokens,
64+ json_extract(data, '$.cost') AS costUsd
65+ FROM session_message
66+ WHERE time_created >= ?
67+ AND type = 'assistant'
68+ AND json_valid(data)
69+ ` ;
70+
71+ const OPEN_CODE_CURRENT_MALFORMED_QUERY = `
72+ SELECT COUNT(*) AS records
73+ FROM session_message
74+ WHERE time_created >= ?
75+ AND type = 'assistant'
76+ AND NOT json_valid(data)
77+ ` ;
78+
79+ const OPEN_CODE_TABLE_QUERIES = {
80+ session_message : {
81+ usage : OPEN_CODE_CURRENT_USAGE_QUERY ,
82+ malformed : OPEN_CODE_CURRENT_MALFORMED_QUERY ,
83+ } ,
84+ message : {
85+ usage : OPEN_CODE_LEGACY_USAGE_QUERY ,
86+ malformed : OPEN_CODE_LEGACY_MALFORMED_QUERY ,
87+ } ,
88+ } as const ;
89+
90+ type OpenCodeMessageTable = keyof typeof OPEN_CODE_TABLE_QUERIES ;
91+
4492interface OpenCodeUsageRow {
4593 readonly messageId ?: unknown ;
4694 readonly sessionId ?: unknown ;
@@ -59,8 +107,12 @@ interface CountRow {
59107 readonly records ?: unknown ;
60108}
61109
110+ interface TableNameRow {
111+ readonly name ?: unknown ;
112+ }
113+
62114interface SqliteStatement {
63- readonly all : ( sinceMs : number ) => readonly unknown [ ] ;
115+ readonly all : ( ... parameters : readonly number [ ] ) => readonly unknown [ ] ;
64116}
65117
66118interface SqliteDatabase {
@@ -191,7 +243,7 @@ async function openDatabase(databasePath: string): Promise<SqliteDatabase> {
191243 return {
192244 statement : ( sql ) => {
193245 const statement = database . query ( sql ) ;
194- return { all : ( sinceMs ) => statement . all ( sinceMs ) } ;
246+ return { all : ( ... parameters ) => statement . all ( ... parameters ) } ;
195247 } ,
196248 close : ( ) => database . close ( ) ,
197249 } ;
@@ -202,7 +254,7 @@ async function openDatabase(databasePath: string): Promise<SqliteDatabase> {
202254 return {
203255 statement : ( sql ) => {
204256 const statement = database . prepare ( sql ) ;
205- return { all : ( sinceMs ) => statement . all ( sinceMs ) } ;
257+ return { all : ( ... parameters ) => statement . all ( ... parameters ) } ;
206258 } ,
207259 close : ( ) => database . close ( ) ,
208260 } ;
@@ -219,18 +271,41 @@ export async function readOpenCodeUsage(
219271 let database : SqliteDatabase | undefined ;
220272 try {
221273 database = await openDatabase ( databasePath ) ;
222- const rows = database . statement ( OPEN_CODE_USAGE_QUERY ) . all ( sinceMs ) ;
223- const records : UsageRecord [ ] = [ ] ;
274+ const tableRows = database . statement ( OPEN_CODE_MESSAGE_TABLES_QUERY ) . all ( ) ;
275+ const tables = new Set < OpenCodeMessageTable > ( ) ;
276+ for ( const row of tableRows ) {
277+ const name = ( row as TableNameRow ) . name ;
278+ if ( name === "session_message" || name === "message" ) tables . add ( name ) ;
279+ }
280+ if ( tables . size === 0 ) return null ;
281+
282+ // Current databases can contain both projections. Prefer the current row
283+ // when an upgraded database carries the same message ID in each table.
284+ const recordsByKey = new Map < string , UsageRecord > ( ) ;
224285 let malformedRecords = 0 ;
225- for ( const row of rows ) {
226- const parsed = decodeOpenCodeUsageRow ( row ) ;
227- if ( parsed . _tag === "Record" ) records . push ( parsed . record ) ;
228- else if ( parsed . _tag === "Malformed" ) malformedRecords += 1 ;
286+ for ( const table of [ "session_message" , "message" ] as const ) {
287+ if ( ! tables . has ( table ) ) continue ;
288+ const queries = OPEN_CODE_TABLE_QUERIES [ table ] ;
289+ const rows = database . statement ( queries . usage ) . all ( sinceMs ) ;
290+ for ( const row of rows ) {
291+ const parsed = decodeOpenCodeUsageRow ( row ) ;
292+ if ( parsed . _tag === "Record" ) {
293+ const dedupeKey = parsed . record . dedupeKey ;
294+ if ( dedupeKey === null ) {
295+ malformedRecords += 1 ;
296+ } else if ( ! recordsByKey . has ( dedupeKey ) ) {
297+ recordsByKey . set ( dedupeKey , parsed . record ) ;
298+ }
299+ } else if ( parsed . _tag === "Malformed" ) {
300+ malformedRecords += 1 ;
301+ }
302+ }
303+
304+ const [ malformed ] = database . statement ( queries . malformed ) . all ( sinceMs ) ;
305+ malformedRecords += nonNegativeInt ( ( malformed as CountRow | undefined ) ?. records ) ;
229306 }
230307
231- const [ malformed ] = database . statement ( OPEN_CODE_MALFORMED_QUERY ) . all ( sinceMs ) ;
232- const invalidJsonRecords = nonNegativeInt ( ( malformed as CountRow | undefined ) ?. records ) ;
233- return { records, malformedRecords : malformedRecords + invalidJsonRecords } ;
308+ return { records : [ ...recordsByKey . values ( ) ] , malformedRecords } ;
234309 } catch {
235310 return null ;
236311 } finally {
0 commit comments