Simon Willison just shipped sqlite-utils 4.0rc2 after spending $149.25 on Claude Fable prompts. Willison accelerated the work because Fable access was ending July 7th, making this a time-limited opportunity to refactor before the price increase. The interesting part is not the dollar amount. It’s that the agent uncovered a class of transaction bugs that would have caused silent data loss in production, and the fix required rethinking when writes commit.
The worst bug: delete_where() never committed. It left the connection in an uncommitted state, poisoning every subsequent write. Close the connection and everything rolled back. The agent found this during a pre-release review. A human probably would have shipped it.
The Transaction Boundary Problem
SQLite connections have an in_transaction flag. When true, writes are buffered until you commit. When false, writes are durable immediately (autocommit mode).
The old sqlite-utils behavior:
db.execute("INSERT ...")opened a transaction but never committed it- Writes appeared to succeed when read on the same connection
- Closing the connection silently rolled back everything
delete_where()used bareexecute()with no atomic wrapper
This created a failure mode where code looked correct but lost data:
db = sqlite_utils.Database("data.db")
db["users"].delete_where("id = ?", [123]) # Connection now in_transaction=True
db["logs"].insert({"event": "deleted"}) # Joins the open transaction
db.close() # Silent rollback: both operations lost, rows still presentThe poisoned state is invisible on the same connection. Reads succeed because uncommitted writes are visible to the connection that made them:
db = sqlite_utils.Database("data.db")
db["users"].insert_all([{"id": i} for i in range(3)], pk="id")
db["users"].delete_where("id = ?", [0]) # in_transaction=True now
# Read on same connection: delete appears to work
assert len(list(db["users"].rows)) == 2 # Passes
db.close()
# Reopen and read: delete was rolled back
db = sqlite_utils.Database("data.db")
assert len(list(db["users"].rows)) == 3 # All rows still presentThe new rule: write statements via db.execute() now auto-commit unless a transaction is already open. If you want grouped writes, you must explicitly open a transaction with db.begin() or use the db.atomic() context manager.
What the Agent Found
Fable ran 37 prompts over 34 commits, touching 30 files. The initial prompt was simple: “Final review before shipping a stable 4.0 release, very important to spot any last minute things that would be a breaking change if we fix them later.”
The agent categorized five issues as release blockers:
- delete_where() never commits: Left connection poisoned, all subsequent writes rolled back on close
- query() with INSERT RETURNING: Only committed after the generator was exhausted, so
next(db.query(...))left transactions open - query() auto-commits before validation:
db.query("UPDATE ...")committed the write before raising ValueError for non-row statements - enable_wal() silently committed open transactions: Broke rollback guarantees of
db.atomic() - Python 3.12 autocommit mode: Entire test suite failed because
commit()androllback()behave differently
The agent also wrote comprehensive documentation for the new transaction model, which Willison reviewed first. Reviewing documentation edits turned out to be an effective way to understand what changed.
Cross-Model Review as a Second Pass
After Fable finished, Willison prompted GPT-5.5 xhigh to review the changes. This caught two P1 issues Fable missed:
db.query("UPDATE ...")raises ValueError but the update is already committed (surprising side effect)INSERT ... RETURNINGthroughdb.query()only commits after full iteration, contradicting the docs
The cross-model review pattern: have Anthropic’s best model review OpenAI’s work and vice versa. Willison admits he used to think this was superstitious, but it turns up real issues often enough to be worth the cost. In this case, two P1 bugs caught for roughly the cost of a single GPT-5.5 xhigh session (estimated $3-5 based on typical pricing for high-tier models).
The New Transaction API
sqlite-utils 4.0 now has three transaction modes:
| Mode | Behavior | Code Example | Use Case |
|---|---|---|---|
| Auto-commit (new default in 4.0) | Every write method commits before returning | db["users"].insert({"name": "Alice"}) | Prevents silent rollback on close (fixes delete_where() poisoning bug) |
| Atomic context manager | Groups writes, rolls back on exception | with db.atomic(): ... | Multi-step operations that must succeed or fail together (prevents enable_wal() silent commit bug) |
| Manual control | Explicit transaction lifecycle | db.begin() … db.commit() | Complex workflows where you need savepoint control and explicit commit timing (prevents generator exhaustion bug) |
The library never commits a transaction you opened manually. This prevents the enable_wal() bug where changing journal mode silently committed open transactions.
Migrations now run inside transactions by default. If a migration raises an exception, its changes roll back and it stays pending. Migrations that cannot run in a transaction (like VACUUM) can opt out with @migrations(transactional=False).
Agent Cost Breakdown
Willison used AgentsView inside the Claude Code session to calculate costs:
- Main session (claude-fable-5): $141.02
- API-surface sweep agent: $2.40
- Transactions/atomic review agent: $2.39
- Post-rc1 commits review agent: $1.72
- Migrations review agent: $1.40
- Prompt-counting agent (claude-opus-4-8): $0.32
- Total: $149.25
Fable cost $141.02 across 37 prompts in the primary session (roughly $3.81 per prompt), while Opus 4.8k cost $0.32 for 4 subagent tasks (roughly $0.08 per task), a 47x difference per task. Willison notes he should have leaned more heavily into subagents for narrower review work.
The work happened during the July 7th Fable deprecation deadline, when even Claude Max subscribers would start paying full API costs. Willison upgraded from $100/month to $200/month to increase his Fable allowance and aimed to hit 100% usage before the price increase.
Failure Modes and Edge Cases
The transaction work exposed several SQLite edge cases:
- Generator exhaustion:
db.query("INSERT ... RETURNING")only commits when you iterate to the end. Callingnext()once leaves the transaction open. - Python 3.12 autocommit mode: Connections created with
sqlite3.connect(..., autocommit=True)orautocommit=Falsebehave differently. The library now raisesTransactionErrorif you pass one. - View vs table confusion:
drop-tablewould silently drop a view if the name matched. Now it exits with an error suggesting the correct command.
The library also added validation that raises ValueError instead of AssertionError. Previously, invalid arguments were rejected with bare assert statements, which are silently skipped when Python runs with the -O flag.
Technical Verdict
Use agent-driven refactoring for pre-release reviews when:
- You have a codebase with 20+ methods sharing connection state and no explicit transaction documentation. Agents excel at tracing
in_transactionflag mutations across method call chains and detecting savepoint vs commit mismatches that humans miss in diffs. In this case, Fable traced howdelete_where()left connections poisoned across 30 files. - Your library has implicit commit semantics where writes appear to succeed but silently roll back on connection close. Agents can run exhaustive end-to-end reproduction scripts (like the
db.close()/ reopen test) faster than humans can write them. - Generator-based APIs hide transaction state. The
INSERT ... RETURNINGbug required understanding thatnext(db.query(...))leaves transactions open because the commit lives at generator exhaustion. Agents follow generator lifecycle better than humans scanning code. - You can afford 10-15 minute agent think times while doing other work. Willison went to a parade while Fable ran.
- You want documentation written in parallel with code changes. Reviewing docs first helps you understand what changed.
- You are shipping a major version where breaking changes are acceptable. The 4.0 bump gave Willison room to fix transaction semantics without forcing a 5.0.
Avoid it when:
- Transaction semantics are tribal knowledge rather than docstring-documented. If rollback guarantees and commit boundaries are implicit, you will spend more time explaining context in prompts than reviewing fixes. Agents cannot infer
db.atomic()rollback guarantees from code alone. - Session cost exceeds 10% of your total release timeline budget. At $149.25 for 37 prompts, this works if your release cycle budget is $1,500+. For smaller projects, the ROI breaks down.
- You cannot verify agent claims with tests or cross-model review. Fable missed two P1 bugs (query auto-commit side effects, RETURNING generator exhaustion) that GPT-5.5 xhigh caught. Single-model review is insufficient for transaction boundary work.
- You need to explain every line changed to a compliance team. 34 commits across 30 files is hard to audit without automated tooling.
- Your library uses Python 3.12+ autocommit mode or other connection-level state that changes
commit()/rollback()behavior. Agents struggle with mode-dependent semantics unless you explicitly document them in prompts.
Cross-model review is worth the extra cost ($3-5 per session for GPT-5.5 xhigh) if you are shipping a major version with breaking changes. The second model catches different classes of bugs, especially around side effects and documentation contradictions. In this case, two P1 issues caught for roughly $5 is a clear win.
The transaction boundary work is a good example of what agents can find: bugs that are silent, rare, and only visible when you think about connection state across method calls. A human reviewer would need deep SQLite knowledge and time to trace through every code path. The agent had both.