@@ -11,6 +11,7 @@ import * as FileSystem from "effect/FileSystem";
1111import * as Option from "effect/Option" ;
1212import * as Path from "effect/Path" ;
1313import type * as PlatformError from "effect/PlatformError" ;
14+ import * as Semaphore from "effect/Semaphore" ;
1415import * as SqlClient from "effect/sql/SqlClient" ;
1516
1617import type { OpenCodeSettings } from "../settings.ts" ;
@@ -93,6 +94,18 @@ export interface OpenCodeUsageReadResult {
9394 readonly error : boolean ;
9495}
9596
97+ interface CachedRow {
98+ readonly updated : unknown ;
99+ readonly size : unknown ;
100+ readonly created : unknown ;
101+ readonly record : UsageRecord | null ;
102+ }
103+
104+ // Parsed rows by `${databasePath}\0${table}`, then rowid, so unchanged rows are not read again.
105+ const rowCache = new Map < string , { version : string ; rows : Map < number , CachedRow > } > ( ) ;
106+ // Concurrent scans of the same database would each read every uncached row.
107+ const scanLock = Semaphore . makeUnsafe ( 1 ) ;
108+
96109const isNotFound = ( cause : PlatformError . PlatformError ) => cause . reason . _tag === "NotFound" ;
97110const MESSAGE_PAGE_SIZE = 64 ;
98111
@@ -116,6 +129,7 @@ const LEGACY_STAT_BATCH = 256;
116129export const readOpenCodeUsage = Effect . fn ( "readOpenCodeUsage" ) ( function * (
117130 root : string ,
118131 sinceMs : number ,
132+ retentionCutoffMs = sinceMs ,
119133) : Effect . fn . Return < OpenCodeUsageReadResult , never , FileSystem . FileSystem | Path . Path > {
120134 const fileSystem = yield * FileSystem . FileSystem ;
121135 const path = yield * Path . Path ;
@@ -164,6 +178,22 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
164178 found = true ;
165179 const file = { path : path . join ( root , name ) , records : [ ] as UsageRecord [ ] } ;
166180 files . push ( file ) ;
181+ // Taken before the snapshot opens, so a write that races the scan invalidates its rows.
182+ const [ main , wal ] = yield * Effect . forEach ( [ file . path , `${ file . path } -wal` ] , ( target ) =>
183+ Effect . option ( fileSystem . stat ( target ) ) ,
184+ ) ;
185+ const stamp = ( info : Option . Option < FileSystem . File . Info > ) =>
186+ Option . match ( info , {
187+ onNone : ( ) => "none" ,
188+ onSome : ( stat ) => `${ stat . size } :${ Option . getOrNull ( stat . mtime ) ?. getTime ( ) } ` ,
189+ } ) ;
190+ // A replaced or restored database can repeat rowids and change markers, so every key includes
191+ // the main file; WAL commits leave it alone until a checkpoint.
192+ const identity = Option . match ( main ! , {
193+ onNone : ( ) => "none" ,
194+ onSome : ( stat ) =>
195+ `${ stat . dev } :${ Option . getOrNull ( stat . ino ) } :${ Option . getOrNull ( stat . birthtime ) ?. getTime ( ) } /${ stamp ( main ! ) } ` ,
196+ } ) ;
167197 yield * Effect . gen ( function * ( ) {
168198 const sql = yield * SqlClient . SqlClient ;
169199 // Fail promptly on live-writer locks instead of stalling the server.
@@ -182,30 +212,53 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
182212 ) ,
183213 ) ;
184214 const timestamp = columns . has ( "time_created" ) ? "time_created" : "NULL" ;
215+ const updated = columns . has ( "time_updated" ) ? "time_updated" : "NULL" ;
216+ // Without time_updated an in-place edit can keep the length, so any database write counts.
217+ const version = updated === "NULL" ? `${ identity } /${ stamp ( wal ! ) } ` : identity ;
185218 const predicates = table === "session_message" ? [ "type = 'assistant'" ] : [ ] ;
186219 if ( timestamp !== "NULL" ) predicates . push ( "time_created >= ?" ) ;
187- // List only rowids so SQLite can use time indexes without reading message bodies.
188- const listed = ( yield * sql . unsafe < { readonly row_id : number } > (
189- `SELECT rowid AS row_id FROM ${ table }
220+ // List rowids and change markers so SQLite can use time indexes without reading message
221+ // bodies; octet_length comes from the record header.
222+ const listed = ( yield * sql . unsafe < {
223+ readonly row_id : number ;
224+ readonly updated : unknown ;
225+ readonly size : unknown ;
226+ readonly created : unknown ;
227+ } > (
228+ `SELECT rowid AS row_id, ${ updated } AS updated, octet_length(data) AS size,
229+ ${ timestamp } AS created
230+ FROM ${ table }
190231 ${ predicates . length ? `WHERE ${ predicates . join ( " AND " ) } ` : "" } ` ,
191232 timestamp === "NULL" ? [ ] : [ sinceMs ] ,
192233 ) ) . toSorted ( ( a , b ) => a . row_id - b . row_id ) ;
193234 databaseRows += listed . length ;
235+ const key = `${ file . path } \0${ table } ` ;
236+ const previous = rowCache . get ( key ) ;
237+ const reusable = previous ?. version === version ? previous . rows : undefined ;
238+ const cached = new Map < number , CachedRow > ( ) ;
239+ const misses : typeof listed = [ ] ;
240+ for ( const row of listed ) {
241+ const hit = reusable ?. get ( row . row_id ) ;
242+ if ( hit && hit . updated === row . updated && hit . size === row . size )
243+ cached . set ( row . row_id , hit ) ;
244+ else misses . push ( row ) ;
245+ }
194246 // Filter and project in SQLite: user diffs and assistant bodies can be hundreds of MB.
195247 const validAssistant = `CASE WHEN typeof(data) = 'text' AND json_valid(data)
196248 THEN json_type(data, '$.role') IS NULL OR data ->> '$.role' = 'assistant' END` ;
197249 // Omit per-page spans; the enclosing scan records row and page counts.
198- for ( let start = 0 ; start < listed . length ; start += MESSAGE_PAGE_SIZE ) {
199- const page = listed . slice ( start , start + MESSAGE_PAGE_SIZE ) ;
250+ for ( let start = 0 ; start < misses . length ; start += MESSAGE_PAGE_SIZE ) {
251+ const page = misses . slice ( start , start + MESSAGE_PAGE_SIZE ) ;
200252 databasePages ++ ;
201253 const rows = yield * sql
202254 . unsafe < {
255+ readonly row_id : number ;
203256 readonly id : unknown ;
204257 readonly session_id : unknown ;
205258 readonly data : unknown ;
206259 readonly created : unknown ;
207260 } > (
208- `SELECT id, session_id, ${ timestamp } AS created, json_object(
261+ `SELECT rowid AS row_id, id, session_id, ${ timestamp } AS created, json_object(
209262 'tokens', data -> '$.tokens', 'cost', data -> '$.cost',
210263 'model', data -> '$.model', 'modelID', data -> '$.modelID',
211264 'providerID', data -> '$.providerID',
@@ -217,22 +270,39 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
217270 page . map ( ( row ) => row . row_id ) ,
218271 )
219272 . pipe ( Effect . withTracerEnabled ( false ) ) ;
273+ const parsed = new Map < number , UsageRecord | null > ( ) ;
220274 for ( const row of rows ) {
221- append (
222- file . records ,
275+ parsed . set (
276+ row . row_id ,
223277 parseOpenCodeMessage ( text ( row . data ) , {
224278 id : text ( row . id ) ,
225279 sessionId : text ( row . session_id ) ,
226280 ...( typeof row . created === "number" ? { timestampMs : row . created } : { } ) ,
227281 } ) ,
228282 ) ;
229283 }
284+ // Rows the SQL filter dropped are cached as empty so they are not read again either.
285+ for ( const row of page ) {
286+ cached . set ( row . row_id , { ...row , record : parsed . get ( row . row_id ) ?? null } ) ;
287+ }
230288 yield * Effect . yieldNow ;
231289 }
290+ for ( const row of listed ) append ( file . records , cached . get ( row . row_id ) ! . record ) ;
291+ // Rows before this window were not listed; keep them for a wider window until retention.
292+ for ( const [ rowId , entry ] of reusable ?? [ ] ) {
293+ const created = entry . created ;
294+ if ( typeof created === "number" && created < sinceMs && created >= retentionCutoffMs )
295+ cached . set ( rowId , entry ) ;
296+ }
297+ rowCache . set ( key , { version, rows : cached } ) ;
232298 }
233299 } ) . pipe (
234300 ( read ) => Effect . flatMap ( SqlClient . SqlClient , ( sql ) => sql . withTransaction ( read ) ) ,
235- Effect . provide ( NodeSqliteClient . layer ( { filename : file . path , readonly : true } ) ) ,
301+ // Run the scan on a reader worker so large databases do not block the server's event loop.
302+ NodeSqliteClient . readOnly ,
303+ Effect . provide (
304+ NodeSqliteClient . layer ( { filename : file . path , readonly : true , readerWorker : true } ) ,
305+ ) ,
236306 Effect . catchTags ( {
237307 SqlError : ( ) => {
238308 error = true ;
@@ -312,7 +382,7 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
312382 "usage.database_pages" : databasePages ,
313383 } ) ;
314384 return { files, missing : ! found && ! error , error } ;
315- } ) ;
385+ } , scanLock . withPermit ) ;
316386
317387const resolveOpenCodeDataDirs = Effect . fn ( "resolveOpenCodeDataDirs" ) ( function * (
318388 environment : NodeJS . ProcessEnv ,
@@ -346,15 +416,19 @@ export type OpenCodeUsageReaderEnv = FileSystem.FileSystem | Path.Path;
346416export const openCodeUsageReader : ProviderUsageReader < OpenCodeSettings , OpenCodeUsageReaderEnv > = {
347417 kind : "scan" ,
348418 provider : "opencode" ,
349- scan : Effect . fn ( "openCodeUsageReader.scan" ) ( function * ( { instances, windowStartMs } ) {
419+ scan : Effect . fn ( "openCodeUsageReader.scan" ) ( function * ( {
420+ instances,
421+ windowStartMs,
422+ retentionCutoffMs,
423+ } ) {
350424 const roots = new Set < string > ( ) ;
351425 for ( const instance of instances ) {
352426 for ( const root of yield * resolveOpenCodeDataDirs ( instance . environment ) ) roots . add ( root ) ;
353427 }
354428 return yield * Effect . forEach (
355429 roots ,
356430 ( dir ) =>
357- readOpenCodeUsage ( dir , windowStartMs ) . pipe (
431+ readOpenCodeUsage ( dir , windowStartMs , retentionCutoffMs ) . pipe (
358432 Effect . map ( ( result ) => ( {
359433 dir,
360434 files : result . missing && ! result . error ? null : result . files ,
0 commit comments