Skip to content

Commit bbdc7dc

Browse files
Yash-Singh1juliusmarmingeTymekTMmaria-rcksclaude
authored
perf(opencode): bound usage reads to in-window messages (#33612)
OpenCode usage scans materialized complete message bodies and queried empty rowid ranges. Select in-window rowids first and project usage fields in SQLite, keeping message bodies off the JS heap. Read each database in one snapshot and record scan row and page counts. On a read-only snapshot of a 3.32 GB real database, five fresh-process runs measured 2.33x faster 30-day reads and 2.82x faster 90-day reads with identical records. These are cold application-cache measurements; OS caches were not cleared. Local check:changed and CI passed. Adapted SQL projection from #27679 and window selection/snapshot handling from #26613. Model and harness: GPT 6.1 Sol through Codex in T3 Code. 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 b10cdde commit bbdc7dc

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)