Skip to main content

second_brain_core/sqlite_store/
mod.rs

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