Database Migration Rollback Procedure

Overview

This runbook provides step-by-step instructions for rolling back database migrations in the Almafrica platform. Use this procedure when you need to revert to a previous application version that requires an older database schema.

When to Use This Runbook

• Application rollback: When reverting to a previous application version that is incompatible with the current database schema
• Failed migration: When a migration causes issues in production and needs to be reversed
• Emergency recovery: When database changes need to be undone quickly

Prerequisites

Before starting a rollback, ensure you have:

1. Database access: Connection string and credentials for the production database
2. EF Core CLI: dotnet ef tools installed locally
3. Backup verification: Confirm that a recent database backup exists
4. Target migration name: Know which migration to roll back to
5. Team notification: Inform the team that a rollback is in progress
6. Maintenance window: Schedule downtime if possible (recommended for production)

The RUN_MIGRATIONS Environment Variable

CRITICAL: The production environment has RunMigrations=false set in docker-compose.production.yml. This prevents the application from automatically running migrations on startup.

Why this matters for rollback:

• If you deploy an older application version, it won't see migrations that were added after that version
• Without the RunMigrations=false gate, the old app would fail or behave unpredictably
• With the gate in place, you can safely deploy the old app, then manually roll back the database

For other environments:

• Development: RunMigrations=true (default) - auto-migration enabled for convenience
• Staging: RunMigrations=true (default) - auto-migration enabled for testing
• Production: RunMigrations=false - migrations are manual and explicit

Step-by-Step Rollback Procedure

Step 1: Record Current State

Before making any changes, document the current state:

# Connect to production database and record current migration
cd backend/Almafrica.API

# List all applied migrations
dotnet ef migrations list --project ../Almafrica.Infrastructure --connection "<PRODUCTION_CONNECTION_STRING>"

# Record the last migration name (e.g., "20260210123456_AddFarmerPhoneNumber")

Save this information - you'll need it if you need to roll forward again.

Step 2: Identify Target Migration

Determine which migration to roll back to:

# Review migration history
dotnet ef migrations list --project ../Almafrica.Infrastructure

# Identify the target migration (the last "good" migration before the problematic one)
# Example: If rolling back from v1.2.0 to v1.1.0, find the last migration in v1.1.0

Target migration name format: 20260210123456_MigrationName

Step 3: Create Database Backup

MANDATORY: Always create a backup before rolling back:

# Using pg_dump (PostgreSQL)
pg_dump -h <host> -U <user> -d almafrica_production -F c -b -v -f "backup_before_rollback_$(date +%Y%m%d_%H%M%S).dump"

# Verify backup file exists and has reasonable size
ls -lh backup_before_rollback_*.dump

Step 4: Roll Back Database

Execute the migration rollback:

cd backend/Almafrica.API

# Roll back to target migration
dotnet ef database update <TargetMigrationName> \
  --project ../Almafrica.Infrastructure \
  --connection "<PRODUCTION_CONNECTION_STRING>"

# Example:
# dotnet ef database update 20260201120000_InitialCreate \
#   --project ../Almafrica.Infrastructure \
#   --connection "Host=db.example.com;Database=almafrica_prod;Username=admin;Password=***"

What happens:

• EF Core executes the Down() method of each migration between the current state and the target
• Migrations are rolled back in reverse chronological order
• Database schema is reverted to match the target migration

Step 5: Verify Database State

Confirm the rollback succeeded:

# List applied migrations - should show target migration as the last one
dotnet ef migrations list --project ../Almafrica.Infrastructure --connection "<PRODUCTION_CONNECTION_STRING>"

# Connect to database and verify schema
psql -h <host> -U <user> -d almafrica_production

# Check critical tables exist and have expected columns
\d farmers
\d orders
# etc.

Step 6: Deploy Previous Application Version

Now that the database matches the older schema, deploy the previous application version:

# Option A: Using Coolify UI
# 1. Go to Coolify dashboard
# 2. Select the API service
# 3. Choose "Redeploy" with the previous image tag (e.g., v1.1.0)

# Option B: Using Docker Compose
cd /path/to/deployment
export IMAGE_TAG=v1.1.0
docker compose -f docker-compose.production.yml pull
docker compose -f docker-compose.production.yml up -d api

# Option C: Using Coolify API
curl -X GET "https://coolify.example.com/api/v1/deploy?uuid=<API_UUID>&tag=v1.1.0" \
  -H "Authorization: Bearer ${COOLIFY_TOKEN}"

Step 7: Verify Application Health

Confirm the application is running correctly:

# Check health endpoint
curl https://api.almafrica.com/health

# Check version endpoint
curl https://api.almafrica.com/health/version

# Verify version matches expected rollback version
# Expected: {"version": "1.1.0", "environment": "Production", ...}

# Check application logs for errors
docker logs almafrica-api --tail 100

Step 8: Smoke Test Critical Functionality

Perform basic smoke tests:

1. Authentication: Test login with admin credentials
2. Data retrieval: Fetch farmers, orders, or other critical entities
3. Data creation: Create a test record (if safe to do so)
4. API endpoints: Test key endpoints used by frontend/mobile

Step 9: Monitor and Communicate

After rollback:

1. Monitor logs: Watch for errors or warnings
2. Monitor metrics: Check API response times, error rates
3. Notify team: Inform team that rollback is complete
4. Update incident log: Document what happened and why rollback was needed

Migration Convention: Down() Methods

CRITICAL CONVENTION: All new migrations MUST have a working Down() method.

Why This Matters

• Rollback capability: Without Down() methods, you cannot roll back migrations
• Production safety: Enables quick recovery from failed deployments
• Testing: Allows testing migration rollback in staging before production

How to Ensure Down() Methods Work

When creating a new migration:

# 1. Create migration
dotnet ef migrations add AddNewFeature --project ../Almafrica.Infrastructure

# 2. Review the generated migration file
# Verify that the Down() method reverses what Up() does

# 3. Test the Down() method locally
dotnet ef database update AddNewFeature  # Apply migration
dotnet ef database update PreviousMigration  # Roll back

# 4. Verify database is in expected state after rollback

Down() Method Guidelines

• Reverse operations: Down() should undo everything Up() does
• Data preservation: Consider data loss - add warnings if Down() drops columns with data
• Order matters: Drop foreign keys before dropping tables
• Idempotent: Down() should be safe to run multiple times

Example:

public partial class AddFarmerPhoneNumber : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "PhoneNumber",
            table: "Farmers",
            type: "character varying(20)",
            maxLength: 20,
            nullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "PhoneNumber",
            table: "Farmers");
    }
}

Troubleshooting

Issue: Migration Rollback Fails

Symptoms: dotnet ef database update fails with constraint or data errors

Solutions:

1. Check if there's data that prevents rollback (e.g., foreign key constraints)
2. Manually clean up problematic data before retrying
3. If Down() method is missing or broken, manually write SQL to revert changes
4. Restore from backup if rollback is not possible

Issue: Application Fails After Rollback

Symptoms: Application starts but returns errors

Solutions:

1. Verify database schema matches application expectations
2. Check that correct application version is deployed
3. Review application logs for specific errors
4. Verify environment variables are correct

Issue: Data Loss After Rollback

Symptoms: Data created after the target migration is missing

Expected behavior: Rolling back migrations can cause data loss if:

• Columns are dropped that contained data
• Tables are dropped
• Data transformations are reversed

Prevention:

• Always backup before rollback
• Review Down() methods for data-destructive operations
• Consider manual data migration if needed

Emergency Contacts

If rollback fails or causes critical issues:

1. Database Administrator: [Contact info]
2. DevOps Lead: [Contact info]
3. Project Lead: [Contact info]

Related Documentation

• Migration best practices: agent-os/standards/backend/migrations.md
• Migration checklist: backend/MIGRATION_CHECKLIST.md
• AGENTS.md: Migration conventions for the team
• Release process: RELEASE.md (when available)

Rollback Checklist

Use this checklist when performing a rollback:

• Record current migration name
• Identify target migration
• Create database backup
• Verify backup file exists
• Execute dotnet ef database update <target>
• Verify database state
• Deploy previous application version
• Check health endpoint
• Check version endpoint
• Perform smoke tests
• Monitor logs for errors
• Notify team of completion
• Document incident and lessons learned

Post-Rollback Actions

After a successful rollback:

1. Root cause analysis: Investigate why rollback was needed
2. Fix the issue: Address the problem that caused the rollback
3. Test thoroughly: Ensure fix works in staging before production
4. Plan roll-forward: Determine when to deploy the fix
5. Update documentation: Document lessons learned

Notes

• Rollback is not failure: It's a safety mechanism for production stability
• Practice in staging: Test rollback procedures in staging environment
• Keep it simple: Don't over-engineer - manual rollback with clear runbook is better than complex automation
• Communication is key: Keep team informed throughout the process