48 小時實測筆記：作者重播生成式遷移腳本兩次，發現資料被再次改寫

48-Hour Field Notes: I Replayed a Generated Migration Twice and the Row Counts Moved

A generated backfill script looked clean in review on Monday morning, and by Wednesday it had already been applied twice against a staging copy of real shapes. The first pass rewrote 412,318 rows, and the second pass rewrote the same rows again on top of the result. Nobody had lost production data, but only because that staging database was scheduled to be destroyed that same afternoon. So I blocked two days and treated every generated migration as hostile until a scratch host proved it could survive a replay.

Why did code review pass a script that breaks on its own second run? Because reviewers read the predicate for correctness, and almost nobody reads it for repeatability. Those are different properties, and only one of them shows up in a diff.

What I Actually Tried in the First 48 Hours

My plan was deliberately boring, and I wrote it down before I touched anything, so I could not quietly move the goalposts later.

1. Freeze the generated draft and hash it, so I knew exactly which revision I was testing.
2. Build a scratch database from a schema-only dump plus a small synthetic data seed.
3. Apply the migration, snapshot the state, then apply the exact same file again.
4. Compare fingerprints instead of eyeballing SELECT COUNT(*) output in a terminal.
5. Only after a green replay did I read the predicate logic line by line for correctness.

Step 4 is the part I had been skipping for years, and it is the part that caught the bug. Counting rows felt like verification, but counts stay identical while individual values drift, and my ledger table drifted in exactly that way.

The Failure I Hit on the Second Application

The generated script was an UPDATE ... WHERE owner_id IS NULL backfill wrapped in a defensive BEGIN/COMMIT. On the first run it assigned owners to orphaned ledger rows, and on the second run it reassigned a subset of those rows to a different owner. The predicate did not re-check whether a row had already been claimed, so the statement was never idempotent in the first place. Reviewers approved it because the join looked correct; the join was correct, and the missing part was the guard clause.

The Artifact: Replay Harness and State Fingerprint

Here is the reduced version of what I ran, using SQLite so you can reproduce it in about two minutes without a database server. It is not a benchmark, and it proves nothing about performance; it only answers one question, which is whether the same file applied twice produces the same state.

#!/usr/bin/env bash
# replay.sh — apply one generated migration twice against a throwaway database
set -euo pipefail

MIGRATION="${1:?usage: replay.sh migrations/0007_backfill.sql}"
SCRATCH="scratch_$(date +%s).db"

sha256sum "$MIGRATION" | tee migration.sha256
sqlite3 "$SCRATCH" < schema.sql

sqlite3 "$SCRATCH" < "$MIGRATION"
cp "$SCRATCH" after_run1.db

sqlite3 "$SCRATCH" < "$MIGRATION"
cp "$SCRATCH" after_run2.db

python3 fingerprint.py after_run1.db after_run2.db

The fingerprint script ignores row order and hashes the logical content of the table, which is what I should have been comparing all along.

# fingerprint.py — compare logical table state between two replay snapshots
import hashlib
import sqlite3
import sys


def fingerprint(path):
    con = sqlite3.connect(path)
    rows = con.execute(
        "SELECT owner_id, amount_cents, status FROM ledger ORDER BY owner_id, id"
    ).fetchall()
    con.close()
    digest = hashlib.sha256()
    for owner_id, amount, status in rows:
        digest.update(f"{owner_id}|{amount}|{status}\n".encode())
    return digest.hexdigest(), len(rows)


left, left_rows = fingerprint(sys.argv[1])
right, right_rows = fingerprint(sys.argv[2])
print(f"after run 1: {left_rows} rows, {left[:12]}")
print(f"after run 2: {right_rows} rows, {right[:12]}")
print("IDEMPOTENT" if left == right else "NON-IDEMPOTENT: state changed on replay")

The Guard Clause I Should Have Demanded Up Front

The fix was small and unglamorous: add a predicate that excludes rows already claimed, and assert that the affected-row count of the second run is exactly zero. That assertion is now a required part of my review checklist, because it converts a vague correctness argument into a number I can fail the build on. Idempotency stopped being a property I hoped for and became a property I measured.

A Decision Table for Accepting Generated Migrations

Not every generated statement deserves the same suspicion, so I stopped treating them as one category.

Where the Free Tier Actually Fit in This Workflow

I used the free server option for the throwaway scratch host and the free model access to draft the fingerprint script, then rewrote the parts I did not trust. The operator states that the free tier includes a 10,000,000-token allowance, and I have not independently verified how long that allowance or the free server stays available, so check the current terms yourself before planning around them. The useful part for me was not the drafting speed; it was having a disposable machine where a broken migration was free to fail loudly.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

If you want to reproduce this, spin up a scratch host, apply one generated migration twice, and watch the second run for a delta you did not expect.

Limitations and Who Should Not Copy This

A green replay on SQLite is necessary but nowhere near sufficient, because Postgres and MySQL handle DDL transactions and locking differently from what I tested here. The fingerprint only proves repeatability, and it says nothing about whether the original predicate selected the right rows to begin with. The harness also assumes a schema snapshot that resembles production closely enough that the same joins execute, which is an assumption that quietly breaks the whole test when it fails.

I would not use this approach if your team has no disposable database copy, or if your migration's safety depends on lock duration, replica lag, or long-running transactions rather than on data state. Teams handling regulated data that cannot leave production boundaries should build an equivalent check inside their own environment instead of copying this script as-is. And if a migration is small enough to read in thirty seconds, reading it carefully is still faster than building a harness around it.

What I Would Repeat Tomorrow

Hashing the draft, replaying it, and refusing to argue about correctness until the replay returned a number are the three habits I am keeping. The 48 hours were not wasted on a bug that never reached production, and that is exactly the point of spending them. Generated drafts are useful, but the second run is where their honesty gets tested.