Skip to main content

second_brain_core/sqlite_store/
mod.rs

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