#Database

#Engine

PostgreSQL via Supabase with Drizzle ORM for type-safe queries.

#ORM & Query Layer

Layer	Location	Use Case
Drizzle ORM	packages/db/src/drizzle/	Type-safe queries, transactions, complex joins
Supabase Client	packages/db/src/client.ts	Auth, Storage, RLS-dependent operations
Query Cache	packages/db/src/cache.ts	Redis caching for hot paths (plan lookups)

#Drizzle Setup

# Connection string (Supavisor transaction mode)
DATABASE_URL=postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

#Type Generation

bun run db:types  # Generate types from Supabase schema

#User & Subscription Tables

Table	Description	RLS
profiles	User profiles synced from Supabase Auth	Yes
plan_definitions	Plan configuration (free, base, pro, team, plus)	Yes (read-only)
user_subscriptions	Maps users to plans with Stripe integration	Yes
notification_preferences	Per-user notification settings	Yes
system_notifications	System-generated notifications	Yes
feature_flags	Runtime feature flag toggles	No
ai_model_tier_config	AI model routing configuration per tier	No

#Fact Engine Tables (V2)

The V2 fact engine introduces a knowledge platform built on hierarchical taxonomy, structured facts, and interactive challenges.

#Taxonomy

Table	Description	RLS
topic_categories	Hierarchical taxonomy tree (depth 0-3, slug, path, dailyQuota). Supports deprecation via deprecated_at, replaced_by, deprecation_reason columns.	No
topic_category_aliases	Maps external provider slugs (e.g., NewsAPI business) to canonical topic category IDs	No
unmapped_category_log	Logs category slugs that could not be resolved — used for periodic audit and alias creation	No

#Fact Storage

Table	Description	RLS
fact_records	Core fact storage — facts JSONB with key-value pairs, title, context, notabilityScore, status, validation JSONB	Yes
fact_record_schemas	Per-topic fact shape definitions — factKeys JSONB defining typed fields, cardFormats array	No

#Challenge System

Table	Description	RLS
fact_challenge_content	Pre-generated challenge text per style/difficulty with challenge_title	Yes
challenge_formats	8 named challenge formats with knowledgeType and tone	No
challenge_format_styles	Format → eligible style mapping	No
challenge_format_topics	Format → eligible topic mapping	No
challenge_sessions	Multi-turn conversational challenge state with conversation history	Yes
card_interactions	User engagement tracking (views, answers, bookmarks, shares)	Yes
challenge_group_progress	Per-position progress within challenge groups	Yes
card_bookmarks	User bookmarked cards	Yes
score_disputes	User score dispute submissions for AI re-evaluation	Yes
reward_milestones	Achievement milestones for gamification	No
user_reward_claims	Claimed milestone rewards	Yes
user_quality_grades	Per-topic quality grade aggregation	Yes

#Ingestion & Observability

Table	Description	RLS
stories	Clustered news articles grouped by TF-IDF cosine similarity	No
news_sources	Raw articles fetched from news APIs	No
ai_cost_tracking	Per-model, per-feature daily cost aggregation	No
ingestion_runs	Pipeline observability — tracks each cron invocation with status and metrics	No
content_operations_log	Operational event log for pipeline debugging	No

#Seed Pipeline

Table	Description	RLS
seed_entry_queue	Batch seed entry processing queue	No
super_fact_links	Cross-entry correlation links	No
seed_entry_links	Entry-to-fact provenance links	No

#Brand & Domain

Table	Description	RLS
brands	Canonical brand entities	No
domains	Domain records with verification status	No
brand_categories	Brand categorization taxonomy	No
brand_category_assignments	Brand → category mappings	No

See Fact & Taxonomy Schema Map for the full schema reference including Zod schemas, AI types, and worker handlers.

#Migrations

Location: supabase/migrations/

The project has 167 migration files numbered up to 0174, spanning foundation schema (V1), vNext global URL library, V1 cleanup, and the V2 fact engine. See Migrations Index for the full listing organized by phase.

Key migration phases:

• 0001-0017: Foundation — core tables, RLS, brand library, vNext
• 0018-0056: Features & vNext — fact engine schema, challenge system, ingestion pipeline
• 0057-0060: V1 cleanup — 41 legacy tables dropped
• 0061-0174: V2 development — taxonomy expansion, challenge groups, points/grading system, evidence enrichment, seed pipeline

Conventions:

• Forward-only SQL (no rollback)
• 4-digit zero-padded numbering
• RLS enabled by default for user tables
• Use CONCURRENTLY for index creation

#Access Patterns

Role	Tables	Use Case
Anon/Authenticated	Core tables (via RLS)	User reads/writes
Service role	All tables	Workers, admin, Brand Library

#Query Caching

Hot paths are cached using Upstash Redis with in-memory fallback:

Function	TTL	Description
getCachedUserPlan()	5 min	User plan lookups (every auth request)
getCachedPlanDefinitions()	1 hour	Plan definitions (rarely change)

Invalidate cache when subscriptions change: invalidateUserPlanCache(userId)

#Backups

Supabase managed. Verify before destructive migrations.

---

Related docs:

• Drizzle ORM Guide
• Migrations Index
• Architecture Overview