Your AI’s Memory Breaks on Real Work. The Fix Is Fifty Years Old.

Your AI’s Memory Breaks on Real Work. The Fix Is Fifty Years Old.

I had been building for weeks with an AI coding agent at my elbow — real work, a real application, the kind that accretes a hundred small hard-won decisions. One morning, it forgot one. Not a big one. But I had asked the same thing days earlier, watched it solve it, heard it say, “I’ve got that in memory.” This time, it reached for the memory and came back holding half of it. The tool said so in plain text: it had loaded only part of the file. The shelf had overflowed and quietly dropped the rest on the floor.

Anyone who builds with these agents past a weekend meets that wall. It never announces itself. Your assistant just turns a little more forgetful — re-solves a thing it already solved, contradicts a decision from Tuesday. You blame the model. The model didn’t fail you. The filing system did.

How the memory actually works

Under the hood, the agent’s long-term memory amounts to a stack of text files it loads into its head at the start of every session. That works beautifully at ten files. At a hundred and forty, it stops working. Everything you have ever told it to remember competes for one small space — the context window — and that window has a hard edge. Push past it, and whatever falls over the line simply doesn’t come along. No error crosses your screen. You just get a quieter, dumber assistant.

Name that plainly, because the fix hangs on it: the default memory keeps everything in working memory and reads all of it, every time.

I had seen this before

The moment I caught the shape of it, thirty years fell away. This problem wore new clothes, but I knew its face. It ranks among the oldest problems in data.

I started solving it in the early 1990s. Back then, the naive way to use information meant loading the whole file and scanning it top to bottom — fine until the file grew, then ruinous. The entire discipline of relational databases grew up to kill that habit. Edgar Codd wrote the argument down in 1970: stop holding everything in memory and reading it end to end. Store it structured, index it, and ask for exactly the row you need. A database never reads the whole library to answer one question. It walks to the shelf, pulls a single book, and shuts the door.

The AI memory wall repeats that flat-file problem in a new coat. And the answer has sat on the shelf for fifty years.

The two-tier fix

So I split the memory in two. This runs as a build log, so here’s the whole thing — schema, queries, and all. Skim the code if you don’t write it; the shape carries the argument.

Tier two: a database you question, not a file you scan

Every hard problem becomes a row. The structure stays boring on purpose:

CREATE TABLE solutions (
  id         INTEGER PRIMARY KEY,
  created    TEXT NOT NULL,   -- when it got solved
  area       TEXT,            -- db | api | infra | ...
  title      TEXT NOT NULL,
  problem    TEXT,            -- the concrete problem
  root_cause TEXT,            -- why it happened
  solution   TEXT,            -- what actually worked
  gotcha     TEXT,            -- the trap that bit us
  artifacts  TEXT,            -- files / commits it produced
  tags       TEXT
);

That’s the memory — not prose the agent re-reads, but fields it can question.

Retrieval: one book off the shelf

The whole point of a database: you never read all of it. You ask for the row you need. So the searchable columns get a full-text index — SQLite’s built-in FTS5, kept in lockstep with the table by triggers:

CREATE VIRTUAL TABLE solutions_fts USING fts5(
  title, problem, root_cause, solution, gotcha, tags,
  content='solutions', content_rowid='id'
);

And the retrieval — the entire reason the memory stops overflowing — comes down to one query:

SELECT s.id, s.title,
       snippet(solutions_fts, -1, '[', ']', ' … ', 12) AS snip
FROM   solutions_fts f JOIN solutions s ON s.id = f.rowid
WHERE  solutions_fts MATCH ?      -- the words the agent is chewing on
ORDER  BY rank                    -- best match first (bm25)
LIMIT  8;

The agent hands it a phrase and gets back the handful of rows that match, ranked, not a hundred and forty files fighting for the window. In practice:

$ python3 memory.py query "attach view disappears"
#1  [db]  SQLite can't persist a view over an ATTACH-ed database
     SQLite can't persist a [view] over an [ATTACH]-ed database

One shelf, one book, door closed. That query costs the same whether the table holds ten rows or ten thousand.

Tier one: an index that can’t drift

The always-loaded tier holds nothing but a projection of the database — one line per row, regenerated on demand:

SELECT id, title, tags FROM solutions ORDER BY id;

written out as a flat list, the agent carries into every session:

- #1  SQLite can't persist a view over an ATTACH-ed database   (sqlite, attach, view)
- #2  eBay Sell offer publishes but the listing is wrong        (ebay, validation)

It tells the agent what it already knows and where to look — and because it comes from the database, it never lies about what the database holds.

Stop loading the library. Keep an index you can hold, and a database you can question.

The part that isn’t the database

Let me stay honest about what the database does not fix, because the tool alone sets a trap.

An agent’s reflex runs toward solving, not looking up. A perfect knowledge base nobody consults becomes dead weight — worse, because it lulls you. So the database earns its keep only with a hard habit riding on top: query before you rebuild. We had to write that down as a standing rule, because left alone, the machine will cheerfully re-derive on Thursday what it filed on Monday.

The rows have to stay honest, too — written the day you solve the thing, deleted the day they turn out wrong. A stale row misleads worse than an empty table.

Retrieval carries a ceiling as well. That MATCH finds the row whose words you match; it misses the row you meant. The next rung teaches the table to answer intent instead of keywords — semantic search, embeddings. But that bolts onto the relational base; it does not replace it. Start with the boring, exact thing. Add the clever thing when the boring thing runs dry.

The newest field, relearning the oldest lesson

Here sits the part I keep chewing on. The industry’s answer to AI memory carries a name — RAG, vector databases, embeddings — and a great deal of it amounts to the newest, best-funded corner of computing rediscovering the database from scratch, in unfamiliar clothes. Store what you can’t hold. Fetch what you need. Don’t read the whole library. We have known that since Nixon’s first term.

The insight didn’t come from chasing the frontier. It came from a small, memory-starved graphics card — an 8-gigabyte GPU I was trying to fit a retrieval system onto. Scarcity asks the only question that counts: what do I truly need in front of me, and what can I go get? Answer it honestly, and you rebuild the relational model by hand, whether or not you’ve heard its name. The card that “wasn’t big enough” handed me the pattern.

And the lesson under the lesson: sometimes the newest problem is the oldest one, and whoever lived the old one sees it first. Stay in this trade long enough, and your experience stops trailing the curve. Now and then, it runs a lap ahead.

---

Tom Adelstein builds in the open. This pattern came out of a working session with Claude Code, Anthropic’s coding agent: I pitched the relational approach; it built, tested, and hardened the implementation, and caught the failure modes I named above. The collaboration is the point — and, fittingly, the subject. The working code — schema, queries, and the query-first habit — lives on GitHub: https://github.com/tadelstein9/two-tier-memory