ππΌββοΈ The blackboard for coding agents - agentic development environment for claude code, codex, cursor, opencode, grok and more.
Rust adeai-agentsclaudeclaude-codecodexcursorgeminigrokopencodespec-driven-developmentvibe-codingvim
use anyhow::{Context, Result};
use rusqlite::{params, Connection};
use std::path::Path;
use super::models::{
MobileDevice, Notification, NotificationKind, PhaseStatus, Project, Task, TaskRuntime,
TaskStatus, TransitionRequest,
};
/// Database wrapper for SQLite operations
pub struct Database {
conn: Connection,
}
impl Database {
/// Root directory holding `index.db` and `projects/`.
///
/// `AGTX_DATA_DIR` overrides it so tests (and the smoke runner) can open real
/// databases without writing into the user's own store β otherwise every
/// `Database::open_project(TempDir)` in the suite leaves an orphan DB behind,
/// keyed by a temp path that no longer exists. Same reasoning as
/// `AGTX_AGENT_HOME`.
pub fn data_root() -> Result<std::path::PathBuf> {
if let Ok(dir) = std::env::var("AGTX_DATA_DIR") {
if !dir.is_empty() {
return Ok(std::path::PathBuf::from(dir));
}
}
let dirs = directories::ProjectDirs::from("", "", "agtx")
.context("Could not determine config directory")?;
Ok(dirs.config_dir().to_path_buf())
}
/// Open or create a project database (stored centrally in config dir)
pub fn open_project(project_path: &Path) -> Result<Self> {
let config_dir = Self::data_root()?;
// Create a stable ID from the project path using a hash
let path_str = project_path.to_string_lossy();
let path_hash = Self::hash_path(&path_str);
let db_path = config_dir
.join("projects")
.join(format!("{}.db", path_hash));
// Ensure projects directory exists
if let Some(parent) = db_path.parent() {
std::fs::create_dir_all(parent)?;
}
// Migration: if the new-hash DB doesn't exist, check for an old-hash DB and rename it
if !db_path.exists() {
let old_hash = Self::hash_path_legacy(&path_str);
let old_db_path = config_dir
.join("projects")
.join(format!("{}.db", old_hash));
if old_db_path.exists() {
let _ = std::fs::rename(&old_db_path, &db_path);
}
}
let conn = Connection::open(&db_path)
.with_context(|| format!("Failed to open database at {:?}", db_path))?;
// Harden file permissions: owner-only read/write
#[cfg(unix)]
{
use std::os::unix::fs::PermissionsExt;
let _ = std::fs::set_permissions(&db_path, std::fs::Permissions::from_mode(0o600));
}
let db = Self { conn };
db.init_project_schema()?;
Ok(db)
}
/// Create a stable hash from a path string for database filename.
/// Uses SHA-256 (truncated to 16 hex chars) for cross-version stability.
fn hash_path(path: &str) -> String {
use sha2::{Digest, Sha256};
let mut hasher = Sha256::new();
hasher.update(path.as_bytes());
let result = hasher.finalize();
// Take first 8 bytes (16 hex chars) β same length as the old DefaultHasher output
format!(
"{:016x}",
u64::from_be_bytes(result[..8].try_into().unwrap())
)
}
/// Legacy hash function (DefaultHasher). Used only for migration from pre-SHA256 databases.
fn hash_path_legacy(path: &str) -> String {
use std::collections::hash_map::DefaultHasher;
use std::hash::{Hash, Hasher};
let mut hasher = DefaultHasher::new();
path.hash(&mut hasher);
format!("{:016x}", hasher.finish())
}
/// Open or create the global index database
pub fn open_global() -> Result<Self> {
let db_path = Self::data_root()?.join("index.db");
// Ensure config directory exists
if let Some(parent) = db_path.parent() {
std::fs::create_dir_all(parent)?;
}
let conn = Connection::open(&db_path)
.with_context(|| format!("Failed to open global database at {:?}", db_path))?;
// Harden file permissions: owner-only read/write
#[cfg(unix)]
{
use std::os::unix::fs::PermissionsExt;
let _ = std::fs::set_permissions(&db_path, std::fs::Permissions::from_mode(0o600));
}
let db = Self { conn };
db.init_global_schema()?;
Ok(db)
}
/// Open an in-memory project database (for testing only)
#[cfg(feature = "test-mocks")]
pub fn open_in_memory_project() -> Result<Self> {
let conn = Connection::open_in_memory()?;
let db = Self { conn };
db.init_project_schema()?;
Ok(db)
}
/// Open a project DB at an arbitrary file path (for concurrency tests β in-memory is per-connection).
#[cfg(feature = "test-mocks")]
pub fn open_project_at_path(path: &Path) -> Result<Self> {
let conn = Connection::open(path)
.with_context(|| format!("Failed to open database at {:?}", path))?;
let db = Self { conn };
db.init_project_schema()?;
Ok(db)
}
/// Open an in-memory global database (for testing only)
#[cfg(feature = "test-mocks")]
pub fn open_in_memory_global() -> Result<Self> {
let conn = Connection::open_in_memory()?;
let db = Self { conn };
db.init_global_schema()?;
Ok(db)
}
fn init_project_schema(&self) -> Result<()> {
self.conn.execute_batch(
"PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;",
)?;
self.conn.execute_batch(
r#"
CREATE TABLE IF NOT EXISTS tasks (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
status TEXT NOT NULL DEFAULT 'backlog',
agent TEXT NOT NULL,
project_id TEXT NOT NULL,
session_name TEXT,
worktree_path TEXT,
branch_name TEXT,
pr_number INTEGER,
pr_url TEXT,
plugin TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_project ON tasks(project_id);
"#,
)?;
// Migration: add new columns if they don't exist
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN branch_name TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN pr_number INTEGER", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN pr_url TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN plugin TEXT", []);
let _ = self.conn.execute(
"ALTER TABLE tasks ADD COLUMN cycle INTEGER NOT NULL DEFAULT 1",
[],
);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN referenced_tasks TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN escalation_note TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN base_branch TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN phase_entered_at TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE tasks ADD COLUMN base_agent TEXT", []);
// A task that has never had a session has never been switched, so its
// `agent` is still the pick it was created with. Any other row's `agent`
// may be a per-phase override's, and is left to the configured default.
let _ = self.conn.execute(
"UPDATE tasks SET base_agent = agent
WHERE base_agent IS NULL AND status = 'backlog' AND session_name IS NULL",
[],
);
// MCP transition request queue
self.conn.execute_batch(
r#"
CREATE TABLE IF NOT EXISTS transition_requests (
id TEXT PRIMARY KEY,
task_id TEXT NOT NULL,
action TEXT NOT NULL,
requested_at TEXT NOT NULL,
processed_at TEXT,
error TEXT
);
CREATE TABLE IF NOT EXISTS notifications (
id TEXT PRIMARY KEY,
message TEXT NOT NULL,
created_at TEXT NOT NULL
);
-- Published mirror of the TUI's in-memory PhaseStatus, for readers
-- in other processes. One row per task, rewritten by each session
-- refresh; `updated_at` is what tells a reader whether anything is
-- still refreshing it.
CREATE TABLE IF NOT EXISTS task_runtime (
task_id TEXT PRIMARY KEY,
phase_status TEXT NOT NULL,
pane_hash TEXT,
pane_changed_at TEXT,
updated_at TEXT NOT NULL
);
"#,
)?;
// The status each published verdict was computed for; see `TaskRuntime::status`.
let _ = self
.conn
.execute("ALTER TABLE task_runtime ADD COLUMN status TEXT", []);
// Migration: add reason column to transition_requests if it doesn't exist
let _ = self
.conn
.execute("ALTER TABLE transition_requests ADD COLUMN reason TEXT", []);
let _ = self.conn.execute(
"ALTER TABLE transition_requests ADD COLUMN claimed_by TEXT",
[],
);
// Migration: notifications gain the two fields an out-of-process reader
// needs to route them. Nullable, so rows written before this survive.
let _ = self
.conn
.execute("ALTER TABLE notifications ADD COLUMN task_id TEXT", []);
let _ = self
.conn
.execute("ALTER TABLE notifications ADD COLUMN kind TEXT", []);
Ok(())
}
fn init_global_schema(&self) -> Result<()> {
self.conn.execute_batch(
"PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;",
)?;
self.conn.execute_batch(
r#"
CREATE TABLE IF NOT EXISTS projects (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
path TEXT NOT NULL UNIQUE,
github_url TEXT,
default_agent TEXT,
last_opened TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS running_agents (
session_name TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
task_id TEXT NOT NULL,
agent_name TEXT NOT NULL,
started_at TEXT NOT NULL,
status TEXT NOT NULL,
FOREIGN KEY (project_id) REFERENCES projects(id)
);
CREATE INDEX IF NOT EXISTS idx_running_project ON running_agents(project_id);
-- One row per project a TUI is currently open on. Its only job is
-- to answer "is anything draining the transition queue?" β a phone
-- can enqueue a request whenever, but nothing executes it without a
-- running TUI, and a board that accepts taps and silently does
-- nothing is worse than one that says so.
CREATE TABLE IF NOT EXISTS tui_heartbeat (
project_path TEXT PRIMARY KEY,
beat_at TEXT NOT NULL
);
-- When a phone last asked for a project's board.
--
-- The TUI publishes `task_runtime` only while this is fresh, so a
-- board nobody is looking at costs no writes at all. Without it the
-- refresh wrote a transaction every couple of seconds forever, to
-- keep a mirror current for a reader that might never exist.
CREATE TABLE IF NOT EXISTS board_watch (
project_path TEXT PRIMARY KEY,
beat_at TEXT NOT NULL
);
-- Devices paired with `agtx serve`. One row per phone, so a lost
-- one can be revoked without re-pairing the rest.
--
-- The token is stored **hashed**. This file is 0600, but a
-- credential that grants shell-equivalent access to the machine
-- should not be readable from a backup, a stray copy, or anything
-- that later gets a wider mode by accident.
CREATE TABLE IF NOT EXISTS mobile_devices (
id TEXT PRIMARY KEY,
label TEXT NOT NULL,
token_hash TEXT NOT NULL UNIQUE,
created_at TEXT NOT NULL,
last_seen TEXT,
session_id TEXT
);
CREATE INDEX IF NOT EXISTS idx_devices_hash ON mobile_devices(token_hash);
"#,
)?;
// Migration: a device records the serve session that paired it.
// Nullable, so a row written before this column existed survives.
let _ = self
.conn
.execute("ALTER TABLE mobile_devices ADD COLUMN session_id TEXT", []);
Ok(())
}
// === Task Operations ===
pub fn create_task(&self, task: &Task) -> Result<()> {
self.conn.execute(
r#"
INSERT INTO tasks (id, title, description, status, agent, project_id, session_name, worktree_path, branch_name, pr_number, pr_url, plugin, cycle, referenced_tasks, escalation_note, base_branch, created_at, updated_at, phase_entered_at, base_agent)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20)
"#,
params![
task.id,
task.title,
task.description,
task.status.as_str(),
task.agent,
task.project_id,
task.session_name,
task.worktree_path,
task.branch_name,
task.pr_number,
task.pr_url,
task.plugin,
task.cycle,
task.referenced_tasks,
task.escalation_note,
task.base_branch,
task.created_at.to_rfc3339(),
task.updated_at.to_rfc3339(),
task.phase_entered_at.map(|t| t.to_rfc3339()),
task.base_agent,
],
)?;
Ok(())
}
pub fn create_tasks_batch(&mut self, tasks: &[Task]) -> Result<()> {
let tx = self.conn.transaction()?;
for task in tasks {
tx.execute(
r#"
INSERT INTO tasks (id, title, description, status, agent, project_id, session_name, worktree_path, branch_name, pr_number, pr_url, plugin, cycle, referenced_tasks, escalation_note, base_branch, created_at, updated_at, phase_entered_at, base_agent)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20)
"#,
params![
task.id,
task.title,
task.description,
task.status.as_str(),
task.agent,
task.project_id,
task.session_name,
task.worktree_path,
task.branch_name,
task.pr_number,
task.pr_url,
task.plugin,
task.cycle,
task.referenced_tasks,
task.escalation_note,
task.base_branch,
task.created_at.to_rfc3339(),
task.updated_at.to_rfc3339(),
task.phase_entered_at.map(|t| t.to_rfc3339()),
task.base_agent,
],
)?;
}
tx.commit()?;
Ok(())
}
/// Persist `task`. A change of status also stamps `phase_entered_at`.
///
/// Stamped here, in the one writer every route goes through, rather than at
/// each call site that moves a task: a forgotten call site is a phase whose
/// stale artifact reads as done. The comparison is in SQL, against the stored
/// row β `status` in the `CASE` is the value *before* this update, since
/// SQLite evaluates every `SET` expression against the old row β so the stamp
/// needs no read first and cannot race one.
pub fn update_task(&self, task: &Task) -> Result<()> {
self.conn.execute(
r#"
UPDATE tasks SET
title = ?2,
description = ?3,
status = ?4,
agent = ?5,
session_name = ?6,
worktree_path = ?7,
branch_name = ?8,
pr_number = ?9,
pr_url = ?10,
plugin = ?11,
cycle = ?12,
referenced_tasks = ?13,
escalation_note = ?14,
base_branch = ?15,
updated_at = ?16,
phase_entered_at = CASE WHEN status != ?4 THEN ?17 ELSE phase_entered_at END,
base_agent = ?18
WHERE id = ?1
"#,
params![
task.id,
task.title,
task.description,
task.status.as_str(),
task.agent,
task.session_name,
task.worktree_path,
task.branch_name,
task.pr_number,
task.pr_url,
task.plugin,
task.cycle,
task.referenced_tasks,
task.escalation_note,
task.base_branch,
task.updated_at.to_rfc3339(),
chrono::Utc::now().to_rfc3339(),
task.base_agent,
],
)?;
Ok(())
}
pub fn delete_task(&self, task_id: &str) -> Result<()> {
self.conn
.execute("DELETE FROM tasks WHERE id = ?1", params![task_id])?;
Ok(())
}
fn task_from_row(row: &rusqlite::Row) -> rusqlite::Result<Task> {
Ok(Task {
id: row.get("id")?,
title: row.get("title")?,
description: row.get("description")?,
status: TaskStatus::from_str(&row.get::<_, String>("status")?)
.unwrap_or(TaskStatus::Backlog),
agent: row.get("agent")?,
base_agent: row.get("base_agent").ok().flatten(),
project_id: row.get("project_id")?,
session_name: row.get("session_name")?,
worktree_path: row.get("worktree_path")?,
branch_name: row.get("branch_name").ok().flatten(),
pr_number: row.get("pr_number").ok().flatten(),
pr_url: row.get("pr_url").ok().flatten(),
plugin: row.get("plugin").ok().flatten(),
cycle: row.get("cycle").unwrap_or(1),
referenced_tasks: row.get("referenced_tasks").ok().flatten(),
escalation_note: row.get("escalation_note").ok().flatten(),
base_branch: row.get("base_branch").ok().flatten(),
phase_entered_at: row
.get::<_, Option<String>>("phase_entered_at")
.ok()
.flatten()
.and_then(|s| chrono::DateTime::parse_from_rfc3339(&s).ok())
.map(|dt| dt.with_timezone(&chrono::Utc)),
created_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>("created_at")?)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
updated_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>("updated_at")?)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
})
}
pub fn get_task(&self, task_id: &str) -> Result<Option<Task>> {
let mut stmt = self.conn.prepare("SELECT * FROM tasks WHERE id = ?1")?;
let task = stmt.query_row(params![task_id], Self::task_from_row).ok();
Ok(task)
}
pub fn get_tasks_by_status(&self, status: TaskStatus) -> Result<Vec<Task>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM tasks WHERE status = ?1 ORDER BY created_at")?;
let tasks = stmt
.query_map(params![status.as_str()], Self::task_from_row)?
.filter_map(|r| r.ok())
.collect();
Ok(tasks)
}
pub fn get_all_tasks(&self) -> Result<Vec<Task>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM tasks ORDER BY created_at")?;
let tasks = stmt
.query_map([], Self::task_from_row)?
.filter_map(|r| r.ok())
.collect();
Ok(tasks)
}
/// Check whether all referenced_tasks (dependencies) are in Review or Done.
/// Returns true if the task has no dependencies or all deps are satisfied.
pub fn deps_satisfied(&self, task: &Task) -> bool {
let refs_str = match &task.referenced_tasks {
Some(s) if !s.is_empty() => s,
_ => return true,
};
refs_str.split(',').filter(|s| !s.is_empty()).all(|ref_id| {
self.get_task(ref_id).ok().flatten().map_or(true, |t| {
matches!(t.status, TaskStatus::Review | TaskStatus::Done)
})
})
}
// === Project Operations (for global db) ===
pub fn upsert_project(&self, project: &Project) -> Result<()> {
self.conn.execute(
r#"
INSERT INTO projects (id, name, path, github_url, default_agent, last_opened)
VALUES (?1, ?2, ?3, ?4, ?5, ?6)
ON CONFLICT(path) DO UPDATE SET
name = excluded.name,
github_url = excluded.github_url,
default_agent = excluded.default_agent,
last_opened = excluded.last_opened
"#,
params![
project.id,
project.name,
project.path,
project.github_url,
project.default_agent,
project.last_opened.to_rfc3339(),
],
)?;
Ok(())
}
pub fn get_project_by_id(&self, id: &str) -> Result<Option<Project>> {
let mut stmt = self.conn.prepare("SELECT * FROM projects WHERE id = ?1")?;
let project = stmt
.query_row(params![id], |row| {
Ok(Project {
id: row.get("id")?,
name: row.get("name")?,
path: row.get("path")?,
github_url: row.get("github_url")?,
default_agent: row.get("default_agent")?,
last_opened: chrono::DateTime::parse_from_rfc3339(
&row.get::<_, String>("last_opened")?,
)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
})
})
.ok();
Ok(project)
}
pub fn get_all_projects(&self) -> Result<Vec<Project>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM projects ORDER BY last_opened DESC")?;
let projects = stmt
.query_map([], |row| {
Ok(Project {
id: row.get("id")?,
name: row.get("name")?,
path: row.get("path")?,
github_url: row.get("github_url")?,
default_agent: row.get("default_agent")?,
last_opened: chrono::DateTime::parse_from_rfc3339(
&row.get::<_, String>("last_opened")?,
)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(projects)
}
// === Transition Request Operations (MCP command queue) ===
pub fn create_transition_request(&self, req: &TransitionRequest) -> Result<()> {
self.conn.execute(
r#"
INSERT INTO transition_requests (id, task_id, action, reason, requested_at, processed_at, error)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
"#,
params![
req.id,
req.task_id,
req.action,
req.reason,
req.requested_at.to_rfc3339(),
req.processed_at.map(|dt| dt.to_rfc3339()),
req.error,
],
)?;
Ok(())
}
pub fn get_transition_request(&self, id: &str) -> Result<Option<TransitionRequest>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM transition_requests WHERE id = ?1")?;
let req = stmt
.query_row(params![id], Self::transition_request_from_row)
.ok();
Ok(req)
}
pub fn get_pending_transition_requests(&self) -> Result<Vec<TransitionRequest>> {
let mut stmt = self.conn.prepare(
"SELECT * FROM transition_requests
WHERE processed_at IS NULL AND claimed_by IS NULL
ORDER BY requested_at ASC",
)?;
let requests = stmt
.query_map([], Self::transition_request_from_row)?
.filter_map(|r| r.ok())
.collect();
Ok(requests)
}
pub fn mark_transition_processed(&self, id: &str, error: Option<&str>) -> Result<()> {
self.conn.execute(
"UPDATE transition_requests SET processed_at = ?1, error = ?2 WHERE id = ?3",
params![chrono::Utc::now().to_rfc3339(), error, id],
)?;
Ok(())
}
/// Atomically claim a pending request. Returns true iff this caller won.
pub fn claim_transition_request(&self, id: &str, claimant: &str) -> Result<bool> {
let rows = self.conn.execute(
"UPDATE transition_requests
SET claimed_by = ?1
WHERE id = ?2 AND claimed_by IS NULL AND processed_at IS NULL",
params![claimant, id],
)?;
Ok(rows == 1)
}
/// Release claims left behind by a TUI that is no longer running, so the
/// next one re-drains them.
///
/// A claim is taken the moment a request is picked up, but a Backlog
/// transition is only *marked* once the serialized setup slot frees up and
/// it actually starts. Between those two points the request is claimed and
/// unprocessed, and [`get_pending_transition_requests`] filters claimed rows
/// out β so a TUI that exits while a setup is queued strands it. Nothing
/// re-runs it; `cleanup_old_transition_requests` eventually deletes it, an
/// hour later, having never executed the transition the caller asked for.
///
/// Rows claimed by `this_instance` are left alone: this instance's own
/// in-memory queue still owns them.
///
/// `older_than` keeps a *live* second instance's genuine backlog out of
/// reach. Reclaiming one anyway is safe rather than merely unlikely β the
/// drain re-validates each task before starting it, so the loser of the race
/// resolves its copy with an error instead of setting the worktree up twice.
pub fn reclaim_stale_transition_requests(
&self,
this_instance: &str,
older_than: chrono::Duration,
) -> Result<usize> {
let cutoff = (chrono::Utc::now() - older_than).to_rfc3339();
let rows = self.conn.execute(
"UPDATE transition_requests
SET claimed_by = NULL
WHERE processed_at IS NULL
AND claimed_by IS NOT NULL
AND claimed_by != ?1
AND requested_at < ?2",
params![this_instance, cutoff],
)?;
Ok(rows)
}
pub fn cleanup_old_transition_requests(&self) -> Result<()> {
let cutoff = (chrono::Utc::now() - chrono::Duration::hours(1)).to_rfc3339();
self.conn.execute(
"DELETE FROM transition_requests
WHERE (processed_at IS NOT NULL AND processed_at < ?1)
OR (processed_at IS NULL AND claimed_by IS NOT NULL AND requested_at < ?1)",
params![cutoff],
)?;
Ok(())
}
/// Directly set processed_at to an arbitrary timestamp (for testing cleanup logic only).
#[cfg(feature = "test-mocks")]
pub fn backdate_transition_processed_at(&self, id: &str, processed_at: &str) -> Result<()> {
self.conn.execute(
"UPDATE transition_requests SET processed_at = ?1 WHERE id = ?2",
params![processed_at, id],
)?;
Ok(())
}
/// Directly set requested_at to an arbitrary timestamp (for testing cleanup logic only).
#[cfg(feature = "test-mocks")]
pub fn backdate_transition_requested_at(&self, id: &str, requested_at: &str) -> Result<()> {
self.conn.execute(
"UPDATE transition_requests SET requested_at = ?1 WHERE id = ?2",
params![requested_at, id],
)?;
Ok(())
}
fn transition_request_from_row(row: &rusqlite::Row) -> rusqlite::Result<TransitionRequest> {
Ok(TransitionRequest {
id: row.get("id")?,
task_id: row.get("task_id")?,
action: row.get("action")?,
reason: row.get("reason").ok().flatten(),
requested_at: chrono::DateTime::parse_from_rfc3339(
&row.get::<_, String>("requested_at")?,
)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
processed_at: row.get::<_, Option<String>>("processed_at")?.and_then(|s| {
chrono::DateTime::parse_from_rfc3339(&s)
.map(|dt| dt.with_timezone(&chrono::Utc))
.ok()
}),
error: row.get("error")?,
})
}
// ββ Notifications βββββββββββββββββββββββββββββββββββββββββββββββββββ
pub fn create_notification(&self, notif: &Notification) -> Result<()> {
self.conn.execute(
"INSERT INTO notifications (id, message, created_at, task_id, kind)
VALUES (?1, ?2, ?3, ?4, ?5)",
params![
notif.id,
notif.message,
notif.created_at.to_rfc3339(),
notif.task_id,
notif.kind.map(|k| k.as_str()),
],
)?;
Ok(())
}
/// Peek at pending notifications without consuming them.
pub fn peek_notifications(&self) -> Result<Vec<Notification>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM notifications ORDER BY created_at ASC")?;
let notifs: Vec<Notification> = stmt
.query_map([], |row| {
Ok(Notification {
id: row.get("id")?,
message: row.get("message")?,
created_at: chrono::DateTime::parse_from_rfc3339(
&row.get::<_, String>("created_at")?,
)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
task_id: row.get("task_id")?,
kind: row
.get::<_, Option<String>>("kind")?
.as_deref()
.and_then(NotificationKind::from_str),
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(notifs)
}
/// Atomic fetch-and-delete via `DELETE ... RETURNING`.
pub fn consume_notifications(&self) -> Result<Vec<Notification>> {
let mut stmt = self.conn.prepare(
"DELETE FROM notifications
RETURNING id, message, created_at, task_id, kind",
)?;
let mut notifs: Vec<Notification> = stmt
.query_map([], |row| {
Ok(Notification {
id: row.get("id")?,
message: row.get("message")?,
created_at: chrono::DateTime::parse_from_rfc3339(
&row.get::<_, String>("created_at")?,
)
.map(|dt| dt.with_timezone(&chrono::Utc))
.unwrap_or_else(|_| chrono::Utc::now()),
task_id: row.get("task_id")?,
kind: row
.get::<_, Option<String>>("kind")?
.as_deref()
.and_then(NotificationKind::from_str),
})
})?
.filter_map(|r| r.ok())
.collect();
// `RETURNING` doesn't guarantee any particular row order β sort in Rust.
notifs.sort_by(|a, b| a.created_at.cmp(&b.created_at));
Ok(notifs)
}
// ββ Paired devices ββββββββββββββββββββββββββββββββββββββββββββββββββ
pub fn add_mobile_device(&self, device: &MobileDevice) -> Result<()> {
self.conn.execute(
"INSERT INTO mobile_devices (id, label, token_hash, created_at, last_seen, session_id)
VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
params![
device.id,
device.label,
device.token_hash,
device.created_at.to_rfc3339(),
device.last_seen.map(|t| t.to_rfc3339()),
device.session_id,
],
)?;
Ok(())
}
/// The device holding this token hash, if any.
///
/// Looked up by hash rather than compared in Rust so the secret itself
/// never has to be read out of the database to check a request.
pub fn mobile_device_by_hash(&self, token_hash: &str) -> Result<Option<MobileDevice>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM mobile_devices WHERE token_hash = ?1")?;
let mut rows = stmt.query_map(params![token_hash], Self::mobile_device_from_row)?;
Ok(rows.next().transpose()?)
}
pub fn list_mobile_devices(&self) -> Result<Vec<MobileDevice>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM mobile_devices ORDER BY created_at ASC")?;
let rows = stmt
.query_map([], Self::mobile_device_from_row)?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
}
/// Record that a device was used. Callers should throttle: this is a write,
/// and a phone polling the board would otherwise cause one every two
/// seconds to store a timestamp nobody reads that often.
pub fn touch_mobile_device(&self, id: &str) -> Result<()> {
self.conn.execute(
"UPDATE mobile_devices SET last_seen = ?1 WHERE id = ?2",
params![chrono::Utc::now().to_rfc3339(), id],
)?;
Ok(())
}
/// Returns whether a row was removed, so a caller can tell "revoked" from
/// "no such device" rather than reporting success either way.
pub fn revoke_mobile_device(&self, id: &str) -> Result<bool> {
let rows = self
.conn
.execute("DELETE FROM mobile_devices WHERE id = ?1", params![id])?;
Ok(rows > 0)
}
/// Drop every device paired by one serve session.
///
/// Nothing calls this on shutdown β pairings persist until revoked. It is
/// the scoped primitive an expiry or forget-on-exit policy needs, and the
/// scoping is the point: `mobile_devices` is global, so a blanket delete
/// would cut off a second agtx serving another project.
///
/// A `NULL` session_id is never matched, so a row written before the
/// column existed is left for the user to revoke by hand.
pub fn revoke_session_devices(&self, session_id: &str) -> Result<usize> {
Ok(self.conn.execute(
"DELETE FROM mobile_devices WHERE session_id = ?1",
params![session_id],
)?)
}
/// Returns how many were removed.
pub fn revoke_all_mobile_devices(&self) -> Result<usize> {
Ok(self.conn.execute("DELETE FROM mobile_devices", [])?)
}
fn mobile_device_from_row(row: &rusqlite::Row) -> rusqlite::Result<MobileDevice> {
let parse = |s: String| {
chrono::DateTime::parse_from_rfc3339(&s)
.map(|dt| dt.with_timezone(&chrono::Utc))
.ok()
};
Ok(MobileDevice {
id: row.get("id")?,
label: row.get("label")?,
token_hash: row.get("token_hash")?,
created_at: row
.get::<_, Option<String>>("created_at")?
.and_then(parse)
.unwrap_or_else(chrono::Utc::now),
last_seen: row.get::<_, Option<String>>("last_seen")?.and_then(parse),
session_id: row.get("session_id")?,
})
}
// ββ TUI heartbeat βββββββββββββββββββββββββββββββββββββββββββββββββββ
/// Record that a TUI is live on `project_path`, as of now.
///
/// Called on the transition-poll cadence rather than per event-loop
/// iteration: the loop wakes on `HOUSEKEEPING_TICK` (100 ms) and on every
/// keystroke, so beating per iteration would be a write ten-plus times a
/// second to answer a question whose useful resolution is seconds.
pub fn beat_tui_heartbeat(&self, project_path: &str) -> Result<()> {
self.conn.execute(
"INSERT INTO tui_heartbeat (project_path, beat_at) VALUES (?1, ?2)
ON CONFLICT(project_path) DO UPDATE SET beat_at = excluded.beat_at",
params![project_path, chrono::Utc::now().to_rfc3339()],
)?;
Ok(())
}
/// Record that something asked for this project's board.
///
/// Written by the web server on a board request. Callers should throttle:
/// this is a write, and it answers a question whose useful resolution is
/// minutes.
pub fn note_board_watched(&self, project_path: &str) -> Result<()> {
self.conn.execute(
"INSERT INTO board_watch (project_path, beat_at) VALUES (?1, ?2)
ON CONFLICT(project_path) DO UPDATE SET beat_at = excluded.beat_at",
params![project_path, chrono::Utc::now().to_rfc3339()],
)?;
Ok(())
}
/// Whether a board was asked for within `stale_after`.
///
/// The gate on publishing `task_runtime`. Absent and stale are the same
/// answer β nobody is reading β so a missing row is not an error.
pub fn board_watched_recently(
&self,
project_path: &str,
stale_after: chrono::Duration,
) -> Result<bool> {
let beat: Option<String> = self
.conn
.query_row(
"SELECT beat_at FROM board_watch WHERE project_path = ?1",
params![project_path],
|row| row.get(0),
)
.ok();
Ok(beat
.and_then(|b| chrono::DateTime::parse_from_rfc3339(&b).ok())
.is_some_and(|t| chrono::Utc::now() - t.with_timezone(&chrono::Utc) < stale_after))
}
/// Whether a TUI has beaten for `project_path` within `stale_after`.
///
/// Absent and stale are the same answer β nothing is draining the queue β
/// so a missing row is not an error.
pub fn tui_is_live(&self, project_path: &str, stale_after: chrono::Duration) -> Result<bool> {
let beat: Option<String> = self
.conn
.query_row(
"SELECT beat_at FROM tui_heartbeat WHERE project_path = ?1",
params![project_path],
|row| row.get(0),
)
.ok();
Ok(beat
.and_then(|b| chrono::DateTime::parse_from_rfc3339(&b).ok())
.is_some_and(|t| chrono::Utc::now() - t.with_timezone(&chrono::Utc) < stale_after))
}
// ββ Task runtime (published phase status) βββββββββββββββββββββββββββ
/// Publish one refresh pass as a whole: upsert every live task's row and
/// drop rows for tasks that no longer exist, in one transaction.
///
/// The refresh writes every live task on every pass β that is what makes
/// `updated_at` mean "last observed" rather than "last changed", which is
/// the distinction a reader needs to tell a steadily-working task from a
/// board nothing has refreshed since the TUI died. Batching is what keeps
/// that affordable: one commit per pass instead of one per task.
///
/// Pruning removes rows for tasks that no longer *exist*, not rows absent
/// from this pass: the refresh tracks only active phases, so a task that
/// reaches Done stops being published and keeps its last row. That row is
/// stale rather than wrong, and `updated_at` is what a reader checks β the
/// same contract that covers a board nothing is refreshing at all. Pruning
/// to the published set instead would empty the table on any pass that
/// transiently reported nothing.
///
/// Uses an unchecked transaction because the caller holds `&Database`.
pub fn publish_task_runtime(&self, rows: &[TaskRuntime]) -> Result<()> {
let tx = self.conn.unchecked_transaction()?;
for rt in rows {
tx.execute(
"INSERT INTO task_runtime
(task_id, phase_status, pane_hash, pane_changed_at, updated_at, status)
VALUES (?1, ?2, ?3, ?4, ?5, ?6)
ON CONFLICT(task_id) DO UPDATE SET
phase_status = excluded.phase_status,
status = excluded.status,
pane_hash = excluded.pane_hash,
pane_changed_at = excluded.pane_changed_at,
updated_at = excluded.updated_at",
params![
rt.task_id,
rt.phase_status.as_str(),
rt.pane_hash,
rt.pane_changed_at.map(|t| t.to_rfc3339()),
rt.updated_at.to_rfc3339(),
rt.status.map(|s| s.as_str()),
],
)?;
}
// A deleted task's last status would otherwise be served to readers
// forever as current. In the same transaction so a reader never sees a
// pass applied without its prune.
tx.execute(
"DELETE FROM task_runtime WHERE task_id NOT IN (SELECT id FROM tasks)",
[],
)?;
tx.commit()?;
Ok(())
}
pub fn get_task_runtime(&self, task_id: &str) -> Result<Option<TaskRuntime>> {
let mut stmt = self
.conn
.prepare("SELECT * FROM task_runtime WHERE task_id = ?1")?;
let mut rows = stmt.query_map(params![task_id], Self::task_runtime_from_row)?;
Ok(rows.next().transpose()?)
}
pub fn list_task_runtime(&self) -> Result<Vec<TaskRuntime>> {
let mut stmt = self.conn.prepare("SELECT * FROM task_runtime")?;
let rows = stmt
.query_map([], Self::task_runtime_from_row)?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
}
fn task_runtime_from_row(row: &rusqlite::Row) -> rusqlite::Result<TaskRuntime> {
let parse = |s: String| {
chrono::DateTime::parse_from_rfc3339(&s)
.map(|dt| dt.with_timezone(&chrono::Utc))
.ok()
};
Ok(TaskRuntime {
task_id: row.get("task_id")?,
// An unparseable status means a writer from a newer version wrote a
// variant this build has no arm for. Treating that as `Working` is
// the honest fallback: it is the state that claims the least.
phase_status: PhaseStatus::from_str(&row.get::<_, String>("phase_status")?)
.unwrap_or(PhaseStatus::Working),
status: row
.get::<_, Option<String>>("status")
.ok()
.flatten()
.and_then(|s| TaskStatus::from_str(&s)),
pane_hash: row.get("pane_hash")?,
pane_changed_at: row
.get::<_, Option<String>>("pane_changed_at")?
.and_then(parse),
updated_at: row
.get::<_, Option<String>>("updated_at")?
.and_then(parse)
.unwrap_or_else(chrono::Utc::now),
})
}
}