Building an Edge-to-Core Data Pipeline: Re-engineering My Kobo Architecture

Part 1 of this series built a pipeline that extracted highlights off my bare-metal Kobo and synced them to a public website. Part 2 started as a simple feature request to myself: “Can I auto-scrobble my reading progress to StoryGraph?”

What it turned into was an exercise in pipeline observability and data integrity. I discovered my v1 pipeline had been silently dead for five months, that my initial extraction method was dropping hours of data, and that building reliable edge-to-core pipelines requires treating the edge device as inherently hostile.

The resulting v2 architecture is a full ELT (Extract, Load, Transform) data pipeline that ships the entire database on every network join, implements strict idempotency, and actually monitors its own health.

The Anatomy of a Silent Outage: Client Reliability

The original sin of v1 was its ingestion trigger: a manual NickelMenu tap on the device. Manual triggers in data pipelines mean data rarely moves, and when I finally checked the device logs, I found that the last three runs had all ended mid-send with no success line and no error.

Running lsof on the Kobo revealed the culprit: a 16-day-old TCP connection to my server, still holding the SQLite database and its Write-Ahead Log (WAL) open.

The root cause was a classic edge-case failure. On the server side, two function nodes in my Node-RED flow had mysteriously zeroed out, halting the flow and never returning an HTTP 200 response. On the client side, Zig’s std.http.Client has no built-in timeout. A silent server met an infinite block, creating a zombie process that held the database hostage for over two weeks.

Atomicity and The SQLite WAL Trap

StoryGraph doesn't have a developer API, and the official Kobo integration explicitly excludes sideloaded books by design, meaning I needed to extract progress updates directly from the local device.

Nickel keeps KoboReader.sqlite in WAL mode (write-ahead logging). My initial test to see if I needed to extract the -wal file produced identical results to the bare .sqlite file, leading me to incorrectly conclude I didn't need it. I had run SQLite PRAGMA queries on the database before copying it, which silently checkpointed the WAL into the main file.

A clean test revealed a yet another hurdle for data integrity: the bare main file was missing 18 hours of reading progress. Highlights are written rarely and get checkpointed automatically, but progress updates happen constantly and live entirely in the WAL.

Copying the main database, the WAL, and the SHM file over the network is inherently non-atomic; a checkpoint landing between file reads yields corrupted data. The fix was to leverage the fact that my custom Zig binary vendors the full SQLite amalgamation. By executing VACUUM INTO '/tmp/kobo_snapshot.sqlite' inside a read transaction, the client safely emits a single compacted, atomic snapshot of the database.

Reverse Engineering the Edge Trigger

I needed this extraction to trigger automatically whenever the Kobo connected to WiFi. If you look at the device filesystem, /etc/udhcpc.d/ looks exactly like a hook directory. It's a decoy.

Thankfully, my time spent reverse-engineering the firmware on my RVLink roof units (which you can read about in my RVLink Deep Dive: Firmware Secrets and API Mapping) meant I was already deeply familiar with traversing busybox environments and knew exactly where to begin looking when the obvious DHCP hook failed.

Some device archaeology revealed that Nickel actually uses dhcpcd 6.6.6, which has a real, supported hook chain. I wrote a script at /libexec/dhcpcd-hooks/99-kobo-sync that detaches into a subshell, checks if the WAL's modified time is newer than a marker file, and fires the Zig binary to upload the atomic snapshot.

Observability: The Routers as Witnesses

There is one major flaw with edge logic: Kobo firmware updates wipe the rootfs, meaning my custom dhcpcd hook will get deleted during over-the-air updates. Unmonitored automation doesn't fail; it evaporates.

To prevent another silent pipeline death, I instrumented the network layer. Both my home MikroTik hEX router and the Chateau router in the camper now run custom lease-scripts. Whenever the Kobo joins the network, the router fires a webhook to my server to record a device_joins event. My infrastructure monitor, Gatus, watches this state: if the network sees the Kobo join, but my server doesn't ingest a fresh DB snapshot within a few hours, the pipeline is marked unhealthy and it fires a Discord alert.

:if ($leaseBound = "1" && $leaseActMAC = "A4:3C:D7:5F:F6:83") 
  do={
    :do {
      /tool fetch output=none url=("http://192.168.88.69:8077/kobo-joined?site=home&ip=" . $leaseActIP . "&mac=" . $leaseActMAC . "&host=" . $"lease-hostname")
    }
  on-error={} }

ELT and Dimensional Modeling in PostgreSQL

For v2, I abandoned the old MariaDB architecture and fully embraced an ELT pattern. The edge device is dumb: it just ships the raw SQLite snapshot to a Python Flask container. All transformation and parsing logic happens server-side, consolidating the data into a purpose-built database in my PostgresSQL 18 server.

The raw data is parsed into a strictly modeled dimensional schema:

• Idempotency & Deduplication: The snapshots table records one row per upload. VACUUM INTO is byte-deterministic, meaning identical snapshots generate identical SHA256 hashes, allowing the pipeline to instantly reject and deduplicate unchanged re-sends.
• The Fact Table: progress operates as an append-only change log of reading progress, deliberately shaped to mirror how I track timeseries telemetry.
• The Dimensions: The books and highlights tables act as dimensional lookups, while a currently_reading view surfaces the latest state per started book.

By shifting to this architecture, I built a pipeline that is entirely decoupled from the fragile edge device. Every raw snapshot is archived on my storage pool forever. If I decide I want to track a new metric or reverse-engineer more Kobo analytics next year, I don't have to touch the e-reader—I can just rebuild the schema and re-parse the history.

Beyond just ensuring data integrity, having the entire Kobo schema flowing automatically into this dimensional model unlocks completely new analytical capabilities. Because the pipeline ships the full database—including all of Kobo's internal event trackers and analytics counters—I'm no longer limited to whatever specific metrics I thought to extract at the time. This robust foundation enables me to build my own custom BI visualizations, and once I collect a sufficient volume of historical data, I plan to explore deeper data science and statistical angles. I'll be able to analyze my own reading velocity, session patterns, and drop-off metrics, all back-derived from history without ever needing to touch the edge device again.

erDiagram
    snapshots {
        bigserial id PK
        text device
        text sha256 UK
        bigint byte_size
        text archive_path
        timestamptz received_at
        timestamptz parsed_at
        text parse_error
    }
    
    books {
        text content_id PK "File path or ID"
        text title
        text attribution "Author"
        text isbn
        text publisher
        text series
        text mime_type
        boolean is_sideload
        integer num_pages
        bigint file_size
        timestamptz date_created
        timestamptz first_seen
        timestamptz last_seen
    }

    progress {
        bigserial id PK
        bigint snapshot_id FK
        text content_id FK
        integer percent_read
        smallint read_status
        timestamptz date_last_read
        integer num_pages
        integer time_spent_reading
        timestamptz recorded_at
    }

    highlights {
        text text_hash PK
        text book_hash
        text content_id FK
        text book
        text text
        text annotation
        timestamptz date_created
        timestamptz first_seen
        timestamptz last_seen
    }

    events {
        integer event_type PK
        text content_id PK
        timestamptz first_occurrence
        timestamptz last_occurrence
        integer event_count
        text checksum
        timestamptz last_seen
    }

    analytics_events {
        text id PK
        text type
        timestamptz timestamp
        text attributes
        text metrics
        timestamptz last_seen
    }

    device_joins {
        bigserial id PK
        text site
        text ip
        text mac
        text hostname
        timestamptz joined_at
        bigint snapshot_id FK "Matches to snapshot if valid"
    }

    storygraph_links {
        text content_id PK
        text sg_book_id
        text matched_by
        text confidence
        timestamptz linked_at
        timestamptz last_pushed_at
        integer last_pushed_percent
        text last_pushed_status
    }

    storygraph_queue {
        text content_id PK
        text title
        text author
        text isbn
        jsonb candidates
        text reason
        boolean resolved
        timestamptz created_at
    }

    %% Relationships
    snapshots ||--o{ progress : "captures"
    snapshots ||--o| device_joins : "validates"
    books ||--o{ progress : "has"
    books ||--o{ highlights : "has"
    books ||--o| storygraph_links : "linked_to"
    books ||--o| storygraph_queue : "queued_for"
    books ||--o{ events : "generates"

Did We Actually Complete the Original Goal?

So, after this massive data engineering detour, did the pipeline actually achieve the original goal of auto-scrobbling progress to StoryGraph?

The short answer is yes. By reverse-engineering the web app's protocol—navigating undocumented session cookies and CSRF tokens—the pipeline is successfully taking this clean, dimensional data and syncing it.

However, dealing with the realities of mapping sideloaded epubs to a closed ecosystem, deciding between automated ISBN matching versus a human-in-the-loop queue, and managing state across StoryGraph's frontend is an entirely different architectural challenge. There is more than enough there for its own dedicated write-up, so that will have to be a post for another day.