Make agent actions idempotent with a local ledger

Choose the property the ledger can establish

A retry can create duplicate work when the caller does not know whether its earlier request completed. This workflow isolates one limited property: a SQLite table records an action key and payload hash once. A repeat with identical content returns the existing recorded intent. Reusing the same key for a different payload fails. The demo never performs the action itself. That distinction prevents a useful local deduplication pattern from being advertised as exactly-once email, payment or publication delivery.

Bind a key to an immutable intent

The payload contains exactly kind, target and body as bounded strings. Its canonical JSON representation produces a SHA-256 digest. The table uses action_key as its primary key and stores that digest with a recorded status. SQL parameters carry the values rather than building a statement from the request. The key is a bounded lowercase identifier, not a filesystem path or a query fragment. A caller should choose a stable key for the intended operation and retain it when retrying that operation.

Read the transaction boundary

record_intent creates the table if needed, attempts an insert and reads the stored row within the connection’s transaction context. A uniqueness conflict is expected for an identical retry. A conflicting payload raises an error rather than replacing the original hash. SQLite’s transaction handling supports this local record operation; it cannot atomically include a remote service that is not participating in the transaction. The recorded status therefore means only that the intent exists, not that an external effect happened.

Run the example and persistence check

Save local_ledger.py and run it with Python 3.12. The main block uses an in-memory database and prints created true for the first intent and false for its duplicate. The supplied tests also reopen a file-backed scratch database, verify a retained key and reject a changed payload. Connections are explicitly closed where file cleanup depends on releasing the handle. The test’s scratch directory stays inside the workflow folder and is removed after the exercise; it contains only synthetic records.

Recover without changing the meaning of the key

If a retry finds a matching intent, the next step is to inspect the relevant execution state in a real integration rather than blindly repeating a remote action. If content changed, create a new reviewed operation under a new key. Never overwrite the ledger merely to make a conflict disappear. A key conflict is useful evidence that the caller’s understanding differs from the stored intent. Error logs should identify the safe operation key without dumping a potentially sensitive body.

What an external integration would need

A production design needs an explicit state machine, provider idempotency support where available, authentication, concurrency behavior and reconciliation for uncertain outcomes. Consider the crash between a remote success and the local update that records it. This fixture leaves that problem open because it has no remote call. Any extension should test that gap deliberately with a mock boundary before touching a consequential provider. Backups and retention also need requirements appropriate to the records the ledger contains.

Use the baseline honestly

The passed tests demonstrate duplicate suppression and payload binding for these local examples. They do not prove distributed consensus, unlimited throughput or recovery from every storage failure. A small ledger is valuable when its status has a clear meaning and another operator can reproduce its behavior. Preserve that clarity as the surrounding workflow grows: recorded intent, attempted execution, confirmed outcome and reconciled uncertainty should not silently become the same state.

Runnable Python fixture

"""Deduplicate local intents with SQLite; never executes an external action."""
import hashlib
import json
import re
import sqlite3

def digest(payload):
    if type(payload) is not dict or set(payload) != {"kind", "target", "body"}:
        raise ValueError("unexpected payload shape")
    if any(type(value) is not str or len(value) > 4000 for value in payload.values()):
        raise ValueError("payload must contain bounded text")
    raw = json.dumps(payload, sort_keys=True, ensure_ascii=True, separators=(",", ":"))
    return hashlib.sha256(raw.encode("utf-8")).hexdigest()

def record_intent(connection, action_key, payload):
    if type(action_key) is not str or not re.fullmatch(r"[a-z0-9-]{1,80}", action_key):
        raise ValueError("invalid action key")
    payload_hash = digest(payload)
    with connection:
        connection.execute("""CREATE TABLE IF NOT EXISTS intents (
            action_key TEXT PRIMARY KEY, payload_sha256 TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status = 'recorded'))""")
        cursor = connection.execute(
            "INSERT INTO intents VALUES (?, ?, 'recorded') ON CONFLICT(action_key) DO NOTHING",
            (action_key, payload_hash))
        stored = connection.execute(
            "SELECT payload_sha256, status FROM intents WHERE action_key = ?", (action_key,)).fetchone()
        if stored[0] != payload_hash:
            raise ValueError("key already binds a different payload")
        return {"created": cursor.rowcount == 1, "action_key": action_key, "status": stored[1]}

if __name__ == "__main__":
    from contextlib import closing
    with closing(sqlite3.connect(":memory:")) as db:
        action = {"kind": "draft", "target": "fixture-article", "body": "Fixture text"}
        print(record_intent(db, "fixture-article-v1", action))
        print(record_intent(db, "fixture-article-v1", action))

Verification and references

The ledger passed four fixture tests on 16 October 2026 using Python 3.12.10, including database reopening and conflicting payloads. No remote action was attempted. Run local_ledger.py for the example and test_workflows.py for the complete suite.

• Python 3.12 sqlite3 documentation (living reference; accessed 2026-10-16)
• Python 3.12 contextlib closing documentation (living reference; accessed 2026-10-16)