# CRITICAL DATABASE MIGRATION & DEPLOYMENT ANALYSIS
## RateRight Production Deployment Strategy

---

## EXECUTIVE SUMMARY ⚠️

**CRITICAL FINDINGS:**
- 6 new database migrations add essential rating system functionality
- Application code has HARD DEPENDENCIES on new database columns  
- Backward compatibility is partially implemented but DEPLOYMENT ORDER IS CRITICAL
- Feature flag system exists but rating system is NOT feature-flagged
- Safe deployment requires specific sequence to avoid production outages

**RECOMMENDED DEPLOYMENT SEQUENCE:** Database-first approach with staged rollout

---

## 1. CURRENT PRODUCTION DATABASE STATE ANALYSIS

### Production Database Schema Requirements:
```sql
-- MISSING IN PRODUCTION (added by migrations):
ALTER TABLE contracts ADD contractor_rated BOOLEAN DEFAULT FALSE NOT NULL;
ALTER TABLE contracts ADD worker_rated BOOLEAN DEFAULT FALSE NOT NULL;  
ALTER TABLE contracts ADD mutual_rating_completed_date DATETIME NULL;
ALTER TABLE jobs ADD risk_level VARCHAR(20) NULL;
```

### Application Dependencies on New Schema:
- **routes.py**: Rating system directly uses `contract.contractor_rated`, `contract.worker_rated`
- **contract.py**: Models define rating completion methods using these fields
- **Multiple services**: Risk assessment uses `job.risk_level` and `category.whs_risk_level`

---

## 2. MIGRATION ANALYSIS & DEPENDENCIES

### Migration Sequence & Dependencies:
```
c5e9c4fdd34a (base) → dc3c13ef2107 (rating fields) → fix_risk_level → 95c567b6260d (merge) → c99a67c4ee83 (rating + risk) → 7b3e164dde26 (final merge)
```

### Critical Migration Details:

#### Migration 1: `c5e9c4fdd34a_align_models_with_existing_database_.py`
- **Action**: Removes `risk_level` column from jobs table
- **Risk**: HIGH - breaks existing functionality if deployed alone
- **Dependencies**: Must be followed by fix_risk_level migration

#### Migration 2: `dc3c13ef2107_add_rating_fields_to_contracts_table.py`  
- **Action**: Adds rating tracking columns to contracts
- **Backward Compatible**: YES - checks column existence first
- **New Columns**: `contractor_rated`, `worker_rated`, `mutual_rating_completed_date`

#### Migration 3: `fix_risk_level_column.py`
- **Action**: Re-adds `risk_level` column with correct type  
- **Risk**: MEDIUM - temporarily removes then re-adds column
- **Critical**: Must run immediately after c5e9c4fdd34a

#### Migration 4-6: Merge migrations
- **Action**: Resolve migration conflicts (empty operations)
- **Risk**: LOW - no schema changes

### Backward Compatibility Assessment:
✅ **Compatible**: Rating columns have safe defaults and existence checks  
⚠️ **Risk Period**: Brief window where risk_level column is missing  
❌ **Hard Dependencies**: App code will fail without new rating columns

---

## 3. FEATURE DEPENDENCY MAPPING

### Hard Dependencies (NO Feature Flags):
```python
# routes.py - Rating System (ALWAYS ACTIVE)
if current_user.id == contract.contractor_id:
    contract.contractor_rated = True
else:
    contract.worker_rated = True

if contract.contractor_rated and contract.worker_rated:
    contract.status = 'completed'
    contract.mutual_rating_completed_date = datetime.utcnow()
```

### Feature Flagged Dependencies:
```python
# config.py - Payments (DISABLED)
FEATURES = {
    "gamification_leaderboards": True,
    "payments": False,  # Safe to deploy without Stripe
}
```

### Risk Assessment Features:
```python
# AI Service - Risk Level Assessment (ACTIVE)
whs_requirements=f"Category: {category.name}. Risk Level: {category.whs_risk_level}."
```

---

## 4. SAFE DEPLOYMENT STRATEGY

### ⚠️ DEPLOYMENT SEQUENCE (CRITICAL ORDER):

#### Phase 1: Database Migration (FIRST)
```bash
# 1. Backup production database
fly postgres backup --app your-db-name

# 2. Apply ALL migrations in sequence 
fly ssh console --app rateright
export FLASK_APP=run.py
flask db upgrade

# 3. Verify schema changes
psql $DATABASE_URL -c "\d contracts"  # Check new rating columns
psql $DATABASE_URL -c "\d jobs"      # Check risk_level column
```

#### Phase 2: Application Deployment (SECOND)  
```bash
# Deploy new application code
fly deploy --app rateright

# Verify deployment
fly status --app rateright
```

#### Phase 3: Production Validation (IMMEDIATE)
```bash
# Test critical endpoints
curl -f https://write.com.au/dashboard
curl -f https://write.com.au/jobs

# Check database connectivity
fly ssh console --app rateright "python -c 'from app import create_app; app=create_app(); print(\"DB OK\")'"
```

### Why Database-First is CRITICAL:
1. **NEW app code REQUIRES new database columns**
2. **OLD app code can TOLERATE new database columns** (unused columns ignored)
3. **Database changes are BACKWARD COMPATIBLE** (with defaults)
4. **Minimizes downtime window** (schema ready before code deployment)

---

## 5. ROLLBACK PROCEDURES

### Database Rollback (HIGH RISK):
```bash
# Option 1: Migration Rollback (if caught early)
flask db downgrade -1  # Roll back last migration

# Option 2: Database Restore (if problems persist)  
fly postgres restore --app your-db-name backup-timestamp
```

### Application Rollback (SAFER):
```bash
# Revert to previous deployment
fly releases --app rateright
fly rollback v123 --app rateright  # Replace with previous version
```

### Emergency Procedures:
```bash
# If rating system fails, temporarily disable rating routes
# (Requires code change and emergency deployment)
```

---

## 6. INTEGRATION TESTING STRATEGY

### Pre-Deployment Testing:
```bash
# 1. Create staging database with production schema
fly postgres create --name rateright-staging

# 2. Test migration sequence on staging
flask db upgrade  # Apply all migrations

# 3. Deploy app to staging environment
fly deploy --app rateright-staging

# 4. Run automated tests on staging
python -m pytest tests/ -v

# 5. Manual testing of critical workflows:
#    - User registration and login
#    - Job posting and application  
#    - Contract creation and signing
#    - Rating system (both parties)
#    - Payment system (if enabled)
```

### Production Monitoring (Post-Deployment):
```bash
# Monitor application logs
fly logs --app rateright

# Monitor database performance
fly postgres list --app your-db-name

# Check error rates
curl -s https://write.com.au/health | jq '.status'
```

---

## 7. RISK ASSESSMENT & MITIGATION

### HIGH RISK FACTORS:
❌ **Hard database dependencies**: App will crash without new schema  
❌ **Migration sequence critical**: Wrong order can cause data loss  
❌ **No graceful degradation**: Rating system has no fallback mode

### MITIGATION STRATEGIES:
✅ **Database-first deployment**: Ensures compatibility during transition  
✅ **Comprehensive backups**: Multiple restore points available  
✅ **Staging environment testing**: Validate complete process first  
✅ **Monitoring and alerting**: Immediate detection of issues

### TIMING RECOMMENDATIONS:
- **Deploy during low-traffic period** (early morning AEST)
- **Have support team available** for 2 hours post-deployment  
- **Prepare rollback procedures** in advance
- **Test rollback process** on staging first

---

## 8. PRODUCTION READINESS CHECKLIST

### Pre-Deployment Requirements:
- [ ] Production database backup completed
- [ ] Staging environment tests passing
- [ ] Migration sequence validated on staging
- [ ] Rollback procedures tested and documented
- [ ] Monitoring and alerting configured
- [ ] Support team briefed and available
- [ ] Emergency contact information ready

### Database Migration Checklist:
- [ ] All 6 migrations identified and ordered correctly
- [ ] Backward compatibility verified for each migration
- [ ] Data integrity checks prepared
- [ ] Performance impact assessed (minimal for these migrations)

### Application Deployment Checklist:  
- [ ] Feature flags reviewed and configured
- [ ] Environment variables validated  
- [ ] Security configuration verified
- [ ] SSL certificates and domain setup confirmed
- [ ] Load balancer and scaling configuration checked

---

## 9. SUCCESS CRITERIA

### Technical Success Metrics:
- [ ] All migrations applied successfully (6/6)
- [ ] Application starts without errors
- [ ] Database connectivity confirmed
- [ ] All critical endpoints responding (< 500ms)
- [ ] No error spikes in monitoring

### Functional Success Metrics:
- [ ] User registration and login working
- [ ] Job posting and browsing functional
- [ ] Contract system operational
- [ ] Rating system fully functional (both contractor and worker ratings)
- [ ] Search and filtering working
- [ ] Mobile responsiveness maintained

### Business Success Metrics:
- [ ] Zero user-reported issues in first 24 hours
- [ ] No data loss or corruption
- [ ] Performance equal to or better than before deployment
- [ ] All existing user accounts and data intact

---

## 10. EMERGENCY CONTACTS & PROCEDURES

### If Things Go Wrong:
1. **Immediate Response**: Execute rollback procedures within 15 minutes
2. **Communication**: Notify stakeholders of issues and ETA for resolution
3. **Investigation**: Analyze logs and database state to identify root cause
4. **Recovery**: Implement fix and re-test before next deployment attempt

### Success Indicators:
- ✅ Zero 5xx errors in first hour
- ✅ Database response times < 100ms
- ✅ All user workflows completing successfully
- ✅ No rollback required within 24 hours

---

**FINAL RECOMMENDATION:** Proceed with database-first deployment during low-traffic hours with full team support and tested rollback procedures ready.