Claude & OpenAI: Deconstructing Agent Memory

A coding agent needs memory it can retrieve.

Concept. An LLM model only sees the context supplied for its current call. Agent memory persists decisions outside the model, then retrieves relevant rows for later context. One SQLite file can support local memory. A replicated database can support memory across a global product.

Intuition. Storage preserves information across calls. Retrieval decides what the model sees again. That split between working context and persistent memory are key ideas for this course.

Case Study Reading Time: 6 mins

Agent memory at two scales. A model call can miss an earlier decision when the current context does not contain it. A local agent can retrieve saved project evidence from SQLite. A global product can serve user memory from a replicated Postgres deployment supporting 800 million users.

Figure 1. Context (or memory) lives outside the model. The current context is short-term working memory, limited to what a single call includes. A local agent persists saved evidence in an embedded database (SQLite on one machine) that survives every session. OpenAI reports a read-heavy Postgres deployment serving 800 million users, with one primary and nearly 50 read replicas.

A new session can miss prior decisions

On Monday you and Claude Code build a custom auth module. You choose several function names, fix an edge case, and ship it. On Tuesday you open a new session and ask Claude to extend the module. If the new request neither includes Monday's decisions nor retrieves the relevant code, the agent may call a plausible function such as verifyJWTWithScope(), even though that function does not exist.

That is a hallucination: a confident output unsupported by the available evidence. A second failure mode is rediscovery, where the agent repeats work because it cannot retrieve an earlier decision.

Tuesday's request does not include Monday's auth decision. Without retrieved evidence, the agent proposes verifyJWTWithScope even though the codebase contains no such function.

Figure 2. Monday produced a working module. Tuesday's request does not contain the earlier decision. The agent generates verifyJWTWithScope() from patterns rather than retrieved evidence. Persistent memory gives the next session something to query.

The context window is the agent's working-memory limit

Remember the RAM limit from the previous case study: a fast working memory that cannot hold the whole problem. An agent hits the same limit, with a different name.

Modern agents work inside a context window, the text the model receives for one request. Current Claude models support roughly 200,000 to 1,000,000 tokens, depending on the model and deployment. A chat application may save an earlier transcript, but the model sees it again only when the application includes or retrieves it for the next request.

Each model call receives a limited working context. Persistent memory stores records outside that context, and retrieval can return selected records to a later call.

Figure 3. The context window stays bounded at roughly 200,000 to 1,000,000 tokens. Persistent memory can retain records outside that window across sessions. The application retrieves selected records when a later request needs them.

Local memory: a database on your machine

Local agent memory. Lifecycle hooks store sessions, observations, prompts, and summaries in SQLite. FTS5 supports keyword retrieval; Chroma or sqlite-vec can support vector retrieval. Selected records return to a later model context.

Figure 4. Local memory captures records in SQLite and retrieves selected evidence into later context. The diagram shows FTS5 keyword search and Chroma or sqlite-vec for vector search. claude-mem documents an optional Chroma index; QMD uses sqlite-vec.

QMD, created by Tobi Lütke, is a local search engine for documents, notes, and code (github.com/tobi/qmd). It stores indexed content in SQLite, uses FTS5 for keyword search and sqlite-vec for vector search, and exposes retrieval through its CLI and MCP server. An agent can retrieve a saved function name instead of guessing it.

The tools solve related problems with different designs. claude-mem records sessions, observations, prompts, and summaries in SQLite. Aider builds a compact repository map and uses graph ranking to choose which code definitions fit in the active context.

SQLite fits local memory for four practical reasons:

  • No separate database server. The local tool opens SQLite directly.

  • Embedded. SQLite runs inside the local process or service without a network hop.

  • Transactional. A write mid-task must not corrupt the database if the laptop sleeps or the process dies.

  • One file. You can copy it, back it up, or inspect it with a SQL client.

Global memory: the same need, at scale

Global product memory. OpenAI reports one Azure PostgreSQL primary and nearly fifty read replicas supporting a read-heavy workload for 800 million users and millions of queries per second.

Figure 5. OpenAI reports one Azure PostgreSQL primary and nearly 50 read replicas supporting a read-heavy workload for 800 million users. The primary accepts writes and replicates changes across geographic regions; the read replicas serve queries and return results to users.

Local agent memory and global product memory store different records, but they use the same systems principle: memory lives outside the model. A local tool can use one SQLite file. OpenAI's read-heavy product workload uses PostgreSQL replication to distribute queries across many machines.

The next case study applies the same local-to-global progression to UberEats, where diners, drivers, and restaurants must agree on every order.

Takeaway: Agent memory stores records outside the model and retrieves relevant evidence into a later context. SQLite supports the local case, while replicated databases support larger products.

Going deeper

How the database and the retrieval work, in detail.

The observations table

An illustrative agent-memory schema can fit in one table:

CREATE TABLE observations (
    id           INTEGER PRIMARY KEY,  -- unique row id; no two observations share one
    session_id   TEXT,                 -- which working session produced this
    project      TEXT,                 -- which codebase/project it belongs to
    created_at   TIMESTAMP,            -- when it was written; used to sort by recency
    kind         TEXT,                 -- 'decision' | 'pattern' | 'bug-fix' | 'todo'
    content      TEXT,                 -- the actual remembered text the agent searches
    embedding    BLOB                  -- optional vector; some systems store it separately
);

-- Indexes make the common lookups fast (more on indexes in Module 3).
CREATE INDEX idx_session ON observations(session_id);  -- "everything from this session"
CREATE INDEX idx_project ON observations(project);     -- "everything in this project"

QMD's index lives at ~/.cache/qmd/index.sqlite with the sqlite-vec extension and SQLite's built-in FTS5 module. claude-mem stores sessions, observations, prompts, and summaries in SQLite and can add an optional Chroma vector index. Aider's repository map is a separate code-context mechanism.

What compaction costs

Long histories may be summarized before they return to the context. Summaries use fewer tokens but omit detail. A system can retain the original records for later search while injecting only the shorter summary into the active context.

Three ranking signals

Memory retrieval systems rank different kinds of evidence. QMD combines keyword and vector search with reranking. Aider ranks definitions in a code-dependency graph. These signals can complement one another, but they do not have to live in one table or execute as one SQL expression.

Three vertically stacked retrieval signals feed a fourth box: combined fusion ranking merges ranked candidates, removes duplicates, and selects evidence for the next context.

Figure 6. BM25 ranks stored text by term matches. Vector similarity ranks stored text by embedding distance, so "login" can match "auth." Graph ranking orders code definitions by their relationships to other definitions. The fourth box illustrates combined fusion ranking: merge ranked candidates, remove duplicates, and select evidence for the next context. This is a possible combined design; QMD and Aider do not necessarily use all three signals.

1. Keyword search with BM25. Classical full-text search weighted by term frequency and inverse document frequency, built into SQLite via FTS5. Best for exact terms: "find where we mentioned verifyJWTWithScope."

-- "What did we decide about auth in this project?"
-- observations_fts is an FTS5 virtual table over observations.content.
SELECT o.content FROM observations o
JOIN observations_fts f ON f.rowid = o.id
WHERE observations_fts MATCH 'auth'
  AND o.project = 'cs145'
  AND o.kind = 'decision'
ORDER BY bm25(observations_fts)
LIMIT 10;

2. Vector similarity. Embedding-based semantic search. An embedding model converts the question to a vector. The agent retrieves observations with nearby vectors. Best for paraphrase: "what are we doing about token refresh?" surfaces any observation about session lifetime even if "refresh" never appears.

-- :query_embedding is computed beforehand by an external embedding model.
SELECT content FROM observations
WHERE project = 'cs145'
ORDER BY vec_distance_cosine(embedding, :query_embedding) ASC
LIMIT 10;

3. Graph ranking. Aider's RepoMap uses tree-sitter to extract definitions and references, builds a dependency graph, and ranks the portions that fit the available token budget. This operates on code structure rather than text similarity.

The three signals answer different questions. BM25 catches the words you used. Vectors catch related meaning. Graph ranking finds structurally important code. A retrieval pipeline can filter by project or recency, combine scores, and place the highest-ranked evidence into the next context.