Database Setup
Run this SQL in your Supabase SQL Editor to create the tables your app needs. Run it once after creating your project.
-- Run in Supabase SQL Editor → New Query
-- 1. User profiles (extends Supabase auth.users)
CREATE TABLE IF NOT EXISTS profiles (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
email TEXT NOT NULL,
full_name TEXT,
plan TEXT NOT NULL DEFAULT 'pro',
status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active','inactive','trialing','canceled')),
stripe_customer_id TEXT,
stripe_subscription_id TEXT,
pages_analyzed INT DEFAULT 0,
tokens_used BIGINT DEFAULT 0,
cost_cents INT DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
last_active TIMESTAMPTZ DEFAULT NOW()
);
-- 2. Auto-create profile when a user signs up
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.profiles (id, email)
VALUES (NEW.id, NEW.email);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION handle_new_user();
-- 3. Usage logs (one row per analysis run)
CREATE TABLE IF NOT EXISTS usage_logs (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
tokens_in INT DEFAULT 0,
tokens_out INT DEFAULT 0,
pages INT DEFAULT 0,
cost_cents INT DEFAULT 0,
model TEXT DEFAULT 'claude-sonnet-4-6',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 4. Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage_logs ENABLE ROW LEVEL SECURITY;
-- Users can read/update their own profile
CREATE POLICY "users_own_profile_select" ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "users_own_profile_update" ON profiles FOR UPDATE USING (auth.uid() = id);
-- Users can insert + read their own usage logs
CREATE POLICY "users_own_usage_insert" ON usage_logs FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "users_own_usage_select" ON usage_logs FOR SELECT USING (auth.uid() = user_id);
-- Note: Admin dashboard uses Service Role key which bypasses RLS automatically.