Assisted-by: Claude Opus 4.6 via pi

diff --git a/crates/ranger-lib/migrations/001_initial.sql b/crates/ranger-lib/migrations/001_initial.sql
new file mode 100644
index 0000000..8324dd8
--- /dev/null
+++ b/crates/ranger-lib/migrations/001_initial.sql
@@ -0,0 +1,50 @@
+CREATE TABLE IF NOT EXISTS backlogs (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    key TEXT NOT NULL UNIQUE,
+    name TEXT NOT NULL,
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
+);
+
+CREATE TABLE IF NOT EXISTS tasks (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    key TEXT NOT NULL UNIQUE,
+    parent_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
+    title TEXT NOT NULL,
+    description TEXT,
+    state TEXT NOT NULL DEFAULT 'icebox' CHECK(state IN ('icebox', 'queued', 'in_progress', 'done')),
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
+);
+
+CREATE TABLE IF NOT EXISTS backlog_tasks (
+    backlog_id INTEGER NOT NULL REFERENCES backlogs(id) ON DELETE CASCADE,
+    task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    position TEXT NOT NULL,
+    PRIMARY KEY (backlog_id, task_id)
+);
+
+CREATE TABLE IF NOT EXISTS comments (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    body TEXT NOT NULL,
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
+);
+
+CREATE TABLE IF NOT EXISTS blockers (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    blocked_by_task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    UNIQUE(task_id, blocked_by_task_id)
+);
+
+CREATE TABLE IF NOT EXISTS tags (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS task_tags (
+    task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
+    PRIMARY KEY (task_id, tag_id)
+);
diff --git a/crates/ranger-lib/src/db.rs b/crates/ranger-lib/src/db.rs
new file mode 100644
index 0000000..acb6e05
--- /dev/null
+++ b/crates/ranger-lib/src/db.rs
@@ -0,0 +1,61 @@
+use crate::error::RangerError;
+use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
+use std::path::Path;
+
+pub async fn connect(path: &Path) -> Result<SqlitePool, RangerError> {
+    if let Some(parent) = path.parent() {
+        std::fs::create_dir_all(parent)?;
+    }
+
+    let options = SqliteConnectOptions::new()
+        .filename(path)
+        .create_if_missing(true)
+        .foreign_keys(true);
+
+    let pool = SqlitePoolOptions::new()
+        .max_connections(1)
+        .connect_with(options)
+        .await?;
+
+    migrate(&pool).await?;
+    Ok(pool)
+}
+
+async fn migrate(pool: &SqlitePool) -> Result<(), RangerError> {
+    sqlx::raw_sql(include_str!("../migrations/001_initial.sql"))
+        .execute(pool)
+        .await?;
+    Ok(())
+}
+
+#[cfg(test)]
+mod tests {
+    use super::*;
+    use tempfile::tempdir;
+
+    #[tokio::test]
+    async fn connect_creates_db_and_runs_migrations() {
+        let dir = tempdir().unwrap();
+        let db_path = dir.path().join("test.db");
+        let pool = connect(&db_path).await.unwrap();
+
+        let result =
+            sqlx::query("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
+                .fetch_all(&pool)
+                .await
+                .unwrap();
+
+        let table_names: Vec<String> = result
+            .iter()
+            .map(|row| sqlx::Row::get(row, "name"))
+            .collect();
+
+        assert!(table_names.contains(&"backlogs".to_string()));
+        assert!(table_names.contains(&"tasks".to_string()));
+        assert!(table_names.contains(&"backlog_tasks".to_string()));
+        assert!(table_names.contains(&"comments".to_string()));
+        assert!(table_names.contains(&"blockers".to_string()));
+        assert!(table_names.contains(&"tags".to_string()));
+        assert!(table_names.contains(&"task_tags".to_string()));
+    }
+}
diff --git a/crates/ranger-lib/src/lib.rs b/crates/ranger-lib/src/lib.rs
index 3da7711..e750927 100644
--- a/crates/ranger-lib/src/lib.rs
+++ b/crates/ranger-lib/src/lib.rs
@@ -1,3 +1,4 @@
+pub mod db;
 pub mod error;
 pub mod key;
 pub mod position;