A Memory That Survives

# labs/long_term_memory.py
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE IF NOT EXISTS facts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        key TEXT NOT NULL,
        value TEXT NOT NULL,
        created_at TEXT NOT NULL
    )
""")
tables = conn.execute(
    "SELECT name FROM sqlite_master WHERE type='table'"
).fetchall()
print("Tables:", tables)
conn.close()

$ uv run python labs/long_term_memory.py
Tables: [('facts',), ('sqlite_sequence',)]

":memory:" spins up a throwaway database, so you can experiment without touching disk; the real file arrives in stage 4. CREATE TABLE IF NOT EXISTS is chapter 1's idempotence habit wearing SQL clothes: safe to run at every startup, a no-op when the table is already there. And you asked for one table and got two: sqlite_sequence is SQLite's own bookkeeping for AUTOINCREMENT columns. You never write to it, but knowing it belongs there saves you a confused ten minutes someday.

from datetime import datetime, timezone

def store_fact(conn, key: str, value: str) -> None:
    conn.execute(
        "INSERT INTO facts (key, value, created_at) VALUES (?, ?, ?)",
        (key, value, datetime.now(timezone.utc).isoformat()),
    )
    conn.commit()

def recall_facts(conn) -> dict[str, str]:
    rows = conn.execute(
        "SELECT key, value FROM facts ORDER BY created_at DESC"
    ).fetchall()
    return {row[0]: row[1] for row in rows}

store_fact(conn, "user_name", "Kryo")
store_fact(conn, "preferred_language", "Python")
print("Remembered facts:", recall_facts(conn))

$ uv run python labs/long_term_memory.py
Remembered facts: {'preferred_language': 'Python', 'user_name': 'Kryo'}

Two details deserve your suspicion. The ? placeholders send the SQL template and your values to SQLite as two separate things, so the values are never parsed as SQL; that is the only real defense against a user named O'Brien, and the failure box makes the case the hard way. And the dict comes back "reversed" because ORDER BY created_at DESC puts the newest fact first, and the comprehension preserves that order. conn.commit() after every write is mandatory; SQLite buffers until you commit, and uncommitted rows die with the process, recreating exactly the amnesia you came here to cure.

def store_summary(conn, summary: str) -> None:
    conn.execute(
        "INSERT INTO conversations (summary, created_at) VALUES (?, ?)",
        (summary, datetime.now(timezone.utc).isoformat()),
    )
    conn.commit()

def recall_summaries(conn, limit: int = 5) -> list[str]:
    rows = conn.execute(
        "SELECT summary FROM conversations ORDER BY created_at DESC LIMIT ?",
        (limit,),
    ).fetchall()
    return [row[0] for row in rows]

$ uv run python labs/long_term_memory.py
Recent summaries: ["User switched the wake word to 'Hey GLaDOS'.", 'User asked about TTS and confirmed Piper works.']

Facts are for things that stay true; summaries are for things that happened. The LIMIT ? makes "the five most recent" one fast query instead of a load and sort, and yes, even the integer goes through a placeholder. Never format a value into SQL, no matter how harmless it looks; the habit only protects you if it has no exceptions. Notice too that the apostrophes in 'Hey GLaDOS' round-tripped without any escaping from you. That is parameterization quietly earning its keep.

# labs/long_term_memory.py — final form
import sqlite3
from pathlib import Path
from datetime import datetime, timezone

DB_PATH = Path("glados/data/memory.db")

def init_db(db_path: Path) -> sqlite3.Connection:
    db_path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(db_path)
    conn.execute("""CREATE TABLE IF NOT EXISTS facts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        key TEXT NOT NULL, value TEXT NOT NULL, created_at TEXT NOT NULL)""")
    conn.execute("""CREATE TABLE IF NOT EXISTS conversations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        summary TEXT NOT NULL, created_at TEXT NOT NULL)""")
    conn.commit()
    return conn

if __name__ == "__main__":
    conn = init_db(DB_PATH)
    store_fact(conn, "user_name", "Kryo")
    store_summary(conn, "First persistent session. She will remember this.")
    print("Facts:", recall_facts(conn))
    print("Summaries:", recall_summaries(conn, limit=3))
    conn.close()

$ uv run python labs/long_term_memory.py   # second run, after a first run and a restart
Facts: {'user_name': 'Kryo'}
Summaries: ['First persistent session. She will remember this.', 'First persistent session. She will remember this.']

Run it, kill the process, run it again: the facts are still there, which is the entire point of the chapter, witnessed. The double summary in the second run is not a bug in SQLite; it is the demo inserting the same line on every launch, and it points at a real design question — facts are keyed and could be de-duplicated, while summaries are a log and duplicates are honest history. Chapter 14 makes that distinction do real work when memory starts flowing into her prompts.