Encore OS — Documentation

Version: 1.1.0
Last Updated: 2026-03-23 How to set up and configure Supabase for the Encore OS Platform in development, staging, and production.

Overview
Project Setup
Database Configuration
Connection Pooling
Edge Functions Deployment
Storage Buckets
Authentication Configuration
Realtime Subscriptions
Backup and Recovery
Performance Tuning
Security Best Practices
Environment-Specific Setup
Branching Setup
Troubleshooting

Overview

Supabase serves as the primary backend for Encore OS Platform, providing:

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

Go to Supabase Dashboard

Click New Project

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

After project creation, get credentials from Settings → API:

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

3. Configure Environment Variables

Set in .env.local (development) or Vercel (production):

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

Never commit service role key to client code!

Database Configuration

1. Run Migrations

Local Development:

# Start Supabase locally
supabase start

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

Production:

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

# Run migrations
supabase db push

Migration Files: Located in supabase/migrations/

2. Verify Database Setup

Check Tables:

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

Check RLS:

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

Check Functions:

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

3. Seed Data (Development Only)

Never seed production with real PHI/PII!

# Run seed scripts (if available)
supabase db seed

Connection Pooling

Why Connection Pooling?

Supabase has connection limits. Connection pooling allows multiple clients to share database connections efficiently.

Supabase Connection Pooler

Direct Connection (Development):

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

Pooled Connection (Production):

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

Port Differences:

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

Configuration

For Edge Functions:

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

For Client Applications:

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

Connection Pool Settings:

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 (RingCentral)
supabase secrets set RINGCENTRAL_CLIENT_ID=xxxxx
supabase secrets set RINGCENTRAL_CLIENT_SECRET=xxxxx
supabase secrets set RINGCENTRAL_JWT_TOKEN=xxxxx

# Platform
supabase secrets set LOVABLE_API_KEY=xxxxx

List Secrets:

supabase secrets list

3. Configure JWT Verification

Edit supabase/config.toml:

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

Functions Requiring JWT (verify_jwt = true):

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

Functions Without JWT (verify_jwt = false):

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"}'

See: docs/integrations/EDGE_FUNCTIONS.md for complete function documentation

Storage Buckets

Current Storage Buckets

Bucket Name	Purpose	Public	Size Limit	MIME Types
`documents`	General documents (PF-11)	No	50MB	PDF, DOCX, XLSX, images, text
`submission-attachments`	Form submission attachments (FW-02)	No	50MB	Images, PDF, Office docs, text
`fa-report-exports`	Finance report exports	No	10MB	PDF, CSV, XLSX
`pf-avatars`	User profile avatars	Yes	5MB	Images only

Create Storage Bucket

Via Migration:

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;

Via Supabase Dashboard:

Go to Storage → Buckets

Click New Bucket

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

Example: Organization-Scoped Access

-- 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)
  )
);

Example: User-Scoped Uploads

-- 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
);

Bucket Naming Convention:

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

Authentication Configuration

1. Email Authentication

Enable Email Auth:

Go to Authentication → Providers

Enable Email

Configure:

Enable email confirmations: true (production)
Secure email change: true
Double confirm email changes: true

Email Templates:

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

2. Password Policy

Configure Password Requirements:

Go to Authentication → Policies

Set minimum password length: 8 (recommended: 12)

Enable password complexity requirements

3. Session Configuration

Session Settings:

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

Configure in supabase/config.toml:

[auth]
jwt_expiry = 3600
refresh_token_rotation_enabled = true

Realtime Subscriptions

Enable Realtime

For Tables:

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

Example:

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();

Best Practices:

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

Backup and Recovery

Automated Backups

Supabase Pro Plan:

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

Backup Settings:

Go to Settings → Database

Configure backup schedule

Set retention period

Manual Backup

Export Database:

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

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

Export Specific Schema:

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

Restore Database

From Backup:

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

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

Point-in-Time Recovery:

Go to Database → Backups

Select restore point

Click Restore

Performance Tuning

1. Database Indexes

Verify Indexes Exist:

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

Create Missing Indexes:

-- 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

Enable Query Logging:

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

Analyze Query Plans:

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

3. Connection Limits

Monitor Connections:

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

Max Connections: 200 (Supabase default)

4. Materialized Views

For Heavy Aggregations:

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

Verify RLS is Enabled:

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

Test RLS Policies:

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

2. Service Role Key

⚠️ CRITICAL:

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

Rotate Service Role Key:

Go to Settings → API

Click Reset service_role key

Update edge function secrets

Update test environment variables

3. API Keys

Anon Key:

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

Service Role Key:

Never expose
Regenerate if compromised
Monitor access logs

4. Network Security

IP Restrictions (Optional):

Go to Settings → Database

Configure IP allowlist

Restrict to known IPs (if needed)

SSL/TLS:

Always use HTTPS
Supabase enforces SSL connections
Verify certificate in production

Environment-Specific Setup

Development

Local Supabase:

# Start local Supabase
supabase start

# Get local credentials
supabase status

Local Credentials:

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

Staging

Staging Project:

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

Production

Production Checklist:

Branching Setup

Overview

Supabase Branches provide isolated database environments that sync with Git branches. This eliminates the need for separate projects for staging/testing and enables automatic migration deployment. Benefits:

✅ 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

1. Create Branch via CLI:

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

# List branches to verify
supabase --experimental branches list

2. Create Branch via Dashboard:

Go to Supabase Dashboard → Branches

Click “Create Branch”

Name: staging

Type: Persistent

Click “Create”

3. Link Local Project:

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

# Verify link
supabase projects list

4. Apply Migrations:

# Apply all migrations to staging branch
supabase db push

# Verify migrations applied
supabase migration list

GitHub Integration

Enable GitHub Integration:

Go to Supabase Dashboard → Settings → Integrations

Click “Connect GitHub”

Authorize Supabase to access your repository

Select repository: encoreos-platform

Enable “Automatic migration deployment”

Branch Mapping:

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

How It Works:

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

Workflow Example:

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

Branch Configuration

Persistent Branches:

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

Preview Branches:

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

Branch Naming Convention

Git Branch	Supabase Branch/Project	Purpose
`main`	Production project	Production environment
`staging`	Staging persistent branch	Pre-production testing
`develop`	Development persistent branch (optional)	Development testing
`feature/*`	Preview branch (auto-created)	Feature testing

Switching Between Branches

Via Dashboard:

Click branch dropdown in top menu

Select target branch

All operations now target selected branch

Via CLI:

# 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

Each branch has its own:

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

Important: Changes in one branch do not affect others.

Best Practices

Always test migrations in staging before production

Use preview branches for PR testing

Keep staging branch in sync with production schema

Document branch-specific configurations in config.toml

Monitor branch usage to avoid unnecessary costs

See:

Supabase Branching Guide
GitHub Integration Guide
docs/database/migration-workflow.md for the live migration workflow (the one-time 2026-01 playbook is archived at docs/archive/migration-2026-01/COMPLETION_PLAN.md)

Troubleshooting

Issue: Migration Fails

Symptoms:

Migration errors during deployment
Schema conflicts

Solutions:

Check migration order (timestamps)

Verify no manual schema changes

Test migrations locally first

Check for breaking changes

Issue: RLS Policy Errors

Symptoms:

“permission denied” errors
Infinite recursion errors

Solutions:

Verify SECURITY DEFINER functions used

Check policy syntax

Test with RLS test suite

Review constitution.md §5.7

Issue: Edge Function Timeout

Symptoms:

Function returns 504 after 60 seconds

Solutions:

Break into smaller functions

Use database functions for heavy processing

Implement pagination for large datasets

Add timeout handling

Issue: Storage Upload Fails

Symptoms:

“permission denied” on storage uploads

Solutions:

Verify storage RLS policies

Check bucket exists

Verify file size within limits

Check MIME type is allowed

Issue: Connection Pool Exhausted

Symptoms:

“too many connections” errors

Solutions:

Use connection pooling (port 6543)

Close connections properly

Reduce connection pool size

Monitor connection usage

Monitoring

Supabase Dashboard

Metrics Available:

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

Custom Monitoring

Set up alerts for:

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

Supabase MCP Usage: docs/development/MCP_USAGE.md - When to use MCP vs CLI
Migration Guide: docs/database/migration-workflow.md - Live migration workflow (one-time 2026-01 playbook archived at docs/archive/migration-2026-01/COMPLETION_PLAN.md)
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

Document Owner: Platform Team
Review Frequency: Quarterly
Last Updated: 2025-01-07

​Table of Contents

​Overview

​Project Setup

​1. Create Supabase Project

​2. Get Project Credentials

​3. Configure Environment Variables

​Database Configuration

​1. Run Migrations

​2. Verify Database Setup

​3. Seed Data (Development Only)

​Connection Pooling

​Why Connection Pooling?

​Supabase Connection Pooler

​Configuration

​Edge Functions Deployment

​1. Deploy All Functions

​2. Set Function Secrets

​3. Configure JWT Verification

​4. Test Edge Functions

​Storage Buckets

​Current Storage Buckets

​Create Storage Bucket

​Storage RLS Policies

​Authentication Configuration

​1. Email Authentication

​2. Password Policy

​3. Session Configuration

​Realtime Subscriptions

​Enable Realtime

​Client-Side Usage

​Backup and Recovery

​Automated Backups

​Manual Backup

​Restore Database

​Performance Tuning

​1. Database Indexes

​2. Query Performance

​3. Connection Limits

​4. Materialized Views

​Security Best Practices

​1. RLS Policies

​2. Service Role Key

​3. API Keys

​4. Network Security

​Environment-Specific Setup

​Development

​Staging

​Production

​Branching Setup

​Overview

​Setup Persistent Staging Branch

​GitHub Integration

​Branch Configuration

​Branch Naming Convention

​Switching Between Branches

​Branch Isolation

​Best Practices

​Troubleshooting

​Issue: Migration Fails

​Issue: RLS Policy Errors

​Issue: Edge Function Timeout

​Issue: Storage Upload Fails

​Issue: Connection Pool Exhausted

​Monitoring

​Supabase Dashboard

​Custom Monitoring

​Related Documentation

Table of Contents

Overview

Project Setup

1. Create Supabase Project

2. Get Project Credentials

3. Configure Environment Variables

Database Configuration

1. Run Migrations

2. Verify Database Setup

3. Seed Data (Development Only)

Connection Pooling

Why Connection Pooling?

Supabase Connection Pooler

Configuration

Edge Functions Deployment

1. Deploy All Functions

2. Set Function Secrets

3. Configure JWT Verification

4. Test Edge Functions

Storage Buckets

Current Storage Buckets

Create Storage Bucket

Storage RLS Policies

Authentication Configuration

1. Email Authentication

2. Password Policy

3. Session Configuration

Realtime Subscriptions

Enable Realtime

Client-Side Usage

Backup and Recovery

Automated Backups

Manual Backup

Restore Database

Performance Tuning

1. Database Indexes

2. Query Performance

3. Connection Limits

4. Materialized Views

Security Best Practices

1. RLS Policies

2. Service Role Key

3. API Keys

4. Network Security

Environment-Specific Setup

Development

Staging

Production

Branching Setup

Overview

Setup Persistent Staging Branch

GitHub Integration

Branch Configuration

Branch Naming Convention

Switching Between Branches

Branch Isolation

Best Practices

Troubleshooting

Issue: Migration Fails

Issue: RLS Policy Errors

Issue: Edge Function Timeout

Issue: Storage Upload Fails

Issue: Connection Pool Exhausted

Monitoring

Supabase Dashboard

Custom Monitoring

Related Documentation