Query

event_store.db — actions

SQL

CREATE TABLE actions  (
  id TEXT PRIMARY KEY,
  -- stable action id (from action_id_for or event payload)
  type TEXT NOT NULL,
  params TEXT NOT NULL,
  tier TEXT NOT NULL CHECK (tier IN ('T0','T1','T2','T3')),
  -- lifecycle (projection)
  status TEXT NOT NULL CHECK (status IN (
    'queued','needs_approval','approved','executing','applied','failed','cancelled'
  )),
  idempotency_key TEXT NOT NULL UNIQUE,
  episode_id TEXT,
  proposal_id TEXT,
  -- from materializer
  approval_id TEXT,
  -- optional,
  if you want a direct link
  source_event_id TEXT,
  -- context/proposal/approval event that caused creation
  -- work leasing (ONLY mutated by ActionStore claim/renew/release)
  claim_id TEXT,
  claimed_by TEXT,
  claimed_at TEXT,
  lease_expires_at TEXT,
  attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
  -- terminal metadata (projection)
  applied_at TEXT,
  failed_at TEXT,
  last_error 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 (episode_id) REFERENCES episodes(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id TEXT ✓ ✓ Rename | Drop
type TEXT Rename | Drop
params TEXT Rename | Drop
tier TEXT Rename | Drop
status TEXT Rename | Drop
idempotency_key TEXT Rename | Drop
episode_id TEXT ✓ Rename | Drop
proposal_id TEXT ✓ Rename | Drop
approval_id TEXT ✓ Rename | Drop
source_event_id TEXT ✓ Rename | Drop
claim_id TEXT ✓ Rename | Drop
claimed_by TEXT ✓ Rename | Drop
claimed_at TEXT ✓ Rename | Drop
lease_expires_at TEXT ✓ Rename | Drop
attempt_count INTEGER Rename | Drop
applied_at TEXT ✓ Rename | Drop
failed_at TEXT ✓ Rename | Drop
last_error TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
episode_id episodes.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_actions_episode_status
  • episode_id
  • status
 SQL 
CREATE INDEX idx_actions_episode_status
ON actions (episode_id, status)
Drop
idx_actions_lease
  • status
  • lease_expires_at
 SQL 
CREATE INDEX idx_actions_lease
ON actions (status, lease_expires_at)
Drop
idx_actions_proposal
  • proposal_id
  • status
  • updated_at
 SQL 
CREATE INDEX idx_actions_proposal
ON actions (proposal_id, status, updated_at DESC)
Drop
idx_actions_status_created_at
  • status
  • created_at
 SQL 
CREATE INDEX idx_actions_status_created_at
ON actions (status, created_at)
Drop
idx_actions_status_updated_at
  • status
  • updated_at
 SQL 
CREATE INDEX idx_actions_status_updated_at
ON actions (status, updated_at)
Drop
sqlite_autoindex_actions_1 id ✓ SQL
-- no sql found --
 Drop
sqlite_autoindex_actions_2 idempotency_key ✓ SQL
-- no sql found --
 Drop