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