# Provision Supabase Local Development Environment Task

Deploy database schema to local Supabase development environment for rapid development and testing.

## Workflow Steps

### 1. Prerequisites Validation
**Objective:** Ensure system readiness for local Supabase development

**System Requirements Check:**
- **Memory:** 4GB RAM minimum, 8GB recommended
- **Storage:** 5GB free disk space for Docker images and data  
- **Network:** Internet connection for image downloads (one-time)
- **Node.js:** Version 20.19.4 (verified stable)
- **Docker:** Compatible container runtime available

**Interactive Validation (elicit=true):**
- Docker installation confirmation (Docker Desktop, Docker Engine, Rancher Desktop, Podman, OrbStack)
- Node.js version manager availability (nvm, n, fnm)
- Available memory allocation for Docker (minimum 4GB)
- Available disk space confirmation (minimum 5GB)

**Pre-flight System Check:**
```bash
# Check Docker availability and status
docker --version && docker info

# Check Node.js version manager
command -v nvm || [ -s "$HOME/.nvm/nvm.sh" ] || command -v n || command -v fnm

# Check available disk space
df -h . | tail -1 | awk '{print $4}' 

# Check for port conflicts on Supabase default ports
lsof -i :54321,54322,54323,54324,54325,54326,54327,54329 || netstat -an | grep -E ":(54321|54322|54323|54324|54325|54326|54327|54329) "
```

**Validation Results:**
- ✅ All prerequisites met → Proceed to environment setup
- ❌ Missing requirements → Guide user through installation process
- ⚠️ Port conflicts detected → Provide resolution guidance

### 2. Environment Setup and Configuration
**Objective:** Prepare Node.js and Docker environment for Supabase local development

**Node.js Environment Setup:**
```bash
# Source NVM if available
if [ -s "$HOME/.nvm/nvm.sh" ]; then
    source "$HOME/.nvm/nvm.sh"
fi

# Install and activate Node.js 20.19.4 (verified stable)
if ! nvm list | grep -q "v20.19.4"; then
    nvm install 20.19.4
fi

nvm use 20.19.4
nvm alias default 20.19.4

echo "✅ Node.js $(node --version) active"
```

**Docker Environment Preparation:**
```bash
# Check for existing Supabase containers and clean if needed
docker stop $(docker ps --filter "name=supabase" -q) 2>/dev/null || true
docker rm $(docker ps --filter "name=supabase" -aq) 2>/dev/null || true

# Check Docker memory allocation
docker system info --format '{{.MemTotal}}' 2>/dev/null
```

**Port Conflict Resolution:**
- Detect conflicts on ports: 54321, 54322, 54323, 54324, 54325, 54326, 54327, 54329
- Stop conflicting services or modify config.toml if needed
- Provide alternative configuration options

### 3. Supabase CLI Installation and Management
**Objective:** Install appropriate Supabase CLI version with compatibility assurance

**Progressive Installation Strategy:**
1. **Primary:** Install latest Supabase CLI
2. **Fallback:** Use stable version 2.32.0 if compatibility issues detected
3. **Validation:** Test CLI functionality before proceeding

**CLI Installation Commands:**
```bash
# Initialize package.json if it doesn't exist
if [ ! -f "package.json" ]; then
    npm init -y
fi

# Install latest Supabase CLI
npm install supabase --save-dev

# Get installed version for validation
npx supabase --version
```

**Compatibility Testing and Rollback:**
```bash
# Test CLI with timeout to detect issues
timeout 120 npx supabase start --debug 2>&1 | tee /tmp/supabase-start.log

# If Node.js syntax errors detected, rollback to stable version
if grep -q "SyntaxError.*Invalid or unexpected token" /tmp/supabase-start.log; then
    echo "🔧 Node.js compatibility issue detected - rolling back to stable version"
    npm uninstall supabase
    npm install supabase@2.32.0 --save-dev
fi
```

**CLI Version Matrix (from testing):**
- **Latest (Try First):** Current version with Node.js 20 compatibility
- **Stable Fallback:** v2.32.0 - Verified stable with PostgreSQL 17 and Node.js 20.19.4
- **Emergency Fallback:** v2.30.x - Alternative stable option

### 4. Project Initialization and Configuration
**Objective:** Initialize Supabase project with optimized local development settings

**Interactive Project Setup (elicit=true):**
- Project directory confirmation (current working directory or specify)
- Project ID specification (defaults to directory name)
- Custom configuration preferences
- Schema deployment method selection

**Project Initialization:**
```bash
# Initialize Supabase project in current directory
npx supabase init

# Configure project ID in config.toml
sed -i "s/project_id = .*/project_id = \"$PROJECT_ID\"/" supabase/config.toml
```

**Optimized Configuration (supabase/config.toml):**
```toml
[api]
enabled = true
port = 54321
schemas = ["public", "graphql_public"]
extra_search_path = ["public", "extensions"]
max_rows = 1000

[db]
port = 54322
shadow_port = 54320
major_version = 17

[db.pooler]
enabled = false
port = 54329
pool_mode = "transaction"
default_pool_size = 20
max_client_conn = 100

[realtime]
enabled = true

[studio]
enabled = true
port = 54323
api_url = "http://127.0.0.1"

[inbucket]
enabled = true
port = 54324

[storage]
enabled = true
file_size_limit = "50MiB"

[auth]
enabled = true
site_url = "http://127.0.0.1:3000"
additional_redirect_urls = ["https://127.0.0.1:3000"]
jwt_expiry = 3600
enable_refresh_token_rotation = true
refresh_token_reuse_interval = 10
enable_signup = true
enable_anonymous_sign_ins = false

[auth.email]
enable_signup = true
double_confirm_changes = true
enable_confirmations = false

[edge_runtime]
enabled = true
policy = "oneshot"
inspector_port = 8083

[analytics]
enabled = true
port = 54327
backend = "postgres"
```

### 5. Schema Deployment to Local Environment
**Objective:** Deploy database schema to local Supabase instance

**Schema Detection and Deployment:**
- Check for existing schema files in project (.vcsys/generated-schema.sql)
- If custom schema exists, deploy to local instance
- If no schema exists, offer schema generation first
- Support for RLS policies and security configurations

**Local Schema Deployment Process:**
```bash
# Start Supabase local services
npx supabase start

# Wait for services to become healthy
# Health check with timeout
timeout 300 bash -c 'until curl -f -s http://127.0.0.1:54321/health >/dev/null 2>&1; do sleep 5; done'

# Deploy schema if available
if [ -f ".vcsys/generated-schema.sql" ]; then
    # Apply schema to local database
    npx supabase db reset --db-url "postgresql://postgres:postgres@127.0.0.1:54322/postgres"
    
    # Execute custom schema
    psql -h 127.0.0.1 -p 54322 -U postgres -d postgres -f ".vcsys/generated-schema.sql"
fi
```

**Schema Validation Steps:**
- Verify all tables created successfully
- Confirm RLS policies are active
- Test constraints and relationships
- Validate indexes and performance
- Check authentication integration

### 6. Service Startup and Health Monitoring
**Objective:** Start all Supabase services and verify healthy operation

**Service Startup Sequence:**
```bash
# Start with debugging enabled for troubleshooting
npx supabase start --debug

# Monitor container health
docker ps --filter "name=supabase" --format "table {{.Names}}\t{{.Status}}\t{{.Ports}}"
```

**Health Monitoring and Validation:**
```bash
# Wait for all services to become healthy
wait_for_healthy_services() {
    local services=(
        "http://127.0.0.1:54321/health:API"
        "http://127.0.0.1:54323:Studio"  
        "http://127.0.0.1:54324:Inbucket"
        "http://127.0.0.1:54322:Database"
    )
    
    for service in "${services[@]}"; do
        local url=$(echo $service | cut -d: -f1-3)
        local name=$(echo $service | cut -d: -f4)
        
        if curl -f -s --max-time 5 "$url" >/dev/null 2>&1; then
            echo "✅ $name: Healthy"
        else
            echo "❌ $name: Unhealthy - checking logs..."
            return 1
        fi
    done
}
```

**Service Recovery Strategies:**
- **Soft restart:** `npx supabase stop && npx supabase start`
- **Hard reset with data preservation:** Stop containers, clean, restart
- **Complete reset:** Full cleanup including volumes
- **CLI rollback:** Switch to stable version if issues persist

### 7. Environment Variables and Integration Setup
**Objective:** Generate environment variables for application integration

**Local Environment Variables Generation:**
```bash
# Extract connection details from Supabase status
SUPABASE_STATUS=$(npx supabase status)

# Parse values for environment file
API_URL=$(echo "$SUPABASE_STATUS" | grep "API URL" | awk '{print $3}')
DB_URL=$(echo "$SUPABASE_STATUS" | grep "DB URL" | awk '{print $3}')  
ANON_KEY=$(echo "$SUPABASE_STATUS" | grep "anon key" | awk '{print $3}')
SERVICE_ROLE_KEY=$(echo "$SUPABASE_STATUS" | grep "service_role key" | awk '{print $3}')
STUDIO_URL=$(echo "$SUPABASE_STATUS" | grep "Studio URL" | awk '{print $3}')
```

**Generated .env.local File:**
```bash
# Supabase Local Development Environment Variables
# Generated by Database Wizard Local Provisioning

# API Configuration
NEXT_PUBLIC_SUPABASE_URL=$API_URL
NEXT_PUBLIC_SUPABASE_ANON_KEY=$ANON_KEY  
SUPABASE_SERVICE_ROLE_KEY=$SERVICE_ROLE_KEY

# Database Connection
DATABASE_URL=$DB_URL

# Development Tools
SUPABASE_STUDIO_URL=$STUDIO_URL

# Local Development Indicators
SUPABASE_ENVIRONMENT=local
SUPABASE_LOCAL_MODE=true

# Standard JWT Secret for local development
SUPABASE_JWT_SECRET=super-secret-jwt-token-with-at-least-32-characters-long
```

### 8. Validation and Testing Suite
**Objective:** Comprehensive validation of local Supabase environment

**Automated Validation Tests:**
```bash
# Test 1: CLI Responsiveness
npx supabase --version

# Test 2: Status Command
npx supabase status

# Test 3: Database Connection
pg_isready -h 127.0.0.1 -p 54322 -U postgres

# Test 4: API Endpoint
curl -f -s "http://127.0.0.1:54321/health"

# Test 5: Studio Accessibility  
curl -f -s "http://127.0.0.1:54323"

# Test 6: Storage Service
curl -f -s "http://127.0.0.1:54321/storage/v1/bucket"

# Test 7: Auth Service
curl -f -s "http://127.0.0.1:54321/auth/v1/settings"

# Test 8: Environment Variables
grep -q "NEXT_PUBLIC_SUPABASE_URL" .env.local
```

**Success Criteria:**
- ✅ All 8 validation tests pass
- ✅ Studio accessible at http://127.0.0.1:54323
- ✅ API endpoints responsive
- ✅ Database connection established
- ✅ Environment variables generated
- ✅ Schema deployed successfully (if provided)

### 9. Documentation and Handoff
**Objective:** Provide comprehensive local development documentation

**Generated Documentation:**
- Local development setup summary
- Available services and their URLs
- Environment variable configuration
- Development workflow guidance
- Troubleshooting and recovery procedures

**Connection Information Summary:**
```
🎉 Supabase Local Development Environment Ready!

Services Available:
- Studio UI: http://127.0.0.1:54323
- API Endpoint: http://127.0.0.1:54321  
- Database: postgresql://postgres:postgres@127.0.0.1:54322/postgres
- Inbucket (Email): http://127.0.0.1:54324

Useful Commands:
- npx supabase status    # Check service status
- npx supabase stop      # Stop all services  
- npx supabase start     # Start all services
- npx supabase db reset  # Reset database with migrations

Next Steps:
1. Open Studio UI for database management
2. Configure your application with .env.local variables  
3. Start your application development!
```

## Success Criteria

- ✅ Local Supabase environment successfully initialized
- ✅ All services running and healthy
- ✅ Database schema deployed (if provided)
- ✅ Environment variables configured for integration
- ✅ Studio UI accessible for database management
- ✅ All validation tests passing
- ✅ Development workflow documentation provided
- ✅ Troubleshooting guidance available

## Error Recovery

**Common Issues and Solutions:**

1. **Docker Not Running:**
   - Start Docker Desktop or Docker service
   - Verify Docker daemon is accessible
   - Check Docker memory allocation (minimum 4GB)

2. **Port Conflicts:**
   - Identify conflicting services using lsof or netstat
   - Stop conflicting services or modify supabase/config.toml
   - Use alternative port configuration

3. **Node.js Compatibility Issues:**
   - Switch to Node.js 20.19.4 using version manager
   - Rollback to Supabase CLI v2.32.0 if needed
   - Clear npm cache and reinstall CLI

4. **Container Health Issues:**
   - Check Docker logs for specific errors
   - Restart with container cleanup
   - Verify system resources (memory, disk space)
   - Reset with volume cleanup if database issues

5. **Schema Deployment Failures:**
   - Validate SQL syntax and PostgreSQL compatibility
   - Check for naming conflicts with reserved words
   - Verify RLS policy syntax and references
   - Use psql for manual schema debugging

## Integration Points

- **Previous Step:** draft-database-schema.md for schema generation (optional)
- **Alternative to:** provision-supabase-backend.md (cloud deployment)
- **Next Steps:** 
  - database-testing.md for comprehensive testing
  - database-documentation.md for local API documentation
  - Application development with local backend
- **Templates:** supabase-config-tmpl.yaml for local configuration
- **Environment:** .env.local for application integration

## Performance and Resource Management

**Resource Requirements:**
- **Memory:** 4GB minimum allocated to Docker
- **CPU:** 2+ cores recommended for smooth operation
- **Storage:** 5GB for images and data
- **Network:** Initial setup requires internet for image downloads

**Performance Optimization:**
- Use Node.js 20.19.4 for optimal compatibility
- Enable Docker BuildKit for faster image builds
- Configure appropriate connection pool sizes
- Monitor resource usage during development

**Development Workflow Integration:**
```json
{
  "scripts": {
    "supabase:local:start": "npx supabase start",
    "supabase:local:stop": "npx supabase stop", 
    "supabase:local:status": "npx supabase status",
    "supabase:local:reset": "npx supabase db reset",
    "supabase:local:studio": "open http://127.0.0.1:54323"
  }
}
```