Make a scheduled Python job resume from a checkpoint

A checkpoint lets a batch skip completed work after a restart. The difficult part is defining what "completed" means. Saving the cursor before the result is durable can lose work; saving it afterward can repeat the last operation.

The local result and checkpoint commit together; an injected failure rolls the record back.

Use one transaction for local results

A small SQLite example makes that boundary explicit. It writes synthetic processed records and advances the cursor in the same database transaction.

import sqlite3

def prepare(db):
    db.executescript("""
        CREATE TABLE IF NOT EXISTS results(
            record_id INTEGER PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS checkpoint(
            singleton INTEGER PRIMARY KEY CHECK(singleton = 1),
            last_id INTEGER NOT NULL);
        INSERT OR IGNORE INTO checkpoint VALUES(1, 0);
    """)

def process(db, records, fail_on=None):
    for record_id, value in records:
        last_id = db.execute(
            "SELECT last_id FROM checkpoint WHERE singleton=1"
        ).fetchone()[0]
        if record_id <= last_id:
            continue
        with db:
            db.execute(
                "INSERT INTO results VALUES(?, ?)",
                (record_id, value.upper()),
            )
            if record_id == fail_on:
                raise RuntimeError("synthetic interruption")
            db.execute(
                "UPDATE checkpoint SET last_id=? WHERE singleton=1",
                (record_id,),
            )

Call prepare(), process records numbered one through three and inject a failure at record two. The second insert and cursor update should roll back together. Restart without the failure and inspect both tables.

The example assumes a stable input ordered by increasing integer ID. A cursor cannot safely skip records if IDs arrive late below the saved position. For a changing source, use a durable work table or a source-provided continuation contract that accounts for late arrivals.

Keep remote effects outside the promise

A transaction in SQLite cannot roll back an email already sent by a provider. If processing includes remote actions, use an idempotency key or record an uncertain outcome for reconciliation. Advancing a local cursor after a timeout can skip an action that never completed; repeating blindly can duplicate one that did.

Persist enough source identity with the checkpoint to detect an accidental switch to another input dataset. A cursor value of 500 is meaningless if the next run points at a differently sorted file.

This pattern is useful background for Postzyo's scheduled work. Python documents sqlite3 transaction control; its path interfaces are useful when checkpoints reference file inputs.