Skip to main content

second_brain_core/sqlite_store/
mod.rs

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