A retry ledger can have a unique index and still admit the same incomplete operation twice. In SQLite, two rows containing the same tenant and a missing operation ID do not violate a composite UNIQUE constraint.

For an agent tool adapter, that is a dangerous place to confuse “the insert succeeded” with “this is a new authorised operation”. My requirement is explicit: every component of the deduplication key must be present before the adapter admits work. The database must enforce that requirement even if the caller skips application validation.

The example below is a synthetic local fixture. It sends no payments, calls no model and describes no observed agent incident. It tests one part of an idempotency design: whether the ledger rejects invalid or repeated keys. It does not implement exactly-once execution of an external tool.

Reproduce the missing-key retry

Suppose a tool adapter stores requests under a tenant-scoped operation ID:

CREATE TABLE operations (
    tenant TEXT,
    operation_id TEXT,
    payload TEXT NOT NULL,
    UNIQUE (tenant, operation_id)
);

The intention is reasonable. Within a tenant, the same logical operation should retain its ID across attempts. A different tenant can use the same local ID without colliding.

Now insert these two requests:

INSERT INTO operations VALUES ('tenant-a', NULL, 'same-request');
INSERT INTO operations VALUES ('tenant-a', NULL, 'same-request');

Both succeed. There are two rows, despite the identical tenant and payload.

SQLite’s UNIQUE constraint documentation states that NULL values are distinct from all other values, including other NULLs, for uniqueness checks. The payload is outside the unique key and does not change that result. Making tenant nullable creates a second route through the same defect: (NULL, 'op-1') can also repeat.

This becomes an agent retry problem if an adapter accepts a missing ID, maps it to SQL NULL and executes work whenever insertion succeeds. A timeout followed by another incomplete request can then create another admitted row. The fixture stops at the database boundary; the external duplicate effect is a consequence of that hypothetical dispatch rule, not something the fixture performs.

Do not fill the missing value with a fresh UUID on each attempt. That makes the value non-null while assigning a new identity to every retry. Create and persist the logical operation identity before dispatch, then reuse it when retrying that operation.

PRIMARY KEY and CHECK need inspection too

Changing the column declaration to operation_id TEXT PRIMARY KEY is not a reliable repair for an ordinary SQLite rowid table. SQLite documents a historical primary-key exception: unless the column is an INTEGER PRIMARY KEY, belongs to a WITHOUT ROWID or STRICT table, or explicitly declares NOT NULL, primary-key columns may contain NULL.

The downloadable fixture tests that behaviour on an ordinary table. It also tests a tempting alternative:

operation_id TEXT CHECK (length(operation_id) > 0)

This still admits NULL. SQLite’s CHECK documentation says that an expression evaluating to NULL is not a constraint violation. The expression above checks the length of a supplied value; it does not establish that a value exists.

These are documented database semantics. A comment saying “required idempotency key” does not change them.

Require the full key at both boundaries

For this fixture, the database repair is small:

CREATE TABLE operations (
    tenant TEXT NOT NULL CHECK (length(trim(tenant)) > 0),
    operation_id TEXT NOT NULL CHECK (length(trim(operation_id)) > 0),
    payload TEXT NOT NULL,
    UNIQUE (tenant, operation_id)
);

NOT NULL rejects missing values. The length checks reject empty strings and strings containing only ordinary spaces. The composite constraint continues to reject repeated supplied keys within the same tenant.

That SQL is not a complete identifier grammar. SQLite’s default trim does not mean “remove every Unicode whitespace character”, and ordinary text affinity is not strict application type validation. The Python entry point therefore applies an additional, deliberately narrow contract: each key must be a string of 1 to 128 ASCII letters, digits, underscores or hyphens. It rejects tabs, surrounding spaces, booleans and non-string values rather than coercing or silently trimming them.

Treat the grammar as an application design choice, not a vendor requirement. If your existing IDs contain other characters, define the allowed form for that system and test it. Avoid changing case or normalising IDs without checking whether that merges identities the upstream service treats as distinct.

The tenant in a real adapter must come from trusted authentication and authorisation context. A database namespace prevents accidental collisions; it does not establish that the caller has permission to act for the named tenant. Likewise, the payload column here is plain teaching data, not a validated tool request.

Run the counterexample and regression tests

Download the complete Python fixture. It uses the standard library and in-memory databases, with no network access or persistent database files.

python3 --version
python3 agent-idempotency-null-key.py
python3 agent-idempotency-null-key.py --test
python3 -O agent-idempotency-null-key.py --test

The demonstration prints:

nullable_key_rows=2
fixed_missing_key=rejected
fixed_duplicate_key=rejected
fixed_rows=1

Before publication, I ran the demonstration and all 12 test methods with Python 3.9.6 and SQLite 3.51.0. The tests passed in both normal and optimised mode. They retain the broken schema as a counterexample, reject missing values in either key column and check that different tenants can use the same operation ID.

One test attempts to reuse a key with a changed payload. The plain insert raises an integrity error and preserves the original payload. That proves the fixture does not overwrite the first request. A production adapter still needs to distinguish a legitimate replay of the same request from incompatible reuse of a key. It must inspect the stored request binding and execution state before deciding what to return.

A suppressed error is not an execution permit

Keep the insertion semantics visible. SQLite’s conflict-resolution documentation describes two shortcuts that need care in a retry ledger.

INSERT OR IGNORE can suppress a constraint error. The fixture shows a missing key producing no exception but inserting zero rows. Code that dispatches after any non-throwing database call would still be wrong after adding NOT NULL. It needs a positive, committed admission result, not merely the absence of an exception.

INSERT OR REPLACE is worse for preserving the original request binding. On a uniqueness conflict, SQLite deletes the conflicting row before inserting the replacement. The fixture demonstrates the stored payload changing under the same key. Do not use that as shorthand for “return the previous result”. It replaces the record you need to compare.

A plain failed insert also requires deliberate transaction handling. In this small fixture, the caller observes the exception and closes the in-memory database at the end. In a service, roll back or otherwise resolve the transaction before reusing the connection. SQLite’s transaction documentation explains the different error paths and the possibility of SQLITE_BUSY; none of those states should automatically authorise tool execution.

Repairing the ledger does not close the remote-write gap

The repaired key stops this specific duplicate-admission defect. It does not make a database commit and an external API call one atomic action.

If the adapter commits admission, performs a remote write, then crashes before recording the result, the local record cannot prove whether that write happened. Keep the outcome unresolved until you can reconcile it with the provider. Where the provider supports idempotency, use its documented key scope, request-binding rules and retention window. Where it does not, design a recovery path that checks the effect before issuing another potentially irreversible write.

This is the same boundary discussed in agent retry amplification: an application can multiply attempts while every individual layer appears to behave as designed. The production checklist gives a broader review path for tool permissions and recovery controls.

For the wider operating model, see Claude Code: Building Production Agents That Actually Scale. Get the Kindle edition on Amazon.

Before migrating an existing ledger, inventory missing and blank key components, preserve the affected records and reconcile their effects. Rehearse the migration on a copy. Then add a regression that bypasses the Python validator and inserts NULL directly into each database key column. Both attempts must fail before any work is dispatched.