migration-create

name	migration-create
description	Create database migrations with proper validation, rollback support, and testing. For SEA-Forge services using databases.
disable-model-invocation	true

Database Migration Creation

Use this skill when:

• Creating a new database migration
• Modifying database schema
• Adding/removing columns or tables
• Changing indexes or constraints

Supported Databases

• PostgreSQL (via @testcontainers/postgresql for testing)
• Any database supported by SEA services

Migration Process

1. Pre-Migration Checks

Before creating a migration:

# Check current schema
# (database-specific command)

# Check for pending migrations
ls -la apps/*/migrations/pending/ 2>/dev/null

# Review recent migrations
find . -name "*migration*" -type f -mtime -7

2. Create Migration

Generate migration file with proper naming:

# Naming convention: YYYYMMDDHHMMSS_description.{ext}
# Example: 20240304120000_add_user_preferences_table.sql

# Create migration file
touch apps/sea-mq-worker/migrations/20240304120000_add_user_preferences_table.sql

3. Migration Template

Use this template for SQL migrations:

-- Migration: {description}
-- Created: {timestamp}
-- Author: {name}
-- Up: {what this migration does}
-- Down: {how to rollback}

-- ═══════════════════════════════════════════════════════════
-- UP MIGRATION
-- ═══════════════════════════════════════════════════════════

BEGIN;

-- Example: Create table
CREATE TABLE user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    preferences JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Example: Create index
CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);

-- Example: Add constraint
ALTER TABLE user_preferences ADD CONSTRAINT check_preferences_not_empty CHECK (jsonb_array_length(preferences) > 0 OR preferences = '{}');

COMMIT;

-- ═══════════════════════════════════════════════════════════
-- DOWN MIGRATION (ROLLBACK)
-- ═══════════════════════════════════════════════════════════

BEGIN;

-- Rollback in reverse order
DROP INDEX IF EXISTS idx_user_preferences_user_id;
DROP TABLE IF EXISTS user_preferences;

COMMIT;

4. Migration Validation

Validate migration before applying:

# Check SQL syntax
psql -f migration.sql --parse-only

# Dry run (if tool supports)
# Some migration tools support --dry-run

# Test migration on test database
# (service-specific command)

5. Create Tests

Write tests for the migration:

// migration.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { PostgreSqlContainer } from '@testcontainers/postgresql';

describe('Migration: add_user_preferences_table', () => {
  let container;

  beforeAll(async () => {
    container = await PostgreSqlContainer.start();
  });

  afterAll(async () => {
    await container.stop();
  });

  it('should create user_preferences table', async () => {
    const client = await container.getClient();

    // Run up migration
    await client.query(/* up migration SQL */);

    // Verify table exists
    const result = await client.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables
        WHERE table_name = 'user_preferences'
      )
    `);

    expect(result.rows[0].exists).toBe(true);
  }));

  it('should rollback successfully', async () => {
    const client = await container.getClient();

    // Run up migration
    await client.query(/* up migration SQL */);

    // Run down migration
    await client.query(/* down migration SQL */);

    // Verify table is gone
    const result = await client.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables
        WHERE table_name = 'user_preferences'
      )
    `);

    expect(result.rows[0].exists).toBe(false);
  });
});

6. Apply Migration

After validation, apply to development database:

# Apply up migration
# (service-specific command)

# Verify migration applied
# (service-specific command)

# Check schema
# (database-specific command)

Migration Best Practices

DO ✅

• Use transactions (BEGIN/COMMIT)
• Provide rollback (DOWN migration)
• Add descriptive comments
• Use descriptive names
• Test with testcontainers
• Check for existing objects before creating
• Use IF EXISTS when dropping
• Add appropriate indexes

DON'T ❌

• Don't use destructive operations without backup
• Don't change data type in place (use new column + migration)
• Don't forget to rollback in tests
• Don't assume database state
• Don't use migrations for data fixes (use separate scripts)
• Don't use SELECT * in migrations
• Don't create circular dependencies

Common Patterns

Add new column (nullable)

ALTER TABLE users ADD COLUMN preferences JSONB;

Add new column (non-nullable with default)

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

Add new column (non-nullable, no default)

-- Step 1: Add as nullable
ALTER TABLE users ADD COLUMN status TEXT;

-- Step 2: Backfill data
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Step 3: Make non-nullable
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Rename column

ALTER TABLE users RENAME COLUMN old_name TO new_name;

Add index

CREATE INDEX idx_users_email ON users(email);

Add foreign key

ALTER TABLE posts ADD CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Migration Checklist

Before committing migration:

• Migration file follows naming convention
• Up migration works correctly
• Down migration (rollback) works
• Migration uses transactions
• Tests written and passing
• Tested on local database
• No destructive operations without backup
• Documentation updated
• Performance impact considered (indexes)

Output Format

After creating migration, output:

## Migration Created

**File**: {path/to/migration.sql}
**Description**: {what it does}
**Type**: {schema|data|rollback}

### Changes
{bullet list of changes}

### Rollback
{how to rollback}

### Tests
{test file location}

### Next Steps
1. Review migration file
2. Run tests: `pnpm test migration.test.ts`
3. Apply to local DB
4. Verify changes
5. Commit with descriptive message

Usage

User invokes with:

• "Create a migration for user preferences"
• "Add a migration to index the email column"
• "I need to add a new table for settings"

Integration

• Integrates with postgres MCP for database operations
• Integrates with @testcontainers/postgresql for testing
• Integrates with CI/CD for migration validation