JSONL Is Truth; SQLite Is a Mirror

"Never patch derived state. If it drifts, purge the affected span and replay from JSONL."

The Ground Truth Is an Append-Only Log

Under all those projections sits a storage spine Vesta adopts wholesale from cwkPippa. Ground truth is an append-only JSONL log — one log per journal. Every crumb event — add, edit, tombstone, move-out, move-in — is a single appended line. Live state isn't stored as such; it's folded from the events on read. Want the current state of a crumb? Replay its events in order and apply them. The source log is never rewritten, never edited in place — it only grows. That's the same append-only discipline you saw for re-filing and journal moves, now as the foundation of the whole store: history is the truth, and current state is just history folded forward.

SQLite Is Derived, and You Never Patch It

Folding events on every read would be slow for queries like 'how many crumbs each day' or 'everything tagged garden', so Vesta keeps a SQLite mirror that owns every query surface: calendar counts, tag filters, the multi-year month-day index, future full-text search. But the mirror is strictly derived. It holds no truth of its own; it's a fast index built from the JSONL. And that leads to the single most important operational rule, inherited verbatim from cwkPippa: if the mirror ever drifts from the log, you purge the affected span and replay from JSONL — you never patch the derived state.

Recover derived state by rebuilding, never by patching. When a derived mirror disagrees with its source, the temptation is to write a little reconciliation that nudges the mirror back into line. Resist it. Patch-and-reconcile logic is where bugs breed — it's code that runs rarely, is tested least, and quietly makes the two stores diverge in new ways. Purge the bad span and replay from the source of truth. The rebuild path is simple, total, and always correct; the patch path is subtle, partial, and eventually wrong.

Fernet at Rest, Plaintext Where It Must Be

Because a journal is deeply private, the JSONL ground truth is Fernet-encrypted at rest, line by line, keyed by a Vesta-specific passphrase held in the operating system's Keychain. Backups and any peer mirrors carry only opaque encrypted blobs — a copy of the log on another machine reveals nothing without the key. But encryption is applied with judgment, not everywhere. Two working surfaces stay deliberately plaintext: the derived SQLite mirror (it's local, rebuildable, and holds no truth the encrypted log doesn't) and the draft .md files (they're filesystem-canonical so Rekindle can open them in place). Encrypting those would break the Rekindle round trip for no security gain, since the real archive — the append-only log — is already ciphertext. Protect the ground truth; don't cargo-cult encryption onto the working surfaces that need to stay open.

Code

Fold events to state; rebuild by purge-and-replay·python

def fold(events):
    """Live state is history folded forward — never stored directly."""
    crumbs = {}
    for e in events:                 # events are append-only, in order
        if e.type == "add":      crumbs[e.id] = e.payload
        elif e.type == "revise": crumbs[e.id].update(e.changes)
        elif e.type == "tombstone": crumbs.pop(e.id, None)
        # move_out / move_in handled per-journal-log
    return crumbs

def rebuild_mirror(journal):
    # the ONLY repair for a drifted SQLite mirror:
    sqlite.purge(journal)                       # drop derived rows
    sqlite.load(fold(read_jsonl(journal)))      # replay from ground truth
    # never: sqlite.patch(...) to 'reconcile' — that's where bugs live

Exercise

Take a system with a cache or a denormalized read table derived from a source of truth. When they disagree, what does the code do — patch the derived side, or rebuild it from source? Find (or imagine) the reconciliation logic and argue whether a purge-and-replay would be simpler and more correct. Then decide what actually needs encryption at rest versus what's a rebuildable working surface that can stay plaintext.

Hint

Reconciliation code is a bug farm: rarely run, hard to test, and prone to inventing new divergences. Purge-and-replay is boring and total, which is exactly why it's safer. On encryption: protect the source of truth; a derived, rebuildable mirror leaks nothing the source doesn't, so encrypting it is usually cargo-cult that breaks something else.

JSONL Is Truth; SQLite Is a Mirror

The Ground Truth Is an Append-Only Log

SQLite Is Derived, and You Never Patch It

Fernet at Rest, Plaintext Where It Must Be

Code

External links

Exercise

Progress

Comments 0