- Generate migration (M) by comparing to production (P0): I
- P0 = M
- Edit M as necessary, test for correctness, commit to master (meets your second criteria)
- Deploy code, with the migration running as a deploy step (meets your first criteria)
- Migration works as follows:
- Inspect P again, save as P1. If P0 != P1, abort process (this prevents any issues from out-of-band changes as per your third criteria, and means the pending script won't run more than once)
- Apply M.
- Inspect P once more save as P2. Double-check P2 == I as expected.
- Develop intended schema (I)
- Inspect production schema (P), save as P0
- Generate migration (M) by comparing to production (P0): I
- P0 = M
- Edit M as necessary, test for correctness, commit to master (meets your second criteria)
- Deploy code, with the migration running as a deploy step (meets your first criteria)
- Migration works as follows:
- Inspect P again, save as P1. If P0 != P1, abort process (this prevents any issues from out-of-band changes as per your third criteria, and means the pending script won't run more than once)
- Apply M.
- Inspect P once more save as P2. Double-check P2 == I as expected.