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.
| Question | Example | Store that answers it well |
|---|---|---|
| Find rows by exact values and join them | Every checkpoint of run-7f3a, newest first | Relational table |
| Keep a flexible blob next to fixed columns | The run state at state_version 2 | JSON column in a relational table |
| Fetch one self-contained record by id | The whole submission SUB-1004 with its 63 fields | Document store, or a JSON column |
| Keep large files cheaply | The provider's roof report as a PDF; a backup | Object 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 meaning | Memories 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.
- Execution state
- runs, attempts: One row per run and per attempt, with the ids of the run side by side.
- checkpoints, events: Appended checkpoints with the state as JSON; an append-only event log.
- Facts and effects
- evidence, recommendations: Evidence stored once; recommendations with a unique idempotency key.
- outbox, inbox, decisions: Messages owed and accepted; decisions written only by a person.
- Memory
- memories: One row per memory, with the columns the filter and the policy depend on.
- 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#
| Table | One row is | Key columns | Why this shape |
|---|---|---|---|
runs | One run | run_id, submission_id, thread_id, correlation_id, status | The ids of Run identity side by side, so a run can be found from any of them |
attempts | One attempt of a run | (run_id, attempt_number), trace_id unique | Attempts belong to runs; one trace per attempt is enforced by the unique constraint |
checkpoints | One saved state | (run_id, state_version) unique, state_json | Appended, never overwritten; the state is JSON because its fields change by step |
events | One event in a run | seq, run_id, type, payload_json | Append-only; replay folds them in seq order |
evidence | One stored piece of evidence | evidence_id | Stored once, pointed to by reasons and memories |
recommendations | One stored recommendation | idempotency_key unique | The unique key is what makes the write idempotent |
outbox | One message owed | message_id, sent_at | Written in the same transaction as the change it announces |
inbox | One message accepted by the cockpit | message_id primary key | The consumer's deduplication |
decisions | One person's decision | run_id, decided_by, override, reason | Written only by a person, through the cockpit |
memories | One memory | memory_id, kind, scope, status, expires_at | Scope and status are columns so the filter can run in the query |
shared_records | One versioned shared record | record_id, version | The 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#
| Need | Store |
|---|---|
| The newest checkpoint of one run | Relational table, with (run_id, state_version) |
| The run state, whose fields change by step | JSON column |
| The provider's PDF report and the nightly backup | Object 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 question | Vector 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#
- List the questions your agent asks of its storage. Which store answers each?
- Which records must agree with each other after a crash? Are they in one database?
- Is every column a rule depends on (scope, status, key, version, expiry) a real column, or a field inside JSON?
- Is the vector index a derived copy that can be rebuilt from tables, or the only copy?
- 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.