From chatbot to agent: a bounded tool-use loop for org.jkiss.dbeaver.model.ai

[Ashfaqbs]

From chatbot to agent: extending org.jkiss.dbeaver.model.ai with a bounded tool-use loop

While working on #42155 and #42157 (the two small bugs blocking Ollama/local-LLM use via the existing openai engine, tied to #36951), I read through org.jkiss.dbeaver.model.ai closely enough to see what it already does well and where its ceiling is. Posting this as an idea rather than a PR, since it's a design/scope question before it's code.

What exists today

A schema-scoped chat. AIDatabaseScope/AIDatabaseContext restrict the model's view to selected DB objects, and the model can call one function tool — db_listTableNames — against the live connection. Two provider engines (openai-compatible, copilot). The model answers questions and drafts SQL; a human copies, reviews, and runs it.

The gap

It's Q&A. It can't look at why a query is slow, check whether an index would help, inspect what's actually in a table, or verify its own suggestion against the live database before handing it to the user — every one of those needs a second round-trip through a human. That's the gap between "chatbot that talks about your database" and "agent that works on it."

The proposal

Add a bounded plan → act → observe → reflect loop on top of the existing chat infrastructure, driven by a larger set of read-only (and, later, explicitly gated write) database tools. Not a new product or a new provider engine — an extension of the same AIDatabaseScope/function-tool mechanism that's already there.

New tools (read-only first)

Alongside db_listTableNames:

• db_getTableDDL — full CREATE TABLE for a table: columns, types, constraints
• db_getIndexes — existing indexes, with selectivity/cardinality where the driver exposes it
• db_explainQuery — run EXPLAIN / EXPLAIN ANALYZE (dialect-aware), return the plan
• db_sampleData — LIMIT N sample rows, respecting the existing scope restriction
• db_getTableStats — row count, table size, last-analyzed time where available

All read-only queries DBeaver already knows how to run through its existing driver abstraction (DBPDataSource/DBCSession) — wiring, not new database-access code.

The loop

Instead of one prompt → one answer, the agent gets a capped iteration budget (configurable, default ~5):

1. Plan — decide which tool call would reduce uncertainty about the task.
2. Act — call it.
3. Observe — feed the result back into context.
4. Reflect — answer now, or take another step.

Concretely, for "why is this query slow": call db_explainQuery, see a seq scan, call db_getIndexes on that table, then answer with the diagnosis and the evidence — not a guess.

Safety boundary — read vs. write

The part that most needs maintainer input before any code:

• Phase 1 (this proposal): read-only tools only. Nothing here can mutate data or schema. The agent can look, reason, and propose — it can't act irreversibly on its own.
• Phase 2 (explicitly out of scope here): a db_executeStatement tool for DDL/DML would need an explicit human-approval gate per call — a diff/preview requiring a click, not a settings toggle that silently allows it. DBeaver already has transaction/commit UI conventions worth reusing here rather than inventing new ones.

Why this fits DBeaver specifically

• DBeaver already has the hard parts an agent needs and a chatbot doesn't: a live authenticated connection, a driver abstraction that works across engines, and a scoping mechanism (AIDatabaseScope) that already limits blast radius.
• The existing two-engine provider setup needs no changes — this only adds to what the model is allowed to call.
• It extends the exact pain point in [New feature] Local LLM Support for DBeaver Community #36951: self-hosted models aren't just about privacy/cost, they're about running a loop more often without per-iteration API cost — which only matters once there's an actual loop to run.

Suggested phasing

1. Add db_getTableDDL, db_getIndexes, db_sampleData, db_getTableStats as read-only tools in the existing single-shot chat (no loop yet) — low risk, useful on their own, easy to review.
2. Add db_explainQuery plus the plan/act/observe/reflect loop, capped, read-only tools only.
3. (Needs sign-off before any code) the write-gate design for db_executeStatement — a product/UX decision as much as an engineering one.

Open questions

• Does org.jkiss.dbeaver.model.ai have appetite for a multi-step loop at all, or is "single-shot with more tools" (step 1 only) the right stopping point?
• Where should the iteration cap and tool allowlist live — connection settings, a global AI preference page, or per-scope like AIDatabaseScope already does?
• Is there an existing DBeaver convention for "AI wants to run a query, show the user, require a click" to reuse, or would this be the first one?

---

Drafted with Claude Code (autonomous mode, human-reviewed before posting); the idea follows directly from reading the existing AI module while working on #42155/#42157.

---

Update (2026-10-19)

Checked for any overlap since posting: #42231 ("Optional ScreenContextAgent context for explaining a query error") is the only other open item mentioning "agent" — it's about pulling in external screen-capture context, unrelated to this proposal.

A couple of additions worth folding in here:

• Risk-tiering the tool list explicitly helps reviewers: everything in step 1 (db_getTableDDL, db_getIndexes, db_sampleData, db_getTableStats, db_explainQuery) is read-only; only the phase-2 write tool needs the approval-gate design work.
• On the write-gate question: the existing chat already shows a proposed SQL statement before running it today, so phase 2's gate is really "apply that same review-then-click pattern after a multi-step loop instead of a single-shot answer" rather than a new UI concept.
• Happy to put together a draft PR for step 1 (the read-only tools, no loop yet) if the team thinks this direction is worth taking upstream, rather than designing the whole thing before any code exists.

(Closing a duplicate discussion I opened separately before finding this one — consolidating here.)

[renezander030]

db_explainQuery can break the Phase 1 read-only promise: on Postgres, EXPLAIN ANALYZE executes the statement, so analyzing an UPDATE or DELETE the model drafted changes data, and analyzing the slow SELECT runs it in full against the live connection. I'd let the tool run ANALYZE only on a read-only session inside a transaction it always rolls back, and the same shape carries Phase 2: run the approved statement without auto-commit, show the affected row count, and leave the commit to the human.
Is the agent's session going to be opened read-only at the driver level, or does Phase 1 rest on the tool list alone?

[Ashfaqbs]

Good catch, and you're right that Phase 1 can't rest on the tool list alone — that was a gap in the proposal as written.

To answer directly: the agent's session should be opened read-only at the driver level for these calls, not just restricted by which tools are exposed. On top of that, db_explainQuery should wrap ANALYZE in a transaction that's always rolled back regardless of outcome, so even if the statement being explained is a drafted UPDATE/DELETE, nothing persists. That same read-only session should back db_sampleData and the other read tools too, rather than each one relying on its own query-shape assumptions to stay safe.

For Phase 2, reusing that shape (no-autocommit, show affected row count, human clicks to commit) makes sense — one safety mechanism across both phases instead of two different ones to reason about.

I'll fold this into the proposal: Phase 1 = driver-level read-only session + rollback-wrapped EXPLAIN ANALYZE, not tool-list-only gating. Thanks for flagging it before any code existed to carry the bug forward.

---

Drafted with Claude Code (autonomous mode, human-reviewed before posting)