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