DEV Community: Anya Summers

How to Build a Controlled MCP Workflow for Codex and Oracle AI Database

Anya Summers — Wed, 12 Aug 2026 15:28:59 +0000

Connect Codex CLI to Oracle AI Database through SQLcl MCP, then Add Oracle AI agent memory and LangChain Retrieval.

Companion notebook: Codex MCP Oracle AI Database

Key Takeaways

MCP turns AI-to-database access into an explicit tool contract instead of implicit system access. 
Oracle SQLcl in MCP mode (sql -mcp) is a direct, documented way to connect Codex CLI to Oracle AI Database through an MCP server. 
Oracle AI Database provides the persistent storage and vector search layer for memory workloads, while Oracle AI Agent Memory gives teams a Python API for threads, durable memory records, scoped retrieval, and context assembly on top of it.
LangChain can provide application-side wrappers and orchestration after the Oracle-backed memory and retrieval path is in place.
A strong default is hybrid: Codex CLI plus MCP for interactive database work, Oracle AI Database plus Oracle AI Agent Memory for durable memory, and LangChain only when the application needs reusable retrieval orchestration.

Here is how those components connect in this pattern. Codex works with Oracle through SQLcl MCP’s explicit tools. The saved SQLcl profile, database user, grants, and database policies remain the enforcement boundary. Oracle AI Agent Memory is the Python package your application uses to manage memory records on Oracle AI Database. LangChain is optional application-side orchestration after the database-backed memory and evidence path is in place; it is not the permission boundary or source of truth.

Production success depends less on clever prompting and more on boundaries, privileges, logging, scoped retrieval, and repeatable runbooks.

This guide is for developers who want Codex CLI to work with Oracle AI Database through explicit tools, durable memory, and reviewable retrieval evidence. It also shows an AI agent memory architecture where Codex handles the developer interaction, SQLcl MCP provides the tool boundary for database access; the saved SQLcl profile and database user permissions determine what Codex can actually do, and Oracle AI Database stores durable memory and retrieval evidence.

The developer path through this guide is simple:

Start with one approved Oracle connection and a read-only validation query. 
Put SQLcl MCP in front of that connection so Codex sees tools, not raw database credentials. 
Check the audit and activity trail before adding more tool access. 
Add Oracle AI Agent Memory when the workflow needs durable thread context, scoped recall, or reusable context cards. 
Add LangChain only when you need application-side retrieval orchestration beyond the MCP interaction loop.

Controlled Codex MCP + Oracle AI Database Workflow

Why This Architecture Is Useful for Developers

Giving an AI assistant database access is easy. But making that access controlled, inspectable, and repeatable is the hard part.

Codex is useful because it sits close to the developer’s actual work: files, commands, notebooks, configuration, and implementation details. That closeness becomes more sensitive when the workflow touches a database.

The question is not whether Codex can produce SQL-shaped text. The question is whether the database path is approved, observable, and easy to debug later. A useful workflow needs to preserve the request, the tool call, the database identity, the retrieved context, and the reason a risky action was allowed or blocked.

By the end of this guide, you should know how to connect Codex CLI to Oracle AI Database through a controlled MCP boundary, when Codex context is enough and when Oracle-backed memory is needed, and how to build a retrieval path that can be queried, audited, and scaled.

The workflow has four layers. Codex CLI is the developer-facing agent interface. SQLcl MCP is the tool boundary. Oracle AI Database is the durable substrate for memory, traces, and retrieval. The notebook is the build-and-validation harness that proves the pieces are wired correctly before the workflow is handed to Codex.

That separation matters. Codex does not need raw credentials in its prompt. SQLcl MCP can be configured once using the saved alias. Oracle AI Database owns persistence and retrieval. The notebook shows the setup in a way that can be rerun, inspected, and debugged.

A simple way to divide the responsibilities:

Layer	Responsibility
SQLcl MCP	Exposes declared Oracle tools to Codex; it is the tool boundary.
Oracle AI Database	Stores durable data, retrieval evidence, vectors, metadata, traces, and enforces database privileges.
Oracle AI Agent Memory	Provides application APIs for users, agents, threads, durable memories, scoped retrieval, and context assembly.
LangChain	Wraps Oracle-backed retrieval results as `Document` objects and supports application-side orchestration.

The Two Execution Loops

Building on the separation of responsibilities above, the system naturally forms two execution loops:

Loop A is the operational interaction loop: Codex CLI works with MCP to discover tools, inspect data, run bounded read-only queries, and return results immediately.
Loop B is the durable memory and retrieval loop: application code writes history, tool logs, memory records, chunks, and embeddings to Oracle AI Database, then retrieves context before a later answer or workflow step.

Dual Execution Loop: MCP Interaction and Durable Memory 

SQLcl MCP is for Codex operating interactively through declared tools. Oracle AI Agent Memory is for application code that stores turns, retrieves history, and assembles context before Codex or another assistant sees a prompt. You can drop either one for a narrower use case, but most production setups need both loops.

Reproducing the SQLcl MCP and Codex CLI Workflow

The setup should be reproducible. SQLcl runs in MCP mode with sql -mcp. Codex CLI launches it as an MCP server and talks to Oracle through declared tools, not through direct access. Connections come from saved SQLcl profiles that you create and test before Codex uses them.

Codex does not invent them at runtime; it reuses profiles you have already created and validated.

Prerequisites before you connect Codex CLI:

Oracle SQLcl 25.2.0 or higher. 
Oracle JRE 17 or 21. 
Codex CLI or another MCP-capable client you are explicitly configuring and testing. 
At least one saved SQLcl connection profile under ~/.dbtools, created with password persistence for MCP use. 
A database user with the minimum permissions required for the workflow. Start with read-only access and a sanitized development or replica environment where possible.

The notebook deliberately treats the saved SQLcl connection alias as a first-class artifact. In local development, that alias is what lets SQLcl MCP connect without forcing the agent to assemble credentials dynamically.

The notebook then generates a sanitized Codex MCP config preview. The preview is intentionally safe: it shows the server command and arguments without exposing secrets.

For the saved connection itself, the important detail is -savepwd. MCP cannot stop and ask a human for the password every time Codex invokes a database tool. The saved alias becomes the repeatable local path Codex can use after you have reviewed it.



```toml 
[mcp_servers.sqlcl]
command = "/absolute/path/to/sql"
args = ["-mcp"]
```

That small TOML block defines the connection between Codex and SQLcl MCP Server. Codex interacts with the database through the tools and permissions exposed by the MCP server, using the saved SQLcl connection profile you created and tested first. Save this configuration in ~/.codex/config.toml, restart Codex, then run codex mcp list to confirm that the SQLcl MCP server and its tools are discoverable.

Validation checklist before expanding access

Run sql -mcp locally and confirm the server starts. 
Restart Codex CLI and confirm the SQLcl tools are discoverable. 
Run one read-only query against an approved schema. 
Check database-side MCP activity logs and session metadata. 
Document the connection alias, database user, grant scope, restrict level, and troubleshooting owner.

Good first proof looks like this:

The MCP server starts without a Java or path error. 
Codex lists the SQLcl MCP tools after restart. 
A read-only query succeeds against the expected schema. 
The notebook audit trail records the expected tool interaction in codex_tool_logs.
For live Codex CLI + SQLcl MCP validation, confirm the database/session activity through your normal Oracle monitoring path.
A denied query fails because of the database role, not because a prompt asked nicely.

A useful MCP boundary is more than tool discovery. It is an operating contract. The notebook models that contract with risk levels, read-only defaults, confirmation requirements, scope checks, and controlled failure examples. Denied and warning states are useful because they prove the workflow can say no.

Read-only default: start with inspection and diagnostics before allowing changes.
Confirmation gate: require explicit approval for medium-risk, write-like, or destructive actions.
Scope control: keep user, tenant, and schema filters close to the database query.
Failure trace: store denied calls and warnings as evidence instead of hiding them.

Why put application memory records in Oracle AI Database, not just outputs 

Once the first MCP tool calls work, the next challenge is continuity. This is where long-term memory for AI agents becomes different from short-lived chat context.

If memory lives only in chat context, the system is fragile. If memory is scattered across files without structure, retrieval and auditing become expensive over time.

A database-backed memory model is usually cleaner and more scalable. The companion notebook builds this memory layer from scratch so the mechanics are visible, then shows how Oracle AI Agent Memory sits on top of it once the substrate is working.

Memory Categories That Matter in Practice:

You do not need to start by memorizing table names or column names. The useful idea is simpler: different kinds of memory serve different jobs.

Conversation memory: Keeps the important user and assistant turns that future sessions may need.
Operational memory: Keeps tool calls, outcomes, warnings, and failures so a team can debug what happened.
Semantic memory: Adds embeddings so the system can find relevant context even when the user asks in different words.

In practice, hybrid retrieval for agent memory usually combines exact operational terms, such as sql -mcp or codex_mcp, with semantic search over memory records.

The notebook shows the lower-level mechanics first so the storage and retrieval path is visible. This is also a context engineering problem: the application has to decide which memories, tool traces, and retrieval results should be assembled before Codex or another assistant answers. Oracle AI Agent Memory then gives application code a cleaner package API over that same database-backed idea.

Where Oracle AI Agent Memory Fits

Oracle AI Agent Memory sits between your application code and Oracle AI Database. The package manages conversation threads, durable memory records, scoped retrieval, and context assembly. Oracle AI Database remains the storage and enforcement layer underneath; the package does not replace that, it makes working with it faster and more consistent.

The notebook includes an abbreviated package-backed memory pattern. It initializes OracleAgentMemory with a database connection pool and a custom local deterministic embedder. LocalCodexEmbedder is notebook code, not a built-in Oracle AI Agent Memory embedder.

The local embedder is intentionally billing-free, which makes the notebook runnable for people who do not want to attach paid model usage to a tutorial.

Abbreviated setup pattern: define CONFIG, import and initialize oracledb, implement LocalCodexEmbedder, and set stable values for AGENT_MEMORY_USER_ID and AGENT_MEMORY_AGENT_ID before running this example.



```python 
from oracleagentmemory.core.oracleagentmemory import OracleAgentMemory 
from oracleagentmemory.apis.searchscope import SearchScope 
 
db_pool = oracledb.SessionPool( 
    user=CONFIG["ORACLE_USER"], 
    password=CONFIG["ORACLE_PASSWORD"], 
    dsn=CONFIG["ORACLE_DSN"], 
    min=1, 
    max=4, 
    increment=1, 
) 
 
agent_memory = OracleAgentMemory( 
    connection=db_pool, 
    embedder=LocalCodexEmbedder(dimensions=32), 
    llm=None, 
    extract_memories=False, 
    schema_policy="create_if_necessary", 
) 
```

Use oracleagentmemory from your application layer when you need package-managed users, agents, memories, threads, scoped retrieval, and context assembly. Keep systems of record separate from memory records: memory helps provide context, but application logic and authoritative data sources should still decide what is true, allowed, and final.

Implementation note: Use a schema whose default tablespace supports the JSON objects created by Agent Memory. If a local SYSTEM schema sits on a tablespace that rejects JSON object creation, the better production answer is a dedicated application schema. The notebook can still explain the issue clearly instead of hiding it.

Store and Search: What a Realistic Memory Looks Like

A realistic Codex memory is not generic trivia about a user. For this workflow, memory should capture how a developer actually works: the connection name they used, the SQLcl path that succeeded, the MCP config location, the failed privilege boundary, the retrieval query that helped, and the final fix that should be reused later.



```python 
thread = agent_memory.create_thread(
    user_id=AGENT_MEMORY_USER_ID,
    agent_id=AGENT_MEMORY_AGENT_ID,
)
 
thread.add_memory( 
    "Developer validated Codex CLI with SQLcl MCP alias codex_mcp " 
    "against local Oracle AI Database service FREEPDB1." 
) 
 
results = agent_memory.search(
    query="Codex SQLcl MCP alias validation and Agent Memory setup", 
    scope=SearchScope(user_id=AGENT_MEMORY_USER_ID), 
) 
```

That kind of memory pays off because it is operational. It can help Codex answer the next question with context from the previous debugging session, but it is still scoped and retrievable through a database-backed API.

Where LangChain Adds Value

LangChain should not be treated as the source of truth. Codex CLI does not call LangChain directly in this architecture.

LangChain runs in your application layer, where it can format context, wrap retrieval behavior, expose a familiar retriever shape, and plug Oracle-backed memory into chains or agents that already expect LangChain interfaces. In this notebook, LangChain is used as a compatibility layer: the notebook’s custom Oracle-backed hybrid_search() path performs hybrid retrieval, and its results are wrapped as LangChain Document objects so they can flow into applications that already expect LangChain interfaces.

Use Oracle AI Database for persistence, retrieval, tenancy, validation evidence, and tool traces.
Use Oracle AI Agent Memory for package-backed memory threads, durable memories, and scoped search.
Use LangChain for retriever wrappers and orchestration compatibility when the application already expects that shape.
Use Codex CLI for developer interaction, planning, and guided execution.

The notebook's LangChain section is valuable because it comes late. By the time it appears, the database tables, package memory, retrieval scores, and validation snapshot already exist. LangChain becomes a wrapper around evidence, not a substitute for evidence.

Do not add LangChain just to make the workflow sound more agentic. If the application only needs direct SQL, package-backed Agent Memory search, or a simple evidence table, the extra abstraction can make debugging harder. LangChain is a good fit when the consuming application already expects retrievers, documents, chains, or tool orchestration. It is not the permission boundary, the memory store, or the audit layer.

Engineering Guidance

The difference between demo success and production success is disciplined operations. Most failures at this stage come from integration gaps, not model behavior. A local notebook can prove the wiring. A production workflow needs smaller database roles, managed secrets, clear MCP approval policy, repeatable environment setup, and monitoring around memory writes and tool calls.

Access and privilege model

Use a dedicated application schema where possible, then tighten grants with least-privilege roles and quotas.
Keep saved SQLcl aliases separate by role and environment instead of sharing one broad connection.
Start read-only wherever possible and gate write operations with explicit confirmation workflows.
Use schema allowlists and separate accounts for development, test, and production.

Observability model

Log tool name, thread ID, timestamp, status, and sanitized inputs and outputs.
Classify failures into runtime, discovery, connection, permission, query, and retrieval categories.
Keep a troubleshooting playbook in the repo so setup issues do not become tribal knowledge.
Check whether retrieval quality changes as more data and memory records are added.

Reliability model

Prefer deterministic SQL patterns with bounded result sets.
Use retrieval-first context assembly for memory-heavy tasks.
Avoid giant context stuffing as a substitute for memory design.
Review and prune tool surfaces periodically.
Move from the local deterministic embedder to a supported embedding model after cost, latency, and privacy review.

Typical Failure Modes

Most teams hit a predictable set of issues.  

Runtime failure: sql -mcp does not start

Check the absolute SQLcl path, confirm Java is available, and run sql -mcp outside Codex first. Resolve runtime issues before checking assistant behavior.

Discovery failure: Codex does not see tools

Check the Codex MCP configuration, confirm the configured command points to the SQLcl executable, and restart or reload Codex CLI after edits.

Connection failure: tools are present but queries fail immediately

Check the saved SQLcl connection alias, confirm the profile lives under the expected SQLcl connection store, and verify password persistence for the MCP workflow. Then test the same connection outside Codex.

Permission failure: queries execute selectively and fail on specific objects

Check the database role first. A selective failure can be the right outcome when least privilege is working. Add grants intentionally and keep read-write access separate from the initial validation path.

Retrieval quality failure: answers are fluent but weakly grounded

Inspect the retrieved records before blaming the model. Check chunk size, metadata filters, embedding choice, top-k settings, and whether the query is asking for exact history, semantic similarity, or operational logs.

Why the Hybrid Model Is Usually the Best Long-Term Design

No single layer handles both execution and memory well. Trying to force everything into Codex context gets messy fast: you either lose control over execution, or you stuff too much state into prompts just to keep things working. On the other side, if you only build backend memory systems, you lose the speed and usability that makes an assistant useful during development.

The hybrid approach works because it does not try to solve everything in one place. Execution stays controlled through MCP. Memory stays durable and queryable in Oracle AI Database. Oracle AI Agent Memory gives application code a cleaner memory API. LangChain adds orchestration only after evidence exists.

In real teams this usually evolves over time. Start with Codex CLI, SQLcl MCP, read-only access, and strong logging. Once people rely on the workflow, the gaps become visible: lost context, weak traceability, repeated setup work, or retrieval that is hard to explain. That is when database-backed memory and structured retrieval become worth adding.

Conclusion

A Codex and SQLcl MCP workflow becomes useful when it is treated as an engineering pattern, not just a setup trick. Codex keeps the developer moving. MCP makes tool access explicit. SQLcl connects those tools to reviewed Oracle profiles. Oracle AI Database stores durable memory records, traces, and retrieval evidence. Oracle AI Agent Memory gives applications a practical memory API. LangChain helps when retrieval needs to become reusable orchestration.

The result is a workflow a team can inspect. You can see what Codex asked for, which tool path ran, what the database allowed, which memory records were retrieved, and how the final answer was assembled.

That is the shift that matters: from assistant access that is implicit and hard to audit, to explicit boundaries, durable memory, and evidence a developer can actually debug.

Frequently Asked Questions

What is MCP in this context?

MCP is the protocol boundary that lets Codex call explicit tools exposed by a server instead of accessing systems implicitly.

What does MCP protect, and what does it not protect?

MCP makes the tool interface explicit and reviewable: Codex can call only the tools exposed by the server. It does not replace database security. The saved SQLcl connection profile, database user, grants, roles, network controls, and database policies determine what those tools can actually access or change.

Why use SQLcl for Oracle MCP?

SQLcl already understands Oracle workflows and can run as the Oracle SQLcl MCP server with sql –mcp, making the Oracle integration practical and direct.

Is this Codex CLI or Codex app?

The workflow should be described as Codex CLI/TUI oriented because the MCP configuration is written as a local Codex config preview and the SQLcl MCP server is launched as a command.

Why include Oracle AI Database if MCP already works?

MCP handles the execution boundary. Oracle AI Database handles durable memory, retrieval, vector search, concurrency, observability, and governance.

Do I need an OpenAI API key?

An external model-provider API key is only needed if you change the notebook to use a provider-backed embedding or LLM service. The default notebook path uses a local deterministic embedder so people can run the Agent Memory package section without adding billing details.

Why use SQLcl MCP instead of connecting directly from Codex?

MCP gives the workflow a defined tool boundary. Direct connections can be useful in notebooks, but Codex should call tools through a contract that can be logged, scoped, and reviewed.

Why include LangChain if Oracle already stores memory?

Because many teams already have LangChain-shaped retrievers and chains. The notebook shows how to expose Oracle-backed retrieval through that shape while keeping memory and enforcement in the database.

Is this RAG vs agent memory?

Not exactly. RAG retrieves external knowledge for a response, while agent memory preserves useful context, decisions, tool traces, and workflow state across sessions. In practice, production systems often use both.

What is the minimum viable setup?

SQLcl MCP configured in Codex CLI, one safe saved Oracle connection, and a read-only validation flow.

Should production start with read-write permissions?

Usually no. Start read-only, log everything important, and add write scopes gradually with explicit approvals.

What is the best rollout strategy?

Pilot in development with read-only access and strong logging, then expand capabilities in controlled phases as the team learns which memory and tool paths are actually useful.

Companion Troubleshooting Appendix

Minimum viable setup: SQLcl MCP configured in Codex CLI, one approved Oracle connection, read-only validation, and database-side activity logging. 

First checks: confirm sql -mcp starts, Codex sees the tools after restart, and the saved SQLcl connection alias resolves. 

Environment model: use separate credentials and policies for dev, test, and prod, with stricter controls as capability expands. 

Logging model: capture tool name, timestamp, thread ID, status, sanitized input/output summaries, and relevant SQLcl MCP log records. 

Retrieval quality: tune chunk size, enrich metadata, review embedding choice, and evaluate retrieval against representative queries. 

Common anti-pattern: expanding tool surfaces before ownership, logging standards, and runbooks are in place. 

Rollout path: pilot in dev with read-only access and strong logging, then expand capabilities in controlled phases. 

Schema: Agent Memory package tables need a schema and tablespace that can create the package's JSON-backed objects.

Model calls: an API key can be present but still fail if the model provider account has no quota.

Resources

Agent memory is a database problem: Oracle research makes the case

Anya Summers — Tue, 11 Aug 2026 15:16:38 +0000

A technical report on arXiv sets out a database-native memory substrate for long-horizon agents: 93.8% on LongMemEval, roughly 10.7× fewer input tokens than flat conversation history, and governance treated as a design constraint, not an afterthought.

Key Takeaways

Agent memory is fundamentally a data-management problem. Long-horizon agents need persistent, scoped, retrievable, and governable memory, not just larger context windows or more conversation history.
Oracle Agent Memory manages working memory, long-term factual memory, and procedural memory directly on Oracle AI Database, with a lifecycle spanning extraction, consolidation, retrieval, summarisation, and revision.
In the report, Oracle Agent Memory achieved 93.8% on LongMemEval and kept estimated input context to about 1,300 tokens per request at turn 80, compared with roughly 13,900 for flat conversation history.
Keeping agent memory in the database keeps it close to the enterprise data it reasons over, while bringing transactions, relational, JSON and vector capabilities, scoped retrieval, and database-enforced governance into the same substrate.

Prompt and context engineering emerged for a reason: to elicit specific behaviours from large language models whilst maintaining a high signal-to-noise ratio in the context window, through systematic curation of the data injected into it. But as models grow more capable at reasoning, extensive context-window management is becoming less necessary.

The real job to be done sits elsewhere: adapting agents to new information, and making them dependable on long-horizon tasks. That job is the subject of a new technical report from Oracle's AI teams, Oracle Agent Memory as an Enterprise Memory Substrate for Long-Horizon AI Agents, published this month on arXiv.

Agent memory has emerged as the solution: the capability that lets agentic systems learn, adapt, and personalise from continuous interaction between the agent, its users, and its environment. The report grounds this in recognisable enterprise use cases: support agents recalling incident history across weeks, coding agents reusing prior design decisions, analytics agents that know what "average spending" means in the business.

The core problem investigated is not recall; it is maintaining heterogeneous memory under explicit scope, retention, and retrieval constraints; this is effective memory management. Yet although many memory-management solutions have emerged, few have met the demanding requirements of enterprise AI workloads. Most are variations on "just add a vector store": fragments assembled around the model (reflection buffers, framework add-ons, standalone memory services) that each work in a demo, and that together complicate governance, security, data locality, and reliability in an enterprise setting.

The gap is enterprise-grade memory: a substrate where governance and security are entry requirements rather than roadmap items, and where scale, reliability, and data locality come built in rather than bolted on. The report is written squarely at that gap. Its starting observation is simple: the list of things agent memory demands (persistence, indexing, scoped retrieval, transactions, governance) is a list of database requirements.

Oracle Agent Memory is a database-native memory substrate that treats agent memory as a managed lifecycle, spanning ingestion, extraction, consolidation, retrieval, summarisation, and revision, built directly on Oracle AI Database. The report organises that memory into three operational categories:

Working memory: the active state of a task, materialised as thread summaries and context cards compact enough for prompt injection.
Long-term factual memory: durable facts, preferences, and profile attributes that persist across sessions under user and agent scope.
Procedural memory: lessons, strategies, and guidelines drawn from prior outcomes; what the agent has learned to do, not just what it knows.

An active memory core does the opinionated work: synchronising threads, extracting durable facts, refreshing summaries and context cards, orchestrating search. Beneath it sits a passive memory-store contract (insert, search, retrieve, enforce scope) that stays reusable even for third-party memory libraries wanting Oracle AI Database as their backend.

The report measures the substrate across long-horizon memory benchmarks, a flat-history baseline, and estimated token behaviour:

93.8% on LongMemEval (469 of 500), including 100% on single-session assistant recall and 96.2% on temporal reasoning. Multi-session reasoning is the honest low point at 88%, and the report says so.
Roughly 10.7× fewer estimated input tokens than a flat-history baseline by turn 80 of a scripted conversation: about 1,300 tokens per request against roughly 13,900.
48 wins, 13 losses, 19 ties in pairwise judging against that same flat-history baseline, a 3.7× win ratio among non-tied turns, even though the baseline saw the entire transcript. Bounded, relevant context beat complete, unfocused context.
0.680 on BEAM at the 1M-token scale under event-presence scoring, ahead of previously reported external results, with the stricter order-sensitive score (0.630) and an early 10M stress test (0.510) published alongside it.

For enterprise leaders, the argument is about where institutional memory should live. Agent memory holds private conversations, inferred preferences, learned procedures, and retrieved enterprise facts. Store it in a side service with its own authorisation model and you replicate identity, role, and policy outside your primary data platform. Place it in the database and memory stays close to the enterprise data it reasons over, inheriting the controls you already trust: with database-enforced access control through Oracle Deep Data Security, a denied record is absent from retrieval because the acting identity was never allowed to see it, not because an application filter caught it afterwards. Memory stops being another shadow data store to govern.

For developers, the report reads like an engineering document, because it is one. Threads are the integration primitive, and the workflow has four recurring stages:

Initialise the memory client with a database connection, an embedder, and an optional LLM.
Create or reopen a thread, scoped to a user and an agent.
Add messages so the system persists history and runs extraction and summarisation.
Retrieve context via scoped search, context cards, or thread summaries before the next turn.

Defaults are conservative: bounded reads, bounded search, explicit scope with exact-match controls, no hidden schema side effects. The oracleagentmemory Python client is model-agnostic at the embedder and LLM layer, slots into harnesses such as LangGraph and WayFlow, and supports three integration patterns (middleware-centric, tool-centric, or hybrid). The appendices cover setup, thread lifecycle, and search semantics in runnable detail.

The report also stakes out an evaluation position both audiences should note: downstream accuracy alone is not enough. A correct answer can hide noisy retrieval; a wrong one can hide a memory layer that did its job. It measures memory as a system instead, separating evidence retrieval, evidence use, task outcome, and operational efficiency, and applies that standard to itself, publishing configuration caveats, threshold sweeps, and failure modes in plain sight.

Every team building agents eventually rediscovers the same requirements list, and then rebuilds it around a vector store. The position behind Oracle Agent Memory is that the list already has an answer. Give agents a substrate that speaks transactions, vectors, JSON, and policy natively, and the effort moves to where it actually matters: managing the lifecycle of what an agent remembers, not plumbing where it stores it. The storage question is settled. Remembering is the work. The full report is available on arXiv: https://arxiv.org/pdf/2607.13157

FAQs

What is agent memory?

Agent memory is the persistent state that lets an AI agent retain and retrieve useful information across interactions. That can include active task context, facts and preferences learned over time, and procedures or strategies derived from previous outcomes. Unlike the context window, that information does not have to disappear when the current conversation ends.

How is agent memory different from RAG or a vector database?

Vector retrieval can be part of agent memory, but retrieval alone is not memory management. An agent also needs to decide what to retain, how that information is scoped, when it should be updated or summarised, how long it should persist, and who is allowed to retrieve it. The Oracle report treats those as lifecycle and data-management problems rather than just search problems.

Why use a database for AI agent memory?

Agent memory needs many of the capabilities databases already provide: persistence, indexing, structured and semantic retrieval, transactions, access control, and governance. A database-native approach also allows relational data, JSON, vectors, and memory state to live on the same managed substrate rather than being distributed across separate memory services.

How does Oracle Agent Memory integrate with agent applications?

Developers create or reopen a thread scoped to a user and agent, add messages, and retrieve relevant context before subsequent turns. The oracleagentmemory Python client is model-agnostic at the embedding and LLM layers and supports middleware-centric, tool-centric, and hybrid integration patterns, including with frameworks such as LangGraph and WayFlow.

Custom Memory Extraction for AI Agents: Turning Conversations into Useful Facts

Anya Summers — Tue, 11 Aug 2026 15:11:32 +0000

Learn how Oracle AI Agent Memory uses custom extraction instructions, thread-level overrides, and tool-result metadata to turn support conversations into durable, scoped memory.

Companion notebook: custom_memory_extraction_agent_memory.ipynb

Key Takeaways

Oracle AI Agent Memory can transform raw user-agent messages into durable memories stored in Oracle AI Database.
Default extraction provides a general memory baseline; custom extraction instructions make memory formation reflect a domain-specific policy.
A customer-support agent should preserve durable facts such as order IDs, return requests, delivery issues, escalation commitments, tool-confirmed statuses, and stable communication preferences.
Good memory is selective: the extractor should ignore greetings, speculation, credentials, payment details, one-time codes, and temporary conversational wording.
Client-level custom instructions define the default extraction behavior, while thread-level overrides allow narrower policies for special workflows such as escalations.
Tool-result metadata can be inherited into memories, making later search more scoped, auditable, and aligned with enterprise retrieval boundaries.
The notebook verifies database connectivity and table-creation permissions before running memory operations.

Custom extraction turns raw conversation into governed memory

Why Agents Need Selective Memory

Oracle AI Agent Memory provides a database-backed memory layer for AI agents, so applications can store messages, durable memories, metadata, summaries, and prompt-ready context in Oracle AI Database.

AI agents are most useful when they can carry context forward. A support agent may need to remember that a customer reported a damaged item, prefers SMS updates, or was promised an escalation by Friday. Without persistent memory, the agent has to rediscover these facts from chat history or ask the user again.

But remembering everything is not the same as remembering well. Real conversations contain greetings, clarifications, temporary codes, apologies, repeated facts, speculation, and sometimes sensitive information. If every detail becomes durable memory, future retrieval becomes noisier and harder to govern.

Custom memory extraction addresses this by turning extraction into an application policy. Instead of only asking the model to summarize a conversation, developers can define what their workflow considers useful memory and what should be ignored.

The Customer-Support Use Case

The companion notebook uses a customer-support workflow because it has a realistic mix of natural language, exact identifiers, tool results, and policy boundaries. This is the kind of workflow where a generic transcript summary is not enough.

The support conversation includes durable facts such as order identifiers, return intent, delivery problems, replacement commitments, and customer preferences. It also includes details that should not become long-term memory, such as temporary wording, credentials, payment details, and speculation.

Preserve exact identifiers such as order IDs, return IDs, and case IDs.
Preserve confirmed support facts such as delivery issues, product defects, return requests, and replacement commitments.
Preserve stable preferences that should influence future interactions.
Preserve tool-confirmed facts when a tool result is the source of truth.
Ignore small talk, unconfirmed guesses, secrets, payment details, and one-time codes.

Customer-support extraction policy

From Conversation Text to Durable Memory

Oracle AI Agent Memory stores the conversation and uses an extraction workflow to form higher-level memories. In a support case, those memories should be concise, durable, and useful for later retrieval. A memory like "Customer prefers morning delivery windows for replacement shipments" is more reusable than several raw turns where the user mentioned their preference indirectly.

The notebook first establishes a baseline extraction path. This baseline is useful because it shows the general extraction behavior before any domain-specific policy is added. The custom path then uses the same kind of support conversation but adds explicit extraction instructions.

SUPPORT_EXTRACTION_INSTRUCTIONS = """
Extract only durable customer-support memory that can help future support interactions.

Preserve confirmed order IDs, return requests, delivery issues, escalation reasons,
stable customer preferences, and tool-derived support facts.

Ignore greetings, small talk, speculation, credentials, payment secrets,
one-time verification codes, and temporary conversational wording.
""".strip()

These instructions guide the LLM-assisted extraction step. They help shape what should become durable memory, but they should not be treated as deterministic filtering, guaranteed redaction, or a replacement for application-level security controls.

This instruction block is intentionally written like a product policy. It does not ask the extractor to remember more; it asks the extractor to remember better. The policy defines durable support memory, instructs the extractor to preserve exact identifiers, and excludes information that should not influence future agent behavior.

Baseline Extraction vs. Custom Extraction

The notebook shows the comparison directly. A baseline client uses the package's general extraction behavior. A custom client uses the same support scenario with custom extraction instructions. This makes the effect of the policy visible rather than theoretical.

from oracleagentmemory.core import MemoryExtractionConfig, OracleAgentMemory

base_memory = OracleAgentMemory(
    store=base_store,
    llm=memory_llm,
    memory_extraction_config=MemoryExtractionConfig(
        memory_extraction_frequency=1,
        enable_context_summary=False,
    ),
)

custom_memory = OracleAgentMemory(
    store=custom_store,
    llm=memory_llm,
    memory_extraction_config=MemoryExtractionConfig(
        memory_extraction_frequency=1,
        enable_context_summary=False,
        memory_extraction_custom_instructions=SUPPORT_EXTRACTION_INSTRUCTIONS,
    ),
)

The important design point is that the application owns the memory policy. The model helps extract memories, but the application defines what kinds of facts should survive, which exact identifiers matter, and what kinds of text should be excluded.

Because this policy is evaluated by an LLM-assisted extraction step, it should be treated as guidance rather than deterministic filtering, guaranteed redaction, or a replacement for application-level security controls.

For the reader, the expected outcome is straightforward: baseline extraction demonstrates general memory formation; custom extraction demonstrates memory formation shaped by support workflow priorities.

What to Look for in the Output

The most important signal is not that the custom path creates more memory records. The stronger signal is that the memory records are better shaped for the workflow. The custom extraction path should preserve durable support facts while avoiding temporary or sensitive details that should not guide future conversations.

The order identifier remains available as an exact support reference.
The return or replacement context is preserved as durable case state.
The customer's stable delivery or communication preference is retained.
Temporary details, such as a one-time lobby or access code, are not treated as reusable memory.
Tool-confirmed status can be preserved with metadata that describes the source and workflow tag.

This before-and-after comparison is what makes custom extraction practical: it shows how memory can become more useful, searchable, and governable without turning the full transcript into long-term state.

Signal in the notebook	Baseline extraction	Custom extraction
Exact support identifiers	May preserve the identifier if it is generally salient.	Explicitly preserves order, return, and case identifiers as durable support references.
Return or replacement state	May summarize the conversation at a general level.	Keeps confirmed return, replacement, delivery, and escalation facts as reusable case state.
Customer preference	May capture the preference if it appears important in the conversation.	Preserves stable communication and delivery preferences that should influence future interactions.
Temporary detail	May not have a domain-specific reason to exclude it.	Treats one-time codes, temporary instructions, and conversational noise as non-durable.
Tool-confirmed fact	May treat tool output like another message.	Preserves tool-derived support facts and selected source metadata for scoped retrieval.

Client-Level Defaults and Thread-Level Overrides

Not every conversation in an application needs the same extraction policy. Most support threads may follow a broad support-memory policy, while an escalation thread may need a narrower policy focused only on shipment commitments and customer-facing follow-up deadlines.

Oracle AI Agent Memory supports this pattern by allowing custom extraction instructions at the client level and overrides at the thread level. The client-level policy becomes the default. A thread-level override can refine the policy for one conversation.

escalation_thread = custom_memory.create_thread( 
    thread_id=f"escalation_support_{RUN_ID}", 
    user_id=USER_ID, 
    agent_id=AGENT_ID, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_frequency=1, 
        enable_context_summary=False, 
        memory_extraction_custom_instructions=ESCALATION_EXTRACTION_INSTRUCTIONS, 
    ), 
)

The notebook also shows lifecycle control for thread-level instructions. A workflow can update the thread policy when the support state changes, or clear the override to return to the client-level default.

updated_escalation_thread = custom_memory.update_thread( 
    escalation_thread.thread_id, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_custom_instructions=( 
            "Extract only shipment escalation commitments and customer-facing follow-up deadlines." 
        ) 
    ), 
) 
 
cleared_escalation_thread = custom_memory.update_thread( 
    escalation_thread.thread_id, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_custom_instructions=None 
    ), 
)

Client-level policy and thread-level override

Common Patterns for Custom Extraction Instructions

Custom extraction instructions are flexible by design. Developers do not need a different memory API for every workflow. Instead, they can express the workflow's memory policy through instructions passed into the extraction configuration.

The useful pattern is to write instructions as a boundary: what to preserve, what to ignore, and which source or metadata signals should influence the extracted memory. The following patterns are not separate package objects; they are practical ways to structure `memory_extraction_custom_instructions` for common agent workflows.

Pattern	Use when	Instruction focus
Support memory policy	A support agent needs durable case context across future interactions.	Preserve order IDs, return IDs, delivery issues, replacement commitments, escalation reasons, and stable preferences. Ignore greetings, credentials, payment details, one-time codes, and temporary wording.
Escalation-only policy	One thread needs a narrower policy than the default client-level behavior.	Preserve escalation reason, owner or team, customer-facing follow-up deadline, and confirmed commitments. Ignore general troubleshooting and repeated background context.
Tool-result policy	The agent receives tool outputs that should be treated as authoritative workflow state.	Preserve tool-confirmed facts, keep exact identifiers, inherit selected metadata such as tenant, source, and tags, and distinguish tool-confirmed facts from user-reported claims.

Tool-Aware Extraction with Metadata

Tool-aware extraction is especially useful in enterprise workflows because many agents do not rely only on user messages. They call tools, retrieve operational state, and produce tool results that may be more authoritative than the natural-language conversation.

In the support notebook, a tool result can carry metadata such as tenant, source, and tags. The extraction policy can tell the memory layer to preserve tool-derived support facts, and selected metadata can be inherited into the extracted memory.

tool_thread = custom_memory.create_thread( 
    thread_id=f"tool_support_{RUN_ID}", 
    user_id=USER_ID, 
    agent_id=AGENT_ID, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_frequency=1, 
        enable_context_summary=False, 
        memory_extraction_custom_instructions=TOOL_AWARE_EXTRACTION_INSTRUCTIONS, 
        memory_extraction_inherit_message_metadata=["tenant", "source", "tags"], 
    ), 
)

This pattern lets the application preserve not only the memory text, but also where the remembered fact came from. Later, scoped retrieval can use both natural-language query text and metadata filters.

tool_filtered_results = await tool_thread.search_async( 
    "replacement shipment delayed backordered hinge escalate", 
    max_results=5, 
    exact_thread_match=True, 
    record_types=["memory", "fact", "preference", "guideline"], 
    metadata_filter={ 
        "tenant": "acme", 
        "source": "support-copilot", 
        "tags": ["support", "tool:order_status", "replacement"], 
    }, 
)

Tool-result metadata and scoped retrieval

Why Metadata Matters for Memory Governance

Memory retrieval should be relevant, but it also needs boundaries. In enterprise systems, an application may need to search only within a tenant, case, workflow, source system, or review state. Similarity ranking alone is not an authorization or governance model. Metadata filters complement, but do not replace, application authentication and authorization.

Metadata gives the application a way to constrain which memories are eligible before ranking. In this article's use case, metadata can distinguish support-copilot facts from raw conversation messages, tag memories that came from an order-status tool, or keep tenant-specific facts scoped to the right customer context.

This is also why custom extraction and metadata inheritance belong together. Custom extraction shapes the content of the memory. Metadata inheritance shapes how that memory can be retrieved, filtered, reviewed, and governed later.

Running the Companion Notebook

The companion notebook starts with a practical database setup path. The setup cells connect to an Oracle AI Database schema, verify a simple query, and verify table-creation permissions before the memory store is initialized.

For a lightweight trial environment, developers can use FreeSQL to get an Oracle AI Database schema before moving to a managed development or production database environment.

That table-permission check matters because Oracle AI Agent Memory manages database-backed storage objects. If a developer can connect but cannot create tables, the memory workflow will fail later during schema setup. Surfacing that issue early makes the notebook more practical.

The notebook also sets the Developer Hub program identifier before creating the database connection pool. This follows the Developer Hub convention for technical assets that connect to Oracle AI Database and makes the notebook identifiable in database telemetry.

For environments with a database-resident embedding model, the same pattern can use Oracle AI Database-backed embeddings and Oracle AI Vector Search. For the custom extraction article, the main focus remains memory formation: how raw conversations become durable, domain-specific memories with the right policy and metadata.

For database-level retrieval background, see the Oracle AI Vector Search documentation.

What the Companion Notebook Demonstrates

Connect to Oracle AI Database and verify table permissions.
Set the Developer Hub program identifier before opening the database connection.
Configure Oracle AI Agent Memory for a support workflow.
Add a support conversation containing exact identifiers, support commitments, preferences, and details that should not become memory.
Run baseline extraction as a general-purpose reference point.
Apply support-specific custom extraction instructions.
Compare extracted memories before and after customization.
Override extraction instructions for one escalation thread.
Update and clear thread-level extraction instructions.
Extract tool-derived support facts and inherit selected metadata.
Search with metadata filters to retrieve scoped memory results.
Clean up memory records and database objects after the run.

Security and Deployment Notes

Custom extraction instructions are not a substitute for application security controls. Oracle AI Agent Memory persists data in the database schema selected by the application owner, while the surrounding application remains responsible for authenticating users, enforcing access control, and passing the correct user, agent, and thread scope into memory operations.

Applications should avoid sending secrets or unnecessary sensitive information into memory ingestion flows unless that behavior is explicitly intended and reviewed. Extraction instructions can guide what the model should preserve or ignore, but they should be treated as policy guidance rather than a hard redaction guarantee.

Keep credentials, payment details, and one-time verification codes out of memory ingestion when possible.
Use exact user, agent, thread, and tenant scope for retrieval.
Use metadata filters for workflow boundaries such as source system, review status, or tool tag.
Review custom extraction instructions with domain owners, not only with developers.
Use cleanup steps in notebooks and development schemas to avoid leaving test data behind.

Conclusion

Custom memory extraction makes agent memory more useful by making it selective. Instead of treating every conversation as durable knowledge, developers can define what their application should preserve, which identifiers matter, what metadata should follow extracted memories, and when a specific thread needs a narrower policy.

For customer-support agents, this means future interactions can draw on the right facts: order IDs, delivery issues, return requests, escalation commitments, stable preferences, and tool-confirmed statuses. The agent gets memory that is easier to retrieve, easier to scope, and better aligned with the workflow it serves.

A practical way to start is with a narrow extraction policy, inspect the extracted memories, and then expand the policy and metadata taxonomy with domain owners.

Frequently Asked Questions

What is custom memory extraction?

Custom memory extraction lets developers guide what Oracle AI Agent Memory should preserve as durable memory and what it should ignore when processing conversation messages.

Why should an agent not remember everything?

Full transcripts contain temporary wording, repeated details, speculation, and sometimes sensitive data. Durable memory should preserve reusable facts, preferences, commitments, and workflow state.

What is the difference between baseline and custom extraction?

Baseline extraction shows general-purpose memory formation. Custom extraction adds domain-specific instructions so the memory output reflects the application's workflow policy.

What is a thread-level extraction override?

A thread-level override is a custom extraction policy applied to one thread. It can narrow or change the default client-level policy for a specific workflow such as an escalation.

How does tool metadata help?

Tool metadata records where a memory came from and how it should be scoped. It can help retrieve only memories from a specific tenant, source system, or tool result.

Does the notebook require FreeSQL?

No. FreeSQL is a lightweight trial setup path. The same memory APIs can run against another Oracle AI Database environment with the required credentials and permissions.

Resources

Companion notebook

Oracle AI Agent Memory package

Oracle AI Agent Memory documentation

FreeSQL

Oracle AI Vector Search documentation

Hybrid Search for Oracle AI Agent Memory: Combining Semantic Recall with Exact Match

Anya Summers — Tue, 11 Aug 2026 14:59:04 +0000

Use Oracle AI Agent Memory hybrid search when persistent AI agent memory needs both semantic similarity and exact text precision.

Companion Notebook: Hybrid Search for Oracle AI Agent Memory: Combining Semantic Recall with Exact Match

Key Takeaways

Vector search is strong for semantic recall, but agent memory often contains exact strings that should remain first-class retrieval signals.
Hybrid search combines semantic retrieval and exact text matching so agents can recall both the named item and the surrounding context.
Scoped retrieval and hybrid search solve different parts of the problem: scope controls which memories are eligible to be searched, while hybrid search controls how those eligible memories are ranked.
Oracle AI Agent Memory uses `SearchStrategy.HYBRID` with `OracleDBEmbedder`, while `SearchIndexSyncMode` controls managed search-index refresh behavior.
The companion notebook validates hybrid retrieval across five exact, semantic, and mixed-query scenarios using deliberately similar memories and confirms the expected memory at rank one in every scenario.

This guide shows how to build hybrid retrieval for agent memory with Oracle AI Agent Memory, combining semantic recall with exact text matching.

The retrieval problem hybrid search solves

A useful agent memory system has to do two things at once. It must remember by meaning when the user paraphrases, and it must stay exact when the user refers to a specific business object, system event, or operational handle. Many retrieval misses happen in the gap between those two needs: the query is semantically close to several memories, but only one memory contains the exact identifier that should anchor the answer.

Hybrid search closes that gap. Instead of treating semantic recall and exact text matching as separate retrieval systems, it gives the memory layer a combined path for vector and keyword search. The agent can retrieve the surrounding context and preserve the literal token that makes the memory trustworthy.

That is the core value of agent memory hybrid search: long-term agent memory can stay flexible without losing the exact text that enterprise workflows depend on.

This article starts with a simple retrieval problem: a user may ask about a business event in natural language, then later refer to the same event by an invoice number, issue ID, error code, customer alias, or filename. A useful memory system should handle both query styles.

Setting up Oracle AI Agent Memory

Oracle AI Agent Memory provides a durable memory layer for AI agents. It stores scoped memories, retrieves relevant context for later turns, and helps applications separate what the agent is allowed to remember from how memories are ranked.

Hybrid search expands that retrieval model so an agent can recall by semantic meaning and by exact text in the same workflow, while memory governance remains tied to scope and application policy.

The setup has four main components:

`OracleAgentMemory` is the durable memory layer. It stores memories, applies scope, and exposes search.
`OracleDBEmbedder` connects retrieval to Oracle-backed embedding behavior used by the memory store.
`SearchStrategy.HYBRID` tells the memory layer to combine semantic and exact-text retrieval signals.
`SearchIndexSyncMode` controls when the managed search index is refreshed after memories are written.

Start by installing the oracleagentmemory package in the notebook or application environment. The hybrid-search API is configured in application code; the database schema and index setup are handled through the Oracle-backed memory store.

This makes Oracle AI Database agent memory a practical pattern for applications that need durable memories, scoped retrieval, and database-managed search.



```bash 
pip install oracleagentmemory==26.6.0 
```

The core imports for a hybrid-search configuration look like this:



```python 
from oracleagentmemory.apis.searchscope import SearchScope 
from oracleagentmemory.core import ( 
    MemoryExtractionConfig, 
    OracleAgentMemory, 
    SchemaPolicy, 
    SearchIndexSyncMode, 
    SearchStrategy, 
) 
from oracleagentmemory.core.embedders import OracleDBEmbedder 
```

Why vector memory is not always enough

Agent memory is not only a semantic archive of previous conversations. In enterprise systems, memory records often include short strings that carry operational meaning.

A single identifier can point to the correct incident, invoice, customer, model, file, run, or database error. If retrieval treats that identifier like ordinary prose, the agent can return a memory that is semantically related but operationally wrong.

Vector search helps when users paraphrase. It is especially useful when a user asks about a topic without repeating the exact words stored in memory.

But vector similarity is not the same as exactness. A short token can be diluted by the rest of the sentence, especially when the surrounding context is broad or when several memories are about similar workflows.

Where exact text matters

Exact text matters whenever a memory must point to a specific object rather than a general topic. Common examples include:

Issue IDs and ticket references used in engineering and support workflows.
Invoice, order, contract, and purchase-order numbers used in business workflows.
Database, application, and integration error codes used in troubleshooting.
Customer aliases, account IDs, and tenant names used for scoped recall.
Product SKUs, model numbers, filenames, branch names, and run IDs used by operational agents.

These values are small, but they often carry more authority than nearby prose. A robust agent memory layer should preserve that literal signal while still supporting semantic lookup and exact-match retrieval.

Types of search

Vector search	Keyword search	Hybrid search
Finds by meaning and paraphrase	Finds by exact text (literal identifiers)	Combines semantic and exact-text signals in one ranked retrieval path
Example query: “What blocked the renewal?”	Example query: “INV-48291”	Example queries: “INV-48291” and “What blocked the renewal?”

Table 1: Conceptual search-mode comparison

The differences become clearer when you compare what each mode optimizes for.

The following comparison explains the retrieval modes conceptually. The companion notebook executes the hybrid configuration and evaluates it across exact, semantic, and mixed queries; it is not a benchmark of three separately executed strategies.

Mode	Best at	Weak spot	Agent memory fit
Vector search	Paraphrase, semantic similarity, concept recall	Short identifiers may be low-signal	Good for natural-language recall
Keyword search	Literal identifiers, error codes, aliases, filenames	Brittle when wording changes	Good when exact strings matter
Hybrid search	Natural language plus exact handles	Requires database embedding and managed index setup	Strong fit when both semantic and exact-text retrieval matter

Table 2: Search mode comparison

Use hybrid search when:

Users ask in natural language but refer to exact business objects.
Memories include identifiers, aliases, filenames, error codes, or transaction IDs.
Several memories are semantically similar, but one exact token should decide the result.
The agent needs scoped retrieval across user, agent, tenant, or thread boundaries.

How Oracle AI Agent Memory supports hybrid retrieval for agent memory

Oracle AI Agent Memory exposes hybrid retrieval as a memory-store configuration. The application supplies an Oracle-backed embedder, selects `SearchStrategy.HYBRID`, and chooses an index synchronization mode. The result is one memory layer that can retrieve by meaning and exact text over the same stored memories, while still respecting user, agent, and thread scope.

`OracleDBEmbedder` is important because hybrid search relies on the database-managed embedding and index path. `SearchStrategy.HYBRID` selects the hybrid retrieval backend. `SearchIndexSyncMode` defines when new or updated memory content becomes searchable through the managed index. Together, these settings make retrieval behavior explicit in application code rather than hiding it behind an ad hoc query pipeline.

The configuration below shows the minimum pattern: choose an Oracle-backed embedder, set `SearchStrategy.HYBRID`, and choose how the search index should refresh:



```python 
db_embedder = OracleDBEmbedder( 
    connection=connection, 
    model=CONFIG["ORACLE_DB_EMBEDDING_MODEL"], 
    embedding_dimension=CONFIG["ORACLE_DB_EMBEDDING_DIMENSION"], 
) 
memory = OracleAgentMemory( 
    connection=connection, 
    embedder=db_embedder, 
    memory_extraction_config=MemoryExtractionConfig( 
        extract_memories=False 
    ), 
    schema_policy=SchemaPolicy.CREATE_IF_NECESSARY, 
    search_strategy=SearchStrategy.HYBRID, 
    search_index_sync=SearchIndexSyncMode.ON_COMMIT, 
    memory_store_id="hybrid_blog", 
) 
```

Memory extraction is disabled because the example inserts controlled durable memories directly and focuses specifically on retrieval behavior.

In a typical workflow, the agent writes durable memories with user, agent, or thread scope. Later, search requests use `SearchScope` to restrict eligible records before ranking happens. That separation matters: scope decides what the agent is allowed to remember, while hybrid search decides how eligible memories are ranked.

Hybrid retrieval flow

In an agent workflow, hybrid search is most useful when it sits between memory scoping and context assembly:

Figure 1. Scoped hybrid retrieval flow

`SearchScope` and hybrid search are complementary. Scope determines which records are eligible for retrieval; hybrid search ranks only those eligible records. In the notebook, each evaluation query uses the same generated finance-user and support-agent scope, and retrieval is restricted to durable memory records by passing `record_types=["memory"]`.

This flow is the reason hybrid search fits agent memory well. The application first narrows the memory universe with scope. Hybrid retrieval then combines semantic and exact-text signals only over eligible records. The final output is not just a matching row; it is a ranked context package the agent can use in the next step.



```python 
user_id = "finance_user_123" 
agent_id = "support_finance_agent" 
  
scope = SearchScope(user_id=user_id, agent_id=agent_id) 
  
memory.add_memory( 
    content=( 
        "Northstar Renewals has a renewal blocker: invoice INV-48291 "
        "failed reconciliation after ORA-27102 during month-end processing." 
    ), 
    user_id=user_id, 
    agent_id=agent_id, 
) 
 
async def search_memory(query, scope, max_results=5): 
    return await memory.search_async( 
        query=query, 
        scope=scope, 
        max_results=max_results, 
        record_types=["memory"], 
    ) 
 
exact_results = await search_memory("INV-48291", scope) 
 
semantic_results = await search_memory( 
    "What blocked the Northstar renewal?", 
    scope, 
) 
 ```

Both calls use the same `SearchScope`, hybrid strategy, and managed hybrid index. Only the query style changes.

For example, a user might ask “INV-48291” or “What blocked the renewal?” over the same scoped memories.

A vector-only search may understand the renewal-blocker question but underweight the exact invoice or error code. A keyword-only search may find INV-48291 or ORA-27102 but miss a paraphrased question such as “What blocked the renewal?”

Hybrid search gives both signals a chance to influence ranking, so the agent can retrieve the Northstar Renewals memory with the exact invoice ID, the ORA-27102 error code, and the natural-language explanation.

Testing hybrid retrieval with deliberately similar memories

The companion notebook does not test hybrid retrieval against a single obvious memory. It creates a deliberately similar set of memories, so the retrieval path has to choose between records that share overlapping business, invoice, customer, renewal, and error-code vocabulary.

The notebook stores five durable memories:

A target renewal-blocker memory for Northstar Renewals, containing invoice INV-48291, Oracle error ORA-27102, and the failed reconciliation that blocked the renewal.
A neighboring invoice memory for Northstar Renewals, containing invoice INV-48290, which is marked as paid and does not require follow-up.
A different customer renewal-delay memory for Milan Office Supplies, where the delay is caused by an approval workflow rather than reconciliation.
A general ORA-27102 troubleshooting memory that explains the database error without tying it to the Northstar invoice.
A Northstar customer-alias memory that maps Northstar Renewals to its enterprise account name.

This setup makes the demonstration more realistic. The expected result must outrank memories that are partially similar, such as another Northstar invoice, another renewal delay, or another ORA-27102-related record. That is the behavior an enterprise agent memory system needs: not just finding something related but retrieving the memory that preserves the right operational detail.

Evaluating exact, semantic, and mixed queries

The notebook then evaluates the same hybrid-search configuration across five query scenarios. Each query uses the same `SearchScope`, the same `SearchStrategy.HYBRID` configuration, and the same managed hybrid index. Only the query wording changes.

The five query categories are:

Exact invoice identifier: a query for INV-48291.
Exact Oracle error code: a query for ORA-27102.
Semantic renewal-blocker question: a natural-language question about what blocked the Northstar renewal.
Semantic customer question: a natural-language question about the Milan customer renewal delay.
Mixed customer and invoice question: a question that combines customer context with invoice reconciliation.

The validation checks whether the expected memory appears as the top-ranked result.

Query type	Expected memory	Rank	Validation
Exact invoice ID	Renewal blocker	1	PASS
Exact error code	Error reference	1	PASS
Semantic renewal question	Renewal blocker	1	PASS
Semantic customer question	Milan renewal	1	PASS
Mixed customer and invoice question	Renewal blocker	1	PASS

Table 3: Evaluation results table

This is a controlled functional evaluation, not a statistical benchmark comparing vector, keyword, and hybrid search. The purpose is to show that one hybrid-search configuration can retrieve the expected durable memory across exact, semantic, and mixed query styles.

Reading the notebook results

The results show that exact identifiers remained useful retrieval signals. The invoice query returned the renewal-blocker memory, while the ORA-27102 query returned the general error-reference memory. This matters because both the target renewal memory and the general troubleshooting memory contain the same error code, but they serve different user intents.

The semantic queries also returned the intended memories without requiring the user to repeat the exact stored wording. For example, the renewal-blocker question retrieved the Northstar renewal memory, while the Milan customer question retrieved the separate Milan renewal-delay memory.

The mixed query is the most representative enterprise case. It combines business context with an implied operational object: the user asks which invoice failed reconciliation for Northstar. The expected result is the memory containing INV-48291, ORA-27102, and the failed reconciliation context.

The notebook also displays distance values for the ranked results. In this result set, smaller distance values indicate stronger matches. However, distance should be treated as a retrieval signal for inspection, not as a universal accuracy score. The more important validation is whether the expected memory was returned at rank one for each query.

How hybrid search improves agent memory retrieval

Hybrid search can improve retrieval behavior because it matches how enterprise memory is actually written. A memory record rarely contains only natural-language explanation or only an identifier. It usually contains both: a named handle plus the context that explains why the handle matters.

Higher precision for identifier-driven questions because exact strings can influence ranking.
Better recall for paraphrased questions because semantic search still finds related memory records.
More useful context packages because the agent can retrieve both the handle and the surrounding explanation.
Lower risk of plausible but wrong recall when several memories are semantically similar.
Developers can inspect the ranked memories, metadata, and distances to verify whether the expected operational context was retrieved.

This is especially useful for support agents, finance assistants, operations copilots, developer agents, and workflow agents that must remember prior decisions, tool outputs, system errors, or customer-specific context.

The practical benefit is not only better search quality; it is better agent behavior.

Better retrieval can improve grounding, but the model still needs normal validation and application controls. Hybrid search gives the retrieval layer a stronger chance of selecting the memory that a human operator would have recognized immediately.

Production notes: schema and index setup

For production, treat hybrid search as part of the memory-store design, not as a last-minute query option. The schema should be owned by a dedicated application user with the right privileges, tablespace quota, and deployment controls. Memory scope should also match the application boundary, such as user, agent, tenant, or thread.

`SchemaPolicy.CREATE_IF_NECESSARY` is useful during first-time setup or when upgrading a schema so it can support hybrid search. For a large existing memory store, the first hybrid index build may take time because Oracle needs to prepare the managed search structures over stored memory content. Treat that step as a planned migration rather than a normal application startup task.

After the schema and index are ready, production applications may prefer `SchemaPolicy.REQUIRE_EXISTING`. That lets startup validate the expected schema instead of creating or modifying database objects.

`SearchIndexSyncMode` is a freshness and operations tradeoff:

`ON_COMMIT` is best for notebooks, demos, and interactive applications because newly committed memories become searchable immediately.
`MANUAL` is useful for bulk loads, backfills, and migrations where teams want to ingest many records first and refresh the index on a controlled schedule.
`AUTO` delegates background maintenance to Oracle-managed behavior, which can fit production workloads where some freshness lag is acceptable. `AUTO` is supported for `SearchStrategy.HYBRID`; keyword-only search does not support `AUTO`.

The database-resident embedding model is also part of the production contract. The same model and embedding dimension should be used by `OracleDBEmbedder` and the managed hybrid index so query embeddings and indexed memory vectors remain compatible.

Before publishing an agent that depends on durable memory, test the retrieval path with representative records. Include exact-identifier queries, semantic queries, mixed queries, scoped retrieval checks, and index freshness checks after writes. If the application has many similar records, include distractor memories in the test set so the expected result has to outrank nearby alternatives.

Teams can also pair hybrid retrieval with custom memory extraction when they need more predictable stored facts. Extraction controls what gets remembered; scope controls which records are eligible; hybrid search controls how eligible memories are ranked.

Conclusion

AI agent memory needs more than semantic similarity. Vector search helps agents remember by meaning, but enterprise workflows often depend on exact strings such as invoice numbers, error codes, customer aliases, file names, and transaction IDs. Keyword search helps with those strings, but it can be brittle when users ask in natural language.

Hybrid search gives the memory layer a practical middle ground. It preserves literal identifiers while still supporting semantic recall, so an agent can retrieve both the named object and the context that explains why it matters.

In the companion notebook, one `SearchStrategy.HYBRID` configuration retrieves the expected durable memory across exact, semantic, and mixed query styles. The deliberately similar memory set makes the demo more realistic: the target memory has to outrank records that overlap on customer, invoice, renewal, or error-code vocabulary.

For Oracle AI Agent Memory, `SearchStrategy.HYBRID`, `OracleDBEmbedder`, `SearchScope`, and `SearchIndexSyncMode` provide the core application controls for this pattern. Together, they help developers build memory systems that are scoped, durable, searchable by meaning, and precise when exact text matters.

Run the companion notebook to configure Oracle AI Agent Memory hybrid search, load the deliberately similar memory set, and inspect the ranked results for exact, semantic, and mixed queries.

Frequently Asked Questions

What is Oracle AI Agent Memory?

Oracle AI Agent Memory is a durable memory layer for AI agents. It lets applications store memories, retrieve relevant context later, and use scope controls such as user, agent, and thread scope so the agent searches only eligible memories before ranking results.

Why use hybrid search for agent memory?

Hybrid search is useful for agent memory because many memories contain both meaning and exact text. Vector search helps with paraphrased questions, while keyword search helps with identifiers such as issue IDs, SKUs, invoice numbers, aliases, and error codes. Hybrid search combines both signals so the agent can retrieve the right memory and the surrounding context.

Is vector search still useful for agent memory?

Yes. Vector search remains essential for paraphrase, concept recall, and long natural-language memories. Hybrid search adds exact-text strength instead of replacing vector search.

How do I build hybrid retrieval for agent memory?

Configure OracleAgentMemory with `OracleDBEmbedder`, set `SearchStrategy.HYBRID`, choose `SearchIndexSyncMode`, store scoped memories, then test exact, semantic, and mixed queries.

When should I use keyword-only search?

Use keyword-only search when retrieval is almost entirely literal text matching and embeddings are unnecessary. Many agent memory workloads need both exact strings and semantic context, which makes hybrid search a better default.

What is the difference between scoped retrieval and hybrid search?

Scoped retrieval controls which memories are eligible to be searched. Hybrid search controls how those eligible memories are ranked using both semantic and exact-text signals.

Why does hybrid search require `OracleDBEmbedder`?

The hybrid path uses Oracle-managed indexing and database-side embedding metadata. `OracleDBEmbedder` keeps the application embedder aligned with the database-backed retrieval path.

Which `SearchIndexSyncMode` should I start with?

For notebooks and interactive demos, `ON_COMMIT` is the simplest starting point. For bulk ingestion, `MANUAL` gives more control. `AUTO` is useful when Oracle-managed hybrid index maintenance fits the workload.

What should I test before publishing an agent memory workflow?

Test exact-identifier queries, semantic queries, scoped retrieval, index freshness after writes, and behavior when multiple similar memories exist. The strongest demos show both exact and natural-language retrieval over the same records.

Resources

Building Scalable Multi-Language Applications with Oracle Globally Distributed Database Using OCI-Based Drivers

Anya Summers — Tue, 11 Aug 2026 14:30:21 +0000

Modern enterprise applications are rarely built using a single programming language. A cloud-native application might use Python for AI and analytics, Node.js for REST APIs and C for latency-sensitive services. Despite the diversity of languages, the database-access goals are familiar: efficient connection pooling, intelligent routing, high availability, and predictable scalability.

Oracle Globally Distributed Database (GDD) enables applications to scale horizontally by distributing data across shards while presenting a single logical database to the application. To make these capabilities available consistently across languages, Oracle provides OCI-based (Oracle Call Interface) drivers that expose the same distributed database features through language-native APIs.

In this article, we'll explore how Python, Node.js and C applications use the same OCI foundation to build scalable, shard-aware applications.

Key Takeaways

Python and Node.js in Thick mode and C through OCI(Oracle Call Interface) give developers a consistent core workflow for Oracle Globally Distributed Database: create or reuse sessions, supply shard keys when needed, and release connections predictably. The APIs and feature coverage remain language-specific.
Shard-aware connection routing is consistent across languages. Applications acquire connections using sharding keys (and optionally super sharding keys), allowing Oracle Globally Distributed Database to automatically route requests to the appropriate shard.
Thoughtful pool sizing and high-availability configuration are essential in multi-shard systems: they support session reuse, help avoid uneven connection distribution, and can improve recovery behavior when the required services and client configuration are in place.

Use Python, Node.js, and C for shard-aware connection pooling and resilient database access.

A Common Foundation

One of the strengths of Oracle's language drivers is that they share a common implementation stack. Although the programming APIs differ, they ultimately leverage the same Oracle Call Interface (OCI) capabilities for connection management, sharding, and high availability. In Thick mode, the Python and Node.js drivers use Oracle Client libraries and Oracle Call Interface (OCI). C applications call OCI directly.

This means that developers can choose the programming language best suited to their workload while relying on consistent database behavior.

Why Thick Mode?

Thick mode is the Python and Node.js deployment mode that uses Oracle Client libraries and the Oracle Call Interface (OCI). It gives those applications access to the OCI-based connection behavior that C applications use directly.

High-performance session pooling enables session reuse, reducing connection-creation overhead under load and helping applications manage demand across shards.
Sharding and super sharding keys let the client request a connection for the data-owning shard, without embedding physical shard locations in application logic.
Oracle Net and the native-client or provider capabilities supply the connection behavior needed for enterprise deployments, including the routing information used to establish the right connection.
High availability is configured separately from driver selection. FAN, Application Continuity or Transparent Application Continuity (TAC), and Transparent Application Failover (TAF) can improve failure response and continuity during planned or unplanned events, but their availability and defaults depend on the driver or provider type, client version, database service, and pool or connection configuration.

Initializing Oracle Client enables Thick mode for Python and Node.js only. It does not, by itself, enable high-availability behavior. The same principle applies across C/OCI: configure and test the relevant services, client settings, and pool behavior for the driver in use.

Creating a Scalable Session Pool

Connection pooling is the first step toward scalable access. Instead of creating a new database connection for every request, applications reuse existing sessions, lowering latency and improving throughput. In a multi-shard system, pool configuration also matters because demand may not be evenly distributed across shards. Monitor pool utilization and shard-level workload patterns, then tune capacity for the traffic you actually observe.

The pool sizes below are illustrative only, not recommended defaults. Size pools using expected concurrency, request duration, database capacity, shard distribution, and service-level objectives.

Python

import oracledb

oracledb.init_oracle_client()

pool = oracledb.create_pool(
    user=user,
    password=password,
    dsn=connect_string,
    min=5,
    max=50,
    increment=5,
    getmode=oracledb.POOL_GETMODE_WAIT
)

Node.js

const oracledb = require('oracledb');

oracledb.initOracleClient();

const pool = await oracledb.createPool({
    user,
    password,
    connectString,
    poolMin: 5,
    poolMax: 50,
    poolIncrement: 5
});

C

OCIEnvCreate(...);

OCIHandleAlloc(...);

OCISessionPoolCreate(
    envhp,
    errhp,
    poolhp,
    &poolName,
    &poolNameLen,
    connectString,
    strlen(connectString),
    minSessions,
    maxSessions,
    increment,
    username,
    strlen(username),
    password,
    strlen(password),
    OCI_SPC_HOMOGENEOUS
);

Acquiring Shard-Aware Connections

The key benefit of shard-aware access is not that the application learns where shards live. It is that the client can direct work to the shard that owns the requested data. Supplying the sharding key and, where the data model uses one, the super sharding key helps avoid unnecessary data movement and keeps physical topology out of business logic. The following snippets are abbreviated examples. They omit credentials, error handling, variable declarations, pool shutdown, and full OCI environment and handle management. Production code should add those concerns and should release every acquired connection

Python

# The context manager releases the connection to the pool.
with pool.acquire(
    supershardingkey=[region],
    shardingkey=[customer_id]
) as connection:
    with connection.cursor() as cursor:
        cursor.execute(sql)

Node.js

let connection;
try {
  connection = await pool.getConnection({
    superShardingKey: [region],
    shardingKey: [customerId]
  });
  await connection.execute(sql);
} finally {
  if (connection) await connection.close(); // Return it to the pool
}

C

/* Create an authInfo handle, allocate key descriptors, add values,
   attach both keys, then request the pooled session.
   Error checks are omitted here. */
OCIAuthInfo *authInfo = NULL;
OCIShardingKey *shardKey = NULL, *superShardKey = NULL;

OCIHandleAlloc(envhp, (dvoid **)&authInfo,
               OCI_HTYPE_AUTHINFO, 0, NULL);

OCIDescriptorAlloc(envhp, (dvoid **)&shardKey,
                   OCI_DTYPE_SHARDING_KEY, 0, NULL);
OCIDescriptorAlloc(envhp, (dvoid **)&superShardKey,
                   OCI_DTYPE_SHARDING_KEY, 0, NULL);

OCIShardingKeyColumnAdd(shardKey, errhp, &customerId,
                         sizeof(customerId), SQLT_INT, OCI_DEFAULT);
OCIShardingKeyColumnAdd(superShardKey, errhp, region,
                         strlen((char *)region), SQLT_CHR, OCI_DEFAULT);

OCIAttrSet(authInfo, OCI_HTYPE_AUTHINFO, shardKey, sizeof(shardKey),
           OCI_ATTR_SHARDING_KEY, errhp);
OCIAttrSet(authInfo, OCI_HTYPE_AUTHINFO, superShardKey, sizeof(superShardKey),
           OCI_ATTR_SUPER_SHARDING_KEY, errhp);

OCISessionGet(envhp, errhp, &svcHandle, authInfo,
              poolName, poolNameLen, NULL, 0, NULL, NULL, NULL,
              OCI_SESSGET_SPOOL);

/* Execute work on the routed shard, then release the session. */
OCISessionRelease(svcHandle, errhp, NULL, 0, OCI_DEFAULT);
OCIDescriptorFree(shardKey, OCI_DTYPE_SHARDING_KEY);
OCIDescriptorFree(superShardKey, OCI_DTYPE_SHARDING_KEY);
OCIHandleFree(authInfo, OCI_HTYPE_AUTHINFO);

Although the APIs differ, each performs the same sequence:

Construct the sharding key.
Request a pooled connection.
Allow OCI to route the request to the correct shard.

Comparing the Drivers

The table helps an architect choose the API that matches the application language while preserving the same operating model.

Capability	Python (Thick)	Node.js (Thick)	C
Client / driver	Oracle Client	Oracle Client	Direct OCI calls
Session pool	create_pool()	createPool()	OCISessionPoolCreate()
Shard-aware checkout	pool.acquire()	pool.getConnection()	OCISessionGet()
Sharding / super sharding keys	Language-native parameters	Language-native parameters	OCIShardingKey + OCIAttrSet()
Connection release	Context manager or close()	connection.close()	OCISessionRelease()

The APIs are intentionally language-specific, while the core pooling and shard-aware routing workflow is consistent. Individual feature availability varies by driver mode, client version, database service, and configuration.

Conclusion

Oracle's OCI-based drivers provide a unified foundation for building scalable applications across multiple programming languages. Whether applications are written in Python, Node.js or C, developers have access to the same core capabilities—session pooling, shard-aware routing, high availability, and efficient native connectivity—through familiar language APIs.

The result is a more consistent deployment and operating model across the application stack, with work directed to the data-owning shard and with less duplicated connection-management knowledge between language teams.

Frequently Asked Questions

Why should I use Thick mode instead of Thin mode?

Thin mode is suitable for many Oracle Database applications, but Thick mode uses Oracle Client libraries and the Oracle Call Interface (OCI). For Oracle Globally Distributed Database, it gives Python and Node.js applications access to OCI-based shard-aware connections and advanced connection management. Features such as Fast Application Notification (FAN), Application Continuity, Transparent Application Continuity (TAC), and Transparent Application Failover (TAF) require support from the relevant driver mode and version, database services, and client or pool configuration. Initializing Oracle Client enables Thick mode for Python and Node.js; it does not by itself enable high-availability behavior.

Why do I need sharding keys?

Sharding keys allow Oracle Globally Distributed Database to determine which shard contains the requested data. By supplying sharding keys when acquiring connections, applications are automatically routed to the appropriate shard, minimizing cross-shard communication and improving performance.

Do I need to know which shard my data resides on?

No. Applications simply provide the appropriate sharding key. Oracle Globally Distributed Database handles the routing transparently, allowing developers to focus on application logic rather than shard location.

Do all drivers have the same programming model and capabilities?

They share a consistent core workflow: configure a client and pool, request a connection with routing information when needed, perform work, and release the connection. The APIs are intentionally language-native, and availability of individual features can vary by driver mode, client version, database service, and configuration.

References

End-to-End Agentic AI Observability: Tracing from Agents INTO the Oracle AI Database

Anya Summers — Thu, 30 Jul 2026 14:42:59 +0000

Build one continuous trace across a Spring Boot agent workload, Oracle JDBC, and Oracle AI Database server-side execution.

All source code, configuration, scripts, and supporting docs for the demo are available in the observability folder on GitHub.

Key Takeaways

Oracle AI Database lets the trace continue into database server-side execution instead of stopping at the database edge.
Spring Boot and Micrometer create the application trace context, while Oracle JDBC adds database client spans.
DBMS_OBSERVABILITY exports Oracle AI Database server-side spans to OpenTelemetry-compatible observability tools; this demo uses Jaeger.
Agent metadata, MODULE, ACTION, and CLIENT_IDENTIFIER create a foundation for security and audit correlation.

This walkthrough answers a practical question for agentic applications: can you see one trace that starts with an AI agent request, crosses the Spring Boot and JDBC layers, and continues inside Oracle AI Database? Yes. With Spring Boot, Micrometer, Oracle JDBC observability providers, and DBMS_OBSERVABILITY, the same request can appear in any OpenTelemetry-compatible observability backend as application spans, JDBC spans, and Oracle AI Database server-side spans. This walkthrough uses Jaeger because it is simple to run and easy to inspect, but the same signal can be viewed in any OpenTelemetry-compliant observability tool.

That database-internal span is the important part. Typical database observability stops at the database edge: the application trace shows that JDBC waited on the database, then the user must switch tools and manually investigate inside the database. Oracle AI Database keeps the trace continuous. Developers, DevOps teams, and security reviewers can see how long the database actually took, correlate it to database-side evidence, and use the same trace for performance profiling, troubleshooting, and security context.

Video walkthrough and summary: Tracing from Agents into the Oracle AI Database.

Featured cover diagram: one OpenTelemetry trace from an AI agent request into Oracle AI Database server-side execution.

What We Are Building

The demo app lives in observability/springboot-oracle-db-otel-demo. It is a Spring Boot app with two main endpoints:

Endpoint	Purpose
`GET /trace/roundtrip`	Simple app-to-database trace showing Spring Boot, Micrometer, JDBC, and database server-side spans.
`GET /trace/agent-task/view`	Browser-friendly agentic demo that returns the trace id, agent id, database session metadata, database security context, SQL ID, SQL text, bind values, SQL Monitor preview, `DBMS_XPLAN`, and an embedded Jaeger trace on one page. Jaeger is the demo viewer; the telemetry is OpenTelemetry-compatible.

The intended trace shape is:

Browser or curl
  -> Spring Boot HTTP span
  -> Micrometer observation
  -> Oracle JDBC provider spans
  -> Oracle AI Database DB Server span
  -> HTTPS OTLP endpoint
  -> OpenTelemetry-compatible observability backend (Jaeger in this demo)

Application and JDBC spans flow from the Java process, while database server-side spans flow from Oracle AI Database; both meet in the same OpenTelemetry collector.

The selected database server-side span shows database timing and database-side identity tags in the same trace as the application request.

The demo page keeps the agent task, database session context, database security context, SQL diagnostics, and trace bridge together.

How Do You Set Up and Run the Example?

The demo can run on any Linux environment or VM with enough memory for Oracle Database Free, Jaeger, an HTTPS proxy, and the Spring Boot app. You can also use an Autonomous Database in Oracle Cloud instead of a local database; the important requirements are JDBC connectivity and a database server-side exporter that can reach an HTTPS OTLP endpoint.

A clean OCI Linux VM verification with Oracle Database Free 23.26.2.0 produced the expected Spring Boot, Oracle JDBC, and oracle-db server-side spans without setting hidden KSTRC instance parameters. The normal setup below uses documented DBMS_OBSERVABILITY configuration, a reachable HTTPS OTLP endpoint, network ACLs, and the database disttrc trust wallet.

Install Java 25, Maven, Git, Podman, curl, jq, and OpenSSL.
Run Oracle Database Free on Linux, or provision an Autonomous Database in Oracle Cloud.
Clone the source from github.com/oracle-devrel/oracle-ai-for-sustainable-dev and use the observability folder.
Start Jaeger with OTLP HTTP enabled. In the sample setup, Jaeger receives app and JDBC spans on http://127.0.0.1:4318/v1/traces.
Put a small HTTPS proxy in front of Jaeger's OTLP endpoint for database server-side export. The sample uses NGINX as otel-tls-proxy, forwarding https://otel-tls-proxy:4318/v1/traces to Jaeger's http://oracle-db-otel-jaeger:4318/v1/traces.
Configure DBMS_OBSERVABILITY, network ACLs, and the database wallet/trust path so Oracle AI Database can push server-side spans to the HTTPS OTLP endpoint.
Configure, build, and run the Spring Boot app, then open the agent task view or call the JSON endpoint to generate a trace.

sudo dnf install -y podman git java-25-openjdk java-25-openjdk-devel maven jq curl openssl
export JAVA_HOME=/usr/lib/jvm/java-25-openjdk
export PATH="$JAVA_HOME/bin:$PATH"

git clone https://github.com/oracle-devrel/oracle-ai-for-sustainable-dev.git
cd oracle-ai-for-sustainable-dev/observability/springboot-oracle-db-otel-demo
mvn -DskipTests package

Keep database credentials and runtime endpoints outside the repository:

export DB_URL='jdbc:oracle:thin:@//127.0.0.1:1521/FREEPDB1'
export DB_USERNAME='FINANCIAL'
export DB_PASSWORD='<app-user-password>'
export OTLP_TRACES_ENDPOINT='http://127.0.0.1:4318/v1/traces'
export TRACE_SAMPLE_PROBABILITY=1.0
export ORACLE_JDBC_SERVER_TELEMETRY_TRACES_ENABLED=true
export ORACLE_JDBC_SERVER_TELEMETRY_LOGGING_ENABLED=false
export ORACLE_JDBC_TRACEPARENT_CLIENT_INFO_ENABLED=true
export ORACLE_JDBC_TRACELEVEL_CLIENT_INFO_ENABLED=true

java -jar target/springboot-oracle-db-otel-demo-0.0.1-SNAPSHOT.jar

curl -sS http://127.0.0.1:8080/trace/roundtrip | jq .

curl -sS \
  'http://127.0.0.1:8080/trace/agent-task?agentId=claims-investigator-agent&task=investigate_payment_anomalies' \
  | jq .

For the cohesive browser view, open:

http://127.0.0.1:8080/trace/agent-task/view?agentId=claims-investigator-agent&task=investigate_payment_anomalies

Maven Dependencies and the Oracle JDBC Observability Provider

The app uses Java 25, the Oracle JDBC 23.26 line from Maven Central, the published Maven Central ojdbc-provider-observability artifact, and the Oracle JDBC 17 production dependency.

<properties>
  <java.version>25</java.version>
  <oracle.jdbc.version>23.26.2.0.0</oracle.jdbc.version>
  <oracle-database.version>${oracle.jdbc.version}</oracle-database.version>
  <ojdbc.provider.observability.version>1.1.0</ojdbc.provider.observability.version>
</properties>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

<dependency>
  <groupId>io.micrometer</groupId>
  <artifactId>micrometer-tracing-bridge-otel</artifactId>
</dependency>

<dependency>
  <groupId>io.opentelemetry</groupId>
  <artifactId>opentelemetry-exporter-otlp</artifactId>
</dependency>

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc17-production</artifactId>
  <version>${oracle.jdbc.version}</version>
  <type>pom</type>
</dependency>

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc-provider-observability</artifactId>
  <version>${ojdbc.provider.observability.version}</version>
</dependency>

Spring Boot Actuator creates the observation infrastructure, Micrometer bridges those observations to OpenTelemetry, and the OTLP exporter sends spans to the collector. The oracle-database.version property keeps Oracle JDBC, UCP, wallet/security, and related transitive artifacts aligned on 23.26.2.0.0.

The Oracle JDBC observability provider implements the JDBC driver TraceEventListener interface and publishes JDBC events into OpenTelemetry. Those events include database round trips and connection behavior, with attributes such as connection id, database operation, database user, tenant, and SQL ID. SQL text and connection details are treated as sensitive and are disabled by default unless explicitly enabled.

How Do the Spring Boot and JDBC Spans Get Created?

The Oracle JDBC OpenTelemetry extensions expect the application to already have an active OpenTelemetry context. They do not create the root application trace by themselves. In this demo, Spring Boot Actuator and Micrometer provide the HTTP and application observations, and the Oracle JDBC provider adds child spans for JDBC driver events such as database round trips. The provider also propagates the trace context to Oracle AI Database so the database can export its server-side span into the same trace.

If you do not want to add application instrumentation directly, use the OpenTelemetry Java agent for zero-code Java instrumentation. The Oracle JDBC extensions can then add database round-trip spans as children of the current application span and propagate that context to the server.

Spring Boot sends application and JDBC spans through OTLP HTTP. The demo sends them to Jaeger, but the same OpenTelemetry signal can be collected by any OpenTelemetry-compatible observability platform. Oracle JDBC provider properties enable the JDBC trace listener and identify the Java service in the collector.

spring:
  application:
    name: springboot-oracle-db-otel-demo
  datasource:
    url: ${DB_URL}
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    oracleucp:
      connection-factory-properties:
        "[oracle.jdbc.provider.traceEventListener]": observability-trace-event-listener-provider
        "[oracle.jdbc.provider.traceEventListener.unique_identifier]": springboot-oracle-db-otel-demo

management:
  otlp:
    tracing:
      endpoint: ${OTLP_TRACES_ENDPOINT:http://localhost:4318/v1/traces}
  tracing:
    sampling:
      probability: 1.0

How Do We Export Database Server-Side Spans?

The database server-side exporter needs a reachable HTTPS OTLP endpoint. The verified Linux demo uses Jaeger, which accepts OTLP HTTP on port 4318, and puts a small HTTPS proxy in front of it for database server-side export. In the sample, that proxy is NGINX with a trusted local certificate:

Oracle AI Database -> https://otel-tls-proxy:4318/v1/traces
otel-tls-proxy -> http://oracle-db-otel-jaeger:4318/v1/traces

Replace Jaeger with another OpenTelemetry-compatible collector or backend by changing the OTLP endpoint. The important requirement is that Oracle AI Database can reach the endpoint over HTTPS for server-side trace export.

Configure the database with DBMS_OBSERVABILITY, grant network ACL access to the user executing the SQL session, and place the trusted wallet where the distributed trace exporter expects it: WALLET_ROOT/<PDB_GUID>/disttrc.

begin
  dbms_observability.add_endpoint(
    endpoint_type => dbms_observability.otel_traces,
    endpoint => 'https://otel-tls-proxy:4318/v1/traces',
    credential_name => null);

  dbms_observability.enable_endpoint('https://otel-tls-proxy:4318/v1/traces');
  dbms_observability.enable_service_option(dbms_observability.capture_traces);
  dbms_observability.enable_service_option(dbms_observability.show_extra_metadata);
  dbms_observability.enable_service(dbms_observability.all_services);
end;
/

A clean verification run on Oracle Database Free 23.26.2.0 showed that the documented DBMS_OBSERVABILITY endpoint setup, the HTTPS OTLP proxy, the network ACL, and the WALLET_ROOT/<PDB_GUID>/disttrc trust wallet were sufficient for database server-side spans. Older local diagnostic runs used hidden KSTRC parameters while debugging export failures, but those settings are not part of the normal demo setup.

How Does JDBC Propagate the Trace Context?

The app uses the published ojdbc-provider-observability dependency. It is currently necessary to force a server telemetry state change before enabling traces, so the driver piggybacks the telemetry state to the database.

EnumSet<OracleConnection.ServerTelemetry> requestedTelemetry =
    EnumSet.of(OracleConnection.ServerTelemetry.Traces);

oracleConnection.setServerTelemetry(
    EnumSet.noneOf(OracleConnection.ServerTelemetry.class));
oracleConnection.setServerTelemetry(requestedTelemetry);

How Do We Make the Database Span Useful?

A generic DB Server span proves that the trace crossed into the database, but the demo makes it more useful by setting database session fields before the SQL runs. The app uses the Oracle JDBC end-to-end metrics API for this metadata, so it does not need a separate PL/SQL round trip just to set MODULE, ACTION, and CLIENT_IDENTIFIER.

String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "agent:claims-investigator-agent";
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = "agent-workload-query";
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "traceId=<trace-id>";
metrics[OracleConnection.END_TO_END_ECID_INDEX] = "<trace-id>";
oracleConnection.setEndToEndMetrics(metrics, (short) 0);

In Jaeger, the orange oracle-db / DB Server span then carries database-side evidence such as oracle.db.module, oracle.db.action, oracle.db.session.id, oracle.db.pdb, and oracle.db.query.sql.id.

Some of these names can also appear in client-side JDBC spans because the driver can receive database metadata and the application deliberately sets MODULE, ACTION, and CLIENT_IDENTIFIER. The important difference is provenance and timing: the oracle-db / DB Server span is emitted by Oracle AI Database itself, with database-side duration, response status, session context, and SQL identity attached to the server's view of the work. A client span can time the Java round trip, but it cannot by itself prove the database server's own span timing in the same trace.

How Do We Link the Trace to SQL Diagnostics?

The demo returns a canonical SQL ID from the cursor used for its DBMS_XPLAN and SQL Monitor lookups. Oracle JDBC spans and database-exported spans can also expose oracle.db.query.sql.id, but do not assume that every layer will report the same cursor. A verified run for the screenshots in this article returned one canonical SQL ID on the demo page and a different SQL ID on the selected oracle-db / DB Server span.

Keep the correlation intact with the trace id, database ECID, MODULE, ACTION, and session id. Then use the canonical SQL ID returned by the page for its V$SQL, bind capture, SQL Monitor, and DBMS_XPLAN drill-down. A SQL ID on a particular span remains useful evidence, but it identifies that span's database cursor rather than a guaranteed cross-layer identifier.

Evidence	Where it comes from
Visual trace and database server evidence	Trace id, ECID, module, action, session id, and any layer-specific `oracle.db.query.sql.id` attribute
Full SQL text	Canonical `SQL_ID -> V$SQL.SQL_FULLTEXT`
Captured bind samples	`SQL_ID -> V$SQL_BIND_CAPTURE`
Runtime plan	`DBMS_XPLAN.DISPLAY_CURSOR`, the SQL execution plan with optimizer steps, estimated and actual rows, I/O, memory, and timing
SQL Monitor report	`DBMS_SQL_MONITOR.REPORT_SQL_MONITOR`

The demo also calls DBMS_SQL_MONITOR.BEGIN_OPERATION around the agent task. That call is different from setting JDBC end-to-end metrics: it creates a named database operation for SQL Monitor so the agent task has a database-side operation id and attributes. The trace and SQL-ID bridge still work without it, because the workload SQL uses the MONITOR hint and the app queries V$SQL, V$SQL_BIND_CAPTURE, SQL Monitor, and DBMS_XPLAN by SQL ID. Keeping BEGIN_OPERATION makes the demo stronger by giving SQL Monitor a named server-side operation for the agent task; removing it would simplify privileges and code, but would lose that named database-operation evidence.

SQL_ID lets the demo drill from the trace into full SQL text, bind-sample notes, and SQL Monitor output.

SQL Monitor and DBMS_XPLAN make the traced database span useful for performance profiling and SQL diagnostics.

What Does the Agentic AI Demo Page Show?

The browser endpoint keeps the demo cohesive by returning the agent story, database diagnostics, and Jaeger trace on one page:

http://localhost:8080/trace/agent-task/view?agentId=claims-investigator-agent&task=investigate_payment_anomalies

The page includes:

Agent id and task name.
Trace id and SQL ID bridge.
Database session context, including module/action/client identifier.
Application bind values used by this exact request.
Full SQL Text heading labeled from SQL_ID -> V$SQL.SQL_FULLTEXT.
Captured Bind Samples heading labeled from SQL_ID -> V$SQL_BIND_CAPTURE.
SQL Monitor and DBMS_XPLAN output.
An embedded Jaeger trace loaded after a short delay so spans have time to flush.

What Does Success Look Like?

In the verified Linux environment, Jaeger showed a single trace containing both services: springboot-oracle-db-otel-demo from the Java process and oracle-db from Oracle AI Database server-side export.

{
  "services": [
    "oracle-db",
    "springboot-oracle-db-otel-demo"
  ],
  "ops": [
    "DB Server",
    "Execute query",
    "Fetch a row",
    "http get /trace/agent-task",
    "oracle.demo.agent-database-investigation"
  ]
}

Oracle Database Metrics Exporter (External)

Server-side OpenTelemetry spans answer request-scoped questions: which agent request reached the database, how long that database work took, which SQL ID was involved, and how the database span fits into the same trace as the Java service. For fleet-level and time-series questions, pair that with the external Oracle AI Database Metrics Exporter from oracle/oracle-db-appdev-monitoring.

The exporter is a separate process or container that connects to one or more Oracle AI Database instances and exposes database metrics in standard Prometheus/OpenTelemetry formats. Oracle's exporter project includes default metrics, custom metrics defined in YAML or TOML, support for single instance, clustered, Autonomous, cloud, on-premises, Kubernetes, and container-based databases, plus sample Grafana dashboards.

Signal	Best question	How it complements this demo
Server-side OpenTelemetry trace export	What happened during this specific agent request?	Shows the database span in the same trace as Spring Boot, JDBC, SQL ID, module/action, and agent metadata.
Oracle AI Database Metrics Exporter	What is happening across the database over time?	Adds dashboards and alerts for resource use, sessions, waits, workload health, and custom business or security metrics.

A practical setup is to run both signals into the same observability platform. Keep this demo's OTLP trace path for request-level causality, and deploy the exporter beside the database environment for database metrics:

Spring Boot + Oracle JDBC provider -> OTLP traces -> collector/backend
Oracle AI Database server-side exporter -> HTTPS OTLP traces -> collector/backend
Oracle AI Database Metrics Exporter -> Prometheus/OpenTelemetry metrics -> collector/backend

Used together, traces and metrics give a stronger agentic AI operations story: the trace explains one request all the way into Oracle AI Database, while the metrics exporter shows whether that request happened during broader database pressure, session growth, wait spikes, alert-log events, or workload-specific metric changes.

How Does the Trace Connect to Security and Auditing?

The demo correlates its trace with Oracle AI Database Deep Data Security. Observability shows what the agent did; database security explains why Oracle AI Database allowed or denied it. The trace already carries the join keys needed for that correlation:

trace.traceId identifies the distributed request.
agent.id identifies the AI actor in the application span.
oracle.db.module=agent:<agent-id> identifies the same actor inside Oracle AI Database.
oracle.db.action identifies the database phase.
CLIENT_IDENTIFIER=traceId=<trace-id> gives database security and audit policy a compact correlation value.
oracle.db.query.sql.id bridges the trace to SQL text, SQL Monitor, DBMS_XPLAN, and later audit records.

The sample app now includes a Database Security Context panel backed by a real password-authenticated local Deep Data Security end user. It works without Entra ID, OCI IAM, OAuth tokens, or a JDBC EndUserSecurityContext call. The page correlates the trace with ORA_END_USER_CONTEXT.username, the associated schema, MODULE, ACTION, CLIENT_IDENTIFIER, enabled database roles, effective privileges, the granted data role, the applicable data grant, and V$END_USER_DATA_ROLE for this request. A row-filtering proof runs in that same traced session and shows both the number of rows allowed by the data grant and zero visible rows for other agents.

The panel deliberately separates regular Oracle Database roles from Oracle AI Database Deep Data Security data roles. A role such as SELECT_CATALOG_ROLE is a normal session role and appears in SESSION_ROLES. A DDS data role such as AGENT_CLAIMS_INVESTIGATOR appears in DBA_DATA_ROLES and receives data grants in DBA_DATA_GRANTS. The local setup script observability/sql/setup_local_deep_data_security.sql creates the local end user claims-investigator-agent, assigns AGENT_CLAIMS_INVESTIGATOR with GRANT DATA ROLE, and grants that role SELECT access to only the agent's event-log rows. The FINANCIAL database user remains the owning schema rather than the runtime principal.

A Deep Sec local end user is distinct from a conventional database user. Data roles cannot be granted directly to the FINANCIAL schema account. Direct password login as the local end user establishes the end-user security context in Oracle AI Database and activates the assigned data role without application-side token propagation. The Entra ID and OCI IAM provider/API examples remain the right pattern when an application must propagate externally managed end-user identities through a shared pool user.

A security-aware version can run the same agent workload against protected tables, then show effective database user, enabled roles, policy decisions, rows allowed or filtered, and a denied-operation example. After that, a scoped audit policy can record the same actor, module, action, SQL ID, and trace id so the page shows one story: this agent made this request, Oracle AI Database enforced these policies, and the audit trail recorded it.

The trace itself is not auditing. It is observability: useful for debugging, performance, causality, and visual correlation. Auditing requires durable database records, such as Unified Auditing or Fine-Grained Auditing entries, retained under a security-controlled policy. The best pattern is to use traces to explain the execution path and audit records to prove the security-relevant facts.

A companion design note expands this direction: Agent Security Observability With Oracle AI Database.

How Can This Connect to Model Observability?

Agent observability should not stop at infrastructure traces. The same task can also be correlated with model-level telemetry: prompt, model, tool calls, retrieval steps, latency, token usage, evaluator scores, and final answer quality. Tools such as LangSmith, OpenTelemetry-based GenAI semantic conventions, or enterprise AI observability platforms can capture those model and agent workflow details.

The useful pattern is to carry the same task or trace identifier through the agent workflow, the Java service, Oracle JDBC, and Oracle AI Database. Then one investigation can answer: which agent ran, which prompt or plan it used, which tool call reached the database, how long the database took, which SQL ran, and which database security policies applied. That ties model observability, application observability, database observability, and security review into one storyline.

References

FAQ

Does this replace database auditing?

No. The trace is observability, not an audit trail. Use Oracle AI Database Unified Auditing or Fine-Grained Auditing when you need durable security evidence about users, roles, objects, SQL, and policy decisions.

Do I have to use Jaeger?

No. Jaeger is used here because it is easy to run and inspect, but the demo exports OpenTelemetry traces. The same approach can feed any OpenTelemetry-compatible observability tool that can receive the app/JDBC spans and the Oracle AI Database server-side spans.

What makes this different from ordinary database tracing?

Ordinary application traces often stop at the database edge. Oracle AI Database can export database server-side spans into the same trace, so the database portion is visible without breaking continuity or switching immediately to a separate diagnostic workflow.

Why frame this as an AI agent workload?

Agentic applications need explainability across application and database boundaries. Adding agent id, task, module, action, client identifier, and SQL_ID makes the trace useful for operations, security review, and future audit correlation.

Develop Database-Enforced End-User Auth with Oracle AI Database Deep Data Security and Java

Anya Summers — Thu, 30 Jul 2026 14:34:15 +0000

A practical walkthrough for propagating Microsoft Entra identity through a Spring Boot application via a JDBC connection to Oracle AI Database, which enforces row, column, and cell-level access rules.

Source code and video

All source code for the app is available in the GitHub repo.

Video walkthrough (8 minutes): watch directly on YouTube if the embedded player is unavailable.

Key Takeaways

Pooled JDBC connections can carry end-user identity when application code or ojdbc-provider-spring attaches an Oracle JDBC end-user security context before SQL runs.
The OAuth on-behalf-of flow produces a separate database-access token, so the user token proves the browser user while the database token proves access to Oracle AI Database.
Microsoft Entra app roles such as EMPLOYEES and MANAGERS map to Oracle Deep Data Security data roles, letting the same SQL return different rows based on the signed-in user.
The Java app stays role-agnostic: Oracle AI Database becomes the enforcement point for row, column, and cell-level authorization.

Yes—an enterprise Spring Boot application can keep pooled JDBC connections while Oracle AI Database enforces authorization for each signed-in Microsoft Entra user. The application propagates user and database tokens; Deep Data Security applies declarative row, column, and cell-level rules when SQL executes.

Microsoft Entra identity flows through Spring Boot into Oracle AI Database, where Deep Data Security enforces fine-grained access.

Why This Matters

Most enterprise Java services use a connection pool. That is good engineering: fast, stable, observable, and easy to operate. It also means every request may reach the database through the same application database account.

That is where authorization gets tricky. If Emma and Marvin both use the same pooled database user, the database cannot naturally tell which rows belong to Emma, which columns Marvin may see, or which values a manager may update.

One option is to push that logic into every application query. Another is to create separate data sources or connection pools per user. Neither is a good fit for real enterprise systems.

Oracle Deep Data Security changes the shape of the problem. The application still uses a pooled connection, but it attaches an end-user security context to the JDBC connection before SQL runs. Oracle AI Database evaluates declarative data grants at execution time.

This matters even more for agentic applications. Agents, copilots, and natural-language SQL tools can fan out into many possible data paths. With Deep Data Security, the database remains the final policy enforcement point for fine-grained row, column, and cell-level access.

The important idea: the Spring Boot app does not rewrite SQL or implement authorization logic. It propagates identity and tokens to Oracle AI Database, which enforces authorization policies.

This walkthrough includes the Microsoft Entra setup explicitly instead of only linking to the Microsoft documentation. That part is easy to get subtly wrong, and writing it down forced me to understand it better too.

A good starting point, and the one I extended to create this Java app, is Richard C. Evans's excellent LiveLabs FastLab: Identity-Aware Database Access with Microsoft Entra ID and Oracle Deep Data Security. While I am giving credit due, I will mention that Michael McMahon is the fantastic and friendly developer who wrote the Java support for this Deep Data Security feature.

What We Are Building

The demo is a Spring Boot application that performs four main jobs:

Authenticates a browser user with Microsoft Entra ID.
Requests a database-access token using the OAuth 2.0 on-behalf-of flow.
Attaches both tokens to an Oracle JDBC connection with EndUserSecurityContext.
Runs a normal SQL query against an HR table while Oracle AI Database enforces data grants.

The repo includes two aligned demos, both using Oracle UCP for pooled JDBC connections:

Explicit JDBC API version: application code obtains both tokens and calls OracleConnection.setEndUserSecurityContext(...) and clearEndUserSecurityContext().
ojdbc-provider-spring provider/SPI version: Oracle JDBC reads the current Spring Security context and supplies the end-user security context through the provider SPI.

Runtime Flow

The browser opens GET /deepsec/query.
Spring Security redirects the browser to Microsoft Entra ID.
The user signs in.
Spring Security receives an authorization code and exchanges it for the user's Entra access token.
The backend exchanges that user token for a separate database-access token using OAuth 2.0 on-behalf-of.
Oracle JDBC receives an EndUserSecurityContext containing both tokens, either from explicit application code or through the Spring provider SPI.
Oracle AI Database validates the context, activates mapped data roles from the Entra roles claim, and enforces data grants while SQL runs.
The explicit API app clears the context before returning the connection to the pool; the provider manages that context lifecycle through the SPI.

Outcome: the application remains role-agnostic. Authorization logic is removed from Java code. Oracle AI Database becomes the enforcement point.

Identity and Token Model

Understand the Token Model Before OBO

The on-behalf-of flow is easier to follow if you separate the two tokens early:

Token	What it proves	Audience / important claims
User token	Proves the signed-in browser user to the Spring Boot application. The backend uses it as the assertion for OBO.	Audience is the Spring Boot app. It is requested during browser login.
Database-access token	Proves that the request is addressed to the Oracle AI Database protected resource.	Audience is the database/API app. For this demo, it should be v2 and include the roles claim used for DDS role mapping.

Both tokens matter. The explicit API demo creates the context with EndUserSecurityContext.createWithToken(databaseAccessToken, endUserToken); the provider demo supplies the equivalent context through the JDBC provider SPI.

Real Users and Sample HR Records

Real Entra users

The browser login uses a real Microsoft Entra user. That user receives app-role assignments on the database/API Enterprise Application, such as EMPLOYEES or MANAGERS.

Sample HR records

Emma, Marvin, Charlie, and Dana are sample HR rows in the database, not Entra users. For the employee demo, one HR row is mapped to the value Oracle sees in ORA_END_USER_CONTEXT.username.

Setup order: the remaining sections configure the database, Microsoft Entra, and then the Spring Boot implementations before running the demo.

Database Setup

1. Create an Oracle AI Database if You Do Not Have One

You need an Oracle AI Database with Deep Data Security support. Any 23.26.2 or later database is fine for this walkthrough, including Autonomous Database, Oracle AI Database Free in a container, or another local or cloud database you can reach from the Spring Boot app.

2. Configure Microsoft Entra Trust

Oracle AI Database needs to trust the Entra tenant and the database/API app registration before it can validate the database-access token. Choose the database-admin setup script for your environment:

sql-entraid/01_enable_adb_entra_external_authentication.sql uses DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION for Autonomous Database. Edit its DEFINE values and run it once as ADMIN.
sql-entraid/01_enable_database_free_entra_external_authentication.sql uses IDENTITY_PROVIDER_TYPE and IDENTITY_PROVIDER_CONFIG for Oracle AI Database Free or another non-ADB installation. Run it once as SYSDBA or equivalent.

For non-ADB databases, keep the domain-qualified Application ID URI aligned across the database/API app registration, DEEPSEC_ENTRA_DATABASE_SCOPE, and the optional tnsnames.ora azure_db_app_id_uri value.

-- Autonomous Database, run once as ADMIN after editing DEFINE values:
@security/deepdatasecurity-api-version/sql-entraid/01_enable_adb_entra_external_authentication.sql

-- Oracle AI Database Free container, run once as SYSDBA or equivalent:
@security/deepdatasecurity-api-version/sql-entraid/01_enable_database_free_entra_external_authentication.sql

Verify that the database picked up the identity provider:

SELECT name, value
  FROM v$parameter
 WHERE name IN ('identity_provider_type', 'identity_provider_config');

Grant the application connection-pool user only the privileges it needs to connect and attach an end-user security context:

GRANT CREATE SESSION TO hr_app_user;
GRANT CREATE END USER SECURITY CONTEXT TO hr_app_user;

3. Map Entra App Roles to Deep Data Security Roles

In the database, Microsoft Entra app roles map to Oracle Deep Data Security data roles:

CREATE OR REPLACE DATA ROLE hrapp_employees
  MAPPED TO 'AZURE_ROLE=EMPLOYEES';

CREATE OR REPLACE DATA ROLE hrapp_managers
  MAPPED TO 'AZURE_ROLE=MANAGERS';

If the signed-in user has the Entra app role EMPLOYEES, Oracle activates HRAPP_EMPLOYEES. If that same user later has the Entra app role MANAGERS, or a different signed-in user has MANAGERS, Oracle activates the manager data role. Users can also have multiple app roles.

4. Create the Data Grants

The policy itself lives in SQL. For an employee, the row predicate says a user can see rows where the table username matches ORA_END_USER_CONTEXT.username:

CREATE OR REPLACE DATA GRANT hr.HRAPP_EMPLOYEES_ACCESS
  AS SELECT, UPDATE(phone_number, first_name)
  ON hr.employees
  WHERE upper(user_name) = upper(ORA_END_USER_CONTEXT.username)
  TO hrapp_employees;

For managers, the policy permits access to direct reports while excluding sensitive columns such as ssn:

CREATE OR REPLACE DATA GRANT hr.HRAPP_MANAGER_ACCESS
  AS SELECT (ALL COLUMNS EXCEPT ssn),
     UPDATE (salary, department_id, first_name)
  ON hr.employees
  WHERE manager_id = ORA_END_USER_CONTEXT.HR.EMP_CTX.ID
  TO hrapp_managers;

Notice what is not happening here: the Java service or AI agent is not building custom SQL for employees and managers. The app can issue the same business query, and Oracle filters or restricts the result according to the active end-user security context.

Optional Connection Metadata in tnsnames.ora

This step is optional. The provider demo and its Deep Data Security flow do not require Entra metadata in tnsnames.ora; use it when you want direct JDBC Thin interactive Entra sign-in or centrally managed TNS connection metadata.

A wallet's tnsnames.ora entry can carry that Entra-specific connection metadata. The key additions are token_auth=azure_interactive and azure_db_app_id_uri=api://<DB_APP_CLIENT_ID> inside the security block. A mydb_high alias would look like this:

mydb_high =
  (description=
    (retry_count=20)(retry_delay=3)
    (address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
    (connect_data=(service_name=asdf_mydb_high.adb.oraclecloud.com))
    (security=
      (ssl_server_dn_match=yes)
      (token_auth=azure_interactive)
      (azure_db_app_id_uri=api://4ad8a6b8-asdf-asdf-asdf-5d14bcc22c8a)))

Use your own service name and database/API app ID URI. token_auth=azure_interactive is useful for direct JDBC Thin testing because the driver can open the browser-based Entra sign-in flow. azure_db_app_id_uri tells the driver which Entra resource represents the database.

The Spring Boot demo itself uses a normal pooled application database user and attaches the Deep Data Security context programmatically, so I keep a separate application alias without token_auth=azure_interactive for the UCP pool.

Entra Setup

All of the identity setup below happens in the Azure Portal under Microsoft Entra ID. You will move between App registrations and Enterprise applications.

App registration responsibilities

Registration / app	Responsibility	Where assignments happen
Spring Boot app registration	Represents the Java web application and OAuth client. It owns the redirect URI, client ID, client secret, and application scope.	It requests permission to call the database/API app on behalf of the user.
Database/API app registration	Represents Oracle AI Database as the protected resource. It defines the database scope and app roles such as EMPLOYEES and MANAGERS.	User or group role assignments happen on the database/API Enterprise Application because that is the resource Oracle validates.

1. Create the Spring Boot app registration

Create a separate app registration for the Spring Boot application. Add a Web redirect URI:

http://localhost:8080/login/oauth2/code/entra

For the provider-version browser UI, also add a SPA redirect URI:

http://localhost:18080

Expose an API for the Spring Boot app too, using:

api://<SPRING_BOOT_APP_CLIENT_ID>

Add a delegated user_impersonation scope. This scope is requested during browser login so the resulting token has the Spring Boot app as its audience. That is required for the on-behalf-of exchange.

Figure 1. Microsoft Entra app registration overview for the DeepSec Sample Spring Boot App with identifiers redacted.

2. Create the database/API app registration

Create an app registration named something like Oracle AI Database DeepSec API. Set its Application ID URI to the default form:

api://<DB_APP_CLIENT_ID>

That default api://... URI is the Autonomous Database path I use in this walkthrough. If you are targeting Oracle AI Database Free or another non-ADB database, use a domain-qualified https://... Application ID URI instead and keep the later database configuration, scope, and tnsnames.ora values aligned.

Add a delegated scope named user_impersonation. Add app roles named EMPLOYEES and MANAGERS. These are the role values Oracle later sees in the database-access token and maps to DDS data roles.

Figure 2. Microsoft Entra enterprise application overview for the Oracle AI Database DeepSec API with identifiers redacted.

Figure 3. Azure Portal Create app role panel for an EMPLOYEES role.

Figure 4. Azure Portal app roles page showing EMPLOYEES and MANAGERS roles.

In the database/API app manifest, set requestedAccessTokenVersion to 2. In older Azure AD Graph and portal references this is often called accessTokenAcceptedVersion. It tells Microsoft Entra which access-token format the resource app supports. For this demo, the database-access token should decode with ver=2.0 and an aud value matching the database/API app client ID.

"requestedAccessTokenVersion": 2

I also added the upn optional claim for access tokens so the end-user context has a stable username value to compare against the sample hr.employees.user_name column.

Figure 5. Azure Portal token configuration blade adding the upn optional claim to access tokens.

3. Connect the two registrations

In the Spring Boot app registration, open API permissions, add a permission from My APIs, choose the database/API registration, and grant its delegated user_impersonation scope.

Figure 6. Azure Portal API permissions page showing Microsoft Graph User.Read and the Oracle AI Database DeepSec API user_impersonation scope.

Then go to Enterprise applications, open the database/API enterprise application, and assign users or groups to EMPLOYEES or MANAGERS. For database enforcement, the important assignment is on the database/API enterprise application, because that is the resource for the database-access token Oracle validates.

Figure 7. Azure Portal Enterprise application Users and groups page showing a user assigned to the EMPLOYEES role.

The Spring Boot YAML in the next section binds these tenant, client, secret, database-scope, application-scope, and browser-client values from environment variables. The provider-version browser UI can use the same client ID or a separate public-client app registration.

The Spring Boot Code

Oracle UCP Connection Pooling

Both demos let Spring Boot create Oracle UCP from spring.datasource properties. The explicit API demo sets and clears EndUserSecurityContext in service code; the provider demo adds its provider settings through spring.datasource.oracleucp.connection-properties. The Java side uses the 23.26.2.0.0 JDBC/UCP release, so keep the database and driver at that level or later when troubleshooting identity behavior.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc17-production</artifactId>
  <version>${oracle.jdbc.version}</version>
  <type>pom</type>
</dependency>

The Oracle production dependency POM keeps the JDK 17 JDBC driver, UCP, wallet support, and related companion artifacts on the same release line.

Explicit API Version

OAuth2 client and database-token configuration

The explicit API application uses Spring Security OAuth2 login for the browser user and a separate deepsec.entra-id block for the on-behalf-of database-token exchange:

spring:
  security:
    oauth2:
      client:
        registration:
          entra:
            client-id: ${DEEPSEC_ENTRA_CLIENT_ID:}
            client-secret: ${DEEPSEC_ENTRA_CLIENT_SECRET:}
            client-authentication-method: client_secret_post
            authorization-grant-type: authorization_code
            redirect-uri: ${DEEPSEC_ENTRA_REDIRECT_URI:{baseUrl}/login/oauth2/code/{registrationId}}
            scope: ${DEEPSEC_ENTRA_APPLICATION_SCOPE:openid,profile,email}
        provider:
          entra:
            authorization-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/authorize
            token-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/token
            jwk-set-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/discovery/v2.0/keys
            user-name-attribute: sub

deepsec:
  entra-id:
    tenant-id: ${DEEPSEC_ENTRA_TENANT_ID:}
    client-id: ${DEEPSEC_ENTRA_CLIENT_ID:}
    client-secret: ${DEEPSEC_ENTRA_CLIENT_SECRET:}
    database-scope: ${DEEPSEC_ENTRA_DATABASE_SCOPE:}

Explicit security-context lifecycle

The service first receives the browser user's Entra token from Spring Security. Then it requests a database-access token with the OAuth 2.0 on-behalf-of flow:

MultiValueMap<String, String> form = new LinkedMultiValueMap<>();
form.add("grant_type", "urn:ietf:params:oauth:grant-type:jwt-bearer");
form.add("client_id", entraId.getClientId());
form.add("client_secret", entraId.getClientSecret());
form.add("scope", entraId.getDatabaseScope());
form.add("assertion", endUserToken);
form.add("requested_token_use", "on_behalf_of");

Then the app attaches the Deep Data Security context to the Oracle JDBC connection. The method names are on oracle.jdbc.OracleConnection. The important calls are setEndUserSecurityContext(...) before SQL and clearEndUserSecurityContext() before returning the connection to the pool.

OracleConnection oracleConnection =
  connection instanceof OracleConnection
    ? (OracleConnection) connection
    : connection.unwrap(OracleConnection.class);

EndUserSecurityContext context =
    EndUserSecurityContext.createWithToken(databaseAccessToken, endUserToken);

oracleConnection.setEndUserSecurityContext(context);
try {
    return runQuery(connection, sql);
}
finally {
    oracleConnection.clearEndUserSecurityContext();
}

Connection-pool hygiene: the finally block is not cosmetic. In a pooled application, clearing the context before returning a connection to the pool is mandatory.

Provider Version: ojdbc-provider-spring

The explicit API version is useful because it shows the moving parts: Spring obtains the signed-in user token, the app exchanges it for a database-access token, and application code calls OracleConnection.setEndUserSecurityContext(...). The provider version keeps the same database policy model but changes where that context is assembled.

In the provider version, the protected HTTP endpoint is a Spring Security OAuth2 resource-server endpoint. A request reaches /deepsec/query with an OAuth2 bearer token. Spring validates the token and stores the authentication in the request security context. When the service borrows a JDBC connection, ojdbc-provider-spring reads that Spring Security context, obtains the database-access token through the configured OAuth2 client registration, and supplies the EndUserSecurityContext through the Oracle JDBC provider SPI.

Compared with the API version, the service no longer calls OracleConnection.setEndUserSecurityContext(...) or clearEndUserSecurityContext(). The Deep Data Security context setup moves from application service code into JDBC/Spring provider configuration.

Provider dependency

The provider app adds the Oracle JDBC Spring provider dependency alongside the Oracle JDBC driver and wallet/security jars:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc-provider-spring</artifactId>
  <version>${ojdbc.provider.version}</version>
</dependency>

Provider configuration

Spring Boot 3.3 creates the Oracle UCP PoolDataSource directly from configuration, so the provider app no longer needs a dedicated UcpDataSourceConfiguration class. Set the datasource type to UCP and put the JDBC provider settings under spring.datasource.oracleucp.connection-properties. The registrationId value must match the Spring OAuth2 client registration that can obtain a database-access token for Oracle AI Database:

spring:
  datasource:
    type: oracle.ucp.jdbc.PoolDataSource
    url: ${DEEPSEC_JDBC_URL:}
    username: ${DEEPSEC_USERNAME:}
    password: ${DEEPSEC_PASSWORD:}
    driver-class-name: oracle.jdbc.OracleDriver
    oracleucp:
      connection-factory-class-name: oracle.jdbc.datasource.impl.OracleDataSource
      connection-pool-name: ${DEEPSEC_UCP_POOL_NAME:DeepDataSecurityProviderUcpPool}
      initial-pool-size: ${DEEPSEC_UCP_INITIAL_POOL_SIZE:1}
      min-pool-size: ${DEEPSEC_UCP_MIN_POOL_SIZE:1}
      max-pool-size: ${DEEPSEC_POOL_SIZE:4}
      connection-properties:
        "oracle.jdbc.provider.endUserSecurityContext": ojdbc-provider-spring-end-user-security-context
        "oracle.jdbc.provider.endUserSecurityContext.registrationId": entra
  security:
    oauth2:
      client:
        registration:
          entra:
            client-name: entra
            client-id: ${DEEPSEC_ENTRA_CLIENT_ID:}
            client-secret: ${DEEPSEC_ENTRA_CLIENT_SECRET:}
            client-authentication-method: client_secret_post
            authorization-grant-type: ${DEEPSEC_ENTRA_DATABASE_GRANT_TYPE:client_credentials}
            scope: ${DEEPSEC_ENTRA_DATABASE_SCOPE:}
        provider:
          entra:
            authorization-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/authorize
            token-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/token

deepsec:
  jwt:
    trusted-issuers: ${DEEPSEC_ENTRA_JWT_TRUSTED_ISSUERS:https://sts.windows.net/${DEEPSEC_ENTRA_TENANT_ID:}/,https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/v2.0}
  browser:
    enabled: ${DEEPSEC_BROWSER_ENABLED:true}
    auth-provider: entraid
    tenant-id: ${DEEPSEC_ENTRA_TENANT_ID:}
    client-id: ${DEEPSEC_ENTRA_BROWSER_CLIENT_ID:${DEEPSEC_ENTRA_CLIENT_ID:}}
    scope: ${DEEPSEC_ENTRA_APPLICATION_SCOPE:}

Spring Boot binds the datasource portion to UCP and the OAuth2 portion to Spring Security. Oracle JDBC reads the provider properties when a pooled connection is borrowed, and the provider uses the matching OAuth2 client registration to request the database-access token. The deepsec.jwt values configure trusted bearer-token issuers, while deepsec.browser exposes only the non-secret values needed by the sample UI. Optional dataRoles and endUserContextAttributes properties can be added to the same connection-properties map.

Resource-server security configuration

The provider app keeps /deepsec/query and /deepsec/whoami protected while allowing the browser page and setup endpoints to load. It also trusts both common Entra issuer forms for the configured tenant, which is helpful when one app registration issues v1 access tokens while another flow emits v2 tokens:

http
  .authorizeHttpRequests(authorize -> authorize
      .requestMatchers(
          "/", "/index.html", "/app.js", "/styles.css",
          "/deepsec/browser-config", "/deepsec/health", "/deepsec/policies")
      .permitAll()
      .anyRequest().authenticated())
  .oauth2Client(Customizer.withDefaults())
  .oauth2ResourceServer(oauth2 -> oauth2
      .authenticationManagerResolver(jwtAuthenticationManagerResolver));

return JwtIssuerAuthenticationManagerResolver.fromTrustedIssuers(
    "https://sts.windows.net/" + tenantId + "/",
    "https://login.microsoftonline.com/" + tenantId + "/v2.0");

Service code becomes ordinary JDBC

With the provider enabled, the service code is intentionally boring. It checks out a connection and executes SQL. The provider supplies the end-user security context when JDBC needs it:

try (Connection connection = dataSource.getConnection();
     Statement statement = connection.createStatement()) {
    runSessionInit(statement);
    try (ResultSet resultSet = statement.executeQuery(sql)) {
        // read rows
    }
}

In my demo environment I disable parallel query to avoid a database-side context handler issue with parallel execution:

DEEPSEC_SESSION_INIT_SQL="alter session disable parallel query"

If your database policy package works under parallel query, set that value to empty.

Browser-friendly provider flow

A pure resource-server endpoint still needs an Authorization: Bearer header. To make the demo natural in a browser without adding a separate frontend build, the provider version includes a tiny same-origin page. It uses MSAL to sign in with Entra, obtains an access token for the Spring Boot API scope, and calls the protected endpoint with that bearer token:

const token = await getAccessToken();
const response = await fetch("/deepsec/query", {
  headers: { Authorization: `Bearer ${token}` }
});

The backend exposes only non-secret browser configuration at /deepsec/browser-config: tenant ID, browser client ID, and scope. Client secrets and database passwords stay server-side in .env or a secret manager.

Provider runtime flow

The browser opens http://localhost:18080/.
The page signs the user in with Entra and obtains an access token for api://<SPRING_BOOT_APP_CLIENT_ID>/user_impersonation.
The page calls /deepsec/query with Authorization: Bearer <access-token>.
Spring Security validates the token and creates the authenticated request context.
ojdbc-provider-spring reads that context, obtains the database-access token with the entra OAuth2 client registration, and supplies the EndUserSecurityContext to Oracle JDBC.
Oracle AI Database validates the context, activates mapped data roles, and enforces the same data grants shown earlier.

Running the Demo

The repository now has two runnable implementations. Use the API version when you want to teach the explicit OBO and OracleConnection API calls. Use the provider version when you want to show how a Spring resource-server endpoint can let ojdbc-provider-spring supply the Deep Data Security context.

Run the explicit API version

The API version uses Spring oauth2Login. Opening /deepsec/query in the browser redirects to Entra, then the controller receives an OAuth2AuthorizedClient and the service calls the Oracle JDBC Deep Data Security API directly.

cd security/deepdatasecurity-api-version
cp .env_example .env
vi .env
./build.sh
./run.sh

# Then open:
http://localhost:8080/deepsec/query

# The employee and manager results are shown in the role-change demonstration below.

Run the provider/SPI version

The provider version is browser-first in the sample repo, but the backend remains a bearer-token resource server. Add http://localhost:18080 as a SPA redirect URI on the Entra app registration used by DEEPSEC_ENTRA_BROWSER_CLIENT_ID, then run:

cd security/deepdatasecurity-provider-version
cp .env_example .env
vi .env
./build_and_run.sh

# Then open:
http://localhost:18080/

# Click Sign in, then Run query.
# Expected employee result in the page:
{"query":{"rows":["3:Emma Baker"]}}

For curl or Postman, request an Entra access token for DEEPSEC_ENTRA_APPLICATION_SCOPE and send it in the Authorization header:

curl -H "Authorization: Bearer ${ACCESS_TOKEN}" \
  http://localhost:18080/deepsec/query

There is still no app-side data-role switch in either flow. Oracle AI Database reads the Entra roles claim and activates the mapped DDS roles automatically.

The Nice Part: Changing Roles Without Changing Code

I run the same endpoint twice to make the behavior obvious. There are two ways to demonstrate the change:

Two-user path: sign in as two different Entra users with different app-role assignments and matching employee or manager rows.
One-user path: keep one real demo user, change that user's database/API Enterprise Application role assignment, obtain a fresh token, and map the same Entra username to different sample HR rows.

In the video I use my Paul Parkinson Entra user for the one-user path. In both paths, the user identity and role claims travel together in the security context.

Employee result

First, the signed-in Entra user has the EMPLOYEES app role, and one sample employee row is mapped to that user's ORA_END_USER_CONTEXT.username. The app executes the business SQL and Oracle returns only that employee row:

{"rows":["3:Emma Baker"]}

Manager result

For the one-user path, use this sequence:

Assign that same real Entra user to the MANAGERS app role in the database/API enterprise application.
Restart or otherwise force a fresh login so the token contains the updated role set.
Map that user's username to a sample manager row for the one-user demo.
Run the same GET /deepsec/query endpoint again.

The fresh token now carries MANAGERS instead of, or in addition to, EMPLOYEES. Oracle AI Database sees that role set during context creation, activates the matching DDS data role, and enforces the manager policy. For the one-user demo, mapping the same real Entra username to the sample manager row lets the manager end-user context resolve to the manager employee ID.

The request is still GET /deepsec/query. The endpoint, Java code, connection pool, and SQL statement stay the same. The result changes because Oracle applies the manager data grant, while restricted columns remain protected by the database.

In the sample HR data used in the walkthrough, the manager row has direct reports Emma, Charlie, and Dana. So the same /deepsec/query call moves from a single employee row to the manager's direct-report set:

{"rows":["3:Emma Baker","4:Charlie Davis","5:Dana Lee"]}

Helpful Tips

/deepsec/whoami is a setup/debug aid, not part of the authorization flow. It shows the exact username value Oracle sees in the end-user context. If your sample rows do not already match that value, use it to update one employee row for a quick demo:

http://localhost:8080/deepsec/whoami

UPDATE hr.employees
   SET user_name = '<value returned by /deepsec/whoami>'
 WHERE employee_id = 101;
COMMIT;

Empty rows can be success. If /deepsec/query returns {"rows":[]} with no exception, the DDS policy probably filtered everything. Make sure hr.employees.user_name matches the end-user context username.
Use schema-qualified table names. With an active end-user context, unqualified employees can resolve unexpectedly. Use hr.employees.
Make the database/API token v2. If Oracle reports an invalid database-access token, decode it and check ver=2.0 and an aud value matching the database/API app ID.
Restart and use a fresh browser session after scope changes. The app reads .env at startup, and browsers cache login sessions aggressively.

Productionization Checklist

Store the Entra client secret in a secret manager, not in a local file.
Validate expected issuer, audience, scopes, tenant, and role claims explicitly.
Manage data roles and data grants with versioned database migrations.
Add integration tests that run the same query as employee and manager identities.
Audit end-user contexts and data grant behavior in database monitoring.
Always clear the end-user security context before returning a pooled connection.

References

FAQ

What problem does this pattern solve?

It lets a Java service keep the operational benefits of a pooled application database user while still allowing Oracle AI Database to enforce access for the signed-in end user.

Why are there two tokens?

The user token proves who signed in to the Spring Boot app. The database-access token is addressed to the Oracle AI Database resource and carries the claims Oracle validates for role mapping and data grants.

Do employees and managers need different Java code?

No. The same endpoint, SQL statement, and connection pool are used. Different results come from Entra role claims mapped to Oracle Deep Data Security data roles.

Where should Entra role assignments be made?

Assign users or groups on the database/API Enterprise Application, because that application represents the Oracle AI Database resource for the database-access token.

Does the provider version still require a bearer token?

Yes. The backend endpoint is a resource-server endpoint. The browser sample obtains the token with MSAL and sends it automatically, while curl or Postman must send the Authorization header explicitly.

Which version should I show first?

Show the API version first when the audience needs to see the token exchange and EndUserSecurityContext calls. Show the provider version when the audience is focused on Spring configuration and reducing database-security code in services.

Persistent Memory and Derived Context: A Two-Layer Pattern for Agents

Anya Summers — Thu, 30 Jul 2026 14:31:49 +0000

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.

Figure 1: Persistent memory is the source of truth; derived context is rebuilt from it for speed, and provenance always points back the other 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.

Figure 2: A fact is valid across a window of time, but its embedding is a snapshot from one instant, so a correction at T2 leaves every later retrieval pointing at a superseded truth.

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.

Figure 3: Derived context is rebuilt from persistent memory and provenance points back to it; nothing points the other way.

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.

Figure 4: Three sync strategies trade off freshness against write cost.

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.

Figure 5: Retrieve reads derived context + exact key canonical lookups, Promote writes canonical memory, and derivation flows canonical-to-derived after every promotion.

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.

Vector Search for AI Memory: SQL, JSON Metadata, and Governance

Anya Summers — Thu, 30 Jul 2026 14:26:46 +0000

How do you combine SQL, JSON, vector search, and metadata for AI memory? Put each capability where the memory workflow can query and govern it together. The pieces belong in one architecture when an agent must retrieve semantically related context, filter it by live business state, preserve flexible memory attributes, and enforce the same access policy as the source data — and by 2026 the interesting question is no longer "where do the vectors live?" but whether all of that composes in one query path.

Answer box — the short version
SQL keeps operational facts, joins, and business predicates queryable.
JSON metadata carries flexible facts such as source, scope, category, and retention attributes without forcing a new schema for every memory type.
Vector search retrieves semantically related context; hybrid retrieval combines that context with exact keyword signals.
Governed access applies the same policy to retrieval that applies to the underlying enterprise data.
Why convergence matters: these capabilities share the five guarantees defined in the series anchor: one transaction boundary, one optimizer, one consistency model, one governance domain, and shared access surfaces.

How this was tested

The SQL examples come from the Module 03 lab, validated by GitHub Actions against Oracle AI Database 26ai Free. The long-conversation agent-memory notebook provides the complementary JSON-metadata and layered-memory example.

The architecture question for AI memory

AI memory is more than an embedding column. A useful memory record usually has relational ownership and lifecycle data, flexible JSON attributes, text that must be searched exactly, an embedding that must be searched semantically, and a policy that decides who can retrieve it. The engineering question is whether those operations compose in the same query path.

The series anchor defines a converged database by the guarantees that span data models — not by how many models a product can store. Its companion, Converged Database vs. Multi-Model Database, makes the same distinction directly: multi-model is a storage claim; converged is a guarantees claim. This article applies that framework to AI memory, where SQL, JSON metadata, vector retrieval, text search, and access policy must operate together — and it tests four of the anchor's guarantees in the retrieval path: one optimizer for vector ranking and joins, one consistency model for read-after-write, one governance domain for tenant filtering, and shared SQL and search access over the same memory data.

The category cooled because the capability spread

In Pavlo's Databases in 2025 (January 2026), he is blunt: "The buzz around vector databases has muted, and VCs are only writing checks for LLM companies," and "If 2023 was the year every DBMS added a vector index, then 2025 was the year that every DBMS added support for Anthropic's Model Context Protocol (MCP)." Vector search became a commodity index — present everywhere, decisive nowhere. By late 2025 the commoditization had reached the storage layer itself: Amazon S3 Vectors — vector indexes native to an object store, at up to two billion vectors per index — went GA in December. And in June 2026 it reached the hyperscale database tier, when Google repositioned Spanner around exactly this argument — the database as a context engine for agents, with vector search it insists is no bolted-on afterthought — a development this article returns to at length below.

The acquisitions tell the same story. The two large data deals of the period were for PostgreSQL companies: Databricks paid roughly $1B for Neon, and Snowflake roughly $250M for Crunchy Data as reported (Snowflake's own 10-Q books the purchase consideration at $164.5M in cash, closed June 6, 2025) — both framed by observers, and by Pavlo, as Postgres and OLTP plays, not vector-pure-play purchases. Capital flowed toward general-purpose engines that also do vectors.

The academic statement of the same point is Stonebraker and Pavlo's 2024 survey, which judged vector databases "essentially document-oriented DBMSs with specialized ANN indexes" — those indexes being "a feature, not the foundation of a new system architecture." And the open-source vector community makes the converged argument in its own words: the pgvector project's stated rationale is to "store your vectors with the rest of your data," with "ACID compliance, point-in-time recovery, and JOINs."

One point of fact requires care, because it is easy to overstate. As of July 2026, Pinecone — the best-funded specialized vector vendor — has not been sold. The Information reported in August 2025 that the company was "exploring a potential sale" and had lost a prominent customer (Notion); its incoming CEO, Ash Ashutosh, subsequently said publicly that a sale was "definitely not on the table right now"; and the 2026 record reads like independent operation, not wind-down: Dedicated Read Nodes went from December-2025 preview to GA in April, and a May launch week introduced native full-text search (preview) and Nexus, a "knowledge engine" in public preview since July 1. The accurate statement is that a sale was rumored and unconfirmed — and the direction of the product pivot is worth noticing: the best-funded vector-database company now markets "knowledge infrastructure," a layer of context compilation and provenance above similarity search. That similarity alone is not the product is this article's thesis, in a specialized vendor's own roadmap.

What dedicated vector stores genuinely do well

This article concedes more than its siblings, and does so deliberately: the thesis is "capability versus architecture," not "specialized stores are bad." There are workloads where a dedicated store is the right tool.

Billion-scale pure similarity serving. Milvus/Zilliz — among the most widely adopted open-source vector engines, Kubernetes-native in its distributed deployment, with multiple ANN index families (IVF, HNSW, DiskANN, GPU-accelerated) — is built for web-scale similarity, scaling to tens of billions of vectors. A single OLTP engine is not trying to win that workload. (Notably, Milvus 3.0 — launched July 16, 2026 — repositions the engine as a lake-native "vector lakebase" querying Iceberg and Lance tables in place: a leading specialized store converging toward the data lake, though still with no SQL joins or shared transactions against operational tables.)
Fused hybrid ranking as a product. Weaviate ships native dense-plus-keyword fusion with tuned ranking out of the box (relative-score fusion has been its default since v1.24); for teams whose product is best-in-class hybrid retrieval, that specialization is real value. Milvus, too, has carried native BM25 hybrid since 2.5 — fused hybrid is now table stakes among the specialized stores.
Managed similarity serving at extreme QPS. Pinecone's serverless architecture and Dedicated Read Nodes — GA since April 2026 — target operational similarity serving as a managed product.
A specialized-style optimization Oracle also has, stated fairly. Oracle's own documentation reports that BINARY vectors (one bit per dimension) cut storage roughly 32× and distance computation up to 40× — attributed here as an Oracle-documented claim, not an independent measurement.

The honest decision rule (returned to at the end): if the workload is billions of vectors and pure similarity serving, reach for a specialized store. If retrieval must compose with operational data, governance, and freshness — which describes most enterprise RAG and essentially all agentic systems — the integration is the architecture.

Why AI memory needs more than similarity

Similarity is necessary, not sufficient — and the production record says so plainly. Anthropic's Contextual Retrieval work (September 2024) reports a chain of gains over embeddings alone: contextual embeddings cut the top-20-chunk retrieval failure rate by 35%; adding contextual BM25 — lexical search — cut it by 49%; adding a reranking step cut it by 67% (5.7% → 1.9%), in Anthropic's own evaluation. (Those figures are specific to their contextual-retrieval method and their dataset; the structural point — that you want lexical and semantic and reranking, not similarity alone — generalizes; the numbers do not.) Lexical search earns its place because it catches exact identifiers, codes, and acronyms that dense embeddings blur.

Agentic retrieval pushes further still: it decomposes a query into parallel structured and unstructured subqueries and routes each appropriately — retrieval as multi-source orchestration, not a single ANN call. And the framework ecosystem codifies "beyond similarity" directly: LlamaIndex notes that most existing vector stores "do not offer a SQL-like interface; hence they are less suited for queries that involve aggregations, joins, sums, etc." — its recommended pattern combines text-to-SQL with semantic search.

Five requirements follow, and the rest of this article tests each one — every claim either a runnable assertion in the companion Module 03 lab or a direct vendor-documentation link.

Requirement	Module-03 proof	The specialized-store cost (vendor-documented)
Metadata filtering + joins to operational data	`01-filtered-joined-ann.sql`	pgvector: post-index filtering, recall loss; Pinecone: flat-metadata only, documented limits; MongoDB: filter only on indexed fields, "typically slower"
Freshness (read-after-write)	`02-vector-read-after-write.sql`	Pinecone: eventually consistent; poll for visibility; MongoDB: no read-after-write guarantee
Permission-aware retrieval	`03-permission-aware-retrieval.sql`	Pinecone's own guide: the store does not enforce permissions; integrate an external authorizer
Hybrid keyword + semantic	`04-hybrid-search.sql`	hand-rolled fusion across two systems; or a separate hybrid store with no operational join

First, though, the embeddings themselves are generated inside the database. Module 03 bakes Oracle's prebuilt all-MiniLM-L12-v2 ONNX model into the container image and loads it into the engine:

  DBMS_VECTOR.LOAD_ONNX_MODEL(
    directory  => 'ONNX_MODELS',
    file_name  => 'all_MiniLM_L12_v2.onnx',
    model_name => 'MINILM_L12',
    metadata   => JSON('{"function":"embedding","embeddingOutput":"embedding","input":{"input":["DATA"]}}'));

All 10,000 support-ticket bodies are then embedded into a real 384-dimensional column by the engine itself — no external embedding service, no copy pipeline:

UPDATE support_tickets SET body_vec = VECTOR_EMBEDDING(MINILM_L12 USING body AS data);

Every proof below runs against those real embeddings.

Oracle AI Database SQL JSON Vector Search for AI Memory in a layered design

AI memory needs a stable core and flexible context. A support decision, user preference, or source document can have relational ownership and timestamps that belong in typed columns, while its evolving attributes — such as source, category, scope, retention, or extraction confidence — fit naturally in JSON metadata. Retrieval then combines those fields with a vector representation of the memory text.

The companion long-conversation agent-memory notebook is the runnable JSON proof for this article. It creates separate tables for conversation turns, structured memory, episodic memory, and retrieval-ready chunks; stores flexible metadata as JSON; and assembles context from the appropriate memory layers. Module 03 supplies the complementary architecture proof: the query can join operational data, apply tenant policy, and rank vector results without moving the memory record into a separate retrieval store.

That split is deliberate. JSON metadata is not a substitute for relational predicates, and vector similarity is not a substitute for either. For AI memory, use SQL when the question is about live business state or a join, JSON when the memory schema must evolve, and vector search when the system needs semantic recall.

Filter and join in one statement

Production retrieval is rarely "find similar documents." It is "find similar documents for premium customers with a recent shipped order." Module 03 expresses exactly that as one statement — a natural-language probe embedded in-database, ranked by cosine distance, composed with relational predicates and a join to operational tables:

SELECT t.ticket_id,
       c.full_name /* non-key column keeps CUSTOMERS in the plan (defeats join
                      elimination) so the relational join is visibly costed */,
       o.order_id,
       VECTOR_DISTANCE(t.body_vec,
         VECTOR_EMBEDDING(MINILM_L12 USING 'unable to log in after reset' AS data),
         COSINE) AS dist
FROM support_tickets t
JOIN customers c ON c.customer_id = t.customer_id
JOIN orders o   ON o.customer_id = c.customer_id
WHERE c.segment IN ('premium','vip')
  AND o.status = 'shipped'
  AND o.order_ts >= TIMESTAMP '2026-03-01 00:00:00'
ORDER BY dist
FETCH APPROX FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90;

The EXPLAIN PLAN, with its predicate list (DBMS_XPLAN … 'BASIC +PREDICATE', captured from the lab container; the plan hash and the system-generated VECTOR$…$ suffixes vary per build):

| Id  | Operation                          | Name                                              |
|   0 | SELECT STATEMENT                   |                                                   |
|*  1 |  COUNT STOPKEY                     |                                                   |
|   2 |   VIEW                             |                                                   |
|*  3 |    SORT ORDER BY STOPKEY           |                                                   |  ← final top-10, filtered rows only
|*  4 |     HASH JOIN                      |                                                   |
|*  5 |      TABLE ACCESS FULL             | ORDERS                                            |  ← status/date filter, in the predicate list
|*  6 |      HASH JOIN                     |                                                   |
|*  7 |       TABLE ACCESS FULL            | CUSTOMERS                                         |  ← segment filter, in the predicate list
|   8 |       NESTED LOOPS                 |                                                   |
|   9 |        VIEW                        | VW_IVENJ_7E289AC7                                 |
|* 10 |         HASH JOIN                  |                                                   |
|  11 |          PART JOIN FILTER CREATE   | :BF0000                                           |
|  12 |           VIEW                     | VW_IVCR_B5B87E67                                  |
|* 13 |            COUNT STOPKEY           |                                                   |
|  14 |             VIEW                   | VW_IVCN_9A1D2119                                  |
|* 15 |              SORT ORDER BY STOPKEY |                                                   |
|  16 |               TABLE ACCESS FULL    | VECTOR$TICKETS_BODYVEC_IVF$…$IVF_FLAT_CENTROIDS   |  ← the vector index: top centroids
|  17 |          PARTITION LIST JOIN-FILTER|                                                   |
|  18 |           TABLE ACCESS FULL        | VECTOR$TICKETS_BODYVEC_IVF$…$IVF_FLAT_CENTROID_PARTITIONS |  ← candidate vectors
|  19 |        TABLE ACCESS BY USER ROWID  | SUPPORT_TICKETS                                   |  ← candidates fetched by rowid

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
   5 - filter("O"."STATUS"='shipped' AND "O"."ORDER_TS">=TIMESTAMP' 2026-03-01 00:00:00.000000000')
   6 - access("C"."CUSTOMER_ID"="T"."CUSTOMER_ID")
   7 - filter("C"."SEGMENT"='premium' OR "C"."SEGMENT"='vip')
  10 - access("VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID")
  13 - filter(ROWNUM<=14)
  15 - filter(ROWNUM<=14)

One plan tree — and the vector index is driving it. Read it bottom-up. The IVF index's own row sources are in the plan (VECTOR$TICKETS_BODYVEC_IVF$…): the optimizer probes the index's top centroids (ids 12–16; the ROWNUM<=14 stopkey is the accuracy-driven probe count), scans the matching centroid partitions for similarity candidates (17–18), and fetches each candidate's base row by rowid (19). Then the predicate list — the section the bare BASIC format omits — proves where the filters run: the segment predicate on the CUSTOMERS scan (id 7), the status-and-date predicate on the ORDERS scan (id 5), both below the final top-10 SORT ORDER BY STOPKEY (id 3). The filters apply to the candidate pool before the ranking, so the top-10 is computed over filtered, joined rows. A selective predicate here shrinks the pool the ranking sees — it is never applied after the top-10 is chosen, which is exactly the failure mode pgvector documents for post-index filtering. The module asserts all of it: the VECTOR$…IVF row sources present, each relational predicate attached to its scan, both filter steps below the ranking stopkey, and all three domain tables in one plan tree.

And the choice is honestly cost-based, not hinted. With only 300 tickets in the table the same query full-scans — exact cosine over a few hundred rows is cheaper than probing centroid partitions; on the lab's 2-core container the crossover to the index arrives by roughly 3,000 rows, and module 03 seeds 10,000 so the index-driven plan has comfortable margin. Oracle documents both pre-filter and post-filter IVF plans; the optimizer prices both and can pick a different plan for a different query. This module proves composition — filter, join, index-driven similarity, and predicate placement in this tested plan — not a universal plan shape or throughput benchmark.

The contrast is the documented filter-versus-ANN tension elsewhere. pgvector applies filters after the index scan, so a selective predicate can starve the result — the README's own example: with an HNSW index and the default ef_search of 40, a condition matching 10% of rows leaves "only 4 rows … on average," with iterative_scan and partial indexing offered as manual mitigations. (That cost is pgvector-specific rather than Postgres-architectural — extensions such as VectorChord and pgvectorscale now offer true pre-filtering.) Pinecone's metadata filtering is real and useful but constrained — flat metadata only, with documented limits on size and structure ("Nested JSON objects are not supported"; 40 KB per record), and filtering applied around the ANN search rather than joined to operational tables. MongoDB's $vectorSearch stage pre-filters only on fields specifically indexed as filters and its documentation notes that "filtered queries are typically slower". And none of these stores joins to the orders table at all — that remains an application-side fetch against a second system. (One distributed engine that can make this composition — Google Spanner — is not a vector store at all; it is a SQL engine that added the index.)

Freshness: read-after-write

In a converged engine, a committed change to an embedding is visible to the next query — there is no separate index to fall behind. Module 03 captures a ticket's distance to a probe, overwrites its embedding, commits, and re-measures:

  UPDATE support_tickets
     SET body_vec = VECTOR_EMBEDDING(MINILM_L12 USING 'tropical fish aquarium maintenance schedule' AS data)
   WHERE ticket_id = 1;
  COMMIT;

  /* the very next query sees the new vector immediately — no reindex/sleep */
  SELECT VECTOR_DISTANCE(body_vec, v_qv, COSINE) INTO v_after_d
    FROM support_tickets WHERE ticket_id = 1;

The result, captured from the lab against the live model (the probe — "cannot access my account login error" — is a paraphrase, not a copy, of the ticket's wording):

            cosine distance   rank (of 10,000)
BEFORE            0.4820             140
AFTER             1.0621           9,989
RESTORED          0.4820             140

The same query that ranked the ticket 140th of ten thousand drops it to 9,989th the instant the unrelated embedding commits — and the very next query sees it, with no reindex, no polling, and no sleep. Re-embedding the unchanged body restores the distance exactly, to the last decimal. The module asserts the shift and the exact restoration. For approximate search specifically, Oracle documents that HNSW vector-index queries retrieve "transactionally consistent top-K results," maintained through a per-transaction private journal and SCN-based incremental snapshots — freshness extends to the index, not just the base table.

The documented contrast, as of July 2026: Pinecone is eventually consistent — "there can be a slight delay before new or changed records are visible to queries" — and provides a freshness-check mechanism (an LSN the application polls) precisely because read-after-write is not guaranteed. MongoDB Vector Search, served by the separate mongot process, likewise provides no read-after-write guarantee — its documentation states search "supports eventual consistency and does not provide any stronger consistency guarantees". Google Spanner draws a subtler line, and it is worth naming precisely: committed vectors are immediately visible to both exact and approximate queries — Google's own framing is that "the results are always fresh" — but the ScaNN vector index's tree structure "is optimized for the dataset at the time of creation, and is static thereafter," so as the data drifts, ANN recall — not visibility — decays until the index is rebuilt. Staleness you can poll for; recall drift arrives silently, which for a retrieval pipeline may be the more dangerous of the two. For an agent that acts on what it retrieves, that staleness window is decision risk, not a tuning detail — the State Vector Dissonance argument developed in the anchor.

Governance: the same query, different rows per tenant

This is the cleanest expression of the whole series' "one governance domain" guarantee, and the place the architectural difference is starkest. The requirement is documented as a named production problem: permission-aware RAG, where most systems "implicitly assume all retrieved content is equally accessible to any user," which fails in multi-tenant and role-scoped deployments. The top-k must be access-filtered before it reaches the model. Oracle Virtual Private Database (VPD) is the database control used in this module; hybrid vector indexes and their generated views must be protected consistently when VPD is applied.

The specialized-store position is visible in Pinecone's own access-control guidance: enforcement lives outside the store. The recommended pattern integrates an external authorizer (the guide uses SpiceDB) to decide what a user may see, copies document identifiers into vector metadata, and carries the authorized-ID list into each query as a filter — a permission model the application must keep synchronized with the source system. That is a consistency boundary by construction: two systems that must agree about who can see what.

In a converged engine, the permission model is the engine. Module 03 attaches a Virtual Private Database policy — available in Oracle AI Database 26ai Free, per the licensing documentation — whose predicate folds into every read of the tickets table, including the vector query:

CREATE FUNCTION m03_tenant_pred(p_schema VARCHAR2, p_object VARCHAR2) RETURN VARCHAR2 IS
BEGIN /* the row-level predicate: a ticket is visible only if its customer's id
   parity matches the current tenant (A=even, B=odd). Folded into every SELECT on
   support_tickets, including the vector ORDER BY ... FETCH APPROX query. */
  RETURN q'[customer_id IN (SELECT customer_id FROM customers
              WHERE MOD(customer_id,2) =
                    CASE WHEN SYS_CONTEXT('M03_TENANT_CTX','TENANT')='A' THEN 0 ELSE 1 END)]';
END;

The module then runs the byte-for-byte identical approximate vector query as two tenants. The results, captured from the lab (probe: "refund for a damaged package"; tenant A is scoped to even-numbered customers, tenant B to odd):

--- Tenant A : identical query, top 10 (first 6) ---   --- Tenant B : identical query, top 10 (first 6) ---
 ticket  customer  parity    dist                 ticket  customer  parity    dist
   6835       180    even   0.2631                   1580       121     odd   0.2715
   8285       156    even   0.2631                   4292       151     odd   0.2763
   4106        16    even   0.2635                   4836       189     odd   0.2768
   8226       100    even   0.2733                   3853        17     odd   0.2775
   2084        64    even   0.2800                   3961        87     odd   0.2815
   4058        80    even   0.2882                   4407        11     odd   0.2822

Not one ticket appears in both lists, and every tenant-A row belongs to an even-numbered customer while every tenant-B row belongs to an odd one — the governance predicate, not chance, partitioned them. The application's query never changed; the engine filtered the unauthorized rows out of the ranking before they could surface. (The module asserts both properties: the two sets are disjoint, and each tenant's rows match its parity.) No second system, no metadata copy, no synchronization. (The proof toggles tenant identity through an application context within one session — an honest scripting convenience; the predicate enforcement is the engine's.)

Hybrid keyword and semantic, in one index

Recall Anthropic's finding that lexical and semantic search are complementary. A converged engine can fuse them in a single index. Module 03 builds Oracle's native Hybrid Vector Index — one domain index unifying Oracle Text and a vector index over the same column — and queries it with a single call that carries both a semantic probe and a keyword constraint:

  v_json := DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "tickets_hvi",
            "vector" : { "search_text" : "the box was crushed and broken in transit" },
            "text"   : { "contains"    : "damaged" },
            "return" : { "topN" : 10 } }'));

The model embeds the search_text and runs the semantic search; the contains clause runs the lexical search; one index returns a fused, score-weighted top-N. The result, captured from the lab:

 rank  ticket  fused score  vector_score  text_score  vector_rank  text_rank
   1     5467     77.40         84.54         6.00          6            1
   2     7680     77.31         85.04         0.00          1           40
   3     8636     76.99         84.69         0.00          2           40
   4     9660     76.95         84.65         0.00          3           40
   5     8487     76.94         84.63         0.00          4           40
   6     3669     76.88         84.57         0.00          5           40
   7     5425     76.85         83.93         6.00         10            1
   8     4008     76.57         84.23         0.00          7           40
   9     9433     76.50         83.55         6.00         24            1
  10      911     76.48         83.53         6.00         25            1

Read the columns: every fused row carries both a vector score and a text score — proof that the single index ran both searches and combined them, with no application-side fusion and no score normalization across systems. And the mix is the argument. Four of the ten were nominated by both arms (text_score 6.00, text_rank 1); the other six came from the vector arm alone (text_score 0.00; text_rank 40 marks rows outside the text arm's returned set). That is keyword saturation in miniature: hundreds of tickets in this corpus contain "damaged," and lexical relevance alone cannot say which damaged-package tickets are about a box crushed in transit — the semantic score can, and the fused ranking keeps them. Fusion, not intersection: the lexical arm anchors precision, the semantic arm supplies recall, and one index returns one ranked list. The module asserts the robust properties: ten rows returned, both scores present on every one, and the fused set is not identical to the pure keyword-only top-N.

Packaged call versus plain SELECT. The call above uses the packaged API, DBMS_HYBRID_VECTOR.SEARCH — hand it a semantic probe, a keyword clause, and a top-N, and Oracle runs both searches against the one index and returns a ranked JSON array, performing the score fusion for you. That is a convenience layer, not a separate access path: the same hybrid index is a first-class SQL index. An ordinary SELECT with a CONTAINS predicate is served by it directly —

| Id | Operation                    | Name            |
|  0 | SELECT STATEMENT             |                 |
|  1 |  TABLE ACCESS BY INDEX ROWID | SUPPORT_TICKETS |
|  2 |   DOMAIN INDEX               | TICKETS_HVI     |

— a single DOMAIN INDEX named TICKETS_HVI (the same plan shape, incidentally, that lets module 02's keyword search work once the hybrid index takes over the body column). So you can let the package fuse for you, or write the keyword and vector clauses yourself in plain SQL — composing CONTAINS with ORDER BY VECTOR_DISTANCE — when you want direct control of the ranking and the freedom to join the result to operational tables. In 26ai the packaged call composes too: DBMS_HYBRID_VECTOR.SEARCH accepts a filter_by clause of ordinary relational predicates alongside the semantic probe and keyword constraint. Review the hybrid-vector-index restrictions and maintenance guidance before adopting the pattern: index status, synchronization, optimization, VPD protection, and model choices matter in production. (The vector and lexical components share one structure because they share the column: when the hybrid index is created, it takes over the body column's text search, since Oracle's one-domain-index-per-indextype rule means a standalone text index and the hybrid index's text component cannot coexist on it — ORA-29879, reproduced in the lab.)

The specialized-stack equivalent of either path is to run two systems and fuse their scores in application code, because lexical and vector scores are not directly comparable; or to adopt a dedicated hybrid store, which fuses well but in a system with no join to operational data. The gap is narrowing from the other side, too — MongoDB shipped native rank-fusion aggregation stages in preview in 8.1/8.2, and Spanner documents the same fusion composed in plain SQL — which rather proves the direction: fusion is moving into the engine.

The scorecard

Capability against store class — every cell from the cited documentation, as of July 2026, to be re-verified at publication:

	Filter + join to operational data	Read-after-write	Permission-aware retrieval	Hybrid keyword + semantic	Billion-scale pure ANN
Specialized managed (Pinecone)	flat metadata; no SQL joins	✗ eventual; poll for visibility	✗ external authorizer	sparse-dense in one index; native BM25 in preview	✓ strong
Specialized OSS (Milvus, Weaviate)	filters; no SQL joins	tunable — Milvus session/strong opt-in, bounded-staleness default; Weaviate eventual	app-layer row scoping; in-engine RBAC + tenant isolation	✓ native fusion (Weaviate; Milvus BM25 since 2.5)	✓ strong
Extension (Postgres + pgvector)	joins ✓; ANN post-filter recall loss	✓	✓ (Postgres RLS)	hand-rolled fusion	partitioned, manual
Distributed SQL (Google Spanner)	✓ SQL joins; in-index leaf-level filtering; filtered indexes fixed at creation	✓ visibility, exact and ANN; ANN recall decays until the static ScaNN tree is rebuilt	partial — roles + column FGAC; no row-level security; views for row scoping	✓ in-SQL RRF over transactional FTS + ANN (hand-composed)	✓ 10B+ ANN; partitioned KNN beyond
Converged (Oracle 26ai)	✓ one statement, optimizer-chosen	✓ transactional	✓ VPD, query unchanged	✓ one engine, native HVI	not the target — IVF/HNSW at OLTP scale

The converged row's last cell is the honest one: a converged OLTP engine is not where you serve ten billion isolated vectors — that column belongs to the specialized stores, and to Spanner. Everywhere retrieval must compose, the converged engine is.

Spanner: the second opinion that proves the point

If the thesis is that similarity search belongs inside the database that holds everything else, the strongest second opinion arrived on June 29, 2026, when Google published its Spanner "agentic era" positioning. The announcement's own words could open this article: the database is "no longer a passive repository; it's a critical context engine," and Spanner's vector search "isn't a bolted-on afterthought" — it is ScaNN, "the exact same state-of-the-art indexing algorithm that powers Google Search and YouTube," running over the same tables as SQL, graph, and full-text search. The flagship case study is the thesis in production: MakeMyTrip consolidated four specialized engines — MongoDB for documents, Neo4j for graph, Elasticsearch for text, and the dedicated vector store Qdrant — into a single Spanner instance, and reports operational complexity "reduced … by 75%". A hyperscaler showcasing a customer's retirement of a dedicated vector database into its multi-model engine is this article's argument, made with someone else's workload.

On the five requirements, Spanner scores far closer to the converged column than to the specialized ones. Filters and joins compose in SQL: predicates on columns stored in the vector index are evaluated at the index's leaf level, a filtered vector index can be built for a fixed predicate, and ANN results join to operational tables in the same statement. Freshness holds up exactly as the read-after-write section noted: full-text indexes are "updated in the same transaction," and vector visibility is on-commit for exact and ANN alike. Hybrid search is documented as reciprocal-rank fusion composed in plain SQL over transactional full-text search and ANN — fused server-side, no client-side score merging.

The seams that remain are exactly the ones this article has been testing. The ScaNN vector index is the static tree the freshness section described: ANN recall decays as the data drifts, and restoring it is a scheduled rebuild — create a new index, evaluate it, drop the old. That is the price of an index built for ten billion vectors, and at that scale it may be a fair price — but it stands against a converged engine whose IVF index reorganizes itself online and whose HNSW index maintains transactional consistency incrementally. Permission-aware retrieval stops at the column: Spanner's fine-grained access control offers database roles with table- and column-level privileges but no row-level security, so per-tenant scoping means maintaining definer's-rights views per audience rather than attaching one policy — the identical-query-different-tenants proof above has no Spanner equivalent. And the embeddings are generated by calling out: ML.PREDICT invokes remote Vertex AI model endpoints — Spanner doesn't host the models — where module 03's ONNX model executes inside the engine, beside the data it embeds. Spanner is the strongest evidence yet that the category question is settled in favor of the database. What remains open — the index that maintains itself, the policy that travels with the row, the model that runs where the data lives — is the guarantees question at the centre of this series.

Choosing a vector store as part of an AI memory architecture

A decision rule, not a verdict. Reach for a dedicated vector store when the workload is web-scale similarity serving with little relational context, or when fused-hybrid ranking is itself the product you are shipping. Evaluate the converged engine when AI memory retrieval must be filtered by live business predicates, joined to operational data, carry evolving JSON attributes, be governed by the same access controls as everything else, and stay fresh on commit — which is the shape of most enterprise RAG, and of essentially every agent that takes actions on what it retrieves. Between those poles now sits distributed SQL: when composed retrieval must also run at ten-billion-vector, globally distributed scale, Spanner delivers the composition — filters, joins, freshness on commit — at the cost of the guarantees named above: a periodically rebuilt index, view-based row scoping, remote models. The architecture question is not only "which database has the best ANN index." Many general-purpose engines now support vector indexes. The question is what the memory query has to compose with.

Next step: Run the long-conversation agent-memory notebook, then adapt the SQL, JSON metadata, vector retrieval, and policy pattern to one production memory workflow.

Oracle AI Database documentation

FAQ

Do I need a dedicated vector database for AI memory? For web-scale, standalone similarity serving — billions of vectors, pure nearest-neighbor queries, no relational context — a specialized store remains a strong choice. For retrieval that must filter on business data, join to operational tables, enforce per-user permissions, and stay fresh on write, those requirements point toward an engine where vectors live with the rest of the data.

How do SQL, JSON, vector search, and metadata work together for AI memory? Keep business state and relationships in SQL, use JSON metadata for evolving memory attributes, and use vector search for semantic recall. Apply access policy before the retrieved context reaches the model, and use hybrid retrieval when exact terms matter alongside semantic relevance. The notebook linked above demonstrates the layered-memory pattern; module 03 demonstrates the relational, governance, freshness, and hybrid-search behaviors.

Can a relational or converged database do real vector search? Yes. Oracle AI Database 26ai provides a native VECTOR type, VECTOR_DISTANCE with multiple metrics, IVF and HNSW indexes, in-database embedding generation via ONNX models, and a hybrid keyword-plus-vector index. Stonebraker and Pavlo characterize ANN as "a feature, not the foundation" — a capability general-purpose engines add, which by 2026 they broadly have.

Doesn't Google Spanner's vector search settle the question? It settles the category question — Google's June 2026 "agentic era" positioning argues, as this article does, that retrieval belongs in the multi-model engine beside the data, and its flagship case study retired a dedicated vector store (Qdrant, along with MongoDB, Neo4j, and Elasticsearch) into Spanner. On capability, Spanner delivers SQL-composed filtered ANN at ten-billion-vector scale with transactionally consistent full-text search. Where it stops short of a converged engine is specific: a static ScaNN vector index that is periodically rebuilt as recall drifts, versus self-reorganizing IVF and incrementally maintained HNSW; no row-level security for permission-aware retrieval; and embedding generation through remote Vertex AI endpoints rather than models executing inside the engine.

What does RAG need besides similarity search? In practice: metadata filtering, joins to operational data, freshness (read-after-write), permission-aware retrieval, and hybrid keyword-plus-semantic search, often with reranking. Anthropic's own results show lexical search and reranking materially reduce retrieval failures over embeddings alone.

When should I still choose a dedicated vector database? When similarity serving is the workload rather than one part of it — extreme scale, extreme query rates, or a product whose value is best-in-class hybrid ranking — and there is little need to compose retrieval with transactional data or in-engine governance.

How I Taught an AI to Sound Like Me: Agent Memory with Oracle AI Database

Anya Summers — Thu, 23 Jul 2026 16:21:34 +0000

A step-by-step tutorial for building three layers of agent memory in Oracle AI Database to help an AI agent learn to write social media posts in your voice.

Companion notebook: https://github.com/oracle-devrel/oracle-ai-developer-hub/tree/main/apps/oracle-agent-memory

Key takeaways

The problem is not that AI writes badly. It’s that AI writes from zero.A stateless model has no memory of your older posts, your cadence, your weird little phrases, or what you never say. So it defaults to the internet-average voice, which is why so much AI-written social content feels the same.
Good writing help needs three kinds of memory.Episodic memory gives the agent examples of what you’ve written before. Semantic memory gives it a structured style profile. Reflective memory lets that profile evolve as your writing changes.
Oracle AI Database keeps the memory stack simple.Posts, vectors, JSON style profiles, and reflection logs all live in the same database. That means the agent can retrieve similar posts, load your voice profile, and update its understanding without stitching together a pile of separate services.
The reflection loop is what makes it feel like learning.Every few new posts, the agent compares your current style profile against your latest writing, creates a conservative diff, and updates the profile without overreacting to one weird week of posts.
The actual agent is deceptively small.The final generatePost function only needs a style profile, a few similar examples, and one LLM call. The hard part is not the prompt. The hard part is giving the prompt the right memory.

The last time you went on social media, did it feel... stale? Every post you scroll past reads the same with slightly different words. A generic opener starting with "Most X think Y", three points and a call to action, the same six emojis. Yeah, those were created by AI.

I'm not here to say that all AI-generated content is bad, but we're definitely seeing a lack of originality these days. Which is a shame, because using generative AI as a tool in the creative process is incredible. But copy/pasting the output of a "write me a LinkedIn post about security issues with AI agents" is the wrong way to go about it.

AI models are stateless. Every time you ask one to write a post for you, it starts from zero. It has no idea what you've written before, what worked, what fell flat, or how you sound when you're not trying. So it falls back on the average... which is exactly what you're seeing in your feed these days.

But it doesn't have to be this way. You can still use an AI agent to help you with social posts AND to sound like your natural voice. You just have to give it some memory.

This post walks through how to build an AI agent with three layers of memory backed by Oracle AI Database 26ai, with a reflection loop that updates the agent's understanding of your voice over time. The stack is TypeScript end-to-end: Node.js backend, React + Vite frontend, the official oracledb driver. If you prefer Python, langchain-oracledb is the direct equivalent.

To learn a little bit more about agent memory, check out this blog by Casius Lee.

What we're building

Our agent has one job: given a topic and a platform (LinkedIn, X, whatever), drafts a post that sounds like me. Easy enough as a one-shot LLM call. But the cool part is how we make it better over time without changing the prompt.

To do that, the agent needs three different kinds of memory:

Layer	What it stores	How it's used
Episodic memory	Every post I've written, embedded as a vector	Retrieve the K most similar past posts as examples
Semantic memory	A structured JSON object describing my voice traits	Inject into the system prompt as explicit guidance
Reflective memory	Observations about how my writing style is evolving over time	Periodically refine the semantic memory

All three live in Oracle AI Database, which makes this easier than it looks. Vector search, JSON, and relational rows all live in the same database with the same query engine. So in a single database, we can store everything we need to make this work.

Here's the loop:

Published posts build episodic memory, while periodic reflection updates the style profile used for future content generation.

Setup

If you don't already have a database, the repo includes a Terraform stack that provisions an Always Free Autonomous AI Database 26ai and writes a populated .env. Just clone the repository and run these three commands:

cd terraform
terraform init && terraform apply
terraform output -raw env_file > ../.env

Always Free covers the cost of the database forever. OCI Generative AI isn't on the always-free tier, but new accounts get $300 in trial credits, and the per-call cost for what we're about to build costs pennies. If you already have an Oracle 26ai instance, skip the Terraform and fill in .env by hand.

Then install the dependencies:

npm install

Once dependencies are installed, we're ready to start building! But before we do that, we should talk about the three tables in our schema, each representing one of the layers of our agentic memory.

-- Episodic memory: every post you've ever written
CREATE TABLE posts (
    id            VARCHAR2(36) PRIMARY KEY,
    user_id       VARCHAR2(64) NOT NULL,
    platform      VARCHAR2(32) NOT NULL,
    topic         VARCHAR2(256),
    content       CLOB NOT NULL,
    embedding     VECTOR(1024, FLOAT32),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted    NUMBER(1) DEFAULT 0
);

CREATE VECTOR INDEX posts_hnsw_idx ON posts (embedding)
    ORGANIZATION INMEMORY NEIGHBOR GRAPH
    DISTANCE COSINE
    PARAMETERS (TYPE HNSW, NEIGHBORS 32, EFCONSTRUCTION 200);


-- Semantic memory: the style profile per user
CREATE TABLE style_profile (
    user_id       VARCHAR2(64) PRIMARY KEY,
    profile       JSON NOT NULL,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version       NUMBER(10) DEFAULT 1
);

-- Reflective memory: what changed and when
CREATE TABLE reflections (
    id            VARCHAR2(36) PRIMARY KEY,
    user_id       VARCHAR2(64) NOT NULL,
    triggered_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    posts_window  JSON NOT NULL,
    diff          JSON NOT NULL,
    profile_after JSON NOT NULL
);

It's important to note that VECTOR(1024, FLOAT32) matches OCI's cohere.embed-english-v3.0 model. If you swap embedding functions, make sure you update the dimension to match. And as a bonus, JSON is a first-class type in Oracle 26ai with indexable paths, so the style profile doesn't need to be re-parsed on every read.

Before we get to the memory layers themselves, let's set up a thin wrapper around the OCI SDK for consistency and simplicity. Two functions: embed(), responsible for creating text embeddings from our social posts, and chat(), for communicating with the model.

// src/server/llm.ts
import * as common from 'oci-common';
import { GenerativeAiInferenceClient } from 'oci-generativeaiinference';
const provider = new common.ConfigFileAuthenticationDetailsProvider();
const client = new GenerativeAiInferenceClient({
  authenticationDetailsProvider: provider,
});

const compartmentId = process.env.OCI_COMPARTMENT_ID!;
export async function embed(texts: string[]): Promise<number[][]> {
  const res = await client.embedText({
    embedTextDetails: {
      inputs: texts,
      servingMode: { servingType: 'ON_DEMAND', modelId: 'cohere.embed-english-v3.0' },
      compartmentId
    }
  });

  return res.embedTextResult.embeddings;
}

export async function chat(args: { system: string; user: string }): Promise<string> {
  const res = await client.chat({
    chatDetails: {
      servingMode: { servingType: 'ON_DEMAND', modelId: 'cohere.command-r-plus-08-2024' },
      compartmentId,
      chatRequest: {
        apiFormat: 'COHERE',
        preambleOverride: args.system,
        message: args.user,
        temperature: 0.2,
        maxTokens: 1500
      }
    }
  });

  return res.chatResult.chatResponse.text;
}

ConfigFileAuthenticationDetailsProvider reads ~/.oci/config (the DEFAULT profile) automatically. servingType: 'ON_DEMAND' is the pay-as-you-go mode that uses your trial credits without provisioning a cluster. Everything from here on calls these embed() and chat() functions.

Episodic memory

The first layer is the simplest. Every time I publish a post, I save it. Every time I want to draft a new one, the agent retrieves the K most similar past posts to use as few-shot examples.

// src/server/memory.ts
import { randomUUID } from 'node:crypto';
import { withConn, oracledb } from './db';
import { embed } from './llm';
export async function savePost(args: {
  userId: string; platform: string; topic: string; content: string;
}): Promise<string> {
  const id = randomUUID();
  const [embedding] = await embed([args.content]);
  await withConn(async (conn) => {
    await conn.execute(
      `INSERT INTO posts (id, user_id, platform, topic, content, embedding)
       VALUES (:id, :userId, :platform, :topic, :content, :embedding)`,
      {
        id, userId: args.userId, platform: args.platform,
        topic: args.topic, content: args.content,
        embedding: { type: oracledb.DB_TYPE_VECTOR, val: new Float32Array(embedding) }
      },
      { autoCommit: true }
    );
  });

  return id;
}

Retrieval is a little more nuanced, and we take full advantage of the hybrid filter here. We want to find the most similar posts on the same platform, by this user, that aren't deleted. To do this, we perform a vector search plus a WHERE clause and are able to get the results we want with a single query.

export async function retrieveSimilarPosts(args: {
  userId: string; platform: string; topic: string; k?: number;
}) {
  const k = args.k ?? 5;
  const [queryEmbedding] = await embed([args.topic]);
  return withConn(async (conn) => {
    const r = await conn.execute<[string, string, string, number]>(
      `SELECT id, content, topic, VECTOR_DISTANCE(embedding, :q, COSINE) AS distance
       FROM posts
       WHERE user_id = :userId AND platform = :platform AND is_deleted = 0
       ORDER BY distance
       FETCH APPROX FIRST :k ROWS ONLY`,
      {
        q: { type: oracledb.DB_TYPE_VECTOR, val: new Float32Array(queryEmbedding) },
        userId: args.userId, platform: args.platform, k,
      }
    );

    return (r.rows ?? []).map(([id, content, topic, distance]) =>
      ({ id, content, topic, distance }));
  });
}

FETCH APPROX FIRST :k ROWS ONLY is what lets Oracle use the HNSW index for approximate nearest neighbor. Without APPROX, the query would fall back to exact scan, which is fine for thousands of vectors, but virtually unusable for millions.

Semantic memory

Episodic retrieval gets you "what have I said about this topic before." But now we need "how do I sound when I write." This is the perfect use case for semantic memory.

We keep our "style profile" as a structured JSON object. It stores attributes like voice traits more about the writing rather than in the writing. To capture a good approximation of what you sound like, our profile looks for the following behaviors:

{
  "tone": ["direct", "self-deprecating", "slightly skeptical of hype"],
  "sentenceLength": {
    "averageWords": 16,
    "habit": "short punchy sentences mixed with longer explanatory ones"
  },
  "structuralHabits": [
    "opens with a personal anchor or a small story",
    "uses italics for the one line that should stick",
    "closes posts with a question or a single-word punchline"
  ],
  "signaturePhrases": ["Happy coding!", "Let me explain", "Here's the thing"],
  "thingsINeverDo": [
    "use 'unlock', 'leverage', 'game-changer'",
    "more than two emoji per post"
  ],
  "topicsICareAbout": ["serverless", "AI agents", "developer experience"],
  "platformQuirks": {
    "linkedin": "longer hooks, line breaks every 1-2 sentences",
    "x": "thread-friendly, one idea per tweet"
  }
}

If you've ever asked someone to write something like this about themselves, you know people are absolutely terrible at this type of self-reflection. So naturally, we bypass the human element and ask the model to generate it from the first N posts a user adds in the system.

import { chat } from './llm';
const SEED_SYSTEM = `You are a voice analyst. You will read several social media posts by one author and produce a JSON style profile describing how they write. Be specific and concrete. "Tone is friendly" is useless. "Tone is direct, occasionally self-deprecating, slightly skeptical of hype" is useful.

Output ONLY valid JSON matching this schema:
{ 
  "tone": [string], 
  "sentenceLength": {
    "averageWords": int, "habit": string
  },
  "structuralHabits": [string], 
  "signaturePhrases": [string],
  "thingsINeverDo": [string], 
  "topicsICareAbout": [string],
  "platformQuirks": {string: string} 
}`;

export async function seedStyleProfile(userId: string, sampleSize = 20) {
  const rows = await withConn(async (conn) => {
    const r = await conn.execute<[string, string]>(
      `SELECT platform, content FROM posts
       WHERE user_id = :userId AND is_deleted = 0
       ORDER BY created_at DESC FETCH FIRST :n ROWS ONLY`,
      { userId, n: sampleSize }
    );

    return r.rows ?? [];
  });

  const postsText = rows
    .map(([p, c]) => `[${p}] ${c}`).join('\n\n---\n\n');
  const response = await chat({
    system: SEED_SYSTEM,
    user: `Posts:\n\n${postsText}`
  });

  const profile = JSON.parse(response);
  await withConn(async (conn) => {
    await conn.execute(
      `MERGE INTO style_profile sp
       USING (SELECT :userId AS user_id FROM dual) src ON (sp.user_id = src.user_id)
       WHEN MATCHED THEN UPDATE SET
         profile = :profile, updated_at = CURRENT_TIMESTAMP, version = version + 1
       WHEN NOT MATCHED THEN INSERT (user_id, profile) VALUES (:userId, :profile)`,
      { userId, profile: JSON.stringify(profile) },
      { autoCommit: true }
    );
  });

  return profile;
}

The MERGE statement handles both inserts and updates in one round trip (and deletes, it's pretty impressive). The Oracle JSON type validates the value on insert, and if the LLM emits malformed JSON, the insert fails, which is exactly what we want.

Reading it back is just as few easy lines:

export async function loadStyleProfile(userId: string) {
  return withConn(async (conn) => {
    const r = await conn.execute<[unknown]>(
      `SELECT profile FROM style_profile WHERE user_id = :userId`,
      { userId },
    );

    if (!r.rows?.length) return null;
    return r.rows[0][0] as StyleProfile;
  });
}

Reflective memory

I've built many agents where I'm done after implementing episodic and semantic memory. In many instances, that's good enough. But with this type of workload, aka people writing about what they care about, preferences change over time. Personally, I used to write nothing but dry, cold facts on serverless architectures. Today, I'm a pretty funny guy (right?!) and ponder on things that take software from good to great. Very different styles, but both me.

Building in reflective memory allows the agent to adjust over time. It's what gives us the impression that it's actually "learning."

Every K new posts (I use K=5), we trigger a reflection. The reflection is an LLM call that reads the current style profile and the K newest posts, then creates a structured diff: what's changed, what should be added, and what should be removed.

We go with the structured diff instead of a straight up overwrite to avoid profile thrashing. You aren't just your last 5 posts. You're a summary of everything you've ever posted with a recency bias. Asking for a diff lets the model commit to small, intentional updates, so you stay you and don't appear like you have violent mood swings every week.

const REFLECT_SYSTEM = `You are reviewing how an author's voice may have evolved. You have:

1. Their CURRENT style profile (built from older posts)
2. Their MOST RECENT posts (not yet incorporated)

Read the recent posts and compare to the profile. Decide whether the profile needs updating. Be conservative: most of the time, voice is stable and you should change little or nothing. Only return updates that you can point to specific evidence for in the recent posts.

Output ONLY valid JSON:
{ "additions": [{"field": string, "value": any, "evidence": string}],
  "removals":  [{"field": string, "value": any, "reason": string}],
  "rationale": string 
}

If nothing should change, return empty arrays.`;

export async function reflect(userId: string, windowSize = 5) {
  const profile = await loadStyleProfile(userId);
  if (!profile) return seedStyleProfile(userId);
  const rows = await withConn(async (conn) => {
    const r = await conn.execute<[string, string]>(
      `SELECT id, content FROM posts
       WHERE user_id = :userId AND is_deleted = 0
       ORDER BY created_at DESC FETCH FIRST :n ROWS ONLY`,
      { userId, n: windowSize }
    );

    return r.rows ?? [];
  });

  const postIds = rows.map(([id]) => id);
  const postsText = rows.map(([, c]) => c).join('\n\n---\n\n');
  const response = await chat({
    system: REFLECT_SYSTEM,
    user: `CURRENT PROFILE:\n${JSON.stringify(profile, null, 2)}\n\nRECENT POSTS:\n${postsText}`,
  });

  const diff = JSON.parse(response);
  const updated = applyDiff(profile, diff);
  await withConn(async (conn) => {
    await conn.execute(
      `UPDATE style_profile SET profile = :profile,
         updated_at = CURRENT_TIMESTAMP, version = version + 1
       WHERE user_id = :userId`,
      { profile: JSON.stringify(updated), userId }
    );

    await conn.execute(
      `INSERT INTO reflections (id, user_id, posts_window, diff, profile_after)
       VALUES (:id, :userId, :window, :diff, :after)`,
      {
        id: randomUUID(), userId,
        window: JSON.stringify(postIds),
        diff: JSON.stringify(diff),
        after: JSON.stringify(updated)
      },
      { autoCommit: true }
    );
  });

  return updated;
}

The applyDiff function is simple. It iterates through the additions and removals, and edits the profile object in place. It's in the repo if you're interested. I need to point out again that the only reason applyDiff works is because we tell the model to be conservative with its reflections. Remember, we don't want wild swings in the profile. Your posts won't make it past the "AI sniff test" if you're calm and collected one day, and corporate and metric-driven the next.

If that does happen though, we can use the reflection log as a point-in-time snapshot we can rollback to. Just rebuild from a previous profile_after snapshot and the agent effectively "unlearns" that unwanted style.

Agent memory in action

Now that we've gone through all three types of memory, it's time to build the generatePost function and see them all in action as we compose the prompt.

// src/server/agent.ts
import { chat } from './llm';
import { loadStyleProfile, retrieveSimilarPosts } from './memory';
export async function generatePost(args: {
  userId: string; platform: string; topic: string;
}) {
  const profile = (await loadStyleProfile(args.userId)) ?? {};
  const examples = await retrieveSimilarPosts({ ...args, k: 5 });
  const examplesText = examples.map((e) => e.content).join('\n\n---\n\n');
  const system = `You are drafting a social media post in the user's voice.
    STYLE PROFILE (how this user writes):
    ${JSON.stringify(profile, null, 2)}
    
    EXAMPLES (recent posts by this user on similar topics):
    ${examplesText}


    Write ONE draft post. Match the style profile and the cadence of the examples. Do not copy phrases from the examples. Do not mention that you are an AI or that you are following a profile.`;

  const draft = await chat({
    system,
    user: `Platform: ${args.platform}\nTopic: ${args.topic}`,
  });

  return { draft, basedOn: examples };
}

That's it. That's the whole thing. This is deceptively simple. It's doing two database reads to load the profile and perform a vector search, AND it's making a call to an LLM.

The agent will get better over time. The first time you use it, it will sound like everything else you see on social media these days. But as you edit the drafts and build up the data with examples in your true voice, it gets better and eerily starts sounding like you.

FAQs

Q: Why not just prompt the model to “write in my voice”?
Because the model does not actually know your voice unless you give it evidence. The article uses past posts plus a style profile so the agent has something concrete to imitate instead of guessing.

Q: What does episodic memory do here?
It stores every past post with an embedding. When you ask for a new draft, the agent finds the most similar posts on the same platform and uses them as examples.

Q: What is the style profile?
It is the semantic memory layer: a JSON object that describes how you write, including tone, sentence habits, structural patterns, signature phrases, topics you care about, and things you avoid.

Q: Why use reflective memory instead of just overwriting the profile?
Because you are not just your last five posts. Reflection creates small, evidence-backed updates so the profile can evolve without thrashing every time your writing mood changes.

Q: What happens if the agent learns the wrong style?
The reflection table keeps a history of changes. Since each reflection stores the diff and the resulting profile, you can roll back to an earlier snapshot and effectively make the agent unlearn that bad update.

To summarize

Agent memory has a lot more to it than simply "remembering things." There are different types of memory that represent similar artifacts, summaries, and long-term audits. To build a production-ready system, you need all three. Episodic and semantic memory to satisfy the business problem, and reflective to improve over time.

Oracle AI Database is the perfect database for these three types of memory. It supports vectors, JSON objects, and similarity search with filtering all in the same database. The schema for this is twenty lines. Reads are three-line functions. The complexity stays out of the database layer and in the prompt engineering where it belongs.

To walk through this project yourself, you can find the code on GitHub. It's built on TypeScript end-to-end, and is easily portable to whatever your preferred programming language is.

If you try this out, send me what you generate. I want to see how well "sounds like you" holds up across different writers.

Happy coding!

Build an Intelligent Document Processor in One Data Store

Anya Summers — Thu, 23 Jul 2026 16:19:23 +0000

Companion notebook: https://github.com/oracle-devrel/oracle-ai-developer-hub/tree/main/apps/idp-oracle-ai-database

Key takeaways

The use case is boring in the best possible way.Intelligent Document Processing is exactly where AI makes sense: incoming business PDFs, repetitive manual work, and structured fields that need to move into a process. In this article, the example is procure-to-pay: purchase orders, delivery notes, and invoices.
The twist is one data store.Instead of splitting blobs, JSON, vectors, relational data, and AI calls across S3, DynamoDB, Pinecone, SQL, and external APIs, the whole pipeline runs through Oracle AI Database.
Classification does not need an LLM.The app embeds labeled sample documents, embeds each new document, then uses k-nearest-neighbor vector search to decide whether it looks most like an invoice, purchase order, or delivery note.
The LLM only shows up when it is actually needed.Vectors can tell you what kind of document you have. They cannot reliably extract invoice numbers, totals, due dates, vendors, and line items. That structured extraction step uses UTL_TO_GENERATE_TEXT and validates the result against a Zod schema.
Oracle AI Database becomes the IDP engine, not just storage.It stores the original PDF as a BLOB, extracts text, summarizes it, creates embeddings, runs vector search, stores structured JSON fields, and calls OCI Generative AI from inside the database.

Everybody wants to build AI applications. But nobody knows a good use case.

One use case I have seen over and over again is processing incoming business documents.
In this article, I will show you how to build an Intelligent Document Processing (IDP) platform around the procure-to-pay cycle: it ingests purchase orders, delivery notes, and invoices, classifies them, and pulls out their structured fields.

The twist: we do all of it inside one data store.

The Issue with Data Stores

IDP platforms are nothing new. They are built and used in many companies already. It is the typical internal tool where a bit of AI saves a lot of manual data entry.

Building one usually means stitching together several data stores:

S3 for the document blobs
DynamoDB for key/values
Pinecone for the vectors
A SQL database for aggregations

…sometimes even more.

In this article, I want to demonstrate how you can build all of that with just one data store: Oracle AI Database.

What Is Oracle AI Database?

Oracle AI Database is Oracle's AI-native database, its flagship database with AI built into the engine rather than bolted on through external services.

It is a converged database, which means it doesn't just support typical SQL workloads. It is a multi-model database, which lets you also:

store JSON documents
store relational rows
store vectors
store BLOB files
… and more!

Instead of wiring up separate OpenAI, Cohere, and vector-store APIs, the database can do the work for you.

Oracle doesn't just give you a VECTOR data type (you could get that from a Postgres extension too). You also get text extraction, chunking, embeddings, vector search, and even calls out to generative-AI models. All driven from SQL and PL/SQL via the DBMS_VECTOR_CHAIN package.

Let's build with it.

The Architecture

We have a typical REST + SPA architecture. The frontend and backend run on AWS. The database lives in the free tier of Oracle Cloud (OCI).

A React SPA on S3 and CloudFront calls a Hono API on AWS Lambda, which connects to Oracle AI Database on OCI

Frontend: React SPA (Vite) + TanStack Router
Backend: Hono API on AWS Lambda (Function URL)
Hosting: S3 + CloudFront
Database: Oracle AI Database (OCI Autonomous, Always Free tier)

AWS provides only compute and hosting. Everything about a document (the original file, the extracted text, the structured JSON, and the vector), lives in Oracle AI Database.

The Documents We Process

The application detects and structures incoming documents. We picked the three documents of the procure-to-pay cycle:

For each type we generated a handful of sample PDFs and embedded these labeled examples into the database. When a new document arrives, the app compares its embedding against those labeled examples to decide which type it most resembles. There is no rules engine and no fine-tuning — just vectors and distance.

We want to get specific fields from each document. For example, for invoices we look for the following data (in a Zod schema):

// packages/schemas/src/invoice.ts
export const invoiceFields = z.object({
  envelope: commonEnvelope,
  vendor: z.string(),
  invoiceNumber: z.string(),
  invoiceDate: z.string(),
  dueDate: z.string().nullable(),
  currency: z.string().length(3),
  subtotal: z.number(),
  tax: z.number(),
  total: z.number(),
  lineItems: z.array(invoiceLineItem),
});

Our AI is extracting exactly this data. In an IDP application this data is used for further processing like sending out the order or validating invoices.

Viewing Documents and Content

The app's document detail view showing the original PDF, its extracted fields, and similar documents found via vector search

In the application you can open any document to see the original PDF, the extracted fields, and similar documents found via vector search.

Uploading Documents

The app's upload screen for adding a new document to be classified and processed

If you want to upload a new document you can do so as well! The uploader stores the document in the database, embeds it, and finds similar documents again. More details about this process follow in the rest of the article.

A Two-Minute Primer on Vectors

A vector embedding is just a list of numbers that represents the *meaning* of a piece of text. You can picture each document as a point in space.

Documents plotted as points in vector space, where documents of the same type cluster together and different types sit farther apart

Documents of the same type land near each other; different types land further apart.

To classify a new document, we embed it and measure the distance to the labeled examples we already stored. The closest examples win.

For example, when a new document comes in we check whether it sits closer to a purchase order, a delivery note, or an invoice:

distance(new, purchase-order-sample) = 0.1 ✅
distance(new, delivery-note-sample) = 0.7 ❌
distance(new, invoice-sample) = 0.4 ❌

The smallest distance is the most similar, so we classify the new document as a purchase order.

A new document compared by distance to a purchase order, delivery note, and invoice sample; the nearest sample wins the classification

Prerequisites

To follow along you need:

Node 20+ and pnpm 10+
An OCI Free account with an Oracle AI Database (Autonomous, Always Free tier), with the wallet downloaded locally
An OCI API key for OCI Generative AI (used by the extraction step)
An AWS account (only if you want to deploy; you can run everything locally without it)

Two short provisioning guides in the repo walk you through the slow parts:

docs/01-provision-oracle.md — create the database, download the wallet, run the migrations, load the embedding model.
docs/02-provision-oci-genai.md — create the API key and register the in-database OCI Generative AI credential.

When the database is provisioned and .env is filled in (see .env.example), bootstrap everything with:

pnpm install
pnpm db:setup # creates the idp user, schema, and indexes
pnpm db:setup-onnx # loads the ONNX embedding model as "doc_embedder"
pnpm db:setup-oci-credential # registers the OCI Generative AI credential + smoke-tests it

Setting Up the Database

The Schema

The whole application lives in two tables. documents holds the file and everything we derive from it, including the 384-dimension embedding as a native VECTOR column:

CREATE TABLE documents (
  id                RAW(16)         DEFAULT SYS_GUID() PRIMARY KEY,
  doc_type          VARCHAR2(16)    DEFAULT 'unknown' NOT NULL,
  status            VARCHAR2(32)    DEFAULT 'pending' NOT NULL,
  original_filename VARCHAR2(512)   NOT NULL,
  mime_type         VARCHAR2(128)   NOT NULL,
  byte_size         NUMBER          NOT NULL,
  page_count        NUMBER,
  language          VARCHAR2(8),
  failed_reason     VARCHAR2(512),
  created_at        TIMESTAMP       DEFAULT SYSTIMESTAMP NOT NULL,
  updated_at        TIMESTAMP       DEFAULT SYSTIMESTAMP NOT NULL,
  file_blob         BLOB            NOT NULL,
  extracted_text    CLOB,
  embedding         VECTOR(384, FLOAT32),
  CONSTRAINT documents_doc_type_chk
    CHECK (doc_type IN ('invoice', 'purchase_order', 'delivery_note', 'unknown')),
  CONSTRAINT documents_status_chk
    CHECK (status IN ('pending','text_extracted','classified','fields_extracted','embedded','done','failed'))
);

CREATE TABLE document_fields (
  document_id RAW(16)   PRIMARY KEY,
  payload     JSON      NOT NULL,
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  updated_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT document_fields_document_fk
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

A vector index makes nearest-neighbor search hit a graph instead of a brute-force scan:

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

The per-type structured fields live in the document_fields table as a native JSON column, written with a MERGE upsert and read back with JSON_SERIALIZE. Full DDL is in packages/db/migrations.

Loading the Embedding Model

Oracle AI Database generates embeddings inside the database from an ONNX model you upload once. We use Oracle's pre-built all_MiniLM_L12_v2.onnx (384-dim output), loaded with DBMS_VECTOR.LOAD_ONNX_MODEL and registered under the name doc_embedder. pnpm db:setup-onnx does the download and load and prints:

Phase 1: ADMIN pulls all_MiniLM_L12_v2.onnx into DATA_PUMP_DIR
✓ all_MiniLM_L12_v2.onnx = 133322334 bytes

Phase 2: idp loads "doc_embedder" from DATA_PUMP_DIR
✓ model doc_embedder loaded
✓ embedding dimension = 384

Registering the OCI Generative AI Credential

The extraction step (the only LLM call in the pipeline) runs from the database via DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT, which calls OCI Generative AI. For that, the database needs a credential built from your OCI API key:

BEGIN
  DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL(
    credential_name => 'OCI_CRED',
    params => JSON('{
      "user_ocid":        "ocid1.user.oc1..xxxx",
      "tenancy_ocid":     "ocid1.tenancy.oc1..xxxx",
      "compartment_ocid": "ocid1.compartment.oc1..xxxx",
      "private_key":      "<PEM body, without the BEGIN/END lines>",
      "fingerprint":      "aa:bb:cc:..."
    }')
  );
END;
/

You also need the IAM policy allow group <your-group> to manage generative-ai-family in tenancy. pnpm db:setup-oci-credential grants the database privileges, opens the outbound network ACL to the OCI Generative AI host, registers OCI_CRED, and runs a smoke test:

Phase 3: smoke test UTL_TO_GENERATE_TEXT against meta.llama-3.3-70b-instruct in eu-frankfurt-1
  ✓ response: PONG.

If you see PONG, the whole chain (API key → fingerprint → network → credential → model) works.

Ingesting Documents

The core of the application is the ingest pipeline.

The six-step ingest pipeline: store the file, extract text, summarize, embed, classify with k-NN, and extract the fields

When a document arrives, the pipeline:

Stores the uploaded file as a BLOB row with status pending.
Calls DBMS_VECTOR_CHAIN.UTL_TO_TEXT to extract the text and saves it.
Calls DBMS_VECTOR_CHAIN.UTL_TO_SUMMARY to generate a short extractive summary.
Generates a 384-dim embedding with VECTOR_EMBEDDING using the loaded ONNX model.
Classifies the document by running a k-NN vector search against the labeled examples — no LLM call.
Extracts the typed fields with DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT.

It sounds straightforward, and it is — but it's worth pausing on the fact that one database stores the file, extracts its text, summarizes it, computes the embedding, runs the vector search, and makes the LLM call. Let's look at each step.

Step 1 — Text Extraction with `UTL_TO_TEXT`

The first step extracts all text from the PDF. In the database we use the function DBMS_VECTOR_CHAIN.UTL_TO_TEXT for that. It can read a file (BLOB) and returns the text within the file. We save the extracted text in the column extracted_text.

UPDATE documents
SET extracted_text = DBMS_VECTOR_CHAIN.UTL_TO_TEXT(file_blob)
WHERE id = HEXTORAW(:id);

Important caveat: UTL_TO_TEXT can only read embedded text. It can not understand images, scans, or hand-written annotations on your documents. For those, you typically need a vision-capable LLM.

Step 2 — Summaries with `UTL_TO_SUMMARY`

In the next step, we want a summary of the document. For that, we use the SQL function UTL_TO_SUMMARY. With the database provider we use here, the summary is produced by Oracle Text inside the database — an extractive summary of the most representative sentences, not an LLM call. If you want a generative summary instead, UTL_TO_SUMMARY can also be pointed at an external provider such as Claude or OpenAI.

SELECT DBMS_VECTOR_CHAIN.UTL_TO_SUMMARY(
extracted_text,
JSON('{"provider":"database","glevel":"sentence","numParagraphs":3}')
) FROM documents WHERE id = HEXTORAW(:id);

Step 3 — Embeddings with `VECTOR_EMBEDDING`

Before we can classify by vectors, we need a vector. Oracle generates one inside the database from the ONNX model we loaded earlier:

UPDATE documents
SET embedding = VECTOR_EMBEDDING(doc_embedder USING extracted_text AS data)
WHERE id = HEXTORAW(:id);

No external embedding service, no second store. The 384-dim vector ends up in the same row as the BLOB and the extracted text.

One important note on size. VECTOR_EMBEDDING here embeds the entire extracted text in a single call. That is fine since our documents are quite small. For larger documents, you need to chunk your documents first! Oracle has a built-in mechanism for that as well with UTL_TO_CHUNKS.

Chunking flow: UTL_TO_TEXT extracts plain text, UTL_TO_CHUNKS splits it into chunks, UTL_TO_EMBEDDINGS turns each chunk into a 384-dim vector

In one SQL statement the whole chain looks like this:

-- TEXT -> CHUNKS -> EMBEDDINGS, in one statement
SELECT et.*
FROM documents d,
     DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS(
       DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
         DBMS_VECTOR_CHAIN.UTL_TO_TEXT(d.file_blob),
         JSON('{ "by":"words", "max":"200", "overlap":"20", "split":"recursively" }')
       ),
       JSON('{ "provider":"database", "model":"doc_embedder" }')
     ) et
WHERE d.id = HEXTORAW(:id);

For us, embedding the documents directly suffices.

Step 4 — Classifying with k-NN

There are two ways to classify a document:

1. Ask an LLM "what kind of document is this?"
2. Ask your vectors which labeled examples it resembles.

We go with option 2. Because it doesn't incur any LLM costs. And it gives us the powers of a vector store.

We run a k-nearest-neighbors search:

embed the new document
find its k nearest labeled examples
take the majority document type among them

// packages/db/src/repositories/documents.ts (abridged)
async classifyByVector(id: string, k = 5, unknownThreshold = 0.5) {
  return withConnection(async (conn) => {
    const result = await conn.execute(
      `SELECT b.doc_type AS DOC_TYPE,
              VECTOR_DISTANCE(a.embedding, b.embedding, COSINE) AS DISTANCE
       FROM documents a, documents b
       WHERE a.id = HEXTORAW(:id)
         AND b.id != HEXTORAW(:id)
         AND b.embedding IS NOT NULL
         AND b.doc_type IN ('invoice', 'purchase_order', 'delivery_note')
         AND b.status = 'done'
       ORDER BY DISTANCE
       FETCH FIRST :k ROWS ONLY`,
      { id, k },
      { outFormat: oracledb.OUT_FORMAT_OBJECT },
    );
    const neighbors = (result.rows ?? []).map((r) => ({
      docType: r.DOC_TYPE,
      distance: Number(r.DISTANCE),
    }));

    if (!neighbors.length || neighbors[0].distance > unknownThreshold) {
      return { docType: 'unknown', confidence: 0 };
    }

    const counts: Record<string, number> = {};
    for (const n of neighbors) counts[n.docType] = (counts[n.docType] ?? 0) + 1;
    const [winner, votes] = Object.entries(counts).sort((a, b) => b[1] - a[1])[0];
    return { docType: winner, confidence: votes / neighbors.length };
  });
}

We only compare against labeled examples that finished processing (status = 'done'), take the k nearest by cosine distance, and let them vote. If even the closest example is farther than our unknownThreshold of 0.5, we mark the document unknown instead of guessing.

For example, for a new purchase order the nearest neighbors might come back as (nearest first):

doc_1 — distance 0.12 — purchase order
doc_2 — distance 0.19 — purchase order
doc_3 — distance 0.24 — purchase order

All three are purchase orders and well inside the threshold, so we classify the new document as a purchase order.

Step 5 — Extracting Fields with `UTL_TO_GENERATE_TEXT`

Vectors tell us what a document is. They can't tell us what's in it.

For that we need structured output. For example for an invoice we look for the following schema:

// packages/schemas/src/invoice.ts
export const invoiceFields = z.object({
  envelope: commonEnvelope,
  vendor: z.string(),
  invoiceNumber: z.string(),
  invoiceDate: z.string(),
  dueDate: z.string().nullable(),
  currency: z.string().length(3),
  subtotal: z.number(),
  tax: z.number(),
  total: z.number(),
  lineItems: z.array(invoiceLineItem),
});

This data is necessary for our business processes.

This is the first time we actually need to call an LLM. And we can do that directly from the database again! With the function DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT.

For each of our documents we have such a Zod validation schema. This schema is converted to JSON and passed onto our LLM call.

// packages/schemas/src/registry.ts
export const fieldsSchemaByType = {
  invoice: invoiceFields,
  purchase_order: purchaseOrderFields,
  delivery_note: deliveryNoteFields,
} as const;

export type ExtractableDocType = keyof typeof fieldsSchemaByType;

export function getJsonSchemaForType(docType: ExtractableDocType): object {
  return zodToJsonSchema(fieldsSchemaByType[docType], {
    target: 'jsonSchema7',
    $refStrategy: 'none',
  });
}

Then we call the database function like that:

SELECT DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT(
  :prompt,
  JSON('{
    "provider":        "ocigenai",
    "credential_name": "OCI_CRED",
    "url":             "https://inference.generativeai.eu-frankfurt-1.oci.oraclecloud.com/20231130/actions/chat",
    "model":           "meta.llama-3.3-70b-instruct",
    "chatRequest":     { "maxTokens": 4096, "temperature": 0 }
  }')
) AS out FROM dual;

In our :prompt we say:

You extract structured fields from a document. Respond with a single JSON object….
JSON Schema:
${JSON.stringify(jsonSchema)}

After the call returns, we validate all data against our Zod schema to make sure all fields are available.
If they are not, the processing fails.

That is all for ingesting! We didn't need to leave our one data store at all.

Validate It End to End

With the database set up, run the API and seed it with the committed sample PDFs:

pnpm dev:api # Hono on :8787
pnpm seed # uploads every sample PDF and waits for ingest

pnpm seed prints a per-file result and a summary you can sanity-check:

✓ invoice-01.pdf type=invoice status=done 3.1s
✓ purchase-order-01.pdf type=purchase_order status=done 2.8s
✓ delivery-note-01.pdf type=delivery_note status=done 2.6s
...
Summary
by type: {"invoice":10,"purchase_order":10,"delivery_note":10}
by status: {"done":30}

If a document lands on status=failed, the reason is stored in documents.failed_reason (a common one is no_text_extracted for a scanned/image PDF — see the OCR caveat above). The two provisioning guides each end with a troubleshooting table covering the usual wallet, credential, and region errors.

Deployment

You can deploy the whole thing into your own AWS account:

pnpm cdk:deploy

It's hosted on S3 + CloudFront, with the API in a single Lambda Function URL. At low scale it costs essentially nothing.

Have fun trying it out!

FAQs

Q: What kind of documents does the app process?
Purchase orders, delivery notes, and invoices. Those three documents cover the basic procure-to-pay flow: ordering goods, receiving them, and getting billed.

Q: Why use vectors for document classification?
Because documents of the same type tend to land near each other in vector space. A new document can be classified by comparing its embedding to labeled examples, without fine-tuning a model or paying for an LLM call.

Q: When does the pipeline use an LLM?
Only during structured field extraction. After the document type is known, the app asks OCI Generative AI to return fields that match the right schema, such as invoice totals, dates, currency, and line items.

Q: What happens if the uploaded PDF is scanned or image-only?
The article calls this out as a caveat: UTL_TO_TEXT can read embedded text, but it does not understand scanned images or handwriting. Those cases usually need OCR or a vision-capable model.

Q: Why is one database such a big deal here?
Because the traditional version of this stack would need several systems: one for files, one for relational data, one for JSON or key-value data, one for vectors, plus external AI calls. Here, the document, extracted text, embedding, classification, structured fields, and AI workflow stay in one place.

Summary

In this article, we went through a whole IDP pipeline. From uploading PDFs, to embedding vectors, classifying documents with k-NN, and even making our own call to OCI Generative AI. All within one data store.

This is one of the biggest benefits of using a converged database such as Oracle AI Database.

In a traditional stack this would have been at least 4 systems:

S3 (BLOB)
Postgres (relational)
Pinecone (Vectors)
DynamoDB/MongoDB (JSON)

…and additionally API calls to external LLM providers. With our used database all of that stays within one system.

Production RAG Evaluation: Keyword, Vector, SQL, or Hybrid Search?

Anya Summers — Thu, 23 Jul 2026 16:17:57 +0000

Short answer: Production RAG evaluation should measure whether the system retrieves the right evidence, answers from that evidence, respects permissions, handles fresh data, and refuses unsupported questions. Compare keyword, vector, SQL, and hybrid retrieval against the same question set. Use retrieval metrics, answer-quality checks, and production failure tests before deciding which path belongs in the application.

RAG, or retrieval-augmented generation, retrieves evidence from an authoritative source and gives that evidence to a language model before it answers. Production RAG evaluation tests both halves of that process: whether retrieval found the right evidence and whether the generated answer used it correctly.

A RAG demo can pass with a few clean documents and one friendly question. Production is where the system starts meeting real users.

They ask for exact IDs. They ask vague questions. They ask about data that changed five minutes ago. They ask across tenants, versions, tables, PDFs, status fields, and long conversations. Sometimes the right answer is not in the corpus at all.

That is why the useful question is not "Should I use vector search or hybrid search?" The useful question is "Which retrieval path gives the application the right evidence, under the constraints this system actually has?"

This article turns that question into a practical evaluation plan for developers building RAG with Oracle AI Database. The companion notebook benchmarks three retrieval methods:

keyword retrieval for exact terms, identifiers, and lexical matches
vector retrieval for semantic similarity and vocabulary mismatch
RRF hybrid retrieval when keyword and vector candidates both add value

SQL or natural-language-to-SQL is treated as a separate route for current structured data. Evaluate it with query-correctness, permission, freshness, and result-limit tests rather than forcing it into document-retrieval metrics.

The goal is not to declare a universal winner. The goal is to build the evidence needed to choose the right retrieval strategy for a production RAG system.

Key takeaways

Evaluate retrieval and generated answers separately. A model can retrieve relevant evidence and still produce an unsupported or incorrect answer.
Use SQL or NL2SQL for current structured facts, and use keyword, vector, or hybrid retrieval for unstructured content. Route mixed questions across both.
Compare keyword, vector, and RRF hybrid retrieval against the same ground-truth question set before choosing a production default.
Test freshness, tenant isolation, metadata filters, exact identifiers, citations, and abstention alongside average retrieval metrics.
Treat hybrid search as a measured option, not an automatic winner. Keep numerical claims unpublished until they are traceable to the notebook exports.

What does production RAG look like?

Short answer: Production RAG is a measured retrieval and answer system. It needs repeatable ingestion, versioned chunks, access controls, freshness rules, citations, abstention, observability, and regression tests. Features such as BM25, vector search, RRF, reranking, HyDE, and incremental indexing only matter when you can prove they improve the answers users actually need.

A common developer question is: "I already have hybrid search, reranking, citations, and a no-hallucination policy. What else makes RAG production ready?"

The missing piece is usually the evaluation harness. Retrieval features are easy to add. Proving that they still work after a chunking change, embedding model swap, schema change, or reranker update is the harder production problem.

A production RAG system should have:

A ground-truth question set with required evidence and expected answer behaviour.
Retrieval metrics for keyword, vector, SQL, and hybrid paths.
Answer evaluation for groundedness, correctness, citation validity, and abstention.
Versioned ingestion, parsing, chunking, embeddings, prompts, and retrieval configuration.
Metadata filters for tenant, permission, source, status, freshness, and document version.
Observability for empty results, retrieval misses, latency, citation failures, and stale evidence.
A rollback path when a new retrieval change improves the average but breaks an important query class.

Chunking needs its own tests. Short support notes, long PDFs, policy documents, tables, code, and product documentation do not share one ideal chunk size. Treat chunk size, overlap, parsing, parent document links, and table handling as versioned configuration. Then test those choices against the same question set before you publish the change.

This is the practical bar: if you cannot detect a broken retrieval change, the system is not production ready yet.

How do I improve a RAG pipeline over a sparse SQL database?

Short answer: Do not embed every row from a sparse SQL database. Route structured questions to SQL, filter null and empty fields before building searchable text, and embed only fields with useful language. Use keyword retrieval for exact IDs and status values, vector retrieval for descriptive text, and metadata filters for valid rows.

A common developer question is: "My database has many empty tables and null columns. I embedded rows, but the model retrieves poor context. How do I make the RAG pipeline efficient?"

The problem is usually not the model. The problem is that the retrieval corpus contains low-information chunks. If a row has many empty columns, turning the whole row into text gives the retriever noise that still competes for context-window space.

Separate the jobs before adding more retrieval tricks:

User question	Best first route	Example
Aggregations, counts, dates, filters	SQL or NL2SQL	"How many open high risks have no mitigation?"
Exact identifiers and controlled values	Keyword plus SQL predicates	"Show risk RSK-1042 with status OPEN"
Narrative similarity	Vector retrieval over meaningful text	"Find incidents involving delayed supplier access"
Mixed exact and semantic intent	Keyword and vector candidates fused with RRF	"OPEN risks similar to the supplier outage"

Create a retrieval view rather than embedding every physical row. The view should include stable IDs, required business metadata, and a deliberate search_text field built from non-empty descriptive columns.

For example, a useful retrieval record might include:

risk_id: RSK-1042
tenant_id: acme
status: OPEN
severity: HIGH
search_text: Supplier access delay caused a missed shipment window. Mitigation owner is reviewing backup routing options.

That is different from serialising twenty columns where half the fields are empty. Preserve nulls as database state for SQL reasoning. Do not turn the word "null" into semantic content unless the absence itself is the thing being searched.

For sparse relational data, evaluate routes separately before combining them:

SQL quality: does the generated or selected SQL return the correct rows?
keyword quality: do exact IDs, codes, statuses, and controlled terms match reliably?
vector quality: do descriptive fields retrieve semantically related incidents or risks?
hybrid quality: does RRF improve mixed queries without adding noisy rows?

RRF is useful when both keyword and vector result lists contain useful evidence. It is not a cleanup step for a bad corpus.

How should RAG handle real-time dynamic data?

Short answer: Frequently changing structured data should usually be queried live, not re-embedded on every update. Use SQL or NL2SQL for current rows, and reserve RAG for unstructured text that benefits from semantic retrieval. For changing documents, update affected embeddings, track freshness metadata, and test stale-versus-current answer behaviour.

A common developer question is: "My backend data updates every five minutes. Should I use RAG, SQL, caching, MCP, tool calling, or something else?"

Start by asking what kind of data needs to be fresh.

If the answer lives in current structured rows, query the database at request time. Re-embedding the full dataset every five minutes creates a constant race with the source of truth. The vector copy can become stale before the indexing job finishes.

If the answer lives in unstructured documents, use retrieval. But make freshness explicit. Store source timestamps, version IDs, current-version flags, ingestion times, and deletion state. Then evaluate whether the system chooses the current evidence instead of a stale chunk.

Use a simple routing model:

Need	Route
Current account, risk, ticket, inventory, or status data	SQL or NL2SQL against governed live tables
Policies, manuals, support notes, or PDFs	Keyword, vector, or hybrid retrieval over indexed documents
Current facts plus explanatory documents	SQL for current state, retrieval for explanation, answer composition with citations
Cross-session user preferences or agent context	Scoped agent memory, not a document index

Caching, MCP, and tool calling are useful, but they solve different problems.

A cache reduces repeated work, but it must be invalidated when source data changes. MCP can expose database tools to an assistant, but it does not make stale data fresh. Tool calling lets a planner choose SQL, retrieval, or another service, but every tool still needs permissions, timeouts, result limits, and traceable outputs.

The production pattern is not "embed everything." It is "route each question to the freshest authoritative source, then evaluate whether the answer used that source correctly."

When should I use SQL, vector search, keyword search, or hybrid search?

Short answer: Use SQL for structured facts, keyword search for exact terms, vector search for semantic similarity, and hybrid search when the same workload contains both lexical and semantic queries. The right choice depends on query distribution, freshness needs, permission rules, latency, and measured retrieval quality.

Each retrieval path has a job.

SQL is the right first route when the question is about rows, filters, joins, dates, counts, totals, statuses, permissions, or current business state. If the data already has structure, keep using it.

Keyword search is strong when the user supplies exact terms: error codes, product names, SKUs, ticket IDs, risk IDs, function names, policy clauses, and other tokens where spelling matters.

Vector search is useful when the query and the document use different language for the same concept. It helps with paraphrases, fuzzy intent, natural-language descriptions, and vocabulary mismatch.

Hybrid search is appropriate when the workload has both patterns and both result lists contribute. A common implementation is Reciprocal Rank Fusion, or RRF. It retrieves candidates from keyword and vector search independently, then fuses ranks:

RRF score(document) = sum(1 / (60 + rank_in_result_set))

RRF avoids pretending that keyword scores and vector distances live on the same numeric scale. A document found by both routes receives contributions from both rankings. A document found by only one route can still survive if it ranks well enough.

The mistake is using "hybrid" as a default badge of seriousness. Hybrid retrieval adds query work, tuning, latency, and operational complexity. Add it when the evaluation shows it improves the questions that matter.

What metrics should I use for RAG evaluation?

Short answer: Use retrieval metrics to test whether the system finds the right evidence, then answer metrics to test whether the model uses that evidence correctly. Retrieval metrics include NDCG, MAP, recall, and precision. Answer metrics should cover groundedness, correctness, citation validity, and abstention quality.

RAG evaluation has two layers that should not be collapsed into one score.

First, evaluate retrieval. Retrieval metrics are model-independent and can run without a generation API. That makes them useful for frequent regression checks.

Metric	What it tells you	Useful question
NDCG@k	Whether relevant documents appear high in the ranking	Did the retriever put the best evidence near the top?
Recall@k	How much known relevant evidence was recovered	Did retrieval miss evidence the answer needed?
Precision@k	How much of the returned set was relevant	How much noise did retrieval add?
MAP@k	Ranking quality across multiple queries	Is performance consistently useful across the test set?

The notebook records retrieval results for keyword, vector, and RRF hybrid retrieval:

Method	NDCG@10	MAP@10	Recall@10	Precision@10
Keyword	{{KEYWORD_NDCG_10}}	{{KEYWORD_MAP_10}}	{{KEYWORD_RECALL_10}}	{{KEYWORD_PRECISION_10}}
Vector	{{VECTOR_NDCG_10}}	{{VECTOR_MAP_10}}	{{VECTOR_RECALL_10}}	{{VECTOR_PRECISION_10}}
RRF hybrid	{{HYBRID_NDCG_10}}	{{HYBRID_MAP_10}}	{{HYBRID_RECALL_10}}	{{HYBRID_PRECISION_10}}

These placeholders must stay placeholders until the notebook has been run cleanly and the exported metrics have been reviewed. Do not turn them into claims manually.

Second, evaluate generated answers. A retrieved document can be relevant while the generated answer is still wrong, unsupported, overconfident, or badly cited.

Use answer-level checks for:

groundedness: does the answer stay within the retrieved context?
correctness: does it match the reference answer?
citation validity: do cited documents support the claims attached to them?
abstention quality: does the system refuse when the corpus does not contain enough evidence?

Model-based judging is a review signal, not ground truth. Keep the raw generated answers, retrieved IDs, reference answers, scores, and rationales so a human can inspect surprising results.

How do I test production failure modes?

Short answer: Add a production challenge set next to the clean benchmark. Include exact identifiers, paraphrases, stale and current versions, tenant isolation, metadata filtering, messy questions, multi-hop questions, and unsupported questions. These cases catch the failures that average retrieval scores often hide.

A clean benchmark is useful, but production traffic is not clean. Developers need a small challenge set that reflects the application’s actual risk.

The notebook includes ten production-style cases:

An exact identifier query.
A paraphrase query.
A stale version query that should not use old evidence.
A current version query that should prefer the latest evidence.
A tenant isolation query.
A messy user question with irrelevant wording.
A question requiring two documents.
An unsupported question where the system should abstain.
A lexical entity query.
An embedding-migration sequence question.

Those cases should be adapted to the domain before publication. For a risk-register system, include risk IDs, mitigation statuses, sparse rows, tenant boundaries, and null-heavy records. For a support assistant, include error codes, product versions, stale documentation, and unsupported product claims. For a real-time operational chatbot, include recently changed records and cache-invalidation cases.

The key is to keep the challenge set stable. Run it before changing chunking, embedding models, metadata filters, SQL generation, rerankers, or prompts. If a change improves the average but breaks tenant isolation or stale-data handling, it is not an improvement.

Decision guide: keyword, vector, SQL, or hybrid

Use the query type and failure cost to pick the starting route.

Your workload contains	Start with	Then test
Current structured facts	SQL or NL2SQL	Query correctness, permissions, freshness, and safe result limits
Error codes, IDs, SKUs, names, exact clauses	Keyword retrieval	Whether vector search improves paraphrases without losing exact matches
Natural-language questions and vocabulary mismatch	Vector retrieval	Exact-identifier failures and metadata constraints
Both lexical and semantic questions	RRF hybrid retrieval	Candidate depth, latency, and ranking gains over both baselines
Sparse relational tables	SQL plus selective semantic fields	Null handling, exact IDs, and route-level quality
Data that changes every few minutes	Live SQL for structured data, incremental indexing for documents	Stale-versus-current answer behaviour
Regulated or multi-tenant data	Any method with mandatory metadata filters	Isolation, auditability, deletion, and freshness
Unsupported questions	Retrieval plus abstention policy	False answers and false refusals

The important move is not choosing one retrieval method forever. It is making the retrieval route explicit, measuring it, and changing it only when the evidence says the system gets better.

Frequently asked questions about production RAG evaluation

When should I use hybrid search instead of vector search for RAG?

Short answer: No. Hybrid search helps when a workload contains both exact lexical queries and semantic queries, and when both candidate lists contribute relevant evidence. It can add latency and tuning work. Compare hybrid retrieval against keyword and vector baselines on the same question set before adopting it.

Can I evaluate RAG without an LLM API?

Short answer: Yes. Retrieval evaluation can run without a generation model. Use relevance judgements to calculate NDCG, MAP, recall, and precision for each retrieval method. Add answer-level evaluation later to test groundedness, correctness, citation validity, and abstention.

Should I use RAG or NL2SQL for a SQL database?

Short answer: Use SQL or NL2SQL when the answer depends on structured rows, joins, filters, dates, counts, or current state. Use RAG for unstructured documents and descriptive text. Many production applications need routing: SQL for live facts and retrieval for supporting explanations.

What is Reciprocal Rank Fusion in hybrid search?

Short answer: Reciprocal Rank Fusion, or RRF, combines independently ranked keyword and vector results by rank position. It avoids comparing raw keyword scores with vector distances directly. Documents that rank highly in either list can survive, while documents found by both methods receive contributions from both rankings.

How often should I evaluate a production RAG system?

Short answer: Run retrieval regression tests whenever chunking, parsing, embeddings, indexes, filters, rerankers, prompts, or source schemas change. Also run a scheduled production challenge set to detect corpus drift, stale evidence, permission failures, and changing user-query patterns.

Next steps

For Oracle AI Database RAG, start with the implementation and documentation that matches the route you need:

Run the production RAG evaluation notebook to compare keyword, vector, and RRF hybrid retrieval and export the evidence used by this article.
Read the Oracle AI Vector Search User's Guide for vector data, indexes, similarity search, and retrieval implementation details.
Understand hybrid search in Oracle AI Database for keyword and semantic search modes, RRF, weighted RRF, and score fusion.
Use Select AI for natural-language interaction with a database when current structured data should be queried through SQL or NL2SQL.
Get started with Oracle AI Agent Memory when the application also needs persistent, scoped memory across agent sessions.