Skip to main content

second_brain_core/sqlite_store/
mod.rs

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