Reading a SQLite Database With the macOS CLI Left an Empty WAL File and Tripped My Freshness Alarm

Originally published on hexisteme notes.

On 2026-11-02 at 09:00:48, the scheduled job in my agent telemetry pipeline raised F3-SOURCE-DB_UNAVAILABLE with the reason active WAL present. My first question was whether the raw archive had been lost. It had not. The next 7 ticks of the job all exited with rc=0, and the archive count rose monotonically from 10231 to 10346 over that stretch. The source database was fine. The check that read it had made a decision from the wrong evidence.

What the check was doing

The pipeline keeps a SQLite database of agent sessions, and a freshness check reads the newest ended_at from it to decide whether the data is current. The check is meant to be a passive observer. Its rejection message says so directly: "read-only observer will not create or mutate SQLite sidecars". To keep that promise it opens the database with an immutable=1 URI, so the read does not create a -wal or -shm file of its own.

That choice has a cost the code accepts on purpose. An immutable read does not pick up frames that are still sitting in the write-ahead log, so when the WAL holds frames, the value it returns can be stale. The check therefore refused to read whenever a -wal file sat beside the database, and reported DB_UNAVAILABLE instead of a freshness verdict.

The refusal was keyed to the file's existence. Any -wal file, of any size, meant "unavailable".

The reproduction

On 2026-11-06 at 07:35 I reproduced the condition. Reading the database with the macOS system CLI, /usr/bin/sqlite3, and doing nothing but reading, left a 0-byte -wal file and a -shm file next to it. That is enough to trip a rule that only asks whether the file exists.

I have not looked into why the CLI leaves those files behind after a read, and I am not going to explain SQLite internals I did not check. What I can say is narrower: on this machine, with this database, a read through that binary leaves an empty -wal file, and my check treated an empty file the same way it treated a log full of frames.

This is not the first time a read of mine tripped this check. In Fixing a Python fromisoformat Error Unmasked a Stale Alarm That Had to Fire, a direct query I typed during an investigation, through Python's sqlite3 with a mode=ro URI, left the same pair of sidecar files and produced the same DB_UNAVAILABLE (active WAL present). That time I changed the caller: I deleted the files by hand and switched my direct queries to immutable=1. The rule stayed as it was. This time the trigger was a different client, the system CLI, and fixing my own habits again would only have covered the next client I happened to think of. So this time the rule changed.

What I could not pin down

I do not know what opened the database on 2026-11-02. The reproduction shows one way an empty -wal file appears, not that a CLI read is what happened that morning. The record is also short on the recovery: after the weekly build at 09:30, the check was normal again from the next tick. I did not confirm what that build did to the sidecar files.

The fix: size, not existence

The new rule looks at how many bytes are in the -wal file. A 0-byte file means there are no frames to apply, so this check now lets an empty WAL through. Only a file larger than 0 bytes still blocks the read. This is the relevant part of _read_ended_at after the change:

        try:
            wal_bytes = Path(str(path) + "-wal").stat().st_size
        except FileNotFoundError:
            wal_bytes = 0
        if wal_bytes > 0:
            return None, "active WAL present; read-only observer will not create or mutate SQLite sidecars"
        uri = path.as_uri() + "?mode=ro&immutable=1"

A missing file and an empty file now take the same path. A WAL that still holds frames keeps the old behaviour, because reading past it with immutable=1 is exactly the case where the answer could be stale. I wanted to drop the false alarm without giving up the reason the alarm existed.

Two tests, one for each side of the line

The first regression test builds a small database, puts an empty -wal and a zeroed -shm of 32768 bytes beside it, and expects a FRESH verdict. It also snapshots the directory before the call and asserts the snapshot is unchanged afterwards, so the observer still does not create or modify any files while it reads. I confirmed this test failed before the fix.

def test_zero_byte_wal_left_by_cli_read_is_readable(tmp_path):
    home = _home(tmp_path)
    db = _make_db(tmp_path / "wal.db", ["2026-10-08T12:00:00Z"])
    Path(str(db) + "-wal").write_bytes(b"")
    Path(str(db) + "-shm").write_bytes(b"\0" * 32768)
    before = _tree_state(tmp_path)
    assert _freshness(db, home)["status"] == "FRESH"
    assert _tree_state(tmp_path) == before

The second test pins the other side. A -wal file with some bytes in it must still produce DB_UNAVAILABLE, with the same reason text as before. Without it, a later change could relax the rule to "never block" and the first test would still pass.

def test_nonempty_wal_stays_unavailable(tmp_path):
    home = _home(tmp_path)
    db = _make_db(tmp_path / "wal.db", ["2026-10-08T12:00:00Z"])
    Path(str(db) + "-wal").write_bytes(b"frame")
    report = _freshness(db, home)
    assert report["status"] == "DB_UNAVAILABLE"
    assert "active WAL present" in report["reason"]

The full suite passed with 147 tests, and the shared telemetry environment check passed. I also ran a CLI query against a copy of the production database and then ran the check against that copy. It produced no alert output.

The next weekly report

The weekly report generated after the fix shows the check reading the source normally. Its ingest health section reports source freshness as FRESH, with source_data_age_seconds=1923.098247 and the newest ended_at at 2026-11-09T00:00:03.108000Z. The latest ingest log line in the same report is 2026-11-09 09:00:34 rc=0 source_readable=4636 archive=10443 prev_archive=10428, and the F3 line reads normal. That is a single report after the fix, so it tells me the fix did not break the normal path. It does not tell me the check has stopped producing false alarms in general.

What is still open

• I still do not know what opened the database on 2026-11-02. If it was something other than a CLI read, there may be another way to produce an empty -wal that I have not seen yet.
• The alert writes a flag file. I deleted it by hand on 2026-11-06. Nothing clears that flag automatically when the condition goes away, so a false alarm stays raised until a person removes it. That path still does not exist.
• The size test assumes that a -wal file with 0 bytes holds nothing an immutable read would miss. I relied on that, and on the reproduction on this machine. I did not check it against SQLite's documentation.
• A non-empty WAL still blocks the read. If a writer leaves frames in the log for a long stretch, the check will report DB_UNAVAILABLE for that whole stretch, and the record does not show whether that happens in practice.

Email list for these notes: hexisteme.beehiiv.com — no issue has gone out yet, so you would be on it before the first one. No welcome sequence, no course, no upsell.

More notes at hexisteme.github.io/notes.