Set up Local, Staging, and Production Supabase Environments

[samholmes]

Establish a proper Supabase environment setup with local development, staging, and production environments to enable safe migration testing and deployment workflows.

Current State

• supabase/config.toml exists and is configured for local development
• 19 migration files exist in supabase/migrations/
• Supabase CLI is in devDependencies (supabase@^2.58.5)
• README mentions Supabase but lacks detailed local setup instructions
• No documented staging environment
• Production environment exists but migration workflow is manual

Requirements

1. Local Development Environment

Setup:

• Document how to start local Supabase instance
• Document how to run migrations locally
• Document how to reset local database
• Document how to access Supabase Studio locally
• Create .env.local.example template file
• Update README with local Supabase setup instructions

Commands to document:

# Start local Supabase
supabase start

# Run migrations locally
supabase db reset

# Stop local Supabase
supabase stop

# Access Supabase Studio
# Should be available at http://127.0.0.1:54323 (per config.toml)

Environment Variables:

• Document required .env.local variables for local development
• Ensure local Supabase URLs/keys are clearly documented
• Add instructions for linking to local instance vs remote

2. Staging Environment

Setup:

• Create a separate Supabase project for staging
• Configure staging project with appropriate settings
• Document how to link local CLI to staging project
• Set up GitHub Actions workflow or script to deploy to staging
• Configure staging environment variables in GitHub secrets

Configuration:

• Staging should mirror production structure
• Use staging for testing migrations before production
• Consider automated deployment to staging on PR creation/updates

GitHub Secrets needed:

• STAGING_SUPABASE_PROJECT_ID
• STAGING_SUPABASE_ACCESS_TOKEN
• STAGING_SUPABASE_DB_PASSWORD (if needed)

3. Production Environment

Setup:

• Document production Supabase project details
• Ensure production credentials are in GitHub secrets
• Document migration deployment process
• Set up proper access controls and permissions

GitHub Secrets needed:

• SUPABASE_PROJECT_ID (production)
• SUPABASE_ACCESS_TOKEN (production)
• SUPABASE_DB_PASSWORD (production, if needed)

4. Environment Management

Documentation:

• Create docs/supabase-setup.md with comprehensive setup guide
• Document how to switch between environments
• Document migration workflow: local → staging → production
• Add troubleshooting section

Scripts/Tooling:

• Consider adding npm/bun scripts for common Supabase operations:

  "supabase:start": "supabase start",
  "supabase:stop": "supabase stop",
  "supabase:reset": "supabase db reset",
  "supabase:migrate:local": "supabase db push",
  "supabase:migrate:staging": "supabase db push --project-ref <staging-ref>",
  "supabase:migrate:prod": "supabase db push --project-ref <prod-ref>"

Implementation Considerations

1. Local Development:

   • Ensure Docker is installed (required for supabase start)
   • Document port conflicts and how to resolve them
   • Document how to seed local database with test data
   • Consider adding a seed script for local development

2. Staging Environment:

   • Should be created as a separate Supabase project
   • Consider using Supabase's free tier for staging
   • Set up automated testing of migrations in staging
   • Document how to sync staging data from production (if needed)

3. Production Environment:

   • Ensure proper backup strategy is documented
   • Document rollback procedures for failed migrations
   • Set up monitoring/alerting for migration failures
   • Consider using Supabase's migration history tracking

4. Security:

   • All production credentials must be in GitHub secrets
   • Never commit .env.local files
   • Document least-privilege access principles
   • Consider using Supabase's project linking feature

5. CI/CD Integration:

   • Staging migrations could run on PR creation
   • Production migrations should run only on merge to master
   • Consider adding migration validation step before deployment
   • Add migration status checks to PR checks

File Structure

After implementation, the project should have:

docs/
  supabase-setup.md          # Comprehensive setup guide
.env.local.example            # Template for local environment variables
supabase/
  config.toml                 # Already exists (local config)
  migrations/                 # Already exists
  seed.sql                    # Seed file (if needed)

Acceptance Criteria

• Local Supabase setup is fully documented and working
• Staging Supabase project is created and configured
• Production Supabase credentials are securely stored in GitHub secrets
• README updated with local development setup instructions
• docs/supabase-setup.md created with comprehensive guide
• .env.local.example template file created
• Migration workflow documented: local → staging → production
• All team members can successfully set up local environment
• Staging environment can be used to test migrations before production

Related Issues

• Include supabase db migration in the deploy workflow #57 - Include Supabase database migrations in the deploy workflow (depends on this issue)

Notes

• Supabase CLI requires Docker to be running for local development
• Local Supabase Studio is available at http://127.0.0.1:54323 (per supabase/config.toml)
• Consider using Supabase's supabase link command to connect to remote projects
• Migration files should be tested locally before pushing to staging/production
• Consider adding a pre-commit hook to validate migration SQL syntax

Resources

• Supabase Local Development Guide
• Supabase CLI Reference
• Supabase Migration Guide

---

This issue covers:

• Local, staging, and production setup
• Documentation requirements
• Security considerations
• CI/CD integration points
• Acceptance criteria
• Related issues

Copy this into a new GitHub issue.