# Provision Supabase Backend Task

Deploy database schema to live Supabase project using the vcsys-cli Supabase integration.

## Workflow Steps

### 1. Pre-Deployment Validation
**Objective:** Ensure readiness for production deployment

**Process:**
- Verify database schema file exists and is valid
- Validate SQL syntax and PostgreSQL compatibility
- Check for potential security vulnerabilities
- Confirm RLS policies are properly defined
- Review performance implications

**Interactive Validation:**
- Schema file path confirmation
- Project name and configuration review
- Environment selection (development/staging/production)
- Backup and rollback strategy confirmation

### 2. Supabase Authentication
**Objective:** Establish secure connection to Supabase platform

**CLI Commands:**
```bash
# IMPORTANT: Use command prefix determined by project detection in supabase-cli-guide.yaml
# Examples: "npx vcsys", "npm start -- vcsys", or "vcsys"

# Check authentication status
{detected_prefix} auth status

# Authenticate if needed  
{detected_prefix} auth login
```

**Process:**
- Verify Supabase CLI authentication
- Check organization access and permissions
- List available organizations for project creation
- Confirm billing and tier limitations

**Troubleshooting:**
- Clear tokens if authentication fails
- Verify Supabase account access
- Check API token permissions and scope

### 3. Project Provisioning
**Objective:** Create new Supabase project with proper configuration

**CLI Commands:**
```bash
# IMPORTANT: Use command prefix determined by project detection in supabase-cli-guide.yaml

# List organizations for selection
{detected_prefix} provision --list-orgs

# List available regions
{detected_prefix} provision --list-regions

# List schema templates  
{detected_prefix} provision --list-templates

# Direct provisioning with custom schema
{detected_prefix} provision \
  --org "Organization Name" \
  --name "project-name" \
  --region "us-east-1" \
  --schema "./path/to/schema.sql"

# OR template-based provisioning
{detected_prefix} provision \
  --org "Organization Name" \
  --name "project-name" \
  --region "us-east-1" \
  --template "saas-starter"
```

**Interactive Elicitation (elicit=true):**
- Project name (must be unique within organization)
- Organization selection from available orgs
- Region selection (us-east-1, eu-west-1, etc.)
- Database password (minimum 8 characters)
- Project tier and billing confirmation

**Process:**
- Create Supabase project via Management API
- Wait for project initialization (can take 2-3 minutes)
- Verify project is active and accessible
- Record project reference and connection details

### 4. Schema Deployment
**Objective:** Deploy database schema to live project

**CLI Commands:**
```bash
# Schema deployment is handled automatically during provisioning
# .env.local is automatically updated with credentials
```

**Process:**
- Upload and execute schema SQL file
- Enable Row Level Security on tables
- Create security policies and indexes
- Verify table creation and constraints
- Test authentication integration

**Validation Steps:**
- Confirm all tables created successfully
- Verify RLS policies are active and working
- Test sample data insertion and access
- Validate foreign key constraints
- Check index creation and performance

### 5. Environment Configuration
**Objective:** Generate and configure environment variables

**CLI Commands:**
```bash
# Environment variables are automatically generated and saved to .env.local
# during the provisioning process - no additional commands needed
```

**Generated Variables:**
```bash
NEXT_PUBLIC_SUPABASE_URL=https://<project-ref>.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=<anon-key>
SUPABASE_SERVICE_ROLE_KEY=<service-role-key>
SUPABASE_PROJECT_REF=<project-ref>
DATABASE_URL=postgresql://postgres:[PASSWORD]@db.<project-ref>.supabase.co:5432/postgres
```

**Process:**
- Retrieve project API keys (anon and service role)
- Generate connection strings and URLs
- Create environment file for development
- Document environment setup for team
- Provide production deployment guidance

### 6. Security and Access Verification
**Objective:** Verify security implementation and access controls

**Verification Steps:**
- Test Row Level Security policies with different users
- Verify authentication flows work correctly
- Check API key restrictions and permissions
- Test data access patterns match security model
- Validate audit logging if implemented

**Security Checklist:**
- ✅ RLS enabled on all user-facing tables
- ✅ Anonymous access properly restricted
- ✅ Service role key secured and not exposed
- ✅ API rate limiting configured
- ✅ Database password meets security requirements
- ✅ Backup and recovery procedures documented

### 7. Integration Testing
**Objective:** Verify end-to-end functionality and integration

**Testing Areas:**
- **Authentication:** User signup, login, session management
- **Data Operations:** CRUD operations through API
- **Security:** Access control and data isolation
- **Performance:** Response times and query efficiency
- **Real-time:** Subscriptions and live updates (if used)

**CLI Testing Commands:**
```bash
# Check authentication status
{detected_prefix} auth status

# List provisioned projects
{detected_prefix} provision --list-orgs
```

### 8. Documentation and Handoff
**Objective:** Provide comprehensive deployment documentation

**Documentation Generated:**
- Project deployment summary
- API endpoint documentation
- Environment setup instructions
- Security implementation guide
- Troubleshooting and monitoring procedures

**Handoff Artifacts:**
- Live project URL and admin dashboard access
- Environment configuration file
- Schema documentation and ERD
- Security policy documentation
- Integration examples and code snippets

## Success Criteria

- ✅ Supabase project successfully created and active
- ✅ Database schema deployed without errors
- ✅ Row Level Security policies active and tested
- ✅ Environment variables generated and documented
- ✅ Authentication integration working correctly
- ✅ API endpoints accessible and secure
- ✅ Performance within acceptable thresholds
- ✅ Complete documentation provided for team

## Error Recovery

**Common Issues and Solutions:**

1. **Authentication Failures:**
   - Clear stored tokens and re-authenticate
   - Verify Supabase account and organization access
   - Check API token permissions

2. **Schema Deployment Errors:**
   - Validate SQL syntax and PostgreSQL compatibility
   - Check for naming conflicts with reserved words
   - Verify constraint and relationship definitions

3. **Performance Issues:**
   - Review index creation and query patterns
   - Check for missing or inefficient indexes
   - Validate data types and constraints

4. **Security Problems:**
   - Verify RLS policy syntax and logic
   - Test policies with different user contexts
   - Check authentication integration points

## Integration Points

- **Previous Step:** draft-database-schema.md for schema generation
- **Next Steps:** 
  - database-testing.md for comprehensive testing
  - database-optimization.md for performance tuning
  - database-documentation.md for API and integration docs
- **Templates:** supabase-config-tmpl.yaml, environment-setup-tmpl.yaml
- **CLI Tools:** All vcsys provision and auth commands for project management