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
258impl SqliteStore {
259    pub fn live_counts(&self) -> Result<LiveCounts> {
260        let conn = self.reader();
261        let mut stmt = conn.prepare(
262            "SELECT CASE WHEN tier = 'archived' THEN 'archived' \
263                         WHEN superseded_by IS NOT NULL THEN 'superseded' \
264                         ELSE 'live' END, memory_type, COUNT(*) \
265             FROM memories GROUP BY 1, 2",
266        )?;
267        let rows = stmt.query_map([], |r| {
268            Ok((
269                r.get::<_, String>(0)?,
270                r.get::<_, String>(1)?,
271                r.get::<_, i64>(2)? as u64,
272            ))
273        })?;
274        let mut counts = LiveCounts::default();
275        for row in rows {
276            let (state, memory_type, n) = row?;
277            counts.stored_total += n;
278            match state.as_str() {
279                "archived" => counts.archived += n,
280                "superseded" => counts.superseded += n,
281                _ => {
282                    counts.live += n;
283                    *counts.by_type.entry(memory_type).or_default() += n;
284                }
285            }
286        }
287        Ok(counts)
288    }
289
290    /// Outbox rows the index drain has not applied yet. While any are
291    /// pending the index counts lag the store by design, so a count
292    /// mismatch then is not drift.
293    pub fn outbox_pending(&self) -> Result<u64> {
294        let watermark = self.index_watermark()?;
295        let n: i64 = self.reader().query_row(
296            "SELECT COUNT(*) FROM index_outbox WHERE seq > ?1",
297            params![watermark],
298            |r| r.get(0),
299        )?;
300        Ok(n as u64)
301    }
302
303    pub fn integrity_report(&self, indexes: Option<&Indexes>) -> Result<IntegrityReport> {
304        let consolidation = self.consolidation_stats()?;
305        let stale_peer_cursors = self
306            .get_all_sync_states()?
307            .iter()
308            .filter(|s| is_stale_cursor(&s.peer_id))
309            .count() as u64;
310        let (index_vectors, index_docs, index_consistent) = match indexes {
311            Some(idx) => (
312                Some(idx.vector_len() as u64),
313                Some(idx.lexical_len()),
314                Some(idx.consistent_with(self)?),
315            ),
316            None => (None, None, None),
317        };
318        // An in-memory store's reader is its writer mutex, and a pooled
319        // checkout blocks, so nothing below may take a second connection.
320        let conn = self.reader();
321        let count = |sql: &str| -> Result<u64> {
322            let n: i64 = conn.query_row(sql, [], |r| r.get(0))?;
323            Ok(n as u64)
324        };
325        let page_size = crate::sqlite_store::page_size(&conn)? as u64;
326        Ok(IntegrityReport {
327            memories_total: count("SELECT COUNT(*) FROM memories")?,
328            hot: count(&format!("SELECT COUNT(*) FROM memories WHERE {HOT}"))?,
329            archived: count("SELECT COUNT(*) FROM memories WHERE tier = 'archived'")?,
330            superseded: count("SELECT COUNT(*) FROM memories WHERE superseded_by IS NOT NULL")?,
331            distilled_live: consolidation.distilled_live,
332            raw_pending: consolidation.raw_pending,
333            raw_retired: consolidation.raw_retired,
334            raw_retire_pending: consolidation.raw_retire_pending,
335            missing_embeddings: count(&format!(
336                "SELECT COUNT(*) FROM memories WHERE {HOT} AND embedding IS NULL"
337            ))?,
338            duplicate_hot_groups: count(&format!(
339                "SELECT COUNT(*) FROM (SELECT 1 FROM memories WHERE {DEDUP_CANDIDATE} \
340                 AND content_prefix IN (SELECT content_prefix FROM memories \
341                                        WHERE {DEDUP_CANDIDATE} \
342                                        GROUP BY content_prefix HAVING COUNT(*) > 1) \
343                 GROUP BY content HAVING COUNT(*) > 1)"
344            ))?,
345            orphaned_distillation_markers: count(&format!(
346                "SELECT COUNT(*) FROM ({ORPHANED_MARKERS})"
347            ))?,
348            dead_vec_keys: count(DEAD_VEC_KEYS)?,
349            dangling_relations: count(&format!(
350                "SELECT COUNT(*) FROM relations WHERE {DANGLING_RELATIONS}"
351            ))?,
352            sync_log_rows: count("SELECT COUNT(*) FROM sync_log")?,
353            sync_log_reclaimable_rows: count(&format!(
354                "SELECT COUNT(*) FROM ({RECLAIMABLE_SYNC_LOG})"
355            ))?,
356            tombstones: count("SELECT COUNT(*) FROM tombstones")?,
357            stale_peer_cursors,
358            outbox_pending: count("SELECT COUNT(*) FROM index_outbox")?,
359            index_vectors,
360            index_docs,
361            index_consistent,
362            db_bytes: count("PRAGMA page_count")? * page_size,
363            freelist_bytes: count("PRAGMA freelist_count")? * page_size,
364        })
365    }
366
367    /// Restores every invariant in `IntegrityReport::drift`, each step in
368    /// its own transactions so a failure part-way leaves earlier steps
369    /// committed. With `indexes`, the outbox is drained before the key
370    /// garbage collection and before the consistency check, so one pass
371    /// leaves nothing for the next. `dry_run` counts without writing.
372    pub fn repair(
373        &self,
374        mut indexes: Option<&mut Indexes>,
375        embed: Option<EmbedFn<'_>>,
376        dry_run: bool,
377    ) -> Result<RepairStats> {
378        let mut stats = RepairStats {
379            dry_run,
380            ..Default::default()
381        };
382        // Dedup first so a keeper can inherit its twin's marker and be
383        // retired in this same pass instead of the next one.
384        stats.duplicates_superseded = self.dedup_hot_memories(dry_run)?;
385        stats.distillation_markers_inherited = self.inherit_distillation_markers(dry_run)?;
386        stats.orphaned_markers_dropped = self.drop_orphaned_markers(dry_run)?;
387        stats.raws_retired = if dry_run {
388            self.consolidation_stats()?.raw_retire_pending
389        } else {
390            self.retire_consolidated_raws()? as u64
391        };
392        stats.relations_pruned = if dry_run {
393            self.count_dangling_relations()?
394        } else {
395            self.prune_dangling_relations()?
396        };
397        if let Some(idx) = indexes.as_deref_mut()
398            && !dry_run
399        {
400            idx.drain(self)?;
401        }
402        match self.gc_vec_keys(dry_run)? {
403            Some(n) => stats.vec_keys_deleted = n,
404            None => stats.vec_keys_gc_skipped = true,
405        }
406        stats.sync_log_rows_deleted = self.gc_sync_log(dry_run)?;
407        stats.stale_cursors_dropped = self.drop_stale_peer_cursors(dry_run)?;
408        if let Some(embed) = embed {
409            let (done, failed) = self.backfill_embeddings(embed, dry_run)?;
410            stats.embeddings_backfilled = done;
411            stats.embedding_failures = failed;
412        }
413        if let Some(idx) = indexes {
414            if !dry_run {
415                idx.drain(self)?;
416            }
417            if !idx.consistent_with(self)? {
418                stats.index_rebuilt = true;
419                if !dry_run {
420                    idx.rebuild(self)?;
421                }
422            }
423        }
424        Ok(stats)
425    }
426
427    /// Byte-identical hot rows collapse onto the oldest one (ties broken by
428    /// id). Losers are superseded, never deleted, so their relations and
429    /// distillation history stay resolvable. Grouping is by full content:
430    /// the 128-char prefix is far too coarse for short messages.
431    fn dedup_hot_memories(&self, dry_run: bool) -> Result<u64> {
432        const BATCH: usize = 500;
433        let mut rows: Vec<(String, DateTime<Utc>, Uuid)> = {
434            let conn = self.reader();
435            let mut stmt = conn.prepare(&format!(
436                "SELECT id, content, created_at FROM memories WHERE {DEDUP_CANDIDATE} \
437                 AND content_prefix IN (SELECT content_prefix FROM memories \
438                                        WHERE {DEDUP_CANDIDATE} \
439                                        GROUP BY content_prefix HAVING COUNT(*) > 1)"
440            ))?;
441            let rows = stmt.query_map([], |r| {
442                // Stored timestamps mix fractional-second widths, so text
443                // order is not time order; parse before choosing the oldest.
444                let created = DateTime::parse_from_rfc3339(&r.get::<_, String>(2)?)
445                    .map(|d| d.with_timezone(&Utc))
446                    .unwrap_or(DateTime::UNIX_EPOCH);
447                Ok((
448                    r.get::<_, String>(1)?,
449                    created,
450                    parse_uuid(&r.get::<_, String>(0)?)?,
451                ))
452            })?;
453            rows.collect::<rusqlite::Result<Vec<_>>>()?
454        };
455        rows.sort();
456        let mut pairs: Vec<(Uuid, Uuid)> = Vec::new();
457        let mut keeper: Option<(&Uuid, &str)> = None;
458        for (content, _, id) in &rows {
459            match keeper {
460                Some((keep_id, keep_content)) if keep_content == content => {
461                    pairs.push((*id, *keep_id));
462                }
463                _ => keeper = Some((id, content)),
464            }
465        }
466        if dry_run {
467            return Ok(pairs.len() as u64);
468        }
469        let mut superseded = 0u64;
470        for chunk in pairs.chunks(BATCH) {
471            superseded += self.with_txn(|tx| {
472                let mut n = 0u64;
473                for (loser, winner) in chunk {
474                    if self.supersede_on_tx(tx, *loser, *winner)? {
475                        n += 1;
476                    }
477                }
478                Ok(n)
479            })?;
480        }
481        Ok(superseded)
482    }
483
484    fn inherit_distillation_markers(&self, dry_run: bool) -> Result<u64> {
485        let rows: Vec<(Uuid, Uuid, Option<String>, bool)> = {
486            let conn = self.reader();
487            let mut stmt = conn.prepare(INHERITABLE_MARKERS)?;
488            let rows = stmt.query_map([], |r| {
489                Ok((
490                    parse_uuid(&r.get::<_, String>(0)?)?,
491                    parse_uuid(&r.get::<_, String>(1)?)?,
492                    r.get::<_, Option<String>>(2)?,
493                    r.get::<_, bool>(3)?,
494                ))
495            })?;
496            rows.collect::<rusqlite::Result<Vec<_>>>()?
497        };
498        let mut rows = rows;
499        let mut keepers: HashSet<Uuid> = rows.iter().map(|r| r.0).collect();
500        for (keeper, distilled_id, model, skip) in self.cold_twin_markers()? {
501            if keepers.insert(keeper) {
502                rows.push((keeper, distilled_id, model, skip));
503            }
504        }
505        if dry_run {
506            return Ok(rows.len() as u64);
507        }
508        for (keeper, distilled_id, model, skip) in &rows {
509            // A skip marker points at itself; keep that shape for the keeper
510            // rather than pointing it at a twin that is not a distillate.
511            let target = if *skip { *keeper } else { *distilled_id };
512            self.mark_consolidated(*keeper, target, model.as_deref().unwrap_or("inherited"))?;
513        }
514        Ok(rows.len() as u64)
515    }
516
517    fn cold_twin_markers(&self) -> Result<Vec<(Uuid, Uuid, Option<String>, bool)>> {
518        let conn = self.reader();
519        let mut stmt = conn.prepare(&inheritable_cold_markers())?;
520        let rows = stmt.query_map([], |r| {
521            Ok((
522                parse_uuid(&r.get::<_, String>(0)?)?,
523                parse_uuid(&r.get::<_, String>(1)?)?,
524                r.get::<_, Option<String>>(2)?,
525                r.get::<_, bool>(3)?,
526                r.get::<_, Vec<u8>>(4)?,
527                r.get::<_, String>(5)?,
528            ))
529        })?;
530        let mut out = Vec::new();
531        for row in rows {
532            let (keeper, distilled_id, model, skip, content, stub) = row?;
533            if String::from_utf8(content).is_ok_and(|c| stub_matches(&stub, &c)) {
534                out.push((keeper, distilled_id, model, skip));
535            }
536        }
537        Ok(out)
538    }
539
540    /// Clears the marker so consolidation picks the raw up again, and logs
541    /// the removal so peers stop treating it as spoken for.
542    fn drop_orphaned_markers(&self, dry_run: bool) -> Result<u64> {
543        let ids: Vec<Uuid> = {
544            let conn = self.reader();
545            let mut stmt = conn.prepare(ORPHANED_MARKERS)?;
546            let rows = stmt.query_map([], |r| parse_uuid(&r.get::<_, String>(0)?))?;
547            rows.collect::<rusqlite::Result<Vec<_>>>()?
548        };
549        if dry_run {
550            return Ok(ids.len() as u64);
551        }
552        let mut dropped = 0u64;
553        for id in &ids {
554            dropped += self.with_txn(|tx| Ok(u64::from(self.drop_marker_on_tx(tx, *id)?)))?;
555        }
556        Ok(dropped)
557    }
558
559    fn count_dangling_relations(&self) -> Result<u64> {
560        let conn = self.reader();
561        let n: i64 = conn.query_row(
562            &format!("SELECT COUNT(*) FROM relations WHERE {DANGLING_RELATIONS}"),
563            [],
564            |r| r.get(0),
565        )?;
566        Ok(n as u64)
567    }
568
569    /// `None` when the gate fails: with rows past the watermark the vector
570    /// index may still hold a key this would delete, stranding the vector
571    /// until the next full rebuild.
572    fn gc_vec_keys(&self, dry_run: bool) -> Result<Option<u64>> {
573        let watermark = self.index_watermark()?;
574        let pending: i64 = {
575            let conn = self.reader();
576            conn.query_row(
577                "SELECT COUNT(*) FROM index_outbox WHERE seq > ?1",
578                params![watermark],
579                |r| r.get(0),
580            )?
581        };
582        if pending > 0 {
583            return Ok(None);
584        }
585        if dry_run {
586            let conn = self.reader();
587            let n: i64 = conn.query_row(DEAD_VEC_KEYS, [], |r| r.get(0))?;
588            return Ok(Some(n as u64));
589        }
590        let conn = self.conn();
591        let n = conn.execute(
592            "DELETE FROM vec_keys WHERE key IN (SELECT vk.key FROM vec_keys vk \
593             LEFT JOIN memories m ON m.id = vk.memory_id \
594             WHERE m.id IS NULL OR m.tier <> 'hot' OR m.superseded_by IS NOT NULL \
595                OR m.embedding IS NULL)",
596            [],
597        )?;
598        Ok(Some(n as u64))
599    }
600
601    /// Same temp-table and chunked-delete shape as `compact_sync_log`.
602    fn gc_sync_log(&self, dry_run: bool) -> Result<u64> {
603        let mut conn = self.conn();
604        conn.execute_batch("PRAGMA cache_size = -262144;")?;
605        conn.execute_batch("DROP TABLE IF EXISTS gc_victims;")?;
606        conn.execute(
607            &format!("CREATE TEMP TABLE gc_victims AS {RECLAIMABLE_SYNC_LOG}"),
608            [],
609        )?;
610        conn.execute_batch("CREATE INDEX idx_gc_victims_seq ON gc_victims(local_seq);")?;
611        let victims: i64 = conn.query_row("SELECT COUNT(*) FROM gc_victims", [], |r| r.get(0))?;
612        let mut deleted = 0u64;
613        if !dry_run {
614            let mut cursor: i64 = 0;
615            loop {
616                let chunk: Vec<i64> = {
617                    let mut stmt = conn.prepare(
618                        "SELECT local_seq FROM gc_victims WHERE local_seq > ?1 \
619                         ORDER BY local_seq LIMIT 20000",
620                    )?;
621                    let rows = stmt.query_map(params![cursor], |r| r.get::<_, i64>(0))?;
622                    rows.collect::<rusqlite::Result<Vec<_>>>()?
623                };
624                let Some(last) = chunk.last().copied() else {
625                    break;
626                };
627                cursor = last;
628                let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(",");
629                let tx = conn.transaction()?;
630                sync_dedup::keep_keys_of(&tx, &chunk)?;
631                deleted += tx.execute(
632                    &format!("DELETE FROM sync_log WHERE local_seq IN ({placeholders})"),
633                    rusqlite::params_from_iter(chunk.iter()),
634                )? as u64;
635                tx.commit()?;
636            }
637        }
638        conn.execute_batch("DROP TABLE IF EXISTS gc_victims;")?;
639        Ok(if dry_run { victims as u64 } else { deleted })
640    }
641
642    fn drop_stale_peer_cursors(&self, dry_run: bool) -> Result<u64> {
643        let stale: Vec<String> = self
644            .get_all_sync_states()?
645            .into_iter()
646            .filter(|s| is_stale_cursor(&s.peer_id))
647            .map(|s| s.peer_id)
648            .collect();
649        if dry_run {
650            return Ok(stale.len() as u64);
651        }
652        let conn = self.conn();
653        let mut dropped = 0u64;
654        for peer_id in &stale {
655            dropped += conn.execute(
656                "DELETE FROM sync_state WHERE peer_id = ?1",
657                params![peer_id],
658            )? as u64;
659        }
660        Ok(dropped)
661    }
662
663    /// Embeds hot rows that have none, over the same context-plus-content
664    /// body the ingest path embeds. `updated_at` is bumped so the peer's
665    /// last-writer-wins check accepts the row that now carries a vector.
666    /// Runs only this step of `repair`, so a caller that must not merge or
667    /// retire rows can fill vectors on its own schedule. Returns (rows
668    /// embedded, rows whose embedding failed or had the wrong dimension);
669    /// with `dry_run` it counts rows awaiting a vector and writes nothing.
670    pub fn backfill_embeddings(&self, embed: EmbedFn<'_>, dry_run: bool) -> Result<(u64, u64)> {
671        const BATCH: usize = 64;
672        let rows: Vec<(Uuid, Option<String>, String)> = {
673            let conn = self.reader();
674            let mut stmt = conn.prepare(&format!(
675                "SELECT id, context, content FROM memories WHERE {HOT} AND embedding IS NULL \
676                 ORDER BY created_at"
677            ))?;
678            let rows = stmt.query_map([], |r| {
679                Ok((
680                    parse_uuid(&r.get::<_, String>(0)?)?,
681                    r.get::<_, Option<String>>(1)?,
682                    r.get::<_, String>(2)?,
683                ))
684            })?;
685            rows.collect::<rusqlite::Result<Vec<_>>>()?
686        };
687        if dry_run {
688            return Ok((rows.len() as u64, 0));
689        }
690        let mut done = 0u64;
691        let mut failed = 0u64;
692        for chunk in rows.chunks(BATCH) {
693            let mut embedded: Vec<(Uuid, Vec<f32>)> = Vec::with_capacity(chunk.len());
694            for (id, context, content) in chunk {
695                let body = body_of(context.clone(), content.clone());
696                match embed(&body) {
697                    Ok(v) if v.len() == EMBEDDING_DIM => embedded.push((*id, v)),
698                    Ok(v) => {
699                        failed += 1;
700                        tracing::warn!(
701                            memory_id = %id,
702                            dim = v.len(),
703                            "embedding backfill produced the wrong dimension"
704                        );
705                    }
706                    Err(e) => {
707                        failed += 1;
708                        tracing::warn!(memory_id = %id, "embedding backfill failed: {e:#}");
709                    }
710                }
711            }
712            done += self.with_txn(|tx| {
713                let mut n = 0u64;
714                for (id, vector) in &embedded {
715                    let changed = tx.execute(
716                        "UPDATE memories SET embedding = ?2, updated_at = ?3 \
717                         WHERE id = ?1 AND embedding IS NULL",
718                        params![
719                            id.to_string(),
720                            embedding_to_blob(vector),
721                            Utc::now().to_rfc3339()
722                        ],
723                    )?;
724                    if changed == 0 {
725                        continue;
726                    }
727                    Self::append_outbox(tx, *id, "upsert")?;
728                    if let Some(memory) = Self::memory_on_tx(tx, *id)? {
729                        self.log_memory_update(tx, &memory)?;
730                    }
731                    n += 1;
732                }
733                Ok(n)
734            })?;
735        }
736        Ok((done, failed))
737    }
738
739    /// Embeds the chunk sets of up to `max_memories` memories that need
740    /// them and stores each set whole via `store_chunks`, which queues the
741    /// index upsert. Texts go to `embed` in batches of at most 16 (a padded
742    /// ONNX batch of 64 held about 2.4 GB of arena, past the Pi's cap), whole
743    /// memories per batch; once `budget` has elapsed no further batch
744    /// starts, so a caller on a shared thread gets it back promptly. A
745    /// memory any of whose vectors fails or has the wrong dimension counts
746    /// as failed and is left for a later call. Records the remaining
747    /// pending count for `chunk_progress`.
748    pub fn backfill_chunk_embeddings(
749        &self,
750        embed: EmbedBatchFn<'_>,
751        max_memories: usize,
752        budget: std::time::Duration,
753    ) -> Result<ChunkBackfill> {
754        const BATCH: usize = 16;
755        let started = std::time::Instant::now();
756        let (work, pending) = self.scan_chunk_work(max_memories)?;
757        let mut out = ChunkBackfill::default();
758        let mut rest = work.as_slice();
759        while !rest.is_empty() && (out.memories + out.failed == 0 || started.elapsed() < budget) {
760            let mut take = 1;
761            let mut texts = rest[0].chunks.len();
762            while take < rest.len() && texts + rest[take].chunks.len() <= BATCH {
763                texts += rest[take].chunks.len();
764                take += 1;
765            }
766            let (batch, tail) = rest.split_at(take);
767            rest = tail;
768            let refs: Vec<&str> = batch
769                .iter()
770                .flat_map(|w| w.chunks.iter().map(String::as_str))
771                .collect();
772            let vectors = match embed(&refs) {
773                Ok(v) if v.len() == refs.len() => v,
774                Ok(v) => {
775                    out.failed += batch.len() as u64;
776                    tracing::warn!(
777                        expected = refs.len(),
778                        got = v.len(),
779                        "chunk embedding returned the wrong number of vectors"
780                    );
781                    continue;
782                }
783                Err(e) => {
784                    out.failed += batch.len() as u64;
785                    tracing::warn!("chunk embedding failed: {e:#}");
786                    continue;
787                }
788            };
789            let mut vectors = vectors.into_iter();
790            for w in batch {
791                let set: Vec<(usize, String, Vec<f32>)> = w
792                    .chunks
793                    .iter()
794                    .cloned()
795                    .enumerate()
796                    .zip(vectors.by_ref())
797                    .map(|((ord, text), v)| (ord, text, v))
798                    .collect();
799                if set.iter().any(|(_, _, v)| v.len() != EMBEDDING_DIM) {
800                    out.failed += 1;
801                    tracing::warn!(memory_id = %w.memory_id, "chunk embedding had the wrong dimension");
802                    continue;
803                }
804                if self.store_chunks(w.memory_id, &w.content_hash, &set)? {
805                    out.memories += 1;
806                    out.chunks += set.len() as u64;
807                }
808            }
809        }
810        out.pending = (pending as u64).saturating_sub(out.memories);
811        self.set_chunks_pending(out.pending as usize)?;
812        Ok(out)
813    }
814
815    /// Whether a sync_log row with this local_seq still exists; a test and
816    /// diagnostic hook for the sync log garbage collector.
817    pub fn sync_log_row_exists(&self, local_seq: u64) -> Result<bool> {
818        let conn = self.reader();
819        let hit: Option<i64> = conn
820            .query_row(
821                "SELECT 1 FROM sync_log WHERE local_seq = ?1",
822                params![local_seq as i64],
823                |r| r.get(0),
824            )
825            .optional()?;
826        Ok(hit.is_some())
827    }
828}