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