Worked out the Kobo Event.Checksum (and how to restore reading stats)

[koboj]

This is about reading and editing the local analytics database (KoboReader.sqlite) on a Kobo you own, to back up and restore your own reading stats.

I sideload books, and a book's reading stats can get orphaned when its file path (its ContentID) changes, when it's deleted and re-added, or when you move to a new device. Wanting to back them up and put them back led me to the Checksum column in KoboReader.sqlite — the thing that's blocked editing these tables for years — and I worked out how it's computed. Posting it as I believe it's useful.

The short version

The Checksum on the Event table (and on AbTest, Rules, and Achievement) is:

Checksum = md5( <every column except Checksum, in alphabetical order by column name,
                 values concatenated as raw bytes, NULL -> nothing>  +  "kobo.rules" )

It's plain MD5 with one fixed salt string, kobo.rules. Not HMAC, and there's no per-device key. Once you can compute a valid checksum the device will accept rows you've edited, which is what makes restoring stats possible.
The salt is a literal string: the 10 ASCII characters kobo.rules. Where I put it in quotes below, the quotes are just delimiters, not part of the data. Those 10 bytes get appended to the row's bytes and the whole lot is MD5'd, with no key or length prefix or separator in between.

The exact recipe

1. Take every column except Checksum, sorted by column name (plain alphabetical).
2. Concatenate the values as bytes, with no separators:
   • numbers -> their decimal text (14 -> "14")
   • text -> its bytes
   • blob -> its raw bytes
   • NULL -> contributes nothing
3. Append the literal 10 bytes kobo.rules (dot included).
4. MD5 the result; the lowercase hex is the checksum.

For the Event table the columns in order are ContentID, EventCount, EventType, ExtraData, FirstOccurrence, LastOccurrence, so:

Event.Checksum = md5( ContentID + str(EventCount) + str(EventType) + ExtraData_bytes
                      + FirstOccurrence + LastOccurrence + "kobo.rules" )

Here it is in Python:

import hashlib, sqlite3
def event_checksum(ContentID, EventCount, EventType, ExtraData,
                   FirstOccurrence, LastOccurrence):
    def b(x):
        if x is None:            return b""
        if isinstance(x, bytes): return x
        return str(x).encode()
    msg = (b(ContentID) + b(EventCount) + b(EventType) + b(ExtraData)
           + b(FirstOccurrence) + b(LastOccurrence) + b"kobo.rules")
    return hashlib.md5(msg).hexdigest()

Verify it yourself

Copy your own KoboReader.sqlite off the device (it's at .kobo/KoboReader.sqlite) and run this against it. If it prints N/N, the algorithm holds on your firmware too:

con = sqlite3.connect("KoboReader.sqlite")
ok = tot = 0
for et, fo, lo, ec, cid, ed, ck in con.execute(
        "SELECT EventType, FirstOccurrence, LastOccurrence, EventCount, ContentID, "
        "CAST(ExtraData AS BLOB), Checksum FROM Event WHERE Checksum IS NOT NULL"):
    tot += 1
    ok  += event_checksum(cid, ec, et, ed, fo, lo) == ck
print(f"{ok}/{tot} match")          # should print N/N - every row matches (tested on fw 4.45.x)

The CAST(ExtraData AS BLOB) matters: ExtraData is stored as TEXT with embedded NUL bytes, so a plain read truncates it and the hash won't line up.

What it unblocks: the per-book stats page

The per-book stats screen doesn't read content.TimeSpentReading. It reads fields out of the Event ExtraData blob. All three numbers (device-confirmed):

hours of reading        =  ExtraDataReadingSeconds / 3600
avg minutes per session =  ExtraDataReadingSeconds / 60 / ExtraDataReadingSessions
avg pages per minute    =  EventType-46.EventCount  / (ExtraDataReadingSeconds / 60)

Those two ExtraData... values are int entries inside the ExtraData blob, which is a serialized key->value map that also holds things like wordsRead and eventTimestamps. They show up in both the EventType=46 (page-turns) row and the EventType=3 (session) row, and the type-46 one is authoritative: the device reads the running count from it, bumps it at the end of each session, and the type-3 copy is what the screen displays. The screen recomputes when you open it, so it isn't a frozen cache you can't get at.

To restore a book's stats, overwrite those two ints in both the type-46 and type-3 ExtraData blobs (they're fixed-size, so it's a 4-byte in-place poke), set the type-46 EventCount for the pages/min number, and recompute each row's Event.Checksum. I tested the whole thing on-device: the patched numbers appear on reboot, and reading the book afterward adds to the restored values instead of resetting them.

That also makes it automatable. Kobo Utilities already backs up KoboReader.sqlite and restores content.TimeSpentReading, but it doesn't write the checksum-guarded Event rows the stats page reads, so the time it restores never appears on that screen. With a working Event.Checksum, storing and restoring the Event stat fields is well within what the plugin already does.
Reading position and content.TimeSpentReading were already writable; the Event stat fields were the missing piece. With those, a book's whole reading history — time spent, sessions, pages per minute, and where you left off — can be backed up and restored as a unit, whether you're re-sending the book or moving to a new device, instead of getting it back in pieces.

Notes

• Checked against real rows on two device models — a Libra Colour and a Clara Colour — with every Event row matching on both (a few hundred rows on one, over a thousand on the other, across two backups), so the salt isn't per-model or per-device. Both backups are on current firmware (4.45.x); I also checked a 2018 firmware build and it uses the same salt the same way, so it's been stable for about 8 years.
• The alphabetical-column ordering should hold for every table, but double-check it for AbTest, Rules, and Achievement.
• The snippets here only read. Restoring stats means writing Event rows back, which changes your device database — so back up KoboReader.sqlite (and any -wal/-shm) first and run PRAGMA integrity_check after. I'd keep Wi-Fi off until you've checked the result too: in theory a sync could overwrite or clear the rows you edited, though I always tested offline and never hit that.

[DNSB]

Thanks for that information!