Supabase (Relational DB) State

Database Schema Overview

The relational database is hosted on Supabase (PostgreSQL). The schema is managed via SQL migrations.

profiles: Maps Supabase Auth users to roles.
wallet: Stores token_balance and subscription_tier.
wallet_ledger: Immutable audit log of all token transactions.
usage_logs: Logs metadata for every RAG interaction (Question, Namespace, Sources used).
references: Catalog of curriculum PDFs discovered by scrapers.
scrape_runs: History of scraper executions.
documents: Tracked PDFs and their metadata.
chunks: Text chunks from PDFs (linked to Pinecone vectors).
personas: AI Teacher persona definitions.

...001_curriculum_document_tracking.sql: Basic doc/chunk tracking.
...002_usage_logs.sql: Interaction logging.
...003_wallet_system.sql: Ledger and balances.
...004_create_scrape_runs.sql: Scraping audit.
...005_create_references.sql: Portals catalog.
...006_rls_phase_1.sql: RLS activation and retrieval query logging.
...007_profiles_trigger.sql: Auth trigger for new users.
...008_pedagogical_tables.sql: Pedagogy and personas.
...009_rls_phase_2_public_tables.sql: RLS enabled on all remaining public tables.
...010_secure_functions.sql: Security hardening for functions (search_path).
...011_indexes.sql: Performance indexes for FKs.

✅ CURRENT STATUS: PHASE 2 COMPLETE (STRICT)

As of Feb 16, 2026, RLS is enabled on ALL public tables.

Tables: documents, chunks, scrape_runs, references, personas.
Policy:
No public/anonymous access.
No authenticated user access (students cannot read these directly; they interact via the API).
Service Role / Admin: Full access via explicit policies or bypass (backend uses service_role key).

handle_new_user: SECURITY DEFINER with fixed search_path = public, auth, extensions to prevent privilege escalation attacks.