sql-conventions-miner

name	sql-conventions-miner
description	One-time skill to mine the SQL migrations repo's git history and produce draft conventions.md, templates/, examples/, and anti-patterns.md for the sql-migration runtime skill. Trigger phrases like "mine SQL history", "refresh conventions", "bootstrap sql-migration artifacts".

sql-conventions-miner

Operator

Run by a human (Ops / DBA), not per ticket. This is an offline, batch-style skill whose outputs are drafts that go through DBA review before the runtime sql-migration skill picks them up.

Inputs

• Path to a full local clone of the migrations repo (so git log, git blame, git show work).
• Optional date or tag range (e.g. --since=2023-01-01, --tag-range v3.0..HEAD) to constrain the sample.

Method

1. Stratified sample. Pull 50–100 migrations across history, stratified so each change-type cluster (see step 2) is represented. Avoid only sampling recent files.
2. Cluster by change type via DDL keyword heuristics (ALTER TABLE
   • ADD COLUMN, ALTER TABLE + DROP COLUMN, CREATE INDEX, backfill UPDATE, rename, partition split, etc.). Record cluster sizes.
3. Per cluster, emit templates. Extract the common header shape, transaction wrapping, idempotency idioms, and engine-specific flags. Write to skills/sql-migration/templates/<type>.forward.sql and .rollback.sql. Include the annotated trigger phrases and failure modes in each template's header.
4. Failure-mode mining. Walk git log --grep=revert and hotfix commits. Pair each revert/hotfix with its original commit to enumerate real failure modes. Emit skills/sql-migration/anti-patterns.md with commit SHAs for each entry.
5. Draft conventions.md covering: naming, directory placement, file header, transaction wrapping, idempotency guards, batch-size thresholds for backfills, engine-specific notes. Each section cites the sampled files that support it.
6. Copy 5–10 canonical scripts into skills/sql-migration/examples/ with short annotations that explain why the file is canonical (clean header, idiomatic guard, correct batch size, etc.).

Hard rules

• Produces drafts only. A DBA must review and sign off before the runtime skill treats the output as authoritative.
• Does not overwrite existing non-empty artifacts unless invoked with --force. When --force is passed, keep a backup at <path>.bak.<YYYYMMDD>.
• Read-only on the target repo. No commits, no pushes. Operator runs the miner locally, reviews the diff, opens the MR by hand.