> ## 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 Migration Completion Plan

> Version: 2.1.0 Date: 2026-01-15 Status: Ready for Execution (MCP-First Approach)

**Version:** 2.1.0\
**Date:** 2026-01-15\
**Status:** Ready for Execution (MCP-First Approach)

***

## Executive Summary

This plan provides step-by-step instructions to complete the migration from Lovable Cloud to Supabase Cloud. It uses an **MCP-first approach** for read operations and verification, with CLI for write operations.

**Key Principle:** Use **Supabase MCP** for discovery, verification, and documentation. Use **Supabase CLI** for execution and deployment.

***

## Tool Usage Summary

| Task Type         | Tool | Examples                                              |
| ----------------- | ---- | ----------------------------------------------------- |
| **Read/Verify**   | MCP  | List tables, list branches, check status, search docs |
| **Write/Execute** | CLI  | Create migrations, apply migrations, generate types   |
| **Documentation** | MCP  | Search Supabase docs for patterns, best practices     |
| **Local Dev**     | CLI  | Start local Supabase, reset database                  |

**Reference:** See `docs/development/MCP_USAGE.md` for complete decision matrix.

***

## Phase 0: Pre-Setup Verification (MCP-First)

> **Purpose:** Verify current setup before proceeding with migration steps.

### Step 0.1: Verify MCP Connection

**Use MCP:**

```typescript theme={null}
// Get project URL to verify connection
mcp_supabase_get_project_url()

// List branches to see available environments
mcp_supabase_list_branches()
```

**Expected Results:**

* Project URL returned (e.g., `https://<project-ref>.supabase.co`)
* Branch list shows `main` and any other branches (e.g., `staging`)

### Step 0.2: Verify Supabase CLI

**Use CLI:**

```bash theme={null}
# Check CLI version
supabase --version
# Expected: Supabase CLI 1.x.x or higher

# Check linked project
supabase projects list
# Expected: Shows encoreos-platform project

# Check current link status
supabase status
# Expected: Shows linked project details
```

### Step 0.3: Search Documentation for Latest Guidance

**Use MCP:**

```typescript theme={null}
// Search for branching best practices
mcp_supabase_search_docs({
  graphql_query: `{
    searchDocs(query: "branching persistent preview", limit: 5) {
      nodes {
        title
        href
        content
      }
    }
  }`
})

// Search for migration best practices
mcp_supabase_search_docs({
  graphql_query: `{
    searchDocs(query: "database migrations workflow", limit: 5) {
      nodes {
        title
        href
        content
      }
    }
  }`
})
```

### Step 0.4: Check Migration Files Locally

**Use CLI:**

```bash theme={null}
# Count migration files
Get-ChildItem "supabase/migrations/*.sql" | Measure-Object

# List recent migrations
Get-ChildItem "supabase/migrations/*.sql" | Sort-Object LastWriteTime -Descending | Select-Object -First 10 Name
```

**Expected:** 393+ migration files in `supabase/migrations/`

### Troubleshooting: Phase 0

| Issue                         | Solution                                           |
| ----------------------------- | -------------------------------------------------- |
| MCP returns empty project URL | Verify MCP server is running, check authentication |
| CLI version outdated          | Run `npm install -g supabase` to update            |
| No linked project             | Run `supabase link --project-ref <project-id>`     |
| Migration files not found     | Verify you're in the project root directory        |

***

## Phase 1: Staging Branch Setup

> **Purpose:** Create or verify staging branch for testing migrations.

### Step 1.1: Check Existing Branches

**Use MCP (First):**

```typescript theme={null}
// List all branches
mcp_supabase_list_branches()
```

**Expected Output:**

* `main` branch (production)
* `staging` branch (if already created)

### Step 1.2: Create Staging Branch (If Needed)

**Use CLI (For Creation):**

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

# Note: Save the branch project ID from output
# Example output: Created branch staging with project ID: <branch-project-id>
```

### Step 1.3: Verify Branch Creation

**Use MCP (To Verify):**

```typescript theme={null}
// List branches again to confirm
mcp_supabase_list_branches()
```

**Expected:** `staging` branch appears in list with status "active"

### Step 1.4: Link to Staging Branch

**Use CLI:**

```bash theme={null}
# Link local project to staging branch
supabase link --project-ref <staging-branch-project-id>

# Verify link
supabase status
```

### Step 1.5: GitHub Integration Setup (Optional but Recommended)

**Purpose:** Sync staging branch with `staging` Git branch for automatic migration deployment.

**Via Dashboard:**

1. Go to Supabase Dashboard → Project Settings → Integrations → GitHub
2. Connect your GitHub repository
3. Map Git branches to Supabase branches:
   * `main` Git branch → Supabase `main` project
   * `staging` Git branch → Supabase `staging` branch

**Verification:**

* Push to `staging` Git branch
* Migrations should auto-deploy to Supabase staging branch

### Troubleshooting: Phase 1

| Issue                      | Solution                                             |
| -------------------------- | ---------------------------------------------------- |
| Branch creation fails      | Check Supabase plan includes branching feature       |
| Link fails                 | Verify branch project ID is correct                  |
| GitHub integration errors  | Check repository permissions, re-authorize if needed |
| Branch not showing in list | Wait 1-2 minutes for propagation, refresh MCP call   |

***

## Phase 2: Apply Migrations to Staging

> **Purpose:** Deploy all 393+ migrations to staging branch.

### Step 2.1: Verify Migration Inventory

**Use CLI (Local Analysis):**

```bash theme={null}
# Run static analysis script
npx ts-node scripts/lint-migrations.ts

# Generate reports
npx ts-node scripts/generate-migration-reports.ts
```

**Use MCP (After Deployment):**

```typescript theme={null}
// List migrations applied to database
mcp_supabase_list_migrations()
```

### Step 2.2: Apply Migrations

**Use CLI:**

```bash theme={null}
# Ensure linked to staging
supabase status

# Push all migrations
supabase db push

# Monitor output for errors
```

**Expected:** 393+ migrations applied without errors

### Step 2.3: Verify Tables Created

**Use MCP:**

```typescript theme={null}
// List all tables in public schema
mcp_supabase_list_tables({ schemas: ["public"] })
```

**Expected Table Counts:**

| Module                       | Expected Tables |
| ---------------------------- | --------------- |
| Platform Foundation (pf\_)   | 74              |
| Human Resources (hr\_)       | 94              |
| Finance & Accounting (fa\_)  | 46              |
| Forms & Workflow (fw\_)      | 54              |
| Recovery Housing (rh\_)      | 51              |
| Governance & Risk (gr\_)     | 49              |
| Facilities Management (fm\_) | 22              |
| Leadership OS (lo\_)         | 29              |
| IT Service Management (it\_) | 35              |
| **Total**                    | **\~465**       |

### Step 2.4: Check for Security Advisors

**Use MCP:**

```typescript theme={null}
// Get security advisors
mcp_supabase_get_advisors({ type: "security" })

// Get performance advisors
mcp_supabase_get_advisors({ type: "performance" })
```

**Action:** Document any advisories for remediation.

### Troubleshooting: Phase 2

| Issue                     | Solution                                    |
| ------------------------- | ------------------------------------------- |
| Migration fails           | Check error message, fix SQL syntax, retry  |
| Duplicate migration error | Migration already applied, safe to skip     |
| Permission denied         | Check database connection string, re-link   |
| Table count mismatch      | Some tables may be in other schemas, verify |

***

## Phase 3: Run SQL Audits

> **Purpose:** Execute audit scripts against staging to identify issues.

### Step 3.1: RLS Policy Audit

**Use Supabase Dashboard or MCP Execute SQL:**

**Script:** `scripts/audit-rls-policies.sql`

**MCP (Execute SQL):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT schemaname, tablename, policyname, cmd, qual, with_check
    FROM pg_policies
    WHERE schemaname = 'public' AND cmd = 'UPDATE' AND with_check IS NULL;
  `
})
```

**Expected Results:**

* \~118 UPDATE policies missing WITH CHECK
* Save results to `reports/audits/rls-audit-results.sql`

### Step 3.2: Column Compliance Audit

**Use MCP:**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT table_name, 
           bool_or(column_name = 'created_by') as has_created_by,
           bool_or(column_name = 'updated_by') as has_updated_by
    FROM information_schema.columns
    WHERE table_schema = 'public'
    GROUP BY table_name
    HAVING bool_or(column_name = 'created_by') AND NOT bool_or(column_name = 'updated_by');
  `
})
```

**Expected Results:**

* \~60 tables have `created_by` but missing `updated_by`
* Save results to `reports/audits/column-audit-results.sql`

### Step 3.3: Storage Bucket Audit

**Use MCP:**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT name, public, file_size_limit, allowed_mime_types
    FROM storage.buckets
    WHERE name NOT LIKE '%-%';
  `
})
```

**Expected Results:**

* 4 non-compliant buckets: `avatars`, `portal-logos`, `documents`, `spreadsheets`
* Save results for bucket rename migration

### Step 3.4: Document Audit Findings

Create/update `reports/audits/SQL_AUDIT_RESULTS.md` with:

* Date of audit
* Staging branch used
* Exact counts from each audit
* Fix statements generated

### Troubleshooting: Phase 3

| Issue                 | Solution                                      |
| --------------------- | --------------------------------------------- |
| SQL execution timeout | Break query into smaller chunks               |
| Permission denied     | Check RLS policies on pg\_catalog tables      |
| Empty results         | Verify migrations applied correctly           |
| MCP execute fails     | Use Supabase Dashboard SQL Editor as fallback |

***

## Phase 4: Create Fix Migrations

> **Purpose:** Create migrations to fix identified issues.

### Step 4.1: Search for Fix Patterns

**Use MCP (Documentation):**

```typescript theme={null}
// Search for RLS UPDATE WITH CHECK patterns
mcp_supabase_search_docs({
  graphql_query: `{
    searchDocs(query: "RLS policy WITH CHECK UPDATE", limit: 5) {
      nodes {
        title
        content
      }
    }
  }`
})
```

### Step 4.2: Create RLS Fix Migration

**Use CLI:**

```bash theme={null}
# Create migration file
supabase migration new fix_rls_with_check

# Edit the new file in supabase/migrations/
# Use template from supabase/migrations/TEMPLATE_fix_rls_with_check.sql
```

**Fill with Query Output:**

* Paste generated `ALTER POLICY` statements from audit results
* Review and validate SQL syntax

### Step 4.3: Create Storage Bucket Rename Migration

**Use CLI:**

```bash theme={null}
# Create migration file
supabase migration new fix_storage_bucket_names

# Edit with rename statements
```

**Example Migration Content:**

```sql theme={null}
-- Rename non-compliant buckets to follow {core}-{purpose} pattern
UPDATE storage.buckets SET name = 'pf-avatars' WHERE name = 'avatars';
UPDATE storage.buckets SET name = 'pf-portal-logos' WHERE name = 'portal-logos';
UPDATE storage.buckets SET name = 'pf-documents' WHERE name = 'documents';
UPDATE storage.buckets SET name = 'pf-spreadsheets' WHERE name = 'spreadsheets';
```

### Step 4.4: Create Missing Column Migration

**Use CLI:**

```bash theme={null}
# Create migration file
supabase migration new fix_missing_audit_columns

# Edit with ADD COLUMN statements from audit
```

### Step 4.5: Verify Migration Files

**Use CLI:**

```bash theme={null}
# List new migrations
Get-ChildItem "supabase/migrations/*.sql" | Where-Object { $_.Name -match "fix_" } | Select-Object Name
```

### Troubleshooting: Phase 4

| Issue                     | Solution                           |
| ------------------------- | ---------------------------------- |
| Migration naming conflict | Use unique timestamp prefix        |
| SQL syntax error          | Test in Dashboard SQL Editor first |
| Too many changes          | Split into multiple migrations     |
| Uncertain about fix       | Search MCP docs for examples       |

***

## Phase 5: Apply and Validate Fixes

> **Purpose:** Deploy fix migrations and verify issues resolved.

### Step 5.1: Apply Fix Migrations

**Use CLI:**

```bash theme={null}
# Ensure linked to staging
supabase status

# Apply new migrations
supabase db push

# Check for errors
```

### Step 5.2: Verify Migrations Applied

**Use MCP:**

```typescript theme={null}
// List migrations to confirm
mcp_supabase_list_migrations()
```

**Expected:** New fix migrations appear in list.

### Step 5.3: Re-Run Audits to Validate

**Use MCP:**

```typescript theme={null}
// RLS Audit - Should return 0 rows
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) 
    FROM pg_policies
    WHERE schemaname = 'public' AND cmd = 'UPDATE' AND with_check IS NULL;
  `
})

// Column Audit - Should return 0 rows
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) 
    FROM information_schema.columns c1
    WHERE c1.table_schema = 'public' 
      AND c1.column_name = 'created_by'
      AND NOT EXISTS (
        SELECT 1 FROM information_schema.columns c2 
        WHERE c2.table_schema = c1.table_schema 
          AND c2.table_name = c1.table_name 
          AND c2.column_name = 'updated_by'
      );
  `
})

// Storage Audit - Should return 0 rows
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) FROM storage.buckets WHERE name NOT LIKE '%-%';
  `
})
```

**Expected Results:**

* RLS audit: 0 UPDATE policies missing WITH CHECK
* Column audit: 0 tables missing `updated_by`
* Storage audit: 0 non-compliant buckets

### Step 5.4: Get Updated Advisors

**Use MCP:**

```typescript theme={null}
// Check for remaining security issues
mcp_supabase_get_advisors({ type: "security" })
```

### Troubleshooting: Phase 5

| Issue                    | Solution                                       |
| ------------------------ | ---------------------------------------------- |
| Fix migration fails      | Check SQL syntax, rollback and fix             |
| Audit still shows issues | Review fix migration, may have missed some     |
| Advisors show new issues | Document and plan additional fixes             |
| Rollback needed          | Use `supabase db reset` and reapply migrations |

***

## Phase 6: Generate TypeScript Types

> **Purpose:** Generate database types for application type safety.

### Step 6.1: Generate Types via CLI

**Use CLI (Preferred):**

```bash theme={null}
# Generate types from staging
supabase gen types typescript --project-id <staging-project-id> > src/integrations/supabase/types.ts

# Verify file size (should be substantial)
(Get-Item "src/integrations/supabase/types.ts").Length
```

**Alternative - Use MCP:**

```typescript theme={null}
// Generate types via MCP
mcp_supabase_generate_typescript_types()
```

### Step 6.2: Validate Types

**Use CLI:**

```bash theme={null}
# Run TypeScript type check
npm run typecheck

# Expected: 0 errors
```

### Step 6.3: Verify Table Definitions

**Manual Check:**

* Open `src/integrations/supabase/types.ts`
* Verify all 465 tables have type definitions
* No `[_ in never]: never` empty definitions

### Troubleshooting: Phase 6

| Issue                 | Solution                                      |
| --------------------- | --------------------------------------------- |
| Type generation fails | Check project ID, verify connection           |
| Types file empty      | Migrations may not be applied, check database |
| TypeScript errors     | Review generated types, may need manual fixes |
| Missing table types   | Table may be in different schema              |

***

## Phase 7: Application Testing

> **Purpose:** Validate application works with staging database.

### Step 7.1: Update Environment Variables

```bash theme={null}
# Create .env.staging file
VITE_SUPABASE_URL=<staging-branch-url>
VITE_SUPABASE_ANON_KEY=<staging-anon-key>
```

### Step 7.2: Run Application Tests

**Use CLI:**

```bash theme={null}
# Start application in staging mode
$env:VITE_SUPABASE_URL="<staging-url>"; npm run dev

# Run test suite
npm run test
```

### Step 7.3: Test Critical Flows

* [ ] Authentication (login/logout)
* [ ] Data CRUD operations
* [ ] Multi-tenant isolation
* [ ] Storage operations
* [ ] Edge function calls

### Step 7.4: Verify No Console Errors

* [ ] No RLS policy errors
* [ ] No type mismatches
* [ ] No connection errors

### Troubleshooting: Phase 7

| Issue            | Solution                                   |
| ---------------- | ------------------------------------------ |
| Auth fails       | Check auth providers configured in staging |
| Data not loading | Verify RLS policies allow access           |
| Storage errors   | Check bucket names updated in code         |
| Type errors      | Regenerate types, update interfaces        |

***

## Phase 8: Pre-Production Checklist

> **Purpose:** Complete final validation before production migration.

See detailed checklist: `docs/migration/PRE_PRODUCTION_CHECKLIST.md`

### Summary Checklist

* [ ] All 393+ migrations applied to staging
* [ ] 0 UPDATE policies missing WITH CHECK
* [ ] 0 non-compliant storage buckets
* [ ] 0 tables missing `updated_by` (where `created_by` exists)
* [ ] TypeScript types generated and validated
* [ ] Application tested against staging
* [ ] Multi-tenant isolation verified
* [ ] Performance acceptable
* [ ] Security advisors reviewed
* [ ] Documentation updated

***

## Phase 9: Production Migration

> **Purpose:** Execute production migration with full validation.

### Pre-Migration Checklist

* [ ] Staging validation complete
* [ ] Team notified of maintenance window
* [ ] Backup strategy confirmed
* [ ] Rollback plan documented
* [ ] Environment variables prepared

### Migration Steps

1. **Export from Lovable** (if applicable)
2. **Create/Configure Production Project**
3. **Apply All Migrations**
4. **Import Data**
5. **Deploy Edge Functions**
6. **Generate Production Types**
7. **Update Application Environment**
8. **Validate Production**

### Post-Migration Validation

**Use MCP:**

```typescript theme={null}
// Verify project URL
mcp_supabase_get_project_url()

// List tables
mcp_supabase_list_tables({ schemas: ["public"] })

// Check security advisors
mcp_supabase_get_advisors({ type: "security" })
```

**Use CLI:**

```bash theme={null}
# Generate production types
supabase gen types typescript --project-id <production-id> > src/integrations/supabase/types.ts

# Verify types
npm run typecheck
```

***

## Quick Reference: MCP vs CLI

### Use MCP For:

* ✅ Listing branches, tables, migrations
* ✅ Searching documentation
* ✅ Getting project information
* ✅ Checking security/performance advisors
* ✅ Executing read-only SQL queries
* ✅ Verifying changes after CLI operations

### Use CLI For:

* ✅ Creating migrations (`supabase migration new`)
* ✅ Applying migrations (`supabase db push`)
* ✅ Generating types (`supabase gen types typescript`)
* ✅ Local development (`supabase start`)
* ✅ Creating branches (`supabase --experimental branches create`)
* ✅ Linking projects (`supabase link --project-ref`)

***

## Timeline Summary

| Phase                             | Duration  | MCP/CLI Split     |
| --------------------------------- | --------- | ----------------- |
| Phase 0: Pre-Setup Verification   | 30 min    | 70% MCP / 30% CLI |
| Phase 1: Staging Branch Setup     | 1-2 hours | 40% MCP / 60% CLI |
| Phase 2: Apply Migrations         | 2-3 hours | 30% MCP / 70% CLI |
| Phase 3: Run SQL Audits           | 2-3 hours | 80% MCP / 20% CLI |
| Phase 4: Create Fix Migrations    | 2-4 hours | 20% MCP / 80% CLI |
| Phase 5: Apply and Validate Fixes | 2-3 hours | 60% MCP / 40% CLI |
| Phase 6: Generate Types           | 30 min    | 20% MCP / 80% CLI |
| Phase 7: Application Testing      | 4-6 hours | 10% MCP / 90% CLI |
| Phase 8: Pre-Production Checklist | 2-4 hours | 50% MCP / 50% CLI |
| Phase 9: Production Migration     | 4-8 hours | 40% MCP / 60% CLI |

**Total Estimated Time:** 20-35 hours (3-5 days)

***

## Resources

### Documentation

* [MCP Usage Guide](../development/MCP_USAGE.md) - MCP vs CLI decision matrix
* [Staging Setup Guide](STAGING_SETUP_GUIDE.md) - Detailed staging setup
* [SQL Audit Instructions](SQL_AUDIT_INSTRUCTIONS.md) - Running audit scripts
* [Type Generation Guide](TYPE_GENERATION_GUIDE.md) - TypeScript type generation
* [Pre-Production Checklist](PRE_PRODUCTION_CHECKLIST.md) - Final validation checklist
* [Pre-Migration Test Plan](../testing/PRE_MIGRATION_TEST_PLAN.md) - Comprehensive data isolation and merge tests
* [Pre-Migration Checklist](../testing/PRE_MIGRATION_CHECKLIST.md) - Dashboard verification steps

### Scripts

* `scripts/lint-migrations.ts` - Static migration analysis
* `scripts/generate-migration-reports.ts` - Generate audit reports
* `scripts/audit-rls-policies.sql` - RLS audit script
* `scripts/audit-table-columns.sql` - Column audit script
* `scripts/audit-storage-buckets.sql` - Storage audit script
* `scripts/generate-types.sh` - Type generation helper

### Supabase Documentation

* [Branching Guide](https://supabase.com/docs/guides/deployment/branching)
* [Database Migrations](https://supabase.com/docs/guides/deployment/database-migrations)
* [Row Level Security](https://supabase.com/docs/guides/database/postgres/row-level-security)
* [Managing Environments](https://supabase.com/docs/guides/deployment/managing-environments)

***

**Last Updated:** 2026-01-15\
**Version:** 2.1.0\
**Status:** Ready for execution (MCP-First Approach)