cmd_usage/ocuse/
db.rs

1// opencode's own message store, read-only.
2//
3// Every assistant row opencode writes carries a provider, model, `cost` and
4// `tokens`, plus a timestamp — a complete, backfilled per-request record for
5// everything the agent ran on this machine. Unlike CommandCode (subscription,
6// cost 0), opencode-go rows carry real cost, so this is also the spend figure.
7//
8// Two layouts: v1 `message` keeps them flat (`role` / `modelID` / `providerID`),
9// v2 `session_message` puts the role in the row's `type` and nests the model
10// (`model.id` / `model.providerID`). v2 stopped appending to `message` at the
11// migration, so reading only the legacy table reports zero for every recent
12// session; `read_rows` picks by table and keeps `message` as the fallback.
13//
14// Read-only by construction: `OpenFlags::SQLITE_OPEN_READ_ONLY`, so a running
15// opencode is unaffected.
16use rusqlite::{Connection, OpenFlags};
17use serde::Deserialize;
18
19#[derive(Debug, Clone, Default)]
20pub struct Row {
21    pub at_ms: i64,
22    pub session: String,
23    pub provider: String,
24    pub model: String,
25    pub cost_usd: f64,
26    pub input: u64,
27    pub output: u64,
28    pub reasoning: u64,
29    pub cache_read: u64,
30    pub cache_write: u64,
31}
32
33#[derive(Deserialize)]
34struct AssistantData {
35    role: Option<String>,
36    #[serde(rename = "providerID")]
37    provider: Option<String>,
38    #[serde(rename = "modelID")]
39    model: Option<String>,
40    cost: Option<f64>,
41    tokens: Option<Tokens>,
42}
43
44/// v2 keeps the role in `session_message.type` (the row selector, so it is not
45/// in the payload) and nests the model ref: `model.id` / `model.providerID`.
46#[derive(Deserialize, Default)]
47struct V2AssistantData {
48    model: Option<V2Model>,
49    cost: Option<f64>,
50    tokens: Option<Tokens>,
51}
52
53#[derive(Deserialize, Default)]
54struct V2Model {
55    id: Option<String>,
56    #[serde(rename = "providerID")]
57    provider: Option<String>,
58}
59
60#[derive(Deserialize)]
61struct Tokens {
62    input: Option<u64>,
63    output: Option<u64>,
64    /// Billed at the output rate by opencode itself, so pricing must count it.
65    reasoning: Option<u64>,
66    cache: Option<Cache>,
67}
68
69#[derive(Deserialize)]
70struct Cache {
71    read: Option<u64>,
72    write: Option<u64>,
73}
74
75/// Providers that belong to this tool. `opencode-go` is the Go subscription,
76/// `opencode` the Zen pay-as-you-go gateway.
77pub const PROVIDERS: [&str; 2] = ["opencode-go", "opencode"];
78
79/// opencode's own directory: `$XDG_DATA_HOME/opencode`, else
80/// `~/.local/share/opencode`. Holds the store — which carries the Go credential
81/// in v2's `credential` table — and `auth.json`, the legacy v1 file.
82pub fn opencode_home() -> String {
83    let base = std::env::var("XDG_DATA_HOME").unwrap_or_else(|_| {
84        let home = std::env::var("HOME").unwrap_or_default();
85        format!("{home}/.local/share")
86    });
87    format!("{base}/opencode")
88}
89
90/// Default store location: `$OPENCODE_DB`, else the XDG data dir.
91pub fn default_path() -> String {
92    if let Ok(path) = std::env::var("OPENCODE_DB") {
93        return path;
94    }
95    format!("{}/opencode.db", opencode_home())
96}
97
98/// The Go credential opencode itself is using.
99///
100/// v2 keeps provider credentials in the store's `credential` table; `auth.json`
101/// is the legacy v1 file. Reading the file alone produced a dashboard that said
102/// `api_key_expired` while Go inference worked: `/connect` had written the
103/// replacement key to the store, and the file still held the superseded one.
104/// `opencode-go` is the Go subscription and `opencode` is Zen — the same
105/// account, and the endpoint answers an entitlement refusal for a Zen key
106/// rather than silence, which is more useful than no key at all.
107pub fn credential_key() -> Option<String> {
108    credential_key_at(&default_path())
109}
110
111fn credential_key_at(path: &str) -> Option<String> {
112    let connection = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY).ok()?;
113    let mut statement = connection
114        .prepare(
115            // The boolean ordering puts `opencode-go` ahead of `opencode`, then
116            // newest first: a re-connected key supersedes the row it replaced.
117            "SELECT value FROM credential WHERE integration_id IN ('opencode-go', 'opencode') \
118             ORDER BY (integration_id = 'opencode-go') DESC, time_updated DESC",
119        )
120        .ok()?;
121    let values: Vec<String> = statement
122        .query_map([], |row| row.get::<_, String>(0))
123        .ok()?
124        .flatten()
125        .collect();
126    drop(statement);
127    values
128        .iter()
129        .filter_map(|value| serde_json::from_str::<Credential>(value).ok())
130        .filter_map(|credential| credential.key)
131        .find(|key| !key.is_empty())
132}
133
134/// `credential.value` is the connector's own JSON — `{"type":"key","key":"…"}`
135/// for a pasted API key. Only the key is read; the rest is its business.
136#[derive(Deserialize)]
137struct Credential {
138    key: Option<String>,
139}
140
141/// Every OpenCode-owned assistant row at or after `since_ms`, oldest first.
142///
143/// Reads v2's `session_message` when the store has it, and the legacy `message`
144/// table otherwise, so an upgraded opencode does not read as "no usage" — v2
145/// stopped appending to `message` at the migration, and every report was zero
146/// until this branch existed. `time_created >= ?` keeps either scan bounded;
147/// rows are filtered by provider in Rust because the JSON payload is not
148/// indexed.
149pub fn read_rows(path: &str, since_ms: i64) -> Result<Vec<Row>, String> {
150    let connection = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY)
151        .map_err(|e| format!("open {path}: {e}"))?;
152    if has_v2(&connection) {
153        read_v2(&connection, since_ms)
154    } else {
155        read_legacy(&connection, since_ms)
156    }
157}
158
159/// Does this store use opencode v2's `session_message` table?
160fn has_v2(connection: &Connection) -> bool {
161    connection
162        .query_row(
163            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'session_message'",
164            [],
165            |_| Ok(()),
166        )
167        .is_ok()
168}
169
170/// v2 rows: the row's `type` names it (no `$.role`), the model ref is nested,
171/// and only a completed turn carries `tokens` — an in-flight one is skipped
172/// rather than counted as a zero-token request.
173fn read_v2(connection: &Connection, since_ms: i64) -> Result<Vec<Row>, String> {
174    let mut statement = connection
175        .prepare(
176            "SELECT data, time_created, session_id FROM session_message
177             WHERE type = 'assistant' AND time_created >= ?",
178        )
179        .map_err(|e| format!("prepare: {e}"))?;
180    let mut rows = statement
181        .query([since_ms])
182        .map_err(|e| format!("query: {e}"))?;
183
184    let mut out = Vec::new();
185    while let Some(row) = rows.next().map_err(|e| format!("row: {e}"))? {
186        let data: String = row.get(0).map_err(|e| format!("data: {e}"))?;
187        let at_ms: i64 = row.get(1).map_err(|e| format!("time: {e}"))?;
188        let session: String = row.get(2).unwrap_or_default();
189        let Ok(parsed) = serde_json::from_str::<V2AssistantData>(&data) else {
190            continue;
191        };
192        let Some(tokens) = parsed.tokens else {
193            continue; // still streaming: not billed yet
194        };
195        let model = parsed.model.unwrap_or_default();
196        let provider = model.provider.unwrap_or_default();
197        if !PROVIDERS.contains(&provider.as_str()) {
198            continue;
199        }
200        out.push(Row {
201            at_ms,
202            session,
203            provider,
204            model: model.id.unwrap_or_default(),
205            cost_usd: parsed.cost.unwrap_or(0.0),
206            input: tokens.input.unwrap_or(0),
207            output: tokens.output.unwrap_or(0),
208            reasoning: tokens.reasoning.unwrap_or(0),
209            cache_read: tokens.cache.as_ref().and_then(|c| c.read).unwrap_or(0),
210            cache_write: tokens.cache.and_then(|c| c.write).unwrap_or(0),
211        });
212    }
213    Ok(out)
214}
215
216/// Pre-v2 rows: `role` in the payload, flat `modelID` / `providerID`.
217fn read_legacy(connection: &Connection, since_ms: i64) -> Result<Vec<Row>, String> {
218    let mut statement = connection
219        .prepare(
220            "SELECT data, time_created, session_id FROM message
221             WHERE data LIKE '%\"tokens\"%' AND time_created >= ?",
222        )
223        .map_err(|e| format!("prepare: {e}"))?;
224    let mut rows = statement
225        .query([since_ms])
226        .map_err(|e| format!("query: {e}"))?;
227
228    let mut out = Vec::new();
229    while let Some(row) = rows.next().map_err(|e| format!("row: {e}"))? {
230        let data: String = row.get(0).map_err(|e| format!("data: {e}"))?;
231        let at_ms: i64 = row.get(1).map_err(|e| format!("time: {e}"))?;
232        let session: String = row.get(2).unwrap_or_default();
233        let Ok(parsed) = serde_json::from_str::<AssistantData>(&data) else {
234            continue;
235        };
236        if parsed.role.as_deref() != Some("assistant") {
237            continue;
238        }
239        let provider = parsed.provider.unwrap_or_default();
240        if !PROVIDERS.contains(&provider.as_str()) {
241            continue;
242        }
243        let tokens = parsed.tokens.unwrap_or(Tokens {
244            input: None,
245            output: None,
246            reasoning: None,
247            cache: None,
248        });
249        out.push(Row {
250            at_ms,
251            session,
252            provider,
253            model: parsed.model.unwrap_or_default(),
254            cost_usd: parsed.cost.unwrap_or(0.0),
255            input: tokens.input.unwrap_or(0),
256            output: tokens.output.unwrap_or(0),
257            reasoning: tokens.reasoning.unwrap_or(0),
258            cache_read: tokens.cache.as_ref().and_then(|c| c.read).unwrap_or(0),
259            cache_write: tokens.cache.and_then(|c| c.write).unwrap_or(0),
260        });
261    }
262    Ok(out)
263}
264
265#[cfg(test)]
266mod tests {
267    use super::*;
268
269    /// A miniature store, shaped like the real one.
270    fn fixture() -> (tempfile::TempDir, String) {
271        let dir = tempfile::tempdir().unwrap();
272        let path = dir.path().join("opencode.db");
273        let connection = Connection::open(&path).unwrap();
274        connection
275            .execute_batch(
276                "CREATE TABLE message (session_id TEXT, data TEXT, time_created INTEGER);",
277            )
278            .unwrap();
279        let insert = |data: &str, at: i64| {
280            connection
281                .execute(
282                    "INSERT INTO message VALUES ('ses_fixture', ?, ?)",
283                    (data, at),
284                )
285                .unwrap();
286        };
287        insert(
288            r#"{"role":"assistant","providerID":"opencode-go","modelID":"glm-5.3-flash","cost":0.5,"tokens":{"input":10,"output":2,"cache":{"read":7,"write":0}}}"#,
289            1_000,
290        );
291        insert(
292            r#"{"role":"assistant","providerID":"opencode","modelID":"gpt-5.5","cost":1.25,"tokens":{"input":1,"output":1}}"#,
293            2_000,
294        );
295        insert(
296            r#"{"role":"user","providerID":"opencode-go","tokens":{"input":1}}"#,
297            3_000,
298        );
299        insert(
300            r#"{"role":"assistant","providerID":"command-code-openai","modelID":"deepseek-v4.1-flash","cost":9.0,"tokens":{"input":1}}"#,
301            4_000,
302        );
303        (dir, path.to_string_lossy().into_owned())
304    }
305
306    #[test]
307    fn reads_only_opencode_assistant_rows_in_window() {
308        let (_dir, path) = fixture();
309        let rows = read_rows(&path, 0).unwrap();
310        assert_eq!(rows.len(), 2, "{rows:#?}");
311        assert_eq!(rows[0].provider, "opencode-go");
312        assert_eq!(rows[0].cache_read, 7);
313        assert!((rows[0].cost_usd - 0.5).abs() < f64::EPSILON);
314        assert_eq!(rows[1].model, "gpt-5.5");
315
316        let later = read_rows(&path, 1_500).unwrap();
317        assert_eq!(later.len(), 1);
318        assert_eq!(later[0].provider, "opencode");
319    }
320
321    /// v2 holds the live credential; the legacy file is only where a v1 install
322    /// kept one — and reading that alone is what reported `api_key_expired` while
323    /// Go inference worked.
324    #[test]
325    fn the_live_credential_comes_from_the_store() {
326        let dir = tempfile::tempdir().unwrap();
327        let path = dir.path().join("opencode.db");
328        let connection = Connection::open(&path).unwrap();
329        connection
330            .execute_batch(
331                "CREATE TABLE credential (\
332                 id TEXT, integration_id TEXT, value TEXT, time_updated INTEGER);",
333            )
334            .unwrap();
335        let insert = |integration: &str, key: &str, at: i64| {
336            connection
337                .execute(
338                    "INSERT INTO credential VALUES (?, ?, ?, ?)",
339                    (
340                        format!("cred_{at}"),
341                        integration,
342                        format!(r#"{{"type":"key","key":"{key}"}}"#),
343                        at,
344                    ),
345                )
346                .unwrap();
347        };
348        insert("opencode", "zen", 9_000);
349        insert("opencode-go", "go-old", 1_000);
350        insert("opencode-go", "go-new", 2_000);
351        let path = path.to_string_lossy().into_owned();
352        // Go beats Zen and the newest Go row beats the one it replaced, whatever
353        // the timestamps say (Zen's is the newest here).
354        assert_eq!(credential_key_at(&path).as_deref(), Some("go-new"));
355
356        // An empty key is not a key: nothing to send.
357        connection
358            .execute(
359                "INSERT INTO credential VALUES ('cred_empty', 'opencode-go', '{\"type\":\"key\",\"key\":\"\"}', 3_000)",
360                [],
361            )
362            .unwrap();
363        assert_eq!(credential_key_at(&path).as_deref(), Some("go-new"));
364
365        // A store with no such table (v1) reads as "ask the legacy file".
366        let legacy = dir.path().join("legacy.db");
367        Connection::open(&legacy).unwrap();
368        assert!(credential_key_at(&legacy.to_string_lossy()).is_none());
369    }
370
371    /// A v2 store: `session_message`, role in `type`, model ref nested.
372    fn v2_fixture() -> (tempfile::TempDir, String) {
373        let dir = tempfile::tempdir().unwrap();
374        let path = dir.path().join("opencode.db");
375        let connection = Connection::open(&path).unwrap();
376        connection
377            .execute_batch(
378                "CREATE TABLE session_message (\
379                 id TEXT, session_id TEXT, type TEXT, seq INTEGER, \
380                 time_created INTEGER, time_updated INTEGER, data TEXT);",
381            )
382            .unwrap();
383        let insert = |kind: &str, data: &str, at: i64| {
384            connection
385                .execute(
386                    "INSERT INTO session_message \
387                     (id, session_id, type, seq, time_created, time_updated, data) \
388                     VALUES (?, 'ses_v2', ?, 0, ?, ?, ?)",
389                    (format!("msg_{at}"), kind, at, at, data),
390                )
391                .unwrap();
392        };
393        insert(
394            "assistant",
395            r#"{"model":{"id":"glm-5.3-flash","providerID":"opencode-go"},"cost":0.5,"tokens":{"input":10,"output":2,"cache":{"read":7,"write":0}}}"#,
396            1_000,
397        );
398        insert(
399            "assistant",
400            r#"{"model":{"id":"gpt-5.5","providerID":"opencode"},"cost":1.25,"tokens":{"input":1,"output":1}}"#,
401            2_000,
402        );
403        // In-flight: a model ref but no tokens yet.
404        insert(
405            "assistant",
406            r#"{"model":{"id":"gpt-5.5","providerID":"opencode"}}"#,
407            3_000,
408        );
409        // The role lives in `type`, so this user row must not count.
410        insert("user", r#"{"text":"hi"}"#, 4_000);
411        insert(
412            "assistant",
413            r#"{"model":{"id":"deepseek","providerID":"command-code-openai"},"cost":9.0,"tokens":{"input":1}}"#,
414            5_000,
415        );
416        (dir, path.to_string_lossy().into_owned())
417    }
418
419    #[test]
420    fn reads_v2_rows_skipping_inflight_and_foreign_providers() {
421        let (_dir, path) = v2_fixture();
422        let rows = read_rows(&path, 0).unwrap();
423        assert_eq!(rows.len(), 2, "{rows:#?}");
424        assert_eq!(rows[0].provider, "opencode-go");
425        assert_eq!(rows[0].model, "glm-5.3-flash");
426        assert_eq!(rows[0].cache_read, 7);
427        assert!((rows[0].cost_usd - 0.5).abs() < f64::EPSILON);
428        assert_eq!(rows[1].model, "gpt-5.5");
429
430        let later = read_rows(&path, 1_500).unwrap();
431        assert_eq!(later.len(), 1);
432        assert_eq!(later[0].provider, "opencode");
433    }
434
435    #[test]
436    fn missing_database_is_an_error_not_a_panic() {
437        let error = read_rows("/nonexistent/opencode.db", 0).unwrap_err();
438        assert!(error.contains("open"), "{error}");
439    }
440}
cmd_usage/ocuse/db.rs

cmd_usage/ocuse/
db.rs
