CREATE TABLE intel_memory (
story_key TEXT PRIMARY KEY,
entity_key TEXT NOT NULL,
theme_key TEXT NOT NULL,
latest_story_id TEXT,
first_seen_at TEXT,
last_seen_at TEXT,
last_alerted_at TEXT,
last_digested_at TEXT,
last_decision TEXT NOT NULL DEFAULT 'monitor',
last_alert_class TEXT NOT NULL DEFAULT 'none',
continuity_state TEXT NOT NULL DEFAULT 'new',
uncertainty_state TEXT NOT NULL DEFAULT 'open',
followup_status TEXT NOT NULL DEFAULT 'none',
followup_attempts INTEGER NOT NULL DEFAULT 0,
last_followup_at TEXT,
last_verification_note TEXT,
last_confidence REAL NOT NULL DEFAULT 0.0,
last_corroboration_score REAL NOT NULL DEFAULT 0.0,
last_importance_score REAL NOT NULL DEFAULT 0.0,
last_provenance_score REAL NOT NULL DEFAULT 0.0,
quality_delta REAL NOT NULL DEFAULT 0.0,
latest_headline TEXT,
latest_thesis TEXT,
latest_why_now TEXT,
unresolved_questions TEXT,
seen_timestamps_30d TEXT,
surfaced_timestamps_30d TEXT,
alerted_timestamps_30d TEXT,
seen_count_total INTEGER NOT NULL DEFAULT 0,
surfaced_count_total INTEGER NOT NULL DEFAULT 0,
alert_count_total INTEGER NOT NULL DEFAULT 0,
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
)