knowledgeC.db-wal: Recovering Recent Events from the WAL

TL;DR. knowledgeC.db runs in SQLite WAL mode: new transactions are appended to knowledgeC.db-wal and only copied into the main file at a checkpoint. Until then, the most recent events exist only in the WAL, and rows deleted by a recent transaction are still in the main file. Collect the -wal with the database, work on copies because opening the original can checkpoint it, and read the WAL as a list of committed page versions validated by salts and checksums. KnowledgeC Parser applies the committed frames and flags each row as "only in the WAL" or "removed by the WAL".

Why the WAL matters for knowledgeC

Many write-ups on the knowledgeC database query knowledgeC.db alone. On a live or recently shut down Mac that often misses the part of the timeline you care about most: the last hours before collection. CoreDuet writes rows when intervals end, the rows go into the WAL, and they reach the main file only when SQLite checkpoints. An examiner who parses the main file alone sees a timeline that stops early, and may conclude that the Mac was idle when it was not.

The reverse also happens. Retention pruning deletes old rows; the deletion is a WAL transaction, and the main file keeps the old rows until the next checkpoint. Both cases are visible only if you have both files and compare them.

How SQLite WAL works

The reference is the SQLite documentation on write-ahead logging and the WAL file format. In short: the database is a set of fixed-size pages. In WAL mode a writer never changes pages in the main file directly. It appends new versions of the changed pages to the -wal file; readers look for the latest committed version of a page in the WAL first, and fall back to the main file.

The WAL header

The -wal starts with a 32-byte header, big-endian:

Frames

After the header come frames: a 24-byte frame header followed by one full page.

Which frames count

A frame is valid only if its salts match the WAL header and its checksum matches the running checksum computed from the start of the file. Reading stops at the first frame that fails either test. Among valid frames, only those up to the last commit frame (non-zero commit size) belong to committed transactions. Frames after it are an unfinished transaction and are ignored by SQLite, so they must not be treated as database content. If a page appears in several committed frames, the latest one wins.

Checkpoints and resets

A checkpoint copies the latest committed version of each page from the WAL back into the main file. By default SQLite runs one automatically when the WAL reaches about 1000 pages, and normally when the last connection to the database closes. After a checkpoint, the next writer can restart the WAL from the beginning: salt-1 is incremented and salt-2 changes, and the file is not necessarily truncated. Frames from the older generation can therefore remain after the valid ones. Their salts no longer match, so SQLite ignores them; they are stale page versions, not part of the current database.

The -shm file

knowledgeC.db-shm is the WAL index: shared memory that helps connections find pages in the WAL quickly. It holds no data that is not in the WAL and can be rebuilt from it. Collect it with the other two files for completeness, but committed content comes from the -wal.

Two cases that change a timeline

Rows that are only in the WAL

A row inserted since the last checkpoint exists only as a page version in the -wal. Parse the main file alone and the row is absent. These are typically the newest rows, so the gap sits at the end of the timeline, right before collection.

Rows removed by the WAL

A committed DELETE produces new page versions without the row, in the WAL. The main file still holds the old page, with the row. Reading the database "as SQLite would" hides the row; reading the main file alone shows it. Comparing both views tells you the row existed and was removed after the last checkpoint.

In knowledgeC, the usual cause is retention pruning of old events. A row removed by the WAL is not by itself evidence of deliberate deletion; check its date against the retention window you measured on the evidence before reading anything into it. Rows modified in place keep their identity and simply show their newer values.

Working safely

• Never open the original. Opening a WAL database with a normal SQLite client can checkpoint it: WAL pages are written into the main file, and the WAL can be reset. Both of the cases above disappear.
• Keep a pristine copy. Hash the three files as collected, and parse a second copy.
• Know which view you are looking at. A SQLite client given both files shows the database with the WAL applied, and may checkpoint your copy. A copy of the main file without its -wal shows the state at the last checkpoint. You need both views to see what the WAL added and removed.
• Record what you did not have. If the -wal was not collected, say so in the report: the absence of recent events is then a collection gap, not a finding. How to collect it is in knowledgeC.db location and acquisition.

How KnowledgeC Parser handles the WAL

KnowledgeC Parser reads the files read-only in your browser and pairs each knowledgeC.db with the -wal of the same name. Then it:

1. checks the WAL header: magic, page size equal to the database page size, header checksum;
2. reads frames in order while salts match and the running checksum is correct, and stops at the first failure with a note such as "checksum mismatch at frame N" or "salt mismatch";
3. applies only committed transactions, notes how many frames of an unfinished transaction were ignored, and overlays the latest committed version of each page on the main file;
4. reads ZOBJECT twice, with and without the WAL, and matches rows by record number and UUID.

Each knowledgeC event then carries one of two flags when relevant: only in the WAL (present with the WAL, absent from the main file) or removed by the WAL (present in the main file, deleted by a committed WAL transaction). The file report summarises the result as "WAL applied: N committed frame(s), X row(s) only in the WAL, Y row(s) removed by it". When a database in WAL mode arrives without its -wal, the report says so and warns that recent events may be missing. Stale frames from older WAL generations are not used.

A synthetic example: FIN-MBP-03

The sample collection shipped with the tool is synthetic; the Mac, the user and the events are fictional. On the MacBook FIN-MBP-03, the main file of the system knowledgeC.db holds rows written up to about 10:00 UTC on 2026-09-14. Everything written later sits in one further committed transaction in the WAL.

Parsed without the -wal, the database shows dana.whitlock's last unlocked interval of the morning ending at 09:58 UTC, when she locked the Mac for lunch, and nothing after it. Parsed with it, the report reads "WAL applied" with a few dozen rows only in the WAL and one removed by it. The rows only in the WAL include:

• a /device/isLocked unlock interval starting at 10:04:31 UTC (ZSECONDSFROMGMT 7200, so 12:04 local), ending at 10:49 UTC;
• /app/usage intervals for Safari, Finder, Archive Utility, Terminal and System Settings inside that window.

The row removed by the WAL is a /app/usage interval for com.apple.mail on 2026-08-10: an old event pruned by retention, still sitting in the main file. It is a good illustration of why "removed" should not be read as "wiped".

What the WAL adds here is the existence and timing of activity during the lunch break. It does not say who was at the keyboard, and it does not name files or commands. For that interpretation, and for corroboration from Biome and other sources, see who was at the keyboard. To reproduce the example, load the sample from the home page or follow analyze knowledgeC and Biome in your browser.

What the WAL does not tell you

• When a frame was written. Frames carry no timestamps. A row in the WAL was committed after the last checkpoint; its event time comes from ZSTARTDATE and ZENDDATE, in Mac absolute time, not from its position in the file.
• Anything before the last reset. Stale frames from an older generation are ignored by SQLite and by the tool; they are outside the current database.
• What was deleted before the last checkpoint. Once a deletion is checkpointed, the row is gone from both views; leftovers in free space are a carving problem, outside what this workflow covers.

Related articles

• knowledgeC.db forensics: the complete macOS guide
• knowledgeC.db location and how to collect Biome streams
• Analyze knowledgeC and Biome in your browser
• Who was at the keyboard? Reading macOS activity carefully