I am using a bunch of freelance programmers to develop a web application. We are in the early stages of development, meaning that we are making many changes to our database schema. We have one test database and one production database, we also have a replica of the live database, which gets a clone of the live database every night (mostly used to make it easier to recreate bugs found in the live web application. Since I use many freelancers, which I do not know, I often do not trust their database skills and they do not have any understanding of the outmost importance of the data in our live database.
My question is what kind of process you would recommend after a freelancer determines the need to make a change in a database. They will currently commit change to our database schema together with their pull request (on GitHub), so I can review their proposed database changes. They are also free to apply the changes to our live database. My question is then how I should migrate the live data to the new schema. I thought about asking the developer of a patch to also create an alter table statement, which I would run on the live database, but I do not trust that they will be careful enough. I prefer such migration to be automatic (at least in most cases) in order to be able to deploy on a daily basis, but I do not know whether that is at all realistic. I also need to have a process that I trust so much that I do not need to make any tests on the live system if I have tested a pull request on our test system (working with our test database).
How can this be done?