[BUG]: drizzle-kit generate derives idx from journal alone — prefix collision + silent snapshot overwrite when journal is stale vs on-disk SQL

**Verified not previously filed:** searched closed + open issues for `migration prefix collision`, `drizzle-kit generate idx`, `journal stale snapshot overwrite`, `two migrations same prefix`, `snapshot overwritten`, `lastEntryInJournal` — no matches.

**drizzle-orm version:** ^0.45.2
**drizzle-kit version:** ^0.31.10
**Other packages:** N/A
**Runtime:** Node 22, postgres dialect (Neon HTTP driver)

---

## Describe the bug

`drizzle-kit generate` derives the next migration index from the local journal alone, **without cross-checking on-disk `drizzle/*.sql`**. When the journal is stale relative to on-disk SQL (a common branch-divergence scenario — partial rebase, manual journal repair, pulled migration files without journal sync), the next `generate`:

1. Produces a new SQL file with a **prefix that collides** with an existing on-disk migration
2. **Silently overwrites** the existing `drizzle/meta/_snapshot.json` in place

Silent at author time. Detection is manual (`ls drizzle/` + `git diff drizzle/meta/`); recovery is multi-step (rename the new file + restore the overwritten snapshot from git + edit `_journal.json` to repair the id/prevId chain).

## Source location

In `node_modules/drizzle-kit/bin.cjs` (verified at ~L32926 in the 0.31.x release; line number may move in subsequent releases — the shape is what matters):

```js
const lastEntryInJournal = journal.entries[journal.entries.length - 1];
const idx = typeof lastEntryInJournal === "undefined" ? 0 : lastEntryInJournal.idx + 1;
const { prefix, tag } = prepareMigrationMetadata(idx, prefixMode, name);
// prefix = idx.toFixed(0).padStart(4, "0")
fs.writeFileSync(`${metaFolderPath}/${prefix}_snapshot.json`, ...); // overwrites without check
fs.writeFileSync(`${outFolder}/${tag}.sql`, sql); // collides without check
```

Two filesystem writes, neither preceded by an existence check against on-disk state.

## Minimum reproduction

```bash
mkdir drizzle-repro && cd drizzle-repro
npm init -y
npm install drizzle-orm@^0.45.2 drizzle-kit@^0.31.10 pg

cat > schema.ts <<'EOF'
import { pgTable, serial, text } from "drizzle-orm/pg-core";
export const a = pgTable("a", {
id: serial("id").primaryKey(),
name: text("name"),
});
EOF

cat > drizzle.config.ts <<'EOF'
import type { Config } from "drizzle-kit";
export default {
schema: "./schema.ts",
out: "./drizzle",
dialect: "postgresql",
} satisfies Config;
EOF

npx drizzle-kit generate --name initial # creates 0000_*.sql + journal entry idx=0

# Edit schema to add `email: text("email")` column
npx drizzle-kit generate --name add_email # creates 0001_*.sql + journal entry idx=1

# Simulate "journal stale vs on-disk" — analogue of partial rebase / manual repair
node -e '
const fs = require("fs");
const j = require("./drizzle/meta/_journal.json");
j.entries.pop();
fs.writeFileSync("./drizzle/meta/_journal.json", JSON.stringify(j, null, 2));
'

# State now: journal max idx = 0; on-disk max prefix = 1
ls drizzle/ # → 0000_*.sql, 0001_*.sql both present
cat drizzle/meta/_journal.json # → only idx=0 entry

# Edit schema to add `bio: text("bio")` column
npx drizzle-kit generate --name add_bio

# OBSERVED:
# drizzle-kit emits 0001_.sql — colliding prefix with existing 0001_add_email.sql
# drizzle/meta/0001_snapshot.json silently OVERWRITTEN with new schema state
git diff drizzle/meta/0001_snapshot.json # → snapshot was rewritten in place
```

## Expected behavior

drizzle-kit should either:

1. **Refuse to run** with a clear error:
`Journal stale: on-disk has migration 0001_* but journal max idx is 0000. Sync your journal (e.g., \`git pull --rebase origin master\`) or run \`drizzle-kit check\`.`
2. **OR derive `idx` from `max(journalMaxIdx, onDiskMaxPrefix) + 1`** so collisions can't occur even when the journal is stale.

Option 1 is more diagnostic (surfaces the underlying state problem to the user). Option 2 is the strictly-safer narrow change.

## Suggested fix sketch

```js
const onDiskMaxPrefix = fs.readdirSync(outFolder)
.filter(f => /^\d{4}_/.test(f))
.map(f => parseInt(f.slice(0, 4), 10))
.reduce((m, n) => Math.max(m, n), -1);

const journalMaxIdx = journal.entries.length === 0
? -1
: journal.entries[journal.entries.length - 1].idx;

if (onDiskMaxPrefix > journalMaxIdx) {
throw new Error(
`Journal stale: on-disk has migration ${pad4(onDiskMaxPrefix)}_* but journal max idx is ${pad4(journalMaxIdx)}. ` +
`Sync your journal (e.g., \`git pull --rebase origin master\`) or run \`drizzle-kit check\`.`
);
}

const idx = journalMaxIdx + 1;
```

## Common triggers

Branch-divergence scenarios that cause the journal to drift from on-disk SQL:

| Scenario | Mechanism |
|---|---|
| Branch opened pre-merge, partial rebase resolved with `--ours` strategy on `_journal.json` | Journal misses upstream entries while SQL files merge cleanly |
| Manual `_journal.json` edit during a prior repair-in-progress left idx < on-disk max prefix | Operator removed an entry to retry generation |
| Two parallel branches each run `generate` before either merges | Both produce idx N+1 — collision surfaces at second branch's PR |

The first two are local-author windows that drizzle-kit would close if it cross-checked on-disk SQL. The third is a cross-PR window that `drizzle-kit check` at CI time can catch.

## Workaround for anyone hitting this in the meantime

A wrapper script around `drizzle-kit generate`:

- **Pre-condition checks:** journal-on-disk consistency (`journalMaxIdx >= fileMaxPrefix`); snapshot-chain integrity via id/prevId walk; clean working tree
- **Post-condition checks:** exactly one new SQL + snapshot file; no in-place snapshot overwrite (content hash compare against pre-snapshot); new journal entry's idx matches new SQL file's prefix
- **Revert on detection:** restores pre-condition `drizzle/` state if any post-condition fails

Closes the local-author window. Every downstream consumer would need their own wrapper — hence filing for an upstream fix.

Happy to provide additional repros or test against a candidate fix.