CREATE TABLE deliveries (
id TEXT PRIMARY KEY,
delivery_fp TEXT NOT NULL UNIQUE,
-- idempotency: hash of (channel, thread, kind, day, payload key)
channel TEXT NOT NULL,
-- 'slack'|'email'|'sms' etc.
destination TEXT NOT NULL,
-- e.g. slack channel id,
email address,
phone
thread_id TEXT,
-- slack thread ts or equivalent
kind TEXT NOT NULL,
-- 'nudge'|'plan_view'|'approval_request'|'approval_result'|'answer'
priority INTEGER NOT NULL DEFAULT 0,
status TEXT NOT NULL CHECK (status IN ('queued','sending','sent','failed','dead')),
payload TEXT NOT NULL,
-- JSON: blocks/text/refs (rendered already; do NOT LLM here)
related_episode_id TEXT,
related_event_id TEXT,
related_proposal_id TEXT,
related_action_id TEXT,
related_approval_id TEXT,
last_error TEXT,
attempt_count INTEGER NOT NULL DEFAULT 0,
send_after TEXT,
-- for scheduled nudges
sent_at 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 (related_episode_id) REFERENCES episodes(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (related_event_id) REFERENCES events(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (related_proposal_id) REFERENCES proposals(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (related_action_id) REFERENCES actions(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (related_approval_id) REFERENCES approvals(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
)