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 cache_read: u64,
29    pub cache_write: u64,
30}
31
32#[derive(Deserialize)]
33struct AssistantData {
34    role: Option<String>,
35    #[serde(rename = "providerID")]
36    provider: Option<String>,
37    #[serde(rename = "modelID")]
38    model: Option<String>,
39    cost: Option<f64>,
40    tokens: Option<Tokens>,
41}
42
43/// v2 keeps the role in `session_message.type` (the row selector, so it is not
44/// in the payload) and nests the model ref: `model.id` / `model.providerID`.
45#[derive(Deserialize, Default)]
46struct V2AssistantData {
47    model: Option<V2Model>,
48    cost: Option<f64>,
49    tokens: Option<Tokens>,
50}
51
52#[derive(Deserialize, Default)]
53struct V2Model {
54    id: Option<String>,
55    #[serde(rename = "providerID")]
56    provider: Option<String>,
57}
58
59#[derive(Deserialize)]
60struct Tokens {
61    input: Option<u64>,
62    output: Option<u64>,
63    cache: Option<Cache>,
64}
65
66#[derive(Deserialize)]
67struct Cache {
68    read: Option<u64>,
69    write: Option<u64>,
70}
71
72/// Providers that belong to this tool. `opencode-go` is the Go subscription,
73/// `opencode` the Zen pay-as-you-go gateway.
74pub const PROVIDERS: [&str; 2] = ["opencode-go", "opencode"];
75
76/// opencode's own directory: `$XDG_DATA_HOME/opencode`, else
77/// `~/.local/share/opencode`. Holds the store and `auth.json` (the Go key).
78pub fn opencode_home() -> String {
79    let base = std::env::var("XDG_DATA_HOME").unwrap_or_else(|_| {
80        let home = std::env::var("HOME").unwrap_or_default();
81        format!("{home}/.local/share")
82    });
83    format!("{base}/opencode")
84}
85
86/// Default store location: `$OPENCODE_DB`, else the XDG data dir.
87pub fn default_path() -> String {
88    if let Ok(path) = std::env::var("OPENCODE_DB") {
89        return path;
90    }
91    format!("{}/opencode.db", opencode_home())
92}
93
94/// Every OpenCode-owned assistant row at or after `since_ms`, oldest first.
95///
96/// Reads v2's `session_message` when the store has it, and the legacy `message`
97/// table otherwise, so an upgraded opencode does not read as "no usage" — v2
98/// stopped appending to `message` at the migration, and every report was zero
99/// until this branch existed. `time_created >= ?` keeps either scan bounded;
100/// rows are filtered by provider in Rust because the JSON payload is not
101/// indexed.
102pub fn read_rows(path: &str, since_ms: i64) -> Result<Vec<Row>, String> {
103    let connection = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY)
104        .map_err(|e| format!("open {path}: {e}"))?;
105    if has_v2(&connection) {
106        read_v2(&connection, since_ms)
107    } else {
108        read_legacy(&connection, since_ms)
109    }
110}
111
112/// Does this store use opencode v2's `session_message` table?
113fn has_v2(connection: &Connection) -> bool {
114    connection
115        .query_row(
116            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'session_message'",
117            [],
118            |_| Ok(()),
119        )
120        .is_ok()
121}
122
123/// v2 rows: the row's `type` names it (no `$.role`), the model ref is nested,
124/// and only a completed turn carries `tokens` — an in-flight one is skipped
125/// rather than counted as a zero-token request.
126fn read_v2(connection: &Connection, since_ms: i64) -> Result<Vec<Row>, String> {
127    let mut statement = connection
128        .prepare(
129            "SELECT data, time_created, session_id FROM session_message
130             WHERE type = 'assistant' AND time_created >= ?",
131        )
132        .map_err(|e| format!("prepare: {e}"))?;
133    let mut rows = statement
134        .query([since_ms])
135        .map_err(|e| format!("query: {e}"))?;
136
137    let mut out = Vec::new();
138    while let Some(row) = rows.next().map_err(|e| format!("row: {e}"))? {
139        let data: String = row.get(0).map_err(|e| format!("data: {e}"))?;
140        let at_ms: i64 = row.get(1).map_err(|e| format!("time: {e}"))?;
141        let session: String = row.get(2).unwrap_or_default();
142        let Ok(parsed) = serde_json::from_str::<V2AssistantData>(&data) else {
143            continue;
144        };
145        let Some(tokens) = parsed.tokens else {
146            continue; // still streaming: not billed yet
147        };
148        let model = parsed.model.unwrap_or_default();
149        let provider = model.provider.unwrap_or_default();
150        if !PROVIDERS.contains(&provider.as_str()) {
151            continue;
152        }
153        out.push(Row {
154            at_ms,
155            session,
156            provider,
157            model: model.id.unwrap_or_default(),
158            cost_usd: parsed.cost.unwrap_or(0.0),
159            input: tokens.input.unwrap_or(0),
160            output: tokens.output.unwrap_or(0),
161            cache_read: tokens.cache.as_ref().and_then(|c| c.read).unwrap_or(0),
162            cache_write: tokens.cache.and_then(|c| c.write).unwrap_or(0),
163        });
164    }
165    Ok(out)
166}
167
168/// Pre-v2 rows: `role` in the payload, flat `modelID` / `providerID`.
169fn read_legacy(connection: &Connection, since_ms: i64) -> Result<Vec<Row>, String> {
170    let mut statement = connection
171        .prepare(
172            "SELECT data, time_created, session_id FROM message
173             WHERE data LIKE '%\"tokens\"%' AND time_created >= ?",
174        )
175        .map_err(|e| format!("prepare: {e}"))?;
176    let mut rows = statement
177        .query([since_ms])
178        .map_err(|e| format!("query: {e}"))?;
179
180    let mut out = Vec::new();
181    while let Some(row) = rows.next().map_err(|e| format!("row: {e}"))? {
182        let data: String = row.get(0).map_err(|e| format!("data: {e}"))?;
183        let at_ms: i64 = row.get(1).map_err(|e| format!("time: {e}"))?;
184        let session: String = row.get(2).unwrap_or_default();
185        let Ok(parsed) = serde_json::from_str::<AssistantData>(&data) else {
186            continue;
187        };
188        if parsed.role.as_deref() != Some("assistant") {
189            continue;
190        }
191        let provider = parsed.provider.unwrap_or_default();
192        if !PROVIDERS.contains(&provider.as_str()) {
193            continue;
194        }
195        let tokens = parsed.tokens.unwrap_or(Tokens {
196            input: None,
197            output: None,
198            cache: None,
199        });
200        out.push(Row {
201            at_ms,
202            session,
203            provider,
204            model: parsed.model.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            cache_read: tokens.cache.as_ref().and_then(|c| c.read).unwrap_or(0),
209            cache_write: tokens.cache.and_then(|c| c.write).unwrap_or(0),
210        });
211    }
212    Ok(out)
213}
214
215#[cfg(test)]
216mod tests {
217    use super::*;
218
219    /// A miniature store, shaped like the real one.
220    fn fixture() -> (tempfile::TempDir, String) {
221        let dir = tempfile::tempdir().unwrap();
222        let path = dir.path().join("opencode.db");
223        let connection = Connection::open(&path).unwrap();
224        connection
225            .execute_batch(
226                "CREATE TABLE message (session_id TEXT, data TEXT, time_created INTEGER);",
227            )
228            .unwrap();
229        let insert = |data: &str, at: i64| {
230            connection
231                .execute(
232                    "INSERT INTO message VALUES ('ses_fixture', ?, ?)",
233                    (data, at),
234                )
235                .unwrap();
236        };
237        insert(
238            r#"{"role":"assistant","providerID":"opencode-go","modelID":"glm-5.3-flash","cost":0.5,"tokens":{"input":10,"output":2,"cache":{"read":7,"write":0}}}"#,
239            1_000,
240        );
241        insert(
242            r#"{"role":"assistant","providerID":"opencode","modelID":"gpt-5.5","cost":1.25,"tokens":{"input":1,"output":1}}"#,
243            2_000,
244        );
245        insert(
246            r#"{"role":"user","providerID":"opencode-go","tokens":{"input":1}}"#,
247            3_000,
248        );
249        insert(
250            r#"{"role":"assistant","providerID":"command-code-openai","modelID":"deepseek-v4.1-flash","cost":9.0,"tokens":{"input":1}}"#,
251            4_000,
252        );
253        (dir, path.to_string_lossy().into_owned())
254    }
255
256    #[test]
257    fn reads_only_opencode_assistant_rows_in_window() {
258        let (_dir, path) = fixture();
259        let rows = read_rows(&path, 0).unwrap();
260        assert_eq!(rows.len(), 2, "{rows:#?}");
261        assert_eq!(rows[0].provider, "opencode-go");
262        assert_eq!(rows[0].cache_read, 7);
263        assert!((rows[0].cost_usd - 0.5).abs() < f64::EPSILON);
264        assert_eq!(rows[1].model, "gpt-5.5");
265
266        let later = read_rows(&path, 1_500).unwrap();
267        assert_eq!(later.len(), 1);
268        assert_eq!(later[0].provider, "opencode");
269    }
270
271    /// A v2 store: `session_message`, role in `type`, model ref nested.
272    fn v2_fixture() -> (tempfile::TempDir, String) {
273        let dir = tempfile::tempdir().unwrap();
274        let path = dir.path().join("opencode.db");
275        let connection = Connection::open(&path).unwrap();
276        connection
277            .execute_batch(
278                "CREATE TABLE session_message (\
279                 id TEXT, session_id TEXT, type TEXT, seq INTEGER, \
280                 time_created INTEGER, time_updated INTEGER, data TEXT);",
281            )
282            .unwrap();
283        let insert = |kind: &str, data: &str, at: i64| {
284            connection
285                .execute(
286                    "INSERT INTO session_message \
287                     (id, session_id, type, seq, time_created, time_updated, data) \
288                     VALUES (?, 'ses_v2', ?, 0, ?, ?, ?)",
289                    (format!("msg_{at}"), kind, at, at, data),
290                )
291                .unwrap();
292        };
293        insert(
294            "assistant",
295            r#"{"model":{"id":"glm-5.3-flash","providerID":"opencode-go"},"cost":0.5,"tokens":{"input":10,"output":2,"cache":{"read":7,"write":0}}}"#,
296            1_000,
297        );
298        insert(
299            "assistant",
300            r#"{"model":{"id":"gpt-5.5","providerID":"opencode"},"cost":1.25,"tokens":{"input":1,"output":1}}"#,
301            2_000,
302        );
303        // In-flight: a model ref but no tokens yet.
304        insert(
305            "assistant",
306            r#"{"model":{"id":"gpt-5.5","providerID":"opencode"}}"#,
307            3_000,
308        );
309        // The role lives in `type`, so this user row must not count.
310        insert("user", r#"{"text":"hi"}"#, 4_000);
311        insert(
312            "assistant",
313            r#"{"model":{"id":"deepseek","providerID":"command-code-openai"},"cost":9.0,"tokens":{"input":1}}"#,
314            5_000,
315        );
316        (dir, path.to_string_lossy().into_owned())
317    }
318
319    #[test]
320    fn reads_v2_rows_skipping_inflight_and_foreign_providers() {
321        let (_dir, path) = v2_fixture();
322        let rows = read_rows(&path, 0).unwrap();
323        assert_eq!(rows.len(), 2, "{rows:#?}");
324        assert_eq!(rows[0].provider, "opencode-go");
325        assert_eq!(rows[0].model, "glm-5.3-flash");
326        assert_eq!(rows[0].cache_read, 7);
327        assert!((rows[0].cost_usd - 0.5).abs() < f64::EPSILON);
328        assert_eq!(rows[1].model, "gpt-5.5");
329
330        let later = read_rows(&path, 1_500).unwrap();
331        assert_eq!(later.len(), 1);
332        assert_eq!(later[0].provider, "opencode");
333    }
334
335    #[test]
336    fn missing_database_is_an_error_not_a_panic() {
337        let error = read_rows("/nonexistent/opencode.db", 0).unwrap_err();
338        assert!(error.contains("open"), "{error}");
339    }
340}
cmd_usage/ocuse/db.rs

cmd_usage/ocuse/
db.rs
