Skip to main content

second_brain_core/sqlite_store/
mod.rs

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