second-brain-core 0.13.5

Core library for second-brain: SQLite storage with vector and lexical indexes, BGE embeddings, and hybrid retrieval
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
//! Store invariants, measured and repaired in place.
//!
//! Every past cleanup was a one-off data fix plus a CLI command nobody ran
//! again, so the same drift came back within weeks. `integrity_report`
//! measures each invariant with one query; `repair` restores them in
//! idempotent, separately committed steps that never hard delete a memory.
//! The daemon runs both on a schedule from the thread that owns the writable
//! indexes, so a repair always has exclusive index write access.

use std::collections::HashSet;

use anyhow::Result;
use chrono::{DateTime, Utc};
use rusqlite::{OptionalExtension, params};
use serde::{Deserialize, Serialize};
use uuid::Uuid;

use crate::cold::{stub_lead_sql, stub_matches};
use crate::index::{EMBEDDING_DIM, Indexes};
use crate::schema::distillable_source;
use crate::sqlite_store::{SqliteStore, body_of, embedding_to_blob, parse_uuid, sync_dedup};
use crate::store::StoreAdmin;

const HOT: &str = "tier = 'hot' AND superseded_by IS NULL";
/// Vault notes stay out of dedup: the vault sync keeps one live row per
/// note file, and two identical files collapsed onto one row would lose
/// the survivor's content from recall once the other file is deleted.
const DEDUP_CANDIDATE: &str = "tier = 'hot' AND superseded_by IS NULL AND source <> 'markdown'";

/// Relations whose endpoints no longer resolve. Must stay identical to the
/// predicate `prune_dangling_relations` deletes by, so the report and the
/// repair agree on what dangling means.
const DANGLING_RELATIONS: &str = "from_id NOT IN (SELECT id FROM memories) \
     OR (relation_type = 'mentions' AND to_id NOT IN (SELECT id FROM entities)) \
     OR (relation_type = 'derived_from' AND to_id NOT IN (SELECT id FROM conversations)) \
     OR (relation_type NOT IN ('mentions', 'derived_from') \
         AND to_id NOT IN (SELECT id FROM memories))";

/// A `vec_keys` row with no live vector behind it: the memory is gone,
/// retired, superseded, or never had an embedding to index.
const DEAD_VEC_KEYS: &str = "SELECT COUNT(*) FROM vec_keys vk \
     LEFT JOIN memories m ON m.id = vk.memory_id \
     WHERE m.id IS NULL OR m.tier <> 'hot' OR m.superseded_by IS NOT NULL \
        OR m.embedding IS NULL";

/// Sync log rows no peer can ever need again, and which a peer at any cursor
/// position converges without. A memory create or update that a later
/// delete for the same id supersedes: a peer behind the create receives the
/// delete and tombstones an id it never held; a peer between them receives
/// the delete. A relation create whose endpoint memory is tombstoned: the
/// import side already skips it as a dangling endpoint.
const RECLAIMABLE_SYNC_LOG: &str = "SELECT s.local_seq FROM sync_log s \
     WHERE ((s.node_type = 'memory' AND s.op IN ('create', 'update') \
             AND EXISTS (SELECT 1 FROM sync_log d \
                         WHERE d.node_type = 'memory' AND d.node_id = s.node_id \
                           AND d.op = 'delete' AND d.local_seq > s.local_seq)) \
         OR (s.node_type = 'relation' AND s.op = 'create' \
             AND (substr(s.node_id, 1, 36) IN (SELECT node_id FROM tombstones) \
                  OR substr(s.node_id, 38, 36) IN (SELECT node_id FROM tombstones))))";

/// A distillation marker on a hot raw whose distillate was deleted. The
/// marker hides the raw from consolidation while nothing distilled remains,
/// so the knowledge is stuck in raw form forever. Only a tombstoned
/// distillate counts: on a peer the marker routinely arrives before the
/// distillate memory does, and dropping the marker then would make the
/// peer redistill, which is exactly the waste the marker exists to stop.
const ORPHANED_MARKERS: &str = "SELECT dl.memory_id FROM distillation_log dl \
     JOIN memories m ON m.id = dl.memory_id \
     WHERE m.tier = 'hot' AND m.superseded_by IS NULL \
       AND dl.memory_id <> dl.distilled_id \
       AND NOT EXISTS (SELECT 1 FROM memories d WHERE d.id = dl.distilled_id) \
       AND dl.distilled_id IN (SELECT node_id FROM tombstones)";

macro_rules! inheriting_keeper {
    () => {
        concat!(
            "k.tier = 'hot' AND k.superseded_by IS NULL AND ",
            distillable_source!("k.source"),
            " AND k.id NOT IN (SELECT memory_id FROM distillation_log) \
               AND (dl.memory_id = dl.distilled_id \
                    OR EXISTS (SELECT 1 FROM memories d WHERE d.id = dl.distilled_id))"
        )
    };
}

/// Hot raws with no marker whose exact text was already distilled or
/// skipped under another id (a duplicate, whatever its tier now). Without
/// the marker the daily job distills the same text again and mints a
/// second distillate. Grouped by keeper so each inherits one marker.
const INHERITABLE_MARKERS: &str = concat!(
    "SELECT k.id, dl.distilled_id, dl.model, \
       dl.memory_id = dl.distilled_id AS skip \
     FROM memories k \
     JOIN memories t ON t.content_prefix = k.content_prefix AND t.content = k.content \
                    AND t.id <> k.id \
     JOIN distillation_log dl ON dl.memory_id = t.id \
     WHERE ",
    inheriting_keeper!(),
    " GROUP BY k.id"
);

/// `INHERITABLE_MARKERS` for a twin in the cold tier, whose row holds a
/// stub: candidates share the stub's leading text, and the caller keeps
/// those whose whole text matches the stub's digest.
fn inheritable_cold_markers() -> String {
    format!(
        "SELECT k.id, dl.distilled_id, dl.model, dl.memory_id = dl.distilled_id AS skip, \
           CAST(k.content AS BLOB), t.content \
         FROM distillation_log dl \
         JOIN memories t ON t.id = dl.memory_id AND t.archived_segment IS NOT NULL \
         JOIN memories k ON k.content_prefix = {lead} AND k.id <> t.id \
         WHERE {keeper}",
        lead = stub_lead_sql("t.content"),
        keeper = inheriting_keeper!(),
    )
}

#[derive(Debug, Clone, Default, Serialize, Deserialize)]
pub struct IntegrityReport {
    pub memories_total: u64,
    pub hot: u64,
    pub archived: u64,
    pub superseded: u64,
    pub distilled_live: u64,
    pub raw_pending: u64,
    pub raw_retired: u64,
    pub raw_retire_pending: u64,
    /// Hot, unsuperseded rows with no stored embedding.
    pub missing_embeddings: u64,
    /// Groups of hot rows sharing byte-identical content, vault notes aside.
    pub duplicate_hot_groups: u64,
    /// Hot raws marked as distilled whose distillate no longer exists.
    pub orphaned_distillation_markers: u64,
    pub dead_vec_keys: u64,
    pub dangling_relations: u64,
    pub sync_log_rows: u64,
    pub sync_log_reclaimable_rows: u64,
    pub tombstones: u64,
    /// `sync_state` cursors keyed by something other than a peer machine id.
    pub stale_peer_cursors: u64,
    pub outbox_pending: u64,
    pub index_vectors: Option<u64>,
    pub index_docs: Option<u64>,
    pub index_consistent: Option<bool>,
    pub db_bytes: u64,
    pub freelist_bytes: u64,
}

impl IntegrityReport {
    /// The fields a repair is expected to bring to zero (or true). Anything
    /// else in the report is descriptive.
    pub fn drift(&self) -> Vec<(&'static str, u64)> {
        let mut out = Vec::new();
        let mut push = |name: &'static str, value: u64| {
            if value > 0 {
                out.push((name, value));
            }
        };
        push("raw_retire_pending", self.raw_retire_pending);
        push("duplicate_hot_groups", self.duplicate_hot_groups);
        push(
            "orphaned_distillation_markers",
            self.orphaned_distillation_markers,
        );
        push("dead_vec_keys", self.dead_vec_keys);
        push("dangling_relations", self.dangling_relations);
        push("sync_log_reclaimable_rows", self.sync_log_reclaimable_rows);
        push("stale_peer_cursors", self.stale_peer_cursors);
        push("missing_embeddings", self.missing_embeddings);
        if self.index_consistent == Some(false) {
            push("index_inconsistent", 1);
        }
        out
    }
}

#[derive(Debug, Clone, Default, Serialize, Deserialize)]
pub struct RepairStats {
    pub dry_run: bool,
    pub duplicates_superseded: u64,
    pub distillation_markers_inherited: u64,
    pub orphaned_markers_dropped: u64,
    pub raws_retired: u64,
    pub relations_pruned: u64,
    pub vec_keys_deleted: u64,
    /// True when the outbox still held undrained rows, so the vector index
    /// could not be trusted to have already dropped the keys being judged.
    pub vec_keys_gc_skipped: bool,
    pub sync_log_rows_deleted: u64,
    pub stale_cursors_dropped: u64,
    pub embeddings_backfilled: u64,
    pub embedding_failures: u64,
    pub index_rebuilt: bool,
}

impl RepairStats {
    pub fn is_clean(&self) -> bool {
        self.raws_retired == 0
            && self.duplicates_superseded == 0
            && self.distillation_markers_inherited == 0
            && self.orphaned_markers_dropped == 0
            && self.relations_pruned == 0
            && self.vec_keys_deleted == 0
            && !self.vec_keys_gc_skipped
            && self.sync_log_rows_deleted == 0
            && self.stale_cursors_dropped == 0
            && self.embeddings_backfilled == 0
            && self.embedding_failures == 0
            && !self.index_rebuilt
    }
}

/// What the daemon remembers about its most recent repair, cheap enough for
/// `/v1/status` to return without recomputing anything.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct MaintenanceSummary {
    pub ran_at: DateTime<Utc>,
    pub stats: RepairStats,
    pub report_after: IntegrityReport,
}

/// Memory counts cheap enough for every `/v1/status` call: one grouped
/// scan, unlike `integrity_report`. `live + archived + superseded` is
/// `stored_total`; a row both archived and superseded counts as archived.
#[derive(Debug, Clone, Default, PartialEq, Eq, Serialize, Deserialize)]
pub struct LiveCounts {
    pub stored_total: u64,
    /// Hot and unsuperseded: what recall and context can return.
    pub live: u64,
    pub archived: u64,
    pub superseded: u64,
    /// Live memories per memory type.
    pub by_type: std::collections::BTreeMap<String, u64>,
}

pub type EmbedFn<'a> = &'a dyn Fn(&str) -> Result<Vec<f32>>;

/// Document-side batch embedding (no query prefix), one vector per text.
pub type EmbedBatchFn<'a> = &'a dyn Fn(&[&str]) -> Result<Vec<Vec<f32>>>;

#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
pub struct ChunkBackfill {
    pub memories: u64,
    pub chunks: u64,
    pub failed: u64,
    /// Memories still needing chunks after this call.
    pub pending: u64,
}

/// A `sync_state` cursor whose peer is not a machine id. Sync used to key
/// cursors by the raw ssh host string; those rows can never advance because
/// nothing writes them any more.
pub(crate) fn is_stale_cursor(peer_id: &str) -> bool {
    let bare = peer_id.strip_prefix("push:").unwrap_or(peer_id);
    Uuid::parse_str(bare).is_err()
}

pub(crate) fn live_counts_on(conn: &rusqlite::Connection) -> Result<LiveCounts> {
    let mut stmt = conn.prepare(
        "SELECT CASE WHEN tier = 'archived' THEN 'archived' \
                     WHEN superseded_by IS NOT NULL THEN 'superseded' \
                     ELSE 'live' END, memory_type, COUNT(*) \
         FROM memories GROUP BY 1, 2",
    )?;
    let rows = stmt.query_map([], |r| {
        Ok((
            r.get::<_, String>(0)?,
            r.get::<_, String>(1)?,
            r.get::<_, i64>(2)? as u64,
        ))
    })?;
    let mut counts = LiveCounts::default();
    for row in rows {
        let (state, memory_type, n) = row?;
        counts.stored_total += n;
        match state.as_str() {
            "archived" => counts.archived += n,
            "superseded" => counts.superseded += n,
            _ => {
                counts.live += n;
                *counts.by_type.entry(memory_type).or_default() += n;
            }
        }
    }
    Ok(counts)
}

impl SqliteStore {
    pub fn live_counts(&self) -> Result<LiveCounts> {
        live_counts_on(&self.reader())
    }

    /// Outbox rows the index drain has not applied yet. While any are
    /// pending the index counts lag the store by design, so a count
    /// mismatch then is not drift.
    pub fn outbox_pending(&self) -> Result<u64> {
        let watermark = self.index_watermark()?;
        let n: i64 = self.reader().query_row(
            "SELECT COUNT(*) FROM index_outbox WHERE seq > ?1",
            params![watermark],
            |r| r.get(0),
        )?;
        Ok(n as u64)
    }

    pub fn integrity_report(&self, indexes: Option<&Indexes>) -> Result<IntegrityReport> {
        let consolidation = self.consolidation_stats()?;
        let stale_peer_cursors = self
            .get_all_sync_states()?
            .iter()
            .filter(|s| is_stale_cursor(&s.peer_id))
            .count() as u64;
        let (index_vectors, index_docs, index_consistent) = match indexes {
            Some(idx) => (
                Some(idx.vector_len() as u64),
                Some(idx.lexical_len()),
                Some(idx.consistent_with(self)?),
            ),
            None => (None, None, None),
        };
        // An in-memory store's reader is its writer mutex, and a pooled
        // checkout blocks, so nothing below may take a second connection.
        let conn = self.reader();
        let count = |sql: &str| -> Result<u64> {
            let n: i64 = conn.query_row(sql, [], |r| r.get(0))?;
            Ok(n as u64)
        };
        let page_size = crate::sqlite_store::page_size(&conn)? as u64;
        Ok(IntegrityReport {
            memories_total: count("SELECT COUNT(*) FROM memories")?,
            hot: count(&format!("SELECT COUNT(*) FROM memories WHERE {HOT}"))?,
            archived: count("SELECT COUNT(*) FROM memories WHERE tier = 'archived'")?,
            superseded: count("SELECT COUNT(*) FROM memories WHERE superseded_by IS NOT NULL")?,
            distilled_live: consolidation.distilled_live,
            raw_pending: consolidation.raw_pending,
            raw_retired: consolidation.raw_retired,
            raw_retire_pending: consolidation.raw_retire_pending,
            missing_embeddings: count(&format!(
                "SELECT COUNT(*) FROM memories WHERE {HOT} AND embedding IS NULL"
            ))?,
            duplicate_hot_groups: count(&format!(
                "SELECT COUNT(*) FROM (SELECT 1 FROM memories WHERE {DEDUP_CANDIDATE} \
                 AND content_prefix IN (SELECT content_prefix FROM memories \
                                        WHERE {DEDUP_CANDIDATE} \
                                        GROUP BY content_prefix HAVING COUNT(*) > 1) \
                 GROUP BY content HAVING COUNT(*) > 1)"
            ))?,
            orphaned_distillation_markers: count(&format!(
                "SELECT COUNT(*) FROM ({ORPHANED_MARKERS})"
            ))?,
            dead_vec_keys: count(DEAD_VEC_KEYS)?,
            dangling_relations: count(&format!(
                "SELECT COUNT(*) FROM relations WHERE {DANGLING_RELATIONS}"
            ))?,
            sync_log_rows: count("SELECT COUNT(*) FROM sync_log")?,
            sync_log_reclaimable_rows: count(&format!(
                "SELECT COUNT(*) FROM ({RECLAIMABLE_SYNC_LOG})"
            ))?,
            tombstones: count("SELECT COUNT(*) FROM tombstones")?,
            stale_peer_cursors,
            outbox_pending: count("SELECT COUNT(*) FROM index_outbox")?,
            index_vectors,
            index_docs,
            index_consistent,
            db_bytes: count("PRAGMA page_count")? * page_size,
            freelist_bytes: count("PRAGMA freelist_count")? * page_size,
        })
    }

    /// Restores every invariant in `IntegrityReport::drift`, each step in
    /// its own transactions so a failure part-way leaves earlier steps
    /// committed. With `indexes`, the outbox is drained before the key
    /// garbage collection and before the consistency check, so one pass
    /// leaves nothing for the next. `dry_run` counts without writing.
    pub fn repair(
        &self,
        mut indexes: Option<&mut Indexes>,
        embed: Option<EmbedFn<'_>>,
        dry_run: bool,
    ) -> Result<RepairStats> {
        let mut stats = RepairStats {
            dry_run,
            ..Default::default()
        };
        // Dedup first so a keeper can inherit its twin's marker and be
        // retired in this same pass instead of the next one.
        stats.duplicates_superseded = self.dedup_hot_memories(dry_run)?;
        stats.distillation_markers_inherited = self.inherit_distillation_markers(dry_run)?;
        stats.orphaned_markers_dropped = self.drop_orphaned_markers(dry_run)?;
        stats.raws_retired = if dry_run {
            self.consolidation_stats()?.raw_retire_pending
        } else {
            self.retire_consolidated_raws()? as u64
        };
        stats.relations_pruned = if dry_run {
            self.count_dangling_relations()?
        } else {
            self.prune_dangling_relations()?
        };
        if let Some(idx) = indexes.as_deref_mut()
            && !dry_run
        {
            idx.drain(self)?;
        }
        match self.gc_vec_keys(dry_run)? {
            Some(n) => stats.vec_keys_deleted = n,
            None => stats.vec_keys_gc_skipped = true,
        }
        stats.sync_log_rows_deleted = self.gc_sync_log(dry_run)?;
        stats.stale_cursors_dropped = self.drop_stale_peer_cursors(dry_run)?;
        if let Some(embed) = embed {
            let (done, failed) = self.backfill_embeddings(embed, dry_run)?;
            stats.embeddings_backfilled = done;
            stats.embedding_failures = failed;
        }
        if let Some(idx) = indexes {
            if !dry_run {
                idx.drain(self)?;
            }
            if !idx.consistent_with(self)? {
                stats.index_rebuilt = true;
                if !dry_run {
                    idx.rebuild(self)?;
                }
            }
        }
        Ok(stats)
    }

    /// Byte-identical hot rows collapse onto the oldest one (ties broken by
    /// id). Losers are superseded, never deleted, so their relations and
    /// distillation history stay resolvable. Grouping is by full content:
    /// the 128-char prefix is far too coarse for short messages.
    fn dedup_hot_memories(&self, dry_run: bool) -> Result<u64> {
        const BATCH: usize = 500;
        let mut rows: Vec<(String, DateTime<Utc>, Uuid)> = {
            let conn = self.reader();
            let mut stmt = conn.prepare(&format!(
                "SELECT id, content, created_at FROM memories WHERE {DEDUP_CANDIDATE} \
                 AND content_prefix IN (SELECT content_prefix FROM memories \
                                        WHERE {DEDUP_CANDIDATE} \
                                        GROUP BY content_prefix HAVING COUNT(*) > 1)"
            ))?;
            let rows = stmt.query_map([], |r| {
                // Stored timestamps mix fractional-second widths, so text
                // order is not time order; parse before choosing the oldest.
                let created = DateTime::parse_from_rfc3339(&r.get::<_, String>(2)?)
                    .map(|d| d.with_timezone(&Utc))
                    .unwrap_or(DateTime::UNIX_EPOCH);
                Ok((
                    r.get::<_, String>(1)?,
                    created,
                    parse_uuid(&r.get::<_, String>(0)?)?,
                ))
            })?;
            rows.collect::<rusqlite::Result<Vec<_>>>()?
        };
        rows.sort();
        let mut pairs: Vec<(Uuid, Uuid)> = Vec::new();
        let mut keeper: Option<(&Uuid, &str)> = None;
        for (content, _, id) in &rows {
            match keeper {
                Some((keep_id, keep_content)) if keep_content == content => {
                    pairs.push((*id, *keep_id));
                }
                _ => keeper = Some((id, content)),
            }
        }
        if dry_run {
            return Ok(pairs.len() as u64);
        }
        let mut superseded = 0u64;
        for chunk in pairs.chunks(BATCH) {
            superseded += self.with_txn(|tx| {
                let mut n = 0u64;
                for (loser, winner) in chunk {
                    if self.supersede_on_tx(tx, *loser, *winner)? {
                        n += 1;
                    }
                }
                Ok(n)
            })?;
        }
        Ok(superseded)
    }

    fn inherit_distillation_markers(&self, dry_run: bool) -> Result<u64> {
        let rows: Vec<(Uuid, Uuid, Option<String>, bool)> = {
            let conn = self.reader();
            let mut stmt = conn.prepare(INHERITABLE_MARKERS)?;
            let rows = stmt.query_map([], |r| {
                Ok((
                    parse_uuid(&r.get::<_, String>(0)?)?,
                    parse_uuid(&r.get::<_, String>(1)?)?,
                    r.get::<_, Option<String>>(2)?,
                    r.get::<_, bool>(3)?,
                ))
            })?;
            rows.collect::<rusqlite::Result<Vec<_>>>()?
        };
        let mut rows = rows;
        let mut keepers: HashSet<Uuid> = rows.iter().map(|r| r.0).collect();
        for (keeper, distilled_id, model, skip) in self.cold_twin_markers()? {
            if keepers.insert(keeper) {
                rows.push((keeper, distilled_id, model, skip));
            }
        }
        if dry_run {
            return Ok(rows.len() as u64);
        }
        for (keeper, distilled_id, model, skip) in &rows {
            // A skip marker points at itself; keep that shape for the keeper
            // rather than pointing it at a twin that is not a distillate.
            let target = if *skip { *keeper } else { *distilled_id };
            self.mark_consolidated(*keeper, target, model.as_deref().unwrap_or("inherited"))?;
        }
        Ok(rows.len() as u64)
    }

    fn cold_twin_markers(&self) -> Result<Vec<(Uuid, Uuid, Option<String>, bool)>> {
        let conn = self.reader();
        let mut stmt = conn.prepare(&inheritable_cold_markers())?;
        let rows = stmt.query_map([], |r| {
            Ok((
                parse_uuid(&r.get::<_, String>(0)?)?,
                parse_uuid(&r.get::<_, String>(1)?)?,
                r.get::<_, Option<String>>(2)?,
                r.get::<_, bool>(3)?,
                r.get::<_, Vec<u8>>(4)?,
                r.get::<_, String>(5)?,
            ))
        })?;
        let mut out = Vec::new();
        for row in rows {
            let (keeper, distilled_id, model, skip, content, stub) = row?;
            if String::from_utf8(content).is_ok_and(|c| stub_matches(&stub, &c)) {
                out.push((keeper, distilled_id, model, skip));
            }
        }
        Ok(out)
    }

    /// Clears the marker so consolidation picks the raw up again, and logs
    /// the removal so peers stop treating it as spoken for.
    fn drop_orphaned_markers(&self, dry_run: bool) -> Result<u64> {
        let ids: Vec<Uuid> = {
            let conn = self.reader();
            let mut stmt = conn.prepare(ORPHANED_MARKERS)?;
            let rows = stmt.query_map([], |r| parse_uuid(&r.get::<_, String>(0)?))?;
            rows.collect::<rusqlite::Result<Vec<_>>>()?
        };
        if dry_run {
            return Ok(ids.len() as u64);
        }
        let mut dropped = 0u64;
        for id in &ids {
            dropped += self.with_txn(|tx| Ok(u64::from(self.drop_marker_on_tx(tx, *id)?)))?;
        }
        Ok(dropped)
    }

    fn count_dangling_relations(&self) -> Result<u64> {
        let conn = self.reader();
        let n: i64 = conn.query_row(
            &format!("SELECT COUNT(*) FROM relations WHERE {DANGLING_RELATIONS}"),
            [],
            |r| r.get(0),
        )?;
        Ok(n as u64)
    }

    /// `None` when the gate fails: with rows past the watermark the vector
    /// index may still hold a key this would delete, stranding the vector
    /// until the next full rebuild.
    fn gc_vec_keys(&self, dry_run: bool) -> Result<Option<u64>> {
        let watermark = self.index_watermark()?;
        let pending: i64 = {
            let conn = self.reader();
            conn.query_row(
                "SELECT COUNT(*) FROM index_outbox WHERE seq > ?1",
                params![watermark],
                |r| r.get(0),
            )?
        };
        if pending > 0 {
            return Ok(None);
        }
        if dry_run {
            let conn = self.reader();
            let n: i64 = conn.query_row(DEAD_VEC_KEYS, [], |r| r.get(0))?;
            return Ok(Some(n as u64));
        }
        let conn = self.conn();
        let n = conn.execute(
            "DELETE FROM vec_keys WHERE key IN (SELECT vk.key FROM vec_keys vk \
             LEFT JOIN memories m ON m.id = vk.memory_id \
             WHERE m.id IS NULL OR m.tier <> 'hot' OR m.superseded_by IS NOT NULL \
                OR m.embedding IS NULL)",
            [],
        )?;
        Ok(Some(n as u64))
    }

    /// Same temp-table and chunked-delete shape as `compact_sync_log`.
    fn gc_sync_log(&self, dry_run: bool) -> Result<u64> {
        let mut conn = self.conn();
        conn.execute_batch("PRAGMA cache_size = -262144;")?;
        conn.execute_batch("DROP TABLE IF EXISTS gc_victims;")?;
        conn.execute(
            &format!("CREATE TEMP TABLE gc_victims AS {RECLAIMABLE_SYNC_LOG}"),
            [],
        )?;
        conn.execute_batch("CREATE INDEX idx_gc_victims_seq ON gc_victims(local_seq);")?;
        let victims: i64 = conn.query_row("SELECT COUNT(*) FROM gc_victims", [], |r| r.get(0))?;
        let mut deleted = 0u64;
        if !dry_run {
            let mut cursor: i64 = 0;
            loop {
                let chunk: Vec<i64> = {
                    let mut stmt = conn.prepare(
                        "SELECT local_seq FROM gc_victims WHERE local_seq > ?1 \
                         ORDER BY local_seq LIMIT 20000",
                    )?;
                    let rows = stmt.query_map(params![cursor], |r| r.get::<_, i64>(0))?;
                    rows.collect::<rusqlite::Result<Vec<_>>>()?
                };
                let Some(last) = chunk.last().copied() else {
                    break;
                };
                cursor = last;
                let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(",");
                let tx = conn.transaction()?;
                sync_dedup::keep_keys_of(&tx, &chunk)?;
                deleted += tx.execute(
                    &format!("DELETE FROM sync_log WHERE local_seq IN ({placeholders})"),
                    rusqlite::params_from_iter(chunk.iter()),
                )? as u64;
                tx.commit()?;
            }
        }
        conn.execute_batch("DROP TABLE IF EXISTS gc_victims;")?;
        Ok(if dry_run { victims as u64 } else { deleted })
    }

    fn drop_stale_peer_cursors(&self, dry_run: bool) -> Result<u64> {
        let stale: Vec<String> = self
            .get_all_sync_states()?
            .into_iter()
            .filter(|s| is_stale_cursor(&s.peer_id))
            .map(|s| s.peer_id)
            .collect();
        if dry_run {
            return Ok(stale.len() as u64);
        }
        let conn = self.conn();
        let mut dropped = 0u64;
        for peer_id in &stale {
            dropped += conn.execute(
                "DELETE FROM sync_state WHERE peer_id = ?1",
                params![peer_id],
            )? as u64;
        }
        Ok(dropped)
    }

    /// Embeds hot rows that have none, over the same context-plus-content
    /// body the ingest path embeds. `updated_at` is bumped so the peer's
    /// last-writer-wins check accepts the row that now carries a vector.
    /// Runs only this step of `repair`, so a caller that must not merge or
    /// retire rows can fill vectors on its own schedule. Returns (rows
    /// embedded, rows whose embedding failed or had the wrong dimension);
    /// with `dry_run` it counts rows awaiting a vector and writes nothing.
    pub fn backfill_embeddings(&self, embed: EmbedFn<'_>, dry_run: bool) -> Result<(u64, u64)> {
        const BATCH: usize = 64;
        let rows: Vec<(Uuid, Option<String>, String)> = {
            let conn = self.reader();
            let mut stmt = conn.prepare(&format!(
                "SELECT id, context, content FROM memories WHERE {HOT} AND embedding IS NULL \
                 ORDER BY created_at"
            ))?;
            let rows = stmt.query_map([], |r| {
                Ok((
                    parse_uuid(&r.get::<_, String>(0)?)?,
                    r.get::<_, Option<String>>(1)?,
                    r.get::<_, String>(2)?,
                ))
            })?;
            rows.collect::<rusqlite::Result<Vec<_>>>()?
        };
        if dry_run {
            return Ok((rows.len() as u64, 0));
        }
        let mut done = 0u64;
        let mut failed = 0u64;
        for chunk in rows.chunks(BATCH) {
            let mut embedded: Vec<(Uuid, Vec<f32>)> = Vec::with_capacity(chunk.len());
            for (id, context, content) in chunk {
                let body = body_of(context.clone(), content.clone());
                match embed(&body) {
                    Ok(v) if v.len() == EMBEDDING_DIM => embedded.push((*id, v)),
                    Ok(v) => {
                        failed += 1;
                        tracing::warn!(
                            memory_id = %id,
                            dim = v.len(),
                            "embedding backfill produced the wrong dimension"
                        );
                    }
                    Err(e) => {
                        failed += 1;
                        tracing::warn!(memory_id = %id, "embedding backfill failed: {e:#}");
                    }
                }
            }
            done += self.with_txn(|tx| {
                let mut n = 0u64;
                for (id, vector) in &embedded {
                    let changed = tx.execute(
                        "UPDATE memories SET embedding = ?2, updated_at = ?3 \
                         WHERE id = ?1 AND embedding IS NULL",
                        params![
                            id.to_string(),
                            embedding_to_blob(vector),
                            Utc::now().to_rfc3339()
                        ],
                    )?;
                    if changed == 0 {
                        continue;
                    }
                    Self::append_outbox(tx, *id, "upsert")?;
                    if let Some(memory) = Self::memory_on_tx(tx, *id)? {
                        self.log_memory_update(tx, &memory)?;
                    }
                    n += 1;
                }
                Ok(n)
            })?;
        }
        Ok((done, failed))
    }

    /// Embeds the chunk sets of up to `max_memories` memories that need
    /// them and stores each set whole via `store_chunks`, which queues the
    /// index upsert. Texts go to `embed` in batches of at most 16 (a padded
    /// ONNX batch of 64 held about 2.4 GB of arena, past the Pi's cap), whole
    /// memories per batch; once `budget` has elapsed no further batch
    /// starts, so a caller on a shared thread gets it back promptly. A
    /// memory any of whose vectors fails or has the wrong dimension counts
    /// as failed and is left for a later call. Records the remaining
    /// pending count for `chunk_progress`.
    pub fn backfill_chunk_embeddings(
        &self,
        embed: EmbedBatchFn<'_>,
        max_memories: usize,
        budget: std::time::Duration,
    ) -> Result<ChunkBackfill> {
        const BATCH: usize = 16;
        let started = std::time::Instant::now();
        let (work, pending) = self.scan_chunk_work(max_memories)?;
        let mut out = ChunkBackfill::default();
        let mut rest = work.as_slice();
        while !rest.is_empty() && (out.memories + out.failed == 0 || started.elapsed() < budget) {
            let mut take = 1;
            let mut texts = rest[0].chunks.len();
            while take < rest.len() && texts + rest[take].chunks.len() <= BATCH {
                texts += rest[take].chunks.len();
                take += 1;
            }
            let (batch, tail) = rest.split_at(take);
            rest = tail;
            let refs: Vec<&str> = batch
                .iter()
                .flat_map(|w| w.chunks.iter().map(String::as_str))
                .collect();
            let vectors = match embed(&refs) {
                Ok(v) if v.len() == refs.len() => v,
                Ok(v) => {
                    out.failed += batch.len() as u64;
                    tracing::warn!(
                        expected = refs.len(),
                        got = v.len(),
                        "chunk embedding returned the wrong number of vectors"
                    );
                    continue;
                }
                Err(e) => {
                    out.failed += batch.len() as u64;
                    tracing::warn!("chunk embedding failed: {e:#}");
                    continue;
                }
            };
            let mut vectors = vectors.into_iter();
            for w in batch {
                let set: Vec<(usize, String, Vec<f32>)> = w
                    .chunks
                    .iter()
                    .cloned()
                    .enumerate()
                    .zip(vectors.by_ref())
                    .map(|((ord, text), v)| (ord, text, v))
                    .collect();
                if set.iter().any(|(_, _, v)| v.len() != EMBEDDING_DIM) {
                    out.failed += 1;
                    tracing::warn!(memory_id = %w.memory_id, "chunk embedding had the wrong dimension");
                    continue;
                }
                if self.store_chunks(w.memory_id, &w.content_hash, &set)? {
                    out.memories += 1;
                    out.chunks += set.len() as u64;
                }
            }
        }
        out.pending = (pending as u64).saturating_sub(out.memories);
        self.set_chunks_pending(out.pending as usize)?;
        Ok(out)
    }

    /// Whether a sync_log row with this local_seq still exists; a test and
    /// diagnostic hook for the sync log garbage collector.
    pub fn sync_log_row_exists(&self, local_seq: u64) -> Result<bool> {
        let conn = self.reader();
        let hit: Option<i64> = conn
            .query_row(
                "SELECT 1 FROM sync_log WHERE local_seq = ?1",
                params![local_seq as i64],
                |r| r.get(0),
            )
            .optional()?;
        Ok(hit.is_some())
    }
}