48-Hour Field Notes: A Crash Mid-Batch Proved My Queue Had No Memory

My batch job had a very confident opinion about progress, and that opinion lived entirely in a Python variable. If the process died, the variable died with it, and the next run started from zero like nothing had ever happened. Sound familiar? I spent a 48-hour window on this instead of shipping the feature I actually wanted to ship.

Here is the thing that annoyed me most: my earlier fix was idempotency-hashing the inputs, which protects you from doing the same work twice. It does absolutely nothing for the opposite failure, where a crash quietly skips items and nobody notices until a report looks short. So this time I attacked the missing piece — a journal that survives SIGKILL and can tell me, at any moment, which items are claimed, which are done, and which are stranded in between.

The failure I designed for, and the one I missed

I expected crashes between calls — the process stops, some items are done, some are not. That case is easy, because the work table already reflects a consistent state and resuming is just picking up pending rows. What I missed is a crash between claim and commit, the tiny window where a worker has reserved an item but not recorded an outcome.

That window is where queues lose data, and it is where my first version lost a pending item forever. The row said claimed, the owner process was a corpse, and no code path would ever look at that row again. So I added a lease, a monotone clock check, and one rule that I now consider non-negotiable: a claim expires.

Four states, not three

The model I settled on is pending → claimed → done | failed, with claimed rows carrying an owner and an expiry. A row that is claimed past its expiry is logically pending again, and any worker may take it. That single change turned an unrecoverable state into a recoverable one.

Here is the schema and the two functions that matter. It is SQLite on purpose — a single file I can copy off a box and inspect with the sqlite3 CLI at 2am.

# runner.py — crash-safe batch runner (single writer, SQLite journal)
import json, sqlite3, time

DB = "journal.sqlite3"
LEASE_SECONDS = 90  # must exceed your slowest single call

def connect():
    con = sqlite3.connect(DB, isolation_level=None, timeout=30)
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA busy_timeout=5000")
    return con

def init(con, items):
    con.execute("""
    CREATE TABLE IF NOT EXISTS work (
      item_id     TEXT PRIMARY KEY,
      payload     TEXT NOT NULL,
      state       TEXT NOT NULL DEFAULT 'pending',
      lease_owner TEXT,
      lease_until REAL,
      attempts    INTEGER NOT NULL DEFAULT 0,
      result      TEXT,
      last_error  TEXT
    )""")
    con.execute("""
    CREATE TABLE IF NOT EXISTS ledger (
      seq     INTEGER PRIMARY KEY AUTOINCREMENT,
      item_id TEXT NOT NULL,
      event   TEXT NOT NULL,
      at      REAL NOT NULL,
      meta    TEXT
    )""")
    con.execute("BEGIN IMMEDIATE")
    try:
        con.executemany(
            "INSERT OR IGNORE INTO work(item_id, payload) VALUES (?, ?)",
            [(i["item_id"], json.dumps(i["payload"])) for i in items],
        )
        con.execute("COMMIT")
    except Exception:
        con.execute("ROLLBACK")
        raise

Note that isolation_level=None means autocommit, so with con: will not give you a transaction. I lost an hour to that assumption; use explicit BEGIN IMMEDIATE / COMMIT and you keep the write lock for the whole read-then-update.

def claim_next(con, owner):
    now = time.time()
    con.execute("BEGIN IMMEDIATE")
    try:
        row = con.execute(
            """SELECT item_id FROM work
                WHERE state = 'pending'
                   OR (state = 'claimed' AND lease_until < ?)
                ORDER BY item_id
                LIMIT 1""",
            (now,),
        ).fetchone()
        if row is None:
            con.execute("COMMIT")
            return None
        con.execute(
            """UPDATE work
                  SET state='claimed', lease_owner=?, lease_until=?, attempts=attempts+1
                WHERE item_id=?""",
            (owner, now + LEASE_SECONDS, row[0]),
        )
        con.execute(
            "INSERT INTO ledger(item_id, event, at, meta) VALUES (?,?,?,?)",
            (row[0], "claimed", now, json.dumps({"owner": owner})),
        )
        con.execute("COMMIT")
    except Exception:
        con.execute("ROLLBACK")
        raise
    return row[0]

def finish(con, item_id, ok, result=None, error=None):
    con.execute("BEGIN IMMEDIATE")
    try:
        con.execute(
            """UPDATE work
                  SET state=?, result=?, last_error=?, lease_owner=NULL, lease_until=NULL
                WHERE item_id=?""",
            ("done" if ok else "failed", result, error, item_id),
        )
        con.execute(
            "INSERT INTO ledger(item_id, event, at) VALUES (?,?,?)",
            (item_id, "done" if ok else "failed", time.time()),
        )
        con.execute("COMMIT")
    except Exception:
        con.execute("ROLLBACK")
        raise

The loop is deliberately boring, and the attempt counter is checked after claiming so that a poison item cannot spin forever inside the lease window. A total budget in seconds stops the run even when items keep failing, which is the difference between a degraded job and a runaway one.

The kill test I now run before trusting any batch job

This is the part I would repeat on every project. It takes ten minutes and it falsifies the exact claim everyone makes in standup: "it's resumable."

1. Seed 200 synthetic items with item_id values that sort lexicographically, so ordering is stable.
2. Start the runner with a fetch function that sleeps a random 0.2–2.0 seconds, so crashes land in different phases.
3. After roughly 20 items, kill -9 the process. Not SIGTERM — you want no cleanup code to run.
4. Restart with the same database and a new worker id, and let it drain.
5. Assert total done count equals 200, and assert no item_id has two done events in the ledger.

Two queries do most of the work:

-- any item completed more than once?
SELECT item_id, COUNT(*) c FROM ledger
 WHERE event = 'done' GROUP BY item_id HAVING c > 1;

-- anything still stranded after the run?
SELECT state, COUNT(*) FROM work GROUP BY state;

If the second query shows a claimed row when the runner has exited cleanly, your lease logic is wrong, not your network.

What actually broke

• Stale claims. Without lease_until, a killed worker's row was immortal. This was the bug that started the whole exercise.
• Two runners on one file. Fine for SQLite with BEGIN IMMEDIATE, but only because the claim is a single transaction. My first draft read the row, then updated it, and two workers happily claimed the same item.
• Retry storms from the failure path. Retrying a failing call immediately made the job look busy while making no progress. The attempt cap plus a total budget fixed it.
• Journal growth. The ledger had no pruning, and it is now trimmed by age on startup rather than during the run.

Where the free server and free model access fit

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

The runner above is most useful when the process can die for reasons you did not choose, and that is exactly the situation when you move it off your laptop. MonkeyCode's free model access covers the fetch step, and the free server option gives you a place to host the runner without paying for infrastructure while you are still validating the design. I would not treat either as a guarantee — verify current availability yourself, and size LEASE_SECONDS to your slowest observed call rather than my 90 seconds.

Swapping the model call in is a single function, which is the point of keeping it outside the journal:

# adapter.py — pseudocode shape, not a shipped client
def make_fetch(client, model):
    def fetch(payload):
        resp = client.complete(model=model, prompt=payload["prompt"],
                               max_tokens=payload.get("max_tokens", 512))
        return json.dumps({"text": resp.text}, sort_keys=True)
    return fetch

Because fetch is pure from the journal's point of view, a failed call is just a failed call. No retry logic leaks into the queue, and no queue state leaks into the prompt.

Decision table: is the journal worth it?

Situation	Journal?	Why
Ten items, run by hand, rerun is free	No	A loop and a print is honest enough
Hundreds of items, external calls with any quota	Yes	Resume beats restart when calls are scarce
Job produces side effects (emails, comments, PRs)	Yes, plus idempotency keys downstream	The journal gives at-least-once, not exactly-once
Multiple machines	Not this	SQLite is a single-writer store; use a real queue
You need an audit trail for a review	Yes	The ledger is the audit trail

Who should not use this

If your job finishes in under a minute, a journal is ceremony. If your only side effect is a row you can safely rewrite, a plain loop with a seen set is cheaper to read and cheaper to delete later. And if you need cross-machine coordination, lease-based claiming in SQLite will fight you; reach for Postgres, Redis, or a managed queue instead of growing this into a scheduler.

What I would repeat

I would keep the kill test, the four-state model, and the ledger, in that order of importance. I would not keep my original assumption that idempotent inputs are the same thing as a durable queue, because they are not, and the gap between them is exactly where items disappear. Run the kill test on your current batch job this week and tell me what your second query returns — if it is all done, you already knew more than I did on Monday.