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

# Supabase Setup Guide

> Version: 1.1.0 Last Updated: 2026-03-23

**Version:** 1.1.0\
**Last Updated:** 2026-03-23

How to set up and configure Supabase for the Encore Health OS Platform in development, staging, and production.

***

## Table of Contents

1. [Overview](#overview)
2. [Project Setup](#project-setup)
3. [Database Configuration](#database-configuration)
4. [Connection Pooling](#connection-pooling)
5. [Edge Functions Deployment](#edge-functions-deployment)
6. [Storage Buckets](#storage-buckets)
7. [Authentication Configuration](#authentication-configuration)
8. [Realtime Subscriptions](#realtime-subscriptions)
9. [Backup and Recovery](#backup-and-recovery)
10. [Performance Tuning](#performance-tuning)
11. [Security Best Practices](#security-best-practices)
12. [Environment-Specific Setup](#environment-specific-setup)
13. [Branching Setup](#branching-setup)
14. [Troubleshooting](#troubleshooting)

***

## Overview

Supabase serves as the primary backend for Encore Health 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

1. Go to [Supabase Dashboard](https://app.supabase.com)
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

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

```bash theme={null}
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:**

```bash theme={null}
# Start Supabase locally
supabase start

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

**Production:**

```bash theme={null}
# 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:**

```sql theme={null}
-- 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:**

```sql theme={null}
-- Verify RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' 
  AND tablename LIKE 'pf_%';
```

**Check Functions:**

```sql theme={null}
-- 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!**

```bash theme={null}
# 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:**

```typescript theme={null}
// Use direct connection (service role key)
const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);
```

**For Client Applications:**

```typescript theme={null}
// 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

```bash theme={null}
# Deploy all functions
supabase functions deploy

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

### 2. Set Function Secrets

```bash theme={null}
# 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
```

**List Secrets:**

```bash theme={null}
supabase secrets list
```

### 3. Configure JWT Verification

Edit `supabase/config.toml`:

```toml theme={null}
[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

```bash theme={null}
# 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:**

```sql theme={null}
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:**

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

**Example: Organization-Scoped Access**

```sql theme={null}
-- 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**

```sql theme={null}
-- 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:**

1. Go to **Authentication** → **Providers**
2. Enable **Email**
3. 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:**

1. Go to **Authentication** → **Policies**
2. Set minimum password length: `8` (recommended: `12`)
3. 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`:**

```toml theme={null}
[auth]
jwt_expiry = 3600
refresh_token_rotation_enabled = true
```

***

## Realtime Subscriptions

### Enable Realtime

**For Tables:**

```sql theme={null}
-- Enable realtime for a table
ALTER PUBLICATION supabase_realtime ADD TABLE {table_name};
```

**Example:**

```sql theme={null}
ALTER PUBLICATION supabase_realtime ADD TABLE pf_notifications;
ALTER PUBLICATION supabase_realtime ADD TABLE hr_employees;
```

### Client-Side Usage

```typescript theme={null}
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:**

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

### Manual Backup

**Export Database:**

```bash theme={null}
# 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:**

```bash theme={null}
pg_dump -h {host} -U postgres -d postgres -n public > backup.sql
```

### Restore Database

**From Backup:**

```bash theme={null}
# 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:**

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

***

## Performance Tuning

### 1. Database Indexes

**Verify Indexes Exist:**

```sql theme={null}
-- 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:**

```sql theme={null}
-- 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:**

```sql theme={null}
-- Log slow queries (> 1 second)
ALTER DATABASE postgres SET log_min_duration_statement = 1000;
```

**Analyze Query Plans:**

```sql theme={null}
EXPLAIN ANALYZE
SELECT * FROM hr_employees 
WHERE organization_id = 'uuid';
```

### 3. Connection Limits

**Monitor Connections:**

```sql theme={null}
SELECT count(*) 
FROM pg_stat_activity 
WHERE datname = 'postgres';
```

**Max Connections:** 200 (Supabase default)

### 4. Materialized Views

**For Heavy Aggregations:**

```sql theme={null}
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:**

```sql theme={null}
-- 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:**

1. Go to **Settings** → **API**
2. Click **Reset service\_role key**
3. Update edge function secrets
4. 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):**

1. Go to **Settings** → **Database**
2. Configure IP allowlist
3. 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:**

```bash theme={null}
# 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:**

* [ ] 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

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

```bash theme={null}
# Create persistent staging branch
supabase --experimental branches create --persistent staging

# List branches to verify
supabase --experimental branches list
```

**2. Create Branch via Dashboard:**

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

**3. Link Local Project:**

```bash theme={null}
# Get branch project ID from dashboard (Branches page)
supabase link --project-ref <branch-project-id>

# Verify link
supabase projects list
```

**4. Apply Migrations:**

```bash theme={null}
# Apply all migrations to staging branch
supabase db push

# Verify migrations applied
supabase migration list
```

### GitHub Integration

**Enable 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"

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

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

**Via CLI:**

```bash theme={null}
# 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

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

**See:**

* [Supabase Branching Guide](https://supabase.com/docs/guides/deployment/branching)
* [GitHub Integration Guide](https://supabase.com/docs/guides/deployment/branching/github-integration)
* `docs/migration/COMPLETION_PLAN.md` for migration workflow

***

## Troubleshooting

### Issue: Migration Fails

**Symptoms:**

* Migration errors during deployment
* Schema conflicts

**Solutions:**

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

**Symptoms:**

* "permission denied" errors
* Infinite recursion errors

**Solutions:**

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

**Symptoms:**

* Function returns 504 after 60 seconds

**Solutions:**

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

**Symptoms:**

* "permission denied" on storage uploads

**Solutions:**

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

**Symptoms:**

* "too many connections" errors

**Solutions:**

1. Use connection pooling (port 6543)
2. Close connections properly
3. Reduce connection pool size
4. 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

***

## 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](https://supabase.com/docs/guides/deployment/branching)

***

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