second-brain-core 0.13.2

Core library for second-brain: SQLite storage with vector and lexical indexes, BGE embeddings, and hybrid retrieval
Documentation
use anyhow::{Context, Result};
use chrono::Utc;
use rusqlite::{Connection, Transaction, TransactionBehavior};

const DDL: &str = include_str!("schema.sql");

type RustStep = fn(&Transaction<'_>) -> Result<()>;

struct Migration {
    version: i64,
    name: &'static str,
    sql: &'static str,
    rust: Option<RustStep>,
}

const SCHEMA_MIGRATIONS_TABLE_SQL: &str = include_str!("migrations/0002_schema_migrations.sql");

const MEMORIES_DEDUP_KEY_SQL: &str = include_str!("migrations/0003_memories_dedup_key.sql");

const DISTILLATION_LOG_DISTILLED_IDX_SQL: &str =
    include_str!("migrations/0004_distillation_log_distilled_idx.sql");

const MEMORIES_RETIRED_REASON_SQL: &str =
    include_str!("migrations/0005_memories_retired_reason.sql");

const MEMORY_CHUNKS_SQL: &str = include_str!("migrations/0006_memory_chunks.sql");

const MEMORIES_SESSION_AT_SQL: &str = include_str!("migrations/0007_memories_session_at.sql");

const INGEST_LOG_SESSION_KEY_SQL: &str = include_str!("migrations/0008_ingest_log_session_key.sql");

const SYNC_DEDUP_SQL: &str = include_str!("migrations/0009_sync_dedup.sql");

const AUTHORED_TIME_SQL: &str = include_str!("migrations/0010_authored_time.sql");

const DEVICE_KEYS_SQL: &str = include_str!("migrations/0011_device_keys.sql");

const LOG_FLOOR_SQL: &str = include_str!("migrations/0012_log_floor.sql");

const DELETE_BLOCKLIST_SQL: &str = include_str!("migrations/0013_delete_blocklist.sql");

const MEMORY_AUTHOR_SCOPE_SQL: &str = include_str!("migrations/0014_memory_author_scope.sql");

const MIGRATIONS: &[Migration] = &[
    Migration {
        version: 2,
        name: "0002_schema_migrations",
        sql: SCHEMA_MIGRATIONS_TABLE_SQL,
        rust: None,
    },
    Migration {
        version: 3,
        name: "0003_memories_dedup_key",
        sql: MEMORIES_DEDUP_KEY_SQL,
        rust: None,
    },
    Migration {
        version: 4,
        name: "0004_distillation_log_distilled_idx",
        sql: DISTILLATION_LOG_DISTILLED_IDX_SQL,
        rust: None,
    },
    Migration {
        version: 5,
        name: "0005_memories_retired_reason",
        sql: MEMORIES_RETIRED_REASON_SQL,
        rust: None,
    },
    Migration {
        version: 6,
        name: "0006_memory_chunks",
        sql: MEMORY_CHUNKS_SQL,
        rust: None,
    },
    Migration {
        version: 7,
        name: "0007_memories_session_at",
        sql: MEMORIES_SESSION_AT_SQL,
        rust: None,
    },
    Migration {
        version: 8,
        name: "0008_ingest_log_session_key",
        sql: INGEST_LOG_SESSION_KEY_SQL,
        rust: None,
    },
    Migration {
        version: 9,
        name: "0009_sync_dedup",
        sql: SYNC_DEDUP_SQL,
        rust: None,
    },
    Migration {
        version: 10,
        name: "0010_authored_time",
        sql: AUTHORED_TIME_SQL,
        rust: Some(super::authored_migration::seed),
    },
    Migration {
        version: 11,
        name: "0011_device_keys",
        sql: DEVICE_KEYS_SQL,
        rust: None,
    },
    Migration {
        version: 12,
        name: "0012_log_floor",
        sql: LOG_FLOOR_SQL,
        rust: Some(super::floor::seed_neighbours),
    },
    Migration {
        version: 13,
        name: "0013_delete_blocklist",
        sql: DELETE_BLOCKLIST_SQL,
        rust: None,
    },
    Migration {
        version: 14,
        name: "0014_memory_author_scope",
        sql: MEMORY_AUTHOR_SCOPE_SQL,
        rust: None,
    },
];

/// The `user_version` of a store created by schema.sql alone, before any
/// ladder step. Stores that old carry no schema_migrations table.
pub const BASELINE_VERSION: i64 = 1;

/// Derived from the ladder length, not hand-maintained: bumping the schema
/// means appending to MIGRATIONS, never editing a version number here.
pub const SCHEMA_VERSION: i64 = BASELINE_VERSION + MIGRATIONS.len() as i64;

pub fn schema_version(conn: &Connection) -> Result<i64> {
    conn.query_row("PRAGMA user_version", [], |r| r.get(0))
        .context("reading PRAGMA user_version")
}

pub fn init_schema(conn: &Connection) -> Result<()> {
    migrate_to(conn, SCHEMA_VERSION)?;

    let version = schema_version(conn)?;
    if version > SCHEMA_VERSION {
        // Rollout guarantee: an old binary must keep serving a database a
        // newer binary has already migrated, so this warns instead of erroring.
        tracing::warn!(
            db_version = version,
            binary_schema_version = SCHEMA_VERSION,
            "database schema is newer than this binary; opening without migrating"
        );
    }
    Ok(())
}

fn migrate_to(conn: &Connection, target: i64) -> Result<()> {
    // schema_migrations only enters the ladder at version 2, but the baseline
    // step (version 1) must also be able to record its row, so the table is
    // bootstrapped unconditionally ahead of the loop. CREATE TABLE IF NOT
    // EXISTS is idempotent, so this is a no-op once ladder step 2 has run.
    conn.execute_batch(SCHEMA_MIGRATIONS_TABLE_SQL)
        .context("bootstrapping schema_migrations table")?;

    while apply_next_migration(conn, target)? {}
    Ok(())
}

/// Leaves `conn`'s empty database exactly as a binary whose SCHEMA_VERSION
/// was `version` would have created it.
#[cfg(any(test, feature = "test-support"))]
pub(super) fn create_at_version(conn: &Connection, version: i64) -> Result<()> {
    anyhow::ensure!(
        (BASELINE_VERSION..=SCHEMA_VERSION).contains(&version),
        "schema version {version} is not on the ladder \
         ({BASELINE_VERSION}..={SCHEMA_VERSION})"
    );
    if version == BASELINE_VERSION {
        conn.execute_batch(DDL)
            .context("applying the baseline schema")?;
        conn.pragma_update(None, "user_version", BASELINE_VERSION)
            .context("stamping the baseline user_version")?;
        return Ok(());
    }
    migrate_to(conn, version)
}

/// Applies exactly one ladder step inside a BEGIN IMMEDIATE transaction.
/// user_version is re-read only after the write lock is held, so a step
/// already applied by a concurrent process is skipped rather than redone.
fn apply_next_migration(conn: &Connection, target: i64) -> Result<bool> {
    let tx = Transaction::new_unchecked(conn, TransactionBehavior::Immediate)
        .context("beginning migration transaction")?;

    let version = schema_version(&tx)?;
    if version >= target {
        return Ok(false);
    }

    let next = version + 1;
    let (name, sql, rust) = step_for_version(next)?;
    tx.execute_batch(sql)
        .with_context(|| format!("applying migration {next} ({name})"))?;
    if let Some(rust) = rust {
        rust(&tx).with_context(|| format!("seeding migration {next} ({name})"))?;
    }
    tx.pragma_update(None, "user_version", next)
        .context("updating user_version")?;
    record_migration(&tx, next, name)?;

    tx.commit().context("committing migration transaction")?;
    Ok(true)
}

fn step_for_version(version: i64) -> Result<(&'static str, &'static str, Option<RustStep>)> {
    if version == BASELINE_VERSION {
        return Ok(("schema.sql", DDL, None));
    }
    MIGRATIONS
        .iter()
        .find(|m| m.version == version)
        .map(|m| (m.name, m.sql, m.rust))
        .with_context(|| format!("no migration registered for schema version {version}"))
}

fn record_migration(tx: &Transaction<'_>, version: i64, name: &str) -> Result<()> {
    tx.execute(
        "INSERT INTO schema_migrations (version, name, applied_at) VALUES (?1, ?2, ?3)",
        rusqlite::params![version, name, Utc::now().to_rfc3339()],
    )
    .with_context(|| format!("recording schema_migrations row for version {version}"))?;
    Ok(())
}

#[cfg(test)]
mod tests {
    use rusqlite::Connection;

    use super::{MIGRATIONS, SCHEMA_VERSION, create_at_version, migrate_to};

    fn version_of(name: &str) -> i64 {
        MIGRATIONS.iter().find(|m| m.name == name).unwrap().version
    }

    #[test]
    fn rows_existing_at_the_ingest_log_migration_are_flagged_legacy() {
        let conn = Connection::open_in_memory().unwrap();
        let step = version_of("0008_ingest_log_session_key");
        create_at_version(&conn, step - 1).unwrap();
        conn.execute(
            "INSERT INTO ingest_log (file_path, file_hash, ingested_at, memory_count, source) \
             VALUES ('/old.jsonl', '0123456789abcdef', '2026-01-01T00:00:00Z', 3, 'claude-code')",
            [],
        )
        .unwrap();

        migrate_to(&conn, SCHEMA_VERSION).unwrap();
        conn.execute(
            "INSERT INTO ingest_log (file_path, file_hash, ingested_at, memory_count, source) \
             VALUES ('/new.jsonl', 'abc', '2026-01-02T00:00:00Z', 1, 'claude-code')",
            [],
        )
        .unwrap();

        let flag = |path: &str| -> (i64, Option<String>, Option<String>) {
            conn.query_row(
                "SELECT legacy_ids, session_key, content_hash FROM ingest_log WHERE file_path = ?1",
                [path],
                |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?)),
            )
            .unwrap()
        };
        assert_eq!(flag("/old.jsonl"), (1, None, None));
        assert_eq!(flag("/new.jsonl").0, 0);
    }
}