Skip to content

Commit c5f1369

Browse files
Yash-Singh1juliusmarmingeTymekTMmaria-rcksclaude
committed
perf(opencode): bound usage reads to projected in-window messages
Select in-window rowids before paging and project usage fields in SQLite, so scans avoid empty rowid ranges and large message bodies on the JS heap. Read each database in one snapshot and record scan row and page counts. Adapt SQL projection from Julius Marminge's work in: #27679 Adapt window selection from Tymoteusz Bielski and snapshot handling from maria-rcks in: #26613 Validation: check:changed and three OpenCode UsageService tests pass. Regression coverage includes large bodies, sparse rowids, malformed data, both SQLite formats, and concurrent WAL updates. Co-authored-by: Julius Marminge <[email protected]> Co-authored-by: Tymoteusz Bielski <[email protected]> Co-authored-by: maria-rcks <[email protected]> Co-authored-by: Claude Opus 5.5 <[email protected]>
1 parent 9110dc2 commit c5f1369

2 files changed

Lines changed: 166 additions & 22 deletions

File tree

‎packages/provider-opencode/src/server/usage.test.ts‎

Lines changed: 136 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ describe("readOpenCodeUsage", () => {
2525
const path = yield* Path.Path;
2626
const dir = yield* fileSystem.makeTempDirectoryScoped({ prefix: "usage-reader-large-test-" });
2727
const db = yield* openDatabase(path.join(dir, "opencode.db"));
28+
yield* db.unsafe("PRAGMA journal_mode = WAL");
2829
yield* db.unsafe(
2930
"CREATE TABLE message (id TEXT, session_id TEXT, data TEXT, time_created INTEGER)",
3031
);
@@ -34,7 +35,7 @@ describe("readOpenCodeUsage", () => {
3435
modelID: "claude-sonnet-4-5",
3536
time: { created: sinceMs },
3637
tokens: { input: 100, output: 20 },
37-
body: "x".repeat(64 * 1024),
38+
body: "x".repeat(256 * 1024),
3839
};
3940
const data = JSON.stringify(message);
4041
const insert = (rowId: number, id: string, payload: string, created = sinceMs) =>
@@ -47,9 +48,24 @@ describe("readOpenCodeUsage", () => {
4748
}
4849
yield* insert(300, "old", data, sinceMs - 1);
4950
yield* insert(301, "user", JSON.stringify({ ...message, role: "user" }));
51+
for (let index = 0; index < 32; index++) {
52+
yield* insert(
53+
400 + index,
54+
`user-${index}`,
55+
JSON.stringify({
56+
role: "user",
57+
summary: { diffs: [{ patch: "x".repeat(1024 * 1024) }] },
58+
}),
59+
);
60+
}
5061
yield* insert(302, "invalid", "{invalid");
5162
yield* insert(303, "msg-0", data);
5263

64+
const writer = yield* Effect.acquireRelease(
65+
Effect.sync(() => new NodeSqlite.DatabaseSync(path.join(dir, "opencode.db"))),
66+
(database) => Effect.sync(() => database.close()),
67+
);
68+
let changed = false;
5369
let largestRawResult = 0;
5470
const originalAll = NodeSqlite.StatementSync.prototype.all;
5571
yield* Effect.acquireRelease(
@@ -64,6 +80,12 @@ describe("readOpenCodeUsage", () => {
6480
0,
6581
);
6682
largestRawResult = Math.max(largestRawResult, rawSize);
83+
if (!changed && rawSize > 0) {
84+
changed = true;
85+
writer
86+
.prepare("UPDATE message SET data = ? WHERE id = ?")
87+
.run(JSON.stringify({ ...message, tokens: { input: 900, output: 20 } }), "msg-129");
88+
}
6789
return rows;
6890
}),
6991
),
@@ -80,8 +102,65 @@ describe("readOpenCodeUsage", () => {
80102
records.reduce((total, record) => total + record.totals.uncachedInputTokens, 0),
81103
13000,
82104
);
105+
assert.strictEqual(
106+
(yield* readOpenCodeUsage(dir, sinceMs)).files
107+
.flatMap((file) => file.records)
108+
.find((record) => record.dedupeKey === "opencode:msg-129")?.totals.uncachedInputTokens,
109+
900,
110+
);
83111
assert.isAbove(largestRawResult, 0);
84-
assert.isBelow(largestRawResult, 6 * 1024 * 1024);
112+
assert.isBelow(largestRawResult, 64 * 1024);
113+
}).pipe(Effect.provide(NodeServices.layer)),
114+
);
115+
116+
it.effect("reads statements in proportion to the window despite sparse, unordered rowids", () =>
117+
Effect.gen(function* () {
118+
const fileSystem = yield* FileSystem.FileSystem;
119+
const path = yield* Path.Path;
120+
const dir = yield* fileSystem.makeTempDirectoryScoped({ prefix: "usage-window-" });
121+
const db = yield* openDatabase(path.join(dir, "opencode.db"));
122+
yield* db.unsafe(
123+
"CREATE TABLE message (id TEXT, session_id TEXT, data TEXT, time_created INTEGER)",
124+
);
125+
yield* db.unsafe("CREATE INDEX message_time ON message(time_created)");
126+
const sinceMs = 1780000000000;
127+
const data = JSON.stringify({ modelID: "m", tokens: { input: 1 } });
128+
yield* db.unsafe(
129+
`WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000)
130+
INSERT INTO message SELECT 'old-' || i, 's', ?, ? FROM n`,
131+
[data, sinceMs - 1],
132+
);
133+
for (const [rowid, id] of [
134+
[-10, "a"],
135+
[10000, "b"],
136+
[20000, "c"],
137+
] as const) {
138+
yield* db.unsafe(
139+
"INSERT INTO message(rowid, id, session_id, data, time_created) VALUES (?, ?, 's', ?, ?)",
140+
[rowid, id, data, sinceMs],
141+
);
142+
}
143+
let statements = 0;
144+
const originalAll = NodeSqlite.StatementSync.prototype.all;
145+
yield* Effect.acquireRelease(
146+
Effect.sync(() =>
147+
vi.spyOn(NodeSqlite.StatementSync.prototype, "all").mockImplementation(function (
148+
this: NodeSqlite.StatementSync,
149+
...params
150+
) {
151+
statements++;
152+
return originalAll.apply(this, params);
153+
}),
154+
),
155+
(spy) => Effect.sync(() => spy.mockRestore()),
156+
);
157+
const result = yield* readOpenCodeUsage(dir, sinceMs);
158+
assert.isFalse(result.error);
159+
assert.deepStrictEqual(
160+
result.files.flatMap((file) => file.records).map((record) => record.dedupeKey),
161+
["opencode:a", "opencode:b", "opencode:c"],
162+
);
163+
assert.isBelow(statements, 10);
85164
}).pipe(Effect.provide(NodeServices.layer)),
86165
);
87166

@@ -136,6 +215,61 @@ describe("readOpenCodeUsage", () => {
136215
}).pipe(Effect.provide(NodeServices.layer)),
137216
);
138217

218+
it.effect(
219+
"projects both SQLite formats without changing malformed or optional field handling",
220+
() =>
221+
Effect.gen(function* () {
222+
const fileSystem = yield* FileSystem.FileSystem;
223+
const path = yield* Path.Path;
224+
const dir = yield* fileSystem.makeTempDirectoryScoped({ prefix: "usage-formats-" });
225+
const db = yield* openDatabase(path.join(dir, "opencode.db"));
226+
const sinceMs = 1780000000000;
227+
const message = { model: { id: "m" }, tokens: { input: 10 }, time: { created: sinceMs } };
228+
const cases = [
229+
["assistant", JSON.stringify({ ...message, role: "assistant", cost: 0.25 }), true],
230+
["absent-role", JSON.stringify(message), true],
231+
["null-role", JSON.stringify({ ...message, role: null }), false],
232+
["user", JSON.stringify({ ...message, role: "user" }), false],
233+
["malformed", "{broken", false],
234+
["json5", "{role:'assistant'}", false],
235+
["null", null, false],
236+
["blob", new Uint8Array(Buffer.from(JSON.stringify(message))), false],
237+
["fallback", JSON.stringify({ modelID: "fallback-model", tokens: { output: 5 } }), true],
238+
] as const;
239+
for (const table of ["message", "session_message"]) {
240+
yield* db.unsafe(
241+
`CREATE TABLE ${table} (id TEXT, session_id TEXT, data, type TEXT, time_created INTEGER)`,
242+
);
243+
for (const [id, data] of cases) {
244+
yield* db.unsafe(`INSERT INTO ${table} VALUES (?, 's', ?, 'assistant', ?)`, [
245+
`${table}-${id}`,
246+
data,
247+
sinceMs,
248+
]);
249+
}
250+
yield* db.unsafe(`INSERT INTO ${table} VALUES (?, 's', ?, 'user', ?)`, [
251+
`${table}-type-user`,
252+
JSON.stringify(message),
253+
sinceMs,
254+
]);
255+
}
256+
const result = yield* readOpenCodeUsage(dir, sinceMs);
257+
assert.isFalse(result.error);
258+
const records = result.files.flatMap((file) => file.records);
259+
assert.deepStrictEqual(
260+
records.map((record) => record.dedupeKey),
261+
[
262+
...cases.filter(([, , valid]) => valid).map(([id]) => `opencode:message-${id}`),
263+
"opencode:message-type-user",
264+
...cases.filter(([, , valid]) => valid).map(([id]) => `opencode:session_message-${id}`),
265+
],
266+
);
267+
assert.strictEqual(records[0]?.reportedCostUsd, 0.25);
268+
assert.strictEqual(records[2]?.model, "fallback-model");
269+
assert.strictEqual(records[2]?.timestampMs, sinceMs);
270+
}).pipe(Effect.provide(NodeServices.layer)),
271+
);
272+
139273
it.effect("reads only legacy OpenCode messages written since the window opened", () =>
140274
Effect.gen(function* () {
141275
const fileSystem = yield* FileSystem.FileSystem;

‎packages/provider-opencode/src/server/usage.ts‎

Lines changed: 30 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,8 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
111111
const seen = new Set<string>();
112112
let found = false;
113113
let error = false;
114+
let databaseRows = 0;
115+
let databasePages = 0;
114116
const append = (records: UsageRecord[], record: UsageRecord | null) => {
115117
if (record === null || record.timestampMs < sinceMs) return;
116118
if (record.dedupeKey !== null) {
@@ -170,33 +172,36 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
170172
const timestamp = columns.has("time_created") ? "time_created" : "NULL";
171173
const predicates = table === "session_message" ? ["type = 'assistant'"] : [];
172174
if (timestamp !== "NULL") predicates.push("time_created >= ?");
173-
predicates.push("rowid > ?", "rowid <= ?");
174-
const [range] = yield* sql.unsafe<{
175-
readonly first_row_id: number | null;
176-
readonly last_row_id: number | null;
177-
}>(
178-
`SELECT (SELECT MIN(rowid) FROM ${table}) AS first_row_id, (SELECT MAX(rowid) FROM ${table}) AS last_row_id`,
179-
);
180-
const firstRowId = range?.first_row_id;
181-
const lastRowId = range?.last_row_id;
182-
if (typeof firstRowId !== "number" || typeof lastRowId !== "number") continue;
183-
// StatementSync.all materializes results; page by rowid and exclude mid-scan appends.
184-
// Omit tracing because large histories require thousands of statements.
185-
for (let after = firstRowId - 1; after < lastRowId; after += MESSAGE_PAGE_SIZE) {
175+
// List only rowids so SQLite can use time indexes without reading message bodies.
176+
const listed = (yield* sql.unsafe<{ readonly row_id: number }>(
177+
`SELECT rowid AS row_id FROM ${table}
178+
${predicates.length ? `WHERE ${predicates.join(" AND ")}` : ""}`,
179+
timestamp === "NULL" ? [] : [sinceMs],
180+
)).toSorted((a, b) => a.row_id - b.row_id);
181+
databaseRows += listed.length;
182+
// Filter and project in SQLite: user diffs and assistant bodies can be hundreds of MB.
183+
const validAssistant = `CASE WHEN typeof(data) = 'text' AND json_valid(data)
184+
THEN json_type(data, '$.role') IS NULL OR data ->> '$.role' = 'assistant' END`;
185+
// Omit per-page spans; the enclosing scan records row and page counts.
186+
for (let start = 0; start < listed.length; start += MESSAGE_PAGE_SIZE) {
187+
const page = listed.slice(start, start + MESSAGE_PAGE_SIZE);
188+
databasePages++;
186189
const rows = yield* sql
187190
.unsafe<{
188191
readonly id: unknown;
189192
readonly session_id: unknown;
190193
readonly data: unknown;
191194
readonly created: unknown;
192195
}>(
193-
`SELECT id, session_id, data, ${timestamp} AS created
194-
FROM ${table} WHERE ${predicates.join(" AND ")} ORDER BY rowid`,
195-
[
196-
...(timestamp === "NULL" ? [] : [sinceMs]),
197-
after,
198-
Math.min(after + MESSAGE_PAGE_SIZE, lastRowId),
199-
],
196+
`SELECT id, session_id, ${timestamp} AS created, json_object(
197+
'tokens', data -> '$.tokens', 'cost', data -> '$.cost',
198+
'model', data -> '$.model', 'modelID', data -> '$.modelID',
199+
'time', data -> '$.time', 'id', data -> '$.id', 'sessionID', data -> '$.sessionID'
200+
) AS data
201+
FROM ${table}
202+
WHERE rowid IN (${page.map(() => "?").join(", ")}) AND ${validAssistant}
203+
ORDER BY rowid`,
204+
page.map((row) => row.row_id),
200205
)
201206
.pipe(Effect.withTracerEnabled(false));
202207
for (const row of rows) {
@@ -213,6 +218,7 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
213218
}
214219
}
215220
}).pipe(
221+
(read) => Effect.flatMap(SqlClient.SqlClient, (sql) => sql.withTransaction(read)),
216222
Effect.provide(NodeSqliteClient.layer({ filename: file.path, readonly: true })),
217223
Effect.catchTags({
218224
SqlError: () => {
@@ -288,6 +294,10 @@ export const readOpenCodeUsage = Effect.fn("readOpenCodeUsage")(function* (
288294
}),
289295
);
290296
}
297+
yield* Effect.annotateCurrentSpan({
298+
"usage.database_rows": databaseRows,
299+
"usage.database_pages": databasePages,
300+
});
291301
return { files, missing: !found && !error, error };
292302
});
293303

0 commit comments

Comments
 (0)