An agent adapter saves a job’s latest status with INSERT OR REPLACE. The statement succeeds. Reading the job back returns the expected ID and status. The adapter’s test passes.

A receipt attached to that job can disappear during the same statement.

I would reject a ledger update test that checks only the parent row. If the application uses SQLite replacement semantics, the test must also inspect the evidence attached to that row. A successful status update is a poor result if it erases the record needed to investigate the tool call.

The example below is an offline, application-owned ledger fixture. It describes no production incident and makes no claim about Claude Code’s internal storage. It uses Python’s standard-library SQLite adapter without model calls, network access or persistent files.

Replacement deletes before it inserts

SQLite’s conflict-resolution documentation specifies that REPLACE, when resolving a primary-key or uniqueness conflict, deletes the pre-existing conflicting rows before continuing with the insertion or update. It is not shorthand for changing selected columns in the existing row.

That deletion interacts with foreign-key actions. A child relationship declared with ON DELETE CASCADE removes matching child rows when the parent is deleted. In this schema, receipts depend on jobs:

CREATE TABLE jobs (
    id TEXT PRIMARY KEY NOT NULL,
    status TEXT NOT NULL
);
CREATE TABLE receipts (
    id TEXT PRIMARY KEY NOT NULL,
    job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    evidence TEXT NOT NULL
);

The fixture explicitly enables PRAGMA foreign_keys=ON on its connection and reads the setting back before creating data. It starts with job-1 in state running and one receipt containing the text tool-returned. These are teaching values. The receipt is not a verified external effect, and the schema does not implement approval or authorisation.

Now run:

INSERT OR REPLACE INTO jobs(id, status)
VALUES ('job-1', 'done');

The existing primary key conflicts. SQLite deletes the old job-1, cascades the deletion to its receipt, and inserts the new job-1. A query against jobs still returns one row. The receipts query returns none.

Even writing the original status, running, causes the loss in this fixture. An application test that compares only the parent’s before-and-after values would observe no change at all. The download includes that exact regression case.

This behaviour is permitted by the schema. SQLite has not lost referential integrity: there is no orphaned receipt. It has removed the receipt. Accordingly, PRAGMA foreign_key_check returns no violations after the destructive write. Constraint validity and evidence retention are different requirements.

Change the intended columns explicitly

SQLite’s UPSERT documentation describes ON CONFLICT ... DO UPDATE: when the specified uniqueness conflict occurs, the insert is omitted and the update runs instead. For this adapter, the intended change is the job’s status:

INSERT INTO jobs(id, status)
VALUES (?, ?)
ON CONFLICT(id) DO UPDATE SET status=excluded.status;

The excluded.status expression uses the status proposed by the insert. The conflict target names id, and the update assigns only status. The existing ID stays unchanged, so this operation does not delete the parent or invoke the schema’s delete cascade.

The fixture tests both branches. Updating job-1 preserves the complete receipt row, including its evidence text. Inserting job-2 creates a new job and leaves job-1 and its receipt alone. Repeating the same UPSERT also preserves the receipt in this schema.

That last result is deliberately narrow. It does not prove that retries of an external tool are idempotent. A real schema may have update triggers, counters or timestamps that change on every update. An adapter could call a remote service before reaching this SQL statement. Test those effects separately rather than treating a repeatable local status assignment as a guarantee for the whole workflow.

If the job must already exist, an explicit UPDATE may express the contract better. Decide whether a missing row should be inserted, rejected or reconciled. Choosing UPSERT merely because it accepts both states can hide a missing-job defect. Likewise, this example allows any supplied status string; it does not enforce valid state transitions or reject a stale worker’s update.

A transaction does not repair a successful destructive write

Yesterday’s SQLite script rollback example showed an implicit commit moving a write outside a later rollback boundary. Here the statement succeeds and the cascade is part of its intended database behaviour. Putting it inside a transaction does not make that deletion disappear after commit.

An explicit rollback before commit does restore the parent and receipt in this fixture. One test begins a transaction, performs the replacement, confirms that the receipt is absent inside that transaction, then rolls back and compares both tables with the original snapshot.

That gives the adapter a chance to abort if it detects an invalid outcome before committing. But a transaction that commits after checking only jobs will still commit the receipt loss. Prefer SQL that expresses the desired mutation, then use a transaction to bind all required changes.

There is another tempting distraction: PRAGMA recursive_triggers. SQLite documents special handling for user-defined delete triggers under REPLACE. However, its foreign-key limitations section says that the recursive-triggers setting does not affect foreign-key actions. Switching it off does not disable this cascade. The tests reproduce the deletion with the setting both off and on.

Give retained evidence a deletion policy

For a ledger whose receipts must outlive ordinary job updates, ON DELETE CASCADE deserves a design review. It may be appropriate for disposable child data. It is dangerous when the child is the only record of what a tool did.

The fixture also tests ON DELETE RESTRICT. Under that relationship, replacing an existing job with a receipt raises sqlite3.IntegrityError; the before-and-after snapshots match. The status-only UPSERT still succeeds. This makes the accidental delete visible rather than silently discarding the child.

Do not switch constraints in a deployed ledger without examining actual retention and deletion requirements. A legitimate purge process may depend on cascade behaviour. A retained audit record may need a separate lifecycle, protected storage or a reference that survives removal of operational job data. RESTRICT also does not prevent someone with sufficient access from deleting the receipts first. It is a relational safeguard, not an access-control system or tamper-proof archive.

The earlier foreign-key enforcement article covers checking that enforcement is enabled. This example assumes that check passes. The deletion occurs because enforcement is working as configured.

Run the preservation tests

Download and inspect agent-sqlite-replace.py. It requires Python 3.9 or later with SQLite 3.24.0 or later, the release that introduced UPSERT. The script checks the SQLite version at startup and creates only in-memory databases.

python3 agent-sqlite-replace.py
python3 agent-sqlite-replace.py --test
python3 -O agent-sqlite-replace.py --test

The demonstration prints:

REPLACE: jobs=1, receipts=0, fk_violations=0
UPSERT: jobs=1, receipts=1, fk_violations=0

The eight tests cover cascade deletion with recursive triggers off and on, receipt preservation under UPSERT, insertion of a new job, replacement with identical parent values, explicit rollback, the RESTRICT relationship, and repeated UPSERT. They use unittest checks that remain active under Python optimisation.

These are local SQL semantics tests. They do not test crash durability, concurrent workers, access control, remote tool outcomes or a production migration. Their acceptance condition is concrete: the intended parent mutation occurs, and every pre-existing receipt that the operation must preserve remains unchanged.

For the wider approach to agent adapters and review evidence, see my Claude Code book, available as a Kindle edition on Amazon UK.

Search the actual adapter and its generated SQL for REPLACE. For each use, identify the conflicting constraint and inspect every dependent table after the write. Keep the destructive counterexample in the regression suite alongside the repaired statement.