Add new command to detect back-dated migrations

[mzabani]

Suppose two different PRs each add their own migration like this:

2000-02-01-first-to-merge.sql

create or replace somefunction as 'function 1';

And the other, 2000-01-31-second-to-merge.sql

create or replace somefunction as 'function 2';

The second one will obviously get merge conflicts, which should make the developer react by trying to understand the nature of the conflict and perhaps talk to their colleague to know how to resolve this. Once they do that, they might still forget to bump the timestamp of their own migration, which means trouble (more on why below).

Of course, one would hope their changes are necessary, so they'd catch this with a test or with manual testing. But we know reality is often not so kind.

So why is this trouble? Because when this is deployed, there's a chance that 2000-02-01-first-to-merge.sql has already been deployed, meaning in that environment 2000-01-31-second-to-merge.sql might be second to be applied, leading to a codd schema mismatch and entirely different functions in that environment vs in developers' machines. And codd up --strict-check in CI does not prevent this.

So we need to provide our users something else to run in CI; something that would detect this. It could be something like:

codd backdated-check --git-revision production-deploy-2024-07-13T09-16-01Z (but could be any git branch/tag nomenclature or arbitrary git revision)

This command would first apply all migrations in that git revision in order, then apply pending migrations of the current branch, and then check for schema equality.

Things we need to do:

• Add this new command to the documentation
• Try to succinctly explain the problem it solves
• We already tell our users to run codd up --strict-check in CI. With this new command it means users will need to drop the database and recreate it between them. Maybe we should provide a single codd ci-checks command that does both automatically? It might be that the role that can drop the database is not obvious, meaning we'd have to ask for a connection string to drop the database. This sounds convoluted.. but maybe we can try a few connection strings that work with most installations and add an optional --conn-string-to-drop-db argument to the command for the cases where that doesn't work. A counterargument to having a single command is that users may have multiple environments they deploy to, so let them handle the simple dropdb commands in between as necessary.