Include supabase db migration in the deploy workflow

[samholmes]

Add automatic database migration deployment to the CI/CD pipeline so that schema changes are automatically applied during production deployments.

Current State

• Supabase migrations exist in supabase/migrations/ (19 migration files)
• Migrations are currently applied manually
• Deploy workflow (.github/workflows/deploy.yml) builds and deploys to GitHub Pages but doesn't run migrations
• Supabase CLI is already in devDependencies (supabase@^2.58.5)

Requirements

1. Add Migration Step to Deploy Workflow

Add a step in .github/workflows/deploy.yml in the build job (before or after the Next.js build) to run:

npx supabase db push --include-all

This should:

• Run only when migrations have changed (optimize with conditional step)
• Use production Supabase credentials from GitHub secrets
• Fail the build if migrations fail

2. Required Secrets/Configuration

Add to GitHub repository secrets:

• SUPABASE_DB_PASSWORD - Database password for production
• SUPABASE_ACCESS_TOKEN - Supabase access token (if using supabase link)
• Or configure SUPABASE_PROJECT_ID and SUPABASE_DB_URL for direct connection

3. Prerequisites (Blockers)

Before implementing, we need:

• Local development setup: Document how to run Supabase locally for testing migrations
• Staging environment: Consider a staging Supabase project to test migrations before production (separate issue)
• Migration testing: Ensure migrations can be tested locally before deployment

Implementation Considerations

1. When to run migrations:

   • Option A: Run on every deploy (simpler, but slower)
   • Option B: Only run when supabase/migrations/ changes (requires paths filter)

2. Error handling:

   • Should migrations run before or after the build?
   • How to handle failed migrations (rollback strategy)?

3. Security:

   • Use GitHub secrets for all Supabase credentials
   • Ensure database connection uses SSL/TLS

4. Performance:

   • Cache Supabase CLI installation if possible
   • Consider migration time impact on deploy duration

Acceptance Criteria

• Migrations run automatically during deployment
• Failed migrations fail the deployment
• Production Supabase credentials are stored securely in GitHub secrets
• Documentation updated with local development setup instructions
• Migration step doesn't significantly slow down deployments

Related Issues

• Create separate issue for staging environment setup
• Create separate issue for local Supabase development environment documentation

Notes

• The --include-all flag pushes all migrations, including seed data
• Consider using supabase db push without --include-all if seed data should be separate
• May want to add a dry-run or validation step before actual push

---

Copy this into issue #57. The requirements section references .github/workflows/deploy.yml as the file to modify.