Your Nightly Job Ran Once, Right? Five Scheduling Myths and an Idempotency Ledger

I woke up to three identical triage comments on one issue.

The model did not hallucinate. My nightly job ran three times.

That morning became a checklist. I had repeated five claims for years. Each one was wrong in a boring, expensive way.

Here is the FAQ I wish I had read first, plus the 40-line ledger that settles it.

Myth 1: “The scheduler runs my job once per night”

What I saw: three runs rows for the same day, same input, different PIDs.

Why it happens:

• Most schedulers guarantee at-least-once, not exactly-once.
• A non-zero exit triggers a retry.
• A long run overlaps the next one.
• A manual rerun looks identical to a scheduled one.

Corrected model: delivery is at-least-once. Idempotency is your job.

Ask yourself: if this job ran twice right now, what would a user see?

Myth 2: “A lock file is a lock”

What I saw: two workers, one job.lock file, two executions.

Why it happens:

• A leftover file blocks nothing.
• flock dies with the process, but the file stays.
• Two containers can have two separate filesystems.
• PIDs get reused, so “is PID 1234 alive” lies.

Corrected model: the lock must live next to the side effect.

If the effect is a database row, the lock is a unique constraint.

Myth 3: “The timestamp tells me which run is newer”

What I saw: two runs with the same local timestamp during a DST fall-back.

Why it happens:

• Local time repeats one hour per year.
• Containers drift without NTP.
• time.time() is wall clock, not monotonic.

Corrected model: store UTC ISO-8601 for humans, and a lease deadline for machines.

Never order work by a wall clock you did not measure.

Myth 4: “A log line means the step finished”

What I saw: delivered=true in the log. No message at the receiver.

Why it happens:

• stdout is buffered, then lost on kill.
• The log line was written before the network call.
• The process died between the call and the flush.

Corrected model: durable state transitions are evidence. Logs are commentary.

Write the state, then log it. Not the other way around.

Myth 5: “Retrying is harmless, the call is cheap”

What I saw: three model calls for one issue, and three public comments.

Why it happens:

• Quota is shared across every retry.
• Retries multiply: 3 attempts x 2 workers = 6 calls.
• User-visible side effects cannot be un-sent.

Corrected model: retry the wrapper, never the side effect.

The artifact: a SQLite idempotency ledger

I want one atomic claim, one durable completion, one outbox row.

SQLite gives me that on a single node. No broker, no new service.

Step 1: the schema

CREATE TABLE IF NOT EXISTS runs (
  idem_key    TEXT PRIMARY KEY,
  status      TEXT NOT NULL,
  owner       TEXT NOT NULL,
  lease_until REAL NOT NULL,
  attempts    INTEGER NOT NULL DEFAULT 1,
  started_at  TEXT NOT NULL,
  finished_at TEXT
);

CREATE TABLE IF NOT EXISTS outbox (
  idem_key TEXT PRIMARY KEY,
  payload  TEXT NOT NULL,
  sent_at  TEXT
);

idem_key is the whole trick. Derive it from the work, not from the clock.

def key_for(day: str, payload: bytes) -> str:
    digest = hashlib.sha256(payload).hexdigest()[:16]
    return f'triage:{day}:{digest}'

If the input changes, the key changes. If only the retry changes, it does not.

Step 2: claim, run, finish

def claim(con, key, owner, lease=900):
    now = time.time()
    cur = con.execute(
        '''
        INSERT INTO runs (idem_key, status, owner, lease_until, started_at)
        VALUES (?, 'running', ?, ?, ?)
        ON CONFLICT(idem_key) DO UPDATE SET
          owner = excluded.owner,
          lease_until = excluded.lease_until,
          attempts = runs.attempts + 1,
          status = 'running'
        WHERE runs.status = 'failed'
           OR (runs.status = 'running' AND runs.lease_until < ?)
        ''',
        (key, owner, now + lease, utcnow(), now),
    )
    return cur.rowcount == 1

One statement, one winner. The WHERE clause is the lease.

A crashed worker leaves status='running' until the lease expires. Then someone else can reclaim it.

def run_job(con, key, payload, classify):
    owner = f'{socket.gethostname()}:{os.getpid()}'
    if not claim(con, key, owner):
        return 'skipped'
    try:
        labels = classify(payload)          # slow, quota-consuming
        con.execute(
            'INSERT OR IGNORE INTO outbox (idem_key, payload) VALUES (?, ?)',
            (key, labels),
        )
        con.execute(
            'UPDATE runs SET status=?, finished_at=?, lease_until=0 WHERE idem_key=?',
            ('done', utcnow(), key),
        )
        return 'done'
    except Exception:
        con.execute('UPDATE runs SET status=? WHERE idem_key=?', ('failed', key))
        raise

Step 3: deliver from the outbox

def deliver(con, send):
    rows = con.execute(
        'SELECT idem_key, payload FROM outbox WHERE sent_at IS NULL'
    ).fetchall()
    for key, payload in rows:
        send(key, payload)                  # receiver dedupes on key
        con.execute(
            'UPDATE outbox SET sent_at=? WHERE idem_key=? AND sent_at IS NULL',
            (utcnow(), key),
        )

The receiver still needs a dedupe key. The ledger narrows the window. It does not close it.

The drill: prove the claim is atomic

Eight processes, one key, one expected winner.

# drill.py
import multiprocessing as mp, os
import ledger

def worker(q):
    con = ledger.connect()
    won = ledger.claim(con, 'triage:2026-10-19:demo', f'w{os.getpid()}')
    q.put((os.getpid(), won))

if __name__ == '__main__':
    q = mp.Queue()
    ps = [mp.Process(target=worker, args=(q,)) for _ in range(8)]
    for p in ps: p.start()
    for p in ps: p.join()
    results = [q.get() for _ in ps]
    wins = [r for r in results if r[1]]
    print(results)
    assert len(wins) == 1, f'expected 1 winner, got {len(wins)}'

Run it twenty times. Delete the database between runs.

for i in $(seq 1 20); do
  rm -f runs.db runs.db-wal runs.db-shm
  if ! python drill.py; then echo "failed on run $i"; exit 1; fi
done
echo "20/20 single-winner"

If you see database is locked, treat it as a retryable claim error. It is not a win.

Now break it on purpose:

1. Claim a key, then kill the process. Claim again before the lease expires. Expect False.
2. Set lease_until = 0 by hand. Claim again. Expect True and attempts = 2.
3. Call deliver twice with the same outbox row. The second call should send nothing.

That third test is where most “exactly once” claims die.

Decision table: when the ledger is overkill

Situation	Use	Why
One node, local disk	this ledger	atomic primary key
Two hosts, no shared DB	a queue with a dedupe key	file locks lie across hosts
Job under a minute, fully idempotent	skip the ledger	complexity beats the risk
Email or public comment	outbox plus receiver dedupe	claims are not deliveries
Sub-second scheduling	a real scheduler	SQLite is not your clock

Limitations

• SQLite assumes one host and a local filesystem. Network filesystems break the guarantee.
• The ledger prevents duplicate work. It cannot undo a side effect already sent.
• A lease expiry can cause two workers to run at once. Keep jobs shorter than the lease.
• This is not a workflow engine. No fan-out, no priorities, no visibility timeouts.
• If you need exactly-once across services, use a broker that supports dedupe.

Skip this approach if your job is already idempotent and cheap. Skip it if you cannot tolerate a 15-minute recovery window.

Where the free tier fits

My nightly triage calls a model to label issues. That call is the slow, quota-consuming step.

In my setup I used MonkeyCode's free model access for that classification step. I used its free server option to host the scheduler and the ledger database.

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

The free tiers are not the point. They just make duplicate calls and missed runs visible faster.

Design the ledger first. Then point any model or host at it.

FAQ

Q: Can I use Redis instead of SQLite?
Yes. SET key value NX PX 900000 gives you the same claim.

Q: Do I still need retries?
Yes. Retry the claim and the classification. Never retry the send.

Q: What about timezones?
Store UTC. Render local time in the UI only.

Q: How do I know the job actually ran?
Count status='done' rows per day. Grafana or a SELECT.

If you run the drill, tell me which myth broke first. Mine was number two.