use super::*;
pub fn default_store_path() -> PathBuf {
PathBuf::from(".sac").join("store.db")
}
pub fn initialize(path: &Path) -> Result<()> {
tracing::debug!(db_path = %path.display(), "initializing store schema");
let _ = open_connection(path)?;
tracing::info!(db_path = %path.display(), "store schema initialized");
Ok(())
}
pub(crate) fn open_connection(path: &Path) -> Result<Connection> {
tracing::trace!(db_path = %path.display(), "opening SQLite store connection");
if let Some(parent) = path.parent() {
std::fs::create_dir_all(parent)
.with_context(|| format!("failed to create store dir {}", parent.display()))?;
}
let conn = Connection::open(path)
.with_context(|| format!("failed to open SQLite store {}", path.display()))?;
conn.execute_batch(
"PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
CREATE TABLE IF NOT EXISTS threads (
name TEXT NOT NULL,
session_id TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
PRIMARY KEY (name, session_id)
);
CREATE TABLE IF NOT EXISTS episodes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
thread_name TEXT NOT NULL,
session_id TEXT NOT NULL,
action TEXT NOT NULL,
content TEXT NOT NULL,
created_at TEXT NOT NULL,
FOREIGN KEY (thread_name, session_id) REFERENCES threads(name, session_id)
);
CREATE TABLE IF NOT EXISTS worksets (
id TEXT NOT NULL,
session_id TEXT NOT NULL,
kind TEXT NOT NULL,
instruction TEXT NOT NULL,
status TEXT NOT NULL,
summary TEXT NOT NULL,
verification_recipe TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
PRIMARY KEY (id, session_id)
);
CREATE TABLE IF NOT EXISTS workset_items (
workset_id TEXT NOT NULL,
session_id TEXT NOT NULL,
position INTEGER NOT NULL,
title TEXT NOT NULL,
thread_name TEXT NOT NULL,
scope TEXT NOT NULL,
description TEXT NOT NULL,
item_kind TEXT NOT NULL,
status TEXT NOT NULL,
source_threads_json TEXT NOT NULL,
last_summary TEXT,
acceptance TEXT NOT NULL DEFAULT '',
updated_at TEXT NOT NULL,
PRIMARY KEY (workset_id, session_id, position),
FOREIGN KEY (workset_id, session_id) REFERENCES worksets(id, session_id)
);
CREATE TABLE IF NOT EXISTS sessions (
session_id TEXT PRIMARY KEY,
cwd TEXT NOT NULL,
store_path TEXT NOT NULL,
model TEXT NOT NULL,
base_url TEXT NOT NULL,
backend TEXT,
reasoning_effort TEXT,
sandbox_json TEXT,
messages_json TEXT NOT NULL,
last_response_duration_ms INTEGER,
previous_response_duration_ms INTEGER,
response_durations_ms_json TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS goals (
session_id TEXT PRIMARY KEY,
objective TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active',
tokens_used INTEGER NOT NULL DEFAULT 0,
time_used_seconds INTEGER NOT NULL DEFAULT 0,
token_budget INTEGER,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_episodes_thread_session_created
ON episodes(thread_name, session_id, id);
CREATE INDEX IF NOT EXISTS idx_worksets_session_updated
ON worksets(session_id, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_workset_items_workset_position
ON workset_items(workset_id, session_id, position);
CREATE INDEX IF NOT EXISTS idx_sessions_updated_at
ON sessions(updated_at DESC);",
)?;
ensure_workset_items_acceptance_column(&conn)?;
ensure_column(&conn, "sessions", "backend", "TEXT")?;
ensure_column(&conn, "sessions", "reasoning_effort", "TEXT")?;
ensure_column(&conn, "sessions", "last_response_duration_ms", "INTEGER")?;
ensure_column(
&conn,
"sessions",
"previous_response_duration_ms",
"INTEGER",
)?;
ensure_column(&conn, "sessions", "response_durations_ms_json", "TEXT")?;
ensure_column(&conn, "sessions", "timeline_json", "TEXT")?;
ensure_column(&conn, "sessions", "archived_messages_json", "TEXT")?;
ensure_column(&conn, "goals", "goal_id", "TEXT NOT NULL DEFAULT ''")?;
ensure_column(&conn, "goals", "tokens_used", "INTEGER NOT NULL DEFAULT 0")?;
ensure_column(&conn, "goals", "time_used_seconds", "INTEGER NOT NULL DEFAULT 0")?;
ensure_column(&conn, "goals", "token_budget", "INTEGER")?;
tracing::trace!(db_path = %path.display(), "SQLite store connection ready");
Ok(conn)
}
fn ensure_workset_items_acceptance_column(conn: &Connection) -> Result<()> {
let mut stmt = conn.prepare("PRAGMA table_info(workset_items)")?;
let mut rows = stmt.query([])?;
while let Some(row) = rows.next()? {
let name: String = row.get(1)?;
if name == "acceptance" {
return Ok(());
}
}
conn.execute(
"ALTER TABLE workset_items ADD COLUMN acceptance TEXT NOT NULL DEFAULT ''",
[],
)?;
Ok(())
}
fn ensure_column(conn: &Connection, table: &str, column: &str, definition: &str) -> Result<()> {
let pragma = format!("PRAGMA table_info({})", table);
let mut stmt = conn.prepare(&pragma)?;
let columns = stmt.query_map([], |row| row.get::<_, String>(1))?;
for existing in columns {
if existing? == column {
return Ok(());
}
}
let alter = format!("ALTER TABLE {} ADD COLUMN {} {}", table, column, definition);
conn.execute(&alter, [])?;
Ok(())
}