CREATE TABLE proposals (
id TEXT PRIMARY KEY,
episode_id TEXT,
event_id TEXT,
-- triggering event (usually user_interaction or signal_ingest)
status TEXT NOT NULL CHECK (status IN (
'new','claimed','accepted','rejected','needs_followup','superseded','applied','failed'
)),
kind TEXT NOT NULL,
-- 'triage'|'plan'|'explain'|'capture' etc.
model TEXT,
-- which model produced it
prompt_fp TEXT,
-- fingerprint of the context+prompt (for debugging)
proposal_fp TEXT NOT NULL UNIQUE,
-- canonical hash of proposal JSON (idempotency)
-- human-readable metadata (optional)
summary TEXT,
rationale TEXT,
risk TEXT,
-- the actual proposal contents
payload TEXT NOT NULL,
-- JSON: fact_ops[],
commitment_ops[],
inbox_ops[],
action_candidates[],
followups[]
-- durable claiming / leasing for daemons
claim_id TEXT,
-- unique id for this claim attempt
claimed_by TEXT,
-- daemon/worker id
claimed_at TEXT,
lease_expires_at TEXT,
-- claim lease expiry; daemon must renew or release
-- terminal bookkeeping (helps UX + debugging)
accepted_at TEXT,
rejected_at TEXT,
applied_at TEXT,
failed_at TEXT,
superseded_by TEXT,
-- proposal_id that superseded this one (optional)
-- error/debug
last_error TEXT,
attempt_count INTEGER NOT NULL DEFAULT 0,
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,
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (superseded_by) REFERENCES proposals(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
)