-- Migration 003: durable work items, run causes, observer cursors and failure cases.
BEGIN IMMEDIATE;
CREATE TABLE work_items (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id),
purpose TEXT NOT NULL,
cause_kind TEXT NOT NULL CHECK(cause_kind IN ('input_frame','deadline_wake','maintenance_ready','check_due','job_completed')),
cause_payload TEXT NOT NULL DEFAULT '{}' CHECK(json_valid(cause_payload)),
readiness TEXT NOT NULL DEFAULT 'ready' CHECK(readiness IN ('ready','blocked','paused')),
blocked_reason TEXT,
evidence_refs TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(evidence_refs)),
impact INTEGER NOT NULL DEFAULT 2 CHECK(impact IN (1,2,3,4)),
urgency_ms INTEGER,
aging_ms INTEGER NOT NULL DEFAULT 0 CHECK(aging_ms >= 0),
reporter_id TEXT NOT NULL,
origin_kind TEXT NOT NULL CHECK(origin_kind IN ('human','agent','system','external')),
status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','claimed','completed','failed','deferred')),
attempt_id TEXT,
claimed_ms INTEGER,
completed_ms INTEGER,
disposition TEXT,
failed_ms INTEGER,
error TEXT,
created_ms INTEGER NOT NULL CHECK(created_ms >= 0)
) STRICT;
CREATE INDEX idx_work_items_session_status ON work_items(session_id, status);
CREATE INDEX idx_work_items_status ON work_items(status);
CREATE TABLE observer_cursors (
partition TEXT PRIMARY KEY,
last_seq INTEGER NOT NULL DEFAULT 0 CHECK(last_seq >= 0),
updated_ms INTEGER NOT NULL DEFAULT 0 CHECK(updated_ms >= 0)
) STRICT;
CREATE TABLE observer_cases (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id),
operation TEXT NOT NULL,
component_revision TEXT NOT NULL,
signature TEXT NOT NULL,
workspace TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 1 CHECK(count >= 1),
first_seen_ms INTEGER NOT NULL CHECK(first_seen_ms >= 0),
last_seen_ms INTEGER NOT NULL CHECK(last_seen_ms >= 0),
evidence_refs TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(evidence_refs)),
work_item_id TEXT REFERENCES work_items(id)
) STRICT;
CREATE INDEX idx_observer_cases_session ON observer_cases(session_id);
PRAGMA user_version = 3;
COMMIT;