Open the connection-check diagram at full size.
An agent’s action ledger has an approval_id column with a REFERENCES approvals(approval_id) constraint. The schema looks reassuring. A worker inserts an action pointing to an approval that does not exist, and SQLite accepts it.
That can happen when foreign-key enforcement is off on the worker’s connection. The declaration survives in the database schema; enforcement depends on the connection using it. A migration that enables the setting does not establish the setting for every later worker.
I would make connection initialisation part of the ledger’s acceptance tests. A schema review cannot establish that the deployed write path rejects orphaned actions.
The example below is a synthetic application fixture. It inserts ledger rows in memory, performs no external actions and makes no model calls. It demonstrates a referential-integrity failure, not a vulnerability in Claude Code or evidence that a real deployment bypassed human approval. Even with the repair, an existing approval row does not prove that the proposed action is authorised.
The constraint exists, but the connection does not enforce it
SQLite’s foreign-key documentation says applications must enable enforcement separately for each connection. Its documented default is off, but SQLite warns against relying on the default. Builds can differ, and a wrapper may configure a connection for you. Read back the setting on the connection that will write.
Consider this deliberately small ledger:
CREATE TABLE approvals (
approval_id TEXT NOT NULL PRIMARY KEY,
revoked INTEGER NOT NULL DEFAULT 0 CHECK (revoked IN (0, 1))
);
CREATE TABLE actions (
action_id TEXT NOT NULL PRIMARY KEY,
approval_id TEXT NOT NULL REFERENCES approvals(approval_id)
);
There are no approval rows. With enforcement off, this statement succeeds:
INSERT INTO actions VALUES ('action-1', 'missing');
NOT NULL does not rescue the relationship: 'missing' is a non-null value. Conversely, foreign-key enforcement alone would not reject a null child key. SQLite explicitly permits that case unless the schema also forbids nulls. The two constraints answer different questions.
The earlier retry-ledger example examined missing values in a unique operation key. This example keeps its identifiers present and tests whether the named parent exists at all. Passing one regression gives no assurance about the other.
Run the counterexample
Download and inspect the standard-library Python fixture. Run it with Python 3.9 or later and an SQLite library with foreign-key support:
python3 agent-sqlite-approval.py
python3 agent-sqlite-approval.py --test
python3 -O agent-sqlite-approval.py --test
The default run prints:
disabled_orphan_rows=1
integrity_check=ok
foreign_key_violations=1
enabled_orphan=rejected
enabled_action_rows=0
The weak path explicitly turns enforcement off. That makes the counterexample reproducible even on a build whose default is on; it does not claim that your installation starts with enforcement off. The repaired path enables enforcement, reads it back and attempts the same invalid insert. SQLite rejects the insert, leaving the action table empty.
The fixture passed its 11 tests on Python 3.9.6 with SQLite 3.51.0, both normally and with Python optimisation enabled. The suite also tests valid parent references, null rejection, parent deletion and an attempted setting change inside a transaction. These are local database results. They do not establish distributed approval enforcement or atomicity between a ledger write and an external API call.
One test deliberately accepts an action whose parent approval has revoked=1. That passing test records a limit of foreign keys: the referenced row exists, so the constraint is satisfied. A real authorisation path must reject revoked approval independently.
Enable enforcement before opening the transaction
The connection helper uses explicit SQL transaction control:
db = sqlite3.connect(":memory:", isolation_level=None)
It then calls this function before creating the fixture’s tables or admitting writes:
def require_foreign_keys(db):
if db.in_transaction:
raise RuntimeError("configure enforcement before BEGIN")
db.execute("PRAGMA foreign_keys = ON")
if db.execute("PRAGMA foreign_keys").fetchone() != (1,):
raise RuntimeError("foreign key enforcement unavailable")
The transaction check is deliberate. SQLite’s documentation for PRAGMA foreign_keys states that changing the setting inside a transaction is a no-op. This sequence can therefore leave enforcement off without raising an error:
PRAGMA foreign_keys = OFF;
BEGIN;
PRAGMA foreign_keys = ON;
The regression confirms that the value remains zero and that an orphan can still be inserted and committed. The repaired helper refuses an already active transaction rather than pretending that its configuration took effect.
This also affects Python connection setup. If your wrapper opens a transaction immediately, configure enforcement through a supported initialisation path before that transaction starts. Do not commit a caller’s transaction inside a convenience helper to make the pragma work. That would change the caller’s transaction boundary and could commit unrelated work.
In the download, isolation_level=None leaves transaction boundaries under explicit SQL control. A production adapter must choose its transaction mode deliberately; copying that setting without adding the transactions your workflow needs would introduce a different defect. Check the Python sqlite3 transaction-control documentation against the Python version you deploy.
Readback also handles a library without usable foreign-key support. SQLite documents builds where the pragma returns no row. The helper treats anything other than (1,) as failure. It does not let the worker continue on an assumption.
An integrity check can pass with an orphaned approval reference
The demonstration’s integrity_check=ok line is real. SQLite documents that PRAGMA integrity_check does not find foreign-key errors. For those, use PRAGMA foreign_key_check.
Enabling enforcement does not repair old orphan rows. The suite first inserts an orphan with enforcement off, enables enforcement and confirms that the violation is still present. A successful setting readback is evidence about the connection’s current behaviour, not a certificate for every historical record.
For an existing ledger, separate the rollout into a write-path repair and a data audit. Configure every writer, then examine foreign_key_check results under a controlled maintenance or consistent-snapshot procedure. Record the affected action and approval identifiers in restricted incident evidence. Decide whether to quarantine the action, reconstruct a genuinely recoverable reference or investigate an unknown outcome.
Do not manufacture approval rows merely to make the check return an empty result. That would convert missing evidence into a fabricated approval history. Nor should a cleanup script automatically delete the action records: those rows may be the only remaining evidence that work was attempted.
A clean foreign-key check has a narrow meaning. Every checked child reference satisfies its declared relationship. It says nothing about whether an approver had authority, whether the approved payload matches the action, or whether the action already ran elsewhere.
Test the writer that will run after the migration
Put the configuration in the connection factory used by each worker, command-line repair utility and background job. Test a fresh connection created through that factory. Testing only the migration connection misses the failure this article demonstrates.
If the application uses a pool, verify its physical-connection creation hook and its reset behaviour. A request may receive a connection different from the one used during startup checks. Treat a disabled setting as a failed readiness condition for that writer. If untrusted code can execute arbitrary SQL on the connection, this pragma is not a security boundary against that code; restrict database access rather than expecting a session setting to resist its owner.
Then test the approval policy above the database constraint. Use an existing but revoked approval, an approval for another tenant and an approval for a different payload. Those require application checks or a stronger data model. Where queued actions can outlive permissions, recheck the applicable policy at dispatch and design for concurrent revocation. A foreign key cannot close that timing gap by itself.
Finally, inject an action with a nonexistent approval through the real adapter. Verify that the insert fails and that the downstream client receives no call. The downloadable fixture proves the insert rejection only. Your integration test must establish that the dispatcher does not perform the effect first, catch the database error later and leave the system with an action nobody can account for.
For broader production tool controls and review practices, see my Claude Code book.