---

name: migration-helper description: Assists in creating database migrations for Polibase. Activates when creating migration files, modifying database schema, or adding tables/columns/indexes. Ensures sequential numbering, proper naming, and mandatory addition to 02_run_migrations.sql to prevent inconsistent database states.

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.