Skip to main content

second_brain_core/sqlite_store/
mod.rs

1mod ddl;
2pub(crate) mod pool;
3mod sync_apply;
4#[cfg(any(test, feature = "test-support"))]
5pub mod test_support;
6
7pub use ddl::{BASELINE_VERSION, SCHEMA_VERSION};
8
9use std::collections::HashSet;
10use std::path::Path;
11use std::sync::{Mutex, MutexGuard};
12
13use anyhow::Result;
14use chrono::{DateTime, Utc};
15use rusqlite::{Connection, OptionalExtension, Row, Transaction, params};
16use uuid::Uuid;
17
18use crate::schema::{
19    Conversation, Distillation, Entity, Memory, MemoryType, Relation, RelationType, SyncNodeType,
20    SyncOp, Tier,
21};
22use crate::store::Store;
23
24const MEMORY_COLS: &str = "id, content, context, memory_type, confidence, created_at, updated_at, \
25     last_accessed, access_count, source, source_id, project_path, machine_id, conversation_id, \
26     tier, archived_segment, superseded_by, retired_reason";
27
28/// Cap on what one `traverse` call returns. The walk also stops expanding
29/// after `TRAVERSE_MAX_WALK` (id, depth) pairs, because a hub entity can
30/// connect one memory to tens of thousands and the context pack that asks
31/// only shows a handful.
32pub const TRAVERSE_MAX_RESULTS: usize = 50;
33const TRAVERSE_MAX_WALK: i64 = 2000;
34
35/// The characters `str::trim` strips (every char with the Unicode White_Space
36/// property) as a SQLite `char()` call, so the dedup key trims in SQL exactly
37/// as `remember` trims in Rust. It must match the expression in
38/// migrations/0003_memories_dedup_key.sql, or the dedup lookup stops reading
39/// that index and scans the table instead.
40const DEDUP_TRIM_SET: &str = "char(9, 10, 11, 12, 13, 32, 133, 160, 5760, 8192, 8193, 8194, 8195, \
41     8196, 8197, 8198, 8199, 8200, 8201, 8202, 8232, 8233, 8239, 8287, 12288)";
42
43/// The oldest live memory whose dedup key equals ?1, a key the caller has
44/// already trimmed and lowercased. `idx_memories_dedup_key` indexes the
45/// 128-character prefix, so the full comparison runs only on rows sharing
46/// it. `+project_path` keeps the planner off the project_path index, which
47/// would read a whole project instead of the few rows the key names.
48fn dedup_query(with_project: bool) -> String {
49    let project = if with_project {
50        " AND +project_path = ?2"
51    } else {
52        ""
53    };
54    format!(
55        "SELECT {MEMORY_COLS} FROM memories \
56         WHERE substr(lower(trim(content, {DEDUP_TRIM_SET})), 1, 128) = substr(?1, 1, 128) \
57         AND lower(trim(content, {DEDUP_TRIM_SET})) = ?1 \
58         AND tier = 'hot' AND superseded_by IS NULL{project} \
59         ORDER BY rowid LIMIT 1"
60    )
61}
62
63/// Every write (via `with_txn` or a direct `execute`) goes through `writer`,
64/// the single connection WAL reserves for the one process-wide writer. Reads
65/// go through `readers`: a pool of separate connections in file-backed mode,
66/// or the same connection as the writer for `in_memory`, where a second
67/// connection would just see an empty, unrelated database.
68pub struct SqliteStore {
69    writer: Mutex<Connection>,
70    readers: ReadBackend,
71    machine_id: String,
72}
73
74enum ReadBackend {
75    Pooled(pool::ReaderPool),
76    SameAsWriter,
77}
78
79#[allow(private_interfaces)]
80pub(crate) enum ReaderGuard<'a> {
81    Pooled(pool::PooledConn<'a>),
82    Writer(MutexGuard<'a, Connection>),
83}
84
85impl std::ops::Deref for ReaderGuard<'_> {
86    type Target = Connection;
87
88    fn deref(&self) -> &Connection {
89        match self {
90            ReaderGuard::Pooled(p) => p,
91            ReaderGuard::Writer(g) => g,
92        }
93    }
94}
95
96impl SqliteStore {
97    pub fn open(path: &Path, machine_id: String) -> Result<Self> {
98        Self::open_with_pool_size(path, machine_id, pool::default_pool_size())
99    }
100
101    /// Same as `open`, with an explicit reader pool size instead of the
102    /// parallelism-derived default. `reader_pool_size` is clamped to at
103    /// least 1 so a caller passing 0 still gets a working store.
104    pub fn open_with_pool_size(
105        path: &Path,
106        machine_id: String,
107        reader_pool_size: usize,
108    ) -> Result<Self> {
109        let writer = pool::open_connection(path)?;
110        ddl::init_schema(&writer)?;
111        let readers = pool::ReaderPool::open(path, reader_pool_size.max(1))?;
112        Ok(Self {
113            writer: Mutex::new(writer),
114            readers: ReadBackend::Pooled(readers),
115            machine_id,
116        })
117    }
118
119    pub fn in_memory(machine_id: String) -> Result<Self> {
120        let conn = Connection::open_in_memory()?;
121        ddl::init_schema(&conn)?;
122        Ok(Self {
123            writer: Mutex::new(conn),
124            readers: ReadBackend::SameAsWriter,
125            machine_id,
126        })
127    }
128
129    pub fn machine_id(&self) -> &str {
130        &self.machine_id
131    }
132
133    pub(crate) fn conn(&self) -> MutexGuard<'_, Connection> {
134        self.writer.lock().unwrap()
135    }
136
137    pub(crate) fn reader(&self) -> ReaderGuard<'_> {
138        match &self.readers {
139            ReadBackend::Pooled(pool) => ReaderGuard::Pooled(pool.checkout()),
140            ReadBackend::SameAsWriter => ReaderGuard::Writer(self.writer.lock().unwrap()),
141        }
142    }
143
144    pub(crate) fn with_txn<T>(&self, f: impl FnOnce(&Transaction<'_>) -> Result<T>) -> Result<T> {
145        let mut conn = self.conn();
146        let tx = conn.transaction()?;
147        let value = f(&tx)?;
148        tx.commit()?;
149        Ok(value)
150    }
151
152    pub fn tombstone_exists(&self, id: Uuid) -> Result<bool> {
153        let conn = self.reader();
154        let hit: Option<i64> = conn
155            .query_row(
156                "SELECT 1 FROM tombstones WHERE node_id = ?1",
157                params![id.to_string()],
158                |r| r.get(0),
159            )
160            .optional()?;
161        Ok(hit.is_some())
162    }
163
164    pub fn memory_content_exists(&self, content_prefix: &str) -> Result<bool> {
165        let conn = self.reader();
166        let hit: Option<i64> = conn
167            .query_row(
168                "SELECT 1 FROM memories WHERE substr(content, 1, length(?1)) = ?1 LIMIT 1",
169                params![content_prefix],
170                |r| r.get(0),
171            )
172            .optional()?;
173        Ok(hit.is_some())
174    }
175
176    pub fn max_sync_seq(&self) -> Result<u64> {
177        let conn = self.reader();
178        let max: i64 = conn.query_row(
179            "SELECT COALESCE(MAX(local_seq), 0) FROM sync_log",
180            [],
181            |r| r.get(0),
182        )?;
183        Ok(max as u64)
184    }
185
186    pub fn get_conversation(&self, id: Uuid) -> Result<Option<Conversation>> {
187        let conn = self.reader();
188        conn.query_row(
189            "SELECT id, source, machine_id, started_at, project_path FROM conversations WHERE id = ?1",
190            params![id.to_string()],
191            |r| {
192                Ok(Conversation {
193                    id: parse_uuid(&r.get::<_, String>(0)?)?,
194                    source: r.get(1)?,
195                    machine_id: r.get(2)?,
196                    started_at: parse_dt(&r.get::<_, String>(3)?)?,
197                    project_path: r.get(4)?,
198                })
199            },
200        )
201        .optional()
202        .map_err(Into::into)
203    }
204
205    fn insert_memory(tx: &Transaction<'_>, memory: &Memory) -> Result<()> {
206        let embedding = if memory.embedding.is_empty() {
207            None
208        } else {
209            Some(embedding_to_blob(&memory.embedding))
210        };
211        tx.execute(
212            &format!(
213                "INSERT OR REPLACE INTO memories ({MEMORY_COLS}, embedding) \
214                 VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16,?17,?18,?19)"
215            ),
216            params![
217                memory.id.to_string(),
218                memory.content,
219                memory.context,
220                memory_type_to_str(memory.memory_type),
221                memory.confidence as f64,
222                memory.created_at.to_rfc3339(),
223                memory.updated_at.to_rfc3339(),
224                memory.last_accessed.to_rfc3339(),
225                memory.access_count as i64,
226                memory.source,
227                memory.source_id,
228                memory.project_path,
229                memory.machine_id,
230                memory.conversation_id.map(|u| u.to_string()),
231                tier_to_str(memory.tier),
232                memory.archived_segment,
233                memory.superseded_by.map(|u| u.to_string()),
234                memory.retired_reason,
235                embedding,
236            ],
237        )?;
238        Ok(())
239    }
240
241    pub(crate) fn append_outbox(tx: &Transaction<'_>, memory_id: Uuid, op: &str) -> Result<()> {
242        tx.execute(
243            "INSERT INTO index_outbox (memory_id, op) VALUES (?1, ?2)",
244            params![memory_id.to_string(), op],
245        )?;
246        Ok(())
247    }
248
249    fn tombstoned(tx: &Transaction<'_>, id: Uuid) -> Result<bool> {
250        Self::tombstone_row_exists(tx, &id.to_string())
251    }
252
253    /// String-keyed sibling of `tombstoned`: memory sync records carry
254    /// node_id as a String, and not every value is guaranteed to parse as
255    /// a Uuid, so the import-side idempotency check needs this instead of
256    /// forcing a parse first.
257    fn tombstone_row_exists(tx: &Transaction<'_>, node_id: &str) -> Result<bool> {
258        let hit: Option<i64> = tx
259            .query_row(
260                "SELECT 1 FROM tombstones WHERE node_id = ?1",
261                params![node_id],
262                |r| r.get(0),
263            )
264            .optional()?;
265        Ok(hit.is_some())
266    }
267
268    fn upsert_tombstone(
269        tx: &Transaction<'_>,
270        node_id: &str,
271        node_type: &str,
272        machine_id: &str,
273    ) -> Result<()> {
274        tx.execute(
275            "INSERT INTO tombstones (node_id, node_type, deleted_at, machine_id) VALUES (?1,?2,?3,?4) \
276             ON CONFLICT(node_id) DO UPDATE SET node_type = excluded.node_type, \
277             deleted_at = excluded.deleted_at, machine_id = excluded.machine_id",
278            params![node_id, node_type, Utc::now().to_rfc3339(), machine_id],
279        )?;
280        Ok(())
281    }
282
283    fn insert_distillation(tx: &Transaction<'_>, entry: &Distillation) -> Result<()> {
284        tx.execute(
285            "INSERT OR REPLACE INTO distillation_log \
286             (memory_id, distilled_id, distilled_at, model, status) VALUES (?1,?2,?3,?4,?5)",
287            params![
288                entry.memory_id.to_string(),
289                entry.distilled_id.map(|v| v.to_string()),
290                entry.distilled_at.map(|v| v.to_rfc3339()),
291                entry.model,
292                entry.status,
293            ],
294        )?;
295        Ok(())
296    }
297
298    pub(crate) fn append_sync_log(
299        tx: &Transaction<'_>,
300        op: SyncOp,
301        node_type: SyncNodeType,
302        node_id: &str,
303        data: Option<&str>,
304        origin_machine_id: &str,
305        origin_seq: Option<i64>,
306    ) -> Result<()> {
307        let local_seq: i64 = tx.query_row(
308            "SELECT COALESCE(MAX(local_seq), 0) + 1 FROM sync_log",
309            [],
310            |r| r.get(0),
311        )?;
312        let origin_seq = origin_seq.unwrap_or(local_seq);
313        let id = format!("{origin_machine_id}:{origin_seq}");
314        let duplicate: Option<i64> = tx
315            .query_row("SELECT 1 FROM sync_log WHERE id = ?1", params![id], |r| {
316                r.get(0)
317            })
318            .optional()?;
319        if duplicate.is_some() {
320            return Ok(());
321        }
322        tx.execute(
323            "INSERT INTO sync_log (id, local_seq, origin_machine_id, origin_seq, op, node_type, \
324             node_id, timestamp, data) VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9)",
325            params![
326                id,
327                local_seq,
328                origin_machine_id,
329                origin_seq,
330                sync_op_to_str(op),
331                sync_node_type_to_str(node_type),
332                node_id,
333                Utc::now().to_rfc3339(),
334                data,
335            ],
336        )?;
337        Ok(())
338    }
339}
340
341impl Store for SqliteStore {
342    fn store_memory(&self, memory: &Memory) -> Result<()> {
343        let data = serde_json::to_string(memory).ok();
344        self.with_txn(|tx| {
345            // A tombstoned id stays dead: sync deletes must win over re-ingest
346            // replays, so the write is skipped entirely.
347            if Self::tombstoned(tx, memory.id)? {
348                return Ok(());
349            }
350            Self::insert_memory(tx, memory)?;
351            Self::append_outbox(tx, memory.id, "upsert")?;
352            Self::append_sync_log(
353                tx,
354                SyncOp::Create,
355                SyncNodeType::Memory,
356                &memory.id.to_string(),
357                data.as_deref(),
358                &self.machine_id,
359                None,
360            )
361        })
362    }
363
364    fn get_memory(&self, id: Uuid) -> Result<Option<Memory>> {
365        let conn = self.reader();
366        conn.query_row(
367            &format!("SELECT {MEMORY_COLS} FROM memories WHERE id = ?1"),
368            params![id.to_string()],
369            |r| row_to_memory(r, false),
370        )
371        .optional()
372        .map_err(Into::into)
373    }
374
375    fn get_memory_with_embedding(&self, id: Uuid) -> Result<Option<Memory>> {
376        let conn = self.reader();
377        conn.query_row(
378            &format!("SELECT {MEMORY_COLS}, embedding FROM memories WHERE id = ?1"),
379            params![id.to_string()],
380            |r| row_to_memory(r, true),
381        )
382        .optional()
383        .map_err(Into::into)
384    }
385
386    fn delete_memory(&self, id: Uuid) -> Result<()> {
387        self.remove_memory(id).map(|_| ())
388    }
389
390    fn remove_memory(&self, id: Uuid) -> Result<bool> {
391        let id_string = id.to_string();
392        self.with_txn(|tx| {
393            let removed = tx.execute("DELETE FROM memories WHERE id = ?1", params![id_string])?;
394            // Only a row that was actually here has anything to tombstone,
395            // evict from the indexes, or tell peers about. Re-logging an id
396            // already gone grew sync_log to 32M delete rows against 88k real
397            // deletions, and a forget of an id this machine never held would
398            // otherwise mint a tombstone and a sync entry for nothing.
399            if removed == 0 {
400                return Ok(false);
401            }
402            tx.execute(
403                "DELETE FROM relations WHERE from_id = ?1 OR to_id = ?1",
404                params![id_string],
405            )?;
406            Self::upsert_tombstone(tx, &id_string, "memory", &self.machine_id)?;
407            Self::append_outbox(tx, id, "delete")?;
408            Self::append_sync_log(
409                tx,
410                SyncOp::Delete,
411                SyncNodeType::Memory,
412                &id_string,
413                None,
414                &self.machine_id,
415                None,
416            )?;
417            Ok(true)
418        })
419    }
420
421    fn store_entity(&self, entity: &Entity) -> Result<()> {
422        let data = serde_json::to_string(entity).ok();
423        self.with_txn(|tx| {
424            let embedding = if entity.embedding.is_empty() {
425                None
426            } else {
427                Some(embedding_to_blob(&entity.embedding))
428            };
429            tx.execute(
430                "INSERT OR REPLACE INTO entities (id, name, entity_type, embedding, aliases) \
431                 VALUES (?1,?2,?3,?4,?5)",
432                params![
433                    entity.id.to_string(),
434                    entity.name,
435                    entity.entity_type,
436                    embedding,
437                    serde_json::to_string(&entity.aliases)?,
438                ],
439            )?;
440            Self::append_sync_log(
441                tx,
442                SyncOp::Create,
443                SyncNodeType::Entity,
444                &entity.id.to_string(),
445                data.as_deref(),
446                &self.machine_id,
447                None,
448            )
449        })
450    }
451
452    fn get_entity(&self, id: Uuid) -> Result<Option<Entity>> {
453        let conn = self.reader();
454        conn.query_row(
455            "SELECT id, name, entity_type, embedding, aliases FROM entities WHERE id = ?1",
456            params![id.to_string()],
457            row_to_entity,
458        )
459        .optional()
460        .map_err(Into::into)
461    }
462
463    fn find_entity_by_name(&self, name: &str) -> Result<Option<Entity>> {
464        let conn = self.reader();
465        conn.query_row(
466            "SELECT id, name, entity_type, embedding, aliases FROM entities WHERE name = ?1 LIMIT 1",
467            params![name],
468            row_to_entity,
469        )
470        .optional()
471        .map_err(Into::into)
472    }
473
474    fn store_conversation(&self, conversation: &Conversation) -> Result<()> {
475        let data = serde_json::to_string(conversation).ok();
476        self.with_txn(|tx| {
477            tx.execute(
478                "INSERT OR REPLACE INTO conversations (id, source, machine_id, started_at, project_path) \
479                 VALUES (?1,?2,?3,?4,?5)",
480                params![
481                    conversation.id.to_string(),
482                    conversation.source,
483                    conversation.machine_id,
484                    conversation.started_at.to_rfc3339(),
485                    conversation.project_path,
486                ],
487            )?;
488            Self::append_sync_log(
489                tx,
490                SyncOp::Create,
491                SyncNodeType::Conversation,
492                &conversation.id.to_string(),
493                data.as_deref(),
494                &self.machine_id,
495                None,
496            )
497        })
498    }
499
500    fn store_relation(&self, relation: &Relation) -> Result<()> {
501        let rel_type = relation_type_to_str(relation.relation_type);
502        let node_id = format!("{}:{}:{}", relation.from_id, relation.to_id, rel_type);
503        let data = serde_json::to_string(relation).ok();
504        self.with_txn(|tx| {
505            tx.execute(
506                "INSERT INTO relations (from_id, to_id, relation_type, strength, context, created_at) \
507                 VALUES (?1,?2,?3,?4,?5,?6) \
508                 ON CONFLICT(from_id, to_id, relation_type) DO UPDATE SET \
509                 strength = excluded.strength, context = excluded.context",
510                params![
511                    relation.from_id.to_string(),
512                    relation.to_id.to_string(),
513                    rel_type,
514                    relation.strength as f64,
515                    relation.context,
516                    Utc::now().to_rfc3339(),
517                ],
518            )?;
519            Self::append_sync_log(
520                tx,
521                SyncOp::Create,
522                SyncNodeType::Relation,
523                &node_id,
524                data.as_deref(),
525                &self.machine_id,
526                None,
527            )
528        })
529    }
530
531    fn get_relations(
532        &self,
533        node_id: Uuid,
534        relation_type: Option<RelationType>,
535    ) -> Result<Vec<Relation>> {
536        let conn = self.reader();
537        let collect = |sql: &str, p: &[&dyn rusqlite::ToSql]| -> Result<Vec<Relation>> {
538            let mut stmt = conn.prepare(sql)?;
539            let rows = stmt.query_map(p, |r| {
540                let to_id = parse_uuid(&r.get::<_, String>(0)?)?;
541                let rt = parse_relation_type(&r.get::<_, String>(1)?)?;
542                // Edge properties are intentionally discarded to match the
543                // Store contract: get_relations always reports strength 1.0
544                // and no context (see the trait doc on all_relations).
545                Ok(Relation {
546                    from_id: node_id,
547                    to_id,
548                    relation_type: rt,
549                    strength: 1.0,
550                    context: None,
551                })
552            })?;
553            Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
554        };
555        match relation_type {
556            Some(rt) => collect(
557                "SELECT to_id, relation_type FROM relations WHERE from_id = ?1 AND relation_type = ?2",
558                &[&node_id.to_string(), &relation_type_to_str(rt)],
559            ),
560            None => collect(
561                "SELECT to_id, relation_type FROM relations WHERE from_id = ?1",
562                &[&node_id.to_string()],
563            ),
564        }
565    }
566
567    fn vector_search(&self, embedding: &[f32], limit: usize) -> Result<Vec<(Memory, f32)>> {
568        let conn = self.reader();
569        let mut stmt = conn.prepare(&format!(
570            "SELECT {MEMORY_COLS}, embedding FROM memories \
571             WHERE embedding IS NOT NULL AND tier = 'hot' AND superseded_by IS NULL"
572        ))?;
573        let rows = stmt.query_map([], |r| row_to_memory(r, true))?;
574        let mut scored: Vec<(Memory, f32)> = Vec::new();
575        for row in rows {
576            let mut memory = row?;
577            let similarity = cosine(embedding, &memory.embedding);
578            memory.embedding = Vec::new();
579            scored.push((memory, similarity));
580        }
581        scored.sort_by(|a, b| b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal));
582        scored.truncate(limit);
583        Ok(scored)
584    }
585
586    fn filtered_vector_search(
587        &self,
588        embedding: &[f32],
589        source: Option<&str>,
590        memory_type: Option<MemoryType>,
591        project_path: Option<&str>,
592        limit: usize,
593    ) -> Result<Vec<(Memory, f32)>> {
594        if limit == 0 {
595            return Ok(Vec::new());
596        }
597        let (predicate, args) = Self::scope_predicate(source, memory_type, project_path);
598        // Score from ids and raw vectors alone, keeping only the running best
599        // `limit`. Peak memory must not grow with the scope: a source pool is
600        // unbounded over time, and this scan reads every row in one, so
601        // collecting per-row state here would make a wide recall's footprint a
602        // function of how long the store has been running.
603        let mut top: Vec<(f32, Uuid)> = Vec::with_capacity(limit + 1);
604        {
605            let conn = self.reader();
606            let mut stmt = conn.prepare(&format!(
607                "SELECT id, embedding FROM memories \
608                 WHERE embedding IS NOT NULL AND tier = 'hot' AND superseded_by IS NULL{predicate}"
609            ))?;
610            let bound: Vec<&dyn rusqlite::ToSql> =
611                args.iter().map(|a| a as &dyn rusqlite::ToSql).collect();
612            let query_norm = embedding.iter().map(|x| x * x).sum::<f32>().sqrt();
613            let mut rows = stmt.query(bound.as_slice())?;
614            let mut floor = f32::NEG_INFINITY;
615            while let Some(row) = rows.next()? {
616                let Some(blob) = row.get_ref(1)?.as_blob_or_null()? else {
617                    continue;
618                };
619                let similarity = cosine_blob(embedding, query_norm, blob);
620                // Read and parse the id only for a row that actually places,
621                // so the common rejection costs no allocation at all.
622                if top.len() == limit && similarity <= floor {
623                    continue;
624                }
625                let id = parse_uuid(row.get_ref(0)?.as_str()?)?;
626                // Place after equal scores, so ties keep scan order exactly as
627                // a stable sort of the whole scan would have left them.
628                let at = top.partition_point(|(s, _)| *s >= similarity);
629                top.insert(at, (similarity, id));
630                top.truncate(limit);
631                if top.len() == limit {
632                    floor = top[limit - 1].0;
633                }
634            }
635        }
636
637        let mut out = Vec::with_capacity(top.len());
638        for (similarity, id) in top {
639            if let Some(memory) = self.get_memory(id)? {
640                out.push((memory, similarity));
641            }
642        }
643        Ok(out)
644    }
645
646    /// Live memories within `depth` hops of `start_id`, newest first, capped
647    /// at `TRAVERSE_MAX_RESULTS`. Ingest writes no memory-to-memory edges
648    /// (only mentions, derived_from and distilled_from), so a hop is any of:
649    /// a memory-to-memory relation in either direction, a shared mentioned
650    /// entity, or a shared conversation.
651    fn traverse(&self, start_id: Uuid, depth: u32) -> Result<Vec<(Memory, Vec<Relation>)>> {
652        let conn = self.reader();
653        let mut stmt = conn.prepare(&format!(
654            "WITH RECURSIVE walk(id, d) AS ( \
655               SELECT ?1, 0 \
656               UNION \
657               SELECT r.to_id, w.d + 1 FROM walk w \
658                 JOIN relations r ON r.from_id = w.id \
659               WHERE w.d < ?2 AND r.relation_type <> 'mentions' \
660               UNION \
661               SELECT r.from_id, w.d + 1 FROM walk w \
662                 JOIN relations r ON r.to_id = w.id \
663               WHERE w.d < ?2 AND r.relation_type <> 'mentions' \
664               UNION \
665               SELECT peer.from_id, w.d + 1 FROM walk w \
666                 JOIN relations own ON own.from_id = w.id AND own.relation_type = 'mentions' \
667                 JOIN relations peer ON peer.to_id = own.to_id AND peer.relation_type = 'mentions' \
668               WHERE w.d < ?2 \
669               UNION \
670               SELECT peer.id, w.d + 1 FROM walk w \
671                 JOIN memories own ON own.id = w.id \
672                 JOIN memories peer ON peer.conversation_id = own.conversation_id \
673               WHERE w.d < ?2 AND own.conversation_id IS NOT NULL \
674               LIMIT ?3 \
675             ) \
676             SELECT {MEMORY_COLS} FROM memories \
677             WHERE id IN (SELECT id FROM walk WHERE d > 0) AND id <> ?1 \
678               AND tier = 'hot' AND superseded_by IS NULL \
679             ORDER BY created_at DESC LIMIT ?4"
680        ))?;
681        let rows = stmt.query_map(
682            params![
683                start_id.to_string(),
684                depth as i64,
685                TRAVERSE_MAX_WALK,
686                TRAVERSE_MAX_RESULTS as i64
687            ],
688            |r| row_to_memory(r, false),
689        )?;
690        let mut results = Vec::new();
691        for row in rows {
692            results.push((row?, Vec::new()));
693        }
694        Ok(results)
695    }
696
697    fn memories_by_source(&self, source: &str) -> Result<Vec<Memory>> {
698        self.memories_where("source = ?1", params![source])
699    }
700
701    fn source_admits(&self, memory: &Memory, source: &str) -> Result<bool> {
702        if memory.source == source {
703            return Ok(true);
704        }
705        // SQLite flattens the subquery, so the outer distilled_id test lands
706        // on idx_distillation_log_distilled instead of materialising every
707        // distillate of the source per candidate.
708        let conn = self.reader();
709        let descends: bool = conn.query_row(
710            &format!(
711                "SELECT EXISTS (SELECT 1 FROM ({}) d WHERE d.distilled_id = ?)",
712                Self::DISTILLED_FROM_SOURCE
713            ),
714            params![source, memory.id.to_string()],
715            |r| r.get(0),
716        )?;
717        Ok(descends)
718    }
719
720    fn memories_by_type(&self, memory_type: MemoryType) -> Result<Vec<Memory>> {
721        self.memories_where("memory_type = ?1", params![memory_type_to_str(memory_type)])
722    }
723
724    fn update_memory(&self, memory: &Memory) -> Result<()> {
725        let data = serde_json::to_string(memory).ok();
726        self.with_txn(|tx| {
727            tx.execute(
728                "UPDATE memories SET confidence = ?2, last_accessed = ?3, access_count = ?4, \
729                 project_path = ?5, updated_at = ?6, machine_id = ?7 WHERE id = ?1",
730                params![
731                    memory.id.to_string(),
732                    memory.confidence as f64,
733                    memory.last_accessed.to_rfc3339(),
734                    memory.access_count as i64,
735                    memory.project_path,
736                    memory.updated_at.to_rfc3339(),
737                    memory.machine_id,
738                ],
739            )?;
740            Self::append_outbox(tx, memory.id, "upsert")?;
741            Self::append_sync_log(
742                tx,
743                SyncOp::Update,
744                SyncNodeType::Memory,
745                &memory.id.to_string(),
746                data.as_deref(),
747                &self.machine_id,
748                None,
749            )
750        })
751    }
752
753    fn record_access(&self, memory: &Memory) -> Result<()> {
754        let conn = self.conn();
755        conn.execute(
756            "UPDATE memories SET last_accessed = ?2, access_count = ?3, confidence = ?4 WHERE id = ?1",
757            params![
758                memory.id.to_string(),
759                memory.last_accessed.to_rfc3339(),
760                memory.access_count as i64,
761                memory.confidence as f64,
762            ],
763        )?;
764        Ok(())
765    }
766
767    fn record_accesses(&self, ids: &[Uuid]) -> Result<()> {
768        if ids.is_empty() {
769            return Ok(());
770        }
771        let now = Utc::now().to_rfc3339();
772        self.with_txn(|tx| {
773            let mut stmt = tx.prepare_cached(
774                "UPDATE memories SET last_accessed = ?2, access_count = access_count + 1 \
775                 WHERE id = ?1",
776            )?;
777            for id in ids {
778                stmt.execute(params![id.to_string(), now])?;
779            }
780            Ok(())
781        })
782    }
783
784    fn bm25_search(&self, query: &str, limit: usize) -> Result<Vec<Memory>> {
785        self.scoped_text_search(query, None, None, None, limit)
786    }
787
788    fn memory_count(&self) -> Result<usize> {
789        let conn = self.reader();
790        let count: i64 = conn.query_row("SELECT COUNT(*) FROM memories", [], |r| r.get(0))?;
791        Ok(count as usize)
792    }
793
794    fn all_memory_ids(&self) -> Result<Vec<Uuid>> {
795        let conn = self.reader();
796        let mut stmt = conn.prepare("SELECT id FROM memories")?;
797        let rows = stmt.query_map([], |r| parse_uuid(&r.get::<_, String>(0)?))?;
798        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
799    }
800
801    fn all_relations(&self) -> Result<Vec<Relation>> {
802        let conn = self.reader();
803        let mut stmt = conn.prepare("SELECT from_id, to_id, relation_type FROM relations")?;
804        let rows = stmt.query_map([], |r| {
805            Ok(Relation {
806                from_id: parse_uuid(&r.get::<_, String>(0)?)?,
807                to_id: parse_uuid(&r.get::<_, String>(1)?)?,
808                relation_type: parse_relation_type(&r.get::<_, String>(2)?)?,
809                strength: 1.0,
810                context: None,
811            })
812        })?;
813        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
814    }
815}
816
817impl SqliteStore {
818    fn memories_where(&self, predicate: &str, p: impl rusqlite::Params) -> Result<Vec<Memory>> {
819        let conn = self.reader();
820        let mut stmt = conn.prepare(&format!(
821            "SELECT {MEMORY_COLS} FROM memories WHERE {predicate}"
822        ))?;
823        let rows = stmt.query_map(p, |r| row_to_memory(r, false))?;
824        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
825    }
826}
827
828pub(crate) fn embedding_to_blob(embedding: &[f32]) -> Vec<u8> {
829    embedding.iter().flat_map(|f| f.to_le_bytes()).collect()
830}
831
832/// Cosine against a stored embedding read straight from its blob, so a wide
833/// scan does not allocate a Vec<f32> per row. `query_norm` is hoisted because
834/// it is constant across the scan.
835fn cosine_blob(query: &[f32], query_norm: f32, blob: &[u8]) -> f32 {
836    let mut dot = 0.0_f32;
837    let mut norm = 0.0_f32;
838    // The norm covers the whole stored vector while the dot product stops at
839    // the shorter of the two, matching `cosine` exactly on a length mismatch.
840    for (i, chunk) in blob.chunks_exact(4).enumerate() {
841        let y = f32::from_le_bytes([chunk[0], chunk[1], chunk[2], chunk[3]]);
842        norm += y * y;
843        if let Some(x) = query.get(i) {
844            dot += x * y;
845        }
846    }
847    let norm = norm.sqrt();
848    if query_norm == 0.0 || norm == 0.0 {
849        return 0.0;
850    }
851    dot / (query_norm * norm)
852}
853
854fn blob_to_embedding(blob: &[u8]) -> Vec<f32> {
855    blob.chunks_exact(4)
856        .map(|c| f32::from_le_bytes([c[0], c[1], c[2], c[3]]))
857        .collect()
858}
859
860fn cosine(a: &[f32], b: &[f32]) -> f32 {
861    let dot: f32 = a.iter().zip(b).map(|(x, y)| x * y).sum();
862    let na: f32 = a.iter().map(|x| x * x).sum::<f32>().sqrt();
863    let nb: f32 = b.iter().map(|x| x * x).sum::<f32>().sqrt();
864    if na == 0.0 || nb == 0.0 {
865        return 0.0;
866    }
867    dot / (na * nb)
868}
869
870fn conversion_err(err: impl std::error::Error + Send + Sync + 'static) -> rusqlite::Error {
871    rusqlite::Error::FromSqlConversionFailure(0, rusqlite::types::Type::Text, Box::new(err))
872}
873
874fn parse_dt(s: &str) -> rusqlite::Result<DateTime<Utc>> {
875    DateTime::parse_from_rfc3339(s)
876        .map(|d| d.with_timezone(&Utc))
877        .map_err(conversion_err)
878}
879
880pub(crate) fn parse_uuid(s: &str) -> rusqlite::Result<Uuid> {
881    Uuid::parse_str(s).map_err(conversion_err)
882}
883
884fn memory_type_to_str(t: MemoryType) -> &'static str {
885    match t {
886        MemoryType::Episodic => "episodic",
887        MemoryType::Semantic => "semantic",
888        MemoryType::Procedural => "procedural",
889        MemoryType::Decision => "decision",
890        MemoryType::Architecture => "architecture",
891        MemoryType::Debugging => "debugging",
892        MemoryType::Task => "task",
893        MemoryType::Question => "question",
894    }
895}
896
897fn parse_memory_type(s: &str) -> rusqlite::Result<MemoryType> {
898    match s {
899        "episodic" => Ok(MemoryType::Episodic),
900        "semantic" => Ok(MemoryType::Semantic),
901        "procedural" => Ok(MemoryType::Procedural),
902        "decision" => Ok(MemoryType::Decision),
903        "architecture" => Ok(MemoryType::Architecture),
904        "debugging" => Ok(MemoryType::Debugging),
905        "task" => Ok(MemoryType::Task),
906        "question" => Ok(MemoryType::Question),
907        other => Err(conversion_err(std::io::Error::other(format!(
908            "unknown memory_type: {other}"
909        )))),
910    }
911}
912
913fn relation_type_to_str(t: RelationType) -> &'static str {
914    match t {
915        RelationType::RelatesTo => "relates_to",
916        RelationType::Mentions => "mentions",
917        RelationType::DerivedFrom => "derived_from",
918        RelationType::Contradicts => "contradicts",
919        RelationType::Reinforces => "reinforces",
920        RelationType::Supersedes => "supersedes",
921        RelationType::DistilledFrom => "distilled_from",
922    }
923}
924
925fn parse_relation_type(s: &str) -> rusqlite::Result<RelationType> {
926    match s {
927        "relates_to" => Ok(RelationType::RelatesTo),
928        "mentions" => Ok(RelationType::Mentions),
929        "derived_from" => Ok(RelationType::DerivedFrom),
930        "contradicts" => Ok(RelationType::Contradicts),
931        "reinforces" => Ok(RelationType::Reinforces),
932        "supersedes" => Ok(RelationType::Supersedes),
933        "distilled_from" => Ok(RelationType::DistilledFrom),
934        other => Err(conversion_err(std::io::Error::other(format!(
935            "unknown relation_type: {other}"
936        )))),
937    }
938}
939
940fn tier_to_str(t: Tier) -> &'static str {
941    match t {
942        Tier::Hot => "hot",
943        Tier::Archived => "archived",
944    }
945}
946
947fn parse_tier(s: &str) -> rusqlite::Result<Tier> {
948    match s {
949        "hot" => Ok(Tier::Hot),
950        "archived" => Ok(Tier::Archived),
951        other => Err(conversion_err(std::io::Error::other(format!(
952            "unknown tier: {other}"
953        )))),
954    }
955}
956
957fn sync_op_to_str(op: SyncOp) -> &'static str {
958    match op {
959        SyncOp::Create => "create",
960        SyncOp::Update => "update",
961        SyncOp::Delete => "delete",
962    }
963}
964
965fn sync_node_type_to_str(t: SyncNodeType) -> &'static str {
966    match t {
967        SyncNodeType::Memory => "memory",
968        SyncNodeType::Entity => "entity",
969        SyncNodeType::Conversation => "conversation",
970        SyncNodeType::Relation => "relation",
971        SyncNodeType::Distillation => "distillation",
972    }
973}
974
975fn row_to_memory(r: &Row<'_>, with_embedding: bool) -> rusqlite::Result<Memory> {
976    let embedding = if with_embedding {
977        r.get::<_, Option<Vec<u8>>>(18)?
978            .map(|b| blob_to_embedding(&b))
979            .unwrap_or_default()
980    } else {
981        Vec::new()
982    };
983    Ok(Memory {
984        id: parse_uuid(&r.get::<_, String>(0)?)?,
985        content: r.get(1)?,
986        context: r.get(2)?,
987        embedding,
988        memory_type: parse_memory_type(&r.get::<_, String>(3)?)?,
989        confidence: r.get::<_, f64>(4)? as f32,
990        created_at: parse_dt(&r.get::<_, String>(5)?)?,
991        updated_at: parse_dt(&r.get::<_, String>(6)?)?,
992        last_accessed: parse_dt(&r.get::<_, String>(7)?)?,
993        access_count: r.get::<_, i64>(8)? as u32,
994        source: r.get(9)?,
995        source_id: r.get(10)?,
996        project_path: r.get(11)?,
997        machine_id: r.get(12)?,
998        conversation_id: r
999            .get::<_, Option<String>>(13)?
1000            .map(|s| parse_uuid(&s))
1001            .transpose()?,
1002        tier: parse_tier(&r.get::<_, String>(14)?)?,
1003        archived_segment: r.get(15)?,
1004        superseded_by: r
1005            .get::<_, Option<String>>(16)?
1006            .map(|s| parse_uuid(&s))
1007            .transpose()?,
1008        retired_reason: r.get(17)?,
1009    })
1010}
1011
1012fn row_to_entity(r: &Row<'_>) -> rusqlite::Result<Entity> {
1013    Ok(Entity {
1014        id: parse_uuid(&r.get::<_, String>(0)?)?,
1015        name: r.get(1)?,
1016        entity_type: r.get(2)?,
1017        embedding: r
1018            .get::<_, Option<Vec<u8>>>(3)?
1019            .map(|b| blob_to_embedding(&b))
1020            .unwrap_or_default(),
1021        aliases: serde_json::from_str(&r.get::<_, String>(4)?).map_err(conversion_err)?,
1022    })
1023}
1024
1025/// Store-side shape the index drainer consumes; `body` is context + content,
1026/// the same text the embedding was computed over.
1027#[derive(Debug, Clone, Copy, PartialEq, Eq)]
1028pub enum RetireOutcome {
1029    Retired,
1030    /// The row was already archived, by an earlier forget or by
1031    /// consolidation; it is left as it was.
1032    AlreadyRetired,
1033    NotFound,
1034}
1035
1036pub struct IndexableMemory {
1037    pub embedding: Vec<f32>,
1038    pub body: String,
1039    pub hot: bool,
1040}
1041
1042pub struct OutboxRow {
1043    pub seq: i64,
1044    pub memory_id: Uuid,
1045    pub op: String,
1046}
1047
1048pub(crate) fn body_of(context: Option<String>, content: String) -> String {
1049    match context {
1050        Some(c) if !c.is_empty() => format!("{c}\n{content}"),
1051        _ => content,
1052    }
1053}
1054
1055impl SqliteStore {
1056    pub fn index_watermark(&self) -> Result<i64> {
1057        let conn = self.reader();
1058        let value: Option<String> = conn
1059            .query_row(
1060                "SELECT value FROM index_meta WHERE key = 'watermark'",
1061                [],
1062                |r| r.get(0),
1063            )
1064            .optional()?;
1065        Ok(value.and_then(|v| v.parse().ok()).unwrap_or(0))
1066    }
1067
1068    pub fn set_index_watermark(&self, seq: i64) -> Result<()> {
1069        let conn = self.conn();
1070        conn.execute(
1071            "INSERT OR REPLACE INTO index_meta (key, value) VALUES ('watermark', ?1)",
1072            params![seq.to_string()],
1073        )?;
1074        Ok(())
1075    }
1076
1077    pub fn outbox_after(&self, seq: i64) -> Result<Vec<OutboxRow>> {
1078        let conn = self.reader();
1079        let mut stmt = conn
1080            .prepare("SELECT seq, memory_id, op FROM index_outbox WHERE seq > ?1 ORDER BY seq")?;
1081        let rows = stmt.query_map(params![seq], |r| {
1082            Ok(OutboxRow {
1083                seq: r.get(0)?,
1084                memory_id: parse_uuid(&r.get::<_, String>(1)?)?,
1085                op: r.get(2)?,
1086            })
1087        })?;
1088        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
1089    }
1090
1091    pub fn prune_outbox(&self, upto: i64) -> Result<()> {
1092        let conn = self.conn();
1093        conn.execute("DELETE FROM index_outbox WHERE seq <= ?1", params![upto])?;
1094        Ok(())
1095    }
1096
1097    pub fn max_outbox_seq(&self) -> Result<i64> {
1098        let conn = self.reader();
1099        Ok(
1100            conn.query_row("SELECT COALESCE(MAX(seq), 0) FROM index_outbox", [], |r| {
1101                r.get(0)
1102            })?,
1103        )
1104    }
1105
1106    /// Stable u64 usearch key for a memory id, allocated on first use.
1107    pub fn vec_key(&self, memory_id: Uuid) -> Result<u64> {
1108        let conn = self.conn();
1109        conn.execute(
1110            "INSERT OR IGNORE INTO vec_keys (memory_id) VALUES (?1)",
1111            params![memory_id.to_string()],
1112        )?;
1113        let key: i64 = conn.query_row(
1114            "SELECT key FROM vec_keys WHERE memory_id = ?1",
1115            params![memory_id.to_string()],
1116            |r| r.get(0),
1117        )?;
1118        Ok(key as u64)
1119    }
1120
1121    pub fn existing_vec_key(&self, memory_id: Uuid) -> Result<Option<u64>> {
1122        let conn = self.reader();
1123        let key: Option<i64> = conn
1124            .query_row(
1125                "SELECT key FROM vec_keys WHERE memory_id = ?1",
1126                params![memory_id.to_string()],
1127                |r| r.get(0),
1128            )
1129            .optional()?;
1130        Ok(key.map(|k| k as u64))
1131    }
1132
1133    /// Drops the key row once the vector is gone from usearch, so `vec_keys`
1134    /// only ever holds keys the index can still resolve.
1135    pub fn delete_vec_key(&self, memory_id: Uuid) -> Result<()> {
1136        let conn = self.conn();
1137        conn.execute(
1138            "DELETE FROM vec_keys WHERE memory_id = ?1",
1139            params![memory_id.to_string()],
1140        )?;
1141        Ok(())
1142    }
1143
1144    /// The live memory `content` duplicates under `remember`'s rule: equal
1145    /// once surrounding whitespace is trimmed and both sides are lowercased.
1146    /// With `project_path`, only memories filed under that project count;
1147    /// among several matches the oldest wins. SQLite's `lower()` folds ASCII
1148    /// only, so a stored memory that differs from `content` just in the case
1149    /// of a non-ASCII letter is not found, which the substring shortlist this
1150    /// replaces could not find either.
1151    pub fn find_exact_duplicate(
1152        &self,
1153        content: &str,
1154        project_path: Option<&str>,
1155    ) -> Result<Option<Memory>> {
1156        let key = content.trim().to_lowercase();
1157        let conn = self.reader();
1158        let mut stmt = conn.prepare(&dedup_query(project_path.is_some()))?;
1159        let found = match project_path {
1160            Some(p) => stmt.query_row(params![key, p], |r| row_to_memory(r, false)),
1161            None => stmt.query_row(params![key], |r| row_to_memory(r, false)),
1162        }
1163        .optional()?;
1164        Ok(found)
1165    }
1166
1167    /// SQL fragment and bindings for a source/type/project scope, appended to
1168    /// an existing WHERE clause. All three columns are indexed, so a scoped
1169    /// scan reads only the matching rows.
1170    /// Ids of distillates that descend from at least one raw of the bound
1171    /// source. `source_admits` and `scope_predicate` must agree, so both
1172    /// are built from this one fragment.
1173    const DISTILLED_FROM_SOURCE: &str = "SELECT dl.distilled_id FROM distillation_log dl          JOIN memories raw ON raw.id = dl.memory_id WHERE raw.source = ?";
1174
1175    fn scope_predicate(
1176        source: Option<&str>,
1177        memory_type: Option<MemoryType>,
1178        project_path: Option<&str>,
1179    ) -> (String, Vec<String>) {
1180        let mut predicate = String::new();
1181        let mut args = Vec::new();
1182        if let Some(s) = source {
1183            // The IN-subquery form lets SQLite serve both arms from indexes
1184            // (idx_memories_source and the primary key) instead of testing a
1185            // correlated EXISTS against every row of the scan.
1186            predicate.push_str(&format!(
1187                " AND (source = ? OR id IN ({}))",
1188                Self::DISTILLED_FROM_SOURCE
1189            ));
1190            args.push(s.to_string());
1191            args.push(s.to_string());
1192        }
1193        if let Some(t) = memory_type {
1194            predicate.push_str(" AND memory_type = ?");
1195            args.push(memory_type_to_str(t).to_string());
1196        }
1197        if let Some(p) = project_path {
1198            predicate.push_str(" AND project_path = ?");
1199            args.push(p.to_string());
1200        }
1201        (predicate, args)
1202    }
1203
1204    /// `bm25_search`'s match with a source/type/project scope inside the
1205    /// query, so `limit` counts only rows the scope admits instead of cutting
1206    /// before a caller's filter could run. The match is a substring test over
1207    /// `content` on hot, unsuperseded rows, with no relevance score to order
1208    /// by (tantivy, not SQLite, holds the text index), so matches come back
1209    /// newest first. Case folding is SQLite's `lower()`, which is ASCII only.
1210    pub fn scoped_text_search(
1211        &self,
1212        query: &str,
1213        source: Option<&str>,
1214        memory_type: Option<MemoryType>,
1215        project_path: Option<&str>,
1216        limit: usize,
1217    ) -> Result<Vec<Memory>> {
1218        let (predicate, args) = Self::scope_predicate(source, memory_type, project_path);
1219        let needle = query.to_lowercase();
1220        let limit = limit as i64;
1221        let bound: Vec<&dyn rusqlite::ToSql> = std::iter::once(&needle as &dyn rusqlite::ToSql)
1222            .chain(args.iter().map(|a| a as &dyn rusqlite::ToSql))
1223            .chain(std::iter::once(&limit as &dyn rusqlite::ToSql))
1224            .collect();
1225        let conn = self.reader();
1226        let mut stmt = conn.prepare(&format!(
1227            "SELECT {MEMORY_COLS} FROM memories \
1228             WHERE instr(lower(content), ?) > 0 AND tier = 'hot' AND superseded_by IS NULL\
1229             {predicate} ORDER BY created_at DESC LIMIT ?"
1230        ))?;
1231        let rows = stmt.query_map(bound.as_slice(), |r| row_to_memory(r, false))?;
1232        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
1233    }
1234
1235    /// How many live memories a source/type/project scope holds. Answered by
1236    /// COUNT off the source, memory_type, and project_path indexes, so it
1237    /// stays O(1) in memory however large the scope grows.
1238    pub fn scoped_count(
1239        &self,
1240        source: Option<&str>,
1241        memory_type: Option<MemoryType>,
1242        project_path: Option<&str>,
1243    ) -> Result<usize> {
1244        let (predicate, args) = Self::scope_predicate(source, memory_type, project_path);
1245        let conn = self.reader();
1246        let bound: Vec<&dyn rusqlite::ToSql> =
1247            args.iter().map(|a| a as &dyn rusqlite::ToSql).collect();
1248        let count: i64 = conn.query_row(
1249            &format!(
1250                "SELECT COUNT(*) FROM memories \
1251                 WHERE tier = 'hot' AND superseded_by IS NULL{predicate}"
1252            ),
1253            bound.as_slice(),
1254            |r| r.get(0),
1255        )?;
1256        Ok(count as usize)
1257    }
1258
1259    /// Which of `ids` fall inside a source/type/project scope.
1260    ///
1261    /// Asks the database about the candidates in hand rather than reading the
1262    /// scope out to test membership locally, so the cost tracks the caller's
1263    /// list and not the scope. Batched because SQLite caps how many bound
1264    /// parameters one statement may carry.
1265    pub fn retain_in_scope(
1266        &self,
1267        ids: &[Uuid],
1268        source: Option<&str>,
1269        memory_type: Option<MemoryType>,
1270        project_path: Option<&str>,
1271    ) -> Result<HashSet<Uuid>> {
1272        const BATCH: usize = 400;
1273        let (predicate, args) = Self::scope_predicate(source, memory_type, project_path);
1274        let mut kept = HashSet::new();
1275        let conn = self.reader();
1276        for chunk in ids.chunks(BATCH) {
1277            let holes = std::iter::repeat_n("?", chunk.len())
1278                .collect::<Vec<_>>()
1279                .join(",");
1280            let mut stmt = conn.prepare(&format!(
1281                "SELECT id FROM memories \
1282                 WHERE tier = 'hot' AND superseded_by IS NULL{predicate} AND id IN ({holes})"
1283            ))?;
1284            let texts: Vec<String> = chunk.iter().map(|id| id.to_string()).collect();
1285            let bound: Vec<&dyn rusqlite::ToSql> = args
1286                .iter()
1287                .map(|a| a as &dyn rusqlite::ToSql)
1288                .chain(texts.iter().map(|t| t as &dyn rusqlite::ToSql))
1289                .collect();
1290            let rows = stmt.query_map(bound.as_slice(), |r| parse_uuid(&r.get::<_, String>(0)?))?;
1291            for id in rows {
1292                kept.insert(id?);
1293            }
1294        }
1295        Ok(kept)
1296    }
1297
1298    pub fn memory_id_for_vec_key(&self, key: u64) -> Result<Option<Uuid>> {
1299        let conn = self.reader();
1300        let id: Option<String> = conn
1301            .query_row(
1302                "SELECT memory_id FROM vec_keys WHERE key = ?1",
1303                params![key as i64],
1304                |r| r.get(0),
1305            )
1306            .optional()?;
1307        Ok(id.as_deref().map(Uuid::parse_str).transpose()?)
1308    }
1309
1310    pub fn memory_for_indexing(&self, memory_id: Uuid) -> Result<Option<IndexableMemory>> {
1311        let conn = self.reader();
1312        conn.query_row(
1313            "SELECT content, context, embedding, tier, superseded_by FROM memories WHERE id = ?1",
1314            params![memory_id.to_string()],
1315            |r| {
1316                let content: String = r.get(0)?;
1317                let context: Option<String> = r.get(1)?;
1318                let embedding = r
1319                    .get::<_, Option<Vec<u8>>>(2)?
1320                    .map(|b| blob_to_embedding(&b))
1321                    .unwrap_or_default();
1322                let tier: String = r.get(3)?;
1323                let superseded_by: Option<String> = r.get(4)?;
1324                Ok(IndexableMemory {
1325                    embedding,
1326                    body: body_of(context, content),
1327                    hot: tier == "hot" && superseded_by.is_none(),
1328                })
1329            },
1330        )
1331        .optional()
1332        .map_err(Into::into)
1333    }
1334
1335    pub fn all_indexable(&self) -> Result<Vec<(Uuid, Vec<f32>, String)>> {
1336        let conn = self.reader();
1337        let mut stmt = conn.prepare(
1338            "SELECT id, content, context, embedding FROM memories \
1339             WHERE tier = 'hot' AND superseded_by IS NULL",
1340        )?;
1341        let rows = stmt.query_map([], |r| {
1342            let id = parse_uuid(&r.get::<_, String>(0)?)?;
1343            let content: String = r.get(1)?;
1344            let context: Option<String> = r.get(2)?;
1345            let embedding = r
1346                .get::<_, Option<Vec<u8>>>(3)?
1347                .map(|b| blob_to_embedding(&b))
1348                .unwrap_or_default();
1349            Ok((id, embedding, body_of(context, content)))
1350        })?;
1351        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
1352    }
1353
1354    /// Hot rows whose stored embedding has exactly `dim` components: the
1355    /// only rows the vector index can hold, so a row still waiting for an
1356    /// embedding does not read as index drift.
1357    pub fn vector_indexable_count(&self, dim: usize) -> Result<usize> {
1358        let conn = self.reader();
1359        let count: i64 = conn.query_row(
1360            "SELECT COUNT(*) FROM memories WHERE tier = 'hot' AND superseded_by IS NULL \
1361             AND embedding IS NOT NULL AND length(embedding) = ?1",
1362            params![(dim * 4) as i64],
1363            |r| r.get(0),
1364        )?;
1365        Ok(count as usize)
1366    }
1367
1368    pub fn indexable_count(&self) -> Result<usize> {
1369        let conn = self.reader();
1370        let count: i64 = conn.query_row(
1371            "SELECT COUNT(*) FROM memories WHERE tier = 'hot' AND superseded_by IS NULL",
1372            [],
1373            |r| r.get(0),
1374        )?;
1375        Ok(count as usize)
1376    }
1377
1378    pub(crate) fn memory_on_tx(tx: &Transaction<'_>, id: Uuid) -> Result<Option<Memory>> {
1379        tx.query_row(
1380            &format!("SELECT {MEMORY_COLS}, embedding FROM memories WHERE id = ?1"),
1381            params![id.to_string()],
1382            |r| row_to_memory(r, true),
1383        )
1384        .optional()
1385        .map_err(Into::into)
1386    }
1387
1388    /// Logs the row's full current state (embedding included) as a sync
1389    /// Update. The peer's Update path replaces the whole row, so a record
1390    /// serialized without the embedding would null it there.
1391    pub(crate) fn log_memory_update(&self, tx: &Transaction<'_>, memory: &Memory) -> Result<()> {
1392        let data = serde_json::to_string(memory)?;
1393        Self::append_sync_log(
1394            tx,
1395            SyncOp::Update,
1396            SyncNodeType::Memory,
1397            &memory.id.to_string(),
1398            Some(&data),
1399            &self.machine_id,
1400            None,
1401        )
1402    }
1403
1404    /// Zep-style soft invalidation: the row stays, the indexes drop it via the
1405    /// outbox, and peers learn of it through a sync Update carrying the row.
1406    pub fn supersede_memory(&self, memory_id: Uuid, by: Uuid) -> Result<()> {
1407        self.with_txn(|tx| self.supersede_on_tx(tx, memory_id, by).map(|_| ()))
1408    }
1409
1410    pub(crate) fn supersede_on_tx(
1411        &self,
1412        tx: &Transaction<'_>,
1413        memory_id: Uuid,
1414        by: Uuid,
1415    ) -> Result<bool> {
1416        let changed = tx.execute(
1417            "UPDATE memories SET superseded_by = ?2, updated_at = ?3 \
1418             WHERE id = ?1 AND superseded_by IS NULL",
1419            params![
1420                memory_id.to_string(),
1421                by.to_string(),
1422                Utc::now().to_rfc3339()
1423            ],
1424        )?;
1425        if changed == 0 {
1426            return Ok(false);
1427        }
1428        Self::append_outbox(tx, memory_id, "upsert")?;
1429        if let Some(memory) = Self::memory_on_tx(tx, memory_id)? {
1430            self.log_memory_update(tx, &memory)?;
1431        }
1432        Ok(true)
1433    }
1434
1435    /// Local cold tier: the row keeps its content and embedding but leaves
1436    /// both retrieval indexes and every recall predicate. `archived_segment`
1437    /// stays NULL, which is what distinguishes this from `mark_archived`'s
1438    /// uploaded-segment stub. Replicated as an Update so peers retire the
1439    /// same row instead of keeping it hot beside the distillate.
1440    pub fn archive_memory(&self, memory_id: Uuid) -> Result<()> {
1441        self.with_txn(|tx| self.archive_on_tx(tx, memory_id).map(|_| ()))
1442    }
1443
1444    /// Returns whether a row was actually flipped, so a batch caller can
1445    /// count real retirements rather than attempts.
1446    pub(crate) fn archive_on_tx(&self, tx: &Transaction<'_>, memory_id: Uuid) -> Result<bool> {
1447        let changed = tx.execute(
1448            "UPDATE memories SET tier = 'archived', updated_at = ?2 \
1449             WHERE id = ?1 AND tier <> 'archived'",
1450            params![memory_id.to_string(), Utc::now().to_rfc3339()],
1451        )?;
1452        if changed == 0 {
1453            return Ok(false);
1454        }
1455        Self::append_outbox(tx, memory_id, "delete")?;
1456        if let Some(memory) = Self::memory_on_tx(tx, memory_id)? {
1457            self.log_memory_update(tx, &memory)?;
1458        }
1459        Ok(true)
1460    }
1461
1462    /// `forget`'s default: archives the row as `archive_memory` does and
1463    /// records why, so the memory leaves recall on every peer while its
1464    /// content stays recoverable. Nothing is tombstoned, and an id this store
1465    /// does not hold changes nothing.
1466    pub fn retire_memory(&self, memory_id: Uuid, reason: &str) -> Result<RetireOutcome> {
1467        self.with_txn(|tx| {
1468            let changed = tx.execute(
1469                "UPDATE memories SET tier = 'archived', retired_reason = ?2, updated_at = ?3 \
1470                 WHERE id = ?1 AND tier <> 'archived'",
1471                params![memory_id.to_string(), reason, Utc::now().to_rfc3339()],
1472            )?;
1473            if changed == 0 {
1474                return Ok(match Self::memory_on_tx(tx, memory_id)? {
1475                    Some(_) => RetireOutcome::AlreadyRetired,
1476                    None => RetireOutcome::NotFound,
1477                });
1478            }
1479            Self::append_outbox(tx, memory_id, "delete")?;
1480            if let Some(memory) = Self::memory_on_tx(tx, memory_id)? {
1481                self.log_memory_update(tx, &memory)?;
1482            }
1483            Ok(RetireOutcome::Retired)
1484        })
1485    }
1486
1487    /// Cold-tier flip: content becomes a 200-char stub, the embedding is
1488    /// dropped (the full row lives in the archived segment), and the outbox
1489    /// evicts the memory from both indexes.
1490    pub fn mark_archived(&self, memory_id: Uuid, segment: &str) -> Result<()> {
1491        self.with_txn(|tx| {
1492            tx.execute(
1493                "UPDATE memories SET tier = 'archived', archived_segment = ?2, \
1494                 embedding = NULL, content = substr(content, 1, 200) WHERE id = ?1",
1495                params![memory_id.to_string(), segment],
1496            )?;
1497            Self::append_outbox(tx, memory_id, "delete")
1498        })
1499    }
1500}
1501
1502#[cfg(test)]
1503mod tests {
1504    use super::*;
1505
1506    fn plan(store: &SqliteStore, with_project: bool) -> Vec<String> {
1507        let conn = store.reader();
1508        let mut stmt = conn
1509            .prepare(&format!("EXPLAIN QUERY PLAN {}", dedup_query(with_project)))
1510            .unwrap();
1511        let detail = |r: &Row<'_>| r.get::<_, String>(3);
1512        let rows = if with_project {
1513            stmt.query_map(params!["k", "/p"], detail)
1514                .unwrap()
1515                .collect::<rusqlite::Result<Vec<_>>>()
1516        } else {
1517            stmt.query_map(params!["k"], detail)
1518                .unwrap()
1519                .collect::<rusqlite::Result<Vec<_>>>()
1520        };
1521        rows.unwrap()
1522    }
1523
1524    #[test]
1525    fn the_dedup_lookup_reads_the_dedup_key_index() {
1526        let store = SqliteStore::in_memory("m".to_string()).unwrap();
1527        for with_project in [false, true] {
1528            let plan = plan(&store, with_project);
1529            assert!(
1530                plan.iter().any(|d| d.contains("idx_memories_dedup_key")),
1531                "with_project={with_project}: {plan:?}"
1532            );
1533        }
1534    }
1535
1536    #[test]
1537    fn the_dedup_trim_set_is_exactly_what_str_trim_strips() {
1538        let listed: Vec<u32> = DEDUP_TRIM_SET
1539            .trim_start_matches("char(")
1540            .trim_end_matches(')')
1541            .split(", ")
1542            .map(|n| n.parse().unwrap())
1543            .collect();
1544        let rust: Vec<u32> = (0..=char::MAX as u32)
1545            .filter_map(char::from_u32)
1546            .filter(|c| c.is_whitespace())
1547            .map(u32::from)
1548            .collect();
1549        assert_eq!(listed, rust);
1550    }
1551}