event_store.db — approvals

• 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 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
)

CREATE INDEX idx_approvals_action_fp
ON approvals (action_fp)

CREATE INDEX idx_approvals_action_idk
ON approvals(action_idempotency_key)

CREATE INDEX idx_approvals_action_status_updated
ON approvals (action_id, status, updated_at DESC)

CREATE INDEX idx_approvals_dedupe_key
ON approvals(dedupe_key)

CREATE INDEX idx_approvals_proposal
ON approvals(proposal_id, status, updated_at DESC)

CREATE INDEX idx_approvals_status
ON approvals (status)

CREATE INDEX idx_approvals_status_updated_action
ON approvals (status, updated_at DESC, action_id)

CREATE INDEX idx_approvals_thread_status_created
ON approvals(thread_id, status, created_at ASC)

CREATE INDEX idx_approvals_thread_status_updated
ON approvals(thread_id, status, updated_at DESC)