AI-powered virtual executive team — a single coherent executive persona backed by 8 specialist agents (FastAPI + Next.js).
"""SQLite persistence for workflow runs.
Reuses the same DB file as `memory/episodic.py` (so a single backup covers
everything). The table is initialized lazily on first write.
Phase 6 additions
-----------------
Four columns added via idempotent ALTER:
state_json TEXT — checkpoint dict (on_timeout, channel, etc.)
awaiting_person_id INTEGER — person we're waiting on
awaiting_until TEXT — ISO timestamp for timeout
resolution_json TEXT — serialized WaitForHumanResolution on match
Resume additions
----------------
Three more columns, same idempotent ALTER:
resume_state_json TEXT — serialized WorkflowResumeState; NULL means the
gate is pause-only and nothing will continue it
resumed_at TEXT — ISO timestamp a resume was claimed at, so a
worker that dies mid-resume can be found again
resume_attempts INTEGER — bounded retries for that requeue, reset at each
new gate (a new gate is new work, not a retry)
resume_claim TEXT — fencing token for the live claim; a worker's
terminal write is refused once it is superseded
Ownership
---------
owner_person_id INTEGER — the person who started the run by hand (in
their chat, or with Run on the Jobs page); its
history and output are theirs alone. NULL is a
scheduled or system run, shared with the team.
New status values beyond running/done/error:
awaiting_human — paused, waiting for a human reply
resolved — human replied, resolution stored in resolution_json.
NOT terminal when resume_state_json is set: the resumer
claims such a row (resolved -> running) and executes the
steps after the gate.
timed_out — awaiting_until passed, timeout policy applied
"""
from __future__ import annotations
import json
import sqlite3
import uuid
from datetime import UTC, datetime
from pathlib import Path
from typing import Any, Literal
from openexecutive.memory.episodic import DB_PATH, _get_conn
def _resolve(db_path: Path | None) -> Path:
"""Dynamic DB_PATH resolution — lets tests monkeypatch persistence.DB_PATH."""
return db_path if db_path is not None else DB_PATH
def initialize_runs_db(db_path: Path | None = None) -> None:
"""Create the workflow_runs table if it doesn't exist. Idempotent."""
with _get_conn(_resolve(db_path)) as conn:
conn.execute(
"""
CREATE TABLE IF NOT EXISTS workflow_runs (
run_id TEXT PRIMARY KEY,
workflow_name TEXT NOT NULL,
title TEXT NOT NULL,
status TEXT NOT NULL,
inputs TEXT NOT NULL,
artifact TEXT,
error TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
)
"""
)
conn.execute(
"CREATE INDEX IF NOT EXISTS workflow_runs_workflow_idx "
"ON workflow_runs (workflow_name, updated_at DESC)"
)
# Phase 6 additive columns — idempotent via PRAGMA + try/except pattern.
existing = {row[1] for row in conn.execute("PRAGMA table_info(workflow_runs)")}
for col, ddl in (
("state_json", "TEXT"),
("awaiting_person_id", "INTEGER"),
("awaiting_until", "TEXT"),
("resolution_json", "TEXT"),
# Artifacts gallery soft-delete: NULL = active, ISO ts = archived.
("archived_at", "TEXT"),
# Resume: the engine payload, plus the claim bookkeeping that lets
# a run stranded in 'running' by a crashed worker be requeued.
("resume_state_json", "TEXT"),
("resumed_at", "TEXT"),
("resume_attempts", "INTEGER NOT NULL DEFAULT 0"),
# Fencing token for the current claim. A worker writes its
# terminal row only if this still matches what it claimed with,
# so a worker wrongly declared dead cannot overwrite the one that
# replaced it.
("resume_claim", "TEXT"),
# Who started the run by hand; NULL = a team run (module doc).
("owner_person_id", "INTEGER"),
):
if col not in existing:
try:
conn.execute(f"ALTER TABLE workflow_runs ADD COLUMN {col} {ddl}")
except sqlite3.OperationalError as exc:
if "duplicate column" not in str(exc).lower():
raise
def create_run(
run_id: str,
workflow_name: str,
title: str,
inputs: dict[str, Any],
db_path: Path | None = None,
owner_person_id: int | None = None,
) -> None:
"""Record a new run. ``owner_person_id`` is who started it by hand (the
run is then theirs alone); leave it None for a scheduled or system run."""
initialize_runs_db(db_path) # _resolve happens inside
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
conn.execute(
"""
INSERT INTO workflow_runs
(run_id, workflow_name, title, status, inputs, created_at, updated_at,
owner_person_id)
VALUES (?, ?, ?, 'running', ?, ?, ?, ?)
""",
(run_id, workflow_name, title, json.dumps(inputs), now, now, owner_person_id),
)
def run_visible_to(run: dict[str, Any], person_id: int | None) -> bool:
"""Whether ``person_id`` may see ``run``: a team run (no owner) anyone
may; one someone started only they may, and the person it is waiting on
to answer it. Not even the principal sees a teammate's own run."""
owner = run.get("owner_person_id")
if owner is None:
return True
return person_id is not None and person_id in (owner, run.get("awaiting_person_id"))
def _visible_clause(person_id: int | None) -> tuple[str, list[Any]]:
"""`run_visible_to` as SQL."""
if person_id is None:
return "owner_person_id IS NULL", []
return (
"(owner_person_id IS NULL OR owner_person_id = ? OR awaiting_person_id = ?)",
[person_id, person_id],
)
# Stored in place of an artifact that drew on what is private to the
# principal (a run reports it as `private_to_principal` on its `result`
# event). A team run's history is readable by everyone signed in to the
# workspace (`run_visible_to`); the
# principal got the full text where it was delivered to them.
PRIVATE_RUN_ARTIFACT = (
"(Delivered to the principal. It drew on what is private to them, so its "
"text is not kept in the shared run history.)"
)
def stored_artifact(artifact: str, *, private_to_principal: bool) -> str:
"""What a run's history keeps for ``artifact``."""
return PRIVATE_RUN_ARTIFACT if private_to_principal and artifact else artifact
def complete_run(
run_id: str,
artifact: str,
db_path: Path | None = None,
) -> None:
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
conn.execute(
"UPDATE workflow_runs SET status = 'done', artifact = ?, updated_at = ? "
"WHERE run_id = ?",
(artifact, now, run_id),
)
def fail_run(
run_id: str,
error: str,
db_path: Path | None = None,
) -> None:
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
conn.execute(
"UPDATE workflow_runs SET status = 'error', error = ?, updated_at = ? "
"WHERE run_id = ?",
(error, now, run_id),
)
def get_run(run_id: str, db_path: Path | None = None) -> dict[str, Any] | None:
if not _resolve(db_path).exists():
return None
with _get_conn(_resolve(db_path)) as conn:
row = conn.execute(
"SELECT * FROM workflow_runs WHERE run_id = ?", (run_id,)
).fetchone()
if row is None:
return None
out = dict(row)
try:
out["inputs"] = json.loads(out["inputs"]) if out["inputs"] else {}
except json.JSONDecodeError:
out["inputs"] = {}
return out
# How much of each artifact `list_runs(with_body=True)` reads.
BODY_CHARS = 20_000
def list_runs(
workflow_name: str | None = None,
limit: int = 100,
db_path: Path | None = None,
status: str | None = None,
*,
visible_to: int | None | Literal["all"] = "all",
with_body: bool = False,
) -> list[dict[str, Any]]:
"""Recent runs, newest-updated first. `workflow_name` and `status` are
optional SQL filters — pushing `status` into the query (rather than letting
callers filter the returned page) ensures a `status='done'` caller isn't
starved when the most-recent `limit` rows are dominated by running/awaiting
runs. `visible_to` keeps only the runs that person may see
(`run_visible_to`; None = nobody on the roster: team runs only); the
default "all" is for the server's own reads, never a person's.
`with_body` adds each run's `inputs` (parsed) and the start of its
`artifact` (`BODY_CHARS`), enough to say what came of it."""
if not _resolve(db_path).exists():
return []
clauses: list[str] = []
params: list[Any] = []
if visible_to != "all":
clause, clause_params = _visible_clause(visible_to)
clauses.append(clause)
params.extend(clause_params)
if workflow_name:
clauses.append("workflow_name = ?")
params.append(workflow_name)
if status:
clauses.append("status = ?")
params.append(status)
where = f"WHERE {' AND '.join(clauses)} " if clauses else ""
params.append(limit)
with _get_conn(_resolve(db_path)) as conn:
rows = conn.execute(
"SELECT run_id, workflow_name, title, status, created_at, updated_at"
+ (f", inputs, substr(artifact, 1, {BODY_CHARS}) AS artifact " if with_body else " ")
+ f"FROM workflow_runs {where}ORDER BY updated_at DESC LIMIT ?",
params,
).fetchall()
out = [dict(r) for r in rows]
if with_body:
for run in out:
try:
run["inputs"] = json.loads(run["inputs"]) if run["inputs"] else {}
except json.JSONDecodeError:
run["inputs"] = {}
return out
def list_artifact_runs(
limit: int = 200,
db_path: Path | None = None,
archived: bool = False,
*,
visible_to: int | None,
) -> list[dict[str, Any]]:
"""Completed runs that produced an artifact, newest first, that
`visible_to` may see (`run_visible_to`).
Powers the Executive Artifacts section. Mirrors `list_runs` — the heavy
`artifact` body is intentionally excluded from the list query (fetch it
per-run via `get_run`). Only `status='done'` runs with a non-empty
artifact qualify; running/failed/awaiting runs have nothing to show. The
`artifact != ''` guard keeps this list consistent with the artifacts
detail route, which treats an empty body as "no artifact".
`archived` selects which slice to return: the default (False) lists only
active artifacts (`archived_at IS NULL`); True lists only archived ones,
mirroring `alerts.store.list_artifact_alerts`.
"""
if not _resolve(db_path).exists():
return []
archived_clause = (
"AND archived_at IS NOT NULL" if archived else "AND archived_at IS NULL"
)
visible, params = _visible_clause(visible_to)
with _get_conn(_resolve(db_path)) as conn:
rows = conn.execute(
"SELECT run_id, workflow_name, title, status, created_at, updated_at, "
"archived_at, owner_person_id "
"FROM workflow_runs "
"WHERE artifact IS NOT NULL AND artifact != '' AND status = 'done' "
f"{archived_clause} AND {visible} "
"ORDER BY created_at DESC LIMIT ?",
(*params, limit),
).fetchall()
return [dict(r) for r in rows]
def set_run_archived(
run_id: str, archived: bool, db_path: Path | None = None
) -> bool:
"""Archive (soft-hide) or restore a workflow-run artifact.
Sets `archived_at` to the current time when archiving, or NULL when
restoring. Returns True if a row was updated. Mirrors
`alerts.store.set_alert_archived`.
"""
if not _resolve(db_path).exists():
return False
now = datetime.now(UTC).isoformat() if archived else None
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
"UPDATE workflow_runs SET archived_at = ? WHERE run_id = ?",
(now, run_id),
)
return cur.rowcount > 0
def delete_run(run_id: str, db_path: Path | None = None) -> bool:
if not _resolve(db_path).exists():
return False
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute("DELETE FROM workflow_runs WHERE run_id = ?", (run_id,))
return cur.rowcount > 0
# ---------------------------------------------------------------------------
# Phase 6 — WaitForHuman checkpoint helpers
# ---------------------------------------------------------------------------
def save_checkpoint(
run_id: str,
state_json: str,
awaiting_person_id: int | None,
awaiting_until: datetime | None,
db_path: Path | None = None,
*,
resume_state_json: str | None = None,
expect_claim: str | None = None,
) -> bool:
"""Persist a WaitForHuman pause point and mark the run awaiting_human.
``resume_state_json`` is keyword-only and defaulted so every existing
positional caller keeps working; NULL leaves the gate pause-only.
``resolution_json``, ``resumed_at``, ``resume_claim`` and
``resume_attempts`` are all CLEARED here, which is what makes a *second*
gate correct. A run resumed past gate 1 still carries gate 1's resolution
and claim, and leaving those would let the stale-resume sweep and the
executor act on gate 1's answer at gate 2. ``resume_attempts`` resets
because a new gate is a new unit of work, not a retry of the last one —
counting gates against the crash-recovery budget would leave a three-gate
workflow with none by its final gate.
Gate 1's decision is not lost by the clear: the engine records it as the
gate step's own output, so it travels on inside the new
``resume_state_json`` and lands in the artifact.
``expect_claim`` fences a SECOND-gate checkpoint: pass the token the
caller claimed with and the write is refused if that claim has since been
superseded, so a worker wrongly declared dead cannot park a run the live
worker has already finished. Returns whether a row was written.
"""
initialize_runs_db(db_path) # _resolve happens inside
now = datetime.now(UTC).isoformat()
until_str = awaiting_until.isoformat() if awaiting_until else None
clause = " AND resume_claim = ?" if expect_claim is not None else ""
params: list[Any] = [
state_json, awaiting_person_id, until_str, resume_state_json, now, run_id
]
if expect_claim is not None:
params.append(expect_claim)
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
f"""
UPDATE workflow_runs
SET status = 'awaiting_human',
state_json = ?,
awaiting_person_id = ?,
awaiting_until = ?,
resume_state_json = ?,
resolution_json = NULL,
resumed_at = NULL,
resume_claim = NULL,
resume_attempts = 0,
updated_at = ?
WHERE run_id = ?{clause}
""",
params,
)
return cur.rowcount > 0
def load_checkpoint(
run_id: str,
db_path: Path | None = None,
) -> tuple[str, int | None, datetime | None] | None:
"""Return (state_json, awaiting_person_id, awaiting_until) or None."""
if not _resolve(db_path).exists():
return None
with _get_conn(_resolve(db_path)) as conn:
row = conn.execute(
"SELECT state_json, awaiting_person_id, awaiting_until "
"FROM workflow_runs WHERE run_id = ?",
(run_id,),
).fetchone()
if row is None or row[0] is None:
return None
until: datetime | None = None
if row[2]:
try:
until = datetime.fromisoformat(row[2])
if until.tzinfo is None:
until = until.replace(tzinfo=UTC)
except ValueError:
pass
return (row[0], row[1], until)
def list_awaiting_runs(db_path: Path | None = None) -> list[dict[str, Any]]:
"""Return all runs with status='awaiting_human'."""
if not _resolve(db_path).exists():
return []
with _get_conn(_resolve(db_path)) as conn:
rows = conn.execute(
"SELECT run_id, workflow_name, title, awaiting_person_id, "
"awaiting_until, state_json, resolution_json, updated_at "
"FROM workflow_runs WHERE status = 'awaiting_human' "
"ORDER BY updated_at"
).fetchall()
result = []
for r in rows:
d = dict(zip(
("run_id", "workflow_name", "title", "awaiting_person_id",
"awaiting_until", "state_json", "resolution_json", "updated_at"),
r,
strict=False,
))
result.append(d)
return result
def store_resolution(
run_id: str,
resolution_json: str,
db_path: Path | None = None,
) -> bool:
"""Store a resolution and mark the run resolved. Idempotent."""
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
"""
UPDATE workflow_runs
SET status = 'resolved', resolution_json = ?, updated_at = ?
WHERE run_id = ? AND status = 'awaiting_human'
""",
(resolution_json, now, run_id),
)
return cur.rowcount > 0
# ---------------------------------------------------------------------------
# Resume — claim/execute helpers for the background executor
# ---------------------------------------------------------------------------
def list_resumable_runs(
limit: int = 50,
db_path: Path | None = None,
) -> list[dict[str, Any]]:
"""Resolved runs that carry a resume payload, oldest-updated first.
A `resolved` row WITHOUT `resume_state_json` is a pause-only gate and is
deliberately excluded — its decision was the whole point, and claiming it
would flip a finished run back to `running` forever.
"""
if not _resolve(db_path).exists():
return []
cols = (
"run_id", "workflow_name", "title", "inputs",
"resume_state_json", "resolution_json", "updated_at",
)
with _get_conn(_resolve(db_path)) as conn:
rows = conn.execute(
f"SELECT {', '.join(cols)} FROM workflow_runs "
"WHERE status = 'resolved' AND resume_state_json IS NOT NULL "
"ORDER BY updated_at LIMIT ?",
(limit,),
).fetchall()
out: list[dict[str, Any]] = []
for r in rows:
d = dict(zip(cols, r, strict=False))
try:
d["inputs"] = json.loads(d["inputs"]) if d["inputs"] else {}
except json.JSONDecodeError:
d["inputs"] = {}
out.append(d)
return out
def claim_run_for_resume(run_id: str, db_path: Path | None = None) -> str | None:
"""Atomically take ownership of a resolved run: resolved -> running.
Returns a fencing token on success, or None if another worker won the race
or the row moved on. This single guarded UPDATE is what prevents double
execution between the resumer poll and the immediate post-resolution kick.
The token matters because the claim is not a lease and cannot be: the
stale sweep decides a worker is dead from a wall-clock guess, and a slow
but ALIVE worker will eventually be declared dead and replaced. Terminal
writes carry the token so the superseded worker's late `complete_run` (or
worse, its `save_checkpoint` at a later gate) is refused instead of
overwriting — or resurrecting — the run the replacement finished.
"""
if not _resolve(db_path).exists():
return None
now = datetime.now(UTC).isoformat()
token = uuid.uuid4().hex
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
"""
UPDATE workflow_runs
SET status = 'running',
resumed_at = ?,
resume_claim = ?,
resume_attempts = resume_attempts + 1,
updated_at = ?
WHERE run_id = ?
AND status = 'resolved'
AND resume_state_json IS NOT NULL
""",
(now, token, now, run_id),
)
return token if cur.rowcount > 0 else None
def touch_resume_claim(run_id: str, claim: str, db_path: Path | None = None) -> bool:
"""Heartbeat a live resume: bump ``resumed_at`` while ``claim`` still holds.
The stale sweep measures a claim's age from ``resumed_at``. A resume that
runs workflow ``action`` steps can legitimately outlast any fixed window
(tool calls, several steps), and a requeue while it is still alive would
replay its external side effects on a second worker. The resumer calls this
as events flow, so a live worker never looks dead. Returns False once the
claim has been superseded — the caller must stop acting.
"""
if not _resolve(db_path).exists():
return False
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
"""
UPDATE workflow_runs
SET resumed_at = ?
WHERE run_id = ?
AND resume_claim = ?
AND status = 'running'
""",
(now, run_id, claim),
)
return cur.rowcount > 0
def finish_resumed_run(
run_id: str,
claim: str,
*,
artifact: str | None = None,
error: str | None = None,
db_path: Path | None = None,
) -> bool:
"""Write a resumed run's terminal row, but only if the claim still holds.
The fenced counterpart to `complete_run` / `fail_run`, which have no status
guard at all. Without the fence a worker the stale sweep replaced could
land its result on top of the replacement's, or flip an already-`done` run
back to `error`. Also clears the resume payload, so the row cannot be
re-claimed. Returns False when the claim was superseded — the caller
should do nothing further.
"""
if not _resolve(db_path).exists():
return False
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
"""
UPDATE workflow_runs
SET status = ?,
artifact = COALESCE(?, artifact),
error = ?,
resume_state_json = NULL,
resumed_at = NULL,
resume_claim = NULL,
updated_at = ?
WHERE run_id = ? AND status = 'running' AND resume_claim = ?
""",
(
"done" if artifact is not None else "error",
artifact,
error,
now,
run_id,
claim,
),
)
return cur.rowcount > 0
def clear_resume_state(run_id: str, db_path: Path | None = None) -> None:
"""Drop the resume payload once a run reaches a terminal state.
Keeps a finished run from being re-claimed, and keeps the (potentially
large) completed-step text out of rows nothing will replay.
"""
if not _resolve(db_path).exists():
return
with _get_conn(_resolve(db_path)) as conn:
conn.execute(
"UPDATE workflow_runs SET resume_state_json = NULL, resumed_at = NULL, "
"resume_claim = NULL WHERE run_id = ?",
(run_id,),
)
def list_stale_resuming_runs(
older_than: datetime,
max_attempts: int = 3,
db_path: Path | None = None,
) -> list[str]:
"""Run ids whose resume was claimed but never finished.
Crash recovery. `sweep_stale_awaiting` only looks at `awaiting_human`, so
without this a process killed between the claim and the terminal write
would leave the run in `running` with nobody coming back for it.
A run legitimately in `running` has no `resume_state_json`, and a run
parked at a later gate is `awaiting_human`, so neither is ever matched.
"""
if not _resolve(db_path).exists():
return []
with _get_conn(_resolve(db_path)) as conn:
rows = conn.execute(
"""
SELECT run_id FROM workflow_runs
WHERE status = 'running'
AND resume_state_json IS NOT NULL
AND resumed_at IS NOT NULL
AND resumed_at < ?
AND resume_attempts < ?
ORDER BY resumed_at
""",
(older_than.isoformat(), max_attempts),
).fetchall()
return [r[0] for r in rows]
def list_exhausted_resuming_runs(
older_than: datetime,
max_attempts: int = 3,
db_path: Path | None = None,
) -> list[str]:
"""Stranded resumes that have used up their retry budget.
The companion to `list_stale_resuming_runs`, and the reason the bound is
not simply a filter: a run that exceeds it drops out of that query, but it
is `running` with a payload, so `list_resumable_runs` (which wants
`resolved`) never sees it either. Without this it would sit in `running`
forever, showing as in-progress on /jobs, in no queue at all.
"""
if not _resolve(db_path).exists():
return []
with _get_conn(_resolve(db_path)) as conn:
rows = conn.execute(
"""
SELECT run_id FROM workflow_runs
WHERE status = 'running'
AND resume_state_json IS NOT NULL
AND resumed_at IS NOT NULL
AND resumed_at < ?
AND resume_attempts >= ?
ORDER BY resumed_at
""",
(older_than.isoformat(), max_attempts),
).fetchall()
return [r[0] for r in rows]
def requeue_run_for_resume(
run_id: str,
db_path: Path | None = None,
*,
stale_before: datetime | None = None,
) -> bool:
"""running -> resolved, so the next tick re-claims it. Guarded, idempotent.
``stale_before`` re-checks staleness in the same UPDATE: the sweep reads
stale ids and then requeues them, and a live worker's heartbeat
(``touch_resume_claim``) can land in between. With the cutoff in the
WHERE clause, a fresh heartbeat wins and the run is left alone.
`resume_attempts` is NOT reset here — it is the bound that stops a run
which reliably kills its worker from being retried forever. It IS reset by
`save_checkpoint`, because reaching a new gate is new work rather than
another attempt at the last one.
"""
if not _resolve(db_path).exists():
return False
now = datetime.now(UTC).isoformat()
sql = (
"UPDATE workflow_runs SET status = 'resolved', resumed_at = NULL, "
# Breaking the claim is the point: the worker we just declared
# dead may in fact be alive, and this is what stops its late
# write from landing on the replacement's work.
"resume_claim = NULL, updated_at = ? "
"WHERE run_id = ? AND status = 'running' "
"AND resume_state_json IS NOT NULL"
)
params: list[str] = [now, run_id]
if stale_before is not None:
sql += " AND resumed_at IS NOT NULL AND resumed_at < ?"
params.append(stale_before.isoformat())
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(sql, params)
return cur.rowcount > 0
def mark_timed_out(run_id: str, db_path: Path | None = None) -> bool:
"""Transition awaiting_human → timed_out. Returns True if updated."""
now = datetime.now(UTC).isoformat()
with _get_conn(_resolve(db_path)) as conn:
cur = conn.execute(
"UPDATE workflow_runs SET status = 'timed_out', updated_at = ? "
"WHERE run_id = ? AND status = 'awaiting_human'",
(now, run_id),
)
return cur.rowcount > 0