Wheels Migration Generator

What this skill does

# Wheels Migration Generator

## When to Use This Skill

Activate automatically when:
- User requests to create a migration (e.g., "create posts table")
- User wants to add/modify/remove columns
- User needs to add indexes or foreign keys
- User is changing database schema
- User mentions: migration, database, table, column, index, schema

## 🚨 CRITICAL: Migration File Location

**Migrations MUST be in:** `app/migrator/migrations/`
**NOT:** `db/migrate/` or any other location

After creating migration files, reload Wheels: `curl -s "http://localhost:PORT/?reload=true&password="`

## Critical Anti-Patterns to Prevent

### ❌ ANTI-PATTERN 1: Wrong Migration Directory

**WRONG:**
```bash
# Creating migration in wrong location
db/migrate/20251022072809_CreateUsers.cfc  ❌ Won't be found!
```

**CORRECT:**
```bash
# Wheels looks for migrations here
app/migrator/migrations/20251022072809_CreateUsers.cfc  ✅ Correct!
```

### ❌ ANTI-PATTERN 2: timestamps() Includes deletedAt

**WRONG:**
```cfm
t.datetime(columnNames="deletedAt", allowNull=true);
t.timestamps();  // ❌ Creates duplicate deletedAt!
```

**CORRECT:**
```cfm
t.timestamps();  // ✅ Includes createdAt, updatedAt, AND deletedAt
```

**Note:** Wheels `t.timestamps()` automatically adds:
- `createdAt` (datetime, NOT NULL)
- `updatedAt` (datetime, NOT NULL)
- `deletedAt` (datetime, NULL) - for soft delete support

### ❌ ANTI-PATTERN 3: Database-Specific Date Functions

**NEVER use database-specific functions like DATE_SUB(), NOW(), CURDATE()!**

**WRONG:**
```cfm
execute("INSERT INTO posts (publishedAt) VALUES (DATE_SUB(NOW(), INTERVAL 1 DAY))");  ❌ MySQL only!
```

**CORRECT:**
```cfm
var pastDate = DateAdd("d", -1, Now());
execute("INSERT INTO posts (publishedAt) VALUES (TIMESTAMP '#DateFormat(pastDate, "yyyy-mm-dd")# #TimeFormat(pastDate, "HH:mm:ss")#')");  ✅ Cross-database!
```

## Migration Structure

### Basic Migration Template

```cfm
component extends="wheels.migrator.Migration" {

    function up() {
        transaction {
            try {
                // Your migration code here

            } catch (any e) {
                local.exception = e;
            }

            if (StructKeyExists(local, "exception")) {
                transaction action="rollback";
                Throw(
                    errorCode="1",
                    detail=local.exception.detail,
                    message=local.exception.message,
                    type="any"
                );
            } else {
                transaction action="commit";
            }
        }
    }

    function down() {
        // Rollback code here
    }
}
```

## Create Table Migration

```cfm
component extends="wheels.migrator.Migration" {

    function up() {
        transaction {
            try {
                // Create table
                t = createTable(name="posts", force=false);

                // String columns
                t.string(columnNames="title", allowNull=false, limit=200);
                t.string(columnNames="slug", allowNull=false, limit=200);

                // Text columns
                t.text(columnNames="content", allowNull=false);
                t.text(columnNames="excerpt", allowNull=true);

                // Integer columns
                t.integer(columnNames="viewCount", default=0);
                t.integer(columnNames="userId", allowNull=false);

                // Boolean columns
                t.boolean(columnNames="published", default=false);

                // DateTime columns
                t.datetime(columnNames="publishedAt", allowNull=true);

                // Timestamps (createdAt, updatedAt)
                t.timestamps();

                // Create the table
                t.create();

                // Add indexes
                addIndex(table="posts", columnNames="slug", unique=true);
                addIndex(table="posts", columnNames="userId");
                addIndex(table="posts", columnNames="published,publishedAt");

                // Add foreign key
                addForeignKey(
                    table="posts",
                    referenceTable="users",
                    column="userId",
                    referenceColumn="id",
                    onDelete="cascade"
                );

            } catch (any e) {
                local.exception = e;
            }

            if (StructKeyExists(local, "exception")) {
                transaction action="rollback";
                Throw(
                    errorCode="1",
                    detail=local.exception.detail,
                    message=local.exception.message,
                    type="any"
                );
            } else {
                transaction action="commit";
            }
        }
    }

    function down() {
        dropTable("posts");
    }
}
```

## Alter Table Migration

```cfm
component extends="wheels.migrator.Migration" {

    function up() {
        transaction {
            try {
                // Add column
                addColumn(
                    table="posts",
                    columnType="string",
                    columnName="metaDescription",
                    limit=300,
                    allowNull=true
                );

                // Change column
                changeColumn(
                    table="posts",
                    columnName="title",
                    columnType="string",
                    limit=255,  // Changed from 200
                    allowNull=false
                );

                // Rename column
                renameColumn(
                    table="posts",
                    oldColumnName="summary",
                    newColumnName="excerpt"
                );

                // Remove column
                removeColumn(table="posts", columnName="oldField");

                // Add index
                addIndex(table="posts", columnNames="metaDescription");

            } catch (any e) {
                local.exception = e;
            }

            if (StructKeyExists(local, "exception")) {
                transaction action="rollback";
                Throw(
                    errorCode="1",
                    detail=local.exception.detail,
                    message=local.exception.message,
                    type="any"
                );
            } else {
                transaction action="commit";
            }
        }
    }

    function down() {
        removeColumn(table="posts", columnName="metaDescription");
        // Reverse other changes...
    }
}
```

## Data Migration (Seed Data)

### Database-Agnostic Date Formatting

```cfm
component extends="wheels.migrator.Migration" {

    function up() {
        transaction {
            try {
                // CORRECT: Use CFML date functions
                var now = Now();
                var day1 = DateAdd("d", -7, now);
                var day2 = DateAdd("d", -6, now);
                var day3 = DateAdd("d", -5, now);

                // Format dates for SQL
                var nowFormatted = "TIMESTAMP '#DateFormat(now, "yyyy-mm-dd")# #TimeFormat(now, "HH:mm:ss")#'";
                var day1Formatted = "TIMESTAMP '#DateFormat(day1, "yyyy-mm-dd")# #TimeFormat(day1, "HH:mm:ss")#'";
                var day2Formatted = "TIMESTAMP '#DateFormat(day2, "yyyy-mm-dd")# #TimeFormat(day2, "HH:mm:ss")#'";

                // Insert data
                execute("
                    INSERT INTO posts (title, slug, content, published, publishedAt, createdAt, updatedAt)
                    VALUES (
                        'Getting Started with HTMX',
                        'getting-started-with-htmx',
                        '<p>HTMX is a modern approach to building web applications...</p>',
                        1,
                        #day1Formatted#,
                        #day1Formatted#,
                        #day1Formatted#
                    )
                ");

                execute("