porcupine-md/jonggrang/safe-migrations

-- SAFE: new nullable column, app ignores it until code deploys
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);
 
-- THEN: deploy code that reads/writes display_name
-- THEN: if needed, backfill values
UPDATE users SET display_name = username WHERE display_name IS NULL;
-- THEN: add NOT NULL constraint (separate migration)
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

-- STEP 1: add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
 
-- STEP 2: copy data
UPDATE users SET full_name = first_name || ' ' || last_name;
 
-- STEP 3: deploy code that reads BOTH columns (backward compat)
-- STEP 4: verify new column has all data
-- STEP 5: drop old columns (in a later migration after old code is gone)
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

-- WRONG: locks the table
CREATE INDEX idx_users_email ON users(email);
 
-- RIGHT: doesn't lock (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- STEP 1: deploy code that no longer reads/writes the column
-- STEP 2: mark column ignored (shadow drop)
ALTER TABLE users ALTER COLUMN old_col DROP NOT NULL;
-- STEP 3: wait for all app instances to deploy
-- STEP 4: drop the column
ALTER TABLE users DROP COLUMN old_col;

prisma/migrations/
  20240101000000_add_user_display_name/
    migration.sql
  20240102000000_backfill_display_name/
    migration.sql

-- Always write a down migration
-- migration_20240101_up.sql
ALTER TABLE users ADD COLUMN score INTEGER DEFAULT 0;
 
-- migration_20240101_down.sql
ALTER TABLE users DROP COLUMN score;