second-brain-core 0.13.0

Core library for second-brain: SQLite storage with vector and lexical indexes, BGE embeddings, and hybrid retrieval
Documentation
use anyhow::Result;
use rusqlite::{Connection, OptionalExtension, params, params_from_iter};

use super::clock::is_new_layout;
use super::sync_node_type_to_str;
use crate::schema::SyncRecord;

/// True when the key was received before: its relay row is still in
/// `sync_log`, or a range in `sync_dedup` covers it.
pub(super) fn seen(conn: &Connection, origin: &str, origin_seq: i64) -> Result<bool> {
    let logged: Option<i64> = conn
        .query_row(
            "SELECT 1 FROM sync_log WHERE id = ?1",
            params![format!("{origin}:{origin_seq}")],
            |r| r.get(0),
        )
        .optional()?;
    if logged.is_some() {
        return Ok(true);
    }
    let hi: Option<i64> = conn
        .query_row(
            "SELECT hi FROM sync_dedup WHERE origin = ?1 AND lo <= ?2 ORDER BY lo DESC LIMIT 1",
            params![origin, origin_seq],
            |r| r.get(0),
        )
        .optional()?;
    Ok(hi.is_some_and(|hi| hi >= origin_seq))
}

/// True when `sync_log` holds a later record for the same node from the same
/// origin; within one origin, origin_seq order is authoring order.
pub(super) fn superseded(conn: &Connection, record: &SyncRecord, origin_seq: i64) -> Result<bool> {
    let later: Option<i64> = conn
        .query_row(
            "SELECT 1 FROM sync_log WHERE node_type = ?1 AND node_id = ?2 \
             AND origin_machine_id = ?3 AND origin_seq > ?4 LIMIT 1",
            params![
                sync_node_type_to_str(record.node_type),
                record.node_id,
                record.origin_machine_id,
                origin_seq
            ],
            |r| r.get(0),
        )
        .optional()?;
    Ok(later.is_some())
}

pub(super) fn record(conn: &Connection, origin: &str, origin_seq: i64) -> Result<()> {
    merge(conn, origin, origin_seq, origin_seq)
}

pub(crate) fn merge_range(conn: &Connection, origin: &str, lo: i64, hi: i64) -> Result<()> {
    merge(conn, origin, lo, hi)
}

/// Adds `lo..=hi` to `origin`'s ranges, folding in every range it overlaps
/// or touches, so the ranges for one origin stay disjoint and non-adjacent.
fn merge(conn: &Connection, origin: &str, lo: i64, hi: i64) -> Result<()> {
    let nearest: Option<(i64, i64)> = conn
        .query_row(
            "SELECT lo, hi FROM sync_dedup WHERE origin = ?1 AND lo <= ?2 ORDER BY lo DESC LIMIT 1",
            params![origin, hi.saturating_add(1)],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .optional()?;
    match nearest {
        None => insert(conn, origin, lo, hi),
        Some((near_lo, near_hi)) if near_lo <= lo => {
            if near_hi < lo.saturating_sub(1) {
                insert(conn, origin, lo, hi)
            } else if near_hi < hi {
                conn.execute(
                    "UPDATE sync_dedup SET hi = ?3 WHERE origin = ?1 AND lo = ?2",
                    params![origin, near_lo, hi],
                )?;
                Ok(())
            } else {
                Ok(())
            }
        }
        Some(_) => fold(conn, origin, lo, hi),
    }
}

fn insert(conn: &Connection, origin: &str, lo: i64, hi: i64) -> Result<()> {
    conn.execute(
        "INSERT INTO sync_dedup (origin, lo, hi) VALUES (?1, ?2, ?3)",
        params![origin, lo, hi],
    )?;
    Ok(())
}

fn fold(conn: &Connection, origin: &str, lo: i64, hi: i64) -> Result<()> {
    let (mut new_lo, mut new_hi) = (lo, hi);
    let mut folded: Vec<i64> = Vec::new();
    let before: Option<(i64, i64)> = conn
        .query_row(
            "SELECT lo, hi FROM sync_dedup WHERE origin = ?1 AND lo < ?2 ORDER BY lo DESC LIMIT 1",
            params![origin, lo],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .optional()?;
    if let Some((before_lo, before_hi)) = before
        && before_hi >= lo.saturating_sub(1)
    {
        new_lo = before_lo;
        new_hi = new_hi.max(before_hi);
        folded.push(before_lo);
    }
    {
        let mut stmt = conn
            .prepare("SELECT lo, hi FROM sync_dedup WHERE origin = ?1 AND lo >= ?2 AND lo <= ?3")?;
        let rows = stmt.query_map(params![origin, lo, hi.saturating_add(1)], |r| {
            Ok((r.get::<_, i64>(0)?, r.get::<_, i64>(1)?))
        })?;
        for row in rows {
            let (range_lo, range_hi) = row?;
            new_hi = new_hi.max(range_hi);
            folded.push(range_lo);
        }
    }
    for range_lo in folded {
        conn.execute(
            "DELETE FROM sync_dedup WHERE origin = ?1 AND lo = ?2",
            params![origin, range_lo],
        )?;
    }
    insert(conn, origin, new_lo, new_hi)
}

/// Records the new-layout keys of the `sync_log` rows at `local_seqs` in
/// `sync_dedup`; run in the transaction that deletes those rows. Keys
/// settled since sync_dedup was filled at apply and mint time are already
/// covered; this keeps those of rows logged before.
pub(crate) fn keep_keys_of(conn: &Connection, local_seqs: &[i64]) -> Result<()> {
    if local_seqs.is_empty() {
        return Ok(());
    }
    let placeholders = local_seqs.iter().map(|_| "?").collect::<Vec<_>>().join(",");
    let mut keys: Vec<(String, i64)> = {
        let mut stmt = conn.prepare(&format!(
            "SELECT origin_machine_id, origin_seq FROM sync_log WHERE local_seq IN ({placeholders})"
        ))?;
        let rows = stmt.query_map(params_from_iter(local_seqs.iter()), |r| {
            Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?))
        })?;
        rows.collect::<rusqlite::Result<Vec<_>>>()?
    };
    keys.retain(|(origin, seq)| is_new_layout(origin, *seq));
    keys.sort();
    let mut keys = keys.into_iter();
    let Some((mut origin, first)) = keys.next() else {
        return Ok(());
    };
    let (mut lo, mut hi) = (first, first);
    for (next_origin, seq) in keys {
        if next_origin == origin && seq <= hi.saturating_add(1) {
            hi = hi.max(seq);
            continue;
        }
        merge(conn, &origin, lo, hi)?;
        (origin, lo, hi) = (next_origin, seq, seq);
    }
    merge(conn, &origin, lo, hi)
}

#[cfg(test)]
mod tests {
    use chrono::Utc;
    use rusqlite::{Connection, params};

    use super::{keep_keys_of, merge, record, seen};
    use crate::schema::{Memory, MemoryType, SyncNodeType, SyncOp, SyncRecord};
    use crate::sqlite_store::SqliteStore;
    use crate::store::{Store, StoreAdmin};

    fn ranges(conn: &Connection, origin: &str) -> Vec<(i64, i64)> {
        let mut stmt = conn
            .prepare("SELECT lo, hi FROM sync_dedup WHERE origin = ?1 ORDER BY lo")
            .unwrap();
        stmt.query_map(params![origin], |r| Ok((r.get(0)?, r.get(1)?)))
            .unwrap()
            .collect::<rusqlite::Result<Vec<_>>>()
            .unwrap()
    }

    #[test]
    fn single_keys_merge_once_the_gap_between_them_fills() {
        let store = SqliteStore::in_memory("m".to_string()).unwrap();
        let conn = store.conn();
        record(&conn, "a", 5).unwrap();
        record(&conn, "a", 7).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(5, 5), (7, 7)]);
        record(&conn, "a", 6).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(5, 7)]);
        record(&conn, "a", 6).unwrap();
        record(&conn, "a", 8).unwrap();
        record(&conn, "a", 4).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(4, 8)]);
    }

    #[test]
    fn overlapping_and_enclosing_ranges_fold_into_one() {
        let store = SqliteStore::in_memory("m".to_string()).unwrap();
        let conn = store.conn();
        merge(&conn, "a", 10, 20).unwrap();
        merge(&conn, "a", 30, 40).unwrap();
        merge(&conn, "a", 50, 60).unwrap();
        merge(&conn, "a", 15, 32).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(10, 40), (50, 60)]);
        merge(&conn, "a", 12, 18).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(10, 40), (50, 60)]);
        merge(&conn, "a", 1, 100).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(1, 100)]);
    }

    #[test]
    fn origins_keep_separate_ranges() {
        let store = SqliteStore::in_memory("m".to_string()).unwrap();
        let conn = store.conn();
        record(&conn, "a", 5).unwrap();
        record(&conn, "b", 6).unwrap();
        assert_eq!(ranges(&conn, "a"), vec![(5, 5)]);
        assert_eq!(ranges(&conn, "b"), vec![(6, 6)]);
        assert!(seen(&conn, "a", 5).unwrap());
        assert!(!seen(&conn, "a", 6).unwrap());
        assert!(!seen(&conn, "b", 5).unwrap());
    }

    #[test]
    fn keys_at_the_ends_of_the_i64_range_do_not_overflow() {
        let store = SqliteStore::in_memory("m".to_string()).unwrap();
        let conn = store.conn();
        record(&conn, "a", i64::MAX).unwrap();
        record(&conn, "a", i64::MAX - 1).unwrap();
        record(&conn, "a", i64::MIN).unwrap();
        assert_eq!(
            ranges(&conn, "a"),
            vec![(i64::MIN, i64::MIN), (i64::MAX - 1, i64::MAX)]
        );
    }

    #[test]
    fn seen_covers_live_rows_and_ranges() {
        let store = SqliteStore::in_memory("m".to_string()).unwrap();
        let conn = store.conn();
        merge(&conn, "a", 100, 200).unwrap();
        assert!(seen(&conn, "a", 100).unwrap());
        assert!(seen(&conn, "a", 150).unwrap());
        assert!(seen(&conn, "a", 200).unwrap());
        assert!(!seen(&conn, "a", 99).unwrap());
        assert!(!seen(&conn, "a", 201).unwrap());
        conn.execute(
            "INSERT INTO sync_log (id, local_seq, origin_machine_id, origin_seq, op, node_type, \
             node_id, timestamp) VALUES ('a:500', 1, 'a', 500, 'create', 'memory', 'x', 't')",
            [],
        )
        .unwrap();
        assert!(seen(&conn, "a", 500).unwrap());
    }

    #[test]
    fn kept_keys_coalesce_into_runs_and_skip_legacy_keys() {
        let store = SqliteStore::in_memory("m".to_string()).unwrap();
        let conn = store.conn();
        let base = 3i64 << 32;
        let rows: [(&str, i64); 6] = [
            ("a", base | 1),
            ("a", base | 2),
            ("a", base | 3),
            ("a", base | 7),
            ("b", 42),
            ("snap:b", 9),
        ];
        for (seq, (origin, origin_seq)) in rows.iter().enumerate() {
            conn.execute(
                "INSERT INTO sync_log (id, local_seq, origin_machine_id, origin_seq, op, \
                 node_type, node_id, timestamp) VALUES (?1, ?2, ?3, ?4, 'create', 'memory', 'x', 't')",
                params![format!("{origin}:{origin_seq}"), seq as i64 + 1, origin, origin_seq],
            )
            .unwrap();
        }
        keep_keys_of(&conn, &[1, 2, 3, 4, 5, 6]).unwrap();
        assert_eq!(
            ranges(&conn, "a"),
            vec![(base | 1, base | 3), (base | 7, base | 7)]
        );
        assert!(ranges(&conn, "b").is_empty(), "legacy keys never enter");
        assert_eq!(ranges(&conn, "snap:b"), vec![(9, 9)]);
    }

    fn memory(content: &str) -> Memory {
        Memory::new(
            content.to_string(),
            MemoryType::Semantic,
            "test".to_string(),
            String::new(),
        )
    }

    fn relayed(origin_seq: u64, op: SyncOp, memory: &Memory) -> SyncRecord {
        SyncRecord {
            local_seq: origin_seq,
            origin_machine_id: "machine-a".to_string(),
            origin_seq,
            op,
            node_type: SyncNodeType::Memory,
            node_id: memory.id.to_string(),
            timestamp: Utc::now(),
            data: (op != SyncOp::Delete).then(|| serde_json::to_value(memory).unwrap()),
        }
    }

    #[test]
    fn applied_keys_of_one_origin_stay_one_range_when_gc_reclaims_scattered_rows() {
        let b = SqliteStore::in_memory("machine-b".to_string()).unwrap();
        let base = 5u64 << 32;
        let memories: Vec<Memory> = (0..5).map(|i| memory(&format!("relayed {i}"))).collect();
        let mut seq = base;
        for m in &memories {
            seq += 1;
            b.apply_record(&relayed(seq, SyncOp::Create, m)).unwrap();
        }
        for m in memories.iter().step_by(2) {
            seq += 1;
            b.apply_record(&relayed(seq, SyncOp::Delete, m)).unwrap();
        }
        let stats = b.repair(None, None, false).unwrap();
        assert_eq!(
            stats.sync_log_rows_deleted, 0,
            "applying each delete already reclaimed the rows before it"
        );
        assert_eq!(
            ranges(&b.conn(), "machine-a"),
            vec![((base | 1) as i64, seq as i64)]
        );
    }

    #[test]
    fn own_keys_stay_one_range_when_gc_reclaims_scattered_rows() {
        let a = SqliteStore::in_memory("machine-a".to_string()).unwrap();
        let memories: Vec<Memory> = (0..5).map(|i| memory(&format!("own {i}"))).collect();
        for m in &memories {
            a.store_memory(m).unwrap();
        }
        for m in memories.iter().step_by(2) {
            a.delete_memory(m.id).unwrap();
        }
        let (lo, hi): (i64, i64) = a
            .conn()
            .query_row(
                "SELECT MIN(origin_seq), MAX(origin_seq) FROM sync_log \
                 WHERE origin_machine_id = 'machine-a'",
                [],
                |r| Ok((r.get(0)?, r.get(1)?)),
            )
            .unwrap();
        let stats = a.repair(None, None, false).unwrap();
        assert_eq!(stats.sync_log_rows_deleted, 3);
        let own = ranges(&a.conn(), "machine-a");
        assert_eq!(own.len(), 1, "one range for one open's keys: {own:?}");
        assert!(
            own[0].0 <= lo && own[0].1 >= hi,
            "{own:?} covers {lo}..={hi}"
        );
    }
}