From Prompt to Persistence (Part 1): Designing Multi-Tenant Agent Memory Schemas for SaaS

A typed, governed memory architecture for agents that serve many organizations at once

Companion notebook: https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/8c9ed028b1bea545f2cbefe057470a0294bf29b7/notebooks/multitenant_schema_walkthrough.ipynb

---

Key Takeaways

• Multi-tenant SaaS changes agent memory design. Every memory record needs a tenant boundary so one customer’s agents, users, facts, and deletion requests never affect another customer.
• Tenant isolation should be enforced by the database, not just application code. The article argues for tenant_id on every durable memory row, protected with row-level security so queries cannot accidentally leak across tenants.
• Agent memory should be typed, not stored in one generic table. The proposed design separates guidelines, persona memory, entity facts, conversations, summaries, workflows, toolbox settings, and knowledge-base content because each has different access patterns and lifecycles.
• Provenance and lifecycle management are essential. Durable memory needs source tracking, versioning, supersession, expiration, and hard-delete flows so teams can audit what was remembered and prove that deletion requests were honored.

---

For many, the agent-memory conversation has focused on companies building agents that automate their own internal work. One organization, one set of users, one shared corpus, one access boundary that the agent never has to think about. That’s a useful starting point, and there is plenty of foundational work (the Oracle AI Agent Memory SDK, the cognitive-architecture papers, the Oracle AI Developer Hub) that all do the job for that shape of system.

SaaS agents serve many organizations from the same code, the same database, the same memory layer. Acme’s users can’t see Globex’s data, ever. Acme’s deletion request can’t accidentally erase Globex’s facts. Acme’s agents can coordinate with each other but not with Globex’s agents. The memory layer has to enforce those boundaries structurally. The application can’t be trusted to remember to filter on its own.

This article is about building that kind of system. We’ll review the core memory types, how we think about memory taxonomy aligned to CoALA, and the Oracle AI Agent Memory framing underneath it. Then we add the one structural primitive that multi-tenant SaaS can’t do without: tenancy as a first-class scope on every record, enforced by row-level security rather than by convention.

---

Why multi-tenant changes the schema

A single-org agent gets to be relaxed about the things that matter most in production. There’s one access boundary, so retrieval queries don’t have to filter by tenant. One set of policies, so the policy table stays small. One deletion authority (the user), so the right-to-forget flow is a DELETE FROM ... WHERE user_id = ? statement and a sweep of any derived artifacts. Things stay straightforward and simple because there’s nothing forcing them to be complicated.

SaaS doesn’t get that. Every row has to know which tenant it belongs to. Every query has to filter by tenant before doing anything else, including before ranking by vector similarity. Every deletion has to cascade across the tenant’s data without touching anyone else’s. Every backup has to be partitionable by tenant. Per-tenant key rotation is a hard requirement for encryption at rest. And the things that make the system feel alive (preferences, learned facts, summarized prior tasks) have to accumulate per-tenant without bleeding across the boundary the customer is paying for.

Adding tenancy shouldn’t be an afterthought, and bolting it on later is a mistake you don’t want to make. Tenancy is the kind of cross-cutting concern that fights you every time you try to retrofit it. The retrieval queries that worked fine in single-tenant mode all need to be rewritten. The deletion code that swept one table now has to walk every derived projection. The vector index that ranked across everything is now ranking across data the user shouldn’t have seen. The fix is structural: tenancy lives on every record from the start, enforced by the database, surfaced through the same APIs no matter which tenant is calling.

That structural commitment is what this article designs around. The taxonomy, the schemas, the lifecycles, the manager, the storage choice. Every section assumes the answer to “is this multi-tenant?” is yes.

---

The full memory taxonomy

Two helpful references ground this taxonomy: my colleague Richmond Alake’s Comparing File Systems and Databases for Effective AI Agent Memory Management, and my previous article From RAG to Memory Systems: Building Stateful AI Architecture. Both map onto the four-type cognitive architecture from CoALA (Cognitive Architectures for Language Agents, Sumers et al., 2024). The taxonomy below adds implementation-grain subdivisions and two top-level concerns that pure CoALA doesn’t model: shared memory across agents, and coordination between them. Both matter more in multi-tenant SaaS than they do in single-agent systems.

Here’s the full taxonomy this article works against:

Short-Term Memory (STM)
├── Working Memory
│   ├── LLM Context Window     : tokens passed to the model this turn
│   └── Session Memory         : in-process scratchpad for the current run
└── Semantic Cache             : vector index over recent thread/user history

Long-Term Memory (LTM)
├── Procedural
│   ├── Workflow               : learned multi-step procedures
│   ├── Toolbox                : tool definitions and calling conventions
│   └── Guideline              : authored rules (CLAUDE.md-style, policy, guardrails)
├── Episodic
│   ├── Conversations          : raw transcripts, append-only
│   └── Summarizations         : distilled completed-task summaries
└── Semantic
    ├── Knowledge Base         : authored / ingested reference content
    ├── Entity Memory          : facts about people, organizations, things
    └── Persona Memory         : user/agent preferences and stable traits

Cross-cutting
├── Shared Memory              : LTM with multi-agent read/write semantics
└── Coordination               : cross-agent messaging, handoffs, event streams

Eleven leaf types plus two cross-cutting concerns. Each leaf has its own access pattern, its own lifecycle, its own write path, and (for the LTM types) its own table in the schema. The cross-cutting concerns are properties of how LTM is read and written rather than new tables, but they get dedicated sections because they’re where multi-agent and multi-tenant systems pull away from single-agent ones.

Cross-reference: how the names line up

Naming conventions still vary across vendors and implementations, but the usage patterns themselves are becoming standard (no matter what you call them). This article expands upon Richmond’s blog post naming as our primary vocabulary (with terms like Policy/Guideline to bridge any differences). The table below shows how our names map to the CoALA academic categories and the Oracle Agent Memory SDK record types. The main difference is multi-versus-single tenant, and the SDK models single-tenant scoping, and we extend it with tenancy:

Our name	CoALA category	Richmond’s Post	Oracle SDK record
LLM Context Window	Working	Working Memory: Context Window	(in-context, not persisted)
Session Memory	Working	Working Memory: Session Memory	(ephemeral, app-managed)
Semantic Cache	Working (adjunct)	Semantic Cache	(no direct record; derived)
Workflow	Procedural	Procedural: Workflow	(extension; no direct record)
Toolbox	Procedural	Procedural: Toolbox	(extension; no direct record)
Guideline	Procedural	Procedural (CLAUDE.md, rules)	GuidelineRecord
Conversations	Episodic	Episodic: Conversations	MessageRecord in ThreadRecord
Summarizations	Episodic	Episodic: Summarizations	MemoryRecord
Knowledge Base	Semantic	Semantic: Knowledge Base	(extension; no direct record)
Entity Memory	Semantic	Semantic: Entity	FactRecord
Persona Memory	Semantic	Semantic: Persona	PreferenceRecord
Shared Memory	(extension)	Shared Memory	(scope pattern)
Coordination	(extension)	Coordination	(not modeled in this article)

CoALA’s four-type spine (working, episodic, semantic, procedural) is the academic backbone. Richmond’s post adds implementation-grain subtypes underneath each, and we add tenancy plus the SaaS-specific Shared/Coordination treatment on top. The Oracle Agent Memory SDK currently exposes the types that map cleanly onto its ScopedRecord shape (Guideline, Persona, Entity, Conversations, Summarizations). Workflow, Toolbox, and Knowledge Base are extensions this article defines because they’re critical for SaaS workloads even though the SDK doesn’t yet model them directly.

---

Scope: four dimensions, one tenant boundary

In a multi-tenant SaaS system, every long-term memory record in the schema carries four scope columns. Three of them match the Oracle AI Memory SDK’s ScopedRecord exactly. The fourth, tenant_id, extends the ScopedRecord to add tenancy.

Column	Required?	NULL means
tenant_id	NOT NULL	(always set; tenant is the structural access boundary)
user_id	nullable	applies to all users in tenant
agent_id	nullable	shared across agents in tenant
thread_id	nullable	not thread-scoped, durable across threads

The Oracle SDK’s Scope class is (user_id, agent_id, thread_id), where thread_id is essentially your session identifier. Production-grade multi-tenant SaaS adds tenant_id as the first dimension, enforced at a different level of the stack: row-level security ties it to a session context the application sets once per request, so individual queries can’t forget to filter on it. The other three dimensions are filter predicates the application supplies normally, the same way the Oracle Agent Memory SDK already exposes them.

Why tenant_id is structurally different from the other three

user_id, agent_id, and thread_id are all nullable on purpose. A guideline can apply to every user in a tenant (user_id IS NULL). A persona (user or agent preference) can apply across every agent that serves a user (agent_id IS NULL). An entity record (known fact) can be durable across every thread the user opens (thread_id IS NULL). Null on those columns means “broader scope,” and the retrieval predicate reads (user_id IS NULL OR user_id = :u) to inherit upward through the hierarchy.

tenant_id works differently. On a scoped LTM row it’s always populated. There’s no concept of “applies to all tenants,” because cross-tenant rows are exactly the kind of access-boundary leak the SaaS architecture exists to prevent. Even global guidelines that the SaaS provider authors (default content policy, default safety rules) are typically replicated per-tenant at provisioning time rather than stored once in “global” scope, because that gives each tenant the ability to override defaults and gives the platform a clean audit boundary.

So the conceptual model is: tenant is the access boundary, and the other three dimensions are the within-tenant scope cube.

Row-level security as the enforcement layer

In Oracle AI Database, tenant isolation lives in the data plane. The application sets a session context (tenant_id, user_id, agent_id, thread_id) once per request, and every subsequent query against a memory table has the tenant predicate appended by the database. A developer who forgets to filter by tenant can’t write a leaking query, because the database enforces the tenant predicate transparently.

CREATE OR REPLACE FUNCTION memory_tenant_policy(
  schema_in IN VARCHAR2, table_in IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN q'[
    tenant_id = SYS_CONTEXT('memory_ctx','tenant_id')
  ]';
END;
/

BEGIN
FOR rec IN (SELECT table_name FROM user_tables
               WHERE table_name LIKE '%_MEMORY'
                  OR table_name LIKE 'KNOWLEDGE_BASE%')   -- the KB tables carry tenant_id too
  LOOP
    DBMS_RLS.ADD_POLICY(
      object_schema   => USER,
      object_name     => rec.table_name,
      policy_name     => rec.table_name || '_tenant_pol',
      policy_function => 'memory_tenant_policy',
      statement_types => 'SELECT, INSERT, UPDATE, DELETE',
      update_check    => TRUE);   -- required for the INSERT predicate to be enforced
  END LOOP;
END;
/

Note the INSERT in the statement types. Tenant isolation isn’t just a read concern. A careless application that inserts a row with the wrong tenant_id is just as bad as one that reads across tenants. RLS on INSERT enforces “you can only write rows whose tenant_id matches the session context,” which closes the gap. That INSERT half only works with update_check => TRUE on the policy, the VPD equivalent of a SQL WITH CHECK OPTION. Without it, the predicate filters reads only, and ADD_POLICY rejects INSERT outright. The session context itself gets set once per request, typically by the application’s authentication middleware calling DBMS_SESSION.SET_CONTEXT('memory_ctx', 'tenant_id', :authenticated_tenant_id) after validating the inbound token.

The other three dimensions (user_id, agent_id, thread_id) are still access boundaries. They’re structural isolation layers enforced at the storage and routing layers. The model never touches that enforcement. But unlike tenant_id, they’re enforced by the application rather than by RLS, because they need flexibility. A user reading their own persona memory passes their own user_id; an agent reading shared persona memory passes user_id = NULL. RLS would interfere with that flexibility. Tenant is different precisely because there’s no legitimate reason for the application to ever supply a tenant_id other than the one the request came from.

Working scope predicate

Within a tenant, the canonical retrieval predicate on any scoped LTM record reads like this:

WHERE tenant_id = SYS_CONTEXT('memory_ctx','tenant_id')   -- enforced by RLS, shown for clarity
  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)
  AND deleted_at IS NULL
  AND (valid_until IS NULL OR valid_until > SYSTIMESTAMP)

That block appears on every retrieval query against every typed LTM table. Once it’s a habit, the MemoryManager wraps it so the application never writes it by hand. We’ll get to the manager later.

---

The canonical LTM schema

Time for the actual DDL. Eight primary tables for each memory type (plus a document chunks helper table and an audit log), all sharing the same scope columns and the same lifecycle columns, each with type-specific payload columns where the access pattern demands them. The shape is intentionally repetitive across tables (same scope, same provenance, same versioning) because every variation across tables is a place a future bug can hide.

Two recurring design choices show up in every table:

Scope columns are denormalized onto every record. Joining to a separate scope table on every read kills the query plan. Putting tenant_id, user_id, agent_id, thread_id on the row itself means the optimizer can use them in any combination, including as the leading columns of composite indexes.

Provenance is required on every durable type. Every row carries written_by (who wrote this) and if derived from a conversation, the source_event_id (which event caused the write). The first is for audit. The second is the lineage column that makes right-to-forget possible without a full table scan. written_by is non-nullable on all durable types, and source_event_id is non-nullable on all derived memory types. We’ll see why that matters in the lifecycle section.

Guideline/Policy memory (Procedural)

Authored rules and policies the agent has to follow. Guidelines are policies: they set the behavioral constraints, compliance requirements, and guardrails the agent has to respect, things like refund thresholds, content rules, and approval gates. Tenant-scoped or user-scoped and never written by the LLM. Retrieved by exact match, never similarity.

CREATE TABLE guideline_memory (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  thread_id       VARCHAR2(64),              -- session identifier
  guideline_key   VARCHAR2(128) NOT NULL,    -- e.g. 'refund_threshold', 'tone'
  guideline_value JSON          NOT NULL,
  version         NUMBER(10)    NOT NULL,
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  written_by      VARCHAR2(64)  NOT NULL,    -- human author or service principal
  source_event_id VARCHAR2(64),              -- release ticket, change request, etc.
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

CREATE UNIQUE INDEX idx_guideline_active
  ON guideline_memory (
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN tenant_id     END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN user_id       END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN agent_id      END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN thread_id     END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN guideline_key END
  );

The unique index does the governance work. A guideline like refund_threshold can have only one currently-in-force value per (tenant, user, agent, thread) tuple at a time. Updates aren’t UPDATEs; they’re INSERTs that supersede the previous row by setting valid_until on it. That keeps history queryable for audit without an audit table.

Usage pattern in the agent loop: at the start of every turn, the agent’s retrieval phase pulls every active guideline that applies to the current scope (tenant + user + agent + thread, with inheritance upward through nulls) and stuffs them into the static prefix of the prompt. No vector search. No top-k. Full enumeration, every turn, because guidelines must apply every time.

Persona memory (Semantic)

This is where the agent remembers how a user likes to work. It stores preferences like response format, verbosity level, communication style, and agent personality settings. Usually user-scoped, sometimes tenant-scoped. Some preferences are set explicitly by the user; others get inferred from their behavior and tagged with a confidence score so the agent can act on strong signals without overstepping on weak ones. Retrieved as a full set per user per turn, just like guidelines.

CREATE TABLE persona_memory (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  thread_id       VARCHAR2(64),              -- session identifier
  persona_key     VARCHAR2(64)  NOT NULL,    -- e.g. 'response_format', 'verbosity'
  persona_value   JSON,                      -- nullable: erased on right-to-forget
  confidence      NUMBER(3,2),               -- NULL for explicit; 0-1 for inferred
  written_by      VARCHAR2(64)  NOT NULL,    -- 'user' | 'agent:<id>' | 'admin'
  source_event_id VARCHAR2(64),
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

CREATE UNIQUE INDEX idx_persona_active
  ON persona_memory (
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN tenant_id   END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN user_id     END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN agent_id    END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN thread_id   END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN persona_key END
  );

The shape is nearly identical to guideline memory because the access pattern is nearly identical. The semantic difference is who writes it (users vs administrators) and how it’s allowed to be inferred (with confidence; never above some threshold without an explicit user signal). The schema treats them as two tables anyway because the write paths, retention defaults, and audit requirements differ, and collapsing them onto one table means losing those distinctions to a discriminator column.

Usage pattern: same as guideline memory. Read the full active set for the current scope at the start of every turn, put it in the static prefix, keep the prompt cache happy. The semantic similarity on persona records is useful during asynchronous sweeps: when the agent infers a new preference, it can check for existing preferences by similarity to avoid writing duplicates or contradictions.

Entity memory (Semantic)

Durable facts about entities the agent has encountered. Customers, accounts, products, people, projects. Subject-predicate-content, with a vector for semantic retrieval, structured metadata for exact filtering, and confidence for retrieval scoring. This is where compounding advantage lives in agent systems, and where the most expensive failure modes also live.

CREATE TABLE entity_memory (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  thread_id       VARCHAR2(64),              -- session identifier
  subject         VARCHAR2(256) NOT NULL,    -- canonical entity reference
  predicate       VARCHAR2(64)  NOT NULL,    -- kind of fact
  content         CLOB,                      -- the fact text (nullable: erased on right-to-forget)
  content_hash    VARCHAR2(64)  NOT NULL,    -- SHA-256 for dedup
  content_json    JSON,                      -- structured metadata
  embedding       VECTOR(1024, FLOAT32),
  confidence      NUMBER(3,2)   NOT NULL,
  written_by      VARCHAR2(64)  NOT NULL,
  source_event_id VARCHAR2(64)  NOT NULL,    -- non-nullable; entity facts must have a source
  version         NUMBER(10)    NOT NULL,
  superseded_by   VARCHAR2(64),
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

CREATE INDEX idx_entity_scope_active
  ON entity_memory (tenant_id, user_id, agent_id, thread_id, deleted_at, valid_until);

CREATE INDEX idx_entity_dedup
  ON entity_memory (tenant_id, user_id, agent_id, content_hash);

CREATE INDEX idx_entity_subject
  ON entity_memory (tenant_id, subject);

CREATE INDEX idx_entity_content_json
  ON entity_memory (JSON_VALUE(content_json, '$.entity_type'));

CREATE VECTOR INDEX idx_entity_embedding
  ON entity_memory (embedding)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

-- Oracle Text index for the lexical half of hybrid retrieval over entities.
CREATE INDEX idx_entity_text ON entity_memory (content)
  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC (ON COMMIT)');

Five columns deserve explicit attention because they’re the ones that are easy to get wrong.

source_event_id is the lineage pointer back to conversation_memory.event_id. Non-nullable, on purpose. A fact without a source can’t be audited, can’t be superseded correctly, and can’t be honored against a deletion request. We’ll use this column in the right-to-forget cascade in the lifecycle section.

written_by separates promotion from authorship. A fact promoted by a background extraction job (‘promotion_job‘) carries a different signal than one written by a user explicitly ('user:jane@acme.com') or by an agent acting on the user’s behalf ('agent:research_asst'). Six weeks into debugging a bad promotion, this is the column that will be very useful.

confidence lives on the row, separate from the embedding score. Embedding distance answers “is this row close to the query.” Confidence answers “did we believe this row when we wrote it.” Both matter at retrieval time. A high-similarity match with confidence 0.4 is a weaker signal than a moderate-similarity match with confidence 0.95, and the application has to be able to see both.

version and superseded_by form the supersession chain. Facts evolve. When a fact gets updated, the old row stays in the table with valid_until stamped and superseded_by pointing at the new row. The new row enters at version = old.version + 1 with the same (tenant, user, agent, subject, predicate) keys. The table is append-only in spirit even though it allows in-place UPDATEs for the supersession bookkeeping.

The dedup index is keyed on (tenant_id, user_id, agent_id, content_hash). Two important properties fall out. Same fact arriving twice in the same scope dedups to one row. Same fact in two different scopes (different user, different agent) writes two rows, because they live in different access boundaries even though the content is identical.

Usage pattern: hybrid retrieval. Lexical search for exact terms (entity references, identifiers), vector search for paraphrase, both filtered by scope before ranking. The retrieval predicate has the full scope block at the top, then something like ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE) FETCH FIRST :k ROWS ONLY at the bottom. The hybrid pipeline is a topic that deserves its own in-depth exploration; for now, every retrieval pulls a small ranked set.

Conversation memory (Episodic: Conversations)

The append-only event stream: your flight recorder, or what I typically think of as trace memory. Every user message, every model response, every tool call, every tool result, in order, with a run_id/thread_id and turn_index. This is the source from which entity and summarization memory get distilled, and the table that grows fastest in any agent system.

CREATE TABLE conversation_memory (
  event_id        VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  thread_id       VARCHAR2(64)  NOT NULL,    -- required; every event belongs to a thread
  run_id          VARCHAR2(64)  NOT NULL,
  turn_index      NUMBER(10)    NOT NULL,
  event_type      VARCHAR2(32)  NOT NULL,    -- 'user_msg' | 'tool_call' | 'tool_result' | 'model_msg'
  role            VARCHAR2(32),              -- for chat-style events
  payload         JSON          NOT NULL,
  token_cost      NUMBER(10),
  latency_ms      NUMBER(10),
  retention_class VARCHAR2(16)  NOT NULL DEFAULT 'short',
  created_at      TIMESTAMP     NOT NULL
) PARTITION BY RANGE (created_at)
  INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
  ( PARTITION p0 VALUES LESS THAN (TIMESTAMP '2026-01-01 00:00:00') );

CREATE INDEX idx_conv_thread_turn ON conversation_memory (tenant_id, thread_id, turn_index);
CREATE INDEX idx_conv_run         ON conversation_memory (tenant_id, run_id, turn_index);
CREATE INDEX idx_conv_user_time   ON conversation_memory (tenant_id, user_id, created_at);

Conversation memory is the only table in this schema with interval partitioning, because conversation memory is the only table where volume forces it. Partitioning by day makes the retention sweep (“drop the partitions older than N days for retention_class = ‘short'”) a partition drop rather than a row-by-row delete. In a multi-tenant system serving hundreds of thousands of users, that distinction is the difference between a five-minute nightly job and a job that runs for hours. Context for a given thread is assembled per turn rather than by restoring the full history. You rarely need to search across multiple past days for a single thread, which is why day-based partitioning works well; the active thread’s events are almost always in today’s partition.

thread_id, your session identifier, is non-nullable here even though it’s nullable on the other LTM tables. Every conversation event belongs to a thread (session) by definition; “thread-less” conversation events don’t exist.

No vector column by default. Conversation memory is the source from which other memory gets distilled. It isn’t meant to be searched semantically itself. If a regulator asks “did this user ever say X,” that’s a forensic query that runs on a derived projection rather than on the canonical conversation rows. Derived projections are beyond the scope of this article, but they are something that requires deeper exploration.

Usage pattern: read by (tenant_id, thread_id, turn_index) to assemble the last N turns into the volatile tail of the prompt. Read by (tenant_id, run_id) to replay a specific run for debugging or audit. Read by (tenant_id, user_id, created_at) to feed the per-user semantic cache (we’ll get to that in the STM section).

Summarization memory (Episodic: Summarizations)

Distilled summaries of completed work. Title, summary, key steps, outcome, with a vector over the summary text and structured metadata for filtering. One row for each completed task rather than one per turn.

CREATE TABLE summarization_memory (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  thread_id       VARCHAR2(64),               -- nullable; some summaries span threads (a mapping table could tie back to multiple source threads for provenance)
  task_type       VARCHAR2(64)  NOT NULL,
  title           VARCHAR2(256) NOT NULL,
  summary         CLOB,                      -- nullable: erased on right-to-forget
  key_steps       JSON,
  outcome         VARCHAR2(64),
  artifacts       JSON,
  embedding       VECTOR(1024, FLOAT32),
  confidence      NUMBER(3,2)   NOT NULL,
  written_by      VARCHAR2(64)  NOT NULL,
  source_event_id VARCHAR2(64)  NOT NULL,     -- typically the run_id of the summarized work
  version         NUMBER(10)    NOT NULL,
  superseded_by   VARCHAR2(64),
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  completed_at    TIMESTAMP     NOT NULL,
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

CREATE INDEX idx_summ_scope_task
  ON summarization_memory (tenant_id, user_id, agent_id, task_type, completed_at);

CREATE VECTOR INDEX idx_summ_embedding
  ON summarization_memory (embedding)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

-- Oracle Text index for the lexical half of hybrid retrieval over summaries.
CREATE INDEX idx_summ_text ON summarization_memory (summary)
  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC (ON COMMIT)');

Structurally close to entity memory, with two real differences. source_event_id typically points at the run_id rather than at a single conversation event (an episode summarizes a whole completed task). And task_type is a first-class filter column rather than a JSON path, because almost every summarization retrieval is “give me prior episodes of this kind of task,” and indexing the JSON predicate on every read is wasted work.

Usage pattern: hybrid retrieval over the summary text, often pre-filtered by task_type. Episodic retrieval is what lets the agent recognize “we’ve done this before” and pull the shape of the prior solution rather than re-deriving it. Read this table when starting a new task; write to it when finishing one.

Workflow memory (Procedural: Workflow)

Learned multi-step procedures. The schema is lighter than entity or summarization because versioning, composition, and success-tracking each deserve a full deep dive. For the purpose of this post, the table establishes the general shape.

CREATE TABLE workflow_memory (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  workflow_name   VARCHAR2(128) NOT NULL,
  description     CLOB,
  steps           JSON          NOT NULL,     -- ordered list of steps
  preconditions   JSON,
  expected_outcome JSON,
  success_count   NUMBER(10)    NOT NULL DEFAULT 0,
  failure_count   NUMBER(10)    NOT NULL DEFAULT 0,
  embedding       VECTOR(1024, FLOAT32),      -- over description, for similarity matching
  confidence      NUMBER(3,2)   NOT NULL,
  written_by      VARCHAR2(64)  NOT NULL,
  source_event_id VARCHAR2(64),
  version         NUMBER(10)    NOT NULL,
  superseded_by   VARCHAR2(64),
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

CREATE INDEX idx_workflow_scope_name
  ON workflow_memory (tenant_id, user_id, agent_id, workflow_name);

CREATE VECTOR INDEX idx_workflow_embedding
  ON workflow_memory (embedding)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

success_count and failure_count are operational columns the Memory Manager increments whenever a workflow is invoked. They’re how workflow gains or loses confidence over time. Workflows with a high failure rate get retired or rewritten; workflows with a high success rate get promoted to broader scope (from agent-scoped to tenant-scoped, for example).

Usage pattern: similarity match on the user’s request against the workflow description embedding, filtered by scope. If a high-confidence workflow exists for the task, the agent uses it; if not, the agent reasons from scratch and (if the run succeeds) writes the trace back as a new workflow candidate.

Toolbox memory (Procedural: Toolbox)

Tool definitions, calling conventions, access policies, and use heuristics. Most agent frameworks already provide built-in tool handling: Claude Code has native MCP server integrations, and frameworks like LangChain and CrewAI let you specify which tools or servers are available per agent. This table is where you persist that configuration so it survives across sessions and stays governed per tenant. Like workflow, this category deserves its own deep treatment; the schema here establishes the shape.

CREATE TABLE toolbox_memory (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),
  agent_id        VARCHAR2(64),
  tool_name       VARCHAR2(128) NOT NULL,
  tool_schema     JSON          NOT NULL,     -- JSON Schema of inputs/outputs
  tool_handler    VARCHAR2(256),              -- reference to executable
  access_policy   JSON,                       -- who can call this tool under what conditions
  usage_notes     CLOB,                       -- prose guidance for the agent
  embedding       VECTOR(1024, FLOAT32),      -- over usage_notes + description
  version         NUMBER(10)    NOT NULL,
  superseded_by   VARCHAR2(64),
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  written_by      VARCHAR2(64)  NOT NULL,
  source_event_id VARCHAR2(64),
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

CREATE UNIQUE INDEX idx_toolbox_active
  ON toolbox_memory (
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN tenant_id END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN user_id   END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN agent_id  END,
    CASE WHEN valid_until IS NULL AND deleted_at IS NULL THEN tool_name END
  );

Usage pattern: at agent startup (or before each tool-bearing turn), the manager loads the active toolbox for the current scope and exposes it as the agent’s tool surface. Adding a tool is a write; deprecating one is a supersession with valid_until stamped. The access_policy JSON column expresses tenant-level constraints (“agents in this tenant cannot call external_api_call between 2 AM and 4 AM UTC”), which the manager evaluates before each invocation.

Knowledge base memory (Semantic: Knowledge Base)

Authored or ingested reference content, brought in out-of-band rather than learned from interaction. Documentation, papers, policies, customer manuals, internal wikis. This is the table that grows when somebody uploads content. What the agent observes on its own lands elsewhere.

Two tables, because the access patterns differ:

-- The source document: one row per ingested document
CREATE TABLE knowledge_base_document (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  user_id         VARCHAR2(64),               -- nullable; null = tenant-wide
  agent_id        VARCHAR2(64),
  collection      VARCHAR2(128) NOT NULL,    -- logical grouping ('legal-policies', 'product-docs')
  title           VARCHAR2(512) NOT NULL,
  source_uri      VARCHAR2(1024),
  source_type     VARCHAR2(64)  NOT NULL,    -- 'pdf' | 'html' | 'markdown' | 'doc' | 'url'
  metadata        JSON,
  ingested_at     TIMESTAMP     NOT NULL,
  ingested_by     VARCHAR2(64)  NOT NULL,
  version         NUMBER(10)    NOT NULL,
  superseded_by   VARCHAR2(64),
  valid_from      TIMESTAMP     NOT NULL,
  valid_until     TIMESTAMP,
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP
);

-- The chunks: many rows per document, each with its own embedding
CREATE TABLE knowledge_base_chunk (
  id              VARCHAR2(64)  PRIMARY KEY,
  tenant_id       VARCHAR2(64)  NOT NULL,
  document_id     VARCHAR2(64)  NOT NULL,    -- FK to knowledge_base_document
  chunk_index     NUMBER(10)    NOT NULL,
  content         CLOB          NOT NULL,
  embedding       VECTOR(1024, FLOAT32),
  metadata        JSON,                       -- chunk-level metadata (page, section, etc.)
  created_at      TIMESTAMP     NOT NULL,
  deleted_at      TIMESTAMP,
  CONSTRAINT fk_kb_chunk_doc FOREIGN KEY (document_id) REFERENCES knowledge_base_document (id)
);

CREATE UNIQUE INDEX idx_kb_chunk_doc_idx
  ON knowledge_base_chunk (tenant_id, document_id, chunk_index);

CREATE INDEX idx_kb_doc_collection
  ON knowledge_base_document (tenant_id, user_id, agent_id, collection, valid_until);

CREATE VECTOR INDEX idx_kb_chunk_embedding
  ON knowledge_base_chunk (embedding)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

Knowledge base differs from entity memory in three important ways. Its documents are uploaded or ingested through a separate write path, where entity facts get extracted from interaction. An authored document comes in high-confidence by default, where an extracted fact stays provisional. And its provenance is a document with its own URI, version, and ingestion timestamp, where an entity fact traces back to a conversation event. Trying to share a table with entity memory loses all three distinctions to a discriminator column that has to be remembered everywhere. knowledge_base_document holds a pointer (source_uri) and metadata rather than the document body. The searchable text lives in the chunks, and the original file stays in object storage. The database keeps the derived, retrievable form and a way to find the source.

Usage pattern: query rewrites the user’s question into a vector, then runs a similarity search against knowledge_base_chunk.embedding filtered by (tenant_id, collection) and any user/agent scope predicates. The retrieved chunks come back with document_id so the application can resurface “this is from page 14 of the customer’s compliance manual, version 2026-03.” That citation chain is what makes KB-grounded answers defensible.

The collection column is the closest thing to a per-department or per-product grouping the schema directly supports. Within tenant Acme, the agent can be configured to read from collection = 'legal-policies' for compliance questions and collection = 'product-docs' for support questions, without those two corpora interfering with each other. If a tenant needs deeper sub-scoping than collection provides (per-department, per-region), it gets layered on as metadata predicates rather than as new scope columns. Scope is structural and there’s a high bar to adding a fifth dimension.

A pattern across all eight tables

Every LTM table in this schema follows the same template:

• Identity columns: id, tenant_id, plus the scope cube (user_id, agent_id, thread_id where applicable)
• Payload columns specific to the type (guideline_value, persona_value, subject/predicate/content, summary, etc.)
• Vector column where semantic retrieval applies; absent where it doesn’t
• Provenance columns: written_by, source_event_id
• Versioning columns: version, superseded_by
• Temporal columns: valid_from, valid_until, created_at, deleted_at

That uniformity is what lets a single MemoryManager interface serve all eight types, and what lets the lifecycle operations (insert, supersede, expire, hard-delete) work identically across them.

---

Lifecycle: insert, supersede, expire, prove-it’s-gone

Every durable memory record supports the same four operations. In my experience, most schemas implement the first one and then spend the next several months (or years) discovering why the others matter, especially in multi-tenant SaaS where compliance and contractual deletion obligations make the fourth one a hard requirement.

Insert

A new row enters at version = 1, with valid_from = SYSTIMESTAMP, valid_until = NULL, deleted_at = NULL, and a source_event_id that points back to the conversation event (or document, or workflow run) that caused the write. Provenance is non-negotiable on the durable types; the manager refuses to write a fact, summary, or workflow without it.

INSERT INTO entity_memory (
  id, tenant_id, user_id, agent_id, thread_id,
  subject, predicate, content, content_hash, embedding,
  confidence, written_by, source_event_id, version,
  valid_from, created_at
) VALUES (
  :id, :tenant_id, :user_id, NULL, NULL,
  'customer:acme-corp', 'infrastructure',
  'Production database is in us-east-1, replicas in us-west-2.',
  :sha256, :embedding,
  0.95, 'agent:support_asst', :conv_event_id, 1,
  SYSTIMESTAMP, SYSTIMESTAMP
);

Supersede

A versioned update. INSERT a new row at version = old.version + 1 with the same identity columns (tenant, scope, subject, predicate). In the same transaction, set the old row’s valid_until = SYSTIMESTAMP and superseded_by = new.id. The old row stays in the table for replay and audit, but default retrieval filters valid_until IS NULL exclude it.

BEGIN
  INSERT INTO entity_memory (id, tenant_id, user_id, agent_id, thread_id,
    subject, predicate, content, content_hash, embedding, confidence,
    written_by, source_event_id, version, valid_from, created_at)
  VALUES (:new_id, :tenant_id, :user_id, NULL, NULL,
    'customer:acme-corp', 'infrastructure',
    'Production database is in eu-west-1, replicas in eu-central-1. Migrated 2026-04.',
    :sha256_v2, :embedding_v2, 0.97,
    'agent:support_asst', :new_conv_event_id, 2, SYSTIMESTAMP, SYSTIMESTAMP);

  UPDATE entity_memory
     SET valid_until = SYSTIMESTAMP, superseded_by = :new_id
   WHERE id = :old_id AND valid_until IS NULL;
COMMIT;
END;

Without supersession, an update to a fact is a destructive operation: the old content is gone, the old embedding is gone, and any downstream system that referenced the old id now points to a row whose content has silently changed. With it, history is queryable and downstream references resolve to the version that was current when they were captured.

Expire

A row reaches its valid_until and stops being part of default retrieval. No DELETE happens. The row is still readable for audit, still useful for “what did the system believe at time T.” A nightly job is welcome to physically delete expired-and-old rows for storage hygiene, but expiration itself is a query-time filter, not a destructive operation. One note on timestamps: standardize on UTC. Oracle has no single “UTC now” builtin, but SYS_EXTRACT_UTC(SYSTIMESTAMP) always returns UTC no matter the session time zone. Write that into the plain TIMESTAMP columns and compare expiry against the same expression, and the result stays correct wherever the session runs. The trap to avoid is writing or comparing against plain SYSTIMESTAMP: when the session time zone differs from the database’s, a row superseded seconds ago can read as still active.

The default valid_until policy varies by type and reflects opinionated guesses that production teams should adjust:

Memory type	Default valid_until	Rationale
Guideline	NULL (until superseded)	Rules are valid until explicitly replaced
Persona	NULL (until superseded)	Preferences don’t expire on a clock
Entity (current-state)	90 days	“Current production region” needs re-confirmation
Entity (historical)	NULL (until superseded)	“Founded in 1987” doesn’t decay
Summarization	365 days	Prior tasks stay useful for about a year
Workflow	NULL (until success rate drops)	Workflows expire based on outcome stats, not time
Toolbox	NULL (until superseded)	Tools are valid until deprecated
Knowledge base	NULL (until document superseded)	KB chunks live and die with their parent document
Conversation (short retention)	14 days	Replay window for debugging
Conversation (standard retention)	90 days	Replay + extraction backfill window
Conversation (audit retention)	7 years	Regulatory; partition separately, encrypt separately

Hard-delete and the right-to-forget cascade

This is the operation that matters most in SaaS. A user invokes their right-to-forget, or a tenant terminates their contract, or a regulator orders deletion of derived artifacts. The user’s own rows are the easy half. The hard half is every fact, summary, and derived artifact downstream of that user’s interactions. Without provenance, that query can’t be written. With source_event_id on every durable row, it can.

The lineage chain runs: a row’s source_event_id points to a row in conversation_memory.event_id. The conversation row’s (tenant_id, user_id) tells you which user it belonged to. Joining the two finds every derived row whose lineage traces back to that user, even if the derived row itself is scoped to the tenant rather than the user.

DECLARE
  v_now TIMESTAMP := SYSTIMESTAMP;
BEGIN
  -- 1. Hard-delete user-scoped rows in every LTM table.
  UPDATE entity_memory
     SET content = NULL, content_hash = 'erased', embedding = NULL, deleted_at = v_now
   WHERE tenant_id = :tenant_id AND user_id = :user_id;

  UPDATE summarization_memory
     SET summary = NULL, embedding = NULL, deleted_at = v_now
   WHERE tenant_id = :tenant_id AND user_id = :user_id;

  UPDATE persona_memory
     SET persona_value = NULL, deleted_at = v_now
   WHERE tenant_id = :tenant_id AND user_id = :user_id;

  -- 2. Hard-delete tenant-scoped derived rows whose lineage points at the user.
  UPDATE entity_memory e
     SET e.content = NULL, e.content_hash = 'erased', e.embedding = NULL, e.deleted_at = v_now
   WHERE e.tenant_id = :tenant_id
     AND e.user_id IS NULL
     AND EXISTS (
       SELECT 1 FROM conversation_memory c
        WHERE c.event_id = e.source_event_id
          AND c.tenant_id = :tenant_id
          AND c.user_id   = :user_id);

  UPDATE summarization_memory s
     SET s.summary = NULL, s.embedding = NULL, s.deleted_at = v_now
   WHERE s.tenant_id = :tenant_id
     AND s.user_id IS NULL
     AND EXISTS (
       SELECT 1 FROM conversation_memory c
        WHERE c.event_id = s.source_event_id
          AND c.tenant_id = :tenant_id
          AND c.user_id   = :user_id);

  -- 3. Drop the conversation events themselves.
  DELETE FROM conversation_memory
   WHERE tenant_id = :tenant_id AND user_id = :user_id;

  -- 4. Audit-log the erasure.
  INSERT INTO deletion_events (tenant_id, user_id, scope, deleted_at, reason)
  VALUES (:tenant_id, :user_id, 'all', v_now, 'gdpr_erasure');

  COMMIT;
END;

That whole block is one transaction. Either every step lands or none of them do. Across a polyglot stack (relational over here, vectors over there, conversation events in object storage somewhere else), the same logical operation becomes a distributed-systems problem with a window during which deletion is partial. For a SaaS provider, “partial deletion under load” is the kind of phrase that ends up in a compliance finding. With everything in one engine inside one transaction boundary, deletion is atomic.

For tenant-level deletion (a customer terminates their contract), the cascade is simpler and broader: drop every row across every table where tenant_id = :tenant_id. Partitioning conversation_memory by day already keeps that operation fast for the high-volume table; the other seven tables get a standard DELETE with the tenant predicate. One transaction; the entire tenant footprint is gone or it’s not gone at all.

The EU AI Act’s high-risk obligations were originally scheduled for August 2026 and now look likely to be deferred to December 2027 under the Digital Omnibus revisions reached in May 2026. Either way, the regulatory direction is the same: “provably scoped” and “provably deleted” are about to be hard requirements rather than nice-to-haves. The provenance column is what makes them possible.

---

What’s coming up in Part 2

That’s the schema. Eight memory type tables with a document chunks table and an audit log, four scope dimensions, a tenant boundary the database enforces instead of trusting the application to remember, and a lifecycle that versions every change and can prove a deletion actually happened. It’s enough to stand up the durable layer and know that no query leaks across tenants and no right-to-forget request leaves a derived row behind.

What it isn’t yet is a running system. A schema doesn’t retrieve anything, doesn’t decide what’s worth remembering, and doesn’t compose a prompt. Part 2 picks up there: how short-term memory meets the durable layer, how agents inside the same tenant share what they learn, the Memory Manager that’s the only code allowed to touch these tables, and the single query that pulls every memory type in one round trip without crossing a tenant line. Next, we put it to work.