How to Build a Memory-Enabled AI Agent with SQLite and Vector Search

Current agent systems forget everything after a session, limiting their ability to reason across interactions. Adding memory, reflection, and goal‑tracking transforms an LLM from a stateless Q&A tool into a collaborative partner.

Why Memory Matters

Memory enables cross‑session continuity, allowing agents to anticipate recurring data‑quality issues, self‑evaluate task outcomes, and track long‑term objectives such as multi‑step research assistance.

Architecture Overview

The design consists of two memory layers: situational memory stored in SQLite and semantic memory stored as embeddings in a vector database (e.g., Pinecone, FAISS, Chroma). The LLM layer generates summaries and reflections.

Database Schema

Two tables are created in SQLite:

CREATE TABLE IF NOT EXISTS memory_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_name TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    input TEXT,
    output TEXT,
    summary TEXT,
    embedding BLOB
);

CREATE TABLE IF NOT EXISTS goals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_name TEXT,
    goal TEXT,
    status TEXT DEFAULT 'in_progress',
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

Recording Situational Memory

After each task the agent logs key information:

def log_memory_event(agent_name, input_text, output_text, summary, embedding):
    conn = sqlite3.connect("memory.db")
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO memory_events (agent_name, input, output, summary, embedding)
        VALUES (?, ?, ?, ?, ?)
    """, (agent_name, input_text, output_text, summary, embedding))
    conn.commit()
    conn.close()

The summary is produced with a prompt such as:

summary_prompt = f"Summarize this agent interaction:

Input: {input_text}
Output: {output_text}
"
summary = llm.complete(summary_prompt)

Embedding the summary enables semantic lookup instead of keyword matching.

Semantic Retrieval

When a new query arrives, the system retrieves the most relevant past summaries:

def recall_related_memories(query, top_k=3):
    query_embedding = embedding_model.embed(query)
    results = pinecone_index.query(vector=query_embedding, top_k=top_k)
    return [r['metadata']['summary'] for r in results]

The retrieved summaries are injected into the LLM prompt, e.g., “Below are related past experiences that may help with the current problem…”. This mimics human recall of similar cases.

Reflection Loop

Every few interactions the agent reflects on its recent behavior:

reflection_prompt = f"""
You are reviewing your recent actions. Based on the following summaries, what patterns,
mistakes, or improvements do you notice?

{recent_summaries}

Provide 3 takeaways and 1 improvement plan for your future tasks.
"""
reflection = llm.complete(reflection_prompt)

The reflection can be stored as meta‑memory or re‑embedded for future retrieval, providing a lightweight reinforcement‑learning style update without gradient steps.

Goal Management

Goals are tracked in the goals table and updated with:

def update_goal(agent_name, goal, status):
    conn = sqlite3.connect("memory.db")
    cur = conn.cursor()
    cur.execute("""
        UPDATE goals SET status = ?, last_updated = CURRENT_TIMESTAMP
        WHERE agent_name = ? AND goal = ?
    """, (status, agent_name, goal))
    conn.commit()
    conn.close()

A dedicated goal‑tracking agent can periodically query unfinished goals and issue reminders, enabling long‑term objective pursuit.

End‑to‑End Workflow

User request arrives; the agent executes the task and logs the interaction to SQLite and the vector store.

Before handling a new request, the system performs semantic retrieval of relevant memories and adds them to the prompt.

After N interactions, the agent generates a reflection note and stores it.

Goals persist across days or weeks, with status updates and progress checks.

The combined system behaves like a learning, memory‑rich partner rather than a one‑shot tool.

Practical Tips

Store concise summaries instead of full dialogues to save space and improve retrieval speed.

Regularly clean or merge similar memories to keep the database manageable.

Compress multiple related events into a single “meta‑memory” to reduce redundancy.

Design prompts that give the LLM a clear role (e.g., “reflection analyst”) to improve self‑assessment quality.

Optional: visualise memory clusters, goal progress, and reflections with a Streamlit or React UI for debugging.

Conclusion

By coupling SQLite‑based situational storage with vector‑based semantic memory, and by adding LLM‑driven reflection and goal‑tracking, an AI agent evolves from a stateless executor into a learning companion. The implementation requires only a few lines of code and standard components, yet it lays the groundwork for future memory‑centric AI systems.