Repair Backfill Checkpoints, Resume & Drift Bugs

FAULT BOUNDARY IDENTIFIED

Case analyzed

A backfill scans users by updated_at and stores the last timestamp as its checkpoint. While it runs, normal traffic updates older users and moves them ahead of the checkpoint. After a restart, some users are processed twice while others that changed near the boundary are missed.

The supplied facts are sufficient to perform a concrete repair diagnosis for this case. No repository, production, vendor, or legal access is assumed.

Verified evidence

• VERIFIED: The scan orders by updated_at.
• VERIFIED: The checkpoint stores only the last timestamp.
• VERIFIED: Normal traffic mutates updated_at during the run.
• VERIFIED: Restart can produce both duplicate and missed work.

Concrete findings

1. updated_at is an unstable checkpoint key because normal traffic mutates it while the backfill uses it as a high-water mark.
2. Rows can move across the checkpoint, creating both duplicates and misses after restart.
3. For id > last_id, advancing past a validation failure is safe only if the failed row has a durable operation identity and guaranteed reconciliation path; the current retry queue lacks that.
4. Separate scan position from per-record completion and finish with independent reconciliation.

The canonical acceptance rule is

A resumable backfill is correct only when its checkpoint identifies a stable continuation boundary, source mutations cannot move unprocessed records behind the checkpoint, retries are idempotent, and completion is reconciled against an independently checkable target.

The current result is based on that invariant, not on a generic success banner or a reusable checklist.

Required actions

1. Checkpoint Identity: repair the owning boundary and preserve this case as a regression fixture; rec