eventcore-sqlite: encrypted SqliteEventStore::migrate() fails on fresh DB with 'file is not a database'

[jwilger]

Summary

Using eventcore-sqlite 0.6.0 with SqliteConfig { encryption_key: Some(...) } fails on a fresh database file when SqliteEventStore::new(...) is followed by migrate().await.

The failure is:

migration failed: file is not a database

Minimal reproduction

use eventcore_sqlite::{SqliteConfig, SqliteEventStore};

#[tokio::main]
async fn main() {
    let db_path = std::env::temp_dir().join("eventcore-sqlcipher-repro.db");
    let _ = std::fs::remove_file(&db_path);

    let store = SqliteEventStore::new(SqliteConfig {
        path: db_path,
        encryption_key: Some("correct horse battery staple".to_string()),
    })
    .expect("store should open");

    store.migrate().await.expect("migration should succeed");
}

Expected

migrate() should create and initialize the encrypted SQLite database successfully.

Actual

migrate() fails with file is not a database.

Likely cause

This looks like a connection setup ordering problem in eventcore-sqlite.

In src/lib.rs, open_connection() sets journal_mode = WAL before the encryption key is applied, and the key is only applied later in SqliteEventStore::new().

That means the adapter touches the database before PRAGMA key has been set. My inference is that this is enough to create or interact with the file as an unkeyed database, after which later SQLCipher operations fail.

Workaround

A local workaround that unblocked me was:

1. open the connection
2. apply PRAGMA key
3. validate the connection with SELECT count(*) FROM sqlite_master
4. then set journal_mode = WAL
5. then run migrations

Context

I hit this while implementing an encrypted operator-side licensing store that uses eventcore command execution with SQLCipher-backed SQLite. I had to replace the SQLite shell adapter locally to keep using eventcore semantics with encrypted persistence.