andersswanson.dev

Key Takeaways

• Database agent memory should be modeled as durable application data, not just as embeddings in a vector table.
• Oracle AI Database can combine vectors, relational columns, JSON metadata, Oracle Text, property graphs, and timestamps in one memory system.
• Hybrid retrieval is stronger than vector search alone because it can match meaning, exact identifiers, entities, relationships, recency, and importance.

If you build agent memory off a vector table, it’s probably not going to give you the full picture.

It may work for a demo, but falls apart when the system needs to remember a preference from last month, ignore a one-off session detail, distinguish two users, explain why a memory was retrieved, and avoid stuffing weak matches into a prompt.

In this article, we’ll explore implementing memory with Oracle AI Database: Store memory as rows, add semantic search, lexical search, JSON metadata, graph relationships, and lifecycle controls. This pattern moves from plain embeddings to solving memory as a systems problem.

1. Store Memories as Rows
2. Add Organizational Layers to memory
3. Memory Enrichment
4. Use Hybrid Retrieval
5. Related Memories (Memory Graph)
6. Recall for Prompt Assembly
7. FAQ
8. References

Store Memories as Rows: Database Agent Memory

We’ll start with one row per memory.

Each row can contain relevant information about the memory: plain text, scope, JSON metadata, tags, embedding vector, and timestamps. Each column gives additional control over the memory data.

create table agent_memory (
  id raw(16) primary key,
  user_id varchar2(200 char),
  agent_id varchar2(200 char),
  content clob not null,
  metadata json,
  embedding vector(384, float32), -- use sizing appropriate for your vector embedding tools
  created_at timestamp with time zone default systimestamp not null,
  updated_at timestamp with time zone default systimestamp not null,
  last_accessed_at timestamp with time zone default systimestamp not null,
  access_count number(19) default 0 not null
);

create table agent_memory (
  id raw(16) primary key,
  user_id varchar2(200 char),
  agent_id varchar2(200 char),
  content clob not null,
  metadata json,
  embedding vector(384, float32), -- use sizing appropriate for your vector embedding tools
  created_at timestamp with time zone default systimestamp not null,
  updated_at timestamp with time zone default systimestamp not null,
  last_accessed_at timestamp with time zone default systimestamp not null,
  access_count number(19) default 0 not null
);

Let’s take a look at this schema:

• Oracle AI Vector Search stores embeddings in the VECTOR data type and lets SQL query by semantic similarity instead of only matching keywords.  See Oracle’s overview of Oracle AI Vector Search and the VECTOR_DISTANCE documentation.
• The embedding column isn’t the whole memory model: user_id, agent_id, content, metadata are available as database filters. Oracle’s JSON documentation describes this mixed relational/JSON model in JSON Data and Oracle AI Database and Overview of Storing and Managing JSON Data.

This enables hybrid search: vectors find candidates; structured fields filter the answer to improve accuracy.

Add Organizational Layers to memory

Retrieval is easier when memory is scoped. For example, you could organize memory into different layers, ordered here from shorter to longer term memory:

This strategy can add priority to long term memory, apply weights to user or organization wide preferences. You can search conversation memory first, then session memory, then user memory, then organization memory. Each layer can use the same retrieval machinery while enforcing different filters or weights.

Memory Enrichment

Before creating or updating memory data, various types of enrichment can be applied; normalization, de-duplication, embedding, and classification. Enrichment should make memories more robust for later retrieval stages.

If an agent stores “Always use the staging wallet for integration tests,” the database row should carry enough metadata to explain why it later appears for “how should I run the database tests?”

Use Hybrid Retrieval

Memory queries can combine distinct signals:

Oracle Text gives you mature lexical retrieval through CONTAINS and SCORE, which is exactly what you want for commands, stack traces, identifiers, and names that embeddings may blur. See Oracle’s Oracle Text CONTAINS query operators.

A simple vector search component looks like this:

select id, content,
       vector_distance(embedding, :query_vector, cosine) as vector_distance
from agent_memory
where json_value(metadata, '$.memory_layer') = :memory_layer
order by vector_distance(embedding, :query_vector, cosine)
fetch first :candidate_limit rows only;

select id, content,
       vector_distance(embedding, :query_vector, cosine) as vector_distance
from agent_memory
where json_value(metadata, '$.memory_layer') = :memory_layer
order by vector_distance(embedding, :query_vector, cosine)
fetch first :candidate_limit rows only;

A text component can run beside it:

select id, content, score(1) as text_score
from agent_memory
where contains(content, :oracle_text_query, 1) > 0
order by score(1) desc
fetch first :candidate_limit rows only;

select id, content, score(1) as text_score
from agent_memory
where contains(content, :oracle_text_query, 1) > 0
order by score(1) desc
fetch first :candidate_limit rows only;

Scores from different search techniques can be weighted and combined to create a final score:

double final_score =
  vector_score     * vector_weight +
  text_score       * text_weight +
  entity_score     * entity_weight +
  graph_score      * graph_weight +
  recency_score    * recency_weight +
  importance_score * importance_weight

double final_score =
  vector_score     * vector_weight +
  text_score       * text_weight +
  entity_score     * entity_weight +
  graph_score      * graph_weight +
  recency_score    * recency_weight +
  importance_score * importance_weight

It’s useful to keep individual component scores in the result. When a memory is retrieved, the caller can see whether it matched semantically, lexically, by entity, through a graph edge, or because it was recent.

Related Memories (Memory Graph)

Sometimes memories make sense because they are related.

Memories may be quite similar, or overlapping: a task may be related to the error it fixes. Storing memory relationships as edges, you can start with direct hits and expand to adjacent context via graph traversal:

create table agent_memory_edge (
  edge_id raw(16) primary key,
  source_memory_id raw(16) not null
    references agent_memory(id) on delete cascade,
  target_memory_id raw(16) not null
    references agent_memory(id) on delete cascade,
  relationship varchar2(120 char) default 'related' not null,
  weight number(5,4) default 1 not null,
  metadata json,
  created_at timestamp with time zone default systimestamp not null
);

create table agent_memory_edge (
  edge_id raw(16) primary key,
  source_memory_id raw(16) not null
    references agent_memory(id) on delete cascade,
  target_memory_id raw(16) not null
    references agent_memory(id) on delete cascade,
  relationship varchar2(120 char) default 'related' not null,
  weight number(5,4) default 1 not null,
  metadata json,
  created_at timestamp with time zone default systimestamp not null
);

In Oracle AI Database, you can do this by defining a property graph (CREATE PROPERTY GRAPH) GRAPH_TABLE can project matches back into tabular SQL results. Graph expansion is part of normal database queries and not a separate service. See the GRAPH_TABLE operator and Oracle’s notes on native property graph support.

You can define different sorts of memory relationships as needed. Here’s a few I might use:

Database Agent Memory Recall for Prompt Assembly

Recall may search each allowed memory layer, keep the score details, group the results by layer, and then assemble a prompt-ready block.

Example shape:

Recall context for query: How should I run the integration test?

[session]
- Use the temporary wallet created for this run. (score=0.8120, sources=vector,text)

[user]
- Prefer commands that can be copied directly into zsh. (score=0.7440, sources=vector)

[org]
- Integration tests must not use production credentials. (score=0.7010, sources=text,entity)

Recall context for query: How should I run the integration test?

[session]
- Use the temporary wallet created for this run. (score=0.8120, sources=vector,text)

[user]
- Prefer commands that can be copied directly into zsh. (score=0.7440, sources=vector)

[org]
- Integration tests must not use production credentials. (score=0.7010, sources=text,entity)

The model receives context grouped by expected lifetime, and the application still has the raw ids and scores if it needs to inspect or explain the result.

FAQ

Why not just store agent memory in a vector table?

Memory needs more than similarity. Real systems need scope, ownership, lifecycle controls, exact-match search, metadata filters, timestamps, relationship tracking, and explainable retrieval.

What does Oracle AI Database add to agent memory?

Oracle AI Database lets you keep memory as transactional data while adding vector search, JSON metadata, Oracle Text, relational filters, and graph relationships. That means the memory layer can be queried, inspected, governed, and cleaned up using normal database patterns.

When should I use vector search for memory?

Use vector search when the query may be phrased differently from the stored memory. It is good for paraphrases and semantic similarity, such as matching “how should I run the tests?” to “Always use the staging wallet for integration tests.”

When is Oracle Text better than vector search?

Oracle Text is better for exact names, commands, flags, error messages, stack traces, file paths, package names, and short identifiers. These details are often critical in developer workflows and can be blurred by embeddings.

Why store JSON metadata with each memory?

JSON metadata gives the application flexible structure without forcing every memory attribute into a fixed column. It can store memory layer, tags, entities, source, confidence, extraction details, or application-specific fields while still allowing database-side filtering.

What are memory layers?

Memory layers separate context by expected lifetime and scope. Conversation memory may be short-lived, session memory may last for a run, user memory may store durable preferences, and organization memory may store shared conventions. Layering keeps recall more precise.

Why track last_accessed_at and access_count?

These fields let the system reinforce memories that are actually used. They can support recency and frequency scoring, but they should only be updated after a memory passes a confidence threshold and is accepted into recall.

What is a memory graph?

A memory graph stores relationships between memories. One memory may be related to another, supersede it, duplicate it, or contradict it. Graph expansion lets retrieval include useful adjacent context that did not directly match the original query.

Does this make agent memory perfect?

No. It makes memory inspectable and governable. To know what was stored, why it matched, which scope allowed it, how it was ranked, when it was used, and how to remove it.

References

Related Articles

• Agent Memory with LangChain4j and Oracle AI Database
• Full-Text JSON Search with Oracle Text
• Oracle AI Database: Property Graphs and JDBC
• What’s a Vector Database?
• JSON in Oracle AI Database: From 19c to 26ai
• AI Optimizer & Toolkit: connect LLMs and use your data

Oracle Documentation and Product Pages

• Oracle AI Agent Memory
• About Agent Memory
• Overview of Oracle AI Vector Search
• VECTOR_DISTANCE
• Vector Distance Functions and Operators
• JSON in Oracle AI Database
• Overview of Storing and Managing JSON Data
• JSON Data Type
• Overview of Oracle Text Queries
• Oracle Text SQL Statements and Operators
• GRAPH_TABLE Operator
• Oracle Property Graph
• SQL Graph Queries