CREATE TABLE approvals (
id TEXT PRIMARY KEY,
status TEXT NOT NULL CHECK (status IN (
'open','approved','rejected','executing','needs_followup','failed','applied'
)),
action_id TEXT,
-- canonical FK to actions
proposal_id TEXT,
thread_id TEXT,
channel_id TEXT,
user_id TEXT,
dedupe_key TEXT,
action_fp TEXT,
summary TEXT,
rationale TEXT,
risk TEXT,
tier TEXT,
event_id TEXT,
decided_by TEXT,
decided_at TEXT,
executing_by TEXT,
executing_at TEXT,
applied_at TEXT,
pending_id TEXT,
error TEXT,
delivery_state TEXT,
action_idempotency_key TEXT,
last_delivered_at TEXT,
last_nudged_at TEXT,
version INTEGER NOT NULL DEFAULT 1,
attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 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')),
CHECK (created_at <= updated_at),
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (action_id) REFERENCES actions(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
)