Problem statement

1. A new developer trying to setup the AMRIT platform has no way to get started with the database.
2. When a new version of AMRIT is deployed, the DB schema changes are done with manual SQL execution even on production.
3. DB schema does not follow any versioning. It is impossible right now to know where we are with the current schema on a production machine.
4. Changes to DB schema are not sequential.

Requirements

1. A new developer or someone trying to set up AMRIT as a DPG must be able to restore the AMRIT DB schema for all the underlying databases of AMRIT.
2. DB schema must follow versioning.
3. When production deployments happen, migrations from one schema to next schema must be automated through a CI-CD pipeline.
4. At any point, as an IT engineer, I must be able to infer the current DB schema version of a production machine.
5. Migrations from one version to the next must follow ACID principles of relational DB. It must be all or none. Migrations must happen as a transaction.
6. There must be a mechanism to roll back migrations and return to the previous schema if the deployment fails or is aborted.

Potential solution

1. Create a new Spring boot service to exclusively handle migrations.
2. Add Flyway as the package for handling migrations: https://documentation.red-gate.com/fd/getting-started-with-flyway-184127223.html
3. Connect the flyway configuration to databases of AMRIT.
4. Create a baseline migration/schema for the existing DB.
5. Test the baseline migration by restoring the migration file to empty DB using migrate command.
6. Any additional alterations to the schema must be added as next DB schema version.
7. The migrate command will be added to Jenkins pipeline as part of the CI-CD process.
8. Create an API endpoint to the service to display current version of the DB and the last 5 migration numbers executed.