Supabase Database

Set up your PostgreSQL database with Supabase for storing user data and application state.

What is Supabase?

Supabase is an open-source Firebase alternative that provides:

PostgreSQL database
Real-time subscriptions
Auto-generated APIs
Built-in authentication (we use Clerk instead)
File storage

Why Supabase?

🗄️ PostgreSQL - Industry-standard relational database
🚀 Instant APIs - Auto-generated REST and GraphQL APIs
📊 Real-time - Subscribe to database changes
🆓 Generous Free Tier - 500MB database, 1GB file storage
🔒 Row Level Security - Built-in security policies

Setup Guide

1. Create Supabase Project

Go to supabase.com and sign up
Click New Project
Fill in details:
- Name: architectgbt-production
- Database Password: (save this securely!)
- Region: Choose closest to your users

2. Get API Keys

Once your project is ready:

Go to Settings → API
Copy these values:

NEXT_PUBLIC_SUPABASE_URL=https://xxxxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_ROLE_KEY=eyJ...

⚠️ Important: Keep SUPABASE_SERVICE_ROLE_KEY secret! It has admin access.

3. Run Database Migrations

Your project includes migration files in supabase/migrations/.

Option 1: Using Supabase Dashboard

Go to SQL Editor in Supabase Dashboard
Click New Query
Execute migrations in order:
- 0001_create_users_table.sql
- 0002_create_ai_models_table.sql
- 0003_create_subscriptions_table.sql
- Continue with all numbered files

Option 2: Using Supabase CLI

# Install Supabase CLI
npm install -g supabase

# Login
supabase login

# Link your project
supabase link --project-ref your-project-ref

# Push all migrations
supabase db push

4. Verify Tables

In Supabase Dashboard:

Go to Table Editor
You should see tables:
- users
- ai_models
- subscriptions
- user_queries
- generated_projects
- announcements
- feedback

Database Schema

Users Table

Synced from Clerk webhooks:

- id (uuid)
- clerk_id (text) - unique
- email (text)
- first_name (text)
- last_name (text)
- created_at (timestamp)

AI Models Table

Stores available AI models:

- id (uuid)
- model_id (text) - e.g., "gpt-4-turbo"
- provider (text) - "openai", "anthropic", "google"
- name (text)
- cost_per_million_input (numeric)
- cost_per_million_output (numeric)
- context_length (integer)

Subscriptions Table

Track user subscription status:

- id (uuid)
- user_id (uuid) - references users
- stripe_subscription_id (text)
- status (text) - "active", "canceled", etc.
- plan (text) - "free", "pro"
- created_at (timestamp)

Using Supabase in Your App

Server-Side (Admin Access)

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // Admin access
);

// Query data
const { data, error } = await supabase
  .from('users')
  .select('*')
  .eq('clerk_id', userId);

Client-Side (Public Access)

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! // Public access
);

// Insert data
const { data, error } = await supabase
  .from('feedback')
  .insert({ message: 'Great app!' });

Real-time Subscriptions

const channel = supabase
  .channel('announcements')
  .on('postgres_changes', 
    { event: 'INSERT', schema: 'public', table: 'announcements' },
    (payload) => {
      console.log('New announcement:', payload.new);
    }
  )
  .subscribe();

Security with Row Level Security (RLS)

Enable RLS to protect your data:

-- Enable RLS on users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Users can only read their own data
CREATE POLICY "Users can read own data"
ON users FOR SELECT
USING (auth.uid() = id);

Your project includes RLS policies in the migration files.

CORS Configuration

If you get CORS errors:

Go to Settings → API
Add your domain to CORS allowed origins
Add both:
- http://localhost:3000 (development)
- https://yourdomain.com (production)

Backup & Recovery

Automatic Backups

Supabase automatically backs up your database daily (on paid plans).

Manual Export

# Using Supabase CLI
supabase db dump -f backup.sql

# Restore
supabase db reset --db-url "postgresql://..."

Monitoring

View database activity:

Go to Database → Logs
Monitor:
- Query performance
- Error rates
- Connection count

Production Checklist

[ ] All migrations executed successfully
[ ] RLS policies enabled on sensitive tables
[ ] Backup strategy configured
[ ] Production domain added to CORS
[ ] Service role key kept secure
[ ] Connection pooling configured
[ ] Monitoring alerts set up

Common Issues

Migration Failed

Check SQL syntax in migration files
Run migrations one at a time
Check error messages in SQL Editor

Connection Errors

Verify URL and keys are correct
Check CORS settings
Ensure project isn't paused (free tier)

Slow Queries

Add indexes to frequently queried columns
Use .select() to limit returned columns
Check query performance in Logs

Resources

Next: Set up Mailerlite →

Quick Links