Retrieval, sharing, and the agent loop on top of a multi-tenant memory schema
Companion notebook: https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/8c9ed028b1bea545f2cbefe057470a0294bf29b7/notebooks/multitenant_schema_walkthrough.ipynb
Key takeaways
- Part 2 turns the schema into an operating system for agent memory. It explains how short-term memory, durable long-term memory, shared memory, and retrieval work together in the agent loop.
- The Memory Manager is the control point. Agents should not write directly to memory tables; the manager enforces tenant context, provenance, deduplication, versioning, deletion, and typed reads and writes.
- Shared memory is scope-based, not a separate table. A memory becomes shared when it is written broadly enough inside a tenant, such as with
agent_id IS NULL, so multiple agents can read and update it safely. - One database engine simplifies tenant-safe retrieval. The article argues that keeping policies, personas, entities, summaries, workflows, toolbox data, knowledge base chunks, and conversation events in one engine reduces cross-system security and deletion risks.
Part 1 designed the durable layer: eight typed memory tables, each carrying the same four scope columns (tenant_id, user_id, agent_id, thread_id) and the same lifecycle columns (version, valid_from, valid_until, deleted_at, source_event_id). Tenant isolation lives in the database through row-level security (RLS). The other three scope dimensions are application-supplied filters. Provenance on every durable row is what makes versioned supersession and a provable right-to-forget cascade possible. If you haven’t read it, start there, because the system we create in this post assumes all of it.
A schema doesn’t do anything on its own. It says what can be stored and how it’s isolated, but it doesn’t retrieve or rank rows, and it doesn’t decide what’s worth keeping. This post is about the code that does. We’ll start with the short-term layer that sits in front of the durable tables, then work up through shared memory, the Memory Manager that fronts the whole schema, the single retrieval query that one engine makes possible, and the agent loop that ties reads and writes to specific tables at specific steps.
Short-term memory in a multi-tenant context
STM is structurally different from LTM. It’s ephemeral by design and scoped to the current run, and most of it lives outside the database. But it interacts with LTM at well-defined seams, and in a multi-tenant system those seams need the same discipline as the durable layer.
Working memory: LLM Context Window + Session Memory
The LLM context window is the most visible piece of working memory: the tokens passed to the model on this turn. The Memory Manager assembles it from the durable layer (active guidelines, active personas, retrieved entities, retrieved summaries) plus the volatile tail (the last N conversation events for this thread). Nothing about the assembly is database-level. The database supplies the inputs, and the manager composes them into the prompt the model actually sees.
Session memory is the in-process scratchpad. Tool call results, intermediate reasoning state, retrieval candidates the agent decided not to surface yet, partial work the agent might want to reference later in the same run. In a single-tenant prototype, this could easily live in a Python dict on the AgentSession object. In multi-tenant SaaS, the question is where to persist it, and do we persist it at all?
class AgentSession:
def __init__(self, tenant_id, user_id, agent_id, thread_id, run_id, memory):
# Identity
self.tenant_id = tenant_id
self.user_id = user_id
self.agent_id = agent_id
self.thread_id = thread_id
self.run_id = run_id
# Ephemeral STM (lost at end of run; reconstructable from conversation_memory)
self.scratch = {} # tool outputs, intermediate state
self.turn_buffer = [] # current turn's events before flush
# Durable, via the memory manager
self.memory = memory
The case for persisting session memory in multi-tenant SaaS is crash recovery. If the agent process dies mid-run, the user shouldn’t have to start over. Since conversation memory already records everything durably, we can pull session state from it after a crash. The pattern that works: write session memory as conversation_memory events with event_type = 'session_state', keyed by run_id. Reads after a crash restore the scratchpad from the most recent state event for that run. No special retention class needed; these events follow the same lifecycle as every other conversation record.
That keeps session memory architecturally consistent with conversation memory (the same table, scope columns, RLS policy, and retention sweep). Anything recorded to conversation memory is durable anyway, so this isn’t adding a new storage class; it’s using the existing retention lifecycle. The retention_class column controls how long records stick around (short, standard, or audit windows), and a nightly sweep drops the partitions that have aged out. Session state tagged as ‘short’ gets cleaned up automatically after the retention window closes.
Semantic Cache
The semantic cache is a vector index over recent conversation history, sitting between Working Memory and Long-Term Memory. The pattern handles a specific failure mode: a user references something from earlier in the conversation that’s already fallen out of the volatile-tail window, but the context they’re referring to isn’t important enough to have been promoted to entity or summarization memory. Pure recent-turn retrieval misses it; semantic search over the full conversation history finds it.
In implementation, the semantic cache is a derived projection over conversation_memory. Two valid shapes:
Per-thread (narrow). A vector index over conversation_memory rows filtered by (tenant_id, thread_id). Useful for long-running threads where context drift within the same conversation is the failure mode.
Per-user-recent (broad). A vector index over conversation_memory rows filtered by (tenant_id, user_id) and created_at > NOW() - INTERVAL '30 days'. Useful for cross-thread continuity (“you mentioned this last week when we were working on something else”).
The per-user-recent shape is more useful in SaaS because users come back across sessions and threads more often than they hit the long-thread case. Both shapes can coexist; the manager picks one based on the query intent.
The semantic cache doesn’t need its own table. It’s a vector index over an existing table, plus a retrieval function that knows how to fuse its hits with the volatile tail of the prompt. Retrieval fusion deserves its own deep dive; for this post, the schema is just CREATE VECTOR INDEX idx_conv_semantic ON conversation_memory (embedding) … on a conversation_memory table that has a populated embedding column for events older than the volatile-tail window.
Shared Memory and Coordination
Both of these are top-level categories in the Oracle blog taxonomy because they’re concerns that cut across the type hierarchy. In single-agent systems they collapse into normal LTM scoping. In multi-agent and multi-tenant systems they need separate treatment.
Shared Memory
Shared memory is any LTM row that multiple agents read and write under the same access boundary. The structural definition falls out of the scope columns directly: a row is shared when it sits at a scope broader than a single agent, which means agent_id IS NULL (shared across every agent in the tenant) or agent_id = '<group_id>' (shared across a defined coordination group).
A few examples to make this concrete inside a single SaaS tenant:
- A support agent learns that “Acme’s production database moved from us-east-1 to eu-west-1.” Writing this as an entity_memory row at
(tenant_id = 'acme', user_id = NULL, agent_id = NULL)makes it visible to every agent serving Acme. Whatever the support agent learns, every agent on the tenant can use. Compounding advantage across the whole tenant. - A billing agent and a support agent need to coordinate on whether a refund request has been approved. The decision lives in
summarization_memoryat(tenant_id = 'acme', user_id = :user_id, agent_id = NULL), where both agents can read it, neither agent owns it exclusively. - A research-assistant agent maintains a workflow (“how we evaluate competitor papers”) that should be available to every research assistant Acme spawns. The workflow row sits at
(tenant_id = 'acme', agent_id = NULL)so any agent instance can find it.
Shared memory doesn’t need a new table type. It’s a write-side discipline: when promoting a candidate to LTM, the manager picks the narrowest scope that’s actually correct, and “actually correct” sometimes means broader than the agent that wrote it. The promotion gate from “From RAG to Memory Systems: Building Stateful AI Architecture” is where that decision belongs. Default to user scope, and promote to tenant scope only when the subject of the fact is the tenant entity itself and the fact has been observed from two independent sources.
What shared memory does need is read-side coordination. Two agents writing facts about the same subject at the same time can produce contradictory rows if the writes overlap. The supersession pattern handles the resolution (later write wins by version), but the agents themselves need to know they might be writing on top of each other. The simplest pattern is optimistic concurrency: the manager’s supersede_fact method checks that the row being superseded is still at the version the caller saw at read time, and raises a retry-able conflict if it isn’t.
Coordination
Coordination is the cross-agent messaging layer. Agent A finishes a step and hands off to Agent B. Agent C broadcasts an event that other agents subscribe to. Agent D queries the system for “who’s working on this customer right now.”
This post doesn’t define a schema for coordination memory because the design space is still wide open. Many different patterns have emerged and new ones are still being experimented with. The right shape depends on the orchestration runtime as much as on the memory layer. The thing worth naming explicitly is that coordination is a separate concern from shared memory. Shared memory is “two agents read the same row.” Coordination is “Agent A tells Agent B that something happened.”
A working baseline for coordination in a multi-tenant SaaS context: events go into a partitioned table scoped by (tenant_id, coordination_group_id, created_at), with the same partitioning strategy as conversation_memory. Subscribers poll or stream from the table; producers append. The table participates in the same RLS policy as everything else, so coordination events never cross tenants by accident.
The Memory Manager: one door into the schema
The schema is the contract. The Memory Manager is the only code allowed to touch it. Every read and write goes through the manager, and it enforces what the schema can’t. Every operation carries tenant context, all durable write carry provenance, data lands in the table that matches its type, and supersession runs in one transaction instead of an error prone multi-step process.
The interface is small and shaped exactly like the typed tables it wraps. Eight write methods, eight read methods, one supersession method per supersedable type, one delete method. The one rule worth holding the line on is no escape hatch that bypasses the schema, because the moment one exists, every team that finds it will use it.
class MemoryManager:
"""One door into the schema. Tenant context comes from scope_ctx and is
enforced by RLS; the manager adds provenance, dedup, embedding, and
transactional supersession on top."""
def __init__(self, db, scope_ctx): ... # scope_ctx sets the tenant context for RLS
# WRITES — one typed method per memory type; provenance required on durable types
def write_entity(self, *, subject, predicate, content, confidence,
written_by, source_event_id,
user_id=None, agent_id=None, thread_id=None): ...
# write_guideline, write_persona, write_summarization, write_workflow,
# write_toolbox, ingest_document, write_conversation_event follow the same shape
# READS — scoped retrieval; the tenant predicate is applied automatically
def search_entities(self, query, *, user_id=None, agent_id=None, top_k=10): ...
# read_active_guidelines, read_active_personas, read_active_toolbox,
# search_summarizations, search_workflows, search_knowledge_base, read_thread
# SUPERSESSION — versioned, with optimistic concurrency
def supersede_entity(self, entity_id, *, new_content, written_by,
source_event_id, confidence, expected_version): ...
# DELETION — right-to-forget, one transaction each
def forget_user(self, user_id): ...
def forget_tenant(self): ...
Four invariants show up across every method. Tenant context is read from scope_ctx, never passed by the caller, because the application set it once per request and the database is already enforcing it via RLS. Provenance is a required parameter on durable types; the manager refuses any write to a durable type (entity, summarization, conversation_event) that’s missing its source_event_id. Supersession is one method per supersedable type, and it takes expected_version so concurrent writes get a retry-able conflict instead of silently clobbering each other. And deletion comes in two flavors (user, tenant), both wrapped in a single transaction internally.
The read side has its own invariants, and they’re as important as the write ones. Every read filters the same way: deleted_at IS NULL, valid_until IS NULL or still in the future. A superseded fact carries a stamped valid_until, so the moment a newer version lands the old one drops out of every read without anyone asking for it, and an expired record drops out the same way when its clock runs out. Because that predicate lives in the manager rather than in each caller, there’s no read path that can accidentally surface a stale or superseded row.
Precedence across types is really two questions, and conflating them is where retrieval designs usually go wrong. The first is governance: an authored guideline outranks an inferred preference, every time. Policies load in full into the static prefix because a rule that applies has to apply, and an inferred value carries a confidence the manager can gate against a policy-supplied floor, so a weak guess never overrides an explicit instruction. The second is relevance: when an entity, a summary, and a vector hit all speak to the same query, the manager doesn’t crown a winner. It tags each result with its type and a relevance tier and hands them back for the prompt to slot into the right region. Fusing those evidence types into a single ranking, or reranking across them, is a real pipeline and deserves its own post. The manager’s job is to keep the candidates honest and labeled. Collapsing them comes later, in a stage built for it.
The manager is also the seam where filesystem-style ergonomics meet database substrate. From the agent’s perspective, calling manager.write_entity(...) feels like writing a row to a notebook. The manager handles storage and indexing, the scope check, provenance, dedup, embeddings, supersession bookkeeping, and the tenant boundary. The agent code never writes raw SQL and never sees the schema directly. When the schema evolves, the manager changes in one place and every caller upgrades for free.
Why one engine wins for multi-tenant SaaS
By this point the schema is eight tables, four scope dimensions, row-level security on every table, transactional lifecycles, and a manager that wraps it all. The remaining question is where to put it. The polyglot persistence trap from From RAG to Memory Systems: Building Stateful AI Architecture applies here with extra force, because every cross-system pain point in a single-org agent compounds in a SaaS provider running thousands of tenants.
The accidental architecture goes the same way it always does. Postgres for users, accounts, and policies, and while Postgres handles vectors and full-text search these days, at multi-tenant SaaS scale the workloads tend to split out anyway. A dedicated vector database for the entity, summarization, and knowledge-base embeddings. Elasticsearch or OpenSearch for the lexical side of hybrid retrieval. Object storage for raw conversation transcripts and ingested documents. Maybe a graph database for entity relationships.
Each component is a reasonable choice for the job it was built for. The pain begins the moment a cross-system operation has to honor tenant boundaries.
Backups split four ways and each one needs a tenant-partitioned strategy. Security models split four ways and each one needs the same tenant predicate enforced. Deletion splits four ways and a partial deletion in one of them is a regulatory finding. Per-tenant encryption splits four ways and key rotation has to coordinate across all of them. Per-tenant data residency (Acme’s data must stay in EU; Globex’s in US) splits four ways and each system has to support the same residency rules independently.
Oracle AI Database is a great choice for this architecture because one engine can host policy data, persona data, entity data with embedding columns, summarization data with embedding columns, knowledge-base documents and chunks, workflow definitions, toolbox definitions, and conversation events, all under one row-level security model that ties tenant isolation to a session context the application sets once per request.
Because it’s all one engine, the agent’s Retrieve step pulls every memory type it needs in a single round trip. Each branch of a UNION ALL returns the same shape (type, vec_score, lex_score, relevance, payload), so guidelines, personas, toolbox definitions, entities, summarizations, workflows, knowledge-base chunks, and the recent conversation tail all come back from a single query plan, with the tenant predicate enforced by RLS on every branch (condensed for brevity):
SELECT type, vec_score, lex_score,
CASE WHEN vec_score IS NULL THEN NULL
WHEN vec_score >= 0.7 THEN 'high'
WHEN vec_score >= 0.5 THEN 'standard'
ELSE 'low'
END AS relevance,
payload, sort_bucket
FROM (
WITH
guidelines AS ( -- enumerated: loads in full, no score
SELECT 'guideline' AS type, CAST(NULL AS NUMBER) AS vec_score,
CAST(NULL AS NUMBER) AS lex_score, 0 AS sort_bucket,
JSON_OBJECT('guideline_key' VALUE guideline_key,
'guideline_value' VALUE guideline_value) AS payload
FROM guideline_memory
WHERE deleted_at IS NULL AND valid_until IS NULL
AND (user_id IS NULL OR user_id = :user_id)
AND (agent_id IS NULL OR agent_id = :agent_id)
AND (thread_id IS NULL OR thread_id = :thread_id)
),
-- Entity: hybrid. A vector candidate pool and an Oracle Text pool, FULL OUTER JOINed.
entity_vec AS (
SELECT id, subject, predicate, confidence,
VECTOR_DISTANCE(embedding,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE) AS vec_dist
FROM entity_memory
WHERE deleted_at IS NULL AND (valid_until IS NULL OR valid_until > SYSTIMESTAMP)
AND (user_id IS NULL OR user_id = :user_id)
ORDER BY vec_dist FETCH FIRST 10 ROWS ONLY
),
entity_lex AS (
SELECT id, subject, predicate, confidence, SCORE(11) AS lex_raw
FROM entity_memory
WHERE deleted_at IS NULL AND (valid_until IS NULL OR valid_until > SYSTIMESTAMP)
AND (user_id IS NULL OR user_id = :user_id)
AND CONTAINS(content, :lex_query, 11) > 0
ORDER BY lex_raw DESC FETCH FIRST 10 ROWS ONLY
),
entities AS (
SELECT * FROM (
SELECT 'entity' AS type,
CASE WHEN v.vec_dist IS NOT NULL THEN 1.0 / (1.0 + v.vec_dist) END AS vec_score,
l.lex_raw AS lex_score, -- raw lexical SCORE, shown alongside, not fused
3 AS sort_bucket,
JSON_OBJECT('subject' VALUE COALESCE(v.subject, l.subject),
'predicate' VALUE COALESCE(v.predicate, l.predicate),
'confidence' VALUE COALESCE(v.confidence, l.confidence)) AS payload
FROM entity_vec v
FULL OUTER JOIN entity_lex l ON v.id = l.id
ORDER BY COALESCE(1.0 / (1.0 + v.vec_dist), 0) DESC
) WHERE ROWNUM <= 5
)
-- persona, toolbox (enumerated); summarization (hybrid); workflow,
-- knowledge_base (vector-only); recent_conversation (the volatile tail)
-- all follow these same shapes and the same scope predicates.
SELECT type, vec_score, lex_score, payload, sort_bucket FROM guidelines
UNION ALL
SELECT type, vec_score, lex_score, payload, sort_bucket FROM entities
-- UNION ALL ... the remaining six branches
)
ORDER BY sort_bucket, vec_score DESC NULLS LAST
-- tenant_id predicate appended automatically by RLS on every branch
Three retrieval styles live in that one statement. Enumerated types (guideline, persona, toolbox) load in full with no score, because a policy or tool that applies must apply. Hybrid types (entity, summarization) pair a vector candidate pool with an Oracle Text pool through a FULL OUTER JOIN, so each row carries both a vector score and a raw lexical score, shown side by side rather than fused. Fusing the two into a single ranking, or adding a reranking pass over the merged candidates, is its own deeper topic. Workflow and knowledge base rank by vector similarity alone. The outer query maps the vector score to a relevance tier (high, standard, low), and the type column tags every row so the application can route each one to the right slice of the prompt. A single branch is worth seeing on its own, because it shows how a policy shapes what comes back.
The example below shows a single branch governed by a policy that supplies the confidence floor:
SELECT e.id, e.subject, e.content, e.confidence, e.source_event_id,
VECTOR_DISTANCE(
e.embedding,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA),
COSINE
) AS vec_dist
FROM entity_memory e
JOIN guideline_memory g
ON g.guideline_key = 'entity_retrieval'
AND g.valid_until IS NULL
AND g.user_id IS NULL -- tenant-scoped guideline
WHERE e.deleted_at IS NULL
AND (e.valid_until IS NULL OR e.valid_until > SYSTIMESTAMP)
AND e.confidence >= JSON_VALUE(g.guideline_value, '$.min_confidence')
AND (e.user_id IS NULL OR e.user_id = :user_id) -- app-supplied filter, like the canonical predicate
ORDER BY vec_dist
FETCH FIRST 10 ROWS ONLY;
-- tenant_id predicate appended automatically by RLS on both tables
It collapses to one query plan over one transactional snapshot, governed by a single security model, backup strategy, and tenant boundary. The query optimizer decides whether to apply the JSON predicate before or after the vector ranking. The application doesn’t coordinate across systems because there are no other systems to coordinate with.
A few things still don’t collapse cleanly. High-volume conversation memory at SaaS scale (millions of events per day across thousands of tenants) sometimes belongs in a store tuned for high-cardinality append-only workloads, with the OLTP engine handling everything else. Large blob assets (raw PDFs of ingested documents, generated images, video tool outputs) belong in object storage, with only their metadata living in the database. The collapse that matters is between the seven LTM tables that hold the agent’s actual knowledge (guidelines, personas, entities, summarizations, workflows, toolbox, knowledge base) which is where every meaningful retrieval join lives. Conversation memory and blobs can sit alongside; the join across the seven core types is the one the converged engine wins.
In a multi-tenant SaaS context, “one less system to enforce tenant isolation in” is the load-bearing benefit. Every external system is a place tenant boundaries can be forgotten. Collapsing the join surface into one engine collapses the security surface into one system, and that’s the architectural property that compounds across every feature added afterward.
From schema to running agent
The schema doesn’t exist for its own sake. It exists to be read from and written to by the agent loop, in a specific pattern that gives the architecture its predictability. Tying the two together is what turns the design from a data dictionary into an architecture.
The loop has five steps: Ingest, Retrieve, Infer & Act, Evaluate, Promote. Each step reads from and writes to specific tables, and the write rules are deliberately narrow.
Ingest writes to conversation_memory. Every user message, every tool call, every tool result, every model response lands as a conversation row first, before anything else happens. Conversation is the source from which everything else gets derived; if a piece of state didn’t make it into a conversation row, it can’t be reconstructed later.
Retrieve reads from the durable tables. Active guidelines via exact match by scope. Active personas via exact match by scope (the full set, every turn). Active toolbox via exact match by scope. Entities via hybrid retrieval. Summarizations via hybrid retrieval. Workflows via similarity match if the current task looks like one we’ve seen before. Knowledge base via vector search if the query is grounded in reference content. The semantic cache via vector search over recent conversation_memory if the user’s reference points outside the volatile tail. No writes in this phase. Retrieve is read-only by design.
Freshness and conflict deserve a callout here. The manager filters deleted, expired, and superseded rows at read time, so nothing stale reaches assembly. When current rows still disagree, Retrieve doesn’t pick a winner. The canonical typed tables are the source of truth, so a summary or semantic-cache hit ranks below the structured fact it came from. Each candidate comes back tagged by type, with its confidence and provenance, leaving fusion and reranking to a later stage. Guidelines are the exception. They apply in full, above the scored evidence, for the governance reasons the Memory Manager section covers.
Infer & Act reads the assembled context and writes back to conversation_memory (model response, tool calls, tool results, optional session_state events). It does not write to any other table directly. The agent’s reasoning doesn’t get promoted to entity memory by virtue of being uttered; it has to earn its way in through the promotion gate.
Evaluate runs the extraction passes for candidate entities, candidate personas, candidate summarizations, and candidate workflow updates. These get scored and queued. They don’t get written to durable tables yet.
Promote is the only step allowed to call write_entity, write_persona, write_summarization, write_workflow, supersede_*, or ingest_document. The promotion gate lives here. Each candidate gets type-specific verification, dedup, scope assignment, and a transactional write. Promote is the only step that modifies the durable LTM layer.
This narrow write rule is why provenance works. Every durable write happens inside the Promote step, which means every durable write has a clear source_event_id (the conversation event the candidate was extracted from) and a clear written_by (the agent that ran the extraction, or 'promotion_job' if it ran in a background worker). Writing to durable memory from inside Infer & Act is a code-review smell worth catching every time.
Where do we go from here
This schema is an advanced starting point for multi-tenant agent memory.
The eight core tables above are the canonical layer: the governed, versioned, tenant-isolated source of truth. Derived context (alternate embeddings, summarization rollups, pre-joined retrieval views, materialized projections) is everything optimized for retrieval that can be rebuilt from the canonical layer. Mixing the two is how memory systems start to drift. Separating them is how the schema stays honest as it scales to thousands of tenants.
Querying the derived layer well is its own challenge. Hybrid retrieval (vector plus lexical plus metadata, fused and reranked) is a multi-stage pipeline, and there’s no single library call that does it for you. The converged query above is what makes that pipeline tractable to write as one query plan instead of four cross-system round trips per tenant, but it stops where the ranking starts: it returns vector and lexical scores side by side without fusing them. Fusing and reranking those candidates, and querying the canonical-versus-derived layer above, are where the next posts go. A vector store is not a memory system, and a single-org schema isn’t a SaaS memory system. A typed, multi-tenant schema with provenance, scope, tenant isolation, and lifecycle is. Get this part right and everything else is optimization. Get it wrong and you’ll spend the next year explaining why one tenant’s data showed up in another tenant’s results.
Appendix: the seed dataset
The companion repo for this article ships a runnable version of the DDL from Part 1 plus a seed dataset that covers all eight core LTM tables (plus the document chunks and deletion_events tables) across three example tenants. The seed follows a SaaS research-assistant scenario: each tenant has its own users, agents, ingested document collection, learned workflows, and toolbox configuration. The notebook walks through tenant provisioning (creating the per-tenant guideline and toolbox defaults), one write per type, the hybrid retrieval query against entity memory, the supersession of a fact after a paper is updated, the right-to-forget cascade for one user inside one tenant, and the tenant-termination cascade for an entire tenant.
If you only have time to try one thing from this article, clone the repo and run the seed. The schema and dataset are the foundation we’ll build on in future posts.