Setup migration strategy

[kevinfalting]

Issue Description

We need a migration strategy to make it easy to migrate the database.

Supporting Documentation

Related: #50

• postgres 15 docs

Implementation Details

I haven't yet decided on how it will be implemented. I think I'd like to just have a list of migrations, starting at 0 and incrementing by one each time, which would enforce an order to the migrations.

migration/
  0.sql
  1.sql
  2.sql
  ...

My preference would be that these are run by a separate process from the server so that migrations and deployments are separate actions, but I also want the migrations to be run automatically locally so they aren't inconvenient. I've not yet settled on how, or if, I want to do this.

We will also require some way of keeping track of which migration has been run, and only run the ones that haven't been run yet. This likely requires a special table for tracking this. That table could look like:

id	migration_file_name	applied_at	migration_sql	hash
int	text	timestamp	text	text
22	22.sql	Oct 9 2023 03:14:15UTC	ALTER TABLE ...	DEADBEEF==

Acceptance Criteria

• [ ] Migrations can be run against the database in all environments
• [ ] A 0 migration for the migration table.

[kevinfalting]

I'm strongly considering a bash script that can accomplish the task here.

• It should get the list of migrations from the database
• apply only the sql files that haven't been run, in order
• insert a new row into the migration table for each successful migration

Aside from the postgres cli, we should be able to accomplish this with builtin tools for portability across unix like systems. At a minimum, the script should be compatible to run from within the official postgres docker image.

[kevinfalting]

As a part of getting it to run automatically on local, I'm considering using the compose file to make this happen, with potentially a depends_on directive.

Running these migrations in production will require thinking about this a little bit. I'm not entirely sure yet how it should be implemented.

[kevinfalting]

For running migrations in production, one option is to perform automatic migrations from a github action using the fly cli. Run a bash script that connects to it, which then runs the psql commands against it.

Another option is to use a release command that creates a temporary machine to run the migration during deployment.

[kevinfalting]

While I'd like to store the hash of the sql migration, another more interesting hash would be to store a hash of what a database schema dump after the migration would provide - run the migration, dump the schema to a text file, hash it, store that in the migration table. Or, create the hash of the current migration using the hash of the previous migration.

The idea is to be able to verify the migrations and their order of being run. I'd like to think about this some more, but it's a neat idea. One thing to be aware of is how a schema dump my change formatting after a database version change, so it may also be prudent to store the database version in the table too.