A SQLite transaction block leaves the connection open

A Python tool can finish its database write inside a with block and still leave the SQLite connection open. This is easy to miss in a small assistant that saves one action receipt at a time. The indentation looks like resource cleanup, but the standard connection context manager handles transaction completion. Connection lifetime needs its own owner.

Python's SQLite documentation describes a successful block committing an open transaction and an uncaught error rolling it back. The same documentation says that leaving the block does not close the connection. Transaction settings matter: when there is no open transaction, or autocommit is enabled, that context manager has no transaction work to perform.

We checked this with Python 3.13.2 and a temporary local database using legacy transaction control. One insertion survived a successful block, and the same connection still accepted a query afterward. A deliberate exception in another block rolled back its insertion while preserving the earlier committed row. These were local development checks with numbered fixture records, not measurements of a production assistant.

For a connection created for one short operation, an outer contextlib.closing block can own cleanup while an inner connection block handles the transaction. In our fixture, that combination saved the intended row and left the connection closed. Later queries on it raised ProgrammingError. We also tested closing alone around an insertion without committing it. Under the tested settings, that insertion was absent after closure. Resource cleanup by itself did not save the pending write.

Before using a database wrapper to record tool actions, check both outcomes: the intended receipt is committed, and a connection that should be finished has actually closed. If the application deliberately reuses a connection, document who closes it at shutdown. Keep that ownership explicit so a later refactor does not create a fresh connection for every event while relying on transaction indentation to clean it up.