[BUG] Missing integrity checks on migrations lead to corrupt DB schema state

Actions performed

A migration SQL script already deployed and applied was altered, so that in the altered migration a CREATE TABLE statement was amended with an additional column. That altered migration was deployed later along with the rest of the app using the migrate lib and ingested at app startup using migrate.Exec.

Expected outcome:

The migrate.Exec call should have panic'ed to indicate an unrecoverable difference of the expected and actual DB schema state, preventing the app from starting at all, so the issues would have been noticed by devops and could have been investigated.

Actual outcome:

The execution of the migration didn't neither yield and error that would have been logged, nor did it panic. However from this point on the DB was in a corrupt state.

Proposal to mitigate the issue:

In the process of preparing the execution of migrations in migrate.go:611 planMigrationCommon the ID of the migrations is used to identify migrations already applied. An additional integrity check – for each already applied migration in the migrations table comparing a stored content hash to a computed hash of the corresponding script – could be performed here before any migrations are applied. If such a check fails, this could be used to panic at the original caller. An additional flag disabling that behavior by default could be used to ensure backwards compatibility.

Start in migrate.go:611 at planMigrationCommon and trace how migrate.Exec identifies migrations already recorded in the migrations table. Define how a stored and computed script hash should be compared before applying migrations, including the proposed backwards-compatibility flag. Done means an altered applied migration is detected and the documented failure reaches the original caller before further migrations run.