Down to Zero: The Rollback Path That Never Ran

I finally ran the migration chain down to zero again, for the first time in months. My guess was optimistic: the chain was healthy down to version 56, and I only needed to open the road beneath it. Instead the chain stopped mid-way with two errors that had nothing to do with the reason down always stopped at 56. I stood in front of the terminal with one uncomfortable realization: the theory behind the checkpoint had never been tested.

Checkpoint v56 and the Wrong Theory

The unwritten rule in the project said down-to-0 was off limits, because version 56 was considered the last safe point. The reasoning was an ENUM-ordering theory: the ENUM structures at that version were assumed to disagree with what the server expected, so forcing the migration lower was believed to risk corrupting data. Without any proof, the theory became the standing answer, and checkpoint 56 was the final word whenever anyone wanted to reset the schema.

That day's down-to-0 run dismantled all of it. What failed were two mechanical steps in files nobody had touched for a long time:

• 000051.down tried to INSERT five module='INFORMASI' categories back without the slug column. That column is NOT NULL with no DEFAULT, so MySQL raised error 1364, Field 'slug' doesn't have a default value [5]. The cause was purely chronological: the down file was written before the slug column was born in a later migration, and it had never been executed since.
• 000022.down performed DROP INDEX uq_views_entity_ip before adding its replacement. The problem: the foreign key on entity_id in entity_views rides on that unique index. MySQL requires indexes for foreign keys so checks do not degrade into table scans [1], so the moment the only index backing the FK was dropped, the server refused with error 1553, Cannot drop index ... needed in a foreign key constraint [4].

Two Fixes, One Ordering Principle

Both fixes are simple, and the principle behind them is reusable: never remove the last support before its replacement stands. In 000022.down the order is reversed: ADD KEY idx_views_entity_ip (entity_id, ip_address) first, then DROP INDEX uq_views_entity_ip. Once another index can carry the foreign key, dropping the old one gets approved. In 000051.down, the INSERT was rebuilt with the slug and sort_order columns, values copied faithfully from 000045.up, the original text rather than invention: olahraga, budaya, promosi, wisata, umum. That fidelity matters because a down migration is a claim that the old state can be restored as it was, not a version that merely resembles it.

What bothered me most about this incident: both down files were never syntactically wrong, passed review when written, and failed only because the world moved on. New columns appeared, indexes changed roles, and the strict SQL mode that once forgave short INSERTs now rejects them. A down file that never runs rots silently while its up sibling gets exercised every time a new environment appears. Rereading those two files felt like reading letters from the past with addresses that no longer exist.

The Roundtrip as the Only Proof

The long-term fix is more than a note saying "be careful below version so-and-so": a test that proves the whole chain, head to 0 and back to head. The golang-migrate documentation itself insists "all migrations should be reversible" and recommends a down migration that cleans up the state of its up counterpart [7]. The roundtrip test migrations_test compares schema snapshots taken before and after the journey: every column and every foreign key read from information_schema in deterministic order, then the fresh-up and post-roundtrip snapshots are compared line by line. The 000067 foreign keys are even touched explicitly: fk_reviews_user, fk_regsub_user, and fk_report_user must read RESTRICT, while otp_codes must still read CASCADE.

Checkpoint v56 is now officially retired, recorded as an owner decision. A rollback path that is never executed is not a rollback path, only the illusion of one. In its place stands a measurable guarantee: a migration chain that may be taken to zero at any time, because its proof is a test that runs every time the chain changes, not a theory.