Database Migration

Migration File Conventions

Every schema change must be captured in a versioned migration file. Follow these conventions:

• Timestamped naming: Use YYYYMMDDHHMMSS_descriptive_name (e.g., 20240315143022_add_status_to_orders.sql).
• Sequential ordering: Migrations run in order. Never insert a migration before one that has already been applied.
• One concern per file: Each migration handles a single logical change. Do not combine unrelated schema changes.
• Descriptive names: The filename should describe the change, not the ticket number. Use verbs like add, remove, create, drop, rename, alter.
• Idempotent when possible: Use IF NOT EXISTS / IF EXISTS guards to make re-runs safe.

migrations/
  20240301100000_create_users_table.sql
  20240305120000_add_email_index_to_users.sql
  20240310090000_create_orders_table.sql
  20240315143022_add_status_to_orders.sql