Migrations are non-destructive (no data loss without explicit intent)

A migration that drops a column or changes a type without a preceding expand-contract sequence destroys data the moment it is applied to production. Even if the application code no longer reads the column, the data may be needed for compliance, analytics, or future recovery. ISO 25010 recoverability requires the system to be restorable after failures; a migration that irreversibly removes data violates this by eliminating the recovery option. Adding NOT NULL to a column that has existing NULL values causes the migration to fail mid-run, leaving the schema in a partially applied state on some databases.

Use the expand-contract pattern for every destructive schema change. Never drop a column in the same deploy that stops writing to it. Always backfill NULL values before adding NOT NULL constraints.

-- Removing a column safely over two deploys:
-- Deploy 1: stop writing to the column (code change, no SQL)
-- Deploy 2: optionally nullify data
UPDATE users SET phone = NULL WHERE phone IS NOT NULL;
-- Deploy 3: drop when safe
ALTER TABLE users DROP COLUMN phone;

-- Adding NOT NULL to existing column:
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

-- Large-table index addition — avoid locking:
CREATE INDEX CONCURRENTLY idx_posts_user_id ON posts(user_id);

For type changes that narrow data (e.g., TEXT to VARCHAR(100)), audit row lengths first: SELECT MAX(LENGTH(description)) FROM posts; before applying the constraint.