Skip to main content

second_brain_core/sqlite_store/
notes.rs

1use std::collections::HashSet;
2
3use anyhow::Result;
4use chrono::{DateTime, Utc};
5use rusqlite::params;
6use serde::{Deserialize, Serialize};
7
8use super::{MEMORY_COLS, SqliteStore, row_to_memory};
9use crate::schema::{AGENT_NOTE_SOURCE, Memory, MemoryType};
10
11#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
12#[non_exhaustive]
13pub enum NoteKind {
14    #[default]
15    #[serde(rename = "session-end")]
16    SessionEnd,
17    #[serde(rename = "decision")]
18    Decision,
19    #[serde(rename = "dead_end")]
20    DeadEnd,
21    #[serde(rename = "fact")]
22    Fact,
23}
24
25impl NoteKind {
26    pub const ALL: &'static [NoteKind] = &[
27        NoteKind::SessionEnd,
28        NoteKind::Decision,
29        NoteKind::DeadEnd,
30        NoteKind::Fact,
31    ];
32
33    pub fn as_str(self) -> &'static str {
34        match self {
35            NoteKind::SessionEnd => "session-end",
36            NoteKind::Decision => "decision",
37            NoteKind::DeadEnd => "dead_end",
38            NoteKind::Fact => "fact",
39        }
40    }
41
42    pub fn parse(name: &str) -> Option<Self> {
43        Self::ALL.iter().copied().find(|kind| kind.as_str() == name)
44    }
45
46    pub fn memory_type(self) -> MemoryType {
47        match self {
48            NoteKind::SessionEnd | NoteKind::Decision => MemoryType::Decision,
49            NoteKind::DeadEnd => MemoryType::Debugging,
50            NoteKind::Fact => MemoryType::Semantic,
51        }
52    }
53
54    fn label(self) -> &'static str {
55        match self {
56            NoteKind::SessionEnd => "Session-end note: ",
57            NoteKind::Decision => "Decision: ",
58            NoteKind::DeadEnd => "Dead end: ",
59            NoteKind::Fact => "Fact: ",
60        }
61    }
62}
63
64/// What an agent records about its own session. Stored as the content of an
65/// `agent-note` memory in the text `render` writes, which `parse` reads back,
66/// so the fields survive sync and redaction without a column of their own.
67#[derive(Debug, Clone, PartialEq, Eq, Default, Serialize, Deserialize)]
68#[non_exhaustive]
69pub struct AgentNote {
70    #[serde(default)]
71    pub kind: NoteKind,
72    pub decision: String,
73    #[serde(default)]
74    pub reason: String,
75    #[serde(default)]
76    pub dead_ends: Vec<String>,
77    #[serde(default)]
78    pub open_items: Vec<String>,
79    #[serde(default)]
80    pub blocked_on_user: Vec<String>,
81}
82
83const REASON: &str = "Reason: ";
84const ITEM: &str = "- ";
85const DEAD_ENDS: &str = "Dead ends:";
86const OPEN_ITEMS: &str = "Open items:";
87const BLOCKED_ON_USER: &str = "Blocked on user:";
88
89fn one_line(text: &str) -> String {
90    text.split_whitespace().collect::<Vec<_>>().join(" ")
91}
92
93fn lines(items: &[String]) -> Vec<String> {
94    items
95        .iter()
96        .map(|item| one_line(item))
97        .filter(|item| !item.is_empty())
98        .collect()
99}
100
101impl AgentNote {
102    pub fn new(kind: NoteKind, decision: impl Into<String>) -> AgentNote {
103        AgentNote {
104            kind,
105            decision: decision.into(),
106            ..AgentNote::default()
107        }
108    }
109
110    /// Every field on one line with its whitespace collapsed and empty list
111    /// items dropped: the form `render` writes and `parse` returns.
112    pub fn normalized(&self) -> AgentNote {
113        AgentNote {
114            kind: self.kind,
115            decision: one_line(&self.decision),
116            reason: one_line(&self.reason),
117            dead_ends: lines(&self.dead_ends),
118            open_items: lines(&self.open_items),
119            blocked_on_user: lines(&self.blocked_on_user),
120        }
121    }
122
123    pub fn render(&self) -> String {
124        let note = self.normalized();
125        let mut out = format!("{}{}", note.kind.label(), note.decision);
126        if !note.reason.is_empty() {
127            out.push('\n');
128            out.push_str(REASON);
129            out.push_str(&note.reason);
130        }
131        for (header, items) in [
132            (DEAD_ENDS, &note.dead_ends),
133            (OPEN_ITEMS, &note.open_items),
134            (BLOCKED_ON_USER, &note.blocked_on_user),
135        ] {
136            if items.is_empty() {
137                continue;
138            }
139            out.push('\n');
140            out.push_str(header);
141            for item in items {
142                out.push('\n');
143                out.push_str(ITEM);
144                out.push_str(item);
145            }
146        }
147        out
148    }
149
150    /// The note `render` wrote, or None for text in any other shape.
151    pub fn parse(content: &str) -> Option<AgentNote> {
152        let mut rows = content.split('\n');
153        let first = rows.next()?;
154        let (kind, decision) = NoteKind::ALL
155            .iter()
156            .copied()
157            .find_map(|kind| Some((kind, first.strip_prefix(kind.label())?)))?;
158        let mut note = AgentNote {
159            kind,
160            decision: decision.to_string(),
161            ..AgentNote::default()
162        };
163        let mut section: Option<&mut Vec<String>> = None;
164        let mut seen_section = false;
165        for row in rows {
166            if let Some(item) = row.strip_prefix(ITEM) {
167                section.as_mut()?.push(item.to_string());
168                continue;
169            }
170            if let Some(reason) = row.strip_prefix(REASON) {
171                if seen_section || !note.reason.is_empty() {
172                    return None;
173                }
174                note.reason = reason.to_string();
175                continue;
176            }
177            seen_section = true;
178            section = Some(match row {
179                DEAD_ENDS => &mut note.dead_ends,
180                OPEN_ITEMS => &mut note.open_items,
181                BLOCKED_ON_USER => &mut note.blocked_on_user,
182                _ => return None,
183            });
184        }
185        Some(note)
186    }
187}
188
189/// The rows that are live agent notes, with the source bound as `?1`.
190const LIVE_NOTE: &str = "source = ?1 AND tier = 'hot' AND superseded_by IS NULL";
191
192/// Up to `limit` of `rows` whose content parses as a note of `kind`, in
193/// the order given; a row that no longer parses is skipped.
194fn parsed(
195    rows: impl Iterator<Item = rusqlite::Result<Memory>>,
196    kind: Option<NoteKind>,
197    limit: usize,
198) -> Result<Vec<(Memory, AgentNote)>> {
199    let mut notes = Vec::new();
200    for memory in rows {
201        if notes.len() >= limit {
202            break;
203        }
204        let memory = memory?;
205        let Some(note) = AgentNote::parse(&memory.content) else {
206            continue;
207        };
208        if kind.is_none_or(|kind| kind == note.kind) {
209            notes.push((memory, note));
210        }
211    }
212    Ok(notes)
213}
214
215impl SqliteStore {
216    /// Live agent notes filed under `session_id`, oldest first, each with
217    /// its fields read back from the content. A row whose content no longer
218    /// parses is skipped.
219    pub fn notes_for_session(
220        &self,
221        session_id: &str,
222        kind: Option<NoteKind>,
223    ) -> Result<Vec<(Memory, AgentNote)>> {
224        self.live_notes("source_id", session_id, "created_at, id", kind, usize::MAX)
225    }
226
227    /// At most `limit` live agent notes filed under `project`, newest first,
228    /// with the kind filter applied before the limit. A row whose content no
229    /// longer parses is skipped.
230    pub fn latest_notes_for_project(
231        &self,
232        project: &str,
233        kind: Option<NoteKind>,
234        limit: usize,
235    ) -> Result<Vec<(Memory, AgentNote)>> {
236        self.live_notes("project_path", project, "created_at DESC, id", kind, limit)
237    }
238
239    fn live_notes(
240        &self,
241        column: &str,
242        value: &str,
243        order: &str,
244        kind: Option<NoteKind>,
245        limit: usize,
246    ) -> Result<Vec<(Memory, AgentNote)>> {
247        let conn = self.reader();
248        let mut stmt = conn.prepare(&format!(
249            "SELECT {MEMORY_COLS} FROM memories \
250             WHERE {LIVE_NOTE} AND {column} = ?2 ORDER BY {order}"
251        ))?;
252        let rows = stmt.query_map(params![AGENT_NOTE_SOURCE, value], |r| {
253            row_to_memory(r, false)
254        })?;
255        parsed(rows, kind, limit)
256    }
257
258    /// Live agent notes created in `[since, until)`, newest first, in one
259    /// project when `project_path` names it. Rows that no longer parse are
260    /// skipped.
261    pub fn notes_created_between(
262        &self,
263        since: DateTime<Utc>,
264        until: DateTime<Utc>,
265        project_path: Option<&str>,
266    ) -> Result<Vec<(Memory, AgentNote)>> {
267        let conn = self.reader();
268        let mut stmt = conn.prepare(&format!(
269            "SELECT {MEMORY_COLS} FROM memories \
270             WHERE {LIVE_NOTE} AND created_at >= ?2 AND created_at < ?3 \
271             AND (?4 IS NULL OR project_path = ?4) \
272             ORDER BY created_at DESC, id"
273        ))?;
274        let rows = stmt.query_map(
275            params![
276                AGENT_NOTE_SOURCE,
277                since.to_rfc3339(),
278                until.to_rfc3339(),
279                project_path
280            ],
281            |r| row_to_memory(r, false),
282        )?;
283        parsed(rows, None, usize::MAX)
284    }
285
286    /// Every session id any live agent note is filed under.
287    pub fn noted_session_ids(&self) -> Result<HashSet<String>> {
288        let conn = self.reader();
289        let mut stmt = conn.prepare(&format!(
290            "SELECT DISTINCT source_id FROM memories WHERE {LIVE_NOTE}"
291        ))?;
292        let rows = stmt.query_map(params![AGENT_NOTE_SOURCE], |r| r.get::<_, String>(0))?;
293        Ok(rows.collect::<rusqlite::Result<HashSet<_>>>()?)
294    }
295}
296
297#[cfg(test)]
298mod tests {
299    use chrono::{Duration, TimeZone};
300    use uuid::Uuid;
301
302    use super::*;
303    use crate::store::Store;
304
305    fn full() -> AgentNote {
306        AgentNote {
307            kind: NoteKind::SessionEnd,
308            decision: "keep the cache  per worktree".to_string(),
309            reason: "a shared one\nmixed crates".to_string(),
310            dead_ends: vec!["one shared dir".to_string(), "  ".to_string()],
311            open_items: vec!["- starts with a dash".to_string()],
312            blocked_on_user: vec!["Reason: looks like a header".to_string()],
313        }
314    }
315
316    #[test]
317    fn a_rendered_note_reads_back_as_its_normalized_form() {
318        let note = full();
319        let text = note.render();
320        assert_eq!(
321            text,
322            "Session-end note: keep the cache per worktree\n\
323             Reason: a shared one mixed crates\n\
324             Dead ends:\n- one shared dir\n\
325             Open items:\n- - starts with a dash\n\
326             Blocked on user:\n- Reason: looks like a header"
327        );
328        assert_eq!(AgentNote::parse(&text), Some(note.normalized()));
329        assert_eq!(AgentNote::parse(&text).unwrap().render(), text);
330    }
331
332    #[test]
333    fn every_kind_round_trips_with_only_a_decision() {
334        for &kind in NoteKind::ALL {
335            let note = AgentNote {
336                kind,
337                decision: "x".to_string(),
338                ..AgentNote::default()
339            };
340            assert_eq!(AgentNote::parse(&note.render()), Some(note), "{kind:?}");
341            assert_eq!(NoteKind::parse(kind.as_str()), Some(kind));
342            assert_eq!(
343                serde_json::to_value(kind).unwrap(),
344                serde_json::json!(kind.as_str())
345            );
346        }
347    }
348
349    #[test]
350    fn text_in_another_shape_is_not_a_note() {
351        for text in [
352            "",
353            "plain remembered text",
354            "Session-end note: a\nsomething else",
355            "Session-end note: a\n- item before any section",
356            "Session-end note: a\nDead ends:\n- x\nReason: late",
357            "Session-end note: a\nReason: one\nReason: two",
358        ] {
359            assert_eq!(AgentNote::parse(text), None, "{text:?}");
360        }
361    }
362
363    fn stored(store: &SqliteStore, session: &str, note: &AgentNote) -> Memory {
364        let memory = Memory::new(
365            note.render(),
366            note.kind.memory_type(),
367            AGENT_NOTE_SOURCE.to_string(),
368            session.to_string(),
369        );
370        store.store_memory(&memory).unwrap();
371        memory
372    }
373
374    #[test]
375    fn notes_for_a_session_are_its_live_agent_notes_filtered_by_kind() {
376        let store = SqliteStore::in_memory("m".to_string()).unwrap();
377        let end = stored(&store, "s1", &full());
378        let fact = AgentNote {
379            kind: NoteKind::Fact,
380            decision: "the port is 7300".to_string(),
381            ..AgentNote::default()
382        };
383        let fact_row = stored(&store, "s1", &fact);
384        stored(&store, "s2", &full());
385        let retired = stored(&store, "s1", &full());
386        store.retire_memory(retired.id, "test").unwrap();
387        let mut other_source = Memory::new(
388            full().render(),
389            MemoryType::Decision,
390            "mcp-direct".to_string(),
391            "s1".to_string(),
392        );
393        other_source.id = uuid::Uuid::new_v4();
394        store.store_memory(&other_source).unwrap();
395
396        let all: Vec<_> = store
397            .notes_for_session("s1", None)
398            .unwrap()
399            .into_iter()
400            .map(|(m, _)| m.id)
401            .collect();
402        let mut expected = vec![(end.created_at, end.id), (fact_row.created_at, fact_row.id)];
403        expected.sort_by_key(|(at, id)| (*at, id.to_string()));
404        let expected: Vec<_> = expected.into_iter().map(|(_, id)| id).collect();
405        assert_eq!(all, expected);
406
407        let ends = store
408            .notes_for_session("s1", Some(NoteKind::SessionEnd))
409            .unwrap();
410        assert_eq!(ends.len(), 1);
411        assert_eq!(ends[0].0.id, end.id);
412        assert_eq!(ends[0].1, full().normalized());
413        assert!(store.notes_for_session("nope", None).unwrap().is_empty());
414    }
415
416    fn at(hour: i64) -> DateTime<Utc> {
417        Utc.with_ymd_and_hms(2026, 11, 1, 0, 0, 0).unwrap() + Duration::hours(hour)
418    }
419
420    fn filed(store: &SqliteStore, session: &str, project: Option<&str>, hour: i64) -> Memory {
421        let mut memory = Memory::new(
422            AgentNote::new(NoteKind::Decision, format!("{session} at {hour}")).render(),
423            MemoryType::Decision,
424            AGENT_NOTE_SOURCE.to_string(),
425            session.to_string(),
426        )
427        .with_project_path(project.map(str::to_string));
428        memory.created_at = at(hour);
429        store.store_memory(&memory).unwrap();
430        memory
431    }
432
433    #[test]
434    fn notes_created_in_a_window_come_newest_first_and_live_only() {
435        let store = SqliteStore::in_memory("m".to_string()).unwrap();
436        let early = filed(&store, "s1", Some("-a"), 4);
437        let late = filed(&store, "s2", None, 9);
438        let other = filed(&store, "s3", Some("-b"), 6);
439        filed(&store, "s4", Some("-a"), 3);
440        filed(&store, "s5", Some("-a"), 12);
441        let retired = filed(&store, "s6", Some("-a"), 5);
442        store.retire_memory(retired.id, "test").unwrap();
443        let mut plain = Memory::new(
444            "Decision: looks like a note".to_string(),
445            MemoryType::Decision,
446            "mcp-direct".to_string(),
447            "s7".to_string(),
448        );
449        plain.created_at = at(5);
450        store.store_memory(&plain).unwrap();
451
452        let ids = |project| -> Vec<Uuid> {
453            store
454                .notes_created_between(at(4), at(12), project)
455                .unwrap()
456                .into_iter()
457                .map(|(m, _)| m.id)
458                .collect()
459        };
460        assert_eq!(ids(None), vec![late.id, other.id, early.id]);
461        assert_eq!(ids(Some("-a")), vec![early.id]);
462    }
463
464    #[test]
465    fn noted_session_ids_are_the_sessions_of_live_notes() {
466        let store = SqliteStore::in_memory("m".to_string()).unwrap();
467        filed(&store, "s1", None, 1);
468        filed(&store, "s1", Some("-a"), 2);
469        filed(&store, "s2", Some("-a"), 3);
470        let retired = filed(&store, "s3", None, 4);
471        store.retire_memory(retired.id, "test").unwrap();
472        let expected: HashSet<String> = ["s1", "s2"].iter().map(|s| s.to_string()).collect();
473        assert_eq!(store.noted_session_ids().unwrap(), expected);
474    }
475
476    fn project_note(project: &str, minute: u32, note: &AgentNote) -> Memory {
477        let mut memory = Memory::new(
478            note.render(),
479            note.kind.memory_type(),
480            AGENT_NOTE_SOURCE.to_string(),
481            format!("s{minute}"),
482        )
483        .with_project_path(Some(project.to_string()));
484        memory.created_at =
485            chrono::DateTime::parse_from_rfc3339(&format!("2026-11-01T10:{minute:02}:00Z"))
486                .unwrap()
487                .with_timezone(&chrono::Utc);
488        memory
489    }
490
491    fn ids(notes: Vec<(Memory, AgentNote)>) -> Vec<uuid::Uuid> {
492        notes.into_iter().map(|(m, _)| m.id).collect()
493    }
494
495    #[test]
496    fn latest_notes_for_a_project_are_its_live_notes_newest_first() {
497        let store = SqliteStore::in_memory("m".to_string()).unwrap();
498        let end = |decision: &str| AgentNote::new(NoteKind::SessionEnd, decision);
499        let first = project_note("p1", 1, &end("first"));
500        let second = project_note("p1", 2, &end("second"));
501        let third = project_note("p1", 3, &end("third"));
502        let fact = project_note("p1", 4, &AgentNote::new(NoteKind::Fact, "the port is 7300"));
503        let retired = project_note("p1", 5, &end("retired"));
504        let elsewhere = project_note("p2", 6, &end("other project"));
505        let mut unparsed = project_note("p1", 7, &end("unparsed"));
506        unparsed.content = "plain remembered text".to_string();
507        let mut other_source = project_note("p1", 8, &end("other source"));
508        other_source.source = "mcp-direct".to_string();
509        for memory in [
510            &third,
511            &first,
512            &fact,
513            &second,
514            &retired,
515            &elsewhere,
516            &unparsed,
517            &other_source,
518        ] {
519            store.store_memory(memory).unwrap();
520        }
521        store.retire_memory(retired.id, "test").unwrap();
522
523        assert_eq!(
524            ids(store.latest_notes_for_project("p1", None, 10).unwrap()),
525            vec![fact.id, third.id, second.id, first.id]
526        );
527        assert_eq!(
528            ids(store.latest_notes_for_project("p1", None, 2).unwrap()),
529            vec![fact.id, third.id]
530        );
531        let ends = store
532            .latest_notes_for_project("p1", Some(NoteKind::SessionEnd), 2)
533            .unwrap();
534        assert_eq!(ends[0].1, end("third"));
535        assert_eq!(ids(ends), vec![third.id, second.id]);
536        assert!(
537            store
538                .latest_notes_for_project("p1", Some(NoteKind::DeadEnd), 10)
539                .unwrap()
540                .is_empty()
541        );
542        assert!(
543            store
544                .latest_notes_for_project("nope", None, 10)
545                .unwrap()
546                .is_empty()
547        );
548    }
549}