SQLite, Journaling, and Schema Migrations

software-engineering
full-stack
databases
sqlite
migrations
reliability
Design the database behind browser sessions, recover interrupted writes, and evolve persisted state without breaking refresh or resume.

The browser application becomes trustworthy when a refresh or process restart reconstructs every acknowledged event. This chapter builds the local data layer behind the REST and WebSocket surfaces: normalized SQLite tables make sessions queryable, an append-only journal defines the acknowledgement boundary, and versioned migrations make schema change an explicit product operation.

Model query needs separately from durable facts

The sessions table stores one row per session for fast sidebar queries. The events table stores ordered payloads and enforces globally unique event and idempotency identifiers. An index on (session_id, cursor) supports detail loading and reconnect suffixes. JSON payloads keep the event vocabulary extensible without putting every tool field into a nullable column.

SQLite is the projection used for reads. The JSON Lines journal is the first record of an acknowledged event. SessionRepository is the only access layer permitted to coordinate the two, which prevents an HTTP handler or job from updating one and forgetting the other.

import sqlite3
from tempfile import TemporaryDirectory

from autocode.store.repository import SessionRepository

with TemporaryDirectory() as directory:
    database = f"{directory}/sessions.db"
    repository = SessionRepository(database)
    session = repository.create("database lesson")
    first = repository.append(
        session.session_id,
        "user_message",
        {"content": "persist me"},
        idempotency_key="browser:1",
    )
    duplicate = repository.append(
        session.session_id,
        "user_message",
        {"content": "persist me"},
        idempotency_key="browser:1",
    )
    with sqlite3.connect(database) as connection:
        tables = {
            row[0]
            for row in connection.execute(
                "SELECT name FROM sqlite_master WHERE type = 'table'"
            )
        }
        event_rows = connection.execute("SELECT COUNT(*) FROM events").fetchone()[0]

assert tables >= {"sessions", "events"}
assert first.event_id == duplicate.event_id
assert event_rows == 1
print("tables:", sorted(tables), "event rows:", event_rows)
tables: ['events', 'sessions'] event rows: 1

The idempotency key turns a retried browser or CLI command into the original durable result instead of a second row. A database uniqueness constraint provides the final guard, while the repository checks first so it also avoids appending a duplicate journal record. The application can safely retry after losing a response.

Recovery makes acknowledgement precise

The critical interruption window lies after the journal flush and before the SQLite transaction commits. The user may have received an acknowledgement from an outer layer, so deleting the journal entry would lose accepted work. Recovery scans journal records in order, projects only missing event ids, and advances the parent session version. Running recovery twice must make no further change.

from tempfile import TemporaryDirectory

from autocode.store.repository import SessionRepository

with TemporaryDirectory() as directory:
    repository = SessionRepository(f"{directory}/sessions.db")
    session = repository.create("recovery drill")
    event = repository.append(session.session_id, "user_message", {"content": "survive"})
    with repository._connect() as connection:
        connection.execute("DELETE FROM events WHERE event_id = ?", (event.event_id,))
        connection.execute(
            "UPDATE sessions SET version = 0 WHERE session_id = ?",
            (session.session_id,),
        )

    first_pass = repository.recover()
    second_pass = repository.recover()
    restored = repository.get(session.session_id)

assert first_pass == 1
assert second_pass == 0
assert restored is not None and restored.events[0].event_id == event.event_id
print("recovery passes:", first_pass, second_pass)
recovery passes: 1 0

The first pass repairs the query model; the second proves idempotency. A process-level kill drill should stop after fsync but before projection and then run this same recovery API on restart. Deleting a row manually only controls the boundary deterministically for the notebook.

Reads, cursors, and migrations serve product behavior

The sidebar needs recent session summaries; the timeline needs one ordered session; reconnect needs events after a cursor. These are distinct queries with distinct limits. Returning every event from the list route is easy at first and expensive later, so the repository exposes list, get, and events_after separately.

Schema evolution is similarly product-facing. A migration that adds a summary column changes what new code may read and what old code can reopen. The upgrade and downgrade paths belong in tests against copied data. SQLite can require table reconstruction for some downgrades, which makes rehearsal more important rather than less.

from tempfile import TemporaryDirectory

from autocode.store.repository import SessionRepository

with TemporaryDirectory() as directory:
    repository = SessionRepository(f"{directory}/sessions.db")
    session = repository.create("cursor reads")
    for content in ["one", "two", "three"]:
        repository.append(session.session_id, "text_delta", {"content": content})

    summaries = repository.list(limit=10)
    suffix = repository.events_after(session.session_id, cursor=1)

assert summaries[0].session_id == session.session_id
assert [event.cursor for event in suffix] == [2, 3]
print("reconnect suffix:", [(event.cursor, event.payload["content"]) for event in suffix])
reconnect suffix: [(2, 'two'), (3, 'three')]

These query boundaries directly support the Chapter 02 REST routes and Chapter 03 WebSocket reconnect. Database design is not isolated backend work: indexes, payload shapes, and transaction ordering determine whether the frontend can load quickly, avoid duplicates, and explain an interrupted run.

Exercises

Implement or describe one schema migration and its rollback against copied session data. Include the journal relationship, a pre-migration backup, queries exercised before and after, and the browser behavior if migration fails during startup.

[P04.1] Recover and migrate a session projection

Describe an idempotent recovery pass for a journal event absent from SQLite, then add a nullable or defaulted session summary column without making old sessions unreadable. State the transactional boundary and browser-visible failure mode.

Back to top