kanban-persistence-sqlite

SQLite storage backend for the kanban workspace. Implements StoreFactory and PersistenceStore from kanban-persistence.

SqliteStore

pub struct SqliteStore {
    path: String,
    instance_id: String,
    // connection pool (lazily initialized)
}

impl SqliteStore {
    pub fn new(path: &str) -> Self;
    pub fn with_instance_id(path: &str, instance_id: &str) -> Self;
}

The connection pool is initialized lazily on the first operation. The database file is created automatically on first use.

Connection Pool Configuration

Schema

The schema consists of 13 tables:

All foreign key relationships are enforced. boards → columns → cards cascade on delete. Edge tables intentionally omit foreign keys on source_id / target_id: edges are bulk-replaced on every save (DELETE-all + re-INSERT).

Dependency edge tables

The single legacy card_edges table was split into one table per kind to mirror the in-memory split (DagGraph<SpawnsEdge> / DagGraph<BlocksEdge> / UndirectedGraph<RelatesEdge>). Each table carries only the metadata its kind needs — no nullable catch-all columns.

Each table has (source_id, target_id) as the composite primary key and indexes on both columns.

Save Flow

1. Begin a write transaction
2. PRAGMA defer_foreign_keys = ON to allow ordered re-population
3. Delete from every table in dependency order (edges, archive metadata, sprint logs, cards, sprints, board aux tables, columns, boards)
4. Re-insert boards, columns, sprints, cards, archived metadata, then graph edges
5. Commit

All operations occur within a single transaction; a failure at any point rolls back completely. This is a full-snapshot replace, not an incremental upsert.

Load Flow

1. Read all tables in join order (boards → columns → sprints → cards → archived metadata → graph edges)
2. Reconstruct kanban_domain::Snapshot from relational rows
3. Return snapshot + PersistenceMetadata derived from the metadata row

Schema Versioning

There is no dedicated schema_version table. The metadata table carries the schema_version column (currently 1) on its singleton row. Migration helpers in sqlite_store.rs drop legacy tables (card_edges, an older command_log shape) when an older database is opened.

SqliteStoreFactory

pub struct SqliteStoreFactory;

impl StoreFactory for SqliteStoreFactory {
    fn name(&self) -> &str { "sqlite" }
    fn matches_content(&self, header: &[u8]) -> bool {
        header.starts_with(b"SQLite format 3\0")
    }
    fn create(&self, locator: &str)
        -> Result<Arc<dyn PersistenceStore + Send + Sync>, PersistenceError>;
}

Backend selection is content-sniffed, not extension-based. StoreRegistry reads the first 32 bytes of the file and asks each registered factory whether it recognises the header. SqliteStoreFactory::matches_content returns true iff the header starts with the SQLite magic string "SQLite format 3\0". Files that do not yet exist are not detected by sniffing; in that case the caller picks a backend by name (create_store("sqlite", path)).

create requires a multi-thread Tokio runtime — it uses block_in_place and returns an error on a current_thread runtime. Tests must use #[tokio::test(flavor = "multi_thread")].

Dependencies