#Database Prompts

Prompts for database operations: migrations, Drizzle ORM queries, Row-Level Security, schema management, and type generation.

#Prompts

#	Prompt	Purpose
01	Create Migration	Create a new numbered SQL migration
02	Generate DB Types	Regenerate TypeScript types from Supabase schema
03	Regenerate Migrations Index	Update the auto-generated migrations catalog

#FAQ

#Schema

What are the most important tables and how do they relate?

• topic_categories -- 3-level hierarchical topic taxonomy (31 roots, 80 depth-1, 165 depth-2; 276 total).
• fact_record_schemas -- Per-topic Zod-validated key definitions stored in fact_keys JSONB column.
• fact_records -- The atomic unit: one verified fact with structured key-value data, linked to a topic category and optionally a story.
• stories / story_clusters -- Clustered news articles that facts are extracted from.
• news_sources -- Raw articles fetched from news APIs (NewsAPI, GNews, etc.).
• fact_challenge_content -- Pre-generated AI challenge text per style and difficulty.
• card_interactions -- User engagement tracking (viewed, expanded, answered, bookmarked, shared).
• user_subscriptions -- Free/Eko+ subscription status linked to plan_definitions.
• ai_cost_log -- Per-call AI spend tracking for budget enforcement (includes reasoning_tokens_total for thinking models).
• Schema definition: packages/db/src/drizzle/schema.ts.

Where is the Drizzle schema defined and how does it map to Supabase?

• Schema: packages/db/src/drizzle/schema.ts -- defines all tables using Drizzle ORM syntax (pgTable, pgEnum, relations).
• Client: packages/db/src/drizzle/client.ts -- Supabase Postgres connection via Drizzle.
• Drizzle generates TypeScript types from the schema definitions, used for type-safe queries throughout the codebase.
• Supabase manages the actual Postgres database -- Drizzle is the ORM layer on top.
• Migrations in supabase/migrations/ are raw SQL applied via Supabase CLI, not generated by Drizzle.

What is the fact_record_schemas table and how do schema keys work per topic?

• Each topic category links to a schema via topic_categories.schema_id.
• fact_record_schemas.fact_keys is a JSONB column defining typed field names (e.g., { player_name: "text", career_points: "number" }).
• During fact extraction, the AI prompt receives these keys so it structures its output to match the expected fields.
• Schema resolution utilities: packages/ai/src/schema-utils.ts.
• Schema storage: fact_record_schemas.fact_keys (JSONB column in Postgres).

#Migrations

How do I create a new database migration?

• Create a new SQL file in supabase/migrations/ with the next sequence number (e.g., 0137_my_migration.sql).
• Use the standardized header format: MIGRATION NNNN: Title, description, and bulleted changes list.
• Run bun run migrations:index to regenerate the migrations index after adding the file.
• CI runs bun run migrations:check to verify the index is not stale.

What is the migrations index and how do I keep it current?

• Generated file: supabase/migrations-index.md -- quick reference table of all migrations with phases.
• Regenerate: bun run migrations:index.
• CI check: bun run migrations:check -- fails if the index is stale.
• Migrations are auto-categorized into phases by their number range (Foundation, Features, User Features, etc.).

What is the migration header format convention?

-- ============================================================================
-- MIGRATION NNNN: Title of Migration
--
-- Brief description.
--
-- Changes:
--   - Change 1
--   - Change 2
-- ============================================================================

• The MIGRATION NNNN: line and Changes: list are parsed by the index generator for quick reference.
• See .claude/rules/migrations-index.md for the full specification.

How are migrations organized into phases?

• 0001-0010: Foundation (core tables and initial setup).
• 0011-0026: Features & Security (feature additions and security fixes).
• 0027-0043: User Features (user-facing feature tables).
• 0044-0056: vNext Migration (data consolidation).
• 0057-0060: V1 Cleanup (legacy table removal).
• 0061+: Current Development (active development).
• Phases are auto-assigned by migration number in the index generator script.

What are the recent migrations (0137-0148)?

• 0137: Fix fact record schema quality issues.
• 0138: Add schemas for nature, human-achievement, and current-events.
• 0139: Seed depth-2 subcategories for all remaining depth-1 parents.
• 0140: Merge business + finance categories.
• 0141: New root categories (Health & Medicine, Language & Linguistics, Space & Astronomy).
• 0142: Subcategory expansion part 1 (A-H).
• 0143: Subcategory expansion part 2 (M-W).
• 0144: Existing root expansion (Animals, Art, Fashion, Food & Beverage, Games).
• 0145: Existing root expansion (Geography, Geology, History, Science).
• 0146: Sports individual subcategory extensions.
• 0147: Depth-3 categories part 1 (new roots + empty roots).
• 0148: Depth-3 categories part 2 (expanded roots).

#RLS and Security

How does Row-Level Security work in Eko?

• Supabase RLS policies on key tables restrict data access based on auth.uid().
• Users can only read/write their own rows in user-scoped tables (interactions, subscriptions, etc.).
• Workers and crons use the service role key (SUPABASE_SERVICE_ROLE_KEY) which bypasses RLS entirely.
• RLS policies are defined in migration SQL files using CREATE POLICY statements.

Which tables have RLS policies and what do they enforce?

• card_interactions -- Users see only their own interactions (auth.uid() = user_id).
• user_subscriptions -- Users see only their own subscription.
• fact_records -- Public read access for validated facts; admin/service-role write.
• score_disputes, reward_milestones -- User-scoped read/write.
• Policies are defined across multiple migration files -- search CREATE POLICY in supabase/migrations/.

#ORM and Types

How do I regenerate TypeScript types from the database schema?

• Run bun run db:types -- generates TypeScript types from Supabase schema introspection.
• Output is used by packages/db/ for type-safe queries across the codebase.
• Run this after any migration that adds, removes, or modifies table columns.

Where are database queries defined and what query files exist?

• Fact engine queries: packages/db/src/drizzle/fact-engine-queries.ts.
• General queries: packages/db/src/drizzle/queries.ts.
• Seed queries: packages/db/src/drizzle/seed-queries.ts.
• Feature flags: packages/db/src/drizzle/feature-flags.ts.
• Query tests: packages/db/src/drizzle/__tests__/fact-engine-queries.test.ts.

How does the query cache work?

• Implemented in packages/db/src/cache.ts -- caches frequent read queries to reduce Supabase load.
• Uses Upstash Redis when configured; falls back to an in-memory cache if Redis is unavailable.
• Cache TTLs: 5 minutes for user plan lookups (PLAN_CACHE_TTL), 1 hour for plan definitions (PLAN_DEFINITIONS_TTL).
• Hot paths: getUserEffectivePlan (called on every authenticated request), plan definitions (rarely change).
• Cache test: packages/db/src/cache.test.ts.

#See Also

• Migrations Index -- Quick reference for all migrations
• APP-CONTROL.md -- Database env vars and operational manifest
• Schema: packages/db/src/drizzle/schema.ts
• Migrations: supabase/migrations/