Save this content to: .claude/skills/migration-squash/SKILL.md

# Migration Squash — Create & Review

A squash folds every migration file into one schema baseline and **deletes the migration files**. The dump becomes the only record of that schema afterwards, so a wrong dump silently breaks every fresh install. This skill holds the conventions and the checks that catch the defects squash PRs actually ship with.

## When to use this skill

- Creating a migration squash
- **Reviewing a "Squash migrations" PR before merge** — run every check below
- Any change touching the tracked schema dump or `schema:dump --prune`

## Conventions (non-obvious — get these wrong and it breaks)

- **Squash at a sync point.** A squash rewrites the baseline that *every* branch builds from, so do it when the target branch and the active development branch are aligned — not mid-flight while feature branches carry migrations the baseline won't include. The target branch is whichever branch the baseline represents (commonly the default/release branch — Base-branch resolution is the same one the `pull-requests` skill uses).
- **Know which file is tracked, and how it's loaded.** Laravel's `schema:dump` writes `database/schema/<connection>-schema.sql` (e.g. `mysql-schema.sql`) and `MigrateCommand::schemaPath()` loads that file on a fresh `migrate`. That standard `.sql` file is the tracked baseline for most projects — verify which path your project commits before you start.
  - **Optional project variant:** some projects track a non-default filename (e.g. `mysql-schema.dump`) because `schemaPath()` prefers a `.dump` over a `.sql`. If yours does, the process gains a rename step: dump → **rename the written `.sql` to the tracked name**. Skipping the rename leaves an untracked `.sql` plus a stale tracked file. If your project tracks the standard `.sql`, there is no rename.
- **CI may not load the schema on a squash PR.** Confirm which of your checks actually rebuild a DB from the baseline. If a squash PR targets a branch your test workflows skip (or only external scanners like Snyk report), then *none* of them load the schema — an incomplete or contaminated dump passes green and the checks below are the **only** gate.

## Creating a squash correctly

The dump copies your **local DB's** schema and `migrations` table verbatim, so it is only correct if that DB matches the target branch's migration set **exactly** — not *behind* (missing recent migrations → incomplete dump) and not *ahead* (your everyday DB usually carries development-branch-only migrations → contaminated dump). Do the pre-flight before dumping.

### Pre-flight — make the DB match the target exactly

```bash
git checkout <target-branch> && git pull
```

**Safest: dump from a clean, target-only DB**, not your day-to-day dev DB (which is almost always migrated to a development branch). Point `DB_DATABASE` at a throwaway database, then rebuild it as pure target state by loading the current baseline and running the target branch's migrations onto it. This is the test-runner-style "build it fresh, throw it away" path the `database-safety` guideline blesses — never wipe or `migrate:fresh` a dev or shared DB to do this.

A CLI rebuild runs the migrations outside the test runner. A data-migration that guards its body with a test-mode check (for example `if (app()->runningUnitTests()) { return; }`) therefore executes for real on the throwaway DB, and it fails when it needs rows a seeder owns — migrations run before seeders. The failure aborts the run, so every migration after it never applies and the dump comes out incomplete without saying so. If that happens, migrate the throwaway DB **through the test runner** instead: point the test environment at that same throwaway database (not `:memory:`, and not the suite's own test DB), run one test in a suite that refreshes the database, then dump from it. The dump reads the connection the CLI uses, so both must name the same database or you dump the unmigrated one.

**If you must dump from your working DB, verify it first:**

```bash
# 1. Not BEHIND — no target migration left unapplied:
php artisan migrate:status | grep -iq pending && echo "BEHIND: run php artisan migrate" || echo "not behind"
php artisan migrate

# 2. Not AHEAD — the DB must carry nothing beyond the target's legitimate migration set.
#    Legitimate = (records already in the target's baseline dump, whose files earlier squashes
#    pruned) ∪ (the target's current migration files). Compare the DB's *applied* migrations
#    against that union — querying the migrations table catches a migration applied from an
#    abandoned/local/renamed branch that a dev-branch diff would miss, while the baseline union
#    avoids false-flagging legitimate pruned-but-recorded history. Any line = applied-but-not-
#    legitimate → contamination, do NOT dump from this DB.
comm -13 \
  <(sort -u <(git show origin/<target-branch>:<schema-path> | grep -oE '[0-9]{4}_[0-9]{2}_[0-9]{2}_[0-9]{6}_[A-Za-z0-9_]+') \
            <(git ls-tree -r --name-only origin/<target-branch> database/migrations/ | sed 's#.*/##;s#\.php$##')) \
  <(php artisan tinker --execute="echo DB::table('migrations')->orderBy('migration')->pluck('migration')->implode(PHP_EOL);" | sort -u)
```

### Dump, (rename,) clean up

```bash
php artisan schema:dump --prune     # writes <connection>-schema.sql, deletes migration files
# Optional project variant — only if your project tracks a non-default schema filename:
# mv database/schema/mysql-schema.sql database/schema/mysql-schema.dump
```

If the squash prunes any **data-migration** (one that seeded or transformed rows, not just schema), those rows are **not** in `schema:dump`'s output — add them to whatever seeds that table on a fresh DB (a seeder, a factory, or your schema-load hook), or the next fresh build / CI loses them (see review check #5).

Another option is cheaper and keeps the rows identical, and it fits a migration that only inserts its own rows: keep the migration file, and delete its row from the dump's `migrations` INSERTs. It does not fit one that transforms rows another migration wrote — on a fresh DB those rows are gone, so the replay changes nothing or fails. The migration then runs again on every fresh DB and writes the same rows. Existing deployments already recorded it, so it never re-runs there. Check #3 reports such a file as `MISSING RECORD` by design — confirm every hit is one of these, not a real gap.

Before you prune, find the tests that load a migration file by path — they break when the file goes:

```bash
grep -rn "migrations/" tests/     # each hit either survives the prune or the test goes
```

Keep a file the prune would delete when a test still loads it, or drop the test. Also keep `database/migrations/` itself as a directory (a `.gitkeep`) when a tool in the project lists that path — a static analyser configured with it refuses to run when the path is gone.

Then create the branch and open the PR — verifying your own dump against the review checklist below before pushing.

## Reviewing / verifying a squash — run EVERY check

Capture the candidate schema dump's **contents** in `DUMP` (the checks below pipe `$DUMP`, not a path). Substitute `<PR>` with the PR number and `<schema-path>` with your tracked baseline file. The fetch and base-check commands below assume the change is a **GitHub** PR (`gh` + the `pull/<PR>/head` ref); on another host, check out the candidate branch however that host exposes it and substitute its ref for `pr-<PR>` (and read the target from the host's PR view) — or just review your **local working tree** with the alternative shown:

```bash
git fetch origin <target-branch>                                                       # refresh the base ref the checks below compare against
git fetch origin "pull/<PR>/head:pr-<PR>" && DUMP=$(git show "pr-<PR>:<schema-path>")   # GitHub
# or local working tree:  DUMP=$(cat <schema-path>)
```

Fetch the target first — the completeness and contamination checks compare against `origin/<target-branch>`, so a stale local copy would let a squash that's missing a recently-added target migration pass.

**1. Target branch is correct** — confirm the candidate targets the baseline's branch. On GitHub:

```bash
gh pr view "<PR>" --json baseRefName -q .baseRefName   # expect: the baseline's target branch
```

On another host, read the PR's target from its UI/API, or skip this check when reviewing a local working tree you already know the base of.

**2. The tracked schema file changed, with no stray untracked dump** — the PR/working tree must modify the tracked baseline file and leave **no** untracked schema file (e.g. a `mysql-schema.sql` left behind when the project tracks a renamed file).

**3. Completeness — no recent migrations missing (the most common defect).** Every deleted migration must be recorded in the dump, and the newest migrations' columns must be present. A DB behind HEAD yields a dump missing the latest migrations' *schema and records*; merging then deletes those files while their schema is absent from the baseline → fresh installs miss tables/columns and the app breaks against its own schema.

```bash
# every migration on the target must be recorded in the dump — except one deliberately kept
# so it runs again on a fresh DB (the data-migration option in #5); confirm each hit is that:
for f in $(git ls-tree -r --name-only origin/<target-branch> database/migrations/); do
  n=$(basename "$f" .php)
  printf '%s' "$DUMP" | grep -q "$n" || echo "MISSING RECORD: $n"
done
# record-count sanity: prior baseline records + folded-in file count ≈ new dump records
# (the INSERT pattern below is MySQL-flavoured — adjust the grep for your driver's dump format)
echo "new dump:   $(printf '%s' "$DUMP" | grep -c 'INSERT INTO `migrations`')"
echo "baseline:   $(git show origin/<target-branch>:<schema-path> | grep -c 'INSERT INTO `migrations`')"
echo "+ files:    $(git ls-tree -r --name-only origin/<target-branch> database/migrations/ | wc -l)"
# spot-check a few of the newest migrations' columns actually exist in the dump:
printf '%s' "$DUMP" | grep -c "<a_column_added_by_the_latest_migration>"   # expect > 0
```

**4. Contamination — no migration the target lacks leaked in.** Every migration *recorded in the dump* must have a file on the target branch. Compare the dump's recorded migration names against the target's files directly — not against a dev branch, which would miss a migration that leaked in from an abandoned/local/renamed branch.

```bash
# A correct squash records exactly: the target's existing baseline records ∪ the target's
# migration files. Anything the new dump records beyond that union is contamination. Subtracting
# the baseline records avoids false-flagging legitimate history whose files earlier squashes pruned.
comm -13 \
  <(sort -u <(git show origin/<target-branch>:<schema-path> | grep -oE '[0-9]{4}_[0-9]{2}_[0-9]{2}_[0-9]{6}_[A-Za-z0-9_]+') \
            <(git ls-tree -r --name-only origin/<target-branch> database/migrations/ | sed 's#.*/##;s#\.php$##')) \
  <(printf '%s' "$DUMP" | grep -oE '[0-9]{4}_[0-9]{2}_[0-9]{2}_[0-9]{6}_[A-Za-z0-9_]+' | sort -u)
# Empty output = clean. Spot-check any flagged name's columns are likewise absent from the dump.
```

**5. Data migrations — seeded rows preserved.** `schema:dump` captures table *structure* and the `migrations` table, but **not** the data rows any other table holds. So a pruned *data-migration* (one that `INSERT`s/`UPDATE`s rows) loses its rows on a fresh DB unless they are also carried into whatever seeds that table. Find the data-migrations among the pruned files and confirm their rows still land on a fresh DB. Run this check even when the full suite is green — a table no test asserts on loses its rows silently, and check #6 still passes:

```bash
# data-migrations among the files THIS PR deletes (they write rows, not just schema):
for f in $(git diff --name-only --diff-filter=D origin/<target-branch> "pr-<PR>" -- database/migrations/); do
  git show "origin/<target-branch>:$f" | grep -Eq 'DB::table|->insert\(|->update\(|->upsert\(' && echo "DATA MIGRATION: $f"
done
# for each, its rows must be present on a fresh DB — in the schema dump's own INSERTs, in
# the relevant seeder, or by the file being kept while its row is dropped from the dump's
# `migrations` INSERTs (so it runs again). Grep the right seed for a value it inserts:
grep -c "<a value the data-migration inserts>" "<seeder-or-seed-dump-path>"   # expect > 0
```

**6. Prove it loads a complete schema (definitive).** A fresh test DB loads the baseline (plus any schema-load seeders) with an empty migrations dir. Run the **full suite** — a single trivial test does not touch the seeded data, so it cannot catch a dropped data-migration (#5); those regressions only surface in tests that assert on seeded rows (e.g. `Failed asserting that actual size 0 matches expected size 2`). The test runner owns its databases, so recreating them here is safe (per the `database-safety` guideline):

```bash
php artisan test --parallel --recreate-databases
```

**7. Obsolete migration tests removed** — no test `require`s a deleted migration file (it would fatal with "file not found").

## Failure modes seen in real squash PRs

| Symptom | Cause | Catch with |
|---|---|---|
| Untracked schema `.sql` left beside a stale tracked dump | forgot the rename (projects tracking a non-default filename) | check #2 |
| Fresh installs miss tables/columns | dumped from a DB **behind** the target HEAD | check #3 |
| Dump stops at some migration, later ones absent | a CLI rebuild of the throwaway DB aborted on a data-migration that only guards itself in test mode | check #3 |
| Baseline has extra/unreleased columns | dumped from a **dev-branch-migrated** DB | check #4 |
| Fresh-DB / CI tests fail asserting on seeded rows (`size 0`) | a pruned **data-migration**'s rows weren't carried into the dump or a seeder | checks #5, #6 |
| Tests fatal "file not found" | a test `require`s a deleted migration | check #7 |

## Related

- A migration squash does **not** back-merge anything. If a hotfix landed directly on the target branch, confirm it's also merged into the development branch separately — otherwise dev / staging / CI still run the old versions.
- Migrations themselves must be idempotent and self-contained — see the `migrations` guideline.
- Never wipe, reset, or `migrate:fresh` a dev or shared database to build a clean dump — see the `database-safety` guideline; use a throwaway DB or the test runner's own lifecycle.