toolhub_storage/
suggestions.rs

1//! `agent_suggestions` accessors. Phase 6.
2//!
3//! The daily-task agent writes one row per top-1 recommendation it makes for
4//! a watched session. When the user invokes the suggested tool within
5//! `acceptance_window_minutes`, `mark_accepted` flips `accepted=1`. Digest
6//! summarises acceptance via `acceptance_stats`.
7
8use anyhow::Result;
9use chrono::{DateTime, Duration, Utc};
10use rusqlite::{Connection, params};
11use serde::{Deserialize, Serialize};
12
13#[derive(Debug, Clone, Serialize, Deserialize)]
14pub struct SuggestionRow {
15    pub id: i64,
16    pub session_id: String,
17    pub tool_id: String,
18    pub task_text: Option<String>,
19    pub score: Option<f64>,
20    pub suggested_at: String,
21    pub accepted: bool,
22    pub accepted_at: Option<String>,
23}
24
25/// Insert a top-1 suggestion. Returns the new row id.
26pub fn record(
27    conn: &Connection,
28    session_id: &str,
29    tool_id: &str,
30    task_text: Option<&str>,
31    score: Option<f64>,
32    suggested_at: DateTime<Utc>,
33) -> Result<i64> {
34    conn.execute(
35        "INSERT INTO agent_suggestions
36            (session_id, tool_id, task_text, score, suggested_at, accepted)
37         VALUES (?, ?, ?, ?, ?, 0)",
38        params![
39            session_id,
40            tool_id,
41            task_text,
42            score,
43            suggested_at.to_rfc3339(),
44        ],
45    )?;
46    Ok(conn.last_insert_rowid())
47}
48
49/// Mark every still-pending suggestion for this `(session_id, tool_id)` pair
50/// whose `suggested_at` is within `window_minutes` as accepted. Returns the
51/// number of rows updated. Idempotent: a second matching `tool_use` is a
52/// no-op because `accepted=0` is part of the predicate.
53pub fn mark_accepted(
54    conn: &Connection,
55    session_id: &str,
56    tool_id: &str,
57    accepted_at: DateTime<Utc>,
58    window_minutes: i64,
59) -> Result<usize> {
60    let cutoff = (accepted_at - Duration::minutes(window_minutes)).to_rfc3339();
61    let n = conn.execute(
62        "UPDATE agent_suggestions
63            SET accepted = 1, accepted_at = ?
64          WHERE session_id = ?
65            AND tool_id = ?
66            AND accepted = 0
67            AND suggested_at >= ?",
68        params![accepted_at.to_rfc3339(), session_id, tool_id, cutoff],
69    )?;
70    Ok(n)
71}
72
73/// `(suggested_count, accepted_count)` since `cutoff`.
74pub fn acceptance_stats(conn: &Connection, since: DateTime<Utc>) -> Result<(i64, i64)> {
75    let cutoff = since.to_rfc3339();
76    let row = conn.query_row(
77        "SELECT
78            COUNT(*) AS n,
79            COALESCE(SUM(accepted), 0) AS k
80         FROM agent_suggestions
81         WHERE suggested_at >= ?",
82        params![cutoff],
83        |r| Ok((r.get::<_, i64>(0)?, r.get::<_, i64>(1)?)),
84    )?;
85    Ok(row)
86}
87
88pub fn list(conn: &Connection, session_id: Option<&str>) -> Result<Vec<SuggestionRow>> {
89    let (sql, params): (&str, Vec<Box<dyn rusqlite::ToSql>>) = match session_id {
90        Some(sid) => (
91            "SELECT id, session_id, tool_id, task_text, score, suggested_at,
92                    accepted, accepted_at
93             FROM agent_suggestions
94             WHERE session_id = ?
95             ORDER BY suggested_at DESC",
96            vec![Box::new(sid.to_string())],
97        ),
98        None => (
99            "SELECT id, session_id, tool_id, task_text, score, suggested_at,
100                    accepted, accepted_at
101             FROM agent_suggestions
102             ORDER BY suggested_at DESC",
103            vec![],
104        ),
105    };
106    let mut stmt = conn.prepare(sql)?;
107    let param_refs: Vec<&dyn rusqlite::ToSql> = params
108        .iter()
109        .map(|b| b.as_ref() as &dyn rusqlite::ToSql)
110        .collect();
111    let rows = stmt
112        .query_map(param_refs.as_slice(), |row| {
113            Ok(SuggestionRow {
114                id: row.get(0)?,
115                session_id: row.get(1)?,
116                tool_id: row.get(2)?,
117                task_text: row.get(3)?,
118                score: row.get(4)?,
119                suggested_at: row.get(5)?,
120                accepted: row.get::<_, i64>(6)? != 0,
121                accepted_at: row.get(7)?,
122            })
123        })?
124        .collect::<Result<Vec<_>, _>>()?;
125    Ok(rows)
126}
127
128#[cfg(test)]
129mod tests {
130    use super::*;
131    use crate::open;
132
133    fn tmp_conn() -> (tempfile::TempDir, Connection) {
134        let dir = tempfile::tempdir().unwrap();
135        let conn = open(&dir.path().join("t.sqlite")).unwrap();
136        (dir, conn)
137    }
138
139    fn seed_tool(conn: &Connection, id: &str) {
140        conn.execute(
141            "INSERT OR IGNORE INTO tools (id, type, name, triggers, examples, requires,
142                                          enabled, added_at, last_seen_at)
143             VALUES (?, 'skill', ?, '[]', '[]', '[]', 1,
144                     '2026-05-03T00:00:00+00:00', '2026-05-03T00:00:00+00:00')",
145            params![id, id],
146        )
147        .unwrap();
148    }
149
150    #[test]
151    fn record_then_mark_accepted_flips_row() {
152        let (_d, conn) = tmp_conn();
153        seed_tool(&conn, "skill:caveman");
154        let suggested = Utc::now();
155        record(
156            &conn,
157            "sess-1",
158            "skill:caveman",
159            Some("be terse"),
160            Some(0.9),
161            suggested,
162        )
163        .unwrap();
164
165        let n = mark_accepted(&conn, "sess-1", "skill:caveman", suggested, 60).unwrap();
166        assert_eq!(n, 1);
167
168        let rows = list(&conn, Some("sess-1")).unwrap();
169        assert_eq!(rows.len(), 1);
170        assert!(rows[0].accepted);
171        assert!(rows[0].accepted_at.is_some());
172    }
173
174    #[test]
175    fn mark_accepted_ignores_old_suggestions() {
176        let (_d, conn) = tmp_conn();
177        seed_tool(&conn, "skill:caveman");
178        let old = Utc::now() - Duration::hours(2);
179        record(&conn, "sess-1", "skill:caveman", None, None, old).unwrap();
180
181        // Only suggestions in the last 60 min count; this one is 2h old.
182        let n = mark_accepted(&conn, "sess-1", "skill:caveman", Utc::now(), 60).unwrap();
183        assert_eq!(n, 0);
184
185        let rows = list(&conn, None).unwrap();
186        assert!(!rows[0].accepted);
187    }
188
189    #[test]
190    fn mark_accepted_is_idempotent() {
191        let (_d, conn) = tmp_conn();
192        seed_tool(&conn, "skill:x");
193        let ts = Utc::now();
194        record(&conn, "s", "skill:x", None, None, ts).unwrap();
195        assert_eq!(mark_accepted(&conn, "s", "skill:x", ts, 60).unwrap(), 1);
196        // Already accepted — second call is a no-op.
197        assert_eq!(mark_accepted(&conn, "s", "skill:x", ts, 60).unwrap(), 0);
198    }
199
200    #[test]
201    fn acceptance_stats_counts_correctly() {
202        let (_d, conn) = tmp_conn();
203        seed_tool(&conn, "skill:a");
204        seed_tool(&conn, "skill:b");
205        let now = Utc::now();
206        record(&conn, "s1", "skill:a", None, None, now).unwrap();
207        record(&conn, "s1", "skill:b", None, None, now).unwrap();
208        record(&conn, "s2", "skill:a", None, None, now).unwrap();
209        mark_accepted(&conn, "s1", "skill:a", now, 60).unwrap();
210
211        let (n, k) = acceptance_stats(&conn, now - Duration::hours(1)).unwrap();
212        assert_eq!(n, 3);
213        assert_eq!(k, 1);
214    }
215}
toolhub_storage/suggestions.rs

toolhub_storage/
suggestions.rs
