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
)