agent-effects-sqlite 0.2.1

SQLite store for agent-effects
Documentation
//! The store contract, durability across reopening, and use from the runtime.

use std::sync::Arc;
use std::sync::atomic::{AtomicU32, Ordering};

use agent_effects::{EffectFailure, EffectOutcome, EffectStore, Runtime};
use agent_effects_sqlite::SqliteStore;

#[tokio::test]
async fn corrupt_stored_columns_fail_closed_without_echoing_their_contents() {
    use agent_effects::store::{ListQuery, NewEffect};
    use agent_effects::{EffectKey, EffectKind, EffectName, LogicalKey};

    let dir = tempfile::tempdir().unwrap();
    let store = SqliteStore::open(dir.path().join("corrupt.db"))
        .await
        .unwrap();
    for (column, value) in [
        ("status", "'secret-corrupt-value'"),
        ("kind", "'secret-corrupt-value'"),
        ("attempt_count", "-1"),
        ("compensation_attempts", "-1"),
        ("lease_epoch", "-1"),
        ("version", "-1"),
        ("input_schema_version", "-1"),
        ("execution_contract", "'{}'"),
        ("last_error", "'{'"),
        (
            "last_error",
            r#"'{"class":{"class":"secret-corrupt-value"},"message":"hidden"}'"#,
        ),
    ] {
        let key = EffectKey::new(
            EffectName::new("corrupt.record").unwrap(),
            LogicalKey::new(column).unwrap(),
        );
        let record = store
            .insert_or_get(NewEffect::new(
                key,
                EffectKind::IrreversibleWrite,
                std::time::SystemTime::now(),
            ))
            .await
            .unwrap()
            .record;
        // Identifiers and expressions above are fixed test constants.
        sqlx::query(sqlx::AssertSqlSafe(format!(
            "UPDATE effects SET {column} = {value} WHERE id = ?"
        )))
        .bind(record.id.to_string())
        .execute(store.pool())
        .await
        .unwrap();
        let error = store.get(record.id).await.unwrap_err();
        assert!(!error.to_string().contains("secret-corrupt-value"));
        assert!(store.list(ListQuery::statuses([])).await.is_err());
        sqlx::query("DELETE FROM effects WHERE id = ?")
            .bind(record.id.to_string())
            .execute(store.pool())
            .await
            .unwrap();
    }
}

#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn passes_the_conformance_suite() {
    let dir = tempfile::tempdir().unwrap();
    let next = AtomicU32::new(0);
    agent_effects_store::testkit::conformance(|| {
        let path = dir
            .path()
            .join(format!("case-{}.db", next.fetch_add(1, Ordering::SeqCst)));
        async move { SqliteStore::open(path).await.unwrap() }
    })
    .await;
}

#[tokio::test(flavor = "multi_thread", worker_threads = 2)]
async fn committed_effects_survive_reopening_the_database() {
    let dir = tempfile::tempdir().unwrap();
    let path = dir.path().join("effects.db");
    let calls = Arc::new(AtomicU32::new(0));
    let charge = |store: SqliteStore| {
        let calls = Arc::clone(&calls);
        async move {
            Runtime::new(store)
                .effect("payment.charge", "order-1")
                .input(&42)
                .run(move |_| {
                    calls.fetch_add(1, Ordering::SeqCst);
                    async { Ok::<_, EffectFailure>("pi_1".to_string()) }
                })
                .await
                .unwrap()
        }
    };

    let first = charge(SqliteStore::open(&path).await.unwrap()).await;
    // A new process: a fresh pool on the same file.
    let reopened = SqliteStore::open(&path).await.unwrap();
    let second = charge(reopened.clone()).await;

    assert_eq!(first, EffectOutcome::Committed("pi_1".into()));
    assert_eq!(second, first);
    assert_eq!(calls.load(Ordering::SeqCst), 1);

    let record = reopened
        .list(agent_effects::store::ListQuery::statuses([]))
        .await
        .unwrap()
        .pop()
        .unwrap();
    let events = reopened.events(record.id).await.unwrap();
    assert_eq!(events.len(), 2, "the audit trail persisted too");
}

#[tokio::test]
async fn migration_preserves_legacy_records_without_inventing_safety_guarantees() {
    use agent_effects::{EffectId, EffectKey, EffectName, LogicalKey, RuntimeError};
    use sqlx::SqlSafeStr;
    use sqlx::migrate::{Migration, MigrationType, Migrator};

    let pool = sqlx::sqlite::SqlitePoolOptions::new()
        .max_connections(1)
        .connect("sqlite::memory:")
        .await
        .unwrap();
    let old = Migrator::with_migrations(vec![Migration::new(
        1,
        "effects".into(),
        MigrationType::Simple,
        include_str!("../migrations/0001_effects.sql").into_sql_str(),
        false,
    )]);
    old.run(&pool).await.unwrap();
    for (logical, status) in [("pending", "pending"), ("committed", "committed")] {
        sqlx::query("INSERT INTO effects (id, effect_name, logical_key, kind, status, attempt_count, may_have_applied, compensation_attempts, approved, lease_epoch, version, created_at, updated_at, output) VALUES (?, 'legacy.op', ?, 'irreversible_write', ?, 0, 0, 0, 0, 0, 0, 0, 0, '7')")
            .bind(EffectId::new().to_string()).bind(logical).bind(status)
            .execute(&pool).await.unwrap();
    }
    let store = SqliteStore::from_pool(pool).await.unwrap();
    for logical in ["pending", "committed"] {
        let key = EffectKey::new(
            EffectName::new("legacy.op").unwrap(),
            LogicalKey::new(logical).unwrap(),
        );
        assert!(
            store
                .get_by_key(&key)
                .await
                .unwrap()
                .unwrap()
                .execution_contract
                .is_none()
        );
    }
    let rt = Runtime::new(store);
    let pending = rt
        .effect("legacy.op", "pending")
        .run(|_| async {
            panic!("legacy effect ran");
            #[allow(unreachable_code)]
            Ok::<_, EffectFailure>(7)
        })
        .await;
    assert!(matches!(pending, Err(RuntimeError::LegacyContract { .. })));
    let replay = rt
        .effect("legacy.op", "committed")
        .run(|_| async {
            panic!("committed legacy effect ran");
            #[allow(unreachable_code)]
            Ok::<_, EffectFailure>(7)
        })
        .await
        .unwrap();
    assert_eq!(replay, EffectOutcome::Committed(7));
}

#[tokio::test]
async fn versioned_outputs_replay_after_reopening_with_a_new_schema() {
    use agent_effects::{PayloadError, PayloadSchema};
    let dir = tempfile::tempdir().unwrap();
    let path = dir.path().join("versioned.db");
    let store = SqliteStore::open(&path).await.unwrap();
    let rt = Runtime::new(store.clone());
    rt.effect("versioned.op", "one")
        .input_schema_version(3)
        .output_schema(PayloadSchema::new(3))
        .run(|_| async { Ok::<_, EffectFailure>(7_u64) })
        .await
        .unwrap();
    store.pool().close().await;
    drop(rt);
    drop(store);
    let store = SqliteStore::open(&path).await.unwrap();
    let record = store
        .list(agent_effects::store::ListQuery::statuses([]))
        .await
        .unwrap()
        .pop()
        .unwrap();
    assert_eq!(
        (record.input_schema_version, record.output_schema_version),
        (3, 3)
    );
    let schema = PayloadSchema::new(4).migrate(|version, value| {
        if version != 3 {
            return Err(PayloadError::UnsupportedVersion {
                stored: version,
                current: 4,
            });
        }
        Ok(serde_json::json!({ "result": value }))
    });
    let replay: EffectOutcome<serde_json::Value> = Runtime::new(store)
        .wait_with_output_schema(record.id, std::time::Duration::ZERO, schema)
        .await
        .unwrap();
    assert_eq!(
        replay,
        EffectOutcome::Committed(serde_json::json!({ "result": 7 }))
    );
}

#[tokio::test]
async fn attach_never_migrates_and_accepts_read_only_pools() {
    let dir = tempfile::tempdir().unwrap();
    let path = dir.path().join("attach.db");

    // An empty database is refused, not migrated.
    let empty = sqlx::SqlitePool::connect(&format!("sqlite://{}?mode=rwc", path.display()))
        .await
        .unwrap();
    let error = SqliteStore::attach(empty.clone()).await.unwrap_err();
    assert!(error.to_string().contains("migration history"), "{error}");
    let tables: i64 = sqlx::query_scalar("SELECT count(*) FROM sqlite_master WHERE type = 'table'")
        .fetch_one(&empty)
        .await
        .unwrap();
    assert_eq!(tables, 0, "attach created nothing");
    empty.close().await;

    SqliteStore::open(&path).await.unwrap();
    let read_only = sqlx::sqlite::SqlitePoolOptions::new()
        .connect_with(
            sqlx::sqlite::SqliteConnectOptions::new()
                .filename(&path)
                .read_only(true),
        )
        .await
        .unwrap();
    let store = SqliteStore::attach(read_only).await.unwrap();
    let records = store
        .list(agent_effects_store::ListQuery::statuses([]))
        .await
        .unwrap();
    assert_eq!(records.len(), 0, "reads work on a read-only pool");
}

/// Writers in one process take turns instead of racing SQLite's busy
/// handler, which is not first-come-first-served: before they did, a writer
/// could be starved past the busy timeout under contention and fail with
/// "database is locked", leaving an effect whose action had succeeded
/// unrecorded. A 50 ms busy timeout makes the race certain on any disk.
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn concurrent_writers_in_one_process_never_hit_the_busy_timeout() {
    let dir = tempfile::tempdir().unwrap();
    let path = dir.path().join("contended.db");
    SqliteStore::open(&path).await.unwrap();
    let pool = sqlx::sqlite::SqlitePoolOptions::new()
        .max_connections(8)
        .connect_with(
            sqlx::sqlite::SqliteConnectOptions::new()
                .filename(&path)
                .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
                .synchronous(sqlx::sqlite::SqliteSynchronous::Full)
                .busy_timeout(std::time::Duration::from_millis(50)),
        )
        .await
        .unwrap();
    let rt = Runtime::new(SqliteStore::attach(pool).await.unwrap());
    let calls: Vec<_> = (0..32)
        .map(|task| {
            let rt = rt.clone();
            tokio::spawn(async move {
                for n in 0..25 {
                    let outcome = rt
                        .effect("contended.write", format!("{task}-{n}"))
                        .run(|_| async { Ok::<_, EffectFailure>(()) })
                        .await
                        .expect("no store error under in-process contention");
                    assert!(matches!(outcome, EffectOutcome::Committed(())));
                }
            })
        })
        .collect();
    for call in calls {
        call.await.unwrap();
    }
}