Skip to content
C.W.K.
Lesson 03 of 06 · published

A Mirror You Can Throw Away

~15 min · sqlite, derived-state, replay, schema

Level 0First Word
0 XP0/47 lessons0/13 achievements
0/100 XP to next level100 XP to go0% complete

A view that knows it is a view

Nobody wants to read a moment by scanning month files. Pages need indexes: newest capture first, everything in one trip, every result for one phrase. So the engine keeps a SQLite file beside the log, mirror.sqlite3, and every read goes through it. Its docstring states its standing in the first paragraph: the log is ground truth; the mirror applies each op as it is appended and is rebuilt whole by replaying the log, never patched by hand. The family calls this the purge-and-replay rule, and Spark keeps it exactly.

That one sentence changes how you treat the file. You can delete it. You can rebuild it on a whim. A bug in a fold is fixed by fixing the code and replaying, never by editing rows.

The shape of a table

Every record table keeps the whole folded record as JSON in a record column, beside the few columns that reads filter and sort on. The moments table, for example, has id, kind, captured_at, local_date, trip_id, deleted and seq as columns, and everything else lives in the JSON. A new field in an op therefore reaches every read without touching the schema. Only a field you want to filter on needs a column.

seq is the op's position in the log. The archive lists moments by captured_at descending and breaks ties by seq, so two moments captured in the same second still come back in a stable order across pages; a test pins that pagination.

There are eleven derived tables: moments, media, results, turns, marks, links, trips, purposes, threads, requests and search, plus meta, which holds the schema version and the log fingerprint. Nine of the eleven are record tables. The other two keep no record column: requests maps a client request id to the op and the id it produced, and search holds text with pointers back to its moment. search is an FTS5 table with the trigram tokenizer, so it matches inside words in any script; a later track in this quest uses it.

Folding

Each op has a small handler that folds it into the tables. Creates use INSERT OR IGNORE, so a create whose id already exists changes nothing. Edits read the current record, patch only the editable fields and write it back. turn.finish merges onto the record turn.start made. A mark or link that is added again flips its active column back to 1. Put together, the mirror is a fold over the log: apply the ops in log order and you get the state.

Is this mirror current?

The second code block answers that with two facts from meta: the schema version must equal the code's SCHEMA_VERSION (4 today), and the stored log fingerprint must equal the one computed from the files right now. Corpus.open, the third block, asks that question at every boot and rebuilds when either fact fails. An explicit rebuild is one route away too: POST /api/admin/rebuild, whose docstring reads purge and replay the mirror from the log, derived state only.

The rebuild itself runs inside one transaction. It deletes every derived row and the meta, resets seq, applies every op the log yields, writes the schema version and commits. Any exception rolls the whole thing back, so a rebuild that fails halfway leaves the previous mirror exactly as it was, and the next attempt starts clean.

A schema change is a replay

The first code block is where most apps keep a migration chain. Spark keeps none. When the mirror opens and finds a stored schema version that differs from the code's, it drops every derived table, clears meta and creates the current shape. open then sees the version mismatch and replays the log into the new tables. In the module's own words, a mirror file never needs a migration of its own.

QuestionMigration chainDerived mirror
Where is the history?In the tables, reshaped by every migrationIn the log, never reshaped
What does a schema change cost?A script per version, kept foreverOne version bump and a full replay
What if a fold had a bug?A data-fix migrationFix the code, replay
What grows over time?The migration listReplay time, with the log

The price is in the last row. Replay time grows with the log, and every schema bump pays it once. For a corpus one person writes, by hand and by camera, that is the trade Spark chose.

Connection details that matter

  • WAL journal mode. Readers do not block the writer and the writer does not block readers.
  • isolation_level=None. Python's sqlite3 module opens no implicit transactions, so every BEGIN and COMMIT in the mirror is written out where you can see it.
  • check_same_thread=False, one connection. Writes arrive on worker threads through asyncio.to_thread, so the connection must cross threads. What makes that safe is the corpus lock from the last lesson; the class docstring says it plainly: one connection, serialized by the corpus lock.

If any of these are new, SQLite Quest covers them properly: its track on writes, transactions and WAL, and its FTS5 lesson for the search table.

The test that earns the word "throwaway"

One test makes the claim falsifiable. It opens a corpus, creates a trip, creates a moment in that trip, edits the moment's place, and reads the moment back. Then it closes the corpus, deletes mirror.sqlite3, reopens, and asserts three things: the rebuilt moment equals the one read before, the trip is there, and filtering moments by that trip still finds it. Incremental folding and a full replay must agree, record for record.

Principle: A cache you are afraid to delete is a second source of truth. Make the derived store cheap to destroy, and prove it with a test that destroys it.

Code

cwkSpark, backend/mirror.py: an older schema drops its derived tables instead of migrating (verbatim, lines 157-173)·python
class Mirror:
    """One connection, serialized by the corpus lock (a single-user app)."""

    def __init__(self, path: Path) -> None:
        path.parent.mkdir(parents=True, exist_ok=True)
        self.path = path
        self.db = sqlite3.connect(path, check_same_thread=False, isolation_level=None)
        self.db.execute("PRAGMA journal_mode=WAL")
        self.db.execute("CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT)")
        if self.meta("schema_version") not in (None, str(SCHEMA_VERSION)):
            # Derived tables from an older shape: drop them; open() sees the
            # version mismatch and replays the log into the current shape.
            for table in _TABLES:
                self.db.execute(f"DROP TABLE IF EXISTS {table}")
            self.db.execute("DELETE FROM meta")
        self.db.executescript(_SCHEMA)
        self._seq = self._max_seq()
cwkSpark, backend/mirror.py: two facts decide currency; a rebuild is one transaction (verbatim, lines 194-217)·python
    def is_current(self, fingerprint: str) -> bool:
        return (
            self.meta("schema_version") == str(SCHEMA_VERSION)
            and self.meta("log_fingerprint") == fingerprint
        )

    def rebuild(self, ops: Iterable[dict[str, Any]]) -> int:
        """Drop every derived row and apply the whole log. Returns the op count."""
        self.db.execute("BEGIN")
        try:
            for table in _TABLES:
                self.db.execute(f"DELETE FROM {table}")
            self.db.execute("DELETE FROM meta")
            self._seq = 0
            count = 0
            for op in ops:
                self._apply(op)
                count += 1
            self.set_meta("schema_version", str(SCHEMA_VERSION))
            self.db.execute("COMMIT")
        except BaseException:
            self.db.execute("ROLLBACK")
            raise
        return count
cwkSpark, backend/corpus.py: every boot asks whether the mirror is current (verbatim, lines 178-195)·python
    def open(self) -> None:
        self.config.ensure_runtime_dirs()
        with self._lock:
            self.mirror = Mirror(self.config.mirror_path)
            fingerprint = self.log.fingerprint()
            if not self.mirror.is_current(fingerprint):
                self._rebuild_locked(fingerprint)

    def close(self) -> None:
        with self._lock:
            if self.mirror is not None:
                self.mirror.close()
                self.mirror = None

    def rebuild(self) -> dict[str, Any]:
        with self._lock:
            self._rebuild_locked(self.log.fingerprint())
            return self.health()

External links

Exercise

Give the log from the previous exercises a SQLite view of its own. Store a schema version and the log fingerprint in a meta table, rebuild inside a single transaction, and make opening the view rebuild whenever either fact is stale. Then write two tests: one that builds incrementally, deletes the database file, rebuilds and compares every record; and one that bumps the schema version, adds a filter column, and shows the view comes back in the new shape with no migration script.
Hint
Keep the full folded record as JSON next to a handful of filter columns, and keep a sequence number that is the op's position in the log. If a rebuild raises halfway, roll back so the old view survives untouched.

Progress

Progress is local-only — sign in to sync across devices.
← PreviousOne Door: Corpus.recordNext →Replay Never Duplicates
Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)
Sign in — Please sign in to comment.

No comments yet — be the first.