Supabase Setup Guide

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

​

Table of Contents

1. Overview
2. Project Setup
3. Database Configuration
4. Connection Pooling
5. Edge Functions Deployment
6. Storage Buckets
7. Authentication Configuration
8. Realtime Subscriptions
9. Backup and Recovery
10. Performance Tuning
11. Security Best Practices
12. Environment-Specific Setup
13. Branching Setup
14. Troubleshooting

​

Overview

• PostgreSQL Database - Multi-tenant data storage with RLS
• Authentication - User management and JWT tokens
• Storage - File storage for documents, attachments, avatars
• Edge Functions - Serverless functions for complex processing
• Realtime - Live data subscriptions
• API - Auto-generated REST API

​

Project Setup

​

1. Create Supabase Project

1. Go to Supabase Dashboard
2. Click New Project
3. Fill in:
   • Name: encoreos-production (or encoreos-staging, encoreos-dev)
   • Database Password: Generate strong password (save securely)
   • Region: Choose closest to users
   • Pricing Plan: Select appropriate plan

​

2. Get Project Credentials

• Project URL: https://{project-ref}.supabase.co
• anon public key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
• service_role key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9... ⚠️ Keep secret!

​

3. Configure Environment Variables

VITE_SUPABASE_URL=https://{project-ref}.supabase.co
VITE_SUPABASE_PUBLISHABLE_KEY={anon-key}

​

Database Configuration

​

1. Run Migrations

# Start Supabase locally
supabase start

# Run migrations
supabase db reset  # Resets and runs all migrations
# or
supabase migration up  # Runs pending migrations

# Link to production project
supabase link --project-ref {project-ref}

# Run migrations
supabase db push

​

2. Verify Database Setup

-- Verify core tables exist
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_name LIKE 'pf_%' 
ORDER BY table_name;

-- Verify RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' 
  AND tablename LIKE 'pf_%';

-- List SECURITY DEFINER functions
SELECT routine_name 
FROM information_schema.routines 
WHERE routine_schema = 'public' 
  AND security_type = 'DEFINER';

​

3. Seed Data (Development Only)

# Run seed scripts (if available)
supabase db seed

​

Connection Pooling

​

Why Connection Pooling?

​

Supabase Connection Pooler

postgresql://postgres:{password}@{host}:5432/postgres

postgresql://postgres.{project-ref}:{password}@{host}:6543/postgres?pgbouncer=true

• 5432 - Direct connection (no pooling)
• 6543 - Pooled connection (pgbouncer)

​

Configuration

// Use direct connection (service role key)
const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);

// Use anon key (RLS enforced)
const supabase = createClient(
  import.meta.env.VITE_SUPABASE_URL,
  import.meta.env.VITE_SUPABASE_PUBLISHABLE_KEY
);

• Transaction Mode: Default (recommended)
• Max Connections: 200 (Supabase default)
• Idle Timeout: 10 minutes

​

Edge Functions Deployment

​

1. Deploy All Functions

# Deploy all functions
supabase functions deploy

# Deploy specific function
supabase functions deploy {function-name}

​

2. Set Function Secrets

# Set secrets for edge functions
# Email provider — use ONE of the following (mutually exclusive):
supabase secrets set ENTRA_CLIENT_SECRET=xxxxx          # Microsoft/Entra
# OR
supabase secrets set GMAIL_SERVICE_ACCOUNT_JSON='{"type":"service_account",...}'  # Google Workspace

# SMS/Voice provider
supabase secrets set TWILIO_ACCOUNT_SID=ACxxxxx
supabase secrets set TWILIO_AUTH_TOKEN=xxxxx
supabase secrets set TWILIO_PHONE_NUMBER=+1234567890

# Platform
supabase secrets set LOVABLE_API_KEY=xxxxx

supabase secrets list

​

3. Configure JWT Verification

[functions.{function-name}]
verify_jwt = true   # Require valid JWT
# or
verify_jwt = false  # No JWT required (scheduled tasks, webhooks)

• execute-report
• process-entity-mapping
• ai-assistant
• workflow-debug-control
• sandbox-execute
• test-* functions
• ai-document-analyze
• workflow-metrics-aggregate
• workflow-path-aggregate
• workflow-version-compare

• Scheduled tasks (cron jobs)
• Database trigger invocations
• Batch processing functions

​

4. Test Edge Functions

# Test locally
supabase functions serve {function-name}

# Test in production
curl -i --location --request POST \
  'https://{project-ref}.supabase.co/functions/v1/{function-name}' \
  --header 'Authorization: Bearer {anon-key}' \
  --header 'Content-Type: application/json' \
  --data '{"test": "data"}'

​

Storage Buckets

​

Current Storage Buckets

​

Create Storage Bucket

INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  '{bucket-name}',
  '{bucket-name}',
  false,  -- or true for public buckets
  52428800,  -- 50MB in bytes
  ARRAY[
    'application/pdf',
    'image/png',
    'image/jpeg'
  ]
)
ON CONFLICT (id) DO NOTHING;

1. Go to Storage → Buckets
2. Click New Bucket
3. Configure:
   • Name: Follow pattern {core}-{purpose}
   • Public: false (unless public access needed)
   • File Size Limit: Set appropriate limit
   • Allowed MIME Types: Restrict to needed types

​

Storage RLS Policies

-- Users can view files in their organization's folder
CREATE POLICY "Users can view org files"
ON storage.objects FOR SELECT
USING (
  bucket_id = '{bucket-name}'
  AND (storage.foldername(name))[1] IN (
    SELECT id::text FROM pf_organizations
    WHERE has_org_access(auth.uid(), id)
  )
);

-- Users can upload to their own folder
CREATE POLICY "Users can upload to own folder"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = '{bucket-name}'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

• Format: {core}-{purpose}
• Examples: pf-avatars, fw-submission-attachments, fa-report-exports
• See: constitution.md §5.2 (Storage Buckets)

​

Authentication Configuration

​

1. Email Authentication

1. Go to Authentication → Providers
2. Enable Email
3. Configure:
   • Enable email confirmations: true (production)
   • Secure email change: true
   • Double confirm email changes: true

• Customize in Authentication → Email Templates
• Templates: Confirmation, Invite, Magic Link, Password Reset

​

2. Password Policy

1. Go to Authentication → Policies
2. Set minimum password length: 8 (recommended: 12)
3. Enable password complexity requirements

​

3. Session Configuration

• JWT expiry: 3600 seconds (1 hour)
• Refresh token expiry: 604800 seconds (7 days)
• Auto-refresh: Enabled

[auth]
jwt_expiry = 3600
refresh_token_rotation_enabled = true

​

Realtime Subscriptions

​

Enable Realtime

-- Enable realtime for a table
ALTER PUBLICATION supabase_realtime ADD TABLE {table_name};

ALTER PUBLICATION supabase_realtime ADD TABLE pf_notifications;
ALTER PUBLICATION supabase_realtime ADD TABLE hr_employees;

​

Client-Side Usage

import { supabase } from '@/integrations/supabase/client';

// Subscribe to table changes
const channel = supabase
  .channel('notifications')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'pf_notifications',
    filter: `user_id=eq.${userId}`,
  }, (payload) => {
    console.log('New notification:', payload.new);
  })
  .subscribe();

• Unsubscribe when component unmounts
• Filter by organization_id for multi-tenant isolation
• Use specific filters to reduce data transfer

​

Backup and Recovery

​

Automated Backups

• Daily automated backups
• 7-day retention (default)
• Point-in-time recovery available

1. Go to Settings → Database
2. Configure backup schedule
3. Set retention period

​

Manual Backup

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

# Using pg_dump
pg_dump -h {host} -U postgres -d postgres > backup.sql

pg_dump -h {host} -U postgres -d postgres -n public > backup.sql

​

Restore Database

# Restore from SQL file
psql -h {host} -U postgres -d postgres < backup.sql

# Or via Supabase CLI
supabase db reset  # Resets and runs migrations

1. Go to Database → Backups
2. Select restore point
3. Click Restore

​

Performance Tuning

​

1. Database Indexes

-- Check indexes on foreign keys
SELECT 
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexdef LIKE '%FOREIGN KEY%'
ORDER BY tablename;

-- Example: Index on organization_id
CREATE INDEX IF NOT EXISTS idx_{table}_org 
ON {table}(organization_id);

-- Example: Composite index
CREATE INDEX IF NOT EXISTS idx_{table}_org_site 
ON {table}(organization_id, site_id);

​

2. Query Performance

-- Log slow queries (> 1 second)
ALTER DATABASE postgres SET log_min_duration_statement = 1000;

EXPLAIN ANALYZE
SELECT * FROM hr_employees 
WHERE organization_id = 'uuid';

​

3. Connection Limits

SELECT count(*) 
FROM pg_stat_activity 
WHERE datname = 'postgres';

​

4. Materialized Views

CREATE MATERIALIZED VIEW hr_employee_stats AS
SELECT 
  organization_id,
  COUNT(*) as total_employees,
  COUNT(*) FILTER (WHERE employment_status = 'active') as active_employees
FROM hr_employees
GROUP BY organization_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW hr_employee_stats;

​

Security Best Practices

​

1. RLS Policies

-- Check all tables have RLS
SELECT tablename 
FROM pg_tables 
WHERE schemaname = 'public' 
  AND rowsecurity = false;

• Use RLS test suite: tests/rls/
• Verify tenant isolation
• Test role-based access

​

2. Service Role Key

• Never use in client code
• Only in edge functions and tests
• Rotate regularly
• Monitor usage

1. Go to Settings → API
2. Click Reset service_role key
3. Update edge function secrets
4. Update test environment variables

​

3. API Keys

• Safe to expose (RLS enforced)
• Used in client applications
• Can be regenerated if compromised

• Never expose
• Regenerate if compromised
• Monitor access logs

​

4. Network Security

1. Go to Settings → Database
2. Configure IP allowlist
3. Restrict to known IPs (if needed)

• Always use HTTPS
• Supabase enforces SSL connections
• Verify certificate in production

​

Environment-Specific Setup

​

Development

# Start local Supabase
supabase start

# Get local credentials
supabase status

• URL: http://localhost:54321
• Anon key: From supabase status output
• Service role key: From supabase status output

​

Staging

• Separate Supabase project
• Same migrations as production
• Test data (no PHI/PII)
• Monitoring enabled

​

Production

• Separate Supabase project
• Strong database password
• Automated backups enabled
• Monitoring configured
• Alerts configured
• SSL/TLS verified
• RLS policies tested
• Edge functions deployed
• Storage buckets configured
• Secrets set for edge functions

​

Branching Setup

​

Overview

• ✅ Isolated database environments per branch
• ✅ Automatic migration sync via GitHub integration
• ✅ Easy branch switching in dashboard
• ✅ Cost-effective (branches included in plan)
• ✅ Preview branches for PR testing

​

Setup Persistent Staging Branch

# Create persistent staging branch
supabase --experimental branches create --persistent staging

# List branches to verify
supabase --experimental branches list

1. Go to Supabase Dashboard → Branches
2. Click “Create Branch”
3. Name: staging
4. Type: Persistent
5. Click “Create”

# Get branch project ID from dashboard (Branches page)
supabase link --project-ref <branch-project-id>

# Verify link
supabase projects list

# Apply all migrations to staging branch
supabase db push

# Verify migrations applied
supabase migration list

​

GitHub Integration

1. Go to Supabase Dashboard → Settings → Integrations
2. Click “Connect GitHub”
3. Authorize Supabase to access your repository
4. Select repository: encoreos-platform
5. Enable “Automatic migration deployment”

• main → Production Supabase project
• staging → Staging persistent branch
• develop → Development persistent branch (optional)
• Feature branches → Preview branches (auto-created for PRs)

• Pushes to staging branch → Automatically deploys migrations to staging Supabase branch
• Pushes to main branch → Automatically deploys migrations to production Supabase project
• Opening PRs → Creates preview branches automatically for testing

Developer → Feature Branch → PR → Preview Branch (auto-created)
                ↓
         Merge to staging → Staging Branch (persistent)
                ↓
         Merge to main → Production Project

​

Branch Configuration

• Long-lived environments (staging, production)
• Map to Git branches
• Sync migrations automatically
• Use for testing before production

• Short-lived (deleted after PR merge)
• Created automatically for PRs
• Use for testing feature migrations
• Isolated from other branches

​

Branch Naming Convention

​

Switching Between Branches

1. Click branch dropdown in top menu
2. Select target branch
3. All operations now target selected branch

# List all branches
supabase --experimental branches list

# Get branch details
supabase --experimental branches get <branch-name>

# Link to specific branch
supabase link --project-ref <branch-project-id>

​

Branch Isolation

• Database instance (isolated data)
• API endpoints (unique URLs)
• Authentication settings
• Storage buckets
• Edge Functions

​

Best Practices

1. Always test migrations in staging before production
2. Use preview branches for PR testing
3. Keep staging branch in sync with production schema
4. Document branch-specific configurations in config.toml
5. Monitor branch usage to avoid unnecessary costs

• Supabase Branching Guide
• GitHub Integration Guide
• docs/migration/COMPLETION_PLAN.md for migration workflow

​

Troubleshooting

​

Issue: Migration Fails

• Migration errors during deployment
• Schema conflicts

1. Check migration order (timestamps)
2. Verify no manual schema changes
3. Test migrations locally first
4. Check for breaking changes

​

Issue: RLS Policy Errors

• “permission denied” errors
• Infinite recursion errors

1. Verify SECURITY DEFINER functions used
2. Check policy syntax
3. Test with RLS test suite
4. Review constitution.md §5.7

​

Issue: Edge Function Timeout

• Function returns 504 after 60 seconds

1. Break into smaller functions
2. Use database functions for heavy processing
3. Implement pagination for large datasets
4. Add timeout handling

​

Issue: Storage Upload Fails

• “permission denied” on storage uploads

1. Verify storage RLS policies
2. Check bucket exists
3. Verify file size within limits
4. Check MIME type is allowed

​

Issue: Connection Pool Exhausted

• “too many connections” errors

1. Use connection pooling (port 6543)
2. Close connections properly
3. Reduce connection pool size
4. Monitor connection usage

​

Monitoring

​

Supabase Dashboard

• Database CPU/Memory usage
• API request count
• Storage usage
• Edge function invocations
• Authentication events

​

Custom Monitoring

• Database CPU > 80%
• API error rate > 1%
• Storage usage > 80%
• Edge function failures
• Connection pool exhaustion

​

Related Documentation

• Supabase MCP Usage: docs/development/MCP_USAGE.md - When to use MCP vs CLI
• Migration Guide: docs/migration/COMPLETION_PLAN.md - Complete migration workflow
• Edge Functions: docs/integrations/EDGE_FUNCTIONS.md
• Environment Variables: docs/development/ENVIRONMENT_VARIABLES.md
• Secrets Management: docs/security/SECRETS_MANAGEMENT.md
• Constitution: constitution.md §5.8-5.9 (Edge Function Patterns)
• Supabase Branching: Official Branching Guide