Supabase Setup Guide

This guide covers setting up Supabase for the Traceo platform, including database configuration, authentication, and security settings.

Prerequisites

Supabase account (https://supabase.com)
Supabase CLI installed (npm install -g supabase)
PostgreSQL client (psql) for migration execution

Project Setup

1. Create Supabase Project

Log in to Supabase Dashboard
Click “New Project”
Configure:
- Organization: Select or create
- Name: traceo-production (or traceo-staging)
- Database Password: Generate a strong password (save this!)
- Region: Choose closest to your users
- Pricing Plan: Pro recommended for production
Wait for project provisioning (~2 minutes)

2. Get Connection Details

From Project Settings > Database:

# Direct connection (for migrations)
DATABASE_URL=postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres

# Pooled connection (for application - recommended)
DATABASE_URL=postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres

From Project Settings > API:

SUPABASE_URL=https://[PROJECT-REF].supabase.co
SUPABASE_ANON_KEY=eyJ...  # Safe to expose in frontend
SUPABASE_SERVICE_KEY=eyJ...  # NEVER expose - backend only

Database Setup

1. Run Migrations

# Connect to database
export DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[REF].supabase.co:5432/postgres"

# Run migrations in order
psql $DATABASE_URL -f migrations/001_initial_schema.sql
psql $DATABASE_URL -f migrations/002_rls_policies.sql
psql $DATABASE_URL -f migrations/003_indexes.sql

# Verify
psql $DATABASE_URL -c "\dt"

2. Using Supabase CLI (Alternative)

# Initialize Supabase in project
supabase init

# Link to remote project
supabase link --project-ref [PROJECT-REF]

# Push migrations
supabase db push

3. Verify RLS

-- Check RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public';

-- All should show 't' for rowsecurity

Authentication Setup

1. Configure Auth Providers

In Supabase Dashboard > Authentication > Providers:

Email/Password

Enable “Email” provider
Configure:
- Confirm email: Yes (production)
- Secure email change: Yes
- Custom SMTP (recommended for production)

Google OAuth

Create OAuth credentials in Google Cloud Console
Add authorized redirect URI: https://[PROJECT-REF].supabase.co/auth/v1/callback
Copy Client ID and Secret to Supabase

Microsoft OAuth

Register app in Azure Portal
Add redirect URI: https://[PROJECT-REF].supabase.co/auth/v1/callback
Copy Application ID and Secret to Supabase

2. Configure Email Templates

In Authentication > Email Templates:

Confirm signup: Customize welcome email
Reset password: Customize reset flow
Magic link: Optional passwordless auth

3. Configure URL Settings

In Authentication > URL Configuration:

Site URL: https://your-app.vercel.app
Redirect URLs:
  - https://your-app.vercel.app/auth/callback
  - http://localhost:3000/auth/callback (development)

Security Configuration

1. API Settings

In Project Settings > API:

JWT Expiry: 3600 (1 hour) recommended
Enable Row Level Security: Already enabled via migrations
Realtime: Enable only if needed

2. Database Security

-- Verify helper functions exist
SELECT routine_name 
FROM information_schema.routines 
WHERE routine_schema = 'auth' 
AND routine_name IN ('uid', 'workspace_id', 'tenant_id', 'is_tenant_owner');

3. Connection Pooling

For production, use Supavisor (Supabase’s connection pooler):

# Transaction mode (recommended for serverless)
DATABASE_URL=postgresql://postgres.[REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true

# Session mode (for long-running connections)
DATABASE_URL=postgresql://postgres.[REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:5432/postgres

Environment Configuration

Production `.env`

# Supabase
SUPABASE_URL=https://[PROJECT-REF].supabase.co
SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_KEY=eyJ...  # Backend only!
SUPABASE_JWT_SECRET=[JWT-SECRET]

# Database (use pooled connection)
DATABASE_URL=postgresql://postgres.[REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres

# Application
LOG_LEVEL=INFO
LOG_FORMAT=json

Development `.env`

# Local Supabase (optional)
SUPABASE_URL=http://localhost:54321
SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_KEY=eyJ...

# Or use remote Supabase staging project
DATABASE_URL=postgresql://postgres:[PASSWORD]@db.[REF].supabase.co:5432/postgres

Client Integration

Python (MCP Server / Engine)

from supabase import create_client, Client

url = os.environ["SUPABASE_URL"]
key = os.environ["SUPABASE_SERVICE_KEY"]  # For backend

supabase: Client = create_client(url, key)

# Query with RLS bypass (service role)
response = supabase.table("requirements").select("*").execute()

# Or use direct PostgreSQL for complex queries
import asyncpg

async def get_pool():
    return await asyncpg.create_pool(
        os.environ["DATABASE_URL"],
        min_size=5,
        max_size=20,
    )

TypeScript (Web Client)

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

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!

export const supabase = createClient(supabaseUrl, supabaseAnonKey)

// Authentication
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: `${window.location.origin}/auth/callback`
  }
})

// Query with RLS
const { data: requirements } = await supabase
  .from('requirements')
  .select('*')
  .eq('status', 'active')

Monitoring

1. Database Metrics

In Supabase Dashboard > Database > Reports:

Monitor query performance
Track connection usage
Review slow queries

2. Auth Metrics

In Supabase Dashboard > Authentication > Users:

Monitor sign-ups
Track failed logins
Review user sessions

3. Logs

# View logs via CLI
supabase logs --project-ref [REF]

# Or in Dashboard > Logs

Backup & Recovery

Automatic Backups (Pro Plan)

Point-in-time recovery: Up to 7 days
Daily backups: Retained for 7 days

Manual Backup

# Export database
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

# Restore
psql $DATABASE_URL < backup_20240115.sql

Troubleshooting

RLS Issues

-- Check if user has access
SELECT auth.uid();  -- Should return user UUID
SELECT auth.workspace_id();  -- Should return workspace UUID

-- Debug RLS policy
EXPLAIN (ANALYZE, VERBOSE) 
SELECT * FROM requirements WHERE workspace_id = '[UUID]';

Connection Issues

# Test connection
psql $DATABASE_URL -c "SELECT 1"

# Check pooler status
curl https://[REF].supabase.co/rest/v1/ -H "apikey: [ANON_KEY]"

Migration Rollback

# Rollback specific migration (manual)
psql $DATABASE_URL -f migrations/rollback/003_indexes_rollback.sql

Next Steps

Run migrations on your Supabase project
Configure authentication providers
Set up environment variables in your deployment platform
Test RLS policies with the seed data
Configure monitoring alerts

Guides

API

Operations

​Supabase Setup Guide

​Prerequisites

​Project Setup

​1. Create Supabase Project

​2. Get Connection Details

​Database Setup

​1. Run Migrations

​2. Using Supabase CLI (Alternative)

​3. Verify RLS

​Authentication Setup

​1. Configure Auth Providers

​Email/Password

​Google OAuth

​Microsoft OAuth

​2. Configure Email Templates

​3. Configure URL Settings

​Security Configuration

​1. API Settings

​2. Database Security

​3. Connection Pooling

​Environment Configuration

​Production .env

​Development .env

​Client Integration

​Python (MCP Server / Engine)

​TypeScript (Web Client)

​Monitoring

​1. Database Metrics

​2. Auth Metrics

​3. Logs

​Backup & Recovery

​Automatic Backups (Pro Plan)

​Manual Backup

​Troubleshooting

​RLS Issues

​Connection Issues

​Migration Rollback

​Next Steps