Building an agent memory layer on RedDB

Every agent project I’ve watched ship spends two weeks deciding what “memory” means before writing a line of retrieval code. This post tries to skip that fortnight. It is a concrete schema — three tables, four indices, and the queries that wire them together — that you can copy into your own RedDB-backed project and start tuning instead of designing.

The schema is opinionated. It distinguishes episodic memory (what happened, in order), semantic memory (facts the agent extracted from those events), and procedural memory (cached outcomes of expensive tool calls). The boundaries between them matter: they have different retention rules, different retrieval paths, and different invalidation triggers. Collapsing them into one big memories table is the most common mistake — and the one that turns a six-week agent project into a six-month one.

This post is the practical follow-up to RAG without a second database. The same single-database principle applies: the vector lives on the row it describes, the same transaction writes both, and retrieval is one query instead of a fan-out.

The three shapes of memory

Before the SQL, the distinctions.

Episodic memory is the raw stream of agent events: user turns, tool calls, tool results, model outputs, errors. It is append-only, ordered, and (mostly) immutable. You query it by conversation, by time range, and occasionally by similarity to find “have I seen this before.” Retention is bounded — most episodic rows are useful for hours to days, not months.

Semantic memory is what the agent has extracted from episodic events: “the user’s deploy target is us-east-1,” “the user prefers concise responses,” “the customer’s plan is enterprise.” These are durable facts. They are not append-only — facts get updated, contradicted, and superseded. Retrieval is by similarity (vector) and by tagged subject (the user, the project, the customer). Retention is long.

Procedural memory is cached outcomes of expensive or non-deterministic tool calls: the result of a web fetch, a search query, an LLM-generated summary of a long document. Procedural rows have a clear cache-key (the tool name plus its arguments) and an explicit expiry. Retrieval is by exact key — vectors are not the access path here, even though the content might be embedded for separate semantic retrieval.

Three shapes, three access patterns, three retention rules. One database.

The schema

-- Episodic: append-only stream of agent events.
CREATE TABLE episodic_events (
  id            BIGSERIAL PRIMARY KEY,
  session_id    UUID NOT NULL,
  ts            TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor         TEXT NOT NULL,  -- 'user' | 'agent' | 'tool:<name>' | 'system'
  kind          TEXT NOT NULL,  -- 'message' | 'tool_call' | 'tool_result' | 'error'
  payload       JSONB NOT NULL,
  tokens_in     INTEGER,
  tokens_out    INTEGER,
  embedding     VECTOR(1536),   -- nullable; embed only when you'll retrieve
  embed_model   TEXT
);

CREATE INDEX episodic_session_ts ON episodic_events (session_id, ts DESC);
CREATE INDEX episodic_embed     ON episodic_events USING hnsw (embedding vector_cosine_ops)
  WHERE embedding IS NOT NULL;

-- Semantic: extracted facts the agent should remember.
CREATE TABLE semantic_facts (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subject       TEXT NOT NULL,  -- 'user:42' | 'project:acme' | 'global'
  predicate     TEXT NOT NULL,  -- short label: 'prefers', 'deploy_target', 'plan_tier'
  object        TEXT NOT NULL,  -- the fact value
  confidence    REAL NOT NULL DEFAULT 1.0,
  source_event  BIGINT REFERENCES episodic_events(id),
  embedding     VECTOR(1536) NOT NULL,
  embed_model   TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  superseded_by UUID REFERENCES semantic_facts(id),
  superseded_at TIMESTAMPTZ
);

CREATE INDEX semantic_subject ON semantic_facts (subject)
  WHERE superseded_at IS NULL;
CREATE INDEX semantic_embed   ON semantic_facts USING hnsw (embedding vector_cosine_ops)
  WHERE superseded_at IS NULL;

-- Procedural: cached tool outcomes.
CREATE TABLE procedural_cache (
  cache_key     TEXT PRIMARY KEY,           -- hash(tool_name || canonical(args))
  tool_name     TEXT NOT NULL,
  args          JSONB NOT NULL,
  result        JSONB NOT NULL,
  computed_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at    TIMESTAMPTZ NOT NULL,
  hit_count     INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX procedural_expires ON procedural_cache (expires_at);

A few notes on the choices before we get to the queries.

The episodic embedding column is nullable. Most events — system messages, tool-call envelopes, error stack traces — will never be retrieved by similarity. Embedding them costs money and bloats the HNSW index for no benefit. Embed only the events you actually search: user turns and final agent responses, typically.

Semantic facts use soft-supersedes, not hard updates. When the user changes their preference, you don’t UPDATE the row — you insert a new row and set the old row’s superseded_by. The partial index WHERE superseded_at IS NULL keeps the live view small, and the full history is still there for “when did this change?” queries. This pattern matters more than people admit: the moment an agent contradicts itself with a stale fact, the contradiction is debuggable only if you can see the old fact and the event that should have superseded it.

Procedural cache keys are hashes of canonicalised args, not the raw JSON. Canonicalise means: sort keys, normalise whitespace, lowercase the tool name. Skip this and the same logical call with arguments in a different order becomes a cache miss, and your “cache” runs at a 20% hit rate while you wonder why the agent is slow.

Writing memory: the agent’s tick

The hot path is the agent loop. Every tick, the agent appends to episodic, occasionally extracts a fact into semantic, and routinely reads-and-writes procedural. The constraint is that all of this must be transactional with respect to one event — partial writes mean the agent’s view of its own past disagrees with its view of its own outputs.

-- Append a user turn and embed it in one transaction.
BEGIN;

WITH event AS (
  INSERT INTO episodic_events (session_id, actor, kind, payload, embedding, embed_model, tokens_in)
  VALUES ($1, 'user', 'message', $2::jsonb, $3::vector, 'text-embedding-3-small', $4)
  RETURNING id
)
INSERT INTO semantic_facts (subject, predicate, object, source_event, embedding, embed_model)
SELECT $5, $6, $7, event.id, $3::vector, 'text-embedding-3-small'
FROM event
WHERE $6 IS NOT NULL;  -- only insert the fact if extraction produced one

COMMIT;

One transaction, one embedding cost, one round-trip. The fact row points to the event that produced it; the event row carries the same embedding it would have written to a sibling vector store in the two-database version. The drift window from the RAG post is gone in the same way it was gone there: you cannot read the event without seeing the fact, because they were inserted under the same BEGIN.

Procedural writes are simpler — an INSERT ... ON CONFLICT (cache_key) DO UPDATE keyed on the canonicalised hash. The only subtlety is to also bump hit_count on every read (not just on the conflict path), which you do with a separate UPDATE on the cache row at retrieval time. Without that counter you have no signal for which cached tool calls are pulling weight and which are dead weight that should be evicted early.

Reading memory: the four queries

Four queries cover ~90% of agent retrieval needs.

1. Recent context. The last N events of the current session, in order. This is the conversation history you feed back to the model.

SELECT actor, kind, payload, ts
FROM episodic_events
WHERE session_id = $1
ORDER BY ts DESC
LIMIT $2;

The compound index (session_id, ts DESC) makes this a range scan. No vector cost, no fan-out — this is the cheapest query in the system and you will run it many times per tick.

2. Similar past events. Used for “have I seen something like this user question before, across sessions?” This is where episodic embeddings earn their keep.

SELECT id, session_id, ts, payload,
       1 - (embedding <=> $1::vector) AS similarity
FROM episodic_events
WHERE embedding IS NOT NULL
  AND ts > now() - interval '30 days'  -- bound the search by retention
ORDER BY embedding <=> $1::vector
LIMIT 10;

The ts > now() - interval '30 days' clause is the retention boundary doing real work — without it, the HNSW search degrades over time and the results get less and less relevant because older events dominate.

3. Subject’s live facts. Everything the agent currently believes about a user or project.

SELECT predicate, object, confidence, created_at
FROM semantic_facts
WHERE subject = $1
  AND superseded_at IS NULL
ORDER BY confidence DESC, created_at DESC;

The partial index WHERE superseded_at IS NULL means this is an index-only scan in the common case. Live facts only. Supersession bookkeeping pays off here: you never accidentally feed the model a stale belief alongside the current one.

4. Cache lookup. The procedural read-and-bump.

UPDATE procedural_cache
SET hit_count = hit_count + 1
WHERE cache_key = $1
  AND expires_at > now()
RETURNING result, computed_at;

The UPDATE ... RETURNING pattern collapses read + write-back-stats into one round-trip. If the row is missing or expired, the result set is empty and the agent falls through to recomputing.

Supersession in practice

The semantic-supersession pattern needs one helper. When the agent decides a new fact contradicts an existing one, mark the old one superseded in the same transaction as the new insert:

BEGIN;

INSERT INTO semantic_facts (subject, predicate, object, source_event, embedding, embed_model, confidence)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id;  -- capture the new id, call it $new_id

UPDATE semantic_facts
SET superseded_by = $new_id,
    superseded_at = now()
WHERE subject = $1
  AND predicate = $2
  AND superseded_at IS NULL
  AND id <> $new_id;

COMMIT;

The subject+predicate match is the supersession key — “user 42’s preferred deploy target” is a single slot in the conceptual model, and only one row should be live per slot at a time. The transaction is what makes this safe under concurrent agent ticks: the partial index sees a consistent live-view at any point.

Retention and bounded growth

Without retention every memory table grows unboundedly. Three rules covers the realistic shape:

• Episodic older than 30 days, except events referenced by a still-live semantic_facts.source_event. A scheduled DELETE with a NOT EXISTS subquery does the work.
• Semantic with superseded_at < now() - interval '180 days' is hard-deleted. Soft-supersedes preserve recent history; long-tail history goes.
• Procedural with expires_at < now() is deleted at a fixed cadence, or on eviction pressure (track pg_relation_size against a budget).

This is the difference between a memory layer and a memory leak. Without these, the agent’s recall gets slower every week and you eventually rewrite it as a sibling vector store with a CDC pipeline — which is exactly the architecture the multi-model approach exists to avoid.

What this schema is not

This is not a replacement for a working-set context buffer (the last few thousand tokens of the current conversation, held in memory or in the agent process’s own state). That belongs in the agent runtime, not the database. Episodic memory is the durable record of the same conversation, available across sessions and across agents.

It also isn’t a replacement for vector-only embedding databases at very high cardinality — if you have a billion fact rows and need single-digit-millisecond ANN, the access pattern looks different. The schema here is sized for the typical agent project: thousands to millions of events, tens of thousands to low millions of facts. That is where most projects live for the first two years, and it is exactly the size where the operational overhead of a second database hurts the most.

The shorter version

Three tables, separated by access pattern not by data type:

• episodic_events — ordered, mostly immutable, nullable embedding, retained for weeks.
• semantic_facts — soft-superseded, subject-keyed, always embedded, retained for months.
• procedural_cache — hash-keyed, explicit TTL, hit-counted, retained until expiry.

One transaction writes an event plus any extracted facts. One query reads the conversation; one query reads the subject’s live facts; one query reads the cache. The drift window is gone because the writes are atomic. The supersession history is preserved because updates aren’t destructive. Retention is bounded because every table has an answer to “when do rows leave.”

Copy the schema. Tune the embedding dimensions, the retention intervals, the HNSW parameters. The shape stays.