Agent

The Agent is a JavaScript runtime inside the ShannonBase server, packaged as a MySQL stored procedure. You talk to it in natural language; it inspects your schema, runs read-only queries, builds plans, and only writes when you approve.

1 · The entry point

sys.shannon_chat is a procedure, so it is called with CALL, not SELECT. Configuration travels in the @chat_options session variable.

SET @chat_options = JSON_OBJECT(
  'conversation_id', UUID(),
  'model_options', JSON_OBJECT(
    'provider', 'deepseek',
    'model_id', 'deepseek-v4-pro',
    'api_key',  'sk-...'));

CALL sys.shannon_chat('Which databases are on this instance?');

The answer comes back as a single-column result set named response.

2 · Conversations

The agent is multi-turn. Keep conversation_id the same and history carries over.

• Omit conversation_id — the agent auto-continues the last conversation on this connection
• Set it to "new" — force a fresh session
• Set history_length to control how many prior turns feed the prompt (default 5)

-- first turn
SET @chat_options = JSON_OBJECT(
  'conversation_id', UUID(),
  'model_options', JSON_OBJECT('provider', 'deepseek',
                               'model_id', 'deepseek-v4-pro',
                               'api_key',  'sk-...'));

CALL sys.shannon_chat('What columns does orders have?');

-- follow-up: same connection, no conversation_id -> continues
CALL sys.shannon_chat('Now find orders over 1000 in the last 7 days');

3 · How it works

Each call runs through a 4-level dispatcher that resolves which agent function executes:

• L1 — a @shannon_agent_plugin session variable pointing at a function
• L2 — enabled entries in mysql.shannon_agent_plugins, by priority
• L3 — a shannon_agent() function in the current database
• L4 — the built-in sys.shannon_agent_default() fallback

Once dispatched, the built-in agent picks one of four routes:

• Route A — Catalog: exact schema/object match, runs canned SQL directly
• Route B — Rule Planner: schema/DDL introspection with a small query loop (max 5 turns)
• Route C — RAG: knowledge-base questions answered via semantic search over your vector stores
• Route D — LLM Agent Loop: the full loop — the model reasons, calls tools, and synthesizes an answer (max 10 turns)

4 · Tools

Eleven tools are available to the loop. The model emits them as JSON ({"thought":"...","tool":"query_db","args":{...}}) and the runtime validates and executes them.

• query_db — read-only SQL (SELECT/SHOW/DESC/EXPLAIN/WITH); write statements are rejected
• explain_sql — EXPLAIN FORMAT=JSON plan with full-table-scan warnings
• plan_sql — an ordered list of read-only SQL steps (max 15)
• list_tables — list tables in the current database, optional keyword filter
• describe_table — full column definitions plus foreign keys for 1–8 tables
• ml_rag — retrieve relevant context via vector search
• generate_text — raw LLM text generation
• begin_tx — start a transaction with a lease in mysql.agent_tx_lease
• update_data — INSERT/UPDATE/DELETE/REPLACE inside an active transaction; UPDATE/DELETE must have a WHERE clause
• commit_tx / rollback_tx — finish or abandon the transaction

5 · Review and approval

Write and risky operations are gated by a review state machine. Enable it with review_mode='review' in @chat_options. Read-only statements stay automatic via auto_execute_read_only=true.

SET @chat_options = JSON_OBJECT(
  'conversation_id', UUID(),
  'model_options', JSON_OBJECT('provider', 'deepseek',
                               'model_id', 'deepseek-v4-pro',
                               'api_key',  'sk-...'),
  'review_mode', 'review',
  'require_approval_for_write', true);

CALL sys.shannon_chat('Set all failed orders from the last 7 days to review');

When a step needs approval the agent pauses and returns a prompt with the plan ID, the SQL, the affected table, and a risk level. You reply with one of:

• Approve — continue with the step
• Reject — cancel the plan
• Modify:SQL — replace the pending SQL with your corrected version

Steps are claimed with compare-and-swap, so a concurrent approve/reject/modify from another session cannot race the decision.

6 · Safety nets

• Read-only by default — writes require an explicit transaction and approval policy
• Transaction lease — begin_tx registers a lease; a stuck transaction is auto-rolled back in a finally block, and expired leases are cleaned up
• Turn and error caps — max 10 LLM turns, max 3 consecutive errors
• Duplicate detection — a repeated tool call forces a summary instead of looping
• Output guards — raw SQL results and stray JSON tool calls are detected and replaced with a clean natural-language summary

7 · Audit and persistence

Everything the agent does lands in tables under mysql., so you get a full audit trail out of the box:

• agent_sql_trace — every SQL execution with turn number and result preview
• agent_review_plan / agent_review_history — approval plans and actions
• agent_memory — conversation memory with vector embeddings
• agent_tx_lease — transaction leases
• shannon_agent_plugins — the plugin registry

8 · Configuring the model and RAG

model_options selects the LLM. Providers are the same set as ML_GENERATE: deepseek, dashscope, qianfan, openai, ollama, and local onnx. Alongside provider / model_id / api_key you get temperature, max_tokens, top_p, deepseek_thinking, reasoning_effort, and language.

rag_options points the ml_rag tool at your vector stores: vector_store, n_citations, distance_metric (COSINE | DOT | EUCLIDEAN | L2), and embed_model_id. If omitted, RAG falls back to the legacy top-level retrieve_top_k / tables keys.

SET @chat_options = JSON_OBJECT(
  'conversation_id', UUID(),
  'model_options', JSON_OBJECT('provider', 'deepseek',
                               'model_id', 'deepseek-v4-pro',
                               'api_key',  'sk-...'),
  'rag_options', JSON_OBJECT(
    'vector_store', JSON_ARRAY('mydb.product_docs'),
    'n_citations',  8,
    'distance_metric', 'COSINE'));

CALL sys.shannon_chat('How does ShannonBase MVCC actually work?');

9 · Pluggable agents

The dispatcher makes it possible to drop in your own agent as a JavaScript function. Management procedures cover the lifecycle:

• sys.shannon_agent_register_plugin(name, schema, func, priority, desc, @result)
• sys.shannon_agent_unregister_plugin(name, @result)
• sys.shannon_agent_toggle_plugin(name, enabled, @result)
• sys.shannon_agent_list_plugins()