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 14 tables:

All foreign key relationships are enforced. boards → columns → cards cascade on delete.

Save Flow

1. Begin a write transaction
2. Upsert all tables (boards, columns, cards, archived_cards, sprints, sprint_logs, dependency_edges, metadata)
3. Delete rows no longer present in the snapshot
4. Commit

All operations occur within a single transaction; a failure at any point rolls back completely.

Load Flow

1. Begin a read transaction
2. Read all tables in join order
3. Reconstruct kanban_domain::Snapshot from relational rows
4. Return snapshot + metadata

Schema Versioning

The schema_version table records the current schema version (v1). A migration skeleton is in place for future schema upgrades.

SqliteStoreFactory

pub struct SqliteStoreFactory;

impl StoreFactory for SqliteStoreFactory {
    fn name(&self) -> &str { "sqlite" }
    fn supported_patterns(&self) -> &[&str] { &["*.sqlite", "*.sqlite3", "*.db"] }
    fn matches(&self, locator: &str) -> bool { /* extension check + magic bytes */ }
    fn create(&self, locator: &str) -> Result<...>;
}

matches checks:

1. File extension is .sqlite, .sqlite3, or .db → true
2. First 16 bytes of an existing file equal the SQLite magic (SQLite format 3\0) → true
3. Otherwise → false

Dependencies