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.

• ID: database-design-operations.migration-versioning.non-destructive-migrations

• Severity: high

• What to look for: Read through all migration files and flag operations that could cause data loss or downtime. Dangerous operations: (1) DROP TABLE without a preceding data backup or export step. (2) DROP COLUMN without a transitional migration that first nullifies the column and waits for code that references it to be removed. (3) ALTER TABLE ... ALTER COLUMN type changes that could truncate data (e.g., VARCHAR(255) to VARCHAR(50), or TEXT to INT). (4) TRUNCATE TABLE in a non-seed migration. (5) Column renames done as DROP + ADD instead of RENAME COLUMN (data loss). (6) Adding NOT NULL to a column that has existing NULL values without a backfill step first. Also look for migrations that lock tables in ways that would cause production downtime (large table rewrites without CONCURRENTLY).

• Pass criteria: Migrations are additive — count every migration file and classify each operation as additive or destructive. fewer than 1 unguarded destructive operation allowed. Destructive operations (drops, type changes) are preceded by data migration steps and require explicit confirmation in comments. Column renames use RENAME COLUMN. NOT NULL additions are preceded by NULL backfills. Long-running index additions use CREATE INDEX CONCURRENTLY.

• Fail criteria: DROP TABLE or DROP COLUMN without data migration. Type change that could truncate existing data. Adding NOT NULL to a column that currently has NULL values without backfilling first.

• Skip (N/A) when: No migration files exist (covered by migration-tool-present check).

• Detail on fail: Specify the dangerous migration. Example: "Migration 008_refactor_users.sql drops the 'phone' column with no prior data export or backfill — data loss if column contains values." or "Migration 012 changes 'description' column type from TEXT to VARCHAR(100) — truncates existing descriptions longer than 100 characters.".

• Remediation: Use the expand-contract pattern for destructive changes:

  -- WRONG — drops column immediately (data loss)
  -- ALTER TABLE users DROP COLUMN phone;
  
  -- CORRECT — expand-contract pattern for column removal:
  
  -- Migration 1: Stop writing to old column (deploy code that no longer uses it)
  -- (no SQL needed — just a code change)
  
  -- Migration 2: Nullify existing data (optional grace period)
  UPDATE users SET phone = NULL WHERE phone IS NOT NULL;
  
  -- Migration 3: Add NOT NULL to confirm nothing writes here anymore
  -- (skip if column is being dropped)
  
  -- Migration 4: Drop the column (now safe — no data remains)
  ALTER TABLE users DROP COLUMN phone;
  
  -- Adding NOT NULL to existing column — backfill first
  -- Step 1: backfill NULLs
  UPDATE users SET status = 'active' WHERE status IS NULL;
  -- Step 2: add constraint (now safe)
  ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  
  -- Index on large table — use CONCURRENTLY to avoid locking
  CREATE INDEX CONCURRENTLY idx_posts_user_id ON posts(user_id);