Retrieving Beyond Documents - Text-to-SQL, Web Search, and Database-Level Control

An agentic RAG system should not force every source into the vector store. Documents, relational tables, external information, and complete files have different access patterns. The agent becomes more useful when it can choose among specialized retrieval tools while remaining inside strict security boundaries.

Structured data is the clearest example. A sales table contains customers, products, quantities, prices, dates, and totals. Converting every row into prose chunks would lose the main advantage of the database: exact filtering, aggregation, sorting, grouping, and calculation.

A text-to-SQL tool lets the agent translate a natural-language request into a database query. The tool executes the query and returns the result to the main model for explanation.

The basic flow is:

1. The user asks a structured-data question.
2. The main agent selects the SQL tool.
3. The model writes a query against the permitted schema.
4. The application executes the query through a restricted database connection.
5. The database returns rows or an aggregate.
6. The tool result is traced.
7. The main agent explains the result.

There are two major design patterns.

The first pattern exposes predefined parameterized queries. Examples include listing documents, counting chunks, retrieving recent activity, filtering documents by metadata, or returning sales totals for a named customer. This pattern is easier to secure and test because the model chooses among known operations rather than writing arbitrary SQL.

The second pattern allows raw SQL generation. This is more flexible. The user can ask for a total, breakdown, date filter, ordering, or grouping that was not anticipated by a fixed template. The cost is a larger security and validation problem.

Application-level validation is not a sufficient security boundary for raw SQL. A Python function can attempt to block DELETE, UPDATE, INSERT, DROP, or access to other tables, but the parser can be incomplete, the validation can be bypassed, or a fallback can execute an unexpected query. The database must enforce the restriction.

The correct pattern is a dedicated PostgreSQL identity with the minimum required permissions. For the sales example, the identity receives SELECT permission on one table and no permission to modify data or access unrelated tables. Even if the model generates destructive SQL, PostgreSQL rejects it.

The distinction is critical:

• The model prompt tells the agent what it should do.
• Application validation checks normal requests.
• Database permissions determine what it can actually do.

The final boundary must exist in the database.

An early design overengineered this with a database function and remote procedure call. The function was intended to execute SQL as a reader, but it complicated the architecture and did not produce the intended behavior. A simpler dedicated read-only database user was easier to reason about and test.

A second bug exposed the danger of hidden fallbacks. When the intended aggregate query failed, the application ignored the failure and returned the entire sales table. The feature appeared to work because the model still received data, but the data did not match the SQL that appeared in the trace.

The user had asked for the total value of all orders placed by Metro Office. The generated SQL used SUM and should have returned one row with a value of 3,524. Instead, the fallback returned all 12 rows. Directly running the SQL in the database proved that the correct result was a single aggregate. Removing the fallback and fixing the restricted connection restored the expected behavior.

This incident establishes a general rule: a failed retrieval tool must fail explicitly. It must not silently substitute a broader query, unrelated result, or full-table return. A transparent error allows the agent or operator to correct the problem. A hidden fallback creates a plausible but ungrounded answer.

After the fix, the SQL tool supported several useful interactions:

• "What is the total value of all orders placed by Metro Office?"
• "Give me a breakdown of those orders."
• "Show all order data."

The first query produced the aggregate. The follow-up generated a SELECT over product name, quantity, unit price, and related fields. The agent used conversation history to preserve the customer constraint.

The database connection itself can create operational failures. A managed PostgreSQL service may expose a direct connection and a transaction-pooler connection on different ports. The restricted SQL user initially attempted the wrong port. Updating the connection string to the pooler port allowed the read-only role to operate. This was not a model problem. It was infrastructure configuration.

Complex databases need additional design. A production schema may contain many normalized tables, foreign keys, internal identifiers, and sensitive columns. Giving a model the full schema increases SQL complexity and security risk. Database views can present a simplified, denormalized, read-only surface designed for agent access.

A view can:

• Join the relevant tables.
• Rename technical columns.
• Exclude private fields.
• Precompute useful values.
• Restrict the available rows.

The read-only agent identity can receive SELECT permission on the view rather than on operational tables.

The SQL tool definition should describe the permitted schema precisely. The model needs the intended table or view names, columns, and the meaning of important fields. It should not be shown credentials or unrelated schemas.

External search is another retrieval path. Private documents and internal tables cannot answer every question. A user may request current weather, recent public information, or material that is not present in the knowledge base. A web-search tool gives the agent a controlled fallback.

The web-search flow is:

1. The main agent determines that the request requires external information.
2. It calls the configured search provider with a focused query.
3. The provider returns search material.
4. The result is traced and supplied to the model.
5. The model produces an answer based on the returned information.

A test request asked for the latest weather in Galway, Ireland. The trace showed the exact external query and the returned search material. This proved that the system used the tool rather than relying on model memory.

The application used Tavily as the initial provider. The provider name, enablement state, and credential were placed in global settings. This makes the feature adjustable through the administrative interface, but it also raises the secret-handling question from Lesson 5. A shared search key may be safer in a server environment variable if ordinary users do not need to change it.

A locally operated search service can reduce dependence on a hosted provider. The important design point is that web search remains behind a tool interface. The agent does not need to know whether the provider is cloud-hosted or local.

Tool routing should be explicit. The system prompt can describe each tool's purpose:

• Search documents for private unstructured knowledge.
• Query the sales database for structured order data.
• Search the web for external or current information.
• Analyze a document when the full file is required.

The agent should not use web search merely because document search returned no results. A zero result may be caused by a bad filter or query. It should first decide whether the answer is expected to exist internally. Likewise, it should not query the sales table for product-manual instructions simply because SQL is available.

Multiple tools can be used in one strategy. A user could ask for internal sales performance and public market context. The agent may query the database and then search externally. The final synthesis must distinguish the two result types and avoid blending them without explanation.

Every tool needs a narrow permission set. The SQL tool receives a read-only database identity. The web-search tool receives only the search credential. The document tool receives access to the authenticated user's permitted records. A subagent receives the selected document, not broad administrative access.

Observability should show:

• Which tool was selected.
• The arguments generated by the model.
• The SQL query or web query.
• The rows or search material returned.
• Errors and permission denials.
• The final synthesis.

Security testing should deliberately attempt unsafe behavior. The SQL role should be asked to delete data and query unrelated tables. The database should reject both. A successful rejection is evidence that the boundary exists below the model.

Practical tool-security rules:

• Do not convert structured data into document chunks when SQL is the natural interface.
• Prefer parameterized query tools when flexibility is not required.
• Use a dedicated read-only database identity for raw SQL.
• Grant access only to the necessary table or view.
• Never hide a failed query behind a broader fallback.
• Test destructive and cross-table attempts.
• Use views to simplify complex schemas and exclude sensitive fields.
• Give the web tool only the credential it needs.
• Route tools according to source type and user intent.
• Trace every query, result, error, and final answer.