Design: Migrations V1

# Design: Migrations V1 **Author**: @sdouglas  ## Description (what) This document describes the initial design for database migrations in FTL. ## Motivation (why, optional) FTL needs migrations as part of it's provisioning support. ## Goals * Define how basic dbmate migrations will be handled by FTL. * Define how these migrations will work in production and in dev mode. ### Non-Goals (optional) * Compatibility is out of scope * In depth understanding of the contents of the migration files is out of scope ## Design (how) [DBMate](https://github.com/amacneil/dbmate) will be used to manage FTL migrations. FTL migrations will be per module, and all migration files will be stored in the `db/$dbname` directory at the top level of the FTL module (TODO: should language plugins control this? `src/main/db` might make more sense for Java). Migrations will be listed in the schema as metadata for the data source. Each migration file + the SHA256 hash of the migration file will be listed under the data source in the schema. Migration files should be deployed with the deployment to allow the provisioner to retrieve them by hash. ``` database mysql testdb +migration 20231103205514_init.sql 42014f0e0bf2f238566d1607727b865a8909421146ed450dfa993c8b19875216 +migration 20240704103403_create_module_secrets.sql 8cc04c75ab7967eb2ec82e11e886831e00b7cb00507e9a8ecf400bdc599eccfd ``` This design may mean that migrations lists in the schema may get unreasonably large, to prevent this we may in the future provide the option to reference migrations from a previous version of a schema. This is the hash of a previous version of the schema, migrations are retrieved by looking up the previous schema from OCI and then appending any additional migrations for the same datasource to the list. This previous schema must be for the same module and contain the same data source: ``` database mysql testdb +migration-reference 72014f0e0bf2f238566d1607727b865a8909421146ed450dfa993c8b19875216 +migration 20240704103403_create_module_secrets.sql 8cc04c75ab7967eb2ec82e11e886831e00b7cb00507e9a8ecf400bdc599eccfd ``` This migration reference is outside the scope of the initial work. When a module is deployed the provisioner will run the migrations as part of the provisioning process, after the database has been provisioned. The migration files will be retrieved from OCI using a lookup of the provided hash. Initially the migrations will be run in the provisioner, however it may be that in future the procisioner needs to spin up a Job to run them, although these details are out of scope for now. In dev mode the database will be provisioned when changes are detected to the migration files. Dev mode may short circuit the requirements to deploy the migrations to an OCI registry. When running tests the test database will be automatically migrated at the start of the test run. We will likely want more control that this long term (e.g. tests specifying they want a clean database), but that is out of scope of the initial implementation. ### Required changes (how) * Add migration information to the schema * Implement migration discovery in the language plugin to add the migrations to the deployment schemas * Implement a provisioner that can apply these migrations as part of the provisioning process * Add support for this to dev and tests to provide a good user experience