Database Migrations

001_create_tables.sql          -- no timestamp
20260318_stuff.sql             -- vague name, incomplete timestamp

2. Use timestamptz — never timestamp

All timestamp columns must use timestamptz (timestamp with time zone).

CORRECT:

created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()

WRONG:

created_at timestamp NOT NULL DEFAULT now()  -- loses timezone info

3. Use gen_random_uuid() for primary keys

Tables with UUID primary keys should use PostgreSQL-generated UUIDs. Note: some tables (e.g., teams) use composite natural keys instead — that is fine when the domain calls for it.

id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

WRONG:

id serial PRIMARY KEY,         -- sequential, predictable
id uuid PRIMARY KEY,           -- no default — app must generate

4. Use IF NOT EXISTS / IF EXISTS for idempotency

Extensions, functions, and indexes should be idempotent so migrations can be safely re-run during development.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE INDEX IF NOT EXISTS idx_user_brackets_user_id ON user_brackets(user_id);
DROP INDEX IF EXISTS idx_old_name;

5. Start each migration with a description comment

-- Description: User brackets for saving tournament picks.

6. Never drop columns or tables with data in production

For schema changes:

• Add new columns as nullable first
• Backfill data
• Then add constraints in a subsequent migration
• Never DROP TABLE unless it's truly empty

WRONG:

ALTER TABLE teams DROP COLUMN conference;  -- data loss

CORRECT:

-- Migration 1: add new column
ALTER TABLE teams ADD COLUMN conf_abbrev text;

-- Migration 2: backfill
UPDATE teams SET conf_abbrev = conference WHERE conference IS NOT NULL;

-- Migration 3: drop old column (after verifying backfill)
ALTER TABLE teams DROP COLUMN conference;

7. Include RLS and policies in the same migration as the table

Don't create a table in one migration and add RLS in another — the table is exposed between migrations.

CREATE TABLE my_table ( ... );
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY "..." ON my_table ...;