Skip to main content

orch/store/
control.rs

1use super::{Context, TaskStore};
2use chrono::{Duration, Utc};
3use serde::{Deserialize, Serialize};
4use sqlx::Row;
5use sqlx::sqlite::SqliteRow;
6
7/// Explicit column list for `SELECT` queries on the `control_messages` table.
8///
9/// Using `SELECT *` with `sqlx-sqlite`'s prepared statement cache can cause OOB
10/// panics when the schema changes (new migration columns) because the cached
11/// column metadata may not match the current table structure. Explicit columns
12/// prevent this mismatch.
13const CONTROL_MESSAGE_COLS: &str = "id, session_id, role, channel, channel_thread, \
14    content, summary, model, agent, input_tokens, output_tokens, tokens_used, cost_usd, created_at";
15
16/// A message in the control session conversation history.
17#[derive(Debug, Clone)]
18pub struct ControlMessage {
19    pub id: i64,
20    pub session_id: String,
21    pub role: String,
22    pub channel: String,
23    pub channel_thread: Option<String>,
24    pub content: String,
25    pub summary: Option<String>,
26    pub model: Option<String>,
27    pub agent: Option<String>,
28    pub input_tokens: Option<i64>,
29    pub output_tokens: Option<i64>,
30    pub tokens_used: Option<i64>,
31    pub cost_usd: Option<f64>,
32    pub created_at: String,
33}
34
35/// A single memory entry from a task attempt.
36#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
37pub struct MemoryEntry {
38    /// Attempt number (1-indexed)
39    pub attempt: u32,
40    /// Agent that made this attempt
41    pub agent: String,
42    /// Model used for this attempt
43    pub model: Option<String>,
44    /// Key learnings from this attempt
45    pub learnings: Vec<String>,
46    /// Error message if the attempt failed
47    pub error: Option<String>,
48    /// Files modified in this attempt
49    pub files_modified: Vec<String>,
50    /// Approach taken (from summary)
51    pub approach: String,
52    /// Timestamp of the attempt
53    pub timestamp: String,
54}
55
56impl TaskStore {
57    pub const DEFAULT_SESSION: &'static str = "default";
58
59    /// Insert a control session message.
60    #[expect(
61        clippy::too_many_arguments,
62        reason = "one argument per column of the message row"
63    )]
64    pub async fn insert_control_message(
65        &self,
66        session_id: &str,
67        role: &str,
68        channel: &str,
69        channel_thread: Option<&str>,
70        content: &str,
71        summary: Option<&str>,
72        model: Option<&str>,
73        agent: Option<&str>,
74        input_tokens: Option<i64>,
75        output_tokens: Option<i64>,
76        tokens_used: Option<i64>,
77        cost_usd: Option<f64>,
78    ) -> anyhow::Result<i64> {
79        let row = sqlx::query(
80        "INSERT INTO control_messages (session_id, role, channel, channel_thread, content, summary, model, agent, input_tokens, output_tokens, tokens_used, cost_usd)
81         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id",
82    )
83    .bind(session_id)
84    .bind(role)
85    .bind(channel)
86    .bind(channel_thread)
87    .bind(content)
88    .bind(summary)
89    .bind(model)
90    .bind(agent)
91    .bind(input_tokens)
92    .bind(output_tokens)
93    .bind(tokens_used)
94    .bind(cost_usd)
95    .fetch_one(&self.pool)
96    .await?;
97        Ok(row.try_get("id")?)
98    }
99
100    /// Forget a session's messages, for a chat the owner starts over
101    pub async fn delete_control_session(&self, session_id: &str) -> anyhow::Result<()> {
102        sqlx::query("DELETE FROM control_messages WHERE session_id = ?")
103            .bind(session_id)
104            .execute(&self.pool)
105            .await?;
106        Ok(())
107    }
108
109    /// List the most recent control messages for a session (chronological order).
110    ///
111    /// If `since` is provided, only messages created at or after that RFC3339/ISO8601
112    /// timestamp are returned. Use [`parse_since_duration`] to convert human-readable
113    /// durations like `"7d"` or `"24h"` into a timestamp string.
114    pub async fn list_control_messages(
115        &self,
116        session_id: &str,
117        since: Option<&str>,
118        limit: i64,
119    ) -> anyhow::Result<Vec<ControlMessage>> {
120        // Subquery: get the N most recent, then re-sort chronologically
121        let rows = if let Some(ts) = since {
122            sqlx::query(sqlx::AssertSqlSafe(format!(
123                "SELECT {CONTROL_MESSAGE_COLS} FROM (
124                SELECT {CONTROL_MESSAGE_COLS} FROM control_messages
125                WHERE session_id = ? AND created_at >= ?
126                ORDER BY created_at DESC, id DESC LIMIT ?
127            ) ORDER BY created_at ASC, id ASC"
128            )))
129            .bind(session_id)
130            .bind(ts)
131            .bind(limit)
132            .fetch_all(&self.pool)
133            .await?
134        } else {
135            sqlx::query(sqlx::AssertSqlSafe(format!(
136                "SELECT {CONTROL_MESSAGE_COLS} FROM (
137                SELECT {CONTROL_MESSAGE_COLS} FROM control_messages
138                WHERE session_id = ?
139                ORDER BY created_at DESC, id DESC LIMIT ?
140            ) ORDER BY created_at ASC, id ASC"
141            )))
142            .bind(session_id)
143            .bind(limit)
144            .fetch_all(&self.pool)
145            .await?
146        };
147        rows.iter()
148            .map(|r| {
149                Self::row_to_control_message(r).with_context(|| {
150                    format!(
151                        "parsing control_message row id={:?} session_id={:?}",
152                        r.try_get::<i64, _>("id").ok(),
153                        r.try_get::<String, _>("session_id").ok()
154                    )
155                })
156            })
157            .collect::<Result<Vec<_>, _>>()
158    }
159
160    /// Search control messages by content (LIKE match, most recent first).
161    ///
162    /// If `since` is provided, only messages created at or after that RFC3339/ISO8601
163    /// timestamp are returned. Use [`parse_since_duration`] to convert human-readable
164    /// durations like `"7d"` or `"24h"` into a timestamp string.
165    pub async fn search_control_messages(
166        &self,
167        session_id: &str,
168        query: &str,
169        since: Option<&str>,
170        limit: i64,
171    ) -> anyhow::Result<Vec<ControlMessage>> {
172        // Escape LIKE wildcards in user input
173        let escaped = query
174            .replace('\\', "\\\\")
175            .replace('%', "\\%")
176            .replace('_', "\\_");
177        let pattern = format!("%{escaped}%");
178        let rows = if let Some(ts) = since {
179            sqlx::query(sqlx::AssertSqlSafe(format!(
180                "SELECT {CONTROL_MESSAGE_COLS} FROM control_messages \
181                 WHERE session_id = ? AND content LIKE ? ESCAPE '\\' AND created_at >= ? \
182                 ORDER BY created_at DESC LIMIT ?"
183            )))
184            .bind(session_id)
185            .bind(&pattern)
186            .bind(ts)
187            .bind(limit)
188            .fetch_all(&self.pool)
189            .await?
190        } else {
191            sqlx::query(sqlx::AssertSqlSafe(format!(
192                "SELECT {CONTROL_MESSAGE_COLS} FROM control_messages \
193                 WHERE session_id = ? AND content LIKE ? ESCAPE '\\' \
194                 ORDER BY created_at DESC LIMIT ?"
195            )))
196            .bind(session_id)
197            .bind(&pattern)
198            .bind(limit)
199            .fetch_all(&self.pool)
200            .await?
201        };
202        rows.iter()
203            .map(|r| {
204                Self::row_to_control_message(r).with_context(|| {
205                    format!(
206                        "parsing control_message row id={:?} session_id={:?}",
207                        r.try_get::<i64, _>("id").ok(),
208                        r.try_get::<String, _>("session_id").ok()
209                    )
210                })
211            })
212            .collect::<Result<Vec<_>, _>>()
213    }
214
215    /// Get the N most recent assistant message summaries (chronological order).
216    pub async fn control_recent_summaries(
217        &self,
218        session_id: &str,
219        limit: i64,
220    ) -> anyhow::Result<Vec<String>> {
221        // Subquery: get the N most recent, then re-sort chronologically
222        let rows = sqlx::query(
223            "SELECT summary FROM (
224            SELECT summary, created_at, id FROM control_messages
225            WHERE session_id = ? AND summary IS NOT NULL
226            ORDER BY created_at DESC, id DESC LIMIT ?
227        ) ORDER BY created_at ASC, id ASC",
228        )
229        .bind(session_id)
230        .bind(limit)
231        .fetch_all(&self.pool)
232        .await?;
233        rows.iter()
234            .map(|r| r.try_get::<String, _>("summary"))
235            .collect::<Result<Vec<_>, _>>()
236            .map_err(Into::into)
237    }
238
239    fn row_to_control_message(row: &SqliteRow) -> Result<ControlMessage, sqlx::Error> {
240        Ok(ControlMessage {
241            id: row.try_get("id")?,
242            session_id: row.try_get("session_id")?,
243            role: row.try_get("role")?,
244            channel: row.try_get("channel")?,
245            channel_thread: row.try_get::<Option<String>, _>("channel_thread")?,
246            content: row.try_get("content")?,
247            summary: row.try_get::<Option<String>, _>("summary")?,
248            model: row.try_get::<Option<String>, _>("model")?,
249            agent: row.try_get::<Option<String>, _>("agent")?,
250            input_tokens: row.try_get::<Option<i64>, _>("input_tokens")?,
251            output_tokens: row.try_get::<Option<i64>, _>("output_tokens")?,
252            tokens_used: row.try_get::<Option<i64>, _>("tokens_used")?,
253            cost_usd: row.try_get::<Option<f64>, _>("cost_usd")?,
254            created_at: row.try_get("created_at")?,
255        })
256    }
257}
258
259/// Cost summary for a chat session.
260#[derive(Debug, Clone, Default)]
261pub struct ChatCostSummary {
262    /// Total number of messages (user + assistant)
263    pub total_messages: i64,
264    /// Total number of assistant messages (responses from LLM)
265    pub assistant_messages: i64,
266    /// Total tokens used across all messages
267    pub total_tokens: i64,
268    /// Input tokens from assistant messages.
269    pub total_input_tokens: i64,
270    /// Output tokens from assistant messages.
271    pub total_output_tokens: i64,
272    /// Total estimated cost in USD
273    pub total_cost_usd: f64,
274    /// Breakdown by model (model name -> (message_count, tokens, cost))
275    pub by_model: Vec<(String, i64, i64, f64)>,
276    /// The most frequently used model
277    pub primary_model: Option<String>,
278    /// The agent used (typically consistent per session)
279    pub primary_agent: Option<String>,
280}
281
282impl TaskStore {
283    /// Get cost summary for a chat session.
284    ///
285    /// Returns aggregated statistics including total messages, tokens, cost,
286    /// and breakdown by model.
287    pub async fn get_session_cost_summary(
288        &self,
289        session_id: &str,
290    ) -> anyhow::Result<ChatCostSummary> {
291        // Get total counts and cost
292        let row = sqlx::query(
293            "SELECT
294                COUNT(*) as total_messages,
295                COUNT(CASE WHEN role = 'assistant' THEN 1 END) as assistant_messages,
296                COALESCE(SUM(tokens_used), 0) as total_tokens,
297                COALESCE(SUM(input_tokens), 0) as total_input_tokens,
298                COALESCE(SUM(output_tokens), 0) as total_output_tokens,
299                COALESCE(SUM(cost_usd), 0.0) as total_cost_usd
300             FROM control_messages
301             WHERE session_id = ?",
302        )
303        .bind(session_id)
304        .fetch_one(&self.pool)
305        .await?;
306
307        let total_messages: i64 = row.try_get("total_messages")?;
308        let assistant_messages: i64 = row.try_get("assistant_messages")?;
309        let total_tokens: i64 = row.try_get("total_tokens")?;
310        let total_input_tokens: i64 = row.try_get("total_input_tokens")?;
311        let total_output_tokens: i64 = row.try_get("total_output_tokens")?;
312        let total_cost_usd: f64 = row.try_get("total_cost_usd")?;
313
314        // Get breakdown by model
315        let model_rows = sqlx::query(
316            "SELECT
317                COALESCE(model, 'unknown') as model_name,
318                COUNT(*) as message_count,
319                COALESCE(SUM(tokens_used), 0) as tokens,
320                COALESCE(SUM(cost_usd), 0.0) as cost
321             FROM control_messages
322             WHERE session_id = ? AND role = 'assistant'
323             GROUP BY model
324             ORDER BY message_count DESC",
325        )
326        .bind(session_id)
327        .fetch_all(&self.pool)
328        .await?;
329
330        let mut by_model = Vec::new();
331        for row in model_rows {
332            let model: String = row.try_get("model_name")?;
333            let count: i64 = row.try_get("message_count")?;
334            let tokens: i64 = row.try_get("tokens")?;
335            let cost: f64 = row.try_get("cost")?;
336            by_model.push((model, count, tokens, cost));
337        }
338
339        // Determine primary model (most used)
340        let primary_model = by_model.first().map(|(m, _, _, _)| m.clone());
341
342        // Get primary agent (most used)
343        let agent_row = sqlx::query(
344            "SELECT COALESCE(agent, 'unknown') as agent_name
345             FROM control_messages
346             WHERE session_id = ? AND role = 'assistant' AND agent IS NOT NULL
347             GROUP BY agent
348             ORDER BY COUNT(*) DESC
349             LIMIT 1",
350        )
351        .bind(session_id)
352        .fetch_optional(&self.pool)
353        .await?;
354
355        let primary_agent = agent_row.and_then(|r| r.try_get::<String, _>("agent_name").ok());
356
357        Ok(ChatCostSummary {
358            total_messages,
359            assistant_messages,
360            total_tokens,
361            total_input_tokens,
362            total_output_tokens,
363            total_cost_usd,
364            by_model,
365            primary_model,
366            primary_agent,
367        })
368    }
369}
370
371/// Parse a human-readable duration string into an RFC3339 timestamp representing
372/// `now - duration`.
373///
374/// Supported suffixes:
375/// - `d` — days (e.g. `"7d"`)
376/// - `h` — hours (e.g. `"24h"`)
377/// - `m` — minutes (e.g. `"30m"`)
378///
379/// Returns an ISO8601/RFC3339 string suitable for comparison against
380/// `control_messages.created_at` (stored as UTC RFC3339).
381///
382/// Returns `Err` if the format is unrecognised or the number overflows.
383pub fn parse_since_duration(s: &str) -> anyhow::Result<String> {
384    let s = s.trim();
385    let (num_str, unit) = if let Some(n) = s.strip_suffix('d') {
386        (n, 'd')
387    } else if let Some(n) = s.strip_suffix('h') {
388        (n, 'h')
389    } else if let Some(n) = s.strip_suffix('m') {
390        (n, 'm')
391    } else {
392        anyhow::bail!(
393            "unrecognised --since format {s:?}; expected a number followed by d, h, or m (e.g. 7d, 24h, 30m)"
394        );
395    };
396
397    let n: u64 = num_str.trim().parse().map_err(|_| {
398        anyhow::anyhow!(
399            "invalid number in --since {s:?}; expected a positive integer followed by d, h, or m"
400        )
401    })?;
402    if n == 0 {
403        anyhow::bail!("--since value must be greater than zero");
404    }
405
406    let secs: i64 = match unit {
407        'd' => n.checked_mul(86_400),
408        'h' => n.checked_mul(3_600),
409        'm' => n.checked_mul(60),
410        _ => anyhow::bail!("unrecognized duration unit '{unit}' (expected d, h, or m)"),
411    }
412    .ok_or_else(|| anyhow::anyhow!("--since value is too large"))?
413    .try_into()
414    .map_err(|_| anyhow::anyhow!("--since value is too large"))?;
415
416    Ok((Utc::now() - Duration::seconds(secs)).to_rfc3339_opts(chrono::SecondsFormat::Secs, true))
417}
418
419#[cfg(test)]
420mod tests {
421    use super::*;
422
423    /// Helper: open an in-memory store and insert one control message via the public API.
424    async fn store_with_message() -> (TaskStore, i64) {
425        let store = TaskStore::open_memory().await.unwrap();
426        let id = store
427            .insert_control_message(
428                "sess1",
429                "user",
430                "cli",
431                None,
432                "hello world",
433                Some("a summary"),
434                Some("sonnet"),
435                Some("claude"),
436                Some(10),
437                Some(20),
438                Some(30),
439                Some(0.001),
440            )
441            .await
442            .unwrap();
443        (store, id)
444    }
445
446    /// All required fields must decode to their actual values, not silently default.
447    #[tokio::test]
448    async fn row_to_control_message_decodes_required_fields() {
449        let (store, _) = store_with_message().await;
450        let msgs = store
451            .list_control_messages("sess1", None, 10)
452            .await
453            .unwrap();
454        assert_eq!(msgs.len(), 1);
455        let m = &msgs[0];
456        assert_eq!(m.session_id, "sess1");
457        assert_eq!(m.role, "user");
458        assert_eq!(m.channel, "cli");
459        assert_eq!(m.content, "hello world");
460        assert_ne!(m.created_at, "");
461    }
462
463    /// Optional fields must be `Some(value)` when present and `None` when NULL.
464    #[tokio::test]
465    async fn row_to_control_message_decodes_optional_fields() {
466        let (store, _) = store_with_message().await;
467        let msgs = store
468            .list_control_messages("sess1", None, 10)
469            .await
470            .unwrap();
471        let m = &msgs[0];
472        assert_eq!(m.summary, Some("a summary".to_string()));
473        assert_eq!(m.model, Some("sonnet".to_string()));
474        assert_eq!(m.agent, Some("claude".to_string()));
475        assert_eq!(m.input_tokens, Some(10));
476        assert_eq!(m.output_tokens, Some(20));
477        assert_eq!(m.tokens_used, Some(30));
478        assert_eq!(m.cost_usd, Some(0.001));
479        assert_eq!(m.channel_thread, None);
480    }
481
482    /// Optional fields must be `None` when all nullable columns are NULL.
483    #[tokio::test]
484    async fn row_to_control_message_null_optional_fields_are_none() {
485        let store = TaskStore::open_memory().await.unwrap();
486        store
487            .insert_control_message(
488                "s",
489                "assistant",
490                "cli",
491                None,
492                "hi",
493                None,
494                None,
495                None,
496                None,
497                None,
498                None,
499                None,
500            )
501            .await
502            .unwrap();
503        let msgs = store.list_control_messages("s", None, 10).await.unwrap();
504        assert_eq!(msgs.len(), 1);
505        let m = &msgs[0];
506        assert!(m.summary.is_none());
507        assert!(m.model.is_none());
508        assert!(m.agent.is_none());
509        assert!(m.input_tokens.is_none());
510        assert!(m.output_tokens.is_none());
511        assert!(m.tokens_used.is_none());
512        assert!(m.cost_usd.is_none());
513        assert!(m.channel_thread.is_none());
514    }
515
516    /// Verify that decode errors from missing columns propagate via `?`.
517    ///
518    /// SQLite's loose type system coerces NULL to an empty string for TEXT columns,
519    /// so NULL-in-required-column does not produce a SQLx decode error. However,
520    /// a missing column (ColumnNotFound) does. This test confirms the `?` operator
521    /// actually propagates errors rather than swallowing them.
522    #[tokio::test]
523    async fn row_to_control_message_missing_column_propagates_error() {
524        let store = TaskStore::open_memory().await.unwrap();
525        // Select a row that omits the required `role` column entirely.
526        let rows = sqlx::query(
527            "SELECT 1 as id, 'sess' as session_id, 'cli' as channel, \
528             NULL as channel_thread, 'content' as content, NULL as summary, \
529             NULL as model, NULL as agent, NULL as input_tokens, NULL as output_tokens, \
530             NULL as tokens_used, NULL as cost_usd, '2024-01-01T00:00:00Z' as created_at",
531        )
532        .fetch_all(&store.pool)
533        .await
534        .unwrap();
535        assert_eq!(rows.len(), 1);
536        let result = TaskStore::row_to_control_message(&rows[0]);
537        assert!(
538            result.is_err(),
539            "expected Err when required column `role` is missing, got Ok"
540        );
541    }
542}