SQLite + aiosqlite — The Right Database for One Person · Pippa Quest

Why SQLite for cwkPippa

One household, not a public service. One Mac. About 16,000 message rows on 2026-10-12, with one conversation, the Master Chief, that never ends (truth track, lesson 8). Postgres would be overkill. The conversation store is a single file under <app-data>/. It still has to be looked after, and the looking after is scripted: a WAL database copied file by file can tear when a checkpoint lands between the reads, so scripts/pippa-db-snapshot.py first takes a verified copy through SQLite's online backup API, and the daily mirror to the fleet carries that copy in place of the live files. On the NAS, where Carbon Copy Cloner copies the tree, the snapshot directory is the recovery location.

aiosqlite wraps SQLite in an async API. Same SQL, async/await ergonomics. Fits right in with FastAPI.

Schema is plain SQL, no ORM

cwkPippa doesn't use SQLAlchemy. The schema is a few CREATE TABLE statements in backend/store/conversations.py. Migrations are ALTER TABLE statements in a function called init_db that runs on every boot and is idempotent: each column addition runs inside a try whose except swallows the error, which is how the failure raised for a column that already exists disappears. Run it once or a hundred times and the schema converges to the same shape. The live except is broader than that job needs: it swallows every error, so a real failure (a locked database, a full disk) disappears the same way and boot goes on without the column. The stricter shape re-raises anything that is not SQLite's duplicate column name error, so a migration that fails says which step it stopped at.

The mirror principle

SQLite is the convenience mirror for the JSONL ground truth. If it ever drifts (corruption, schema change gone wrong, partial writes), the recovery is purge the rows for that conversation and replay the JSONL. Never patch SQLite directly. You'll learn this the hard way in the Truth track.

Parameterized queries always: Even though it's just me on this machine, every query goes through ? placeholders. SQL injection isn't the only reason — it's also how you get the right SQLite type coercion. Strings and bytes don't auto-convert in SQLite the way you'd expect.

Code

Schema init — idempotent migrations without an ORM·python

import aiosqlite

# Sketch of init_db in backend/store/conversations.py, run on every boot.
async def init_db(db_path: str) -> None:
    async with aiosqlite.connect(db_path, timeout=5.0) as db:
        await db.execute("PRAGMA journal_mode = WAL")
        await db.execute("""
            CREATE TABLE IF NOT EXISTS conversations (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL DEFAULT 'New Chat',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS messages (
                id TEXT PRIMARY KEY,
                conversation_id TEXT NOT NULL,
                role TEXT NOT NULL,
                content TEXT NOT NULL,
                created_at TEXT NOT NULL,
                parent_id TEXT DEFAULT NULL,
                FOREIGN KEY (conversation_id) REFERENCES conversations(id)
            )
        """)
        # Idempotent column adds: each ALTER runs in a try. The live code
        # swallows EVERY error here, not only 'duplicate column name'.
        for col in ("thinking", "brain", "reasoning_level", "model", "emotion"):
            try:
                await db.execute(f"ALTER TABLE messages ADD COLUMN {col} TEXT DEFAULT NULL")
            except Exception:
                pass
        await db.commit()

SQLite + aiosqlite — The Right Database for One Person

Why SQLite for cwkPippa

Schema is plain SQL, no ORM

The mirror principle

Code

Progress

Comments 0