Restart a backfill without losing the skipped work

Four synthetic records start with owner = Queue A, revision 1, and an empty new_owner. The source owner and its revision are authoritative for this exercise. The target stores both the copied owner and the revision it represents.

The scan uses increasing primary keys, two rows per batch, with an explicit upper ID of 4. The checkpoint records the highest ID examined, not the number of rows successfully changed. Every row in a batch must either be current already, be updated under its captured revision, or retain a pending reconciliation ID. The fixture does not include deletes, new IDs, parallel migration workers or replicas; a production plan must define those coverage boundaries separately.

Before the first batch writes, an actual separate parent connection changes row 2 to Queue B at revision 2. The child captured Queue A at revision 1. A guarded UPDATE checks the captured revision and empty target; it refuses that stale copy. A zero-row update is a branch to investigate, not a success counter.

The child sends its captured rows over a local process pipe. The parent commits the source change and releases the child. That handshake fixes the interleaving without timing sleeps. The worker then exits abruptly with code 23 at the chosen boundary. The parent joins it before reopening the file and resuming.

In cursor-first, checkpoint 2 commits separately before the data transaction. The child writes inside that later transaction and exits without committing it. Reopening SQLite rolls those data and pending writes back. The checkpoint is now ahead of the work it describes.

In data-first, data and pending row 2 commit, but checkpoint 0 remains. Restart reexamines rows 1 and 2. It recognizes row 1 as already current and rereads row 2 at revision 2. There is additional scanning, but no second target write for row 1.

In atomic, data, pending row 2 and checkpoint 2 commit together. Restart scans rows 3 and 4. It has no reason to revisit row 2 through the cursor, so its durable pending ID matters. Finishing that scan is not finishing reconciliation.

The six visual frames are authored explanations of the runner's observed states. They do not launch a worker in the browser. The written ledger below retains the same evidence for reading, printing and receiving review.

Download the resumable backfill runner. Keep the original and changed reports in separate local files:

python3 --version
python3 fde-resumable-backfill.py --all > backfill-original.json
python3 fde-resumable-backfill.py --case atomic --late-change > backfill-late-change.json

worker reports the owned PID, actual exit code and captured batch. Each later checkpoint is a fresh connection's committed read. rows includes source and target revisions; unmatched_ids compares both value and revision with the current source. attempts contains committed per-row results. Work that rolled back does not invent an applied audit entry.

The written ledger

Inspect the data-first attempts for row 1: one applied, then one already_current. That second observation does not overwrite the target or create another business operation. Inspect atomic.after_scan_resume: cursor 4 coexists with unmatched_ids = [2] and pending ID 2. The program preserves that intermediate state before performing reconciliation.

Now compare the changed report. --late-change is available only with --case atomic. After the scan completes but before reconciliation, a real source write changes pending row 2 again, to Queue C/revision 3. The bounded pass rereads its current source and copies revision 3, rather than using the earlier Queue B or the original captured Queue A. Its final result still concerns only these four rows at that observation point.

The process interruption is real; power-loss durability, disk corruption, network partitions, row-lock performance and replication lag are not exercised. The local pipe coordinates the demonstration. It is not a production scheduling or distributed-worker design.

A conditional write protects this attempt from overwriting a newer source. It does not make every later source change update the new field. Old writers still need a compatibility plan before new readers rely on the target. An empty pending queue also cannot prove coverage when a faulty checkpoint advanced without recording the skipped IDs.

For a receiving review, inspect three things independently:

1. Scan position and coverage: which stable key range and source population were covered, and which records remain outside it?
2. Reconciliation: which IDs were skipped, why, what current read was used, and what remains unresolved after a bounded pass?
3. Target meaning: do value and represented revision match the authoritative record, and can supported old/new writers and readers maintain that meaning?

The runner refuses to overwrite a nonempty target that differs from its current source. It keeps that record pending for an explicit repair decision. Blindly replacing any nonempty target could erase reviewed work or obscure a broken writer contract. The same caution applies to a row that disappears: define a deletion/tombstone policy rather than counting it as migrated automatically.

Use application owner, migration operator and receiving engineer roles. Combine roles explicitly for smaller groups; label solo work self-review. Each person keeps their own prediction, evidence and assistance. The receiver chooses one checkpoint and asks the operator to locate the exact persisted rows without author takeover.

My role / exact runner and command:
Prediction before the worker exits:
Observed PID / exit code / checkpoint:
Source and target rows personally inspected:
Pending IDs / reason / committed attempt references:
One changed condition / prediction before that run:
Changed observation kept separately:
Receiving action actually attempted / assistance:
Unresolved coverage or compatibility / next owner and bounded action:

A local run does not authorize a production migration or establish independent review, qualification or client placement. Keep the receiving decision scoped to what was actually observed. An operator can say “scan reached ID 4; row 2 awaits reconciliation” without inventing completion.

Why is checkpoint-first unsafe here? When is rereading a batch acceptable? Why does an atomic checkpoint still need pending-row reconciliation? Why must a represented revision travel with a copied value? Which evidence would permit old consumers to retire? Answer with particular commits and rows.

SQLite's transaction documentation explains the local commit/rollback boundary. GitLab's batched migration guidance requires small, idempotent jobs and warns against depending on data before the migration is actually finished. Those sources were checked 27 October 2026. This runner is original LockedIn Labs instruction, not an implementation of GitLab's framework or a production migration tool.

Continue with migration compatibility and the PostgreSQL lock-queue workshop. The protected migration lesson adds the receiving mechanism within existing training access.