Migration Helper

Purpose

Assist in creating database migrations following Polibase conventions and ensure proper integration with the migration system.

When to Activate

This skill activates automatically when:

• Creating new migration files
• Modifying database schema
• Adding tables, columns, indexes, or constraints
• User mentions "migration", "schema", or "database change"

⚠️ CRITICAL: Mandatory Steps

NEVER skip these steps when creating a migration:

1. Find Latest Number: Check

   database/migrations/

   for highest number
2. Create Migration File:

   database/migrations/XXX_description.sql

3. ⚠️ UPDATE RUN SCRIPT: Add to

   database/02_run_migrations.sql

   (MANDATORY!)
4. Test Migration: Run

   ./reset-database.sh

   to verify

Skipping step 3 causes inconsistent database states!

Quick Checklist

Before completing a migration:

• Sequential Number: Incremented from latest migration
• File Created: In

  database/migrations/XXX_description.sql

• ⚠️ Run Script Updated: Added to

  database/02_run_migrations.sql

• Idempotent: Uses

  IF NOT EXISTS

  /

  IF EXISTS

• Comments: Header and column comments included
• Indexes: Created for foreign keys and query columns
• Tested: Ran

  ./reset-database.sh

  successfully

Migration Naming

Format:

{number}_{description}.sql

Examples:

• 013_create_llm_processing_history.sql

• 014_add_email_to_politicians.sql

• 015_create_index_on_speakers_name.sql

Guidelines:

• Use descriptive names with action verbs
• Use snake_case
• Keep concise but clear

Common Patterns

Add Table

CREATE TABLE IF NOT EXISTS table_name (
    id SERIAL PRIMARY KEY,
    ...
);

Add Column

ALTER TABLE table_name
    ADD COLUMN IF NOT EXISTS column_name type;

Add Index

CREATE INDEX IF NOT EXISTS idx_table_column
    ON table_name(column_name);

See examples.md for detailed patterns.

Templates

Use templates in

templates/

directory for:

• New table creation
• Column addition
• Index creation
• Foreign key addition
• Enum type creation

Detailed Reference

For comprehensive migration patterns and SQL details, see reference.md.

Testing

# Reset database with all migrations
./reset-database.sh

# Verify migration applied
docker compose -f docker/docker-compose.yml [-f docker/docker-compose.override.yml] exec postgres \
    psql -U sagebase_user -d sagebase_db \
    -c "\d table_name"

Common Pitfalls

1. ❌ Forgetting 02_run_migrations.sql: Most common mistake!
2. ❌ Non-idempotent SQL: Use

   IF NOT EXISTS

3. ❌ Missing data migration: Update existing rows before adding NOT NULL
4. ❌ Breaking foreign keys: Drop constraints before dropping tables

See reference.md for detailed pitfall explanations.