---
name: create-flyway-migration
description: Generate Flyway migration SQL files for H2 and Oracle.
---

# Skill: create-flyway-migration

Generate Flyway migration SQL files for H2 and Oracle.

## Invoked by

💻 Code mode

## Required Inputs

| Input | Source | Example |
|-------|--------|---------|
| `TICKET_KEY` | Jira issue key | `ESIDEPAISY-12081` |
| `MODULE` | PAISY module name | `eau`, `eubp`, `svmeldungen`, `dabpv` |
| `ENTITY_NAME` | Table or entity being changed | `eau_rueckmeldung`, `eubp_archiv` |
| `CHANGE_TYPE` | Type of DDL change | `CREATE TABLE`, `ALTER TABLE`, `CREATE INDEX` |

## Output

- H2 migration: `java/modules/cs-modules/<MODULE>/src/main/resources/db/migration/H2/V{timestamp}__C_{revision}_{entity}.sql`
- Oracle migration: `java/modules/cs-modules/<MODULE>/src/main/resources/db/migration/ORACLE/V{timestamp}__C_{revision}_{entity}.sql`

## Steps

### 1. Determine timestamp

Generate the Flyway version timestamp in the required format:

```bash
date -u +"%Y_%m_%d_%H_%M_%S"
```

Result format: `2026_04_23_10_06_07`

### 2. Get app revision

Read the current app revision from `version.json` in the repo root:

```bash
cat version.json
```

Or extract from the parent POM:

```bash
grep -A1 '<revision>' java/pom.xml | head -2
```

The revision is the major version number (e.g., `123` from `123.4.2`).

### 3. Construct filename

```
V{timestamp}__C_{revision}_{entity_name}.sql
```

Example: `V2026_04_23_10_06_07__C_123_eau_rueckmeldung.sql`

**Note:** Double underscore `__` between version and description is mandatory Flyway convention.

### 4. Determine migration directories

Locate the module's migration directories:

```bash
ls java/modules/cs-modules/<MODULE>/src/main/resources/db/migration/
```

Expected structure:
- `db/migration/H2/` — H2-specific migrations
- `db/migration/ORACLE/` — Oracle-specific migrations

If only one directory exists, the module may use a shared migration path. Check the module's `application.properties` for `spring.flyway.locations`.

### 5. Create H2 migration

H2 supports `IF NOT EXISTS` / `IF EXISTS` clauses:

```sql
-- CREATE TABLE
CREATE TABLE IF NOT EXISTS <ENTITY_NAME> (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    -- columns...
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ALTER TABLE (add column)
ALTER TABLE <ENTITY_NAME> ADD COLUMN IF NOT EXISTS <column_name> <type>;

-- CREATE INDEX
CREATE INDEX IF NOT EXISTS idx_<entity>_<column> ON <ENTITY_NAME>(<column>);

-- DROP COLUMN
ALTER TABLE <ENTITY_NAME> DROP COLUMN IF EXISTS <column_name>;
```

### 6. Create Oracle migration

Oracle does NOT support `IF NOT EXISTS` — use PL/SQL blocks for idempotency when needed:

```sql
-- CREATE TABLE (simple — fails if exists, which is expected for versioned migrations)
CREATE TABLE <ENTITY_NAME> (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- columns...
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- ALTER TABLE (add column — idempotent wrapper)
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE <ENTITY_NAME> ADD <column_name> <type>';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -1430 THEN NULL; -- column already exists
        ELSE RAISE;
        END IF;
END;
/

-- CREATE INDEX
CREATE INDEX idx_<entity>_<column> ON <ENTITY_NAME>(<column>);
```

### 7. Type mapping reference

| Java/H2 Type | Oracle Type | Notes |
|--------------|-------------|-------|
| `BIGINT` | `NUMBER(19)` | Primary keys |
| `INTEGER` | `NUMBER(10)` | Standard integers |
| `VARCHAR(n)` | `VARCHAR2(n)` | Strings |
| `BOOLEAN` | `NUMBER(1)` | Oracle has no native boolean |
| `TIMESTAMP` | `TIMESTAMP` | Same |
| `CLOB` | `CLOB` | Same |
| `BLOB` | `BLOB` | Same |
| `AUTO_INCREMENT` | `GENERATED ALWAYS AS IDENTITY` | Identity columns |
| `CURRENT_TIMESTAMP` | `SYSTIMESTAMP` | Default timestamp |

### 8. Register migration location (if new module)

If this is the first migration for the module, ensure the Flyway location is configured in the module's properties or EMFactory:

```java
// In EMFactory or FlywayController setup
flyway.migrate("<MODULE>", "<timestamp>");
```

Check existing modules for the pattern:
```bash
grep -rn "flyway.migrate" java/modules/cs-modules/<MODULE>/src/
```

### 9. Verify migration

Run the module's Flyway migration test to ensure the SQL is valid:

```bash
cd /Users/pplate/git/paisy-<TICKET_KEY>
mvn test -pl java/modules/cs-modules/<MODULE> -Dtest="*FlywayMigrationTest*" -f java/pom.xml
```

### 10. Store in BigMind

```python
memory_store_fact(
    category="codebase",
    fact=f"{TICKET_KEY}: Flyway migration V{timestamp}__C_{revision}_{entity} created for H2 + Oracle in {MODULE}"
)
```

## Expected Output

- H2 migration SQL file created in `db/migration/H2/`
- Oracle migration SQL file created in `db/migration/ORACLE/`
- Both files use correct Flyway naming convention
- Migration test passes

## Error Handling

| Error | Resolution |
|-------|------------|
| Duplicate version timestamp | Increment the seconds portion by 1 |
| H2 syntax error in test | Check H2-specific syntax (e.g., `IDENTITY` vs `AUTO_INCREMENT`) |
| Oracle PL/SQL block error | Ensure `/` terminator after PL/SQL blocks |
| Migration already applied | Flyway won't re-run versioned migrations — create a new version |
| Missing migration directory | Create `db/migration/H2/` and/or `db/migration/ORACLE/` directories |

## Conventions

- One migration file per logical change (don't combine unrelated DDL)
- H2 and Oracle files must produce equivalent schema
- Use `IF NOT EXISTS` / `IF EXISTS` in H2 for idempotency
- Oracle versioned migrations don't need idempotency (Flyway tracks them)
- Repeatable migrations use `R__` prefix instead of `V{timestamp}__`
- Comments in SQL: `-- <description>` at the top of each file

## Language

- SQL comments: English
- Column names: English (snake_case)
- Table names: English (snake_case), prefixed with module abbreviation where applicable