All chapters
  1. Start hereMemory is what you keep. A checkpoint is where you stopped
  2. Memory · 1The stateless request: why anything has to be stored at all
  3. Memory · 2Five kinds of memory: working, episodic, semantic, procedural and preference
  4. Memory · 3Conversation history, summaries, retrieval and durable facts
  5. Memory · 4Files as memory in coding agents: instructions, journals, artifacts and checkpoints
  6. Memory · 5Database stores: tables, JSON, documents, objects and vectors
  7. State · 6Run identity: run_id, thread_id, trace_id, correlation_id and task_id
  8. State · 7State machines, checkpoints, event sourcing and where replay must stop
  9. State · 8Failure and resume: idempotency keys, retry policy and the exactly-once illusion
  10. State · 9The transactional outbox: side effects, compensation and deduplication
  11. Policy · 10Memory write policy: extraction, confidence, provenance, expiry and deletion
  12. Policy · 11Retrieval and ranking: relevance, recency, salience and scope
  13. Policy · 12Shared memory between agents: consistency, isolation, ownership and races
  14. Policy · 13Security: tenant separation, personal data, secrets, consent and retention
  15. Practice · 14Evaluation: memory correctness, stale facts, contamination and recovery tests
  16. Practice · 15Frameworks and standards: what each one stores for you
  17. Practice · 16The insurance lab: one run that fails, resumes and remembers, end to end
  18. Practice · 17Advanced: migration, conflict resolution, forgetting, summary drift and disaster recovery
  19. ReferenceGlossary
  20. AboutAbout this site and its sources
Memory · 5

Database stores: tables, JSON, documents, objects and vectors

Where durable information lives: relational tables, JSON columns, document stores, object storage and vector indexes, and which question each one answers well.

What you will understand
Six kinds of store
Tables, JSON columns, documents, objects, key-value and vectors: which question each answers well.
One transaction
Why the records that must agree belong in one store that can commit them together.
The example's tables
Eleven tables, three groups, and why each has its shape.

The coding agent of the previous page kept everything in plain files, and that worked because one person and one agent touched them, one session at a time. The insurance agent is different. Hundreds of runs write at once. The underwriter cockpit asks "show me every run waiting for a decision". A resumed attempt must find the newest checkpoint of exactly one run. The write policy must find "the active memory for broker BRK-017 about roof age source" in one step. And when step 7 stores a recommendation and step 8 records the message owed to the cockpit, either both must be saved or neither.

Files answer none of those questions well. A database does, but "a database" is several different tools that answer different questions. A team that puts run state in a vector index, or memories in a JSON blob nobody can query, discovers the mismatch at the worst moment: during a resume, or during a deletion request. This page compares the kinds of store, shows the one property that decides most of the design, and walks through the tables of the example and why each has its shape.

The agent here is a scripted stand-in, so the results on this page show how the mechanism works, not how good any model is.

The questions decide the store#

Begin with the questions the insurance agent asks of its storage, because each kind of store is good at some of them.

QuestionExampleStore that answers it well
Find rows by exact values and join themEvery checkpoint of run-7f3a, newest firstRelational table
Keep a flexible blob next to fixed columnsThe run state at state_version 2JSON column in a relational table
Fetch one self-contained record by idThe whole submission SUB-1004 with its 63 fieldsDocument store, or a JSON column
Keep large files cheaplyThe provider's roof report as a PDF; a backupObject storage
Get one value by one key, fast"Is idempotency key run-7f3a:record_recommendation taken?"Key-value store, or a unique index
Find text with a similar meaningMemories similar to "roof age source for warehouses"Vector index

Each of these is a different tool. Here is each one in a sentence and an example.

A relational table stores rows with fixed, typed columns, and finds and joins them by values. For example, the checkpoints table with one row per checkpoint and a column for run_id.

A JSON column is a column in a relational table that holds a structured value of any shape. For example, state_json in checkpoints: the run state, whose fields differ from step to step. SQLite's documentation, read for this page, says "SQLite stores JSON as ordinary text"; the example therefore keeps JSON as TEXT. PostgreSQL has a jsonb type, which its documentation describes as stored "in a decomposed binary format" that is "significantly faster to process", and which supports indexes.

A document store keeps whole self-contained records, usually JSON, and fetches them by id or by fields inside them. For example, the submission of SUB-1004 as one document with all its fields. Many teams get the same benefit from a JSON column in a relational table.

Object storage keeps large files, each under a name, cheaply and durably. For example, the provider's roof report as a PDF under evidence/EV-ROOF-0412.pdf, and the nightly backup of the database. It is not built for queries; a table holds the metadata and the object's name.

A key-value store maps one key to one value and does nothing else, very fast. For example, a lookup of an idempotency key. A unique index on a column in a relational table gives the same answer inside the same transaction, which is how the example does it.

A vector store keeps an embedding per record and finds the records whose embeddings are nearest to a query's. An embedding is a list of numbers produced by a model so that texts with similar meaning get nearby lists. For example, the memory "this broker gives the roof age source as an inspection report" and the question "where does the roof age come from?" share few words but get nearby embeddings. The example uses word overlap instead, so that it needs no model; Retrieval explains the ranking, and Frameworks describes pgvector, an extension that adds vector search to PostgreSQL, from its own documentation.

What this is not. A vector index is not a database of record. It answers "what is similar?", not "what is true?" or "what is this run's newest checkpoint?". Run state, decisions and the source of truth for every field live in tables; a vector index is a derived copy for one kind of search.

The property that decides most of the design: one transaction#

Now return to the requirement from the opening: step 7 stores a recommendation and step 8 records the message owed to the cockpit, and a crash must never leave one without the other.

A transaction is a group of writes that a database applies all together or not at all. For example: insert the recommendation row, insert the outbox row, commit. A crash before the commit leaves neither; a crash after leaves both. This is what makes the outbox of Outbox work, and what makes the checkpointer save a checkpoint and update the run's state name together.

A transaction covers one database. That single fact decides most of the storage design of an agent. The records that must agree with each other, run state, checkpoints, effects and their keys, the outbox, belong in one store that can commit them together. Spreading them across a relational database, a key-value cache and a vector service means every write that touches two of them is a dual write, with the failure modes Outbox described.

The example does exactly that: one SQLite database holds all eleven tables, and the writes that must agree (a checkpoint and the run's state name; a recommendation and its event; the recommendation and its outbox row; a memory and its audit event) go through one helper. This is transaction in store.py; what it demonstrates is that those writes commit or roll back as a unit in SQLite, nothing about any other database:

@contextmanager
def transaction(conn: sqlite3.Connection) -> Iterator[sqlite3.Connection]:
    """All writes inside commit together, or none of them do."""
    conn.execute("BEGIN")
    try:
        yield conn
        conn.execute("COMMIT")
    except BaseException:
        conn.execute("ROLLBACK")
        raise

The diagram groups the eleven tables by what they hold.

Diagram: The eleven tables of the example · three groups, one database, one transaction

Read left to right. Execution state answers 'where were we?'. Facts and effects record what happened outside the run, each with an id that makes a repeat recognizable. Memory answers 'what do we know?'. All three groups live in one SQLite database, so a step can write to two groups in one transaction.

  1. Execution state
    1. runs, attempts: One row per run and per attempt, with the ids of the run side by side.
    2. checkpoints, events: Appended checkpoints with the state as JSON; an append-only event log.
  2. Facts and effects
    1. evidence, recommendations: Evidence stored once; recommendations with a unique idempotency key.
    2. outbox, inbox, decisions: Messages owed and accepted; decisions written only by a person.
  3. Memory
    1. memories: One row per memory, with the columns the filter and the policy depend on.
    2. shared_records: Records several agents update, with a version for the version check.

Read it left to right. Execution state answers "where were we?". Facts and effects record what happened outside the run, each with an id that makes a repeat recognizable. Memory answers "what do we know?". All three groups are in one database, so a step can write to two groups in one transaction.

The tables of the example, and why each has its shape#

TableOne row isKey columnsWhy this shape
runsOne runrun_id, submission_id, thread_id, correlation_id, statusThe ids of Run identity side by side, so a run can be found from any of them
attemptsOne attempt of a run(run_id, attempt_number), trace_id uniqueAttempts belong to runs; one trace per attempt is enforced by the unique constraint
checkpointsOne saved state(run_id, state_version) unique, state_jsonAppended, never overwritten; the state is JSON because its fields change by step
eventsOne event in a runseq, run_id, type, payload_jsonAppend-only; replay folds them in seq order
evidenceOne stored piece of evidenceevidence_idStored once, pointed to by reasons and memories
recommendationsOne stored recommendationidempotency_key uniqueThe unique key is what makes the write idempotent
outboxOne message owedmessage_id, sent_atWritten in the same transaction as the change it announces
inboxOne message accepted by the cockpitmessage_id primary keyThe consumer's deduplication
decisionsOne person's decisionrun_id, decided_by, override, reasonWritten only by a person, through the cockpit
memoriesOne memorymemory_id, kind, scope, status, expires_atScope and status are columns so the filter can run in the query
shared_recordsOne versioned shared recordrecord_id, versionThe version column makes the version check of Shared memory possible

Read the last column for the pattern. Every column that a rule depends on is a real column, not a field inside JSON: scope and status for the filter that must run before ranking, idempotency_key for the unique check, version for optimistic locking, expires_at for expiry. JSON holds what varies and is read whole: the run state, the event payload, the recommendation body. That split, typed columns for what the code filters on and JSON for what it carries, is the most useful single rule of this page.

Here is the memories table, from schema.sql. Notice that text may be NULL: that is a tombstone after a deletion.

CREATE TABLE IF NOT EXISTS memories (
  memory_id        TEXT PRIMARY KEY,                -- MEM-0031
  kind             TEXT NOT NULL CHECK (kind IN ('episodic', 'semantic', 'procedural', 'preference')),
  scope            TEXT NOT NULL,                   -- org, broker:BRK-017, submission:SUB-1004, user:underwriter
  subject          TEXT NOT NULL,
  text             TEXT,                            -- NULL on a tombstone
  source_type      TEXT NOT NULL,                   -- underwriter_decision, tool_result, broker_statement, agent_inference, derived_summary
  evidence_ids     TEXT NOT NULL,                   -- JSON list
  source_run_id    TEXT,
  confidence       REAL NOT NULL,                   -- assigned by the write policy from the source type
  salience         REAL NOT NULL,
  policy_version   TEXT NOT NULL,
  created_at       TEXT NOT NULL,                   -- a date, YYYY-MM-DD
  expires_at       TEXT,
  version          INTEGER NOT NULL DEFAULT 1,
  status           TEXT NOT NULL CHECK (status IN ('active', 'expired', 'superseded', 'deleted')),
  written_by       TEXT NOT NULL,
  idempotency_key  TEXT UNIQUE,
  personal_data    INTEGER NOT NULL DEFAULT 0,
  superseded_by    TEXT,
  deleted_at       TEXT,
  deleted_reason   TEXT
);

The runner prints the row counts of all eleven tables after every case. After UW-M01 alone, they read: runs 1, attempts 2, checkpoints 9, events 25, evidence 1, recommendations 1, outbox 1, inbox 1, decisions 0, memories 10, shared_records 0. Two attempts, one recommendation, one message: the shape of a resumed run, visible in a single line.

For a backend engineer. This is the schema you would design for any workflow service, with one addition. Runs, steps, an audit log, an outbox and an inbox are standard; a memory table with scope, provenance, expiry and status is the agent-specific part. Teams that already run PostgreSQL usually keep all of it there, use jsonb for the flexible parts, and add vector search as an extension or a derived index rather than a second system of record.

Check yourself#

NeedStore
The newest checkpoint of one runRelational table, with (run_id, state_version)
The run state, whose fields change by stepJSON column
The provider's PDF report and the nightly backupObject storage, with metadata in a table
"Has this idempotency key been used?"Unique index (or a key-value store), inside the write's transaction
Memories similar in meaning to a questionVector index, after the scope filter

The failure, shown#

A team stores memories only in a vector service, because memory search is "semantic". Three things go wrong. The scope filter is an option of the similarity search, and one code path forgets it: contamination. A deletion request removes the vector, but the text was also in a summary held elsewhere, and nobody can query "every memory of this person" because the vector service has no such index. And the memory write and the run's checkpoint are in two systems, so a crash between them leaves a memory from a run that, on resume, writes it again. Each problem disappears when the memory row lives in the same relational database as the run, with typed columns for scope and status, and the vector index is a derived copy.

Framework implementation#

Agent frameworks ship storage adapters: a checkpointer backed by a database, a store for long-term memory, sometimes both on the same database. Which named framework stores what, and on which databases, is read from its own documentation on Frameworks.

A checklist for Monday#

  1. List the questions your agent asks of its storage. Which store answers each?
  2. Which records must agree with each other after a crash? Are they in one database?
  3. Is every column a rule depends on (scope, status, key, version, expiry) a real column, or a field inside JSON?
  4. Is the vector index a derived copy that can be rebuilt from tables, or the only copy?
  5. Where are large files kept, and does a table hold their metadata?
Advanced: when one database is not enough

One transactional database for run state, effects, outbox and memory is the right default and carries most agent systems a long way. It stops being enough in three situations. Write volume on events can outgrow a single primary; partitioning the events table by run, or moving old events to cheaper storage after a run closes, usually fixes that before a new system is needed. Vector search over many memories can outgrow what an extension in the main database handles comfortably; a dedicated index then becomes a derived copy fed from the outbox, with the version of the source row in each entry so readers can see lag. And very large artifacts belong in object storage from day one.

The principle survives each change: one system of record for each fact, transactions where facts must agree, and everything else a derived copy that can be rebuilt and is allowed to lag. Shared memory discusses consistency between such copies.

In one breath#

Each store answers different questions: tables find and join by values, JSON columns carry flexible state, documents hold self-contained records, object storage holds large files, key-value stores answer one lookup, and vector indexes find similar meaning. A transaction covers one database, so the records that must agree after a crash belong in one store. Put every value a rule depends on in a real column, and keep the vector index as a derived copy.

The same idea for a coding agent: the repository is the system of record and the commit is its transaction; a search index over the code is a derived copy that can always be rebuilt.

The tables now exist. Rows in them carry many ids, and several of them look alike. The next page names each one. Run identity.