Skip to content

Commit 3bd1517

Browse files
committed
fix(usage): support OpenCode database schemas
1 parent 5c7d442 commit 3bd1517

3 files changed

Lines changed: 253 additions & 18 deletions

File tree

‎apps/server/src/usage/usageOpenCode.test.ts‎

Lines changed: 161 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -148,7 +148,7 @@ describe("resolveOpenCodeDatabasePaths", () => {
148148
});
149149

150150
describe("readOpenCodeUsage", () => {
151-
it("reads assistant usage without selecting conversation content", async () => {
151+
it("reads the legacy message schema without selecting conversation content", async () => {
152152
const directory = await NodeFSP.mkdtemp(NodePath.join(NodeOS.tmpdir(), "t3-opencode-usage-"));
153153
const databasePath = NodePath.join(directory, "opencode.db");
154154
let database: NodeSqlite.DatabaseSync | undefined;
@@ -214,4 +214,164 @@ describe("readOpenCodeUsage", () => {
214214
await NodeFSP.rm(directory, { recursive: true, force: true });
215215
}
216216
});
217+
218+
it("reads the current session_message schema", async () => {
219+
const directory = await NodeFSP.mkdtemp(NodePath.join(NodeOS.tmpdir(), "t3-opencode-usage-"));
220+
const databasePath = NodePath.join(directory, "opencode.db");
221+
let database: NodeSqlite.DatabaseSync | undefined;
222+
try {
223+
database = new NodeSqlite.DatabaseSync(databasePath);
224+
database.exec(`
225+
CREATE TABLE session_message (
226+
id TEXT PRIMARY KEY,
227+
session_id TEXT NOT NULL,
228+
type TEXT NOT NULL,
229+
time_created INTEGER NOT NULL,
230+
data TEXT NOT NULL
231+
)
232+
`);
233+
const insert = database.prepare(
234+
"INSERT INTO session_message (id, session_id, type, time_created, data) VALUES (?, ?, ?, ?, ?)",
235+
);
236+
insert.run(
237+
"msg_current",
238+
"ses_current",
239+
"assistant",
240+
3000,
241+
JSON.stringify({
242+
agent: "build",
243+
model: { providerID: "anthropic", id: "claude-sonnet-4-5" },
244+
tokens: { input: 20, output: 4, reasoning: 6, cache: { read: 8, write: 10 } },
245+
cost: 0.5,
246+
content: [{ type: "text", text: "must never be selected" }],
247+
}),
248+
);
249+
insert.run("msg_user", "ses_current", "user", 3001, JSON.stringify({ text: "not usage" }));
250+
database.close();
251+
database = undefined;
252+
253+
const result = await readOpenCodeUsage(databasePath, 2500);
254+
255+
expect(result).toEqual({
256+
malformedRecords: 0,
257+
records: [
258+
{
259+
provider: "opencode",
260+
timestampMs: 3000,
261+
model: "anthropic/claude-sonnet-4-5",
262+
sessionId: "ses_current",
263+
totals: {
264+
uncachedInputTokens: 20,
265+
cachedInputTokens: 8,
266+
cacheCreationTokens: 10,
267+
outputTokens: 10,
268+
reasoningTokens: 6,
269+
},
270+
reportedCostUsd: 0.5,
271+
dedupeKey: "opencode:msg_current",
272+
},
273+
],
274+
});
275+
} finally {
276+
database?.close();
277+
await NodeFSP.rm(directory, { recursive: true, force: true });
278+
}
279+
});
280+
281+
it("reads both projections and deduplicates message IDs", async () => {
282+
const directory = await NodeFSP.mkdtemp(NodePath.join(NodeOS.tmpdir(), "t3-opencode-usage-"));
283+
const databasePath = NodePath.join(directory, "opencode.db");
284+
let database: NodeSqlite.DatabaseSync | undefined;
285+
try {
286+
database = new NodeSqlite.DatabaseSync(databasePath);
287+
database.exec(`
288+
CREATE TABLE message (
289+
id TEXT PRIMARY KEY,
290+
session_id TEXT NOT NULL,
291+
time_created INTEGER NOT NULL,
292+
data TEXT NOT NULL
293+
);
294+
CREATE TABLE session_message (
295+
id TEXT PRIMARY KEY,
296+
session_id TEXT NOT NULL,
297+
type TEXT NOT NULL,
298+
time_created INTEGER NOT NULL,
299+
data TEXT NOT NULL
300+
);
301+
`);
302+
const insertLegacy = database.prepare(
303+
"INSERT INTO message (id, session_id, time_created, data) VALUES (?, ?, ?, ?)",
304+
);
305+
const insertCurrent = database.prepare(
306+
"INSERT INTO session_message (id, session_id, type, time_created, data) VALUES (?, ?, ?, ?, ?)",
307+
);
308+
insertLegacy.run(
309+
"msg_shared",
310+
"ses_shared",
311+
4000,
312+
JSON.stringify({
313+
role: "assistant",
314+
providerID: "legacy-provider",
315+
modelID: "legacy-model",
316+
tokens: { input: 1, output: 1, reasoning: 0, cache: { read: 0, write: 0 } },
317+
cost: 1,
318+
}),
319+
);
320+
insertLegacy.run(
321+
"msg_legacy",
322+
"ses_legacy",
323+
4001,
324+
JSON.stringify({
325+
role: "assistant",
326+
providerID: "openai",
327+
modelID: "gpt-5",
328+
tokens: { input: 2, output: 1, reasoning: 0, cache: { read: 0, write: 0 } },
329+
cost: 0.1,
330+
}),
331+
);
332+
insertCurrent.run(
333+
"msg_shared",
334+
"ses_shared",
335+
"assistant",
336+
4000,
337+
JSON.stringify({
338+
model: { providerID: "current-provider", id: "current-model" },
339+
tokens: { input: 3, output: 2, reasoning: 1, cache: { read: 0, write: 0 } },
340+
cost: 2,
341+
}),
342+
);
343+
insertCurrent.run(
344+
"msg_current",
345+
"ses_current",
346+
"assistant",
347+
4002,
348+
JSON.stringify({
349+
model: { providerID: "anthropic", id: "claude-sonnet-4-5" },
350+
tokens: { input: 4, output: 2, reasoning: 0, cache: { read: 0, write: 0 } },
351+
cost: 0.2,
352+
}),
353+
);
354+
database.close();
355+
database = undefined;
356+
357+
const result = await readOpenCodeUsage(databasePath, 3500);
358+
359+
expect(result?.malformedRecords).toBe(0);
360+
expect(result?.records).toHaveLength(3);
361+
expect(result?.records.map((record) => record.model).sort()).toEqual([
362+
"anthropic/claude-sonnet-4-5",
363+
"current-provider/current-model",
364+
"openai/gpt-5",
365+
]);
366+
expect(
367+
result?.records.find((record) => record.dedupeKey === "opencode:msg_shared"),
368+
).toMatchObject({
369+
model: "current-provider/current-model",
370+
reportedCostUsd: 2,
371+
});
372+
} finally {
373+
database?.close();
374+
await NodeFSP.rm(directory, { recursive: true, force: true });
375+
}
376+
});
217377
});

‎apps/server/src/usage/usageOpenCode.ts‎

Lines changed: 91 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2,8 +2,10 @@
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";
1618
const OPEN_CODE_STABLE_DATABASE = "opencode.db";
1719
const OPEN_CODE_CHANNEL_DATABASE = /^opencode-[a-zA-Z0-9._-]+\.db$/;
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+
4492
interface 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+
62114
interface SqliteStatement {
63-
readonly all: (sinceMs: number) => readonly unknown[];
115+
readonly all: (...parameters: readonly number[]) => readonly unknown[];
64116
}
65117

66118
interface 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 {

‎packages/contracts/src/usage.ts‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ import { NonNegativeInt, TrimmedNonEmptyString } from "./baseSchemas.ts";
2121
* client renders partial coverage when an environment reports an older version
2222
* rather than failing the whole page.
2323
*/
24-
export const USAGE_CONTRACT_VERSION = 4 as const;
24+
export const USAGE_CONTRACT_VERSION = 5 as const;
2525

2626
export const UsageProviderKind = Schema.Literals(["claude", "codex", "opencode"]);
2727
export type UsageProviderKind = typeof UsageProviderKind.Type;

0 commit comments

Comments
 (0)