Production writes were silently rolled back and the tests could not see it

This one is from a side project in the app park: a small lottery statistics app. Every week it fetches the latest draws, refits a model of which numbers people tend to pick and stores the output. The whole chain runs as a single weekly command.

On 2 July 2026 we found that the weekly run saved nothing in production. It ran, and the end-to-end test passed. But the model, the prediction and the suggestions it wrote were all rolled back when the run closed its database connection.

What the code did

The app talks to Postgres through psycopg 3. One helper, db.connect(), opened every connection the app used. It opened them with default settings, which means autocommit is off.

With autocommit off, the first statement on a connection quietly starts a transaction, and that is true for a plain SELECT as well. In the weekly run, plain SELECTs opened such an outer transaction, and the writes ran inside it.

The writes were wrapped in explicit transaction() blocks, which looks correct. But a transaction() block opened inside an existing transaction does not commit. It becomes a savepoint inside the outer transaction. When the run ended and called close(), the outer transaction had never been committed, so everything inside it was rolled back.

Why the tests were green

The task notes for the weekly task, dated the same day, say the whole weekly chain had been tested end to end against real Postgres with a mocked API, including a stable re-run. Those notes record 84 passing tests. The test used a real database, not a mock, so it looked like solid coverage.

The gap was in how the test checked its results. It passed its own connection fixture into the weekly run and then counted rows on the same connection. A connection can always see its own uncommitted writes. The rows were there for the test, so the assertions passed. Nothing in the test ever opened a second connection and asked whether the data had actually reached the database.

The commit notes that the tests did not see the bug because they read their own transaction. The task file adds the word that matters: the tests read their own uncommitted transaction.

There was an earlier hint

The same problem had shown up in a different form a few tasks earlier. When the dashboard was built, the test suite hung. The cause was cached read connections sitting idle in a transaction and holding locks, which blocked schema changes. The task notes record that this was found when the AppTest suite hung.

The fix then was local. The dashboard set autocommit on its own connection after calling db.connect(), with a comment explaining that it only reads. That solved the dashboard’s problem. The shared helper that every other part of the app used kept its default, and the weekly writer kept opening its implicit outer transaction. Looking back, that was the moment to ask why one caller had to switch off the helper’s default behaviour.

How it was found

The app had been built and tested against fixtures and a mocked API. The last remaining task was a full backfill of the real draw history, run with Henning’s authorisation. The commit records the backfill: 141 API windows, exit code 0, 775 draws from 27 August 2011 to 27 June 2026, all of them with prize tables and turnover. Rows before August 2011 turned out to be stubs without winning numbers.

After the backfill came the first real weekly run. The task file records, in Norwegian, that the weekly run did not persist the model, the prediction or the suggestions, and that the bug was found and fixed during the backfill task.

Who got what wrong

Henning writes no code himself, so the original connection code and the test were Claude’s. The task file is also kept by Claude Code, whose notes on the weekly task called the chain end-to-end tested. The diagnosis and the fix are in the same commit as the backfill. The task file records the bug as found and fixed during the backfill task.

What we can say with evidence is that a test which reads back through the writer’s own connection gave a false green, and that the green was reported as end-to-end verification.

The fix

Two changes, both in the same commit:

1. db.connect() now opens connections with autocommit on. Plain reads no longer start a hidden transaction. Writes that need to be atomic still use explicit transaction() blocks, and with no outer transaction those blocks now commit when they finish. The commit calls this the psycopg3 pattern. The dashboard’s local workaround was removed, because the shared helper now does the right thing for every caller.
2. The end-to-end test now runs the weekly cycle on a separate autocommit connection, opened the same way as in production and closed after the run. It then checks the results through the test’s own connection. A missing commit now shows up as missing rows. The comment in the test says it is there to expose a missing commit that the shared fixture would have hidden.

After the fix the weekly run was re-run, and the task file records that persistence was verified in the database after the re-run. The suite stood at 88 passing tests.

The rule

Check that data was saved by looking at the database through a separate connection that is set up the way production sets it up. Never check through the connection that did the writing. A connection that reads its own uncommitted writes will report success whether or not anything was saved.

The short version: the database connection is autocommit, writes go in explicit transactions, and the end-to-end test reads its results on its own connection.