second_brain_core/sqlite_store/
sessions.rs1use 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
12const SUBAGENT_TRANSCRIPT: &str = "%/subagents/%";
15
16#[derive(Debug, Clone, PartialEq, Eq)]
18pub struct ActiveSession {
19 pub conversation_id: Uuid,
20 pub project_path: Option<String>,
21}
22
23impl SqliteStore {
24 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 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
97pub 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}