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 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 }
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}