Database Architecture

Kinetic Email uses Supabase (PostgreSQL) as its primary database, with Convex for real-time tracking data.

Supabase Tables

User Management

Table	Purpose
auth.users	Supabase managed auth table
user_profiles	Display name, preferences, marketing opt-in
admin_users	Admin email whitelist

Token Economy

Table	Purpose
token_balances	Per-user balance (balance, lifetime_earned, lifetime_spent)
token_transactions	Full transaction log (credits, debits, type, reference)
token_action_costs	Cost per action (ai_generation, email_send)
token_config	System config (course_completion_bonus amount)
referral_codes	User referral codes and stats

Learning

Table	Purpose
learning_progress	Module-level completion tracking (user_id, module_id, completed, timestamps)

Blog

Blog content lives in code (src/content/blog/) — the old blog_posts table was dropped (migration 95). Only blog_reads (per-user read history) remains in the database.

Workspace

Table	Purpose
workspace_emails	Saved AI-generated emails (incl. style_theory)
workspace_projects	Project groupings
workspace_brands	Brand configurations (design tokens, style_theory)
workspace_products	Per-brand product catalogs (injected into generation)

Service-Role Only (no client access)

These tables have RLS enabled with no client policies — only serverless functions using the service role key can read or write them:

Table	Purpose
rag_documents	Source-of-truth RAG example HTML + metadata (Pinecone holds only the vector)
eval_runs / eval_results	Eval harness storage
email_suppressions	Bounce/complaint suppression list
email_delivery_events	Mailgun delivery event log (Station dashboard)

Row-Level Security (RLS)

All user-facing tables have RLS enabled. The standard policy pattern:

-- Users can only access their own data
CREATE POLICY "Users can view own data" ON table_name
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own data" ON table_name
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own data" ON table_name
  FOR UPDATE USING (auth.uid() = user_id);

Admin overrides use the service role key (server-side only) to bypass RLS when needed.

Shared resources (brand sharing, project sharing) route policy checks through SECURITY DEFINER helper functions such as is_brand_member() and get_user_active_project_ids(). Never subquery a table inside its own RLS policy USING clause — it causes a 42P17 infinite-recursion error (a brand-sharing migration once took the site down this way; migration 87 is the fix pattern).

RPC Functions

The platform uses PostgreSQL SECURITY DEFINER functions for sensitive operations like token management, referral processing, and course completion awards. These functions use auth.uid() internally to scope operations to the calling user and run with elevated privileges to modify tables that users can't directly write to.

All RPC functions are called through the Supabase client SDK — they are not directly accessible via REST.

Migrations

SQL migrations are in the database/ directory, numbered sequentially (01 → 102) and run manually in the Supabase SQL editor:

database/01-auth-schema.sql        → Auth tables
database/02-main-schema.sql        → Core table creation
...
database/59-token-system.sql       → Token economy (balances, costs, RPCs)
...
database/97-eval-harness.sql       → Eval runs/results
database/99-rag-documents.sql      → RAG source-of-truth HTML
database/100-style-theory.sql      → workspace_emails.style_theory
database/101-workspace-products.sql → Per-brand product catalogs
database/102-brand-style-theory.sql → workspace_brands.style_theory

Apply migrations in order to a fresh Supabase project to set up the full schema. Migrations that add columns read by deployed client queries must run before the deploy — a select() naming a missing column returns a 400.

Convex (Real-Time Tracking)

Convex handles real-time event tracking for kinetic emails. Tracking events flow from email opens and interactions through the tracking pixel endpoint into Convex, where they're available for real-time analytics on the Station dashboard.