Idempotent AI Agent Jobs: Stop Duplicate Emails and Double Actions With a SQLite Ledger

An agent job that retries is an agent job that can do the same thing twice. The model call is usually harmless to repeat. The email, the invoice, the Slack message and the ticket are not. If your weekly-report agent crashes after sending the report and a supervisor restarts it, your client gets two reports and wonders what else is broken.

The fix has a name, idempotency, and a small implementation: a SQLite ledger keyed by a stable hash of the work. I built one with the standard library, ran five scenarios on 2026-10-29, and one of them fails on purpose. That failure is the most useful part, because it shows where a ledger alone stops being enough.

Which agent actions need protecting?

Not all of them. Sort your agent's steps by what a repeat costs.

If an action is in the "No" rows, it goes through the ledger. Everything else stays simple.

What does the ledger look like?

One table, one function. A key is the primary key, so two attempts can't create two rows. The status is started while the action runs and done once it returns.

import hashlib
import sqlite3


def connect(path=":memory:"):
    conn = sqlite3.connect(path)
    conn.execute(
        "CREATE TABLE IF NOT EXISTS ledger ("
        " key TEXT PRIMARY KEY, status TEXT NOT NULL, result TEXT)"
    )
    return conn


def make_key(job_name, *parts):
    """Same inputs give the same key, so a replay finds its earlier row."""
    raw = "|".join([job_name, *map(str, parts)])
    return hashlib.sha256(raw.encode()).hexdigest()[:20]


def run_once(conn, key, action):
    row = conn.execute("SELECT status, result FROM ledger WHERE key=?", (key,)).fetchone()
    if row and row[0] == "done":
        return row[1], "skipped (already done)"
    conn.execute("INSERT OR IGNORE INTO ledger(key, status) VALUES (?, 'started')", (key,))
    conn.commit()
    result = action(key)   # a crash here leaves 'started' behind
    conn.execute("UPDATE ledger SET status='done', result=? WHERE key=?", (result, key))
    conn.commit()
    return result, "ran"

Use a file path instead of :memory: in real life so the ledger survives a restart. SQLite is a good fit on a single box: no server, one file, and it's easy to back up with the rest of your data (the restic backup post covers that). It is a poor fit if several machines run the same job. Use Postgres there and put the unique key on the table.

Two details carry the design. make_key hashes the identity of the work (job name, client, week), never a timestamp or a random ID, otherwise every retry makes a new key and protects nothing. And action receives the key, which matters in a moment.

What happens in each scenario?

I used a fake mailer that counts sends and simulated a crash by raising an exception after the send. Output, verbatim:

A) No ledger, retry after a crash that happened after the send
   emails sent: 2
B) Ledger, whole job re-run after it finished
   ('sent #1', 'ran')
   ('sent #1', 'skipped (already done)')
   emails sent: 1
C) Ledger, crash AFTER send but BEFORE marking done, then replay (dumb mailer)
   ledger row: started
   emails sent: 2
D) Same crash, but the key is passed to a mailer that dedupes on it
   emails sent: 1
E) Key stability
   True False

Reading it:

• A is the baseline: without any record, a retry after a crash sends again.
• B is the win. The second run found a done row and returned the stored result without calling the mailer.
• C is the honest failure. The send happened, the process died before the update, and the ledger only says started. On replay the code can't know whether the email went out, so it acts, and the recipient gets two. A ledger on its own cannot close this window, because the send and the database write are two separate systems with no shared transaction.
• D closes it by giving the downstream service the same key. A mailer that honours an idempotency key sees the second request, recognises it, and does nothing new. Mine is a fake that I wrote to behave that way; a real provider's behaviour is in its documentation.
• E confirms the key is stable for the same week and different for the next one.

How do I close the crash window for real?

Pick the strongest option the downstream service allows, in this order.

1. Send the key to the service. Many payment and messaging APIs accept an idempotency key header and will return the first result for a repeated key. Read the provider's documentation for how long they remember keys and what they do on a mismatch, because those details differ. The Razorpay webhook post shows the receiving side of the same idea.
2. Check before you act. If there's no key support, query the service first: does a message with this ID or subject already exist in Sent, does an invoice with this reference exist? That narrows the window but doesn't remove it.
3. Put the ID inside the content. A reference number in the email subject or ticket title lets a human, or your own check, spot a duplicate quickly.
4. Accept the rare duplicate, but make it loud. For low-stakes actions, a started row older than some threshold can raise an alert so a person decides, instead of the job guessing. The heartbeat monitoring post shows how to turn a stuck state into an alert.

Plain SMTP email has no idempotency key that I know of, so for it options 2 and 3 are what you've got. That is a real limitation, and worth knowing before you promise a client "never twice".

How should the agent handle a started row on replay?

The example above simply acts again, which is right when the downstream service dedupes. For services that don't, make the decision explicit. A conservative policy:

Writing the started case as a table forces the question you'd otherwise skip until a client emails you.

Wiring it into a scheduled agent

A job like the daily digest agent already remembers links it has sent, which is the same idea in miniature. For anything with real consequences, build the key from the unit of work and wrap only the side effect:

conn = connect("/var/lib/agent/ledger.db")
key = make_key("weekly-report", client_id, iso_week)
summary = call_model(client_id)                 # safe to repeat
result, how = run_once(conn, key, lambda k: send_report(client_id, summary, idem_key=k))

This wiring snippet is a sketch: call_model and send_report are your functions, and I did not run it as written. One caution: if the model's output changes between attempts, the retry could send a different summary under the same key. Store the generated text before sending, or generate it inside the guarded action, so the second attempt reuses the first.

If your agent already runs on a durable workflow engine, it handles replay of the workflow but the side effect inside a step still needs care; see durable agents with Temporal for how that split works. The ledger habit still protects that step.

Checklist

• Is every email, charge and ticket step behind a key made from the work's identity?
• Does the key contain no timestamp or random value?
• Is the key passed to the downstream service when it supports one?
• Do you know what your ledger does with a started row, written down?
• Did you kill the process between the send and the update on purpose, and watch what the replay did?

That last test is the only one that proves anything. Scenario C above looked fine in my head until I ran it.