CREATE TABLE actions (
id TEXT PRIMARY KEY,
-- stable action id (from action_id_for or event payload)
type TEXT NOT NULL,
params TEXT NOT NULL,
tier TEXT NOT NULL CHECK (tier IN ('T0','T1','T2','T3')),
-- lifecycle (projection)
status TEXT NOT NULL CHECK (status IN (
'queued','needs_approval','approved','executing','applied','failed','cancelled'
)),
idempotency_key TEXT NOT NULL UNIQUE,
episode_id TEXT,
proposal_id TEXT,
-- from materializer
approval_id TEXT,
-- optional,
if you want a direct link
source_event_id TEXT,
-- context/proposal/approval event that caused creation
-- work leasing (ONLY mutated by ActionStore claim/renew/release)
claim_id TEXT,
claimed_by TEXT,
claimed_at TEXT,
lease_expires_at TEXT,
attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
-- terminal metadata (projection)
applied_at TEXT,
failed_at TEXT,
last_error TEXT,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
)