ai-native-layer-roadmap.md

AI-native query layer — near-term roadmap

Context

The team's shared vision for hotdata-langchain is a single query_hotdata(...) tool where "the agent decides what to ask, not how to fetch it" — routing across four pathways (SQL, full-text/BM25, vector/semantic, point lookups), merging/ranking results, with caching and permissions underneath. Caching (HotdataToolCache) is built but still an unmerged draft (PR #33, currently parked); HotdataVectorStore shipped in 0.4.0 and self-provisions its index as of the release after it (see vectorstore-plan.md).

A verification pass (2026-07-22) checked three previously-unverified assumptions directly against the codebase, across monopoly, runtimedb, datafusion-vector-search-ext, hotdata-ibis, and this repo:

• BM25/full-text is not a gap — it's already shipped and mature. runtimedb has a real Tantivy-backed full-text index (src/bm25/), a bm25_search(...) DataFusion table function, its own IndexType::Bm25 catalog entry, and a passing e2e test. monopoly's control plane is a thin proxy — RuntimeDB owns validation/dispatch. This repo just doesn't expose it as a tool yet.
• Point lookups are a scoped refactor, not a redesign. The abstraction (PointLookupProvider::fetch_by_keys in datafusion-vector-search-ext) is already general-purpose; the coupling is in runtimedb's vector-index build pipeline. Tracked as hotdata-langchain#34 (implementation lands in runtimedb).
• Routing/planning across pathways is genuinely greenfield — nothing in the stack does intent-based pathway selection today. vector_search()/bm25_search() are both explicit SQL functions the caller must name; this repo's tools are fully independent with zero shared dispatch. The one reusable precedent is runtimedb's LazyTableProvider::select_best_index (a narrow, single-heuristic strategy picker) and the catalog's IndexType enum, which already models Sorted/Bm25/Vector side by side.

Routing, resolved into three separate problems (2026-07-27)

The earlier "is routing one problem?" framing dissolved once the engine was read directly. See engine-contract.md for the verified specifics.

• Sorted index vs. table scan — already handled, by the planner. Nobody decides. The sorted index has no callable function; it is a transparent substitution inside hotdata_execute_sql. A "search by number" tool would expose a choice that does not exist.
• SQL vs. search — the model decides, and it needs telling. This is an intent difference no engine can recover from SQL. It is also not self-evident to the model: text matching is expressible as computation (LIKE, tsvector), so an unguided agent matches text in SQL and fails. Fixed by stating the constraint in the tool description, which measurably works (see engine-contract.md's last section).
• BM25 vs. vector — nobody should decide; fuse them. Both take free text and return ranked rows, and they fail differently: BM25 misses paraphrase, vector misses rare exact tokens (ids, proper nouns). The established answer is EnsembleRetriever-style reciprocal rank fusion. Fusion must operate on ranks, not scores — BM25 is unbounded (~8–11 observed) and cosine is 0–2, so the scales are not comparable.

The consequence for the tool surface: one text-search capability that fuses underneath, not two tools the model picks between. hotdata_search_text's description deliberately names no mechanism (a test enforces that it never says "bm25"/"vector"/"hnsw"), so the retrieval strategy can change without changing the contract the model was given.

Corrected 2026-08-18: fusion does not need to go client-side, and cannot always happen at all. This paragraph previously argued for a client-side hybrid on round-trip grounds — measured on a real 3-call agent run at 7,057 ms total, ~1,200 ms wall per tool call against 49–79 ms of engine execution, so ~1,100 ms of it round trip. That measurement stands; the conclusion drawn from it does not. RRF is expressible as one SQL query using CTEs, ROW_NUMBER() OVER and FULL OUTER JOIN, all of which the engine supports, so the fused search costs one round trip rather than two and needs no engine primitive — the hybrid_search() in #37 is an optimisation, not a prerequisite. The RRF parameters are now settled. The constant is 60, the standard value from the paper the method comes from, kept rather than tuned. The dedup key is the table's key column, and the sort is tie-broken on it — that one was settled by observation, since every row found by a single pathway scores exactly 1 / (60 + rank) and so ties in pairs.

The harder constraint found at the same time: a provider-backed vector index cannot coexist with any other index on its table. Semantic search is free of client-side embedding only on such an index, so the configuration that makes semantic search cheap is exactly the one that forbids BM25 beside it. Hybrid is therefore available only over a plain vector index plus BM25, where the query vector has to be produced on this side. Both findings are recorded with their verification in engine-contract.md.

Checklist

Superseded in part, 2026-08-13. This checklist predates the downstream LangGraph POC (hotdata-agents), which consumed published 0.6.0 with two agents and produced a 15-finding register (notes/hdlc-feedback.md). Items below marked shipped are still shipped, but several shipped with defects the POC found. The current order of work is carried by the issues, not by this file: #59 model-facing contract → #41 tool layer → #60 tool results → #39 semantic search → #40 discovery → #62 cohort discipline → #61 provisioning boundary.

The POC's headline result: with a one-sentence system prompt, tool routing was correct on every graded question. That is the design intent working — and it is also why a false sentence in a tool description is a behavioural bug rather than a docs nit, since the descriptions are the entire briefing the model gets.

Tier 1 — buildable now, zero blockers (this repo + sdk-python-framework)

• BM25 tool. Shipped as hotdata_langchain/search.py — hotdata_search_text, with the corpus pinned at construction (the engine errors outright when the index is missing, so a model choosing its own corpus can pick one that cannot answer). hotdata_describe_tables now reports what is searchable; the search tool does not yet take that choice from the agent.
• Tool descriptions that carry the engine's contract. The highest-leverage item found so far, and not originally on this list. A 12-token SQL description produced a failed run; the contract version produces the correct search-then-SQL path with no system prompt at all. Pinned by tests/test_descriptions.py.
• Schema discovery. hotdata_describe_tables over information_schema, registered by default. Without it an agent guesses column names — and got away with it only because the demo fixture is a famous public dataset.
• HotdataVectorStore MVP. Shipped in 0.4.0 — add_texts/add_documents, the four similarity_search* variants, get_by_ids, delete, from_texts, and equality metadata filtering over promoted typed columns. Validated against LangChain's published conformance suite (langchain-tests) as well as this package's own tests, and verified end to end against a live workspace. Design: vectorstore-plan.md. Self-provisioned indexes and MMR followed (below), closing every phase under #47.
• Vector fast path verified (2026-08-06). The read path's central assumption — that a plain ORDER BY <distance_fn>(...) LIMIT k is rewritten into an index lookup — is now observed rather than inferred, along with the three query shapes that silently forfeit it. A WHERE-filtered query reaches the fast path too, which the plan had assumed it would not. Plans recorded in engine-contract.md.
• create_index on HotdataClient (sdk-python-framework). Shipped in hotdata-framework 0.10.0 as the general create_index(..., index_type=...) rather than the originally-scoped create_vector_index, since CreateIndexRequest already accepted "bm25" and "sorted" too. It polls the build job and raises with its error_message, because the submit call reports success for builds that later fail.
• Self-provisioned vector index (Phase 3 of #47). HotdataVectorStore.create_index() and from_texts(..., create_index=True), always built for the store's own distance — the server would otherwise default to l2, and a metric the query's distance function was not built for full-scans silently. This is what makes the verified fast path reachable without leaving Python for the CLI.
• MMR search (Phase 2 of #47). max_marginal_relevance_search() and its _by_vector variant, so as_retriever(search_type="mmr") works instead of raising. It is the one read path that projects the stored vectors, so its candidate fetch forfeits the index lookup and is bounded by fetch_k instead.

Tier 2 — needs scoped backend work, not exploratory

• Semantic search tool. Shipped: a column carrying a vector index gets hotdata_search_semantic, with the route read from the control plane at construction rather than chosen by the caller. Both index kinds are supported — provider-backed, where the engine embeds the query, and plain, where the caller supplies an Embeddings.
• Hybrid fusion. Shipped: a BM25 column on a table that also carries a plain vector index is fused with it by reciprocal rank fusion, as a single SQL query, under the text tool's existing name and contract rather than as a second tool the model chooses between. Applying it needs an Embeddings on this side, since the coexistence restriction above rules out the provider-backed index that would let the engine embed the query. The pairing is inferred when the table carries exactly one plain vector index and named with search_semantic_column= when it carries more — the engine records no link between a vector column and the text it came from. The RRF constant is 60 and each pathway searches max(4k, 20) candidates, both overridable on hybrid_search_sql.
• Point-lookup generalization in runtimedb. Tracked as hotdata-langchain#34. Decouple the lookup-sidecar build from the vector-index pipeline, loosen the registry, add a point_lookup(...) UDTF.

Tier 3 — not ready to scope yet, needs a decision first

• Structured-intent routing (deciding when to reach into SQL/point-lookup, à la SelfQueryRetriever). Deferred until the hybrid retriever (Tier 2) shows whether a classifier is actually needed.
• Permissions. Entirely untouched by shipped or planned work; no scoping done.
• Cross-source joins. Engine-level question, likely beyond what client-side LangChain code alone can provide. attach_database_catalog may already be the supported route for the cross-database case; unverified.

Cross-repo work this surfaced

Everything below came out of building the BM25 tool and running it against production. The per-item evidence lives in the linked issues; the verified engine behaviour behind them is in engine-contract.md.

Grouped by code surface:

• hotdata-framework client gaps (#36) — errors discard the engine's message (raises e.reason, "Bad Request", losing the actionable text); create_index since fixed in 0.10.0; resolve_managed_database falls back to matching non-unique display names; from_env() silently picks a workspace. The first is the one with demonstrated impact on agent behaviour.

• runtimedb engine gaps (#37) — some tables reject a projection naming none of their own columns (COUNT(*), COUNT(1), and even SELECT 1), while most tables accept all three, so it is neither an aggregate rule nor universal; to_char returns an unrecognised format pattern verbatim instead of raising, which silently destroys a column of values; no hybrid/RRF primitive for the eventual server-side fusion.

• Vector index dimension detection (#52) — building an index over an existing embedding column fails after a mixed upsert (rewritten ids plus new ids in one load), and fails intermittently on that shape. Earlier "candidate triggers ruled out" conclusions were each based on a single passing control and do not hold. The width is read from stored data rather than supplied, so no client argument works around it.

• ANN lookup key is built from the table reference as written (datafusion-vector-search-ext#32) — a two-part schema.table reference resolves correctly but forfeits the vector index, with nothing reported. Worked around in the SQL tool's description until the rule resolves the reference against session defaults first.

• id-first addressing in this repo (#38) — breaking; mirrors the hotdata-dlt-destination change (its PR #59) that removed by-name resolution entirely. Worth landing before the next release rather than shipping two breaking versions.

• Retrieval surface (#39) — shipped, both halves: the semantic search tool, and hybrid rank fusion over it and BM25 as a single SQL query rather than the client-side fan-out originally planned.

• Discovery surface (#40) — done, both parts. hotdata_describe_tables reports each column's searchable_by, read from IndexesApi.list_indexes, and hides the vector columns a provider-backed index generated; no engine change was needed. make_hotdata_tools takes tool_name_suffix= so two tool sets no longer both register hotdata_execute_sql, and the database-scoped descriptions name their database. What this unlocks is not itself done: the search corpus is still pinned at construction, and un-pinning it is now expressible because an agent can read what is searchable rather than guess.

  Whether an agent would act on that is a separate question, and it has since been measured answering no. Across 24 runs on one model, searchable_by never once moved which table the agent searched, including runs where the annotation was already in the thread from an earlier describe call and the agent then wrote ILIKE against the very column it said was searchable. What moved the choice was a worked call in the SQL tool's description naming that column, so the discovery surface is true information that nothing consumes on its own. Read the finding before designing anything that assumes an agent will look it up: #73.

• Tool-layer robustness (#41) — done. with_error_feedback and engine_error_message are package API, make_hotdata_tools(handle_errors=True) turns the wrapping on, and both the sync and async callables are wrapped — the async one is what LangChain actually calls in a deployed agent, and the demo's version missed it. hotdata_load_managed_table now accepts a URL, which is the only ingest route open to an agent with no filesystem of its own — bounded by a public-address check and a size cap, since the URL is model-chosen and therefore reachable by a planted instruction. Also added a name constant per tool and management_tools=False, so an application selecting a subset stops hardcoding strings.

Already tracked elsewhere and deliberately not duplicated: point-lookup generalization (hotdata-langchain#34) and the sorted-index cost model (runtimedb#481).