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

Table	Purpose
blog_posts	Blog content (title, slug, body, author, published status)

Workspace

Table	Purpose
workspace_emails	Saved AI-generated emails
workspace_projects	Project groupings
workspace_brands	Brand configurations

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.

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:

database/01-schema.sql       → Core table creation
database/02-auth.sql         → Auth configuration
...
database/59-token-system.sql → Token economy (balances, costs, RPCs)
database/60-security.sql     → Security hardening

Apply migrations in order to a fresh Supabase project to set up the full schema.

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.