Database Setup - Complete Guide

This guide provides a comprehensive breakdown of the LaunchKit database schema and explains every table, function, and security policy in the migration file.

Overview

The LaunchKit database is built on PostgreSQL via Supabase and includes:

• User profiles with Stripe integration
• Product and purchase management
• Lead capture and support systems
• File storage for avatars
• Row Level Security (RLS) for data protection

Step-by-Step Database Setup

1. Access Supabase SQL Editor

1. Log in to your Supabase Dashboard
2. Select your project
3. Navigate to SQL Editor in the left sidebar
4. Click “New Query”

2. Execute the Migration

Copy and paste the entire contents of /supabase/migrations/20250708202432_init_db.sql into the SQL Editor and run it.

---

Database Schema Breakdown

Profiles Table

The profiles table extends Supabase’s built-in authentication with additional user data:

CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT,
  email TEXT,
  avatar_url TEXT,
  customer_id TEXT,      -- Stripe customer ID
  price_id TEXT,         -- Stripe price ID for current subscription
  has_access BOOLEAN DEFAULT false,  -- Access to paid features
  is_admin BOOLEAN DEFAULT false,    -- Admin privileges
  created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
  last_login TIMESTAMP WITH TIME ZONE
);

Key Fields:

• customer_id: Links to Stripe customer for billing
• price_id: Tracks current subscription/product
• has_access: Controls feature access (set by Stripe webhooks)
• is_admin: Grants access to admin panels and functionality

Products Table

Manages your SaaS products, pricing, and features:

CREATE TABLE products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  price_anchor DECIMAL(10,2),        -- Optional "crossed out" price
  stripe_price_id TEXT NOT NULL UNIQUE,
  type TEXT NOT NULL CHECK (type IN ('link', 'github')),
  url TEXT,                          -- For link-type products
  github_repo TEXT,                  -- GitHub repository URL
  features JSONB,                    -- Product features as JSON array
  docs_url TEXT,                     -- Documentation URL
  is_active BOOLEAN DEFAULT true,
  is_featured BOOLEAN DEFAULT false, -- Highlight on pricing page
  billing_type TEXT NOT NULL DEFAULT 'one_time' CHECK (billing_type IN ('one_time', 'subscription')),
  billing_interval TEXT CHECK (billing_interval IN ('day', 'week', 'month', 'year')),
  billing_interval_count INTEGER DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Product Types:

• link: Provides access to a URL/service
• github: Grants access to private GitHub repositories

Billing Types:

• one_time: Single payment products
• subscription: Recurring billing with intervals

Purchases Table

Tracks all transactions and purchase records:

CREATE TABLE purchases (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  stripe_session_id TEXT,
  stripe_payment_intent_id TEXT,
  amount_paid DECIMAL(10,2) NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed')),
  github_username TEXT,              -- For GitHub repository access
  github_invited_at TIMESTAMPTZ,     -- When GitHub invitation was sent
  stripe_invoice_url TEXT,           -- Link to Stripe invoice
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, product_id)        -- Prevent duplicate purchases
);

Purchase Flow:

1. User completes Stripe checkout
2. Webhook creates purchase record with status: 'completed'
3. For GitHub products, user provides username
4. System sends GitHub repository invitation

Leads Table

Captures potential customers before they purchase:

CREATE TABLE public.leads (
  id uuid default gen_random_uuid(),
  name text,
  email text not null unique,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  primary key (id)
);

Used by the ButtonLead component to collect email addresses for marketing.

Support Requests Table

Manages customer support tickets:

CREATE TABLE support_requests (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  product_id uuid REFERENCES products(id) ON DELETE SET NULL,
  title text NOT NULL,
  message text NOT NULL,
  file_info jsonb,                   -- Attached file information
  status text DEFAULT 'open',
  created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

Database Functions

Auto-Update Timestamps

Automatically updates updated_at fields when records are modified:

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = (now() AT TIME ZONE 'UTC');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = '';

Auto-Create User Profiles

Automatically creates a profile when a user signs up:

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, name, avatar_url, created_at, updated_at, last_login)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name'),
    NEW.raw_user_meta_data->>'avatar_url',
    (now() AT TIME ZONE 'UTC'),
    (now() AT TIME ZONE 'UTC'),
    (now() AT TIME ZONE 'UTC')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = '';

Row Level Security (RLS) Policies

Profiles Table Security

Read Access

-- Enable read access for all users (including unauthenticated)
CREATE POLICY "Enable read access for all users" ON public.profiles
FOR SELECT
TO public
USING (true);

Update Access

-- Users can update their own profile OR admins can update any profile
CREATE POLICY "Enable update for users based on user_id" ON public.profiles
FOR UPDATE
TO authenticated
USING (
  auth.uid() = id
  OR
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.is_admin = true
  )
);

Insert Access

-- Users can create their own profile OR admins can create any profile
CREATE POLICY "Enable insert for authenticated users only" ON public.profiles
FOR INSERT
TO authenticated
WITH CHECK (
  auth.uid() = id
  OR
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.is_admin = true
  )
);

Products Table Security

-- Products are viewable by everyone (for public pricing page)
CREATE POLICY "Products are viewable by all users" ON products
  FOR SELECT USING (true);

-- Only service role and admins can manage products
CREATE POLICY "Service role can manage products" ON products
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY "Admin users can manage products" ON products
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.is_admin = true
    )
  );

Purchases Table Security

-- Everyone can view purchases (for admin dashboards)
CREATE POLICY "Everyone can view purchases" ON purchases
  FOR SELECT USING (true);

-- Users can only modify their own purchases
CREATE POLICY "Users can insert their own purchases" ON purchases
  FOR INSERT WITH CHECK (auth.uid() = user_id);

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

-- Service role can manage all purchases (for webhooks)
CREATE POLICY "Service role can manage purchases" ON purchases
  FOR ALL USING (auth.role() = 'service_role');

Storage Setup

Avatar Storage Bucket

-- Create the avatars storage bucket
INSERT INTO storage.buckets (id, name, public)
VALUES ('avatars', 'avatars', true);

-- Users can upload/update/delete their own avatars
CREATE POLICY "Users can upload their own avatar" ON storage.objects
FOR INSERT WITH CHECK (
  bucket_id = 'avatars' AND
  auth.uid()::text = (storage.foldername(name))[1]
);

Database Indexes

Performance optimization indexes:

-- Profile indexes
CREATE INDEX IF NOT EXISTS idx_profiles_last_login ON public.profiles(last_login);
CREATE INDEX IF NOT EXISTS idx_profiles_has_access ON public.profiles(has_access);
CREATE INDEX IF NOT EXISTS idx_profiles_is_admin ON public.profiles(is_admin);

-- Product indexes
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_products_type ON products(type);
CREATE INDEX idx_products_featured ON products(is_featured) WHERE is_featured = true;
CREATE INDEX idx_products_features ON products USING GIN (features);

-- Purchase indexes
CREATE INDEX idx_purchases_user_id ON purchases(user_id);
CREATE INDEX idx_purchases_product_id ON purchases(product_id);
CREATE INDEX idx_purchases_status ON purchases(status);

Verification Steps

After running the migration, verify your setup:

1. Check Tables Created

In Supabase Dashboard → Table Editor, you should see:

• profiles
• products
• purchases
• leads
• support_requests

2. Test RLS Policies

Create a test user and verify they can only access their own data.

3. Verify Storage Bucket

Check that the avatars bucket exists in Storage.

4. Test Triggers

Sign up a new user and confirm a profile record is automatically created.

Common Issues & Solutions

RLS Policy Conflicts

If you get permission errors:

1. Check that RLS is enabled on tables
2. Verify policy conditions match your use case
3. Use the service role key for admin operations

Missing Indexes

If queries are slow:

1. Check that all indexes were created
2. Monitor query performance in Supabase Dashboard
3. Add additional indexes for your specific queries

Trigger Failures

If user profiles aren’t created automatically:

1. Verify the trigger function was created
2. Check the trigger is attached to auth.users
3. Look for errors in Supabase logs