Skip to main content

second_brain_core/sqlite_store/
sessions.rs

1use std::collections::HashSet;
2use std::ffi::OsStr;
3use std::path::Path;
4
5use anyhow::Result;
6use chrono::{DateTime, Utc};
7use rusqlite::params;
8use uuid::Uuid;
9
10use super::SqliteStore;
11
12/// Claude Code nests a subagent's transcript under its session's directory;
13/// the main agent's transcript never sits there.
14const SUBAGENT_TRANSCRIPT: &str = "%/subagents/%";
15
16/// A top-level Claude Code session with transcript lines in a window.
17#[derive(Debug, Clone, PartialEq, Eq)]
18pub struct ActiveSession {
19    pub conversation_id: Uuid,
20    pub project_path: Option<String>,
21}
22
23impl SqliteStore {
24    /// Main-agent Claude Code sessions with a transcript line timed in
25    /// `[since, until)`, read from the raw chunks' `session_at`, so
26    /// archived and consolidated chunks still count. Subagent transcripts
27    /// are left out because no note is ever asked of them.
28    pub fn claude_code_sessions_between(
29        &self,
30        since: DateTime<Utc>,
31        until: DateTime<Utc>,
32        project_path: Option<&str>,
33    ) -> Result<Vec<ActiveSession>> {
34        let conn = self.reader();
35        let mut stmt = conn.prepare(
36            "SELECT conversation_id, MAX(project_path) FROM memories \
37             WHERE source = 'claude-code' AND conversation_id IS NOT NULL \
38             AND session_at >= ?1 AND session_at < ?2 \
39             AND source_id NOT LIKE ?3 \
40             AND (?4 IS NULL OR project_path = ?4) \
41             GROUP BY conversation_id",
42        )?;
43        let rows = stmt.query_map(
44            params![
45                since.to_rfc3339(),
46                until.to_rfc3339(),
47                SUBAGENT_TRANSCRIPT,
48                project_path
49            ],
50            |r| Ok((r.get::<_, String>(0)?, r.get::<_, Option<String>>(1)?)),
51        )?;
52        let mut sessions = Vec::new();
53        for row in rows {
54            let (id, project_path) = row?;
55            let Ok(conversation_id) = Uuid::parse_str(&id) else {
56                continue;
57            };
58            sessions.push(ActiveSession {
59                conversation_id,
60                project_path,
61            });
62        }
63        Ok(sessions)
64    }
65
66    /// Which of `ids` came from Claude Code session `session` by
67    /// `session_of`: a raw of its transcript or of a subagent's beneath
68    /// it, or a note filed under its id.
69    pub fn memories_from_session(&self, ids: &[Uuid], session: &str) -> Result<HashSet<Uuid>> {
70        let mut found = HashSet::new();
71        if ids.is_empty() {
72            return Ok(found);
73        }
74        let conn = self.reader();
75        for chunk in ids.chunks(500) {
76            let holes = vec!["?"; chunk.len()].join(", ");
77            let mut stmt = conn.prepare(&format!(
78                "SELECT id, source_id FROM memories WHERE id IN ({holes})"
79            ))?;
80            let rows = stmt.query_map(
81                rusqlite::params_from_iter(chunk.iter().map(Uuid::to_string)),
82                |r| Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?)),
83            )?;
84            for row in rows {
85                let (id, source_id) = row?;
86                if session_of(&source_id).as_deref() == Some(session)
87                    && let Ok(id) = Uuid::parse_str(&id)
88                {
89                    found.insert(id);
90                }
91            }
92        }
93        Ok(found)
94    }
95}
96
97/// The Claude Code session a transcript path belongs to: its file stem, or
98/// for a subagent transcript (`<session>/subagents/<agent>.jsonl`) the
99/// directory above `subagents`. An agent note's source_id is the bare
100/// session id, which is its own stem.
101pub(super) fn session_of(source_id: &str) -> Option<String> {
102    let path = Path::new(source_id);
103    let parent = path.parent();
104    let name = if parent.and_then(Path::file_name) == Some(OsStr::new("subagents")) {
105        parent?.parent()?.file_name()?
106    } else {
107        path.file_stem()?
108    };
109    Some(name.to_string_lossy().into_owned())
110}
111
112#[cfg(test)]
113mod tests {
114    use chrono::{Duration, TimeZone};
115
116    use super::*;
117    use crate::schema::{Conversation, Memory, MemoryType};
118    use crate::store::Store;
119
120    fn at(hour: i64) -> DateTime<Utc> {
121        Utc.with_ymd_and_hms(2026, 11, 1, 0, 0, 0).unwrap() + Duration::hours(hour)
122    }
123
124    fn raw(
125        store: &SqliteStore,
126        source: &str,
127        path: &str,
128        session: &str,
129        project: &str,
130        hour: Option<i64>,
131    ) {
132        let mut memory = Memory::new(
133            format!("chunk of {path} at {hour:?}"),
134            MemoryType::Episodic,
135            source.to_string(),
136            path.to_string(),
137        )
138        .with_project_path(Some(project.to_string()));
139        memory.conversation_id = Some(Conversation::claude_code_id(session));
140        memory.session_at = hour.map(at);
141        store.store_memory(&memory).unwrap();
142    }
143
144    #[test]
145    fn sessions_with_a_main_transcript_line_in_the_window_are_active() {
146        let store = SqliteStore::in_memory("m".to_string()).unwrap();
147        raw(&store, "claude-code", "/p/a/s1.jsonl", "s1", "-a", Some(1));
148        raw(&store, "claude-code", "/p/a/s1.jsonl", "s1", "-a", Some(5));
149        raw(&store, "claude-code", "/p/a/s2.jsonl", "s2", "-a", Some(12));
150        raw(&store, "claude-code", "/p/b/s3.jsonl", "s3", "-b", Some(6));
151        raw(&store, "claude-code", "/p/b/s4.jsonl", "s4", "-b", Some(2));
152        raw(&store, "claude-code", "/p/b/s5.jsonl", "s5", "-b", None);
153        raw(
154            &store,
155            "claude-code",
156            "/p/b/s3/subagents/agent-x.jsonl",
157            "s3/x",
158            "-b",
159            Some(6),
160        );
161        raw(&store, "codex", "/c/s6.jsonl", "s6", "-b", Some(6));
162
163        let mut active = store
164            .claude_code_sessions_between(at(4), at(12), None)
165            .unwrap();
166        active.sort_by_key(|s| s.project_path.clone());
167        assert_eq!(
168            active,
169            vec![
170                ActiveSession {
171                    conversation_id: Conversation::claude_code_id("s1"),
172                    project_path: Some("-a".to_string()),
173                },
174                ActiveSession {
175                    conversation_id: Conversation::claude_code_id("s3"),
176                    project_path: Some("-b".to_string()),
177                },
178            ]
179        );
180
181        let only_a = store
182            .claude_code_sessions_between(at(0), at(13), Some("-a"))
183            .unwrap();
184        let mut ids: Vec<_> = only_a.into_iter().map(|s| s.conversation_id).collect();
185        ids.sort();
186        let mut expected = vec![
187            Conversation::claude_code_id("s1"),
188            Conversation::claude_code_id("s2"),
189        ];
190        expected.sort();
191        assert_eq!(ids, expected);
192    }
193
194    #[test]
195    fn memories_from_a_session_are_its_transcripts_and_its_notes() {
196        let store = SqliteStore::in_memory("m".to_string()).unwrap();
197        let stored = |source: &str, source_id: &str| {
198            let memory = Memory::new(
199                format!("{source} {source_id}"),
200                MemoryType::Episodic,
201                source.to_string(),
202                source_id.to_string(),
203            );
204            store.store_memory(&memory).unwrap();
205            memory.id
206        };
207        let main = stored("claude-code", "/h/.claude/projects/-a/s1.jsonl");
208        let subagent = stored(
209            "claude-code",
210            "/h/.claude/projects/-a/s1/subagents/agent-x.jsonl",
211        );
212        let note = stored("agent-note", "s1");
213        let other = stored("claude-code", "/h/.claude/projects/-a/s2.jsonl");
214        let longer = stored("claude-code", "/h/.claude/projects/-a/s1x.jsonl");
215        let prefixed = stored("agent-note", "xs1");
216        let unknown = uuid::Uuid::new_v4();
217
218        let found = store
219            .memories_from_session(
220                &[main, subagent, note, other, longer, prefixed, unknown],
221                "s1",
222            )
223            .unwrap();
224        let expected: std::collections::HashSet<Uuid> = [main, subagent, note].into();
225        assert_eq!(found, expected);
226        assert!(store.memories_from_session(&[], "s1").unwrap().is_empty());
227    }
228
229    #[test]
230    fn the_session_of_a_transcript_path_is_its_stem_or_the_dir_above_subagents() {
231        for (path, session) in [
232            ("/p/-w/abc.jsonl", Some("abc")),
233            ("/p/-w/abc/subagents/agent-1.jsonl", Some("abc")),
234            ("abc.jsonl", Some("abc")),
235            ("abc", Some("abc")),
236            ("subagents/agent-1.jsonl", None),
237            ("", None),
238        ] {
239            assert_eq!(session_of(path).as_deref(), session, "{path:?}");
240        }
241    }
242}