OpenCode 2 (session_message schema) not detected - local database unreadable + missing auth.json

[gvilelaa]

Description

OpenUsage doesn't detect OpenCode after upgrading to OpenCode 2 (current stable 1.18.18 / 0.0.0-beta-17639 Go binary at ~/.opencode/bin/opencode). Menu bar shows Couldn't read OpenCode's local database and /v1/limits returns errors: [{providerId: "opencode", message: "Couldn't read OpenCode's local database..."}]. After the DB workaround the Go meters reappear but auth.json-based detection also fails.

Steps to Reproduce

1. Install OpenCode 2 (new Go/TUI) via npm i -g @opencode-ai/cli or curl -fsSL https://opencode.ai/install | bash -> binary ~/.opencode/bin/opencode version 1.18.18, data dir ~/.local/share/opencode/opencode.db
2. Run a few sessions (opencode run "hi"), confirm ~/.local/share/opencode/opencode.db has table session_message (not message) with rows where json_extract(data,'$.model.providerID')='opencode-go'
3. Open OpenUsage 0.7.10-beta.1 -> OpenCode card shows error. curl http://127.0.0.1:6736/v1/limits and openusage --force both show the error. curl http://127.0.0.1:6736/v1/usage shows no OpenCode spend tiles.
4. Check DB: sqlite3 ~/.local/share/opencode/opencode.db "SELECT name FROM sqlite_master WHERE type='table';" -> shows session_message etc, no message. Check auth: ls ~/.local/share/opencode/auth.json -> missing (key is now in credential table).

Expected Behavior

OpenUsage detects OpenCode 2 local usage and shows:

• Go meters (Session/Weekly/Monthly from https://opencode.ai/zen/go/v1/usage)
• Spend tiles Today / Last 30 Days + Usage Trend from local SQLite (combined Go+Zen), same as before the upgrade.

Actual Behavior

• hasLocalCredentials() returns false via hasHostedUsage() probe because SELECT 1 FROM message ... fails with no such table: message
• scan() throws databaseUnreadable for same reason
• goAPIKey() returns nil because ~/.local/share/opencode/auth.json doesn't exist anymore; key is now in SQLite credential (SELECT json_extract(value,'$.key') FROM credential WHERE ... LIKE 'sk-%')

OpenUsage Version

0.7.10-beta.1 (523)

macOS Version

15.5+ (Sequoia, Apple Silicon)

Root Cause

Sources/OpenUsage/Providers/OpenCode/OpenCodeUsageScanner.swift:42 and Sources/OpenUsage/Providers/OpenCode/OpenCodePaths.swift:28 still target the OpenCode 1 schema:

• Table message -> renamed to session_message in OpenCode 2 (see ~/.local/share/opencode/opencode.db schema: session_message(id, session_id, type, seq, time_created, time_updated, data))
• JSON shape changed:
  • $.role -> column type (assistant/user)
  • $.providerID -> $.model.providerID
  • $.modelID -> $.model.id
  • $.tokens.total -> COALESCE($.tokens.input,0)+COALESCE($.tokens.output,0)+COALESCE($.tokens.reasoning,0)+COALESCE($.tokens.cache.read,0)+COALESCE($.tokens.cache.write,0)
  • $.cost stays at top level but is now NULL for user/error rows (filtered by json_type IN ('integer','real'))
• Auth moved from ~/.local/share/opencode/auth.json ({"opencode-go":{"key":"sk-..."}}) to SQLite credential table. OpenCodeAuthStore.swift:40 only reads the file.

Strings extracted from binary confirm:

SELECT 1 FROM message WHERE json_valid(data) AND json_extract(data,'$.role')='assistant' AND json_extract(data,'$.providerID') IN ('opencode-go','opencode') ...
SELECT json_group_array(json_array(time_created, json_extract(data,'$.cost'), COALESCE(json_extract(data,'$.tokens.total'),0), ...)) FROM message ...

Proposed Fix

1. Scanner: probe for both tables, or create a compatibility VIEW. Suggested dataSQL/probeSQL that union both schemas or try session_message first:

-- fallback that handles both v1 and v2
SELECT ... FROM (SELECT * FROM message UNION ALL SELECT * FROM session_message) -- if both exist

Or, detection: if session_message exists, query it with transformed columns:

CREATE VIEW IF NOT EXISTS message AS
SELECT time_created,
  json_patch(json_patch(data, json_object('role', type)),
    json_object('providerID', json_extract(data,'$.model.providerID'),
                'modelID', json_extract(data,'$.model.id'),
                'cost', json_extract(data,'$.cost'),
                'tokens', json_object('total',
                  COALESCE(json_extract(data,'$.tokens.input'),0)+
                  COALESCE(json_extract(data,'$.tokens.output'),0)+
                  COALESCE(json_extract(data,'$.tokens.reasoning'),0)+
                  COALESCE(json_extract(data,'$.tokens.cache.read'),0)+
                  COALESCE(json_extract(data,'$.tokens.cache.write'),0))))
FROM session_message;

Better to make OpenCodeUsageScanner natively query session_message with the transformations, and keep message as fallback for old DBs. Also handle both $.tokens.total (v1) vs sum (v2) via COALESCE(json_extract(data,'$.tokens.total'), sum(...)).

2. Auth: make OpenCodeAuthStore.goAPIKey() try file first, then fallback to SQLite:

// if auth.json missing, open ~/.local/share/opencode/opencode*.db and query credential table
SELECT json_extract(value,'$.key') FROM credential WHERE json_extract(value,'$.key') LIKE 'sk-%' LIMIT 1

Respect OPENCODE_DATA_DIR/XDG_DATA_HOME via OpenCodePaths.dataDirectory.

3. Tests: extend OpenCodeUsageScannerTests to cover v2 row shape.

Workaround (verified locally)

Created VIEW + synced auth.json:

KEY=$(/usr/bin/sqlite3 ~/.local/share/opencode/opencode.db "SELECT json_extract(value,'\$.key') FROM credential WHERE json_extract(value,'\$.key') LIKE 'sk-%' LIMIT 1;")
echo "{\"opencode-go\":{\"key\":\"$KEY\"}}" > ~/.local/share/opencode/auth.json; chmod 600 ~/.local/share/opencode/auth.json
/usr/bin/sqlite3 ~/.local/share/opencode/opencode.db "DROP VIEW IF EXISTS message; CREATE VIEW message AS SELECT time_created, json_patch(... ) AS data FROM session_message;"

After that openusage --force shows errors: [], opencode with Today $0.20 · 21.4M and Trend, and /v1/usage lines are correct. LaunchAgent with WatchPaths on ~/.local/share/opencode keeps it persistent.

Happy to open a PR if you want - I have the SQL ready and tested against /usr/bin/sqlite3 3.51.0 (system) and ~/.local/share/opencode/opencode.db with 271 hosted rows.

Additional Context

• /usr/bin/sqlite3 supports json_patch on macOS 15 (tested)
• Data dir resolution via OPENCODE_DATA_DIR should stay, just add DB fallback
• No other providers affected

[arpankanwer]

Hi @robinebers — I'd like to work on this. I’ve reproduced the failure on OpenCode 2 beta (1.18.25, session_message + credential table) — the current scanner shows "Couldn't read OpenCode's local database" because it only hits message / auth.json.

I have a fix ready (draft PR #1186) that:

• unions message + session_message with coalesced JSON paths (COALESCE($.model.providerID,$.providerID), COALESCE($.model.id,$.modelID), COALESCE($.tokens.total, sum input/output/reasoning/cache)) so either schema or both during migration works
• falls back from auth.json to the SQLite credential table (integration_id='opencode-go' then generic sk-%) across all opencode*.db files

Verified: swift build + swift test --filter OpenCode (40 passed) + full suite (1225 passed) + manual sqlite3 ~/.local/share/opencode/opencode.db (46,716 hosted rows via union vs 24,893 v1-only).

Could you assign #1124 to me (@arpankanwer) so I can reopen #1186 as ready for review? Thanks!

[robinebers]

@arpankanwer done, thank you

[arpankanwer]

@robinebers Thanks again for assigning #1124!

Just a heads-up — PR #1186 is still closed from the auto-check just before assignment. Latest policy run is now passing, so it should stay open if reopened.

Could you please reopen #1186 when you have a moment? Happy to mark it Ready right after.

[arpankanwer]

@robinebers New PR for this issue: #1243 (draft).

The earlier #1186 couldn't be reopened — GitHub refuses while its head branch has been force-pushed after close — so this is a clean replacement branch with the same change, rebased onto current main. It unions message + session_message with coalesced token/model/provider paths, and falls back from auth.json to the credential table for the Go key.

Verified against a real migrated OpenCode 2 database: union sees 46,716 hosted rows where a v1-only read sees 24,893, and swift test is 1313 passing. Happy to adjust scope — it currently leaves OpenCodeCodexUsageScanner (added in #1195) untouched, which has the same v2 blind spot, if you'd rather that came along or landed separately.

[robinebers]

Fixed in #1323 and released in v0.7.13. OpenCode 2 usage and Go credentials are now read from the new tables. Thanks for the report and the work on this!

[Nemesis19]

Good morning, this broke the counters for those (like me) still on Opencode v1. Reverting to 0.7.12 fixed the issue. Thanks!