Query

event_store.db — signal_candidates

SQL

CREATE TABLE signal_candidates  (
  id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN (
    'pending','claimed','done','failed'
  )),
  signal_type TEXT NOT NULL,
  -- original signal type (news_headline_delta, etc.)
  source TEXT NOT NULL,
  -- ingestor source (news_rss, markets, etc.)
  url TEXT,
  -- URL for fetching (if applicable)
  headline TEXT,
  -- headline/title for context
  source_event_id TEXT NOT NULL,
  -- backing event (source of truth for payload)
  dedupe_key TEXT NOT NULL,
  -- stable dedupe key from ingestor
  -- work leasing
  claim_id TEXT,
  claimed_by TEXT,
  claimed_at TEXT,
  lease_expires_at TEXT,
  attempt_count INTEGER NOT NULL DEFAULT 0,
  -- completion
  failed_at TEXT,
  last_error TEXT,
  output_event_id TEXT,
  -- event emitted on success (for provenance)
  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 (source_event_id) REFERENCES events(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id TEXT ✓ ✓ Rename | Drop
status TEXT Rename | Drop
signal_type TEXT Rename | Drop
source TEXT Rename | Drop
url TEXT ✓ Rename | Drop
headline TEXT ✓ Rename | Drop
source_event_id TEXT Rename | Drop
dedupe_key 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
failed_at TEXT ✓ Rename | Drop
last_error TEXT ✓ Rename | Drop
output_event_id TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
source_event_id events.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_signal_candidates_claimable
  • status
  • lease_expires_at
  • created_at
 SQL 
CREATE INDEX idx_signal_candidates_claimable
ON signal_candidates (status, lease_expires_at, created_at)
Drop
idx_signal_candidates_dedupe dedupe_key ✓ SQL 
CREATE UNIQUE INDEX idx_signal_candidates_dedupe
ON signal_candidates (dedupe_key)
Drop
idx_signal_candidates_status_created
  • status
  • created_at
 SQL 
CREATE INDEX idx_signal_candidates_status_created
ON signal_candidates (status, created_at)
Drop
sqlite_autoindex_signal_candidates_1 id ✓ SQL
-- no sql found --
 Drop