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

# Pre-Production Migration Checklist

> Version: 2.0.0 Last Updated: 2026-01-15 Purpose: Final validation checklist before migrating to Supabase Cloud production

**Version:** 2.0.0\
**Last Updated:** 2026-01-15\
**Purpose:** Final validation checklist before migrating to Supabase Cloud production

***

## Phase 0: Setup Verification

> **Purpose:** Verify all tools and environments are correctly configured before proceeding.

### MCP Connection

* [ ] MCP server is running and responsive
* [ ] `mcp_supabase_get_project_url()` returns valid URL
* [ ] `mcp_supabase_list_branches()` shows expected branches

**Verification Command (MCP):**

```typescript theme={null}
mcp_supabase_get_project_url()
// Expected: https://<project-ref>.supabase.co
```

### CLI Installation

* [ ] Supabase CLI installed (`supabase --version` returns version)
* [ ] CLI version is 1.0.0 or higher
* [ ] CLI is authenticated (`supabase login` completed)

**Verification Command (CLI):**

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

### Project Linking

* [ ] Local project linked to Supabase (`supabase status` shows project)
* [ ] Correct project ID in link
* [ ] Correct environment (staging vs production)

**Verification Command (CLI):**

```bash theme={null}
supabase status
# Expected: Shows linked project details
```

### Migration Files

* [ ] Migration files exist in `supabase/migrations/`
* [ ] 393+ migration files present
* [ ] Files are properly named with timestamps

**Verification Command (CLI):**

```bash theme={null}
Get-ChildItem "supabase/migrations/*.sql" | Measure-Object
# Expected: Count >= 393
```

### GitHub Integration (if applicable)

* [ ] Repository connected to Supabase
* [ ] Branch mapping configured (staging Git → staging Supabase)
* [ ] Auto-deploy enabled for migrations

**Verification:** Check Supabase Dashboard → Settings → Integrations → GitHub

***

## Phase 1: Staging Validation

### Database Schema

**Expected Counts:**

| Metric             | Expected | Verification                     |
| ------------------ | -------- | -------------------------------- |
| Total Tables       | \~465    | `mcp_supabase_list_tables()`     |
| Migrations Applied | 393+     | `mcp_supabase_list_migrations()` |

**Verification Commands:**

**MCP:**

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

// List applied migrations
mcp_supabase_list_migrations()
```

**CLI:**

```bash theme={null}
# Check migration count
supabase migration list | Measure-Object -Line
```

### Table Counts by Module

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

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT 
      CASE 
        WHEN table_name LIKE 'pf_%' THEN 'PF'
        WHEN table_name LIKE 'hr_%' THEN 'HR'
        WHEN table_name LIKE 'fa_%' THEN 'FA'
        WHEN table_name LIKE 'fw_%' THEN 'FW'
        WHEN table_name LIKE 'rh_%' THEN 'RH'
        WHEN table_name LIKE 'gr_%' THEN 'GR'
        WHEN table_name LIKE 'fm_%' THEN 'FM'
        WHEN table_name LIKE 'lo_%' THEN 'LO'
        WHEN table_name LIKE 'it_%' THEN 'IT'
        ELSE 'Other'
      END as module,
      COUNT(*) as table_count
    FROM information_schema.tables
    WHERE table_schema = 'public'
    GROUP BY module
    ORDER BY module;
  `
})
```

***

## Phase 2: RLS Policy Validation

### Critical: UPDATE Policies WITH CHECK

* [ ] **0 UPDATE policies missing WITH CHECK clause** (P0 requirement)
* [ ] All policies use SECURITY DEFINER helper functions
* [ ] Multi-tenant isolation verified with test accounts
* [ ] Cross-organization data access blocked (tested)

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) as missing_with_check_count
    FROM pg_policies
    WHERE schemaname = 'public' 
      AND cmd = 'UPDATE' 
      AND with_check IS NULL;
  `
})
// Expected: 0
```

**Detailed Check:**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT tablename, policyname
    FROM pg_policies
    WHERE schemaname = 'public' 
      AND cmd = 'UPDATE' 
      AND with_check IS NULL
    ORDER BY tablename;
  `
})
// Expected: Empty result set
```

### RLS Coverage

* [ ] All business tables have RLS enabled
* [ ] RLS policies cover SELECT, INSERT, UPDATE, DELETE

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT c.relname as table_name, c.relrowsecurity as rls_enabled
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' 
      AND c.relkind = 'r'
      AND c.relrowsecurity = false
      AND c.relname NOT LIKE 'pg_%';
  `
})
// Expected: Minimal tables without RLS (only system tables)
```

***

## Phase 3: Column Compliance

### Audit Columns

* [ ] All business tables have `created_at` and `updated_at`
* [ ] Tables with `created_by` also have `updated_by` (**P1**)
* [ ] `custom_fields` present on all business entities (where applicable)

**Verification Query (MCP):**

```typescript theme={null}
// Check for missing updated_by where created_by exists
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) as missing_updated_by_count
    FROM (
      SELECT table_name
      FROM information_schema.columns
      WHERE table_schema = 'public' AND column_name = 'created_by'
      EXCEPT
      SELECT table_name
      FROM information_schema.columns
      WHERE table_schema = 'public' AND column_name = 'updated_by'
    ) t;
  `
})
// Expected: 0
```

### Organization ID Column

* [ ] All multi-tenant tables have `organization_id`
* [ ] `organization_id` is NOT NULL where required
* [ ] Foreign key to `pf_organizations` exists

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT table_name
    FROM information_schema.tables t
    WHERE table_schema = 'public'
      AND table_type = 'BASE TABLE'
      AND table_name LIKE '%_%'
      AND NOT EXISTS (
        SELECT 1 FROM information_schema.columns c
        WHERE c.table_schema = t.table_schema
          AND c.table_name = t.table_name
          AND c.column_name = 'organization_id'
      )
      AND table_name NOT IN (
        -- Exclude tables that don't need organization_id
        'pf_organizations', 'pf_platforms', 'pf_system_settings'
      )
    ORDER BY table_name;
  `
})
```

***

## Phase 4: Storage Bucket Validation

### Naming Convention

* [ ] **0 buckets with non-compliant names** (should follow `{core}-{purpose}`)
* [ ] All renamed buckets: `avatars` → `pf-avatars`, etc.

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT name, public, created_at
    FROM storage.buckets
    WHERE name NOT LIKE '%-%'
    ORDER BY name;
  `
})
// Expected: Empty result set
```

### Storage RLS

* [ ] RLS policies on storage.objects configured
* [ ] Bucket permissions verified
* [ ] File uploads/downloads work

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) as policy_count
    FROM pg_policies
    WHERE schemaname = 'storage' AND tablename = 'objects';
  `
})
// Expected: > 0
```

***

## Phase 5: Database Functions

### SECURITY DEFINER Functions

* [ ] All SECURITY DEFINER functions have `SET search_path = public`
* [ ] Function naming follows `{core}_{action}_{entity}()` pattern
* [ ] Functions tested and working correctly

**Verification Query (MCP):**

```typescript theme={null}
mcp_supabase_execute_sql({
  query: `
    SELECT proname, prosecdef, proconfig
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public'
      AND prosecdef = true
      AND (proconfig IS NULL OR NOT 'search_path=public' = ANY(proconfig))
    ORDER BY proname;
  `
})
// Expected: Empty result set (all SECURITY DEFINER functions have search_path)
```

***

## Phase 6: TypeScript Types

### Type Generation

* [ ] Types generated from staging database
* [ ] `npm run typecheck` passes with 0 errors
* [ ] All table definitions present in types file
* [ ] No `[_ in never]: never` empty type definitions

**Verification Commands:**

**CLI:**

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

# Check file size (should be substantial)
(Get-Item "src/integrations/supabase/types.ts").Length
# Expected: > 100KB

# Run type check
npm run typecheck
# Expected: 0 errors
```

**MCP (Alternative):**

```typescript theme={null}
mcp_supabase_generate_typescript_types()
```

***

## Phase 7: Security Advisors

### Get Advisors

* [ ] Security advisors reviewed
* [ ] Performance advisors reviewed
* [ ] All critical issues addressed

**Verification (MCP):**

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

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

**Expected:** No critical or high-severity issues remaining.

***

## Phase 8: Application Testing

### Authentication

* [ ] User login/logout works
* [ ] Session refresh works
* [ ] Password reset works
* [ ] OAuth providers configured (if applicable)

### Multi-Tenant Isolation

* [ ] Users can only access their organization's data
* [ ] Cross-organization queries return empty results
* [ ] RLS policies prevent unauthorized access
* [ ] Test with multiple organizations and users

### Critical Flows

* [ ] Data creation (INSERT) works correctly
* [ ] Data updates (UPDATE) respect RLS policies
* [ ] Data deletion (DELETE) works correctly
* [ ] Soft deletes work where implemented

### Edge Functions

* [ ] All edge functions deployed to staging
* [ ] Edge functions can access database
* [ ] Edge functions respect RLS policies
* [ ] Error handling works correctly

### Storage Operations

* [ ] File uploads work (with new bucket names)
* [ ] File downloads work
* [ ] File deletions work
* [ ] Storage RLS policies enforced

***

## Phase 9: Performance Validation

### Query Performance

* [ ] No queries taking > 5 seconds
* [ ] Indexes present on foreign keys
* [ ] Indexes present on frequently queried columns
* [ ] No N+1 query patterns

### Application Performance

* [ ] Page load times acceptable (\< 3s)
* [ ] API response times acceptable (\< 1s)
* [ ] No memory leaks
* [ ] No excessive database connections

***

## Phase 10: Final Sign-Off

### Documentation Updated

* [ ] `AGENTS.md` updated with Supabase MCP usage patterns
* [ ] `docs/integrations/SUPABASE_SETUP.md` has branching setup
* [ ] `docs/development/MCP_USAGE.md` complete
* [ ] Migration completion plan updated

### Team Review

* [ ] Database team reviewed and approved
* [ ] Security team reviewed and approved
* [ ] Application team reviewed and approved
* [ ] Product team reviewed and approved

### Go/No-Go Decision

* [ ] All P0 issues resolved
* [ ] All P1 issues resolved or documented
* [ ] Staging validation complete
* [ ] Team ready for migration

***

## Migration Day Checklist

### Pre-Migration

* [ ] Final backup of Lovable database
* [ ] Maintenance mode enabled
* [ ] All users logged out
* [ ] Final data export completed

### During Migration

* [ ] Supabase project created/configured
* [ ] Migrations applied (`supabase db push`)
* [ ] Data imported
* [ ] Edge functions deployed
* [ ] Environment variables configured
* [ ] DNS/URL updated

### Post-Migration Validation

**Use MCP:**

```typescript theme={null}
// Verify tables
mcp_supabase_list_tables({ schemas: ["public"] })

// Check migrations
mcp_supabase_list_migrations()

// Security check
mcp_supabase_get_advisors({ type: "security" })
```

**Use CLI:**

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

# Type check
npm run typecheck
```

* [ ] All migrations applied successfully
* [ ] Data import complete (row counts match)
* [ ] TypeScript types generated and validated
* [ ] Application connects successfully
* [ ] Critical flows tested
* [ ] Multi-tenant isolation verified
* [ ] Performance validated
* [ ] Monitoring confirmed working

***

## Success Criteria Summary

| Criterion                       | Target     | Verification                      |
| ------------------------------- | ---------- | --------------------------------- |
| Migrations Applied              | 393+       | `mcp_supabase_list_migrations()`  |
| Tables Created                  | \~465      | `mcp_supabase_list_tables()`      |
| UPDATE Policies with WITH CHECK | 100%       | SQL query returns 0 missing       |
| Compliant Storage Buckets       | 100%       | SQL query returns 0 non-compliant |
| TypeScript Type Check           | 0 errors   | `npm run typecheck`               |
| Security Advisors               | 0 critical | `mcp_supabase_get_advisors()`     |
| Application Tests               | All pass   | `npm run test`                    |

***

**Last Updated:** 2026-01-15\
**Version:** 2.0.0\
**Next Review:** After staging validation complete