Persistence

tools/api/persistence.py:131 ApiPersistence — thread-safe SQLite at reports/api_runtime.db. Separate from Flow B research.db; DB file lives inside reports_dir and is held open by the live instance (never deleted on reset — rows are deleted instead).

Schema

DDL (tools/api/persistence.py:21):

CREATE TABLE _migrations(version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL);

CREATE TABLE runs(
  id TEXT PRIMARY KEY,
  created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
  state TEXT NOT NULL DEFAULT 'draft',
  request_json TEXT NOT NULL DEFAULT '{}',
  preview_json TEXT NOT NULL DEFAULT '{}',
  result_json TEXT NOT NULL DEFAULT '{}',
  resumed_from TEXT NOT NULL DEFAULT '',
  error TEXT NOT NULL DEFAULT '',
  cancelled_at TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL DEFAULT ''          -- v2
);
CREATE TABLE decisions(
  id TEXT PRIMARY KEY, run_id TEXT NOT NULL,
  kind TEXT NOT NULL, prompt_text TEXT NOT NULL DEFAULT '',
  required_text TEXT NOT NULL DEFAULT '', options_json TEXT NOT NULL DEFAULT '[]',
  status TEXT NOT NULL DEFAULT 'pending', answer TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL, answered_at TEXT NOT NULL DEFAULT '',
  FOREIGN KEY(run_id) REFERENCES runs(id) ON DELETE CASCADE
);
CREATE TABLE users(                       -- D4
  id TEXT PRIMARY KEY, username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL, password_salt TEXT NOT NULL,
  created_at TEXT NOT NULL, last_login TEXT NOT NULL DEFAULT ''
);
CREATE TABLE annotations(                  -- D4
  id TEXT PRIMARY KEY, run_id TEXT NOT NULL, user_id TEXT NOT NULL,
  username TEXT NOT NULL DEFAULT '', body TEXT NOT NULL DEFAULT '',
  finding_ref TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL,
  FOREIGN KEY(run_id) REFERENCES runs(id) ON DELETE CASCADE
);
CREATE INDEX idx_decisions_run_id ON decisions(run_id);
CREATE INDEX idx_runs_state ON runs(state);
CREATE INDEX idx_annotations_run_id ON annotations(run_id);

Version (tools/api/persistence.py:18) 3. Migrations applied idempotently in _init_db (tools/api/persistence.py:151):

• v2 (tools/api/persistence.py:90): ALTER TABLE runs ADD COLUMN title TEXT NOT NULL DEFAULT '' when column missing.
• v3 (tools/api/persistence.py:97): create users + annotations idempotently (new DBs get them via DDL).
• Always INSERT OR IGNORE (_SCHEMA_VERSION, now).

Thread safety: self._lock = threading.Lock() (tools/api/persistence.py:138) around every _connect() (tools/api/persistence.py:145, check_same_thread=False, PRAGMA foreign_keys=ON, row_factory=sqlite3.Row).

Construction & Lifecycle

ApiPersistence(reports_dir: Path) (tools/api/persistence.py:133): reports_dir.mkdir(parents=True), self._path = reports_dir / "api_runtime.db", _init_db().

app.py:81 creates it; app.py:100 lifespan calls recover_interrupted() on startup.

Runs

Sort clauses (tools/api/persistence.py:113):

ID shape: _new_id(prefix) (tools/api/persistence.py:127) → "{prefix}-{uuid4 hex[:12]}" (usr-, ann-, dec-, run- via preview).

Decisions

Users (D4)

Only wired when api.multi_operator → users_routes mounted (app.py:144). PBKDF2 hashing lives in tools/api/auth.py, not here.

Annotations (D4)

Operator comments on findings, per run_id so the WebUI can render inline with the timeline.

Events

Events are not rows — RunEventBroker writes reports/<run_id>/events.jsonl + in-memory ring. Persistence only creates the reports/<run_id>/ directory and owns the reports_dir path (tools/api/persistence.py:142 reports_dir property) used by brokers and list_runs artifact overlays.

Reset Semantics

POST /api/v1/system/reset (tools/api/routes/system.py:526) refuses when run_manager.has_active, else reset_all() (rows) + shutil.rmtree(reports_dir) etc., then mkdir + _init_db() so the live ApiPersistence keeps working. research_workspace/research.db tables are wiped in-place (file held open).

Tests

tests/test_api_persistence.py exercises create_run/list_runs/get_run/update_run_state/recover_interrupted/decisions CRUD; tests/test_api_runs.py exercises run lifecycle via the HTTP layer.