event_store.db — deliveries

• actions
• approvals
• commitments
• constraints
• dead_letters
• decisions
• deliveries
• effects
• entities
• entity_aliases
• entity_tags
• episode_context_index
• episodes
• event_subjects
• events
• fact_sources
• facts
• inbox_items
• inbox_links
• jobs
• leader_leases
• links
• memory_items
• memory_journal
• memory_state
• pending_interactions
• proposal_sources
• proposals
• reminders
• schedules
• schema_migrations
• signal_fps
• signals
• slack_deliveries
• source_cursors
• sources
• sqlite_sequence
• stream_cursors
• threads
• watchers

Toggle helper tables

• Structure
• Content
• Query
• Insert
• Drop
• Import
• Export

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
)

CREATE INDEX idx_deliveries_status_after
ON deliveries(status, send_after)

CREATE INDEX idx_deliveries_status_updated
ON deliveries(status, updated_at DESC)