My Agent Logged 14,800 Successful Writes. SQLite Had Rolled Back 9,240 of Them.

A customer emailed me on a Tuesday: "Is the weekly digest broken? I haven't gotten one since the 3rd." I checked. The job had run every night. systemd said active (exited), exit code 0, eleven nights in a row. My dashboard said the agent had written 14,800 rows that month. SQLite had actually kept 5,560 of them. The setup A Raspberry Pi 4 with a USB SSD. One Python agent that collects results and writes them to app.db. One small Flask admin page I use to check things. One nightly backup at 03:10. All three touch the same file. I thought that was fine because "SQLite handles concurrency." It does. It handles it by refusing your write — and I had spent a year teaching my agent to ignore refusals. Mistake 1: the PRAGMA that never ran My init code looked like this: def init_db(): if Path(DB).exists(): return conn = sqlite3.connect(DB) conn.execute("PRAGMA journal_mode=WAL") ... I created app.db by hand in month one, poking at the schema in a REPL. From that day on, init_db() returned on line 2 and the database stayed in SQLite's default rollback journal mode for a year. I had "enabled WAL" in the sense that the string WAL existed in my repository. That is not the same thing. It's a one-line answer if you check: sqlite3 app.db "PRAGMA journal_mode;". The setting is persistent per database file, so you only have to look once. Mistake 2: a reader that held the door shut for 90 seconds My admin page had a route that streamed a report: cur = conn.execute("SELECT * FROM results WHERE run_id = ?", (run_id,)) for row in cur: # read transaction stays open row["summary"] = summarize(row) # HTTP call, ~200ms each yield render(row) In rollback journal mode, a reader holds a SHARED lock for the life of its transaction. A writer can append, but it cannot commit until every reader is gone. That page took about 90 seconds to render 450 rows, and I left it open in a browser tab most mornings. So my agent's commit window was, several times a night, exactly as long as I felt like staring at a dashboard. Mistake 3: counting rows I never wrote Here is the write path, and it is the part that still bothers me: try: for r in rows: cur.execute("INSERT INTO results VALUES (?,?,?)", r) conn.commit() written += len(rows) # the lie except Exception as e: log.warning("db issue: %s", e) # swallowed Two things wrong. len(rows) measures intent, not outcome. And when commit() raised database is locked after the default 5-second timeout, the bare except caught it, logged a WARNING into a log file that rotated weekly, and the function returned normally. The job exited 0. systemd was satisfied. My counter had already been incremented, so the number I looked at every morning was the number of rows I tried to write. Across 11 nights: 14,800 attempts, 5,560 rows on disk, 41 digests never sent. I found out from a customer, not from monitoring. The shape of the failure is what I want you to notice. Every signal I had was green, because I had built all of them around "did the process finish" instead of "did the data land." What I changed One connection factory, PRAGMAs applied every time. busy_timeout is per-connection, not per-database. If your agent opens a fresh connection per task, a PRAGMA you set once in a REPL did nothing for it. def connect(): conn = sqlite3.connect(DB, timeout=15, isolation_level=None) conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA synchronous=NORMAL") conn.execute("PRAGMA busy_timeout=15000") return conn WAL is what kills the 90-second-tab problem outright: readers no longer block writers. Keep the file on ext4. I've watched WAL refuse to enable on an SMB mount and on a cheap exFAT USB stick, and the failure surfaced much later as a confusing "database is locked." Writes start with BEGIN IMMEDIATE. In deferred mode SQLite takes a read lock first and upgrades at commit; if two writers overlap, one gets SQLITE_BUSY at the worst possible moment. Immediate takes the write lock up front, so you wait at the start of the transaction, where retrying is sane. Retry only the lock, with backoff, and bounded: for attempt in range(5): conn = connect() try: conn.execute("BEGIN IMMEDIATE") conn.executemany( "INSERT INTO results (run_id, customer_id, payload) " "VALUES (?,?,?) ON CONFLICT(run_id, customer_id) DO NOTHING", batch, ) conn.execute("COMMIT") break except sqlite3.OperationalError as e: if "locked" not in str(e) and "busy" not in str(e): raise time.sleep(0.5 * 2 ** attempt) finally: conn.close() else: raise RuntimeError(f"db still locked after 5 attempts, run {run_id}") The ON CONFLICT DO NOTHING with a unique index on (run_id, customer_id) is what makes retries safe. Before I had that, a retry meant duplicate digests, which is why my earlier instinct was simply not to retry. That instinct cost me 9,240 rows. Assert the write. After the batch, count what is actually in the table and compare: n = conn.execute( "SELECT COUNT(*) FROM results WHERE run_id = ?", (run_id,) ).fetchone()[0] if n != len(rows): raise RuntimeError(f"wrote {n} of {len(rows)} rows for run {run_id}") If the count is short, the job fails loudly. I would much rather get a 3 AM page than a Tuesday email from a customer. Monitor freshness, not exit codes. One query, once an hour: SELECT ROUND((julianday('now') - julianday(MAX(created_at))) * 24, 1) AS hours_stale FROM results; Anything over 26 hours is an alert. This is the check that would have caught the whole thing on night one, and it doesn't care why the writes stopped — locks, expired credentials, a full disk, an agent that quietly decided not to run. It only asks whether the data is current. Serialise the writer. The admin page no longer writes anything. The backup now uses sqlite3 app.db ".backup backup.db" instead of cp, because copying a live SQLite file gets you a torn copy that opens fine and is silently missing pages. cp on an active database is a coin flip you only lose once. The uncomfortable lesson I didn't have a concurrency bug. I had a reporting bug that hid a concurrency bug, sitting underneath a monitoring setup that could only detect a process that died — and processes that fail at SQLite almost never die. They log a warning and return. Those 11 days weren't caused by SQLite's locking model. SQLite was correct the entire time. It told me, 9,240 times, that it could not commit. I had written code specifically to make sure I never heard it. If you're running an agent against SQLite on a Pi right now, two commands are worth running before you close this tab: sqlite3 your.db "PRAGMA journal_mode;" grep -c "database is locked" your-agent.log If the first says delete and the second says anything above zero, you already know what your next hour looks like. The connection factory, the startup assertions, the write-boundary rules, and the freshness check above are four of the patterns I use in every agent I ship. I write up the specific playbooks in The Solo Operator's AI Agent Playbook — code LAUNCH90 at checkout makes it $1.90. If it doesn't save you 5 hours in week one, reply to the receipt for a refund.