Hmm one team at work uses python and Alembic to do migrations. It stores a hash on a table in the database so you see clearly what version the database is on. It can get pretty advanced, too: you can state that some revision is dependent on another etc.

But the more involved process you describe above was done manually at the stateside healthcare company I worked for: they had a team of DBAs that would review migrations and design and such and approve them. The chansges would go to staging. QA would test doing integration and other tests. And if passed it would roll out to production and if it failed for whatever reason it would go back to the developers. And then back to the DBAs for review and then to QA etc etc.

Other noteworthy tools include Sqitch. It is a super flexible migration tool. https://sqitch.org

Alembic looks interesting, I currently use Django where I can for version controlling database changes but it’s not ideal. I really want a terraform type tool.