Description

Currently, when a deployment is rolled back after database migrations have been applied, Keep fails to start because the database schema is ahead of the application code. This creates a critical failure scenario where the application cannot recover without manual intervention.

Current behavior:

User deploys v2 of the application
Keep runs and executes the v2migrations successfully
User encounters a bug and initiates a rollback to v1
Keep attempts to start v1 but fails because the database schema is at v2 state

This forces users to manually downgrade the database or perform other workarounds, resulting in extended downtime and potential data issues.

Proposed Solution

Implement an automatic database schema detection and downgrade mechanism that runs during application startup to ensure the database schema is compatible with the running code version.
Technical Approach

Add a startup check that determines if the current database schema version (from alembic_version table) matches the expected version for the running code.
If a version mismatch is detected (database ahead of code), automatically execute a downgrade to the appropriate revision.
Implement a version mapping system that ties application versions to their corresponding alembic revisions.

# Pseudo-code for the implementation
def ensure_db_schema_compatibility():
    # Get current database revision from alembic_version table
    current_db_revision = get_current_db_revision()
    
    # Get expected revision for this application version
    expected_revision = get_expected_revision_for_app_version()
    
    # If database is ahead of code (rollback scenario)
    if current_db_revision != expected_revision:
        log.warning(f"Database schema ({current_db_revision}) doesn't match application version ({expected_revision})")
        
        # Execute downgrade to match application version
        execute_alembic_downgrade(expected_revision)
        
        log.info(f"Successfully downgraded database schema to {expected_revision}")

Integrate this check into the application startup sequence before any database operations are attempted.

Benefits

Zero-downtime rollbacks: Application can recover automatically from failed deployments
Reduced operational overhead: Eliminates the need for manual database interventions during rollbacks
Improved reliability: Ensures database schema and application code stay in sync even during deployment issues

Implementation Considerations

Ensure all migrations have proper downgrade() implementations
Add comprehensive logging for the downgrade process
Consider adding a configuration flag to disable automatic downgrade in specific environments
Add monitoring/alerting when automatic downgrades occur
Create a test suite that verifies the downgrade functionality works as expected

Acceptance Criteria

• Keep can be rolled back from v2 to v1 and automatically downgrade the database schema
• The process is properly logged for debugging and audit purposes
• Comprehensive tests verify the functionality works across multiple version transitions
• Documentation is updated to explain the automatic downgrade capability