Skip to main content

microclaw_engine/internal/storage/db/
usage.rs

1use super::*;
2
3#[derive(Debug, Clone)]
4pub struct LlmUsageSummary {
5    pub requests: i64,
6    pub input_tokens: i64,
7    pub output_tokens: i64,
8    pub total_tokens: i64,
9    pub last_request_at: Option<String>,
10}
11
12#[derive(Debug, Clone)]
13pub struct LlmModelUsageSummary {
14    pub model: String,
15    pub requests: i64,
16    pub input_tokens: i64,
17    pub output_tokens: i64,
18    pub total_tokens: i64,
19}
20
21impl Database {
22    #[allow(clippy::too_many_arguments)]
23    pub fn log_llm_usage(
24        &self,
25        chat_id: i64,
26        caller_channel: &str,
27        provider: &str,
28        model: &str,
29        input_tokens: i64,
30        output_tokens: i64,
31        request_kind: &str,
32    ) -> Result<i64, MicroClawError> {
33        let conn = self.lock_conn();
34        let now = chrono::Utc::now().to_rfc3339();
35        let total_tokens = input_tokens.saturating_add(output_tokens);
36        conn.execute(
37            "INSERT INTO llm_usage_logs
38                (chat_id, caller_channel, provider, model, input_tokens, output_tokens, total_tokens, request_kind, created_at)
39             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
40            params![
41                chat_id,
42                caller_channel,
43                provider,
44                model,
45                input_tokens,
46                output_tokens,
47                total_tokens,
48                request_kind,
49                now,
50            ],
51        )?;
52        Ok(conn.last_insert_rowid())
53    }
54
55    /// Input tokens and model of the most recent main-loop request of a chat.
56    pub fn latest_agent_request(
57        &self,
58        chat_id: i64,
59    ) -> Result<Option<(i64, String)>, MicroClawError> {
60        let conn = self.lock_conn();
61        let row = conn
62            .query_row(
63                "SELECT input_tokens, model FROM llm_usage_logs
64                 WHERE chat_id = ?1 AND request_kind = 'agent_loop'
65                 ORDER BY id DESC LIMIT 1",
66                params![chat_id],
67                |row| Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?)),
68            )
69            .optional()?;
70        Ok(row)
71    }
72
73    pub fn get_llm_usage_summary(
74        &self,
75        chat_id: Option<i64>,
76    ) -> Result<LlmUsageSummary, MicroClawError> {
77        self.get_llm_usage_summary_since(chat_id, None)
78    }
79
80    pub fn get_llm_usage_summary_since(
81        &self,
82        chat_id: Option<i64>,
83        since: Option<&str>,
84    ) -> Result<LlmUsageSummary, MicroClawError> {
85        let conn = self.lock_conn();
86        let (requests, input_tokens, output_tokens, total_tokens, last_request_at) =
87            match (chat_id, since) {
88                (Some(id), Some(since_ts)) => conn.query_row(
89                    "SELECT
90                    COUNT(*),
91                    COALESCE(SUM(input_tokens), 0),
92                    COALESCE(SUM(output_tokens), 0),
93                    COALESCE(SUM(total_tokens), 0),
94                    MAX(created_at)
95                 FROM llm_usage_logs
96                 WHERE chat_id = ?1 AND created_at >= ?2",
97                    params![id, since_ts],
98                    |row| {
99                        Ok((
100                            row.get::<_, i64>(0)?,
101                            row.get::<_, i64>(1)?,
102                            row.get::<_, i64>(2)?,
103                            row.get::<_, i64>(3)?,
104                            row.get::<_, Option<String>>(4)?,
105                        ))
106                    },
107                )?,
108                (Some(id), None) => conn.query_row(
109                    "SELECT
110                    COUNT(*),
111                    COALESCE(SUM(input_tokens), 0),
112                    COALESCE(SUM(output_tokens), 0),
113                    COALESCE(SUM(total_tokens), 0),
114                    MAX(created_at)
115                 FROM llm_usage_logs
116                 WHERE chat_id = ?1",
117                    params![id],
118                    |row| {
119                        Ok((
120                            row.get::<_, i64>(0)?,
121                            row.get::<_, i64>(1)?,
122                            row.get::<_, i64>(2)?,
123                            row.get::<_, i64>(3)?,
124                            row.get::<_, Option<String>>(4)?,
125                        ))
126                    },
127                )?,
128                (None, Some(since_ts)) => conn.query_row(
129                    "SELECT
130                    COUNT(*),
131                    COALESCE(SUM(input_tokens), 0),
132                    COALESCE(SUM(output_tokens), 0),
133                    COALESCE(SUM(total_tokens), 0),
134                    MAX(created_at)
135                 FROM llm_usage_logs
136                 WHERE created_at >= ?1",
137                    params![since_ts],
138                    |row| {
139                        Ok((
140                            row.get::<_, i64>(0)?,
141                            row.get::<_, i64>(1)?,
142                            row.get::<_, i64>(2)?,
143                            row.get::<_, i64>(3)?,
144                            row.get::<_, Option<String>>(4)?,
145                        ))
146                    },
147                )?,
148                (None, None) => conn.query_row(
149                    "SELECT
150                    COUNT(*),
151                    COALESCE(SUM(input_tokens), 0),
152                    COALESCE(SUM(output_tokens), 0),
153                    COALESCE(SUM(total_tokens), 0),
154                    MAX(created_at)
155                 FROM llm_usage_logs",
156                    [],
157                    |row| {
158                        Ok((
159                            row.get::<_, i64>(0)?,
160                            row.get::<_, i64>(1)?,
161                            row.get::<_, i64>(2)?,
162                            row.get::<_, i64>(3)?,
163                            row.get::<_, Option<String>>(4)?,
164                        ))
165                    },
166                )?,
167            };
168
169        Ok(LlmUsageSummary {
170            requests,
171            input_tokens,
172            output_tokens,
173            total_tokens,
174            last_request_at,
175        })
176    }
177
178    pub fn get_llm_usage_by_model(
179        &self,
180        chat_id: Option<i64>,
181        since: Option<&str>,
182        limit: Option<usize>,
183    ) -> Result<Vec<LlmModelUsageSummary>, MicroClawError> {
184        let conn = self.lock_conn();
185        let mut query = String::from(
186            "SELECT
187                model,
188                COUNT(*) AS requests,
189                COALESCE(SUM(input_tokens), 0) AS input_tokens,
190                COALESCE(SUM(output_tokens), 0) AS output_tokens,
191                COALESCE(SUM(total_tokens), 0) AS total_tokens
192             FROM llm_usage_logs",
193        );
194
195        let mut has_where = false;
196        if chat_id.is_some() {
197            query.push_str(" WHERE chat_id = ?1");
198            has_where = true;
199        }
200        if since.is_some() {
201            if has_where {
202                if chat_id.is_some() {
203                    query.push_str(" AND created_at >= ?2");
204                } else {
205                    query.push_str(" AND created_at >= ?1");
206                }
207            } else {
208                query.push_str(" WHERE created_at >= ?1");
209            }
210        }
211        query.push_str(" GROUP BY model ORDER BY total_tokens DESC");
212        if limit.is_some() {
213            match (chat_id.is_some(), since.is_some()) {
214                (true, true) => query.push_str(" LIMIT ?3"),
215                (true, false) | (false, true) => query.push_str(" LIMIT ?2"),
216                (false, false) => query.push_str(" LIMIT ?1"),
217            }
218        }
219
220        let mut stmt = conn.prepare(&query)?;
221        let mapper = |row: &rusqlite::Row<'_>| {
222            Ok(LlmModelUsageSummary {
223                model: row.get(0)?,
224                requests: row.get(1)?,
225                input_tokens: row.get(2)?,
226                output_tokens: row.get(3)?,
227                total_tokens: row.get(4)?,
228            })
229        };
230
231        let rows = match (chat_id, since, limit) {
232            (Some(id), Some(since_ts), Some(limit_n)) => {
233                stmt.query_map(params![id, since_ts, limit_n as i64], mapper)?
234            }
235            (Some(id), Some(since_ts), None) => stmt.query_map(params![id, since_ts], mapper)?,
236            (Some(id), None, Some(limit_n)) => {
237                stmt.query_map(params![id, limit_n as i64], mapper)?
238            }
239            (Some(id), None, None) => stmt.query_map(params![id], mapper)?,
240            (None, Some(since_ts), Some(limit_n)) => {
241                stmt.query_map(params![since_ts, limit_n as i64], mapper)?
242            }
243            (None, Some(since_ts), None) => stmt.query_map(params![since_ts], mapper)?,
244            (None, None, Some(limit_n)) => stmt.query_map(params![limit_n as i64], mapper)?,
245            (None, None, None) => stmt.query_map([], mapper)?,
246        };
247        rows.collect::<Result<Vec<_>, _>>().map_err(Into::into)
248    }
249
250    // --- Memories ---
251}
252
253#[cfg(test)]
254mod tests {
255    use crate::internal::storage::db::test_support::*;
256
257    #[test]
258    fn test_log_llm_usage_and_summary() {
259        let (db, dir) = test_db();
260        db.log_llm_usage(
261            100,
262            "telegram",
263            "anthropic",
264            "claude-test",
265            10,
266            5,
267            "agent_loop",
268        )
269        .unwrap();
270        db.log_llm_usage(
271            100,
272            "telegram",
273            "anthropic",
274            "claude-test",
275            20,
276            8,
277            "agent_loop",
278        )
279        .unwrap();
280        db.log_llm_usage(200, "discord", "openai", "gpt-test", 30, 7, "agent_loop")
281            .unwrap();
282
283        let chat_100 = db.get_llm_usage_summary(Some(100)).unwrap();
284        assert_eq!(chat_100.requests, 2);
285        assert_eq!(chat_100.input_tokens, 30);
286        assert_eq!(chat_100.output_tokens, 13);
287        assert_eq!(chat_100.total_tokens, 43);
288        assert!(chat_100.last_request_at.is_some());
289
290        let all = db.get_llm_usage_summary(None).unwrap();
291        assert_eq!(all.requests, 3);
292        assert_eq!(all.input_tokens, 60);
293        assert_eq!(all.output_tokens, 20);
294        assert_eq!(all.total_tokens, 80);
295        assert!(all.last_request_at.is_some());
296
297        cleanup(&dir);
298    }
299
300    #[test]
301    fn test_delete_chat_data_cleans_llm_usage() {
302        let (db, dir) = test_db();
303        db.upsert_chat(100, Some("chat-100"), "private").unwrap();
304        db.log_llm_usage(
305            100,
306            "telegram",
307            "anthropic",
308            "claude-test",
309            11,
310            9,
311            "agent_loop",
312        )
313        .unwrap();
314        db.log_llm_usage(
315            200,
316            "telegram",
317            "anthropic",
318            "claude-test",
319            3,
320            4,
321            "agent_loop",
322        )
323        .unwrap();
324
325        assert!(db.delete_chat_data(100).unwrap());
326
327        let chat_100 = db.get_llm_usage_summary(Some(100)).unwrap();
328        assert_eq!(chat_100.requests, 0);
329        let chat_200 = db.get_llm_usage_summary(Some(200)).unwrap();
330        assert_eq!(chat_200.requests, 1);
331
332        cleanup(&dir);
333    }
334
335    #[test]
336    fn test_get_llm_usage_summary_since_and_by_model() {
337        let (db, dir) = test_db();
338        db.log_llm_usage(
339            100,
340            "telegram",
341            "anthropic",
342            "claude-a",
343            10,
344            5,
345            "agent_loop",
346        )
347        .unwrap();
348        db.log_llm_usage(
349            100,
350            "telegram",
351            "anthropic",
352            "claude-a",
353            20,
354            10,
355            "agent_loop",
356        )
357        .unwrap();
358        db.log_llm_usage(100, "telegram", "anthropic", "claude-b", 3, 7, "agent_loop")
359            .unwrap();
360
361        let all = db.get_llm_usage_summary_since(Some(100), None).unwrap();
362        assert_eq!(all.requests, 3);
363        assert_eq!(all.input_tokens, 33);
364        assert_eq!(all.output_tokens, 22);
365
366        let future = db
367            .get_llm_usage_summary_since(Some(100), Some("2100-01-01T00:00:00Z"))
368            .unwrap();
369        assert_eq!(future.requests, 0);
370
371        let by_model = db
372            .get_llm_usage_by_model(Some(100), None, Some(10))
373            .unwrap();
374        assert_eq!(by_model.len(), 2);
375        assert_eq!(by_model[0].model, "claude-a");
376        assert_eq!(by_model[0].requests, 2);
377        assert_eq!(by_model[0].total_tokens, 45);
378        assert_eq!(by_model[1].model, "claude-b");
379        assert_eq!(by_model[1].requests, 1);
380        assert_eq!(by_model[1].total_tokens, 10);
381
382        cleanup(&dir);
383    }
384}