I've got an old monolithic project using MariaDB and PHP that I'm trying to put on CI/CD. The project has DB migrations to update the DB state. My current CD is built on top of AWS CodeCommit + CodePipeline + CodeBuild + CodeDeploy pushing a Docker image into ECR and then making AWS ECS redeploy the service. The container itself applies the DB migrations before starting the web server.
New deployments are ok, but I can't figure out how to rollback properly.
If I try to just deploy the older version of the image it won't have the migrations that need to be reverted and the DB state will differ from the one expected by the code.
I couldn't find any AWS guidance on the rollback with the special handling of the DB migrations, so it looks like it is up to me to come up with some solution.
My current idea is to update the build process so it would look for migration files that exist in the currently deployed image (the one being rolled back) but not in the upcoming deployment (some previous app version), and store them in a special directory. When the container will start it will revert all migrations found in the special directory.
This approach has a big flaw: new migrations are not guaranteed to work with the old code and/or configuration. Same time, the reverted code is not guaranteed to work correctly at all, hence I can't use the rolled-back image to take down its changes.
What would be the correct implementation of the rollback in this case?