Provisioning boundary: agent-driven database, table, and index lifecycle

[rohan-hotdata]

Measured against published 0.6.0: an agent holding our tools can create a managed database,
declare a table, load data into it, query it and delete it — the whole lifecycle, end to end,
unaided. What it cannot do is make any of that searchable.

There is no agent-callable way to build a vector store, and no agent-callable way to create an
index of either kind. Both search modes need setup that happens outside the agent's reach, in
Python, by a developer.

So the honest position today is: this integration supports agent-driven analytics over data a
developer provisioned. That may well be the right boundary — letting a model create indexes has
real cost implications — but right now it is neither documented nor decided, it is just where the
API happens to stop.

What is agent-reachable today

Capability	Agent can do it alone	Note
Query, aggregate, analyse	yes	over loaded data or attached live sources
Discover the schema	yes	types only — cannot tell a populated column from an empty one
Rank by keyword or by meaning	yes	only if a developer provisioned the index or corpus
Express a cohort inside one query	yes	keyword only — see the asymmetry below
Create a database, declare tables	yes	verified end to end, then cleanly deleted
Load data	partly	local parquet paths only
Build a vector store or an index	no	developer-only Python API

The four specific gaps

1. load_managed_table takes a local filesystem path only. A URL raises a raw Python
   FileNotFoundError before anything reaches the engine, which makes it unusable from a deployed
   Agent Server that has no local files. (URL loads are already noted in Tool layer: surface tool errors to the model, read-only mode and tool name constants, URL-based table loads #41 — flagged here for
   completeness, not to duplicate.)
2. Parquet only. No CSV, JSON, Arrow, or inline rows — so an agent cannot materialise data it
   already holds.
3. No vector-store creation tool. HotdataVectorStore is Python-only.
4. No index-creation tool, for vector or BM25. BM25 in particular has no brute-force fallback,
   so an unindexed column is a hard error rather than a slow query.

The asymmetry underneath it

bm25_search takes text, so an agent can compose it into SQL unaided — that is what makes a
relevance-defined cohort expressible in one query. vector_search takes a vector, so a
meaning-defined cohort cannot be expressed in SQL by an agent at all. Something has to embed the
concept first.

This is the sharpest gap the downstream POC found, and it is why the semantic side is a
second-class citizen of the composability story that this integration's whole pitch rests on. A
tool that takes a concept, embeds it, and returns a SQL-addressable cohort would close it.
That work belongs with the semantic search tool in #39; this issue is the surrounding provisioning
question.

What this issue needs before it needs code

A decision, not an implementation:

• Should provisioning be agent-callable at all, or explicitly developer-only?
• If agent-callable: behind a flag, off by default? With what cost guardrails?
• If developer-only: say so in the tool descriptions and the README, so the boundary is a stated
  design position rather than an accident.

Whichever way it goes, the boundary should be written down. Right now a reader of the tool list
would reasonably infer more autonomy than exists.

[rohan-hotdata]

Update — 2026-08-19

Repurposed as the epic for this work, not superseded. Full implementation plan is in
docs/ddl-tools-plan.md, committed on branch docs/ddl-tools-plan (not yet pushed).

Confirmed scope, broader than this issue originally framed: create databases, add tables to
one after creation, load data (including non-replace modes), delete/drop both databases and
tables, and create indexes (sorted, BM25, vector) — all agent-callable. Standing up a
HotdataVectorStore itself (this issue's original gap 3) is explicitly out of scope —
create_index is exposed as a plain DDL tool, not a wrapper around the VectorStore class. Gap 1
(URL loads) is already closed by #41.

The open question this issue asked — should provisioning be agent-callable at all, behind what
flag — is answered: creation stays under the existing management_tools=True default, matching
create_managed_database's existing precedent. Deletion gets a new, default-off
destructive_tools flag, so drop capability requires an explicit opt-in independent of whatever
HITL gating sits around it.

Two real interactions with other open issues, worth testing explicitly before shipping:

• runtimedb: vector index creation fails with 'could not detect dimension' on mixed upsert loads #52 (intermittent vector-index-build failure on a mixed-upsert table) becomes newly
  agent-reachable once this issue's index tool and a load tool with upsert support ship
  together — an agent can now construct that exact repro shape unaided, where before it took a
  developer doing it deliberately.
• hotdata-framework: client gaps blocking agent use (error opacity, half-succeeding table delete, create_index, id-first, workspace default) #36 (resolve_managed_database falling back to non-unique display-name matching) means the
  delete tools must resolve to an id first via resolve_database_by_id, never pass a raw string
  straight to the framework call.

Sequencing note: feat/tool-result-caching (unmerged, forked well before 0.9.0/0.10.0 and
stale relative to current main) also touches make_hotdata_tools's signature in tools.py.
Whichever of these two lands first, the other needs to reconcile against it on rebase.

Phased as four separate issues under this one once picked up, each its own PR — see the plan
doc's Tracking section.

[rohan-hotdata]

Correction to the sequencing note above: `feat/tool-result-caching` (and
`bench/tool-cache-backend-comparison` on top of it) was POC work that didn't pan out and is
being scrapped — confirmed neither is depended on by anything on `main` or by this plan.
Ignore that note; there is nothing to reconcile against.