Designing a Down-First Migration: Additive Columns, Postponed FK

I was staring at the screen, the cursor blinking on the first line of 000064_slice_b_entity_columns.up.sql. This is for the KotaPortal services module, an app handling regional tourism and the creative economy. In front of me sat an ALTER TABLE block ready to run. Moments like this always make my heart rate tick up.

My old default assumption: migrations are scary because they rewrite data that already exists. So when adding a type_id column, the thorough move would be to add the foreign key to facility_types right now, for instant relational integrity, right?

Nope. I deliberately postponed that foreign key. The comment inside the migration file says it plainly: the facility_types master table lands in the next slice. More than that, the whole migration is designed so every statement is purely additive. The down file is just an inverted mirror that drops exactly the same columns and indexes. No data rewrite anywhere.

Why This Approach Holds Up

The down-first approach has solid technical footing. On InnoDB with MySQL 5.7, "ADD COLUMN" runs in place and allows concurrent DML [1]. Adding an index is the same story. Dropping an index only touches metadata. Dropping a column still rebuilds the table, and adding a foreign key constraint is only in-place when foreign_key_checks is disabled; otherwise the database forces an expensive copy operation [1].

When everything in the up phase is additive, the down-phase rollback simply drops the same columns and indexes, with no old data getting rewritten.

There is a small detail on the deleted_at column of the categories table. I declared it TIMESTAMP NULL DEFAULT NULL. Not by accident. As a general rule, if the NULL attribute is not stated explicitly and explicit_defaults_for_timestamp is off, assigning NULL to a TIMESTAMP column can initialize it to the current timestamp [2]. Declaring the column NULL exempts it from that magic, so it works as a plain data flag.

The pattern matches the golang-migrate contract: a paired up and down file per version, applied upward by increasing version and downward by decreasing version [3]. Tools like that hang their execution order on those file pairs, so failure scenarios had to be part of the design from the start. The same principle shows up in API design conventions: defaults belong only on optional fields, and readers must not assume a field exists without a server-side default [4]. Exactly the same logic as adding a column to a live table. You cannot force the app to read the new column without a safe default or nullability.

-- up migration
ALTER TABLE entities
  ADD COLUMN contact_person VARCHAR(100) NULL,
  ADD COLUMN rating_enabled TINYINT(1) NOT NULL DEFAULT 1,
  ADD COLUMN rental_enabled TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN type_id BIGINT UNSIGNED NULL,
  ADD KEY idx_type_id (type_id);

ALTER TABLE categories
  ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL,
  ADD KEY idx_deleted_at (deleted_at);

-- down migration (cerminan terbalik)
ALTER TABLE categories
  DROP KEY idx_deleted_at,
  DROP COLUMN deleted_at;

ALTER TABLE entities
  DROP KEY idx_type_id,
  DROP COLUMN type_id,
  DROP COLUMN rental_enabled,
  DROP COLUMN rating_enabled,
  DROP COLUMN contact_person;

I picked this approach because peace of mind at rollback time beats the false comfort of a perfectly related schema on day one. If the master table is not ready, forcing the foreign key just invites an unnecessary copy operation. Sometimes the safest move is not doing the thing that is not truly needed yet.

## Sources [1] https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html [2] https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html [3] https://github.com/golang-migrate/migrate/blob/master/MIGRATIONS.md [4] https://github.com/kubernetes/community/blob/master/contributors/devel/sig-architecture/api-conventions.md