OpenAI AI Chatbots

Oracle Database / APEX 26.1 chatbot proof of concept. Configurable endpoints use the existing OpenAI-compatible Chat Completions format. APEX exports and callers are maintained separately; the application directory has no maintained export.

Architecture

Package	Responsibility
CB_CONVERSATION	Message persistence, regeneration, automatic summary, archive, clear, contextual image lookup.
CB_AGENT	Context assembly, direct OpenAI generation, summaries, image enrichment.
CB_ADAPTER_OPENAI	Chat Completions payloads and choices[0].message.content parsing.
CB_AGENT_UTIL	Shared JSON, context, validation, bounded errors, HTTP.
CB_MEMORY	Message embeddings and summarized-memory recall.

There are no provider types or Claude adapter. Responses API migration is outside this refactor. Compatible endpoints remain configurable.

Retained behavior

Chat context is ordered: bot prompt, global context, running summary, recalled summarized messages, unsummarized history. The first four remain structured JSON, flattened into one system message. Recall defaults to 14 same-chatbot summarized rows, selected by similarity and presented in ID order with original speaker roles and historical attribution guidance.

CB_CONVERSATION.submit_turn saves a supplied user message and the generated assistant CLOB. Blank input regenerates a reply for the latest user row. After a successful reply, at 30 or more unsummarized rows it summarizes older rows, keeping the newest 12. Automatic summary uses the current chat model, omitting the chat-specific token override. Failure rolls back only summary work to the private post-reply savepoint, preserves the reply, logs diagnostics, and retries after the next successful turn. Manual summaries keep 10 by default.

Messages, responses, summaries, recall, and archive JSON stay CLOBs. Summarization retains original messages and embeddings and appends raw summary output. Archive is non-destructive; clear independently removes live messages and resets the running summary. The caller owns transactions. Turns are sequential per bot.

Image enrichment runs separately through CB_AGENT.populate_latest_image_definition, using CB_CHATBOTS.IMAGE_LLM_MODEL_ID independently of the chat model. Its input is the entire assistant CLOB; search terms retain their 300-character bound. Complete metadata is a no-op and failure logging is preserved. CB_CONVERSATION.get_current_image_blob selects a same-bot image by cosine distance to the newest available image-term embedding; no match or lookup failure returns null.

Configuration and APIs

SIGNATURE_TYPE remains in CB_AI_MODELS. After trim/uppercase, only OPENAI, OPENAI_COMPATIBLE, and OPENAI_COMPAT are accepted. Unsupported or blank configurations fail at load time before HTTP. Rows are not rewritten or deleted.

Token limits use explicit override, model MAX_TOKENS, then 10000. Image enrichment uses its independent model limit or that fallback. Credentials are trimmed; an existing case-insensitive Bearer prefix is preserved, otherwise Bearer is added. HTTP clears headers and sets JSON Content-Type plus Authorization. No credential diagnostics are added.

Retained agent entry points are:

cb_agent.get_text_response(
   p_model_id, p_bot_id, p_current_message_id,
   p_recall_message_count default 14, p_max_tokens default null
) return clob

cb_agent.create_summary(
   p_model_id, p_bot_id,
   p_keep_latest_message_count default 10, p_max_tokens default null
)

cb_agent.populate_latest_image_definition(p_bot_id)

The signature/URL/key/model overloads and Claude-only constants are removed. CB_AGENT_UTIL.make_api_request now accepts URL, Authorization value, and request CLOB only. Its context parser and four context-key constants are now private; external utility callers need auditing too.

Typical APEX process:

begin
   cb_conversation.submit_turn(
      p_model_id => :PXX_MODEL_ID,
      p_chatbot_id => :PXX_CHATBOT_ID,
      p_user_message => :PXX_MESSAGE
   );
end;

Refresh the chat, enrich image metadata in a separate Ajax call, then refresh the image region. Page-item and Ajax boundaries must preserve CLOBs.

Installation and validation

Source is grouped under database objects/tables/, packages/, package bodies/, and triggers/. Install tables first, then utility, memory, OpenAI adapter, agent, and conversation specifications/bodies, then triggers. No provider types are required. Oracle VECTOR and APEX APEX_AI, APEX_WEB_SERVICE, APEX_DEBUG, and UTL_HTTP are dependencies. Embedding service db_onnx_model must return 384 FLOAT32 dimensions.

Use the tracked deployment procedure for upgrades; do not rerun table creation. The model-table source change only revises fresh installation annotations; no table/data migration is included.

The external APEX/caller audit is pending and blocks deployment. Oracle compilation, fixture execution, and APEX checks were not performed in this repository-only run. See tests and PROJECT_MAP.