Why mixing source-of-truth with retrieval optimizations is how AI agent memory systems start lying to you
Companion notebook: https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/notebooks/two_layer_pattern_walkthrough.ipynb
Key takeaways
- Persistent memory is the source of truth. Embeddings, summaries, caches, and pre-joined views are derived from it.
- Every derived artifact points back to the exact canonical row and version it came from.
- When canonical memory changes, derived context gets regenerated, expired, or annotated as a pinned snapshot. That call is a policy decision, made per memory type.
- Match the sync strategy to what a stale read costs. Transactional for critical facts, scheduled for summaries and projections, lazy for cheap rollups.
- Delete the derived layer and rebuild it from canonical alone. If you can’t, the separation isn’t clean.
A research assistant at Acme is comparing two retrieval models for a customer. Jane asks which one scores higher on the BEIR benchmark. The agent runs a similarity search, pulls back the most relevant chunk, and answers with confidence: Helios-RAG, 0.92 nDCG@10. Clean number, good citation, done.
The number is wrong. Six weeks ago the authors of that paper published a correction. A data contamination bug had inflated their score. The real number is 0.78. Acme’s agent ingested the correction the day it dropped. The corrected fact is sitting in the database right now, versioned and timestamped, with the old value marked superseded. The agent just didn’t read it.
It read the embedding instead. The embedding was computed from the original abstract, the one that still says 0.92, and nobody ever told the embedding it was out of date. The fact got corrected. The index over the fact did not. So the agent retrieved a vector that points at a sentence that is no longer true, and reported it as current.
This is drift. It’s quiet, it’s confident, and it’s almost never caught in testing, because in testing the fact and its embedding agree. They only diverge in production, after a correction, when the one path you didn’t instrument is the one the agent actually takes.
This is an AI agent memory problem in a way it never was for ordinary applications. A normal app mostly reads the data it wrote. An agent revises and re-embeds continuously, producing derived artifacts all day long. Without a boundary between what’s true and what’s fast, those copies eventually become the truth. Traditional databases have always managed derived structures like indexes and materialized views. What’s new is that agents continuously generate semantic artifacts like summaries and embeddings, which look increasingly like the data itself.
If you read the previous articles in this series (From Prompt to Persistence part 1 and part 2), you already have the schema that makes this fixable. Eight typed tables, scoped by tenant and versioned, with governance on top. Those articles end on a promise: the eight tables are the canonical layer, everything optimized for retrieval is derived from them, and mixing the two is how memory systems start to drift. This article is about keeping that promise. It’s the pattern that sits on top of the schema and keeps it honest as it scales.
The short version, if you only take one thing: separate what’s true from what’s fast, and make provenance point in exactly one direction. Derived context points back to canonical memory. Canonical memory never points at derived. When they disagree, canonical wins, every time, without a vote. The boundary is about authority. Canonical and derived can live in separate stores or the same row, and the rule holds either way.
How memory systems start lying to you
The drift in that opening scenario isn’t a bug in anyone’s code. Nobody wrote a line that says “return stale data.” It’s an architectural absence. Somewhere in the system there should have been a component whose job was to keep the embedding current with respect to the fact, and there wasn’t one. The fact lived in a database. The embedding lived in a vector store. The two were wired together at write time and then left to age independently.
Here’s the mechanism. A fact is valid for a window of time. “Helios-RAG scores 0.92” was true, in the sense that the paper said it, from the day it was published until the day it was corrected. An embedding, by contrast, is computed at a single instant. It’s a photograph of the fact at the moment the embedding model ran. The fact has a lifecycle. The embedding has a timestamp. Those are different kinds of objects, and a system that treats them as interchangeable will eventually serve the photograph as if it were the live feed.
It sounds obvious, but in practice, it hides for three reasons.
First, the two layers usually live in different systems. Canonical facts sit in Postgres while embeddings live in Pinecone or Elasticsearch. No transaction spans both, so updating a fact doesn’t automatically update its index. That synchronization becomes a separate process, and separate processes are the kind of things that break quietly.
Second, retrieval is designed to bypass the source of truth. The whole point of an index is that you query it instead of scanning the underlying data. So even if the canonical fact has already been corrected, the agent never sees it because the fast path goes through the derived layer.
Third, stale derived context looks perfectly healthy. A stale embedding still returns results. A stale summary still reads well. Nothing throws an exception or logs an error. The system confidently serves information that’s no longer true, and unless someone checks it against the canonical source, nobody notices.
Anti-pattern: the index is the source of truth. If a fact exists only as an embedding, you’ve already lost. You can’t audit it, you can’t prove you deleted it, and when the embedding model changes you can’t rebuild it. The vector is acceleration. It is never the record. The moment it becomes the record, you’ve signed up for a memory system that drifts and can’t tell you it’s drifting.
If you don’t separate truth from retrieval, your system will lie to you. More retrieval tuning won’t save you here. What you need is a boundary.
Persistent memory and derived context: the two-layer pattern
The pattern is two layers and one rule.
Persistent memory is the long-term memory for AI agents: the canonical record of what the system knows. Facts, decisions, policies, preferences, the things an agent has learned and is allowed to act on. Every row has provenance (who wrote it, what event caused it) and an AI agent memory lifecycle (when it became valid, when it expired). This is the source of truth. If it isn’t in the persistent layer, the system doesn’t actually know it.
Derived context is every form of that knowledge shaped for a specific access pattern. Embeddings for similarity search. Summaries that compress a long episode into a paragraph. Pre-joined views that collapse four tables into one read. Denormalized blobs that a hot path can grab without a join. None of it is new information. All of it is the persistent layer, re-expressed so retrieval is cheap.
The rule that connects them is directional, and it’s the entirety of the pattern: derived context is built from persistent memory, never the other way around. Provenance always points from derived back to canonical. When persistent memory changes, derived context gets recomputed. When the two disagree, persistent memory wins. If you can’t trace a piece of derived context back to the canonical row it came from, that piece of derived context shouldn’t exist.
This is the same invariant the previous articles stated in one sentence, now made structural: you can re-derive an embedding from a row, but you can never re-derive the row from an embedding. The row holds the fact. The embedding just points to it. Persistent memory is what’s true and derived context is what’s fast. The entire job of this article is keeping you from confusing them.
The schema from the previous articles already lives this pattern, even though it never drew the line explicitly. Look at entity_memory: the content column holds the canonical fact, and the embedding column holds a VECTOR(384, FLOAT32) derived from that content. Canonical and derived, same row. Now look at conversation_memory: no vector column at all. The raw event stream is high-volume and append-only, so its derived context (a semantic cache for recent turns) gets computed into a separate structure rather than riding on every row. Two different storage decisions, one pattern. The boundary was always there. We’re just naming it so you can apply it on purpose instead of by accident.
What lives where, by memory type
The boundary applies across every memory type in the schema, but it shows up a little differently in each one. Below is the complete map for reference.
| Memory type | Persistent (canonical) | Derived (context) |
Guideline (guideline_memory) | Versioned guideline rows with effective dates, retrieved by exact match | Materialized active guideline set per scope |
Persona (persona_memory) | User-scoped key/value rows, the in-force preference set | Materialized active persona profile per user |
Entity (entity_memory) | Fact rows with subject/predicate/content, provenance back to a source event | Vector embedding, hybrid index entries |
Summarization (summarization_memory) | Structured summaries of completed work, pinned to a source run | Embedding of the summary, plus a reverse index by entity |
Conversation (conversation_memory) | Append-only event stream, the flight recorder | Semantic cache over recent turns, aggregated rollups, replay snapshots |
Workflow (workflow_memory) | Learned procedures with success/failure counts | Embedding over the description for similarity matching |
Knowledge base (knowledge_base_*) | Document pointer plus ingested chunk content | Chunk embeddings, the hybrid retrieval index |
A few of these deserve a closer look, because they’re good examples of how the same pattern takes different forms across memory types.
Entities are where the pattern does the most work. A fact has a canonical text, and that text gets embedded and indexed for retrieval, and all of those forms are derived. When the fact is superseded, all of those forms have to follow, or you get the opening scenario. This is the type where the embedding-outlives-the-fact failure does the most damage, because entity memory is where compounding advantage lives, and a wrong fact retrieved confidently is worse than no fact at all.
Conversations are the inverse. The raw event stream is canonical and enormous, and it carries no vector at all. Its derived context, the semantic cache that lets the agent search recent history, is computed asynchronously into a separate structure, kept outside the canonical event stream and refreshed lazily or on a schedule as freshness demands. You’d never pay embedding cost on every logged tool call. The volume is too high and the value per row is too low. So the derived layer for conversations is sparse, lossy, and rebuilt lazily, and that’s the right call for a flight recorder.
Three ways to keep the two layers in sync
Once derived context can drift from canonical memory, the question becomes: how quickly do you close the gap? There are three common synchronization strategies, each striking a different balance between freshness and write cost.
| Strategy | Freshness | Write cost | Best for |
| Lazy: rebuild on read miss | Stale until the first read after a change | Lowest | Cheap-to-rebuild context read rarely (conversation rollups) |
| Scheduled: refresh on a cron | Bounded by the refresh interval | Medium | Pre-joined projections and materialized views |
| Transactional: rebuild in the same write | Immediate with no gap | Highest (embedding cost in the write path) | Critical facts where a stale read is a wrong answer (entity facts) |
Lazy: rebuild on read miss. Derived context is recomputed the first time it’s queried after the canonical row changed. You mark the derived artifact stale when the canonical write happens, and the next read that hits it pays the rebuild. The failure mode is staleness until queried: the gap stays open for an unbounded amount of time, right up until the moment someone reads it, at which point it closes. Bad for anything where the first stale read is itself the problem.
Scheduled: refresh on a cron. Derived context is rebuilt on a fixed cadence, every five minutes, every hour, nightly. The failure mode is staleness between refreshes: the gap is bounded by your interval, but it’s always at least partly open. Episodic summaries are the textbook case. A summary of last week’s planning meeting doesn’t need second-level freshness, and rebuilding every summary in the tenant on every write would be absurd.
Transactional: rebuild in the same write. The derived context is recomputed in the same transaction as the canonical change. The fact and its embedding update together or not at all. No gap, ever. The failure mode isn’t staleness, it’s cost: every canonical write now pays embedding-generation latency in the write path. The opening scenario is exactly this case, and exactly the case where you want transactional sync.
The trap is reaching for one strategy across the whole system. Conversation rollups don’t need transactional synchronization, and benchmark facts shouldn’t wait for a scheduled refresh. Pick the strategy per memory type, based on how costly a stale read is and how often the canonical data changes. One pattern, three synchronization policies, each chosen to fit the memory it’s protecting.
When derived context legitimately outlives a canonical change
Now the uncomfortable case, because it’s the one people get wrong in the other direction. Sometimes derived context is supposed to survive a change to canonical memory. Not all staleness is drift. Some of it is intent.
A summary titled “what we decided in the Q3 planning review” is a snapshot of an episode at a moment in time. If the underlying transcript is later corrected, the right move might be to regenerate the summary, or it might be to leave it exactly as it was. If the summary is meant to capture what the team believed when they made the decision, then “correcting” it to reflect a fact that surfaced later actively destroys its value. The decision was made on the old information. The summary records the decision. It was never meant to be a live view of the facts.
The bigger idea is that memory is temporal. Every fact carries the window when it was true, along with who changed it and why. The version simply records that history, and it lets every piece of derived context stay explicit about which point in time it reflects.
So the rule “canonical changes, derived rebuilds” needs a qualifier. The rule is: derived context must always know which version of canonical it came from. What you do when canonical moves past that version is a deliberate policy decision, made per memory type.
The mechanism is provenance. Every durable row already carries a source_event_id and a version, so every piece of derived context can point back to the exact version of canonical it came from. When canonical memory changes, the system can find every derived artifact built from the old version and decide, per type, what to do with it:
- Regenerate. The default for a re-derived projection or a re-ingested document’s chunks. The derived context exists to reflect current truth, so move it forward.
- Expire. The right call when the derived context is now meaningless. A pre-joined view of a deleted record should disappear rather than update.
- Annotate. The right call for snapshots. Leave the summary intact, but stamp it: “derived from transcript v3; transcript is now at v5.” The snapshot stays clear about being a snapshot.
The provenance pointer is the entire governance story. Because every derived row can name the version of canonical it came from, you can always ask which artifacts have fallen behind, and from there the regenerate/expire/annotate call is a switch on memory type. Take the pointer away and you can’t even ask the question.
Derived context pinned to a canonical version is fine. It can be old on purpose, and you can prove what it’s old relative to. Derived context pinned to nothing is the bug. It’s old by accident, and you can’t tell.
The single-table option (and when to avoid it)
Everything so far works whether you keep canonical and derived in separate stores or the same one. The pattern is about the boundary. Storage layout is a separate concern, but it determines how hard that boundary is to enforce. That’s where a converged engine like Oracle AI Database changes the math. In a converged engine, canonical rows, vector columns, SQL, metadata, and governance share one path, so the boundary becomes something the database enforces rather than something you wire together across separate systems and hope holds.
In a polyglot setup, the canonical fact is a row in Postgres and its embedding is a vector in Pinecone. Two systems, two writes, no shared transaction. To keep them in sync you write application code that updates Postgres and then calls Pinecone, hoping both land. When the second call fails, you have a fact with no embedding, or an embedding for a fact that rolled back. There’s an eventual-consistency window on every write. The opening scenario is what this looks like after the sync process has been quietly failing for six weeks.
In a converged store, the canonical fact and its embedding are columns in the same row, updated in the same statement. Our schema’s entity_memory is already built this way:
-- Corrected fact and its new embedding: one INSERT, one transaction.
INSERT INTO entity_memory (id, tenant_id, user_id, subject, predicate, content,
content_hash, embedding, confidence, written_by,
source_event_id, version, valid_from, created_at)
SELECT :new_id, tenant_id, user_id, subject, predicate,
'Helios-RAG achieves 0.78 nDCG@10 on BEIR (corrected).',
:corrected_hash,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING
'Helios-RAG achieves 0.78 nDCG@10 on BEIR (corrected).' AS DATA),
confidence, written_by, :correction_event_id, version + 1,
SYS_EXTRACT_UTC(SYSTIMESTAMP), SYS_EXTRACT_UTC(SYSTIMESTAMP)
FROM entity_memory WHERE id = :old_id;
-- Same transaction: retire the old row, then commit together.
UPDATE entity_memory SET valid_until = SYS_EXTRACT_UTC(SYSTIMESTAMP),
superseded_by = :new_id WHERE id = :old_id;
COMMIT;
The embedding is recomputed by the database, in the write, from the corrected text. Provenance (source_event_id) ties the new row to the correction event. The old row is retired in the same transaction. When the commit returns, there is no version of reality in which the fact says 0.78 and the index says 0.92. They moved together because they’re the same row and ACID guarantees it. No two-phase commit, no eventual-consistency window, no orphaned embedding.
This is the transactional sync strategy from earlier, made trivial. The thing that’s a distributed-systems problem across two stores is one INSERT ... SELECT inside one. ACID across the boundary is the whole reason the hard version of this pattern collapses into the easy version.
There is a tradeoff, though. You’re paying for embedding generation in the write path. For entity facts that’s the right trade: facts change rarely, retrieval correctness matters a lot, and a few hundred milliseconds on a supersede is invisible. For high-volume types it’s the wrong trade, and the schema already reflects this. The boundary is identical. Only the storage profile differs.
Anti-pattern: vectors on every row of a high-volume table. If you find yourself embedding every trace event inline, you’ve applied the single-table option where the two-table option belongs. Split the derived layer out, sync it on the cheapest strategy that’s still acceptable, and let the canonical stream stay lean.
Why the rebuild path is the test of a clean separation
Here’s the question that tells you whether you actually have two layers or just two tables: can you delete the entire derived layer and rebuild it from canonical memory alone?
If yes, you have a clean separation. The derived layer is genuinely derived, the arrow genuinely runs one way, and you can treat the whole retrieval apparatus as disposable. If no, then something is hiding in the derived layer that exists nowhere else, which means it’s not derived at all. It’s canonical data wearing an index’s clothes, and you’ve lost provenance, replayability, and your deletion guarantees the moment you can’t reproduce it.
So the rebuild path is the definition of the pattern. Everything else rests on whether you can run it. If you can’t write the query that regenerates every derived row from canonical, you don’t have a memory system. You have a cache you can’t rebuild.
The query itself should be boring:
INSERT INTO knowledge_base_chunk (id, tenant_id, document_id, chunk_index, content, embedding, created_at)
WITH chunks AS (
SELECT JSON_VALUE(ct.column_value, '$.chunk_data') AS chunk_data,
JSON_VALUE(ct.column_value, '$.chunk_id' RETURNING NUMBER) AS chunk_id
FROM TABLE(DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(:doc_text,
JSON('{"by":"words","max":200,"overlap":20}'))) ct
)
SELECT 'kbc_' || LOWER(RAWTOHEX(SYS_GUID())),
:tenant_id, :doc_id,
ROW_NUMBER() OVER (ORDER BY chunk_id),
chunk_data,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING chunk_data AS DATA),
SYS_EXTRACT_UTC(SYSTIMESTAMP)
FROM chunks;
DBMS_VECTOR_CHAIN is one example of this principle in action: instead of an external chunking script on a cron that someone has to remember exists, the derivation is a governed, in-database pipeline that chunks, then embeds. VECTOR_EMBEDDING and DBMS_VECTOR_CHAIN both run inside the database, so no row leaves for an external embedding service and the derivation stays on the same governed path as the data it comes from. The derivation becomes a queryable object, which means the rebuild path is something you can test, schedule, and reason about, not a folder of scripts nobody’s run since the person who wrote them left.
Three things become easy once the rebuild path is a single query.
A new embedding model is a derived-layer rebuild. It touches nothing in canonical. Embeddings are a moving target; a better model ships every few months. When you adopt one, you add a shadow embedding column, backfill it from canonical with the new model, validate it against the old, then swap the index and drop the column you replaced. Canonical memory never moves. This is the difference between an afternoon and a migration project.
A different chunking strategy is the same operation. Decide 200-word chunks were too coarse, change the DBMS_VECTOR_CHAIN parameters, rebuild. The facts don’t care how you chunk the text you derived from them.
And the expensive-to-compute, cheap-to-read projections (the pre-joined retrieval views that collapse four tables into one read) are exactly what materialized views were built for. The canonical tables are the base, and the materialized view is derived from them. Its refresh policy is the sync strategy, declared in DDL.
-- A derived projection: active facts pre-joined to their source documents,
-- refreshed on a schedule. The base tables are canonical; this is derived.
CREATE MATERIALIZED VIEW active_fact_retrieval
REFRESH COMPLETE
START WITH SYS_EXTRACT_UTC(SYSTIMESTAMP)
NEXT SYS_EXTRACT_UTC(SYSTIMESTAMP) + INTERVAL '1' HOUR
AS
SELECT e.id, e.tenant_id, e.subject, e.predicate, e.content, e.embedding,
d.title, d.source_uri
FROM entity_memory e
LEFT JOIN knowledge_base_document d
ON d.id = JSON_VALUE(e.content_json, '$.source_document_id')
WHERE e.valid_until IS NULL
AND e.deleted_at IS NULL;
In practice, production deployments may require additional considerations around tenancy and security policies, but the architectural pattern remains the same.
That REFRESH COMPLETE ... NEXT ... INTERVAL '1' HOUR is the scheduled sync strategy from earlier, declared instead of coded. The view is disposable. Drop it, change the join, recreate it. Canonical memory doesn’t notice.
Where this fits in the agent loop
Tie it back to the five-step agent loop: Ingest, Retrieve, Infer & Act, Evaluate, Promote. The two-layer pattern maps onto it cleanly.
Retrieve reads the derived layer. That’s where the indexes are. Context engineering for AI agents happens here: when the agent assembles context, it queries embeddings, hybrid retrieval indexes, and pre-joined views, because those are the fast paths. Retrieve never reads canonical directly for similarity, because canonical is shaped for truth, and similarity search would crawl against it.
Promote writes the canonical layer. AI agent memory promotion is the only step allowed to modify durable memory, and durable memory is the persistent layer. When the research assistant ingests the correction and decides the benchmark fact has changed, that write lands in entity_memory, the canonical table, gated by the promotion logic from the previous article.
Derivation runs between them. After Promote writes canonical, derived context is recomputed, by whichever sync strategy that memory type uses. Transactionally in the same write for critical facts, on a schedule for summaries, lazily for rollups. The derivation step is the most under-engineered piece of most agent stacks. People build a careful promotion gate and a careful retrieval pipeline and then connect them with a cron job and some hope. The connection is the pattern. It deserves the same care as the two ends it joins.
Get this right and the agent reads fast and writes true, and the gap between the two is bounded by a policy you chose instead of a bug you didn’t.
FAQs
Is canonical memory always correct?
No. Canonical means the version the system is currently allowed to act on, with its provenance and history attached. It can still be wrong. What it can never be is untraceable, because you can always see who wrote it, when it became valid, and what it superseded.
Why can’t the vector store be the agent’s memory?
Because a vector is acceleration. It is never the record. If a fact exists only as an embedding, you can’t audit it, you can’t prove you deleted it, and when the embedding model changes you can’t rebuild it. Vectors help you find memory. Something else has to hold it.
How do you detect stale derived context?
Store the canonical row’s id and version alongside every derived artifact. When the canonical record moves past that version, everything still pinned to the old one is stale, and one query finds all of it.
Should embeddings always update transactionally?
No. Go transactional when a single stale read is a wrong answer, like a corrected benchmark number. Use a scheduled refresh for summaries and pre-joined views, and lazy rebuild-on-read for cheap rollups nobody queries often.
Can derived context stay on an older version?
Yes, and sometimes it should. A summary of what a team decided in Q3 is supposed to reflect what they knew at the time, even after the underlying transcript gets corrected. The requirement is that it stays pinned to that version, so you can prove what it’s old relative to.
How do you test the architecture?
Delete the derived layer and rebuild it from canonical memory. If anything you needed disappeared for good, it was living in the wrong layer.
Where this leaves you
The previous articles introduced the schema (Part 1, Part 2). This article introduces the pattern that keeps that schema from drifting: persistent memory is canonical, derived context is rebuilt from it, provenance points one direction, and when they disagree the canonical layer wins. Single-table or two-table, polyglot or converged, the boundary is the same. What changes between those choices is how much work it takes to enforce, and a converged engine with ACID across the boundary takes the most dangerous version of the work and makes it one transaction.
There’s one piece left. Now that canonical and derived are separated, the next question is how to query the derived layer well. Retrieval reads it, but reading it well (vector plus lexical plus metadata, fused and reranked into a single ranking) is its own multi-stage pipeline. That’s the next article. The pattern here is what makes that pipeline tractable, because it guarantees that whatever the retrieval pipeline returns, it’s pointing at something true.
The easiest way to know whether you’ve built a memory system or just a cache is simple: delete the derived layer. If you can rebuild it completely from canonical memory, you’ve built a production-ready AI agent memory system. If you can’t, you’ve built a cache with better marketing.