Skip to main content

second_brain_core/
maintenance.rs

1//! Store invariants, measured and repaired in place.
2//!
3//! Every past cleanup was a one-off data fix plus a CLI command nobody ran
4//! again, so the same drift came back within weeks. `integrity_report`
5//! measures each invariant with one query; `repair` restores them in
6//! idempotent, separately committed steps that never hard delete a memory.
7//! The daemon runs both on a schedule from the thread that owns the writable
8//! indexes, so a repair always has exclusive index write access.
9
10use std::collections::HashSet;
11
12use anyhow::Result;
13use chrono::{DateTime, Utc};
14use rusqlite::{OptionalExtension, params};
15use serde::{Deserialize, Serialize};
16use uuid::Uuid;
17
18use crate::cold::{stub_lead_sql, stub_matches};
19use crate::index::{EMBEDDING_DIM, Indexes};
20use crate::schema::distillable_source;
21use crate::sqlite_store::{SqliteStore, body_of, embedding_to_blob, parse_uuid, sync_dedup};
22use crate::store::StoreAdmin;
23
24const HOT: &str = "tier = 'hot' AND superseded_by IS NULL";
25/// Vault notes stay out of dedup: the vault sync keeps one live row per
26/// note file, and two identical files collapsed onto one row would lose
27/// the survivor's content from recall once the other file is deleted.
28const DEDUP_CANDIDATE: &str = "tier = 'hot' AND superseded_by IS NULL AND source <> 'markdown'";
29
30/// Relations whose endpoints no longer resolve. Must stay identical to the
31/// predicate `prune_dangling_relations` deletes by, so the report and the
32/// repair agree on what dangling means.
33const DANGLING_RELATIONS: &str = "from_id NOT IN (SELECT id FROM memories) \
34     OR (relation_type = 'mentions' AND to_id NOT IN (SELECT id FROM entities)) \
35     OR (relation_type = 'derived_from' AND to_id NOT IN (SELECT id FROM conversations)) \
36     OR (relation_type NOT IN ('mentions', 'derived_from') \
37         AND to_id NOT IN (SELECT id FROM memories))";
38
39/// A `vec_keys` row with no live vector behind it: the memory is gone,
40/// retired, superseded, or never had an embedding to index.
41const DEAD_VEC_KEYS: &str = "SELECT COUNT(*) FROM vec_keys vk \
42     LEFT JOIN memories m ON m.id = vk.memory_id \
43     WHERE m.id IS NULL OR m.tier <> 'hot' OR m.superseded_by IS NOT NULL \
44        OR m.embedding IS NULL";
45
46/// Sync log rows no peer can ever need again, and which a peer at any cursor
47/// position converges without. A memory create or update that a later
48/// delete for the same id supersedes: a peer behind the create receives the
49/// delete and tombstones an id it never held; a peer between them receives
50/// the delete. A relation create whose endpoint memory is tombstoned: the
51/// import side already skips it as a dangling endpoint.
52const RECLAIMABLE_SYNC_LOG: &str = "SELECT s.local_seq FROM sync_log s \
53     WHERE ((s.node_type = 'memory' AND s.op IN ('create', 'update') \
54             AND EXISTS (SELECT 1 FROM sync_log d \
55                         WHERE d.node_type = 'memory' AND d.node_id = s.node_id \
56                           AND d.op = 'delete' AND d.local_seq > s.local_seq)) \
57         OR (s.node_type = 'relation' AND s.op = 'create' \
58             AND (substr(s.node_id, 1, 36) IN (SELECT node_id FROM tombstones) \
59                  OR substr(s.node_id, 38, 36) IN (SELECT node_id FROM tombstones))))";
60
61/// A distillation marker on a hot raw whose distillate was deleted. The
62/// marker hides the raw from consolidation while nothing distilled remains,
63/// so the knowledge is stuck in raw form forever. Only a tombstoned
64/// distillate counts: on a peer the marker routinely arrives before the
65/// distillate memory does, and dropping the marker then would make the
66/// peer redistill, which is exactly the waste the marker exists to stop.
67const ORPHANED_MARKERS: &str = "SELECT dl.memory_id FROM distillation_log dl \
68     JOIN memories m ON m.id = dl.memory_id \
69     WHERE m.tier = 'hot' AND m.superseded_by IS NULL \
70       AND dl.memory_id <> dl.distilled_id \
71       AND NOT EXISTS (SELECT 1 FROM memories d WHERE d.id = dl.distilled_id) \
72       AND dl.distilled_id IN (SELECT node_id FROM tombstones)";
73
74macro_rules! inheriting_keeper {
75    () => {
76        concat!(
77            "k.tier = 'hot' AND k.superseded_by IS NULL AND ",
78            distillable_source!("k.source"),
79            " AND k.id NOT IN (SELECT memory_id FROM distillation_log) \
80               AND (dl.memory_id = dl.distilled_id \
81                    OR EXISTS (SELECT 1 FROM memories d WHERE d.id = dl.distilled_id))"
82        )
83    };
84}
85
86/// Hot raws with no marker whose exact text was already distilled or
87/// skipped under another id (a duplicate, whatever its tier now). Without
88/// the marker the daily job distills the same text again and mints a
89/// second distillate. Grouped by keeper so each inherits one marker.
90const INHERITABLE_MARKERS: &str = concat!(
91    "SELECT k.id, dl.distilled_id, dl.model, \
92       dl.memory_id = dl.distilled_id AS skip \
93     FROM memories k \
94     JOIN memories t ON t.content_prefix = k.content_prefix AND t.content = k.content \
95                    AND t.id <> k.id \
96     JOIN distillation_log dl ON dl.memory_id = t.id \
97     WHERE ",
98    inheriting_keeper!(),
99    " GROUP BY k.id"
100);
101
102/// `INHERITABLE_MARKERS` for a twin in the cold tier, whose row holds a
103/// stub: candidates share the stub's leading text, and the caller keeps
104/// those whose whole text matches the stub's digest.
105fn inheritable_cold_markers() -> String {
106    format!(
107        "SELECT k.id, dl.distilled_id, dl.model, dl.memory_id = dl.distilled_id AS skip, \
108           CAST(k.content AS BLOB), t.content \
109         FROM distillation_log dl \
110         JOIN memories t ON t.id = dl.memory_id AND t.archived_segment IS NOT NULL \
111         JOIN memories k ON k.content_prefix = {lead} AND k.id <> t.id \
112         WHERE {keeper}",
113        lead = stub_lead_sql("t.content"),
114        keeper = inheriting_keeper!(),
115    )
116}
117
118#[derive(Debug, Clone, Default, Serialize, Deserialize)]
119pub struct IntegrityReport {
120    pub memories_total: u64,
121    pub hot: u64,
122    pub archived: u64,
123    pub superseded: u64,
124    pub distilled_live: u64,
125    pub raw_pending: u64,
126    pub raw_retired: u64,
127    pub raw_retire_pending: u64,
128    /// Hot, unsuperseded rows with no stored embedding.
129    pub missing_embeddings: u64,
130    /// Groups of hot rows sharing byte-identical content, vault notes aside.
131    pub duplicate_hot_groups: u64,
132    /// Hot raws marked as distilled whose distillate no longer exists.
133    pub orphaned_distillation_markers: u64,
134    pub dead_vec_keys: u64,
135    pub dangling_relations: u64,
136    pub sync_log_rows: u64,
137    pub sync_log_reclaimable_rows: u64,
138    pub tombstones: u64,
139    /// `sync_state` cursors keyed by something other than a peer machine id.
140    pub stale_peer_cursors: u64,
141    pub outbox_pending: u64,
142    pub index_vectors: Option<u64>,
143    pub index_docs: Option<u64>,
144    pub index_consistent: Option<bool>,
145    pub db_bytes: u64,
146    pub freelist_bytes: u64,
147}
148
149impl IntegrityReport {
150    /// The fields a repair is expected to bring to zero (or true). Anything
151    /// else in the report is descriptive.
152    pub fn drift(&self) -> Vec<(&'static str, u64)> {
153        let mut out = Vec::new();
154        let mut push = |name: &'static str, value: u64| {
155            if value > 0 {
156                out.push((name, value));
157            }
158        };
159        push("raw_retire_pending", self.raw_retire_pending);
160        push("duplicate_hot_groups", self.duplicate_hot_groups);
161        push(
162            "orphaned_distillation_markers",
163            self.orphaned_distillation_markers,
164        );
165        push("dead_vec_keys", self.dead_vec_keys);
166        push("dangling_relations", self.dangling_relations);
167        push("sync_log_reclaimable_rows", self.sync_log_reclaimable_rows);
168        push("stale_peer_cursors", self.stale_peer_cursors);
169        push("missing_embeddings", self.missing_embeddings);
170        if self.index_consistent == Some(false) {
171            push("index_inconsistent", 1);
172        }
173        out
174    }
175}
176
177#[derive(Debug, Clone, Default, Serialize, Deserialize)]
178pub struct RepairStats {
179    pub dry_run: bool,
180    pub duplicates_superseded: u64,
181    pub distillation_markers_inherited: u64,
182    pub orphaned_markers_dropped: u64,
183    pub raws_retired: u64,
184    pub relations_pruned: u64,
185    pub vec_keys_deleted: u64,
186    /// True when the outbox still held undrained rows, so the vector index
187    /// could not be trusted to have already dropped the keys being judged.
188    pub vec_keys_gc_skipped: bool,
189    pub sync_log_rows_deleted: u64,
190    pub stale_cursors_dropped: u64,
191    pub embeddings_backfilled: u64,
192    pub embedding_failures: u64,
193    pub index_rebuilt: bool,
194}
195
196impl RepairStats {
197    pub fn is_clean(&self) -> bool {
198        self.raws_retired == 0
199            && self.duplicates_superseded == 0
200            && self.distillation_markers_inherited == 0
201            && self.orphaned_markers_dropped == 0
202            && self.relations_pruned == 0
203            && self.vec_keys_deleted == 0
204            && !self.vec_keys_gc_skipped
205            && self.sync_log_rows_deleted == 0
206            && self.stale_cursors_dropped == 0
207            && self.embeddings_backfilled == 0
208            && self.embedding_failures == 0
209            && !self.index_rebuilt
210    }
211}
212
213/// What the daemon remembers about its most recent repair, cheap enough for
214/// `/v1/status` to return without recomputing anything.
215#[derive(Debug, Clone, Serialize, Deserialize)]
216pub struct MaintenanceSummary {
217    pub ran_at: DateTime<Utc>,
218    pub stats: RepairStats,
219    pub report_after: IntegrityReport,
220}
221
222/// Memory counts cheap enough for every `/v1/status` call: one grouped
223/// scan, unlike `integrity_report`. `live + archived + superseded` is
224/// `stored_total`; a row both archived and superseded counts as archived.
225#[derive(Debug, Clone, Default, PartialEq, Eq, Serialize, Deserialize)]
226pub struct LiveCounts {
227    pub stored_total: u64,
228    /// Hot and unsuperseded: what recall and context can return.
229    pub live: u64,
230    pub archived: u64,
231    pub superseded: u64,
232    /// Live memories per memory type.
233    pub by_type: std::collections::BTreeMap<String, u64>,
234}
235
236pub type EmbedFn<'a> = &'a dyn Fn(&str) -> Result<Vec<f32>>;
237
238/// Document-side batch embedding (no query prefix), one vector per text.
239pub type EmbedBatchFn<'a> = &'a dyn Fn(&[&str]) -> Result<Vec<Vec<f32>>>;
240
241#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
242pub struct ChunkBackfill {
243    pub memories: u64,
244    pub chunks: u64,
245    pub failed: u64,
246    /// Memories still needing chunks after this call.
247    pub pending: u64,
248}
249
250/// A `sync_state` cursor whose peer is not a machine id. Sync used to key
251/// cursors by the raw ssh host string; those rows can never advance because
252/// nothing writes them any more.
253pub(crate) fn is_stale_cursor(peer_id: &str) -> bool {
254    let bare = peer_id.strip_prefix("push:").unwrap_or(peer_id);
255    Uuid::parse_str(bare).is_err()
256}
257
258pub(crate) fn live_counts_on(conn: &rusqlite::Connection) -> Result<LiveCounts> {
259    let mut stmt = conn.prepare(
260        "SELECT CASE WHEN tier = 'archived' THEN 'archived' \
261                     WHEN superseded_by IS NOT NULL THEN 'superseded' \
262                     ELSE 'live' END, memory_type, COUNT(*) \
263         FROM memories GROUP BY 1, 2",
264    )?;
265    let rows = stmt.query_map([], |r| {
266        Ok((
267            r.get::<_, String>(0)?,
268            r.get::<_, String>(1)?,
269            r.get::<_, i64>(2)? as u64,
270        ))
271    })?;
272    let mut counts = LiveCounts::default();
273    for row in rows {
274        let (state, memory_type, n) = row?;
275        counts.stored_total += n;
276        match state.as_str() {
277            "archived" => counts.archived += n,
278            "superseded" => counts.superseded += n,
279            _ => {
280                counts.live += n;
281                *counts.by_type.entry(memory_type).or_default() += n;
282            }
283        }
284    }
285    Ok(counts)
286}
287
288impl SqliteStore {
289    pub fn live_counts(&self) -> Result<LiveCounts> {
290        live_counts_on(&self.reader())
291    }
292
293    /// Outbox rows the index drain has not applied yet. While any are
294    /// pending the index counts lag the store by design, so a count
295    /// mismatch then is not drift.
296    pub fn outbox_pending(&self) -> Result<u64> {
297        let watermark = self.index_watermark()?;
298        let n: i64 = self.reader().query_row(
299            "SELECT COUNT(*) FROM index_outbox WHERE seq > ?1",
300            params![watermark],
301            |r| r.get(0),
302        )?;
303        Ok(n as u64)
304    }
305
306    pub fn integrity_report(&self, indexes: Option<&Indexes>) -> Result<IntegrityReport> {
307        let consolidation = self.consolidation_stats()?;
308        let stale_peer_cursors = self
309            .get_all_sync_states()?
310            .iter()
311            .filter(|s| is_stale_cursor(&s.peer_id))
312            .count() as u64;
313        let (index_vectors, index_docs, index_consistent) = match indexes {
314            Some(idx) => (
315                Some(idx.vector_len() as u64),
316                Some(idx.lexical_len()),
317                Some(idx.consistent_with(self)?),
318            ),
319            None => (None, None, None),
320        };
321        // An in-memory store's reader is its writer mutex, and a pooled
322        // checkout blocks, so nothing below may take a second connection.
323        let conn = self.reader();
324        let count = |sql: &str| -> Result<u64> {
325            let n: i64 = conn.query_row(sql, [], |r| r.get(0))?;
326            Ok(n as u64)
327        };
328        let page_size = crate::sqlite_store::page_size(&conn)? as u64;
329        Ok(IntegrityReport {
330            memories_total: count("SELECT COUNT(*) FROM memories")?,
331            hot: count(&format!("SELECT COUNT(*) FROM memories WHERE {HOT}"))?,
332            archived: count("SELECT COUNT(*) FROM memories WHERE tier = 'archived'")?,
333            superseded: count("SELECT COUNT(*) FROM memories WHERE superseded_by IS NOT NULL")?,
334            distilled_live: consolidation.distilled_live,
335            raw_pending: consolidation.raw_pending,
336            raw_retired: consolidation.raw_retired,
337            raw_retire_pending: consolidation.raw_retire_pending,
338            missing_embeddings: count(&format!(
339                "SELECT COUNT(*) FROM memories WHERE {HOT} AND embedding IS NULL"
340            ))?,
341            duplicate_hot_groups: count(&format!(
342                "SELECT COUNT(*) FROM (SELECT 1 FROM memories WHERE {DEDUP_CANDIDATE} \
343                 AND content_prefix IN (SELECT content_prefix FROM memories \
344                                        WHERE {DEDUP_CANDIDATE} \
345                                        GROUP BY content_prefix HAVING COUNT(*) > 1) \
346                 GROUP BY content HAVING COUNT(*) > 1)"
347            ))?,
348            orphaned_distillation_markers: count(&format!(
349                "SELECT COUNT(*) FROM ({ORPHANED_MARKERS})"
350            ))?,
351            dead_vec_keys: count(DEAD_VEC_KEYS)?,
352            dangling_relations: count(&format!(
353                "SELECT COUNT(*) FROM relations WHERE {DANGLING_RELATIONS}"
354            ))?,
355            sync_log_rows: count("SELECT COUNT(*) FROM sync_log")?,
356            sync_log_reclaimable_rows: count(&format!(
357                "SELECT COUNT(*) FROM ({RECLAIMABLE_SYNC_LOG})"
358            ))?,
359            tombstones: count("SELECT COUNT(*) FROM tombstones")?,
360            stale_peer_cursors,
361            outbox_pending: count("SELECT COUNT(*) FROM index_outbox")?,
362            index_vectors,
363            index_docs,
364            index_consistent,
365            db_bytes: count("PRAGMA page_count")? * page_size,
366            freelist_bytes: count("PRAGMA freelist_count")? * page_size,
367        })
368    }
369
370    /// Restores every invariant in `IntegrityReport::drift`, each step in
371    /// its own transactions so a failure part-way leaves earlier steps
372    /// committed. With `indexes`, the outbox is drained before the key
373    /// garbage collection and before the consistency check, so one pass
374    /// leaves nothing for the next. `dry_run` counts without writing.
375    pub fn repair(
376        &self,
377        mut indexes: Option<&mut Indexes>,
378        embed: Option<EmbedFn<'_>>,
379        dry_run: bool,
380    ) -> Result<RepairStats> {
381        let mut stats = RepairStats {
382            dry_run,
383            ..Default::default()
384        };
385        // Dedup first so a keeper can inherit its twin's marker and be
386        // retired in this same pass instead of the next one.
387        stats.duplicates_superseded = self.dedup_hot_memories(dry_run)?;
388        stats.distillation_markers_inherited = self.inherit_distillation_markers(dry_run)?;
389        stats.orphaned_markers_dropped = self.drop_orphaned_markers(dry_run)?;
390        stats.raws_retired = if dry_run {
391            self.consolidation_stats()?.raw_retire_pending
392        } else {
393            self.retire_consolidated_raws()? as u64
394        };
395        stats.relations_pruned = if dry_run {
396            self.count_dangling_relations()?
397        } else {
398            self.prune_dangling_relations()?
399        };
400        if let Some(idx) = indexes.as_deref_mut()
401            && !dry_run
402        {
403            idx.drain(self)?;
404        }
405        match self.gc_vec_keys(dry_run)? {
406            Some(n) => stats.vec_keys_deleted = n,
407            None => stats.vec_keys_gc_skipped = true,
408        }
409        stats.sync_log_rows_deleted = self.gc_sync_log(dry_run)?;
410        stats.stale_cursors_dropped = self.drop_stale_peer_cursors(dry_run)?;
411        if let Some(embed) = embed {
412            let (done, failed) = self.backfill_embeddings(embed, dry_run)?;
413            stats.embeddings_backfilled = done;
414            stats.embedding_failures = failed;
415        }
416        if let Some(idx) = indexes {
417            if !dry_run {
418                idx.drain(self)?;
419            }
420            if !idx.consistent_with(self)? {
421                stats.index_rebuilt = true;
422                if !dry_run {
423                    idx.rebuild(self)?;
424                }
425            }
426        }
427        Ok(stats)
428    }
429
430    /// Byte-identical hot rows collapse onto the oldest one (ties broken by
431    /// id). Losers are superseded, never deleted, so their relations and
432    /// distillation history stay resolvable. Grouping is by full content:
433    /// the 128-char prefix is far too coarse for short messages.
434    fn dedup_hot_memories(&self, dry_run: bool) -> Result<u64> {
435        const BATCH: usize = 500;
436        let mut rows: Vec<(String, DateTime<Utc>, Uuid)> = {
437            let conn = self.reader();
438            let mut stmt = conn.prepare(&format!(
439                "SELECT id, content, created_at FROM memories WHERE {DEDUP_CANDIDATE} \
440                 AND content_prefix IN (SELECT content_prefix FROM memories \
441                                        WHERE {DEDUP_CANDIDATE} \
442                                        GROUP BY content_prefix HAVING COUNT(*) > 1)"
443            ))?;
444            let rows = stmt.query_map([], |r| {
445                // Stored timestamps mix fractional-second widths, so text
446                // order is not time order; parse before choosing the oldest.
447                let created = DateTime::parse_from_rfc3339(&r.get::<_, String>(2)?)
448                    .map(|d| d.with_timezone(&Utc))
449                    .unwrap_or(DateTime::UNIX_EPOCH);
450                Ok((
451                    r.get::<_, String>(1)?,
452                    created,
453                    parse_uuid(&r.get::<_, String>(0)?)?,
454                ))
455            })?;
456            rows.collect::<rusqlite::Result<Vec<_>>>()?
457        };
458        rows.sort();
459        let mut pairs: Vec<(Uuid, Uuid)> = Vec::new();
460        let mut keeper: Option<(&Uuid, &str)> = None;
461        for (content, _, id) in &rows {
462            match keeper {
463                Some((keep_id, keep_content)) if keep_content == content => {
464                    pairs.push((*id, *keep_id));
465                }
466                _ => keeper = Some((id, content)),
467            }
468        }
469        if dry_run {
470            return Ok(pairs.len() as u64);
471        }
472        let mut superseded = 0u64;
473        for chunk in pairs.chunks(BATCH) {
474            superseded += self.with_txn(|tx| {
475                let mut n = 0u64;
476                for (loser, winner) in chunk {
477                    if self.supersede_on_tx(tx, *loser, *winner)? {
478                        n += 1;
479                    }
480                }
481                Ok(n)
482            })?;
483        }
484        Ok(superseded)
485    }
486
487    fn inherit_distillation_markers(&self, dry_run: bool) -> Result<u64> {
488        let rows: Vec<(Uuid, Uuid, Option<String>, bool)> = {
489            let conn = self.reader();
490            let mut stmt = conn.prepare(INHERITABLE_MARKERS)?;
491            let rows = stmt.query_map([], |r| {
492                Ok((
493                    parse_uuid(&r.get::<_, String>(0)?)?,
494                    parse_uuid(&r.get::<_, String>(1)?)?,
495                    r.get::<_, Option<String>>(2)?,
496                    r.get::<_, bool>(3)?,
497                ))
498            })?;
499            rows.collect::<rusqlite::Result<Vec<_>>>()?
500        };
501        let mut rows = rows;
502        let mut keepers: HashSet<Uuid> = rows.iter().map(|r| r.0).collect();
503        for (keeper, distilled_id, model, skip) in self.cold_twin_markers()? {
504            if keepers.insert(keeper) {
505                rows.push((keeper, distilled_id, model, skip));
506            }
507        }
508        if dry_run {
509            return Ok(rows.len() as u64);
510        }
511        for (keeper, distilled_id, model, skip) in &rows {
512            // A skip marker points at itself; keep that shape for the keeper
513            // rather than pointing it at a twin that is not a distillate.
514            let target = if *skip { *keeper } else { *distilled_id };
515            self.mark_consolidated(*keeper, target, model.as_deref().unwrap_or("inherited"))?;
516        }
517        Ok(rows.len() as u64)
518    }
519
520    fn cold_twin_markers(&self) -> Result<Vec<(Uuid, Uuid, Option<String>, bool)>> {
521        let conn = self.reader();
522        let mut stmt = conn.prepare(&inheritable_cold_markers())?;
523        let rows = stmt.query_map([], |r| {
524            Ok((
525                parse_uuid(&r.get::<_, String>(0)?)?,
526                parse_uuid(&r.get::<_, String>(1)?)?,
527                r.get::<_, Option<String>>(2)?,
528                r.get::<_, bool>(3)?,
529                r.get::<_, Vec<u8>>(4)?,
530                r.get::<_, String>(5)?,
531            ))
532        })?;
533        let mut out = Vec::new();
534        for row in rows {
535            let (keeper, distilled_id, model, skip, content, stub) = row?;
536            if String::from_utf8(content).is_ok_and(|c| stub_matches(&stub, &c)) {
537                out.push((keeper, distilled_id, model, skip));
538            }
539        }
540        Ok(out)
541    }
542
543    /// Clears the marker so consolidation picks the raw up again, and logs
544    /// the removal so peers stop treating it as spoken for.
545    fn drop_orphaned_markers(&self, dry_run: bool) -> Result<u64> {
546        let ids: Vec<Uuid> = {
547            let conn = self.reader();
548            let mut stmt = conn.prepare(ORPHANED_MARKERS)?;
549            let rows = stmt.query_map([], |r| parse_uuid(&r.get::<_, String>(0)?))?;
550            rows.collect::<rusqlite::Result<Vec<_>>>()?
551        };
552        if dry_run {
553            return Ok(ids.len() as u64);
554        }
555        let mut dropped = 0u64;
556        for id in &ids {
557            dropped += self.with_txn(|tx| Ok(u64::from(self.drop_marker_on_tx(tx, *id)?)))?;
558        }
559        Ok(dropped)
560    }
561
562    fn count_dangling_relations(&self) -> Result<u64> {
563        let conn = self.reader();
564        let n: i64 = conn.query_row(
565            &format!("SELECT COUNT(*) FROM relations WHERE {DANGLING_RELATIONS}"),
566            [],
567            |r| r.get(0),
568        )?;
569        Ok(n as u64)
570    }
571
572    /// `None` when the gate fails: with rows past the watermark the vector
573    /// index may still hold a key this would delete, stranding the vector
574    /// until the next full rebuild.
575    fn gc_vec_keys(&self, dry_run: bool) -> Result<Option<u64>> {
576        let watermark = self.index_watermark()?;
577        let pending: i64 = {
578            let conn = self.reader();
579            conn.query_row(
580                "SELECT COUNT(*) FROM index_outbox WHERE seq > ?1",
581                params![watermark],
582                |r| r.get(0),
583            )?
584        };
585        if pending > 0 {
586            return Ok(None);
587        }
588        if dry_run {
589            let conn = self.reader();
590            let n: i64 = conn.query_row(DEAD_VEC_KEYS, [], |r| r.get(0))?;
591            return Ok(Some(n as u64));
592        }
593        let conn = self.conn();
594        let n = conn.execute(
595            "DELETE FROM vec_keys WHERE key IN (SELECT vk.key FROM vec_keys vk \
596             LEFT JOIN memories m ON m.id = vk.memory_id \
597             WHERE m.id IS NULL OR m.tier <> 'hot' OR m.superseded_by IS NOT NULL \
598                OR m.embedding IS NULL)",
599            [],
600        )?;
601        Ok(Some(n as u64))
602    }
603
604    /// Same temp-table and chunked-delete shape as `compact_sync_log`.
605    fn gc_sync_log(&self, dry_run: bool) -> Result<u64> {
606        let mut conn = self.conn();
607        conn.execute_batch("PRAGMA cache_size = -262144;")?;
608        conn.execute_batch("DROP TABLE IF EXISTS gc_victims;")?;
609        conn.execute(
610            &format!("CREATE TEMP TABLE gc_victims AS {RECLAIMABLE_SYNC_LOG}"),
611            [],
612        )?;
613        conn.execute_batch("CREATE INDEX idx_gc_victims_seq ON gc_victims(local_seq);")?;
614        let victims: i64 = conn.query_row("SELECT COUNT(*) FROM gc_victims", [], |r| r.get(0))?;
615        let mut deleted = 0u64;
616        if !dry_run {
617            let mut cursor: i64 = 0;
618            loop {
619                let chunk: Vec<i64> = {
620                    let mut stmt = conn.prepare(
621                        "SELECT local_seq FROM gc_victims WHERE local_seq > ?1 \
622                         ORDER BY local_seq LIMIT 20000",
623                    )?;
624                    let rows = stmt.query_map(params![cursor], |r| r.get::<_, i64>(0))?;
625                    rows.collect::<rusqlite::Result<Vec<_>>>()?
626                };
627                let Some(last) = chunk.last().copied() else {
628                    break;
629                };
630                cursor = last;
631                let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(",");
632                let tx = conn.transaction()?;
633                sync_dedup::keep_keys_of(&tx, &chunk)?;
634                deleted += tx.execute(
635                    &format!("DELETE FROM sync_log WHERE local_seq IN ({placeholders})"),
636                    rusqlite::params_from_iter(chunk.iter()),
637                )? as u64;
638                tx.commit()?;
639            }
640        }
641        conn.execute_batch("DROP TABLE IF EXISTS gc_victims;")?;
642        Ok(if dry_run { victims as u64 } else { deleted })
643    }
644
645    fn drop_stale_peer_cursors(&self, dry_run: bool) -> Result<u64> {
646        let stale: Vec<String> = self
647            .get_all_sync_states()?
648            .into_iter()
649            .filter(|s| is_stale_cursor(&s.peer_id))
650            .map(|s| s.peer_id)
651            .collect();
652        if dry_run {
653            return Ok(stale.len() as u64);
654        }
655        let conn = self.conn();
656        let mut dropped = 0u64;
657        for peer_id in &stale {
658            dropped += conn.execute(
659                "DELETE FROM sync_state WHERE peer_id = ?1",
660                params![peer_id],
661            )? as u64;
662        }
663        Ok(dropped)
664    }
665
666    /// Embeds hot rows that have none, over the same context-plus-content
667    /// body the ingest path embeds. `updated_at` is bumped so the peer's
668    /// last-writer-wins check accepts the row that now carries a vector.
669    /// Runs only this step of `repair`, so a caller that must not merge or
670    /// retire rows can fill vectors on its own schedule. Returns (rows
671    /// embedded, rows whose embedding failed or had the wrong dimension);
672    /// with `dry_run` it counts rows awaiting a vector and writes nothing.
673    pub fn backfill_embeddings(&self, embed: EmbedFn<'_>, dry_run: bool) -> Result<(u64, u64)> {
674        const BATCH: usize = 64;
675        let rows: Vec<(Uuid, Option<String>, String)> = {
676            let conn = self.reader();
677            let mut stmt = conn.prepare(&format!(
678                "SELECT id, context, content FROM memories WHERE {HOT} AND embedding IS NULL \
679                 ORDER BY created_at"
680            ))?;
681            let rows = stmt.query_map([], |r| {
682                Ok((
683                    parse_uuid(&r.get::<_, String>(0)?)?,
684                    r.get::<_, Option<String>>(1)?,
685                    r.get::<_, String>(2)?,
686                ))
687            })?;
688            rows.collect::<rusqlite::Result<Vec<_>>>()?
689        };
690        if dry_run {
691            return Ok((rows.len() as u64, 0));
692        }
693        let mut done = 0u64;
694        let mut failed = 0u64;
695        for chunk in rows.chunks(BATCH) {
696            let mut embedded: Vec<(Uuid, Vec<f32>)> = Vec::with_capacity(chunk.len());
697            for (id, context, content) in chunk {
698                let body = body_of(context.clone(), content.clone());
699                match embed(&body) {
700                    Ok(v) if v.len() == EMBEDDING_DIM => embedded.push((*id, v)),
701                    Ok(v) => {
702                        failed += 1;
703                        tracing::warn!(
704                            memory_id = %id,
705                            dim = v.len(),
706                            "embedding backfill produced the wrong dimension"
707                        );
708                    }
709                    Err(e) => {
710                        failed += 1;
711                        tracing::warn!(memory_id = %id, "embedding backfill failed: {e:#}");
712                    }
713                }
714            }
715            done += self.with_txn(|tx| {
716                let mut n = 0u64;
717                for (id, vector) in &embedded {
718                    let changed = tx.execute(
719                        "UPDATE memories SET embedding = ?2, updated_at = ?3 \
720                         WHERE id = ?1 AND embedding IS NULL",
721                        params![
722                            id.to_string(),
723                            embedding_to_blob(vector),
724                            Utc::now().to_rfc3339()
725                        ],
726                    )?;
727                    if changed == 0 {
728                        continue;
729                    }
730                    Self::append_outbox(tx, *id, "upsert")?;
731                    if let Some(memory) = Self::memory_on_tx(tx, *id)? {
732                        self.log_memory_update(tx, &memory)?;
733                    }
734                    n += 1;
735                }
736                Ok(n)
737            })?;
738        }
739        Ok((done, failed))
740    }
741
742    /// Embeds the chunk sets of up to `max_memories` memories that need
743    /// them and stores each set whole via `store_chunks`, which queues the
744    /// index upsert. Texts go to `embed` in batches of at most 16 (a padded
745    /// ONNX batch of 64 held about 2.4 GB of arena, past the Pi's cap), whole
746    /// memories per batch; once `budget` has elapsed no further batch
747    /// starts, so a caller on a shared thread gets it back promptly. A
748    /// memory any of whose vectors fails or has the wrong dimension counts
749    /// as failed and is left for a later call. Records the remaining
750    /// pending count for `chunk_progress`.
751    pub fn backfill_chunk_embeddings(
752        &self,
753        embed: EmbedBatchFn<'_>,
754        max_memories: usize,
755        budget: std::time::Duration,
756    ) -> Result<ChunkBackfill> {
757        const BATCH: usize = 16;
758        let started = std::time::Instant::now();
759        let (work, pending) = self.scan_chunk_work(max_memories)?;
760        let mut out = ChunkBackfill::default();
761        let mut rest = work.as_slice();
762        while !rest.is_empty() && (out.memories + out.failed == 0 || started.elapsed() < budget) {
763            let mut take = 1;
764            let mut texts = rest[0].chunks.len();
765            while take < rest.len() && texts + rest[take].chunks.len() <= BATCH {
766                texts += rest[take].chunks.len();
767                take += 1;
768            }
769            let (batch, tail) = rest.split_at(take);
770            rest = tail;
771            let refs: Vec<&str> = batch
772                .iter()
773                .flat_map(|w| w.chunks.iter().map(String::as_str))
774                .collect();
775            let vectors = match embed(&refs) {
776                Ok(v) if v.len() == refs.len() => v,
777                Ok(v) => {
778                    out.failed += batch.len() as u64;
779                    tracing::warn!(
780                        expected = refs.len(),
781                        got = v.len(),
782                        "chunk embedding returned the wrong number of vectors"
783                    );
784                    continue;
785                }
786                Err(e) => {
787                    out.failed += batch.len() as u64;
788                    tracing::warn!("chunk embedding failed: {e:#}");
789                    continue;
790                }
791            };
792            let mut vectors = vectors.into_iter();
793            for w in batch {
794                let set: Vec<(usize, String, Vec<f32>)> = w
795                    .chunks
796                    .iter()
797                    .cloned()
798                    .enumerate()
799                    .zip(vectors.by_ref())
800                    .map(|((ord, text), v)| (ord, text, v))
801                    .collect();
802                if set.iter().any(|(_, _, v)| v.len() != EMBEDDING_DIM) {
803                    out.failed += 1;
804                    tracing::warn!(memory_id = %w.memory_id, "chunk embedding had the wrong dimension");
805                    continue;
806                }
807                if self.store_chunks(w.memory_id, &w.content_hash, &set)? {
808                    out.memories += 1;
809                    out.chunks += set.len() as u64;
810                }
811            }
812        }
813        out.pending = (pending as u64).saturating_sub(out.memories);
814        self.set_chunks_pending(out.pending as usize)?;
815        Ok(out)
816    }
817
818    /// Whether a sync_log row with this local_seq still exists; a test and
819    /// diagnostic hook for the sync log garbage collector.
820    pub fn sync_log_row_exists(&self, local_seq: u64) -> Result<bool> {
821        let conn = self.reader();
822        let hit: Option<i64> = conn
823            .query_row(
824                "SELECT 1 FROM sync_log WHERE local_seq = ?1",
825                params![local_seq as i64],
826                |r| r.get(0),
827            )
828            .optional()?;
829        Ok(hit.is_some())
830    }
831}