Qmigrationdevcheck

Alembic Migration Chain Validator

You are validating that Alembic migrations on the current branch are compatible with develop — no multiple heads now, no multi-head after merge, no chain breaks, no {{COMPANY_SLUG}}-specific violations.

> Multi-repo contract: This skill operates on the repos in $SKILLS_ROOT/qship/repos.json whose has_migrations flag is true. Single-repo users with one alembic chain get a degenerate-but-correct check (one repo in the iteration). Multi-repo users get full cross-schema validation. Resolve the list at the start of every invocation: > > ``bash > REPOS_WITH_MIGRATIONS=$(jq -r '.[] | select(.has_migrations==true) | .name' "$SKILLS_ROOT/qship/repos.json") > ``

Input: $ARGUMENTS may contain:

• A specific repo name (must match one of the has_migrations==true entries) or all (default — every flagged repo)
• A caller flag: mode=qshipp2 (report-only) or mode=user (apply fixes, default)

Examples:

• /qmigrationdevcheck → default user mode, all repos with has_migrations==true, applies fixes interactively
• /qmigrationdevcheck → user mode, just that repo (must be flagged for migrations)
• /qmigrationdevcheck mode=qshipp2 → invoked by /qshipp2; REPORT ONLY, do not apply fixes

Mode Semantics — READ FIRST

The caller mode fundamentally changes behaviour:

| Mode | Detection | Fix application | Output | |---|---|---|---| | user (default) | Runs all checks | Applies fixes interactively — confirms before risky changes | Report + applied changes summary | | qshipp2 | Runs all checks | NEVER applies fixes. Report-only. | Report structured for the caller to post as a PR comment |

In qshipp2 mode, skip Step 8 entirely. The orchestrator decides what to do with findings (post PR comment, block the pipeline, etc.).

In user mode, Step 8 is mandatory after any FAIL — detect-and-describe-without-fixing is not the point of the direct invocation.

Parse mode AND repo selector from $ARGUMENTS:

MODE="user"
REPO="all"
for tok in $ARGUMENTS; do
  case "$tok" in
    mode=qshipp2) MODE="qshipp2" ;;
    mode=user)    MODE="user" ;;
    all) REPO="all" ;;
    *) if jq -e --arg n "$tok" '.[] | select(.name==$n and .has_migrations==true)' "$SKILLS_ROOT/qship/repos.json" >/dev/null 2>&1; then REPO="$tok"; fi ;;
  esac
done

If REPO != all, restrict every subsequent step to that repo's directory. If unset, scan all repos that contain an alembic/ directory.

Protocol

Step 1: Identify Migration Repos

Resolve the repos to check from config — every entry flagged has_migrations==true:

for repo in $(jq -r '.[] | select(.has_migrations==true) | .name' "$SKILLS_ROOT/qship/repos.json"); do
  [ -d "{{CODEBASE_ROOT}}/$repo/alembic" ] && echo "will check: $repo"
done

If $ARGUMENTS named a specific repo (and it's in the has_migrations set), check only that one.

Step 2: Skip if No Migration Changes

git fetch origin develop --quiet
git diff --name-only origin/develop..HEAD -- alembic/versions/ | head -5

If empty → stop, report "No migration changes on this branch — nothing to check".

Step 3: Check Current Branch Heads

cd 
alembic heads

Expected: Single head. If multiple heads → FAIL (Check 3a).

Step 4: Compare with Develop

# Migrations only on our branch (new)
git ls-tree -r origin/develop -- alembic/versions/ | awk '{print $4}' | sort -u > /tmp/develop_migrations.txt
git ls-tree -r HEAD              -- alembic/versions/ | awk '{print $4}' | sort -u > /tmp/head_migrations.txt
comm -23 /tmp/head_migrations.txt /tmp/develop_migrations.txt  # our-branch-only

# Migrations added to develop since branch-point (indicate possible conflict)
git log origin/develop --not HEAD --oneline -- alembic/versions/

For each branch-only migration file, read its down_revision. Run:

grep -E "^(revision|down_revision)" 

Step 5: Detect Conflicts

Run every check. Don't stop at the first failure — accumulate issues for the final report.

Check 3a — Multiple heads right now

Two migrations share the same down_revision. Caught by alembic heads in Step 3.

Check 3b — Missing parent

A migration references a down_revision revision_id that doesn't exist in the tree. Caught by alembic check.

Check 3c — Duplicate revision IDs

Two files with the same revision hash. Caught by alembic check.

Check 3d — Multi-head forecast after merge

Another PR has landed a migration on develop since you branched. If our new migration's down_revision equals a revision that is no longer develop's head, merging creates multi-head immediately.

# Compare the down_revision of our earliest new migration against develop's current head
DEVELOP_HEAD=$(git show origin/develop:alembic/versions/ 2>/dev/null ; \
               # or: run `alembic heads` against a checkout of develop)

Detection (runnable):

# Develop's head revision ID (from a fresh worktree, no checkout needed)
DEVELOP_HEAD=$(git fetch origin develop --quiet && \
  git -c advice.detachedHead=false worktree add -q /tmp/qmdc-develop origin/develop 2>/dev/null && \
  ( cd /tmp/qmdc-develop && alembic heads 2>/dev/null | awk '{print $1}' | head -1 ) ; \
  git worktree remove --force /tmp/qmdc-develop >/dev/null 2>&1)

# Our earliest new migration's down_revision
OUR_PARENT=$(grep -E "^down_revision\s*=" $(comm -23 /tmp/head_migrations.txt /tmp/develop_migrations.txt | head -1) \
  | sed -E "s/.*=[[:space:]]*['\"]([^'\"]+)['\"].*/\1/")

[ "$OUR_PARENT" != "$DEVELOP_HEAD" ] && echo "FAIL 3d: parent=$OUR_PARENT develop=$DEVELOP_HEAD"

If our parent ≠ develop head → FAIL — need rebase or empty merge migration.

Check 3e — Cross-schema FK (only when you have 2+ schemas)

If your repos own separate Postgres schemas (each repo's schema field in repos.json), a migration in one repo must NOT create a foreign key into another repo's schema — the referenced tables may not exist when that repo's migrations run (CI typically runs --schema all in dependency order). Use a plain UUID column + application-layer integrity instead.

# For each other repo's schema, flag cross-schema FKs in this repo's new migrations:
OTHER_SCHEMAS=$(jq -r '.[] | select(.schema != null) | .schema' "$SKILLS_ROOT/qship/repos.json")
for s in $OTHER_SCHEMAS; do
  grep -E "ForeignKey\(['\"]$s\.|referent_schema=['\"]$s" alembic/versions/*.py
done

If any match on branch-new files → FAIL. (Single-schema projects: this check is a no-op.)

Check 3f — Merge migration hygiene

Per CLAUDE.md: merge migrations (those with down_revision = (rev1, rev2, ...)) must be empty — pass in both upgrade() and downgrade(). Schema changes go in a separate migration that depends on the merge.

For any branch-new migration whose down_revision is a tuple, open the file:

• upgrade() body must be pass (plus comments)
• downgrade() body must be pass
• No op.* calls anywhere

If violated → FAIL.

Check 3g — Cross-repo migration ordering (only when 2+ repos have migrations)

If two of your repos both have new migrations on their feature branches and one depends on the other (e.g. a downstream service references a table a shared library just added), check that the dependent repo's migration doesn't reference tables/columns the depended-on repo only just introduced — unless that migration is already merged to develop.

# In the depended-on repo's branch migrations, list newly added tables:
grep -E "op\.(add_column|create_table)\s*\(['\"]" 
# Then grep the dependent repo's branch migrations for those table names.

If found → warn: the depended-on repo must merge and deploy first. Document the merge order in both PR descriptions. (Single-repo projects: no-op.)

Check 3h — Untrimmed autogen boilerplate (advisory)

Smoking gun that drift wasn't reviewed:

grep -l "# ### commands auto generated by Alembic - please adjust" alembic/versions/

Not always a real problem but prompts the reviewer to verify the migration was actually trimmed to PR scope.

Check 3i — Orphaned tenant stamps (post-merge CI risk)

Prevents run_all_migrations.py from failing on CI because a tenant's alembic_version points to a revision that no longer exists on disk. Two recurring root causes — deleted-merge consolidation, or a feature-branch migration that was run against staging then never merged.

Detection (read-only): for each active row in app.account_registry, connect via AccountRegistry + DBProviderClient.build_connection_string(..., pooled=True) (do NOT re-implement) and read .alembic_version for each schema your has_migrations repos own (the schema field in repos.json). FAIL any (tenant, schema, revision) where revision is not resolvable in alembic history on the current branch.

Use the bundled scanner — it implements exactly the contract above so you don't re-derive it:

# From any cwd. Run with the {{PRIMARY_REPO_NAME}} venv (has alembic + sqlalchemy + dotenv installed).
/venv/bin/python \
  ~/.claude/commands/qmigrationdevcheck/scripts/scan_orphan_tenants.py \
  --core-root /{{PRIMARY_REPO_NAME}} \
  --{{PRIMARY_REPO_NAME}}-root /{{PRIMARY_REPO_NAME}}

Output is a tenant×schema list with any orphan revisions, plus a summary line Tenants scanned / Orphan stamps / Skipped tenants. Exit code 0 = all clean, 1 = orphan(s) found, 2 = operator error (missing env / wrong paths).

How it picks the env:

1. Loads /.env (typically has DB_PROVIDER_API_KEY).
2. Loads /.env.staging with override=True (typically has the staging GLOBAL_DATABASE_URL). Staging takes precedence so the scan walks the multi-tenant staging registry, not your single-tenant local DB.

If .env.staging is missing AND GLOBAL_DATABASE_URL points at a single-tenant local DB (no account_registry table), the scanner will fail with a clear error — that's the intended signal that you don't have the credentials for this check on this host.

Env prereq: GLOBAL_DATABASE_URL (staging), DB_PROVIDER_API_KEY, optional DATABASE_OWNER (default {{DB_OWNER_ROLE}}). If either {{PRIMARYREPONAME}} env is missing, emit WARN/SKIPPED — never crash the report.

For root-cause classification and the fix, jump to Resolution Playbook → Orphaned tenant stamps.

Step 6: Validate Chain Integrity

# Full chain validation
alembic history --verbose 2>&1 | head -30

# Modern alembic check (catches unapplied autogen drift)
alembic check 2>&1

Step 7: Report

## Migration Chain Check: 

Status: PASS / FAIL / WARN

### Heads
- Current branch:  ()
- Origin develop:   ()
- Multiple heads now: YES/NO
- Multi-head after merge: YES/NO

### Chain
- Total migrations:   N
- Our branch adds:    N new migrations
- Develop added since branch-point: N migrations

### Check Results
  3a Multiple heads now            [PASS/FAIL]
  3b Missing parent                [PASS/FAIL]
  3c Duplicate revision IDs        [PASS/FAIL]
  3d Multi-head after merge        [PASS/FAIL]
  3e Cross-schema FK ({{PRIMARY_REPO_NAME}})    [PASS/FAIL/N/A]
  3f Merge migration hygiene       [PASS/FAIL/N/A]
  3g Core→{{PRIMARY_REPO_NAME}} ordering        [PASS/FAIL/WARN/N/A]
  3h Untrimmed autogen boilerplate [WARN/N/A]
  3i Orphaned tenant stamps        [PASS/FAIL/SKIPPED]

### Orphaned tenants (if any, 3i)
      stamped_at=  (not resolvable on branch)

### Issues (if any)
1. [Issue description]
   Resolution: [playbook block below]

---

Step 8: Apply Fixes (user mode ONLY — skip in qshipp2 mode)

In MODE=qshipp2: STOP HERE. Return the Step 7 report to the caller. Do not apply any fixes. Do not edit any files. Do not create any commits. The caller will post the report as a PR comment.

In MODE=user: For each FAILing check, apply the fix. Auto-apply the safe ones; confirm with the user for risky ones.

8.1 Auto-apply (no confirmation needed)

Run these without asking. They are mechanical and reversible.

Untrimmed autogen boilerplate (Check 3h): skip auto-fix. Trimming requires PR-scope knowledge this skill doesn't have. Record as "manual review recommended" and continue.

8.2 Confirm-then-apply (ask once, apply all)

Show the user the intended edits as a diff summary, then ask: "Apply these fixes? (y/n)" — one confirmation covers all pending fixes so the user isn't asked per file.

Multi-head forecast after merge (Check 3d) — simple case:

Applies when: exactly one new migration on branch; exactly one develop head that is not our parent.

Rebase the stale down_revision to develop's current head.

# Detected: our migration  has down_revision=
# Develop head is now 
# Proposed: edit 's down_revision to 

After confirmation, edit the file in place, then re-run alembic heads to verify single head.

Multi-head forecast after merge (Check 3d) — complex case (more than one new migration on branch, or multiple develop heads): do NOT auto-rebase. Fall through to 8.3.

Multiple heads currently on the branch (Check 3a): almost always a rebase mistake. Show the conflicting migrations, recommend manual rebase via the Resolution Playbook.

Orphaned tenant stamps (Check 3i) — stamp to nearest valid ancestor:

This is the fix that saves run_all_migrations.py from exiting non-zero on CI. For each offending (tenant, schema) pair, propose a re-stamp:

# Detected orphans:
#   ACME Corp           {{PRIMARY_REPO_NAME}}                       stamped=aaaaaaaaaaaa (not on branch)
#   Northwind Logistics {{PRIMARY_REPO_NAME}}  stamped=deadbeefcafe (not on branch)
#
# Proposed fix (one confirmation covers all):
#   ACME Corp           {{PRIMARY_REPO_NAME}}                       -> 
#   Northwind Logistics {{PRIMARY_REPO_NAME}}  -> 

Choosing the target revision:

1. Prefer the nearest ancestor on the current branch to the orphan's original lineage — reconstruct by inspecting the orphan migration file in git history (git log --all -- alembic/versions//**.py) and walking its down_revision tuple until one of the parents IS in the current branch's alembic history.
2. If the orphan was an empty merge (upgrade/downgrade bodies are pass) — common pattern when an old branch's merge migration carried no real DDL — any of its ancestors on the current branch is schema-equivalent. Default to develop's head for that schema minus this PR's new migrations (i.e., develop's head at the branch point).
3. If the orphan lives on a feature/fix branch only (developer applied it against staging for testing; never merged to develop) — treat it like case 1 but use git log --all --remotes to find the migration file, then walk back to its parent. If the migration's DDL is already on develop under a different revision ID, re-stamp to that develop revision. If the DDL is not on develop AND the tenant DB actually has the schema changes, the fix requires a code companion: either land the migration on develop first, or create a downgrade to undo the partial state before re-stamping.
4. If the orphan carried real DDL and the DB has the changes applied (and there is no develop-side equivalent to stamp to) — STOP and fall through to 8.3. Re-stamping loses state. The user needs to decide whether to apply the missing migration manually or abandon the tenant.

How to apply (after a single user "y" covers the full plan):

Use the bundled script — qmigrationdevcheck/scripts/stamp_orphan_tenants.py. It encapsulates the AccountRegistry + DBProviderClient lookup and the command.stamp(..., purge=True) call so you don't re-derive it on every invocation. Pass a JSON plan of {tenant_name, schema, target_revision} entries:

python ~/.claude/commands/qmigrationdevcheck/scripts/stamp_orphan_tenants.py \
  --repo-root  --plan /tmp/orphan_plan.json

If the host blocks writes from inside the repo tree, c

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: 3awny
• Source: 3awny/qship
• License: MIT

Install and usage instructions live in the source repository linked above.