clm-kernel 0.0.2

Cubical Logic Model (CLM) sovereign execution engine, CPN-VM, and Cordis spatiotemporal substrate
Documentation
//! Versioned Schema Migrations for SQLite Tri-Database Pillars.
//!
//! Provides deterministic schema initialization, upgrade, and downgrade tracking
//! via the `_clm_schema_migrations` metadata table, eliminating ad-hoc DDL execution.

use crate::error::{ClmError, StorageErrorCode, Result};
use rusqlite::{params, Connection};

/// Represents a versioned schema migration step.
#[derive(Debug, Clone)]
pub struct Migration {
    pub version: i64,
    pub description: &'static str,
    pub sql_up: &'static str,
    pub sql_down: &'static str,
}

/// Baseline migration 1: The Monadic Core (card, handle_registry, handle_history).
const MIGRATION_1_BASELINE: Migration = Migration {
    version: 1,
    description: "Baseline Monadic Core Schema (card, handle_registry, handle_history)",
    sql_up: "
        -- INV-TAPE-01: three tables, three columns each. The kind is not a column
        -- (INV-TAPE-01a): the handle names it and `kindOf` decides it. Lifecycle is
        -- external (INV-TAPE-04): tombstones and their reasons live in a sidecar tape,
        -- not in a `change_type` column and not in a `card_tombstone` table.
        CREATE TABLE IF NOT EXISTS card (
            hash TEXT PRIMARY KEY,
            content BLOB NOT NULL,
            g_time TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS handle_registry (
            handle TEXT PRIMARY KEY,
            current_hash TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            FOREIGN KEY (current_hash) REFERENCES card(hash)
        );
        CREATE INDEX IF NOT EXISTS idx_handle_current_hash ON handle_registry(current_hash);
        CREATE TABLE IF NOT EXISTS handle_history (
            handle TEXT NOT NULL,
            previous_hash TEXT NOT NULL,
            changed_at TEXT NOT NULL,
            FOREIGN KEY (handle) REFERENCES handle_registry(handle),
            FOREIGN KEY (previous_hash) REFERENCES card(hash)
        );
        CREATE INDEX IF NOT EXISTS idx_handle_history_handle ON handle_history(handle);
    ",
    sql_down: "
        DROP INDEX IF EXISTS idx_handle_history_handle;
        DROP TABLE IF EXISTS handle_history;
        DROP INDEX IF EXISTS idx_handle_current_hash;
        DROP TABLE IF EXISTS handle_registry;
        DROP INDEX IF EXISTS idx_card_hash;
        DROP TABLE IF EXISTS card;
    ",
};

/// Migration 2: Federation Learning Event CRDT Sync.
const MIGRATION_2_FEDERATION_CRDT: Migration = Migration {
    version: 2,
    description: "Federation & Learning Event CRDT Sync Table",
    sql_up: "
        CREATE TABLE IF NOT EXISTS learning_event (
            vcard_hash TEXT PRIMARY KEY,
            pcard_hash TEXT NOT NULL,
            agent_did TEXT NOT NULL,
            timestamp TEXT NOT NULL,
            vector_clock TEXT NOT NULL,
            marking_delta TEXT NOT NULL,
            node_id TEXT NOT NULL,
            synced_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_learning_event_timestamp ON learning_event(timestamp);
        CREATE INDEX IF NOT EXISTS idx_learning_event_node ON learning_event(node_id);
    ",
    sql_down: "
        DROP INDEX IF EXISTS idx_learning_event_node;
        DROP INDEX IF EXISTS idx_learning_event_timestamp;
        DROP TABLE IF EXISTS learning_event;
    ",
};

/// Returns the ordered list of migrations defined for a given Tri-Database pillar.
pub fn get_migrations_for_pillar(pillar: &str) -> Vec<Migration> {
    match pillar {
        "execution_log" => vec![MIGRATION_1_BASELINE],
        _ => vec![MIGRATION_1_BASELINE, MIGRATION_2_FEDERATION_CRDT],
    }
}

/// Applies all unapplied versioned schema migrations to the given database connection.
pub fn apply_migrations(conn: &mut Connection, pillar: &str) -> Result<()> {
    // 1. Ensure the schema migration tracking table exists
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS _clm_schema_migrations (
            version INTEGER PRIMARY KEY,
            description TEXT NOT NULL,
            applied_at TEXT NOT NULL
        );",
    )
    .map_err(|e| {
        ClmError::storage(
            StorageErrorCode::DatabaseUnavailable,
            format!("Failed to initialize _clm_schema_migrations table: {}", e),
        )
    })?;

    let migrations = get_migrations_for_pillar(pillar);
    for m in migrations {
        let applied: bool = conn
            .query_row(
                "SELECT count(*) > 0 FROM _clm_schema_migrations WHERE version = ?1",
                params![m.version],
                |row| row.get(0),
            )
            .unwrap_or(false);

        if !applied {
            let tx = conn.transaction().map_err(|e| {
                ClmError::storage(
                    StorageErrorCode::TransactionFailed,
                    format!("Failed to open migration transaction: {}", e),
                )
            })?;

            tx.execute_batch(m.sql_up).map_err(|e| {
                ClmError::storage(
                    StorageErrorCode::InvalidSchemaVersion,
                    format!("Failed to apply migration v{} ({}): {}", m.version, m.description, e),
                )
            })?;

            tx.execute(
                "INSERT INTO _clm_schema_migrations (version, description, applied_at) VALUES (?1, ?2, datetime('now'))",
                params![m.version, m.description],
            )
            .map_err(|e| {
                ClmError::storage(
                    StorageErrorCode::TransactionFailed,
                    format!("Failed to record migration v{}: {}", m.version, e),
                )
            })?;

            tx.commit().map_err(|e| {
                ClmError::storage(
                    StorageErrorCode::TransactionFailed,
                    format!("Failed to commit migration v{}: {}", m.version, e),
                )
            })?;
        }
    }

    Ok(())
}

/// Returns the highest migration version currently applied in the database.
pub fn current_schema_version(conn: &Connection) -> Result<Option<i64>> {
    let table_exists: bool = conn
        .query_row(
            "SELECT count(*) > 0 FROM sqlite_master WHERE type='table' AND name='_clm_schema_migrations'",
            [],
            |row| row.get(0),
        )
        .unwrap_or(false);

    if !table_exists {
        return Ok(None);
    }

    let version: Option<i64> = conn
        .query_row(
            "SELECT max(version) FROM _clm_schema_migrations",
            [],
            |row| row.get(0),
        )
        .ok()
        .flatten();

    Ok(version)
}

/// Rolls back migrations down to the target version (inclusive of versions > target_version).
pub fn rollback_migration(conn: &mut Connection, target_version: i64, pillar: &str) -> Result<()> {
    let migrations = get_migrations_for_pillar(pillar);
    // Unwind in reverse order
    for m in migrations.into_iter().rev() {
        if m.version > target_version {
            let applied: bool = conn
                .query_row(
                    "SELECT count(*) > 0 FROM _clm_schema_migrations WHERE version = ?1",
                    params![m.version],
                    |row| row.get(0),
                )
                .unwrap_or(false);

            if applied {
                let tx = conn.transaction().map_err(|e| {
                    ClmError::storage(
                        StorageErrorCode::TransactionFailed,
                        format!("Failed to open rollback transaction: {}", e),
                    )
                })?;

                tx.execute_batch(m.sql_down).map_err(|e| {
                    ClmError::storage(
                        StorageErrorCode::InvalidSchemaVersion,
                        format!("Failed to rollback migration v{} ({}): {}", m.version, m.description, e),
                    )
                })?;

                tx.execute(
                    "DELETE FROM _clm_schema_migrations WHERE version = ?1",
                    params![m.version],
                )
                .map_err(|e| {
                    ClmError::storage(
                        StorageErrorCode::TransactionFailed,
                        format!("Failed to delete migration record v{}: {}", m.version, e),
                    )
                })?;

                tx.commit().map_err(|e| {
                    ClmError::storage(
                        StorageErrorCode::TransactionFailed,
                        format!("Failed to commit rollback v{}: {}", m.version, e),
                    )
                })?;
            }
        }
    }

    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_migrations_apply_and_rollback() {
        let mut conn = Connection::open_in_memory().unwrap();
        assert_eq!(current_schema_version(&conn).unwrap(), None);

        apply_migrations(&mut conn, "content_memory").unwrap();
        assert_eq!(current_schema_version(&conn).unwrap(), Some(2));

        // Re-applying is idempotent
        apply_migrations(&mut conn, "content_memory").unwrap();
        assert_eq!(current_schema_version(&conn).unwrap(), Some(2));

        // Verify card table and learning_event table exist
        let count: i64 = conn
            .query_row("SELECT count(*) FROM card", [], |r| r.get(0))
            .unwrap();
        assert_eq!(count, 0);

        // Roll back migration 2
        rollback_migration(&mut conn, 1, "content_memory").unwrap();
        assert_eq!(current_schema_version(&conn).unwrap(), Some(1));

        // learning_event is dropped, but card remains
        assert!(conn.query_row("SELECT count(*) FROM learning_event", [], |r| r.get::<_, i64>(0)).is_err());
        assert!(conn.query_row("SELECT count(*) FROM card", [], |r| r.get::<_, i64>(0)).is_ok());

        // Roll back to 0
        rollback_migration(&mut conn, 0, "content_memory").unwrap();
        assert_eq!(current_schema_version(&conn).unwrap(), None);
        assert!(conn.query_row("SELECT count(*) FROM card", [], |r| r.get::<_, i64>(0)).is_err());
    }
}