SQL Schema Migration Pattern

-- Migration tracking table
CREATE TABLE IF NOT EXISTS schema_migrations (
    version INT PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at TIMESTAMPTZ DEFAULT NOW()
);

-- Migration 001: Create users table
-- UP
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);

INSERT INTO schema_migrations (version, name) VALUES (1, 'create_users');

-- DOWN (rollback)
-- DROP TABLE users;
-- DELETE FROM schema_migrations WHERE version = 1;

-- Migration 002: Add orders table
-- UP
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    amount NUMERIC(10,2) NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

INSERT INTO schema_migrations (version, name) VALUES (2, 'create_orders');

-- Check current version
SELECT MAX(version) AS current_version FROM schema_migrations;