Supabase Migration Completion Plan

// Get project URL to verify connection
mcp_supabase_get_project_url()

// List branches to see available environments
mcp_supabase_list_branches()

# 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

// 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
      }
    }
  }`
})

# 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

// List all branches
mcp_supabase_list_branches()

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

// List branches again to confirm
mcp_supabase_list_branches()

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

# Verify link
supabase status

# Run static analysis script
npx ts-node scripts/lint-migrations.ts

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

// List migrations applied to database
mcp_supabase_list_migrations()

# Ensure linked to staging
supabase status

# Push all migrations
supabase db push

# Monitor output for errors

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

// Get security advisors
mcp_supabase_get_advisors({ type: "security" })

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

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

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');
  `
})

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

// 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
      }
    }
  }`
})

# 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

# Create migration file
supabase migration new fix_storage_bucket_names

# Edit with rename statements

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

# Create migration file
supabase migration new fix_missing_audit_columns

# Edit with ADD COLUMN statements from audit

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

# Ensure linked to staging
supabase status

# Apply new migrations
supabase db push

# Check for errors

// List migrations to confirm
mcp_supabase_list_migrations()

// 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 '%-%';
  `
})

// Check for remaining security issues
mcp_supabase_get_advisors({ type: "security" })

# 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

// Generate types via MCP
mcp_supabase_generate_typescript_types()

# Run TypeScript type check
npm run typecheck

# Expected: 0 errors

# Create .env.staging file
VITE_SUPABASE_URL=<staging-branch-url>
VITE_SUPABASE_ANON_KEY=<staging-anon-key>

# Start application in staging mode
$env:VITE_SUPABASE_URL="<staging-url>"; npm run dev

# Run test suite
npm run test

// 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" })

# Generate production types
supabase gen types typescript --project-id <production-id> > src/integrations/supabase/types.ts

# Verify types
npm run typecheck