¹

1 Introduction↩︎

With advanced tool calling abilities [1], AI agents are emerging as the primary users of data systems [2], [3], and text2SQL agents in particular offer a promising approach to translating natural language queries to SQL. Agents begin by exploring [2] the data system to gather relevant tables, columns, and JOIN relationships prior to formulating the final query.

While exposing data systems to agents is powerful, securing and scoping access to them is crucial [4], [5]. As a result, data systems construct environments with explicit API surfaces and authentication [6]–[17] using protocols such as MCP [18]. Agents execute tool calls by invoking APIs exposed to it as shown in Figure 1. Since this is becoming the ubiquitous way data systems are exposed to agents, we ask the question: how does the exposed API surface effect the agent’s ability to perform text2SQL tasks? We conduct the first study of API surfaces exposed by different data systems and find that they fall into two broad categories and differ primarily in how they enable exploration: coarse-grained and fine-grained APIs. Coarse-grained APIs allow agents to retrieve the entire schema up-front for the LLM to process in its entirety and determine relevant parts for query formulation. Fine-grained APIs enable progressive exploration, starting with just table names and allow the agent to selectively inspect individual table schemas.

Based on our study, we observe a dichotomy between these choices. With coarse-grained APIs, the agent gets all the schema information, and has to then distill down what is relevant. However, this leads to increased token cost (§2.3) during exploration. Fine-grained APIs enable a more token-efficient exploration process, but the agent has no visibility into the full schema. Since the relevant tables are not known a priori, constructing a correct query necessitates exploring broadly, causing the agent to over-explore and incorporate irrelevant tables into query construction, ultimately degrading accuracy. Furthermore, we summarize and show in Table 2 that a majority of data systems expose fine-grained APIs, making any agent that is exposed to them prone to over-exploration and inaccurate SQL generation.

Our key insight for the effective use of fine-grained APIs is simple: let the environment provide the agent with directives that help constrain its future exploration. Since over-exploration stems from exploring tables that later prove irrelevant, directives steer the agent away from such tables. Our initial results show that directives reduce over-exploration by up to 4.6\(\times\) and boost SQL generation accuracy by up to 12.4% (\(\sim\)​4 percentage points). A key challenge is computing these directives and we present Sophrosyne, a data system environment that addresses it.

2 Motivation↩︎

We now analyze the inefficiencies of coarse-grained API surfaces and quantify the problem of over-exploration.

2.1 Setup↩︎

Our data system environment is an MCP server for SQLite [19] exposing the fine-grained API surface described in Table 1. We build our agent (§7.2) using OpenCode [20], and evaluate it on 157 queries from the BIRD LiveSQLBench benchmark [21]. We evaluate with three models: GPT-5.4-mini, GPT-5.4, and Claude Sonnet 4.5, all configured using their default reasoning effort unless otherwise specified.

2.2 Extent of Over-exploration↩︎

To measure over-exploration, we analyze the agent’s tool call sequences. A tool call is exploratory if it inspects a table’s schema via describe_table or a read_query call of the form PRAGMA table_info (table_name). We establish the ground truth exploration count by parsing each ground truth SQL query and extracting the number of unique tables it references, representing the exact number of schema explorations needed. Furthermore, to understand the effect a model’s reasoning effort has on over-exploration, we measure it at each model’s highest reasoning effort as well.

Figure 3 shows the percentage of exploratory calls across all queries above the ground truth. When exposed to fine-grained API surfaces, agents over-explore by up to 65%, with reasoning effort having a negligible impact on over-exploration, suggesting that for each model, it is an inherent symptom when exposed to fine-grained API surfaces. More critically, over-exploration translates to inaccuracies in query construction as we discuss in §4.1.

2.3 Inefficiencies of Coarse-grained API Surfaces↩︎

Since token consumption directly proxies cost, we measure the inefficiency of coarse-grained surfaces using Sonnet-4.5. With coarse-grained exploration, since the agent retrieves the entire schema for every query, we estimate its token consumption as the entire schema’s token count times the number of queries. For comparison, we compute (1) the actual schema token consumption as the sum of schema tokens of tables used in the ground truth queries provided by BIRD, and (2) the total schema tokens of tables explored when the agent is exposed to a fine-grained API surface. Figure [fig:token-ineff] compares these per database provided. Coarse-grained exploration consumes up to 5.7\(\times\) more tokens than necessary while fine-grained exploration consumes up to 2\(\times\) more, making coarse-grained exploration APIs highly cost-inefficient.

3 Design & Implementation↩︎

Over-exploration stems from exploring tables that prove to be irrelevant. Our key idea to address this issue is to compute and return directives that convey the set of potentially irrelevant tables as feedback to the agent to help guide its future exploration (as shown in Figure 4 (a)). The central challenge here is computing these directives. We design and implement Sophrosyne, a data system environment that solves this.

Directives are returned with the response of list_tables, since we observe this to consistently be the first tool call made by the agent. Interaction with Sophrosyne requires a lightweight offline phase that constructs a hybrid search index (\(\mathbf{HI}_{\mathbf{col}}\)) over individual column names, combining an IVF index for semantic similarity with a BM25 index [22] for keyword matches. The results are combined using the reciprocal rank fusion method [23]. To generate embeddings for \(\mathbf{HI}_{\mathbf{col}}\), we use OpenAI’s text-embedding-3-large model. As a result, \(\mathbf{HI}_{\mathbf{col}}\) can be used to search for relevant column names and which table contains them. On searching \(\mathbf{HI}_{\mathbf{col}}\), it returns the table name of the most relevant match.

The key idea behind directives is that for the knowledge sources provided by the BIRD benchmark, directives are computed based on the observation that entries are defined in terms of relevant column names. Querying \(\mathbf{HI}_{\mathbf{col}}\) using the entire knowledge base entry can lead to inaccuracies given multiple column names are likely to be present [24]. As a result, we first infer potential column names by prompting (§7.1) an inexpensive model as shown in Figure 4 (b). The inferred names now serve as search queries for \(\mathbf{HI}_{\mathbf{col}}\). Running retrieval for each of these gives us a set of potentially relevant tables for this query. Directives are now the irrelevant tables, computed as all table names that are not part of this set.

Sophrosyne is implemented as an MCP server following current data systems [8]–[16] and exposes the fine-grained API surface laid out in Table 1. We posit that directive computation should be tailored to the data system and the available context and that better mechanisms will further enhance the agent’s success. Sophrosyne balances offline and online costs and while schema linking approached [25], [26] make different cost tradeoffs, they can help achieve the same goal. Furthermore, companies building data agents [27]–[29] can leverage the semantically richer context available to them to compute directives more effectively.

4 Evaluation↩︎

We use the setup described in §2.1, with our agent overriding OpenCode’s system prompts (§7.2). We also disallow use of built-in tools thereby treating OpenCode as a simple agent loop with access to only the API surface exposed to it. Consequently, any agent implementation with MCP support [20], [30]–[33] can be used. Additionally, BIRD provides domain-specific knowledge bases to provide the agent with prerequisite context. Since retrieval of knowledge entries can be inaccurate [24], we provide the agent with the exact entries needed for each query to isolate its effects and compare the following three systems on the basis of the directive mechanism: No Directives, Sophrosyne, Sophrosyne-Oracle.

No Directives: This is a faithful representation of existing data system environments that expose fine-grained API surfaces. It exposes the API surface laid out in Table 1.

Sophrosyne: This also exposes the API surface laid out in Table 1, computes directives and returns them as part of the response for list_tables as described in §3.

Sophrosyne-Oracle: This is identical to Sophrosyne except it assumes an oracle present for computing fully accurate directives. The system looks at ground truth SQL queries to compute the set of irrelevant tables thereby guaranteeing 100% accurate directives. With these, Sophrosyne-Oracle intends to represent the upper bound on agent performance when exposed to a fine-grained API surface.

4.1 Over-exploration & Its Effects↩︎

We measure exploration using the methodology in §2.2. Figure 5 (a) shows over-exploration as a percentage of exploratory calls above the required number. With directives, Sophrosyne consistently reduces over-exploration across all models, and Sophrosyne-Oracle reduces it further, indicating that more accurate directive computation only yields better results.

Next, we examine how directives and over-exploration affect query accuracy. Figure 5 (b) shows the number of failed queries that reference tables absent from the ground truth. No Directives serves as the baseline for these failures, though not all can be attributed to over-exploration, as logical errors in query formulation are still possible. With Sophrosyne, these failures decrease consistently across all models. While we get improved accuracy with imperfect directives (§4.3), Sophrosyne-Oracle further improves it with perfect directives indicating the effect over-exploration has on query accuracy.

For Sonnet-4.5, while Sophrosyne-Oracle eliminates over-exploration as seen in Figure 5 (a), we still see failures with extra tables (Figure 5 (b)) because although the agent may not explore these tables using explicit tool calls, it is still made aware of them via information returned by get_join_info.

4.2 Execution Accuracy & Token Cost↩︎

We use token counts as a proxy for inference cost [34], [35]. Table 2 summarizes execution accuracy, input tokens and output tokens (including reasoning) across the three systems. Sophrosyne consistently improves accuracy by reducing over-exploration, and since fewer schema elements are explored, the corresponding token counts also reduce. GPT-5.4-mini is an exception, incurring a 1.2% output token overhead due to excess reasoning tokens and the model summarizing verbose SQL results before terminating for certain queries. We discuss cost in more detail in §4.3. Sophrosyne-Oracle consistently improves execution accuracy and improves token cost, further indicating that better mechanisms to compute directives will only lead to better agent performance.

3pt

4.3 Directive Accuracy & System Overhead↩︎

We now discuss the accuracy and overhead of our directive mechanism (§3). The offline phase embeds column names, costing one-tenth of a cent totally. We treat this as negligible and ignore it in our discussion ahead. To measure accuracy of computed directives we calculate recall percentage with respect to the ground truth set of tables, and across runs of the agent, this averages to 82.5%.

Table 3 summarizes the base cost and overhead incurred with Sophrosyne. The overhead incurred is 8 cents, covering cost to infer columns using GPT-5.4-nano and subsequently embed them (§3). Sophrosyne is more cost-efficient except for GPT-5.4-mini, where the base cost reduction does not absorb the overhead. Sonnet-4.5 sees the largest gains, consistent with the over-exploration reduction in Figure 5 (a).

5 Related Work↩︎

Text2SQL has a rich body of work ranging from building and fine-tuning custom models [36]–[40], to using LLMs with sophisticated prompting strategies [26], [41] and inference time scaling [42] to improve the accuracy of text2SQL. With LLMs becoming more adept at tool calling and interacting with data systems, works on agentic text2SQL focus on improving accuracy by synthesizing common failure modes in an offline phase and modifying the runtime workflow to provide relevant context to the agent [43], [44]. Our work is the first to study the effect of the exposed API surface on text2SQL accuracy and identify over-exploration as a symptom of an overlooked tradeoff in API design. Sophrosyne addresses this by augmenting API responses, and can therefore be used with any existing agent and workflow, as well as in conjunction with other works, further improving accuracy.

6 Future Work↩︎

We now outline directions for future work. Even with perfect directives, some models still over-explore, while those that avoid over-exploration can still produce failed queries with extra tables (Figure 5) suggesting the effectiveness of directives depends on the model’s instruction-following ability. Since directives are returned early, their influence may decay as the agent’s context grows [45] and returning reminders when directives are not followed could help keep the model in line. Next, data agents answering queries spanning multiple data sources [46] need to discover these sources and subsequently relevant schema elements across them. With fine-grained surfaces, the effects of over-exploration would only be exacerbated. Directives aware of multiple sources would prove essential in this setting. Finally, in addition to guiding the exploration process, directives can also provide performance hints to the agent in order to construct more efficient SQL. For example, nudging the agent to use a composite index’s column prefix can dramatically improve query performance for large tables.

7 System Prompts↩︎

7.1 Column Inference System Prompt↩︎

Below is the system prompt used for column inference in the directive computing mechanism (§3).

7.2 Agent System Prompt↩︎

Below is the system prompt using which the OpenCode agent is constructed.

References↩︎