reflection_memory.py

import sqlite3
from datetime import datetime
from typing import List, Dict

DB_PATH = "reflection_memory.sqlite3"

def init_db():
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS reflections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                created_at TEXT NOT NULL,
                theme TEXT NOT NULL,
                user_question TEXT NOT NULL,
                assistant_summary TEXT NOT NULL
            )
        """)

def save_reflection(theme: str, user_question: str, assistant_summary: str):
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute(
            """
            INSERT INTO reflections (created_at, theme, user_question, assistant_summary)
            VALUES (?, ?, ?, ?)
            """,
            (
                datetime.utcnow().isoformat(timespec="seconds") + "Z",
                theme,
                user_question,
                assistant_summary,
            ),
        )

def fetch_recent(limit: int = 5) -> List[Dict]:
    with sqlite3.connect(DB_PATH) as conn:
        rows = conn.execute(
            """
            SELECT theme, assistant_summary
            FROM reflections
            ORDER BY id DESC
            LIMIT ?
            """,
            (limit,),
        ).fetchall()

    return [{"theme": r[0], "summary": r[1]} for r in rows]