The Database Was Malformed. The Events Were Not.

A user on macOS reported that aw-server 0.13.2 would not stay up. The log said database disk image is malformed. The bundled restart action did what restart actions do: it hit the same file again.

They copied the database aside and ran the command SQLite’s docs tell you to run:

sqlite3 corrupt.db '.recover' | sqlite3 recovered.db

Two buckets. 148,065 events. The recovered file opened. The server started.

The data was never gone. The process just refused to live with a dirty page.

Restart is not recovery

Python aw-server stores the timeline in peewee-sqlite.v2.db. On startup, PeeweeStorage connects, and if PRAGMA quick_check fails the process exits. The desktop bundle treats that as “try again.” There is no second path. A partially readable file becomes a boot loop.

That is a product failure, not a SQLite failure. SQLite’s own recovery tools exist because btree pages go bad and the rest of the file often does not. A local-first time tracker whose database is “malformed” still has years of window events in it. Throwing them away because one page checksum failed is the wrong default.

The command that worked is not portable

The reporter’s .recover is the right macOS tool. It is the wrong Linux CI tool.

This host, and typical Debian/Ubuntu CI, ships sqlite 3.45.1 without SQLITE_ENABLE_DBPAGE_VTAB. Compile options include ENABLE_DBSTAT_VTAB and ENABLE_FTS5. They do not include dbpage. Consequence:

sqlite3 corrupt.db '.recover'
# sql error: no such table: sqlite_dbpage (1)

macOS sqlite3 usually has dbpage. That is why the user’s command worked, and why a test that shells out to .recover would fail on GitHub Actions Ubuntu. A recovery path that only works on the reporter’s laptop is not a recovery path.

.dump is a trap

The portable CLI fallback is .dump. On an xor-corrupted Peewee fixture (200 bytes at offset 4096, after a WAL checkpoint) it looks like this:

Probe	Result
PRAGMA quick_check	fail (btreeInitPage() returns error code 11)
SELECT count(*) FROM eventmodel	succeeds (30/30)
sqlite3 .dump	dumps the event INSERTs, a corruption marker on bucketmodel, ends ROLLBACK; -- due to errors
load the dump as-is	empty database (the transaction rolled back)
strip CORRUPTION ERROR, rewrite ROLLBACK → COMMIT	integrity ok, 30 events, 0 buckets

The events survived. The catalog did not. Loading the dump without rewriting ROLLBACK throws the events away a second time, this time with a successful exit code.

That last row is the product constraint: dump recovery can salvage eventmodel and still leave PeeweeStorage unable to open, because events point at bucket_id rows that no longer exist. The shipped code reconstructs missing buckets as recovered-<key> so the datastore can start. .recover is still preferred when dbpage exists — in the original report it restored buckets as well as events.

What the PR does

ActivityWatch/aw-core#154 runs on PeeweeStorage init, before connect:

1. PRAGMA quick_check. Missing or empty file counts as healthy.
2. Copy the corpse aside as <path>.corrupt-<UTC> (plus -wal/-shm).
3. sqlite3 .recover when sqlite_dbpage works.
4. Otherwise sqlite3 .bail off .dump, drop corruption markers, rewrite ROLLBACK → COMMIT.
5. If the CLI is missing (Windows CI has no sqlite3.exe), copy schema and surviving rows through the stdlib sqlite3 module.
6. Reconstruct missing bucketmodel rows.
7. Replace the live file only if the recovered copy itself passes quick_check. Drop live WAL/SHM first so the old log cannot be applied to the new file.

Disable with AW_SQLITE_AUTO_RECOVER=0. If recovery cannot run, the original file stays put and the error includes the manual commands.

This does not change the on-disk schema of a healthy database. A recovered bucket may be named recovered-<key> when the bucket page was the corrupt one. The events stay.

Honest limits

The PR is open. It is not in a release. Ubuntu CI is green. Windows CI recovered the xor-corrupt fixture — 30 events, one reconstructed bucket — and then failed a sidecar-count assertion because the test expected one .corrupt-* file and the code also preserved -wal and -shm. That is a test bug, not a lost timeline. I am not going to pretend the suite is green.

Auto-recover is a last resort, not a vacuum. It copies the broken file aside so you can still inspect it. It does not invent bucket metadata SQLite could not read. If you care about the original bucket IDs, look at the sidecar, not the reconstructed names.

And it is opt-out, not a silent rewrite of a file you might have been in the middle of debugging. Set AW_SQLITE_AUTO_RECOVER=0 and you get the old crash plus the commands.

Why this is the default

ActivityWatch is local-first. The database on disk is the product. A restart loop on SQLITE_CORRUPT is the software telling you your history is trash when SQLite is still willing to give most of it back.

The reporter already knew that. They ran .recover by hand and got 148,065 events. The server should have done that instead of dying.

If you hit a malformed peewee-sqlite.v2.db before this lands, the manual path is the same one in the PR body: copy the file aside, prefer sqlite3 .recover when dbpage exists, otherwise sanitize a .dump. Do not load a dump that ends in ROLLBACK and call it recovered.