Fact Challenge System — Table Flow Diagram
Visual table flow diagram showing how data moves through the Eko fact challenge system, from news ingestion to the user-facing feed.
Sources: packages/db/src/drizzle/schema.ts, worker handlers, queue message types, migration files.
╔══════════════════════════════════════════════════════════════════════════════════╗
║ EKO FACT CHALLENGE SYSTEM ║
║ Table & Data Flow Diagram ║
╚══════════════════════════════════════════════════════════════════════════════════╝
┌─────────────────────────────────────────────────────────────────────────────┐
│ PHASE 1: INGESTION (worker-ingest) │
└─────────────────────────────────────────────────────────────────────────────┘
External News APIs Seed Files / Manual Import
(NewsAPI, Google News, (ESPN, Wikiquote, GeoNames,
GNews, TheNewsAPI) TMDB, file_parse)
│ │
▼ │
┌──────────────┐ content_hash │
│ news_sources │◄── deduplication │
│──────────────│ │
│ id │ │
│ provider │ (newsapi|google_news| │
│ external_id │ bing_news|gnews| │
│ │ thenewsapi|manual) │
│ source_name │ │
│ source_domain│ │
│ title │ │
│ description │ │
│ article_url │ │
│ image_url │ │
│ published_at │ │
│ story_id ────│──┐ │
│ content_hash │ │ │
└──────────────┘ │ │
│ CLUSTER_STORIES │
│ (TF-IDF cosine │
│ similarity) │
▼ │
┌────────────┐ │
│ stories │ │
│────────────│ │
│ id │ │
│ slug │ │
│ headline │ │
│ summary │ │
│ source_ │ │
│ count │ │
│ source_ │ │
│ domains[]│ │
│ status ────│── clustering → │
│ │ published → │
│ │ archived | │
│ │ suppressed │
└─────┬──────┘ │
│ │
│ EXTRACT_FACTS │ IMPORT_FACTS /
│ (AI extraction) │ GENERATE_EVERGREEN /
│ │ EXPLODE_CATEGORY_ENTRY
│ │
▼ ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│ PHASE 2: FACT EXTRACTION (worker-facts) │
└─────────────────────────────────────────────────────────────────────────────┘
┌────────────────────┐ ┌─────────────────────┐
│ topic_categories │◄────────│ fact_record_schemas │
│────────────────────│ 1 M │─────────────────────│
│ id │ │ id │
│ slug │ │ topic_category_id ───│──► topic_categories
│ name │ │ schema_name │
│ parent_id ─────────│──┐ │ fact_keys (JSONB) │ ◄── defines required
│ depth │ │ │ card_formats[] │ fields per topic
│ path │ │ └──────────┬──────────┘
│ icon │ │ │
│ color │ │ self-ref │ schema validation
│ daily_quota │ └──(parent) │
│ percent_target │ │
│ is_active │ ▼
└────────┬───────────┘
│
│ ┌─────────────────────────────┐
│ │ topic_category_aliases │ ◄── Provider slug → internal topic
│ │─────────────────────────────│
│ │ id │
│ │ external_slug ───────────────│── e.g. "business", "entertainment"
│ │ provider ───────────────────│── newsapi | gnews | thenewsapi | NULL
│ │ topic_category_id ──────────│──► topic_categories
│ └─────────────────────────────┘
│ UNIQUE(external_slug, provider)
│
│ ┌─────────────────────────────┐
│ │ unmapped_category_log │ ◄── Audit: unresolved provider slugs
│ │─────────────────────────────│
│ │ id │
│ │ external_slug ───────────────│── slug that couldn't be resolved
│ │ provider ───────────────────│── which provider sent it
│ │ story_id ──────────────────│──► stories (nullable)
│ │ logged_at │
│ └─────────────────────────────┘
│
│ ┌──────────────────────────────┐
│ 1 M │ fact_records │ ◄── THE CORE TABLE
└────────────────►│──────────────────────────────│
│ id │
│ topic_category_id ────────────│──► topic_categories
│ schema_id ────────────────────│──► fact_record_schemas
│ facts (JSONB) ────────────────│── structured data
│ title │ conforming to schema
│ challenge_title │
│ notability_score (0-1) │
│ notability_reason │
│ context │
│ image_url │
│ ┌─────────────────────────┐ │
│ │ STATUS STATE MACHINE: │ │
│ │ │ │
│ │ pending_validation ─────│─► VALIDATE_FACT queue
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ validated rejected │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ archived │ │
│ └─────────────────────────┘ │
│ validation (JSONB) ───────────│── {strategy, confidence,
│ source_type ──────────────────│── flags, reasoning}
│ (news_extraction | │
│ api_import | │
│ ai_generated | │
│ ai_super_fact | manual) │
│ source_story_id ──────────────│──► stories (nullable)
│ ai_model │
│ generation_cost_usd │
│ published_at ─────────────────│── SET only when validated
│ expires_at ───────────────────│── 30d for news, null for
│ │ evergreen
└──────────────┬───────────────┘
│
┌─────────────────┼──────────────────────┐
│ │ │
▼ ▼ ▼
VALIDATE_FACT GENERATE_CHALLENGE RESOLVE_IMAGE
(auto-enqueued) _CONTENT (auto-enqueued
(auto-enqueued) after validation)
┌─────────────────────────────────────────────────────────────────────────────┐
│ PHASE 3: VALIDATION (worker-validate) │
└─────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────┐
│ 4-PHASE VALIDATION PIPELINE │
│─────────────────────────────────────│
│ │
│ Phase 1: structural ── schema/ │
│ types, injection detection ($0) │
│ │
│ Phase 2: consistency ── internal │
│ contradictions, taxonomy ($0) │
│ │
│ Phase 3: cross-model ── AI │
│ adversarial via gemini-2.5-flash │
│ (~$0.001) │
│ │
│ Phase 4: evidence ── APIs │
│ (Wikipedia, Wikidata) + AI │
│ reasoner via gemini-2.5-flash │
│ (~$0.002-0.005) │
│ │
│ ───────────────────────────────── │
│ PASS: confidence >= 0.7 │
│ AND no "critical" flags │
│ → status = 'validated' │
│ → published_at = NOW() │
│ │
│ FAIL: below threshold │
│ → status = 'rejected' │
│ │
│ ModelAdapter: per-model prompt │
│ customization for AI extraction │
│ (see model-code-isolation.md) │
└─────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────┐
│ PHASE 4: CHALLENGE CONTENT GENERATION (worker-facts) │
└─────────────────────────────────────────────────────────────────────────────┘
fact_records (validated)
│
│ GENERATE_CHALLENGE_CONTENT queue
│ AI generates 3-5 styles per fact
▼
┌───────────────────────────────────┐
│ fact_challenge_content │ ◄── PRE-GENERATED challenge variants
│───────────────────────────────────│
│ id │
│ fact_record_id ───────────────────│──► fact_records (CASCADE delete)
│ challenge_style ──────────────────│── multiple_choice | fill_the_gap |
│ │ direct_question | statement_blank |
│ │ reverse_lookup | free_text
│ setup_text ───────────────────────│── Layer 1: Context (2+ sentences)
│ challenge_text ───────────────────│── Layer 2: The challenge (must contain
│ │ "you"/"your")
│ reveal_correct ───────────────────│── Layer 3a: Celebration + teaching
│ reveal_wrong ─────────────────────│── Layer 3b: Education + correct answer
│ correct_answer ───────────────────│── 3-6 sentence narrative payoff
│ style_data (JSONB) ───────────────│── Style-specific metadata:
│ │ MC: {options[], is_correct}
│ │ FTG: {complete_text, answer}
│ │ DQ: {expected_answer, answer_type}
│ │ RL: {key_identifiers[], category}
│ target_fact_key │
│ difficulty (1-5) │
│ ai_model │
│ generation_cost_usd │
└───────────────────────────────────┘
│
│ UNIQUE: (fact_record_id, challenge_style,
│ target_fact_key, difficulty)
│
│ Quality rules enforced:
│ CQ-001: setup_text >= 50 chars, 2-4 sentences
│ CQ-002: challenge_text must contain "you"/"your"
│ CQ-003: reveal_correct 1-3 sentences, 30+ chars
│ CQ-005: multiple_choice = exactly 4 options, 1 correct
│ CQ-006: No "Trivia", "Quiz", "Correct!", "Incorrect!"
│ CQ-008: correct_answer 3-6 sentences, 100+ chars
│
│ CC-001: Every published fact must have >= 3 styles
│ CC-003: conversational & progressive_image_reveal
│ are EXEMPT (runtime only)
▼
┌─────────────────────────────────────────────────────────────────────────────┐
│ PHASE 5: CHALLENGE FORMATS & SESSIONS │
└─────────────────────────────────────────────────────────────────────────────┘
┌────────────────────────┐ ┌──────────────────────────┐
│ challenge_formats │ │ challenge_format_styles │
│────────────────────────│ │──────────────────────────│
│ id │◄───│ format_id │
│ slug ──────────────────│── │ style (challenge_style) │
│ big_fan_of │ │ is_default │
│ know_a_lot_about │ └──────────────────────────┘
│ repeat_after_me │
│ good_with_dates │ ┌──────────────────────────┐
│ degrees_of_separation │ │ challenge_format_topics │
│ used_to_work_there │ │──────────────────────────│
│ partial_pictures │◄───│ format_id │
│ originators │ │ topic_category_id ────────│──► topic_categories
│ display_name │ └──────────────────────────┘
│ knowledge_type │
│ tone │ ┌──────────────────────────┐
│ supports_conversational│ │ challenge_sessions │
│ is_long_form │ │──────────────────────────│
│ is_active │◄───│ challenge_format_id │
└────────────────────────┘ │ user_id ─────────────────│──► profiles
│ topic_category_id ───────│──► topic_categories
│ status (active|completed │
│ |abandoned|timed_out) │
│ turn_count / max_turns │
│ conversation_history │
│ (JSONB) │
│ cumulative_score │
│ ai_model │
│ estimated_cost_usd │
└──────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────┐
│ PHASE 6: USER INTERACTION & FEED │
└─────────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────┐
│ GET /api/feed │
│──────────────────────────────────────│
│ Blending algorithm (authenticated): │
│ 40% Recent published facts │
│ 30% Review-due (spaced repetition) │
│ 20% Evergreen facts │
│ 10% Random exploration │
│ │
│ Unauthenticated: chronological only │
└──────────────┬───────────────────────┘
│
▼
┌──────────────────────────────────────┐
│ card_interactions │ ◄── User engagement tracking
│──────────────────────────────────────│
│ id │
│ user_id ─────────────────────────────│──► profiles
│ fact_record_id ──────────────────────│──► fact_records
│ interaction_type ────────────────────│── viewed | expanded | answered |
│ │ bookmarked | shared
│ challenge_format_id ─────────────────│──► challenge_formats (nullable)
│ challenge_style │
│ card_format (legacy) │
│ difficulty (easy|medium|hard) │
│ hidden_fact_key │
│ selected_answer │
│ correct_answer │
│ score (0-1) │
│ time_to_answer_ms │
│ scoring_method (auto|self_graded) │
│ ┌────────────────────────────────┐ │
│ │ SPACED REPETITION: │ │
│ │ next_review_at ───────────────│───│── when to show again
│ │ review_count │ │
│ │ streak ───────────────────────│───│── correct streak
│ │ │ │
│ │ User Status Derivation: │ │
│ │ streak >= 5 → 'mastered' │ │
│ │ next_review_at past → 'due' │ │
│ │ answered >= 1 → 'attempted' │ │
│ │ else → 'new' │ │
│ └────────────────────────────────┘ │
└──────────────────────┬───────────────┘
│
┌─────────────┼─────────────┐
▼ ▼
┌──────────────────┐ ┌───────────────────────┐
│ card_bookmarks │ │ score_disputes │
│──────────────────│ │───────────────────────│
│ id │ │ id │
│ user_id ─────────│► │ user_id ──────────────│──► profiles
│ fact_record_id ──│► │ card_interaction_id ──│──► card_interactions
│ note │ │ original_score │
│ created_at │ │ user_argument │
└──────────────────┘ │ ai_reasoning │
UNIQUE(user_id, │ decision ─────────────│── pending | upheld |
fact_record_id) │ partial_adjustment │ full_reversal
│ adjusted_score │
└───────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────┐
│ SUPPLEMENTARY: SEED PIPELINE & SUPER-FACTS │
└─────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────┐
│ seed_entry_queue │ ◄── Bulk fact generation from seed files
│─────────────────────────│
│ id │
│ name │
│ topic_category_id ──────│──► topic_categories
│ richness_tier │── high | medium | low
│ source_type ────────────│── file_parse | spinoff_discovery | manual
│ parent_entry_id ────────│──┐ self-referential
│ status ─────────────────│ │ pending → processing → completed | failed
│ facts_generated │ │
│ spinoffs_discovered │ │
└────────────┬────────────┘ │
│ │
│ EXPLODE_CATEGORY_ENTRY
│ + FIND_SUPER_FACTS
▼
┌─────────────────────────┐
│ super_fact_links │ ◄── Cross-entity relationships
│─────────────────────────│ (for "Degrees of Separation" challenges)
│ id │
│ fact_record_id ─────────│──► fact_records
│ linked_entry_name │
│ linked_topic_category_id│──► topic_categories
│ connection_type ────────│── shared_event | rivalry | collaboration |
│ │ temporal | geographic | causal
│ relationship_description│
└─────────────────────────┘
═══════════════════════════════════════════════════════════════════════════════
QUEUE MESSAGE FLOW (Upstash Redis)
═══════════════════════════════════════════════════════════════════════════════
INGEST_NEWS ──► news_sources
│
CLUSTER_STORIES ──► stories
│
EXTRACT_FACTS ─────►│
IMPORT_FACTS ──────►│──► fact_records (status: pending_validation)
GENERATE_EVERGREEN ►│ │
EXPLODE_CATEGORY ──►│ ├──► VALIDATE_FACT ──► fact_records.status
│ = validated | rejected
│ │
├──► RESOLVE_IMAGE ──► fact_records.image_url
│
└──► GENERATE_CHALLENGE_CONTENT
│
▼
fact_challenge_content
(3-5 styles per fact)
═══════════════════════════════════════════════════════════════════════════════
TABLE COUNT SUMMARY
═══════════════════════════════════════════════════════════════════════════════
Ingestion: news_sources, stories, ingestion_runs
Taxonomy: topic_categories, topic_category_aliases,
unmapped_category_log, fact_record_schemas
Core Facts: fact_records, fact_challenge_content
Challenges: challenge_formats, challenge_format_styles,
challenge_format_topics, challenge_sessions
User Engagement: card_interactions, card_bookmarks, score_disputes
Seed Pipeline: seed_entry_queue, super_fact_links
TOTAL: 17 tables + profiles (auth)
V1 Legacy Tables NOT Used by V2 Fact Engine
Already Dropped (41 tables across 5 migration phases)
═══════════════════════════════════════════════════════════════════════════════
DROPPED V1 TABLES — No longer in the database
═══════════════════════════════════════════════════════════════════════════════
┌─────────────────────────────────────────────────────────────────────────┐
│ PHASE 6 CLEANUP (Migration 0060) — Core V1 Tracking │
│ Replaced by: v2 pages/user_pages system (itself later dropped) │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────┐
│ tracked_urls [DROP]│ Core v1 URL tracking
│ url_checks [DROP]│ v1 URL observation snapshots
│ url_changes [DROP]│ v1 URL change events
│ summaries [DROP]│ v1 AI change summaries
│ notification_ [DROP]│ v1 notification log
│ deliveries │
└──────────────────────────┘
Backed up to v1_backup schema (migration 0058, dropped 0118)
┌─────────────────────────────────────────────────────────────────────────┐
│ PHASE 7.1 (Migration 0111) — Page Organization & Rendering │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────┐
│ user_folders [DROP] │ User folder hierarchy
│ user_folder_pages [DROP] │ Folders ↔ URLs junction
│ page_read_states [DROP] │ Read/unread tracking
│ page_diff_settings [DROP] │ Inline diff preferences
│ section_content_snapshots [DROP] │ Page section HTML cache
│ saved_items [DROP] │ Save-for-later feature
│ url_renders [DROP] │ URL rendering cache
│ video_renders [DROP] │ Video rendering cache
└──────────────────────────────────┘
Enums dropped: folder_type, capture_type
┌─────────────────────────────────────────────────────────────────────────┐
│ PHASE 7.2 (Migration 0112) — Brand Admin Workflows │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────┐
│ brand_category_proposals [DROP] │ Brand taxonomy proposals
│ brand_review_queue [DROP] │ Brand review admin queue
│ brand_sources [DROP] │ Brand data source tracking
│ seeding_pages [DROP] │ Brand-seeded pages directory
└──────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ PHASE 7.3 (Migration 0113) — Removed Features │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────┐
│ correlation_groups [DROP] │ Premium correlation feature
│ correlation_group_members [DROP] │ Correlation junction table
│ app_content [DROP] │ CMS content pages
└──────────────────────────────────┘
Enum dropped: content_category
┌─────────────────────────────────────────────────────────────────────────┐
│ PHASE 8 (Migration 0118) — V1 Final Cleanup (largest drop) │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────┐
│ pages [DROP] │ Core v1 page tracking (ex-urls)
│ page_observations [DROP] │ v1 page snapshots
│ page_change_events [DROP] │ v1 page change log
│ page_change_summaries [DROP] │ v1 AI summaries of changes
│ user_pages [DROP] │ v1 user page library
│ section_dom_mappings [DROP] │ Page section DOM structure
│ page_title_history [DROP] │ Page title change history
│ screenshot_captures [DROP] │ Page screenshot storage
│ sms_notification_pages [DROP] │ SMS notification junction
│ sms_delivery_log [DROP] │ SMS delivery tracking
│ notification_delivery_log [DROP] │ Email/push notification log
│ personas [DROP] │ User persona taxonomy
│ use_cases [DROP] │ Use case taxonomy
│ onboarding_state [DROP] │ Onboarding progress tracking
│ screen_avatars [DROP] │ Domain avatars/icons
│ type_metadata [DROP] │ Page type metadata cache
└──────────────────────────────────┘
Enums dropped: avatar_source, use_case_audience, onboarding_step
Functions dropped: get_sms_page_count(), can_add_sms_page()
Schema dropped: v1_backup
┌─────────────────────────────────────────────────────────────────────────┐
│ EARLIER CONSOLIDATIONS (Migrations 0051-0053) │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────┐
│ favorite_folders [DROP] │ Consolidated into user_folders
│ user_favorites [DROP] │ Consolidated (migration 0051)
│ favorite_folder_id_mapping[DROP] │ Consolidated (migration 0051)
│ page_type_metadata [DROP] │ Consolidated into type_metadata
│ brand_type_metadata [DROP] │ Consolidated (migration 0052)
└──────────────────────────────────┘
Still-Active Support Tables (NOT part of the fact pipeline, but used by v2)
═══════════════════════════════════════════════════════════════════════════════
ACTIVE NON-FACT-ENGINE TABLES — Supporting infrastructure
═══════════════════════════════════════════════════════════════════════════════
User & Subscription:
profiles ── User accounts (Supabase Auth)
user_subscriptions ── Billing/plan mappings
plan_definitions ── Billing plan configuration
notification_preferences ── User notification settings
system_notifications ── Account-level notifications
Brand Library (supports fact attribution):
brands ── Brand database
domains ── URL domain grouping
brand_categories ── Hierarchical brand taxonomy
brand_category_assignments── Brand-to-category mappings
Platform Config:
feature_flags ── Feature flag configuration
ai_model_tier_config ── AI model routing
Gamification:
reward_milestones ── Point threshold definitions
user_reward_claims ── User milestone claims
Observability:
ingestion_runs ── Pipeline run tracking
ai_cost_tracking ── AI cost budgeting
Summary
┌─────────────────────────┬───────┬─────────────────────────────────────┐
│ Category │ Count │ Examples │
├─────────────────────────┼───────┼─────────────────────────────────────┤
│ Active v2 Fact Engine │ 20 │ fact_records, stories, challenges │
│ Active v2 Support │ 14 │ profiles, brands, subscriptions │
│ Dropped — Phase 6 │ 5 │ tracked_urls, url_checks │
│ Dropped — Phase 7.1 │ 8 │ user_folders, saved_items │
│ Dropped — Phase 7.2 │ 4 │ brand_review_queue, seeding_pages │
│ Dropped — Phase 7.3 │ 3 │ correlation_groups, app_content │
│ Dropped — Phase 8 │ 16 │ pages, personas, use_cases │
│ Dropped — Early (0051+) │ 5 │ favorite_folders, user_favorites │
├─────────────────────────┼───────┼─────────────────────────────────────┤
│ TOTAL DROPPED │ 41 │ All v1 functionality removed │
│ TOTAL ACTIVE │ 34 │ 20 fact engine + 14 support │
└─────────────────────────┴───────┴─────────────────────────────────────┘
Glossary: All Active Eko V2 Tables
Fact Engine Core (20 tables)
| Table | Purpose | Key Relationships |
|---|---|---|
| news_sources | Individual news articles fetched from external providers (NewsAPI, Google News, etc.). Raw input to the pipeline. | FK → stories (clustered parent) |
| stories | Clustered news narratives. Multiple news_sources get grouped into one story via TF-IDF cosine similarity. | Has many news_sources, has many fact_records |
| topic_categories | Hierarchical taxonomy tree for organizing facts (e.g., science/physics/quantum). Controls daily quotas and feed balance. | Self-referential parent_id. Has many fact_record_schemas, fact_records |
| topic_category_aliases | Maps external news API slugs (e.g., "business", "entertainment") to internal topic_categories. Supports provider-specific and universal (NULL provider) mappings for flexible category resolution. | FK → topic_categories. UNIQUE(external_slug, provider) |
| unmapped_category_log | Audit log for external provider slugs that could not be resolved to any topic_category during ingestion. Enables monitoring alias coverage gaps. | FK → stories (nullable) |
| fact_record_schemas | Defines the required shape of facts within a topic — which JSONB keys are expected (e.g., question, answer, year). | FK → topic_categories. Has many fact_records |
| fact_records | The atomic unit of the entire system. A validated, schema-conformant fact with structured JSONB data, notability score, and status state machine (pending_validation → validated/rejected → archived). | FK → topic_categories, fact_record_schemas, stories. Has many fact_challenge_content, card_interactions, card_bookmarks |
| fact_challenge_content | Pre-generated AI challenge variants for a fact. Each row is one (style × difficulty × target_key) combination. Stores the 4-layer structure: setup, challenge, reveal_correct, reveal_wrong + correct_answer. | FK → fact_records (CASCADE delete) |
| challenge_formats | Named challenge experiences (e.g., "Big Fan Of", "Know A Lot About"). Defines knowledge type, tone, and whether conversational mode is supported. | Has many challenge_format_styles, challenge_format_topics, challenge_sessions |
| challenge_format_styles | Junction table mapping which presentation styles (multiple_choice, fill_the_gap, etc.) each challenge format supports. | FK → challenge_formats. Composite PK: (format_id, style) |
| challenge_format_topics | Junction table mapping which topic categories are eligible for each challenge format. | FK → challenge_formats, topic_categories. Composite PK |
| challenge_sessions | Multi-turn conversational challenge sessions. Tracks turn count, conversation history (JSONB), cumulative scoring, and AI cost. | FK → profiles, challenge_formats, topic_categories |
| card_interactions | Every user interaction with a fact card — views, expansions, answers, bookmarks, shares. Powers spaced repetition via next_review_at, streak, review_count. | FK → profiles, fact_records, challenge_formats |
| card_bookmarks | Facts saved by users for later review. One bookmark per user per fact. | FK → profiles, fact_records. UNIQUE(user_id, fact_record_id) |
| score_disputes | User appeals of AI-graded answer scores. An AI "judge" re-evaluates and can uphold, partially adjust, or fully reverse. | FK → profiles, card_interactions |
| seed_entry_queue | Queue for the file-to-fact bulk seeding pipeline. Entries (people, events, etc.) get "exploded" into structured facts via AI. Self-referential for spinoff discovery. | FK → topic_categories. Self-ref parent_entry_id |
| super_fact_links | Cross-entity relationships between facts (shared events, rivalries, collaborations). Powers the "Degrees of Separation" challenge format. | FK → fact_records, topic_categories |
| ingestion_runs | Observability table tracking every pipeline run (ingest, extract, import, etc.) with status, timing, and metadata JSONB. | Standalone (no FKs to core tables) |
| ai_cost_tracking | Daily aggregate cost tracking by AI provider, model, and feature. Enforces the "cost-bounded AI" invariant with budget caps. | Standalone |
| profiles | User accounts synced from Supabase Auth. Central identity table referenced by all user-scoped tables. | Has many card_interactions, card_bookmarks, score_disputes, challenge_sessions |
Support Infrastructure (14 tables)
| Table | Purpose | Key Relationships |
|---|---|---|
| user_subscriptions | Maps users to billing plans (Free, Eko+). Tracks trial status, Stripe customer/subscription IDs, and billing period. | FK → profiles, plan_definitions |
| plan_definitions | Configuration for each billing plan — price, trial duration, entitlements (e.g., max daily challenges, detail access). | Has many user_subscriptions |
| notification_preferences | Per-user notification settings (email digest frequency, push notification toggles, etc.). | FK → profiles |
| system_notifications | Account-level system notifications (billing warnings, trial expiry, feature announcements). Not user-generated. | FK → profiles |
| brands | Brand database for attribution and linking. Used to associate facts with known entities (companies, publications, etc.). | Has many domains, brand_category_assignments |
| domains | URL domain grouping. Maps domains to brands for automatic source attribution during ingestion. | FK → brands |
| brand_categories | Hierarchical brand taxonomy (e.g., Technology > Social Media). Separate from topic_categories. | Self-referential. Has many brand_category_assignments |
| brand_category_assignments | Junction table linking brands to their categories. A brand can belong to multiple categories. | FK → brands, brand_categories |
| feature_flags | Feature flag configuration for gradual rollouts, A/B testing, and kill switches. | Standalone |
| ai_model_tier_config | AI model routing configuration. Maps task types (extraction, validation, challenge generation) to specific model IDs with fallback chains. | Standalone |
| reward_milestones | Point threshold definitions for the gamification system (e.g., "Bronze Learner" at 100 points, "Silver Expert" at 500). | Has many user_reward_claims |
| user_reward_claims | Records when users reach and claim reward milestones. Prevents double-claiming. | FK → profiles, reward_milestones |