sql-migration

name	sql-migration
description	Turn a Jira ticket into a draft GitLab MR containing a paired forward + rollback SQL migration that follows this repo's conventions. Trigger on phrases like "work <JIRA-KEY>", "create migration for ticket", "draft SQL for <KEY>".

sql-migration

Inputs

A Jira issue key in $JIRA_PROJECT_KEY (regex: ^${JIRA_PROJECT_KEY}-[0-9]+$). Nothing else.

Hard rules (no exceptions)

1. Never execute generated SQL against any database. Reads only, via ./bin/db-query.
2. Never write outside db/migrations/forward and db/migrations/rollback, and only via ./bin/migration-write.
3. Never push to main or master. Branches must be feature/<JIRA-KEY>-<slug>.
4. Always open MRs as draft. Use ./bin/gitlab-mr — never invoke glab directly.
5. If the ticket is ambiguous or no template matches, STOP. Do not freestyle SQL.
6. If rollback cannot be byte-identical, emit IRREVERSIBLE: <reason> at the top of the rollback file and STOP for a human override.
7. The only permitted Jira write is transition To Do → In Progress via ./bin/jira-transition.
8. All external calls go through bin/. If a needed wrapper is missing, STOP and surface the gap. Never substitute a raw command.

Workflow

1. ./bin/jira-read <KEY> → parse the returned JSON.
2. ./bin/jira-transition <KEY> "In Progress".
3. Spawn the schema-discovery subagent (see subagents/schema-discovery.md). It replays db/migrations/forward/ in order and may use ./bin/db-query for narrow validation.
4. Classify the change against templates/. If no template matches, STOP.
5. Generate forward + rollback SQL in a scratch dir using the matched template and the rules in conventions.md. Fill headers with the JIRA key, summary, author=bot, date, reversible flag.
6. Run every item in checklist.md. Fail closed: if any item cannot be satisfied, STOP.
7. ./bin/migration-write forward <name> <scratch-forward-path> then ./bin/migration-write rollback <name> <scratch-rollback-path>. The forward and rollback basenames must match.
8. Spawn the code-review subagent (see subagents/code-review.md). If it flags issues, revise once and re-run the checklist. If it flags again, STOP.
9. git checkout -b feature/<KEY>-<slug>; git add db/migrations/; git commit -m "<KEY>: <summary>"; git push origin feature/<KEY>-<slug>.
10. Prepare an MR description in a file (e.g. .scratch/mr.md) that links the ticket, summarises the change, and lists the checklist results. Then: ./bin/gitlab-mr "<KEY>: <summary>" .scratch/mr.md.
11. STOP. Do not mark the MR ready. Do not merge. Humans own both.

Feedback loop

When a human reviewer edits the generated SQL before merging, capture the diff under examples/ using the naming in examples/README.md together with a one-line rationale. The miner skill folds these back into conventions.md and anti-patterns.md on its next run.