SQLite + aiosqlite — The Right Database for One Person

Why SQLite for cwkPippa

One user. One Mac. Tens of thousands of messages, growing slowly. Postgres would be overkill. The whole DB is a single file under /, backed up by Time Machine like any other file. Zero admin.

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 in a function called init_db that's idempotent — adding a column? Use PRAGMA table_info to check first, only add if missing.

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

async def init_db(db_path: str):
    async with aiosqlite.connect(db_path) as db:
        await db.execute("""
            CREATE TABLE IF NOT EXISTS conversations (
                id TEXT PRIMARY KEY,
                title TEXT,
                created_at TEXT NOT NULL,
                claude_session_id TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS messages (
                id TEXT PRIMARY KEY,
                conversation_id TEXT NOT NULL,
                role TEXT NOT NULL,
                content TEXT,
                parent_id TEXT,
                brain TEXT DEFAULT 'claude',
                reasoning_level TEXT,
                thinking TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (conversation_id) REFERENCES conversations(id)
            )
        """)
        # Idempotent column add — safe to run repeatedly
        cols = {r[1] for r in await (await db.execute('PRAGMA table_info(messages)')).fetchall()}
        if 'thinking' not in cols:
            await db.execute('ALTER TABLE messages ADD COLUMN thinking TEXT')
        await db.commit()

Why SQLite for cwkPippa

Schema is plain SQL, no ORM

The mirror principle

Code

Progress