Support Request: SQL Agent for Legacy Database

[AdeelKhan-Git]

I am building an AI-powered SQL analytics agent that generates SQL queries against a large legacy database. The database has been developed over many years, so the schema contains inconsistent naming, poorly documented columns, encoded values, and relationships that are not always obvious.

Github link: https://github.com/AdeelKhan-Git/optimize-agno

I would like guidance on the best architecture and agent design for handling these challenges reliably.

1. Legacy / Poorly Documented Schema

The database contains many columns whose meanings are not self-explanatory.

For example:

• A column such as jtype may contain values like S and O, where:

  • S = Subscription
  • O = Open Access

• There are many similar coded columns throughout the database.

• Column names and values often do not provide enough information for an LLM to understand their actual business meaning.

I have created a glossary/metadata tool that can provide definitions and resolve these coded values, but I would like to know whether there is a better recommended architecture for exposing this type of metadata to the SQL agent.

2. Foreign-Key Relationships Are Missing or Inconsistent

The legacy database does not always have proper foreign-key constraints.

For example, I may have:

tbl_ebm.journal_id

and:

journal.journal_id

which logically represent the same relationship, but the database either has no FK constraint or the columns have incompatible SQL data types.

This makes automatic relationship discovery difficult.

The agent therefore needs to understand logical relationships, not just rely on database-defined foreign keys.

What is the recommended approach for providing these logical relationships to an SQL agent?

3. Different SQL Dialects

The system may work with databases such as:

• Microsoft SQL Server
• MySQL
• PostgreSQL
• SQLite

The agent needs to generate dialect-specific SQL correctly.

For example, syntax, functions, identifier quoting, pagination, and date operations can differ between databases.

I currently provide dialect information to the agent, but I would like to know whether the recommended approach is to use separate dialect-specific prompts/tools or a single agent with dialect metadata.

4. Large Schema / Table Selection

The database contains many tables and columns.

Providing the entire schema to the final SQL-generation agent consumes a significant amount of context and can make the model less reliable.

I therefore created a table-selection stage that first determines which tables are relevant and then passes only the selected schema to the SQL-generation agent.

The current architecture is roughly:

User Question
      ↓
Table Selector Agent
      ↓
Relevant Tables
      ↓
SQL Generation Agent
      ↓
SQL Validation / Execution
      ↓
Answer

I would like guidance on whether this multi-stage architecture is the recommended approach for a legacy database with a large schema.

5. Resolving Business Values Before SQL Generation

Another problem is that users ask questions using business terminology rather than the actual database values.

For example, a user may ask:

"How many Open Access journals are there?"

But the database stores:

jtype = 'O'

The SQL agent needs to understand that the user's phrase "Open Access" corresponds to jtype = 'O'.

I have implemented tools such as:

• get_column_codes
• resolve_filter_value
• get_sample_values

The agent can use these tools to resolve values before constructing the WHERE clause.

I would like to know whether this tool-based approach is preferable to putting all possible code mappings directly into the system prompt.

6. Sample Values Are Important

In many legacy tables, the schema alone is insufficient.

For example:

column: ebm_role_name

may contain values such as:

Editor-in-Chief
EIC
Managing Editor
Associate Editor
...

The model needs access to actual sample/distinct values to correctly construct filters.

However, exposing too many sample values can significantly increase context usage.

What is the recommended strategy for providing sample values to the agent without unnecessarily increasing the context?

7. Ambiguous Column Names

Some columns have names that are difficult to interpret.

For example, a column may be named:

abbrev_journal_title_dummy

or contain legacy naming conventions that don't clearly describe the business concept.

The agent sometimes needs additional metadata to determine whether a column should be used for:

• filtering
• grouping
• joining
• aggregation
• displaying results

I am considering maintaining richer metadata for each column, such as:

column_name
business_definition
data_type
allowed_values
example_values
synonyms
related_columns

Is this a recommended approach for SQL agents operating over legacy databases?

8. Preventing Incorrect JOINs

One of my biggest concerns is incorrect joins.

Because the legacy database does not always contain explicit foreign keys, an LLM might infer a relationship simply because two columns have similar names.

For example:

journal.journal_id
tbl_ebm.journal_id

may be a valid logical relationship, but other similarly named columns may not be.

I want the agent to use verified relationships only, rather than allowing the LLM to freely guess joins.

Would you recommend maintaining a separate relationship/semantic layer containing information such as:

table_a
column_a
table_b
column_b
relationship_type
verified = true

and exposing that to the agent?

9. SQL Generation Reliability

The goal is not simply to generate syntactically valid SQL.

The agent needs to generate SQL that is semantically correct for the legacy database.

For example, this query may be syntactically valid:

WHERE jtype = 'Open Access'

but semantically wrong if the database actually stores:

jtype = 'O'

Therefore, I need the agent to understand:

User terminology → Business meaning → Database column → Database value → SQL

rather than simply mapping the user's words directly into SQL.

10. Structured Output Issues with Local LLMs

I am also experimenting with local models through Ollama, including Granite models.

I have encountered cases where the model returns a raw string instead of the expected structured/Pydantic output.

For example, the application expects a structured response for table selection, but sometimes receives something equivalent to:

"journal"

instead of the expected structured object.

This makes the agent pipeline fail even though the model technically generated a response.

I would like recommendations for reliable structured output handling with local models.

11. Context-Length Management

I am currently experimenting with context sizes such as:

num_ctx=16384

while some models advertise much larger maximum contexts.

The challenge is that the SQL agent's instructions, schema information, glossary information, relationship metadata, sample values, and user query can all consume context.

I would like guidance on how to design the context pipeline efficiently rather than simply increasing the context window.

12. Model Size vs. SQL Accuracy

I am testing different local models, including:

Granite 4.1 8B
Granite 4.1 30B

The larger model generally provides better reasoning, but it also requires significantly more GPU memory and can affect latency.

I would like to understand whether for this type of SQL-agent workload it is generally better to:

• use a smaller model with strong schema/metadata retrieval, or
• use a larger model with more reasoning capability.

13. Current Agent Architecture

The current system is approximately:

                User Question
                     │
                     ▼
              Table Selector
                     │
                     ▼
          Relevant Table/Schema
                     │
          ┌──────────┴──────────┐
          ▼                     ▼
      Glossary              Relationships
       Tool                    Metadata
          │                     │
          └──────────┬──────────┘
                     ▼
               SQL Agent
                     │
                     ▼
             SQL Validation
                     │
                     ▼
               SQL Execution
                     │
                     ▼
             Result / Insights

The main goal is to make this system reliable enough to work with a real-world legacy database where the schema itself cannot be trusted as the complete source of truth.

Questions I Would Like Help With

1. What is the recommended architecture for an AI SQL agent working with a large legacy database?
2. Should I maintain a separate semantic/metadata layer for table definitions, column definitions, coded values, and relationships?
3. What is the best way to represent logical relationships when database foreign keys are missing?
4. Should schema retrieval, glossary retrieval, relationship retrieval, and sample-value retrieval be separate retrieval steps?
5. How should I prevent the LLM from inventing JOINs or database values?
6. What is the best strategy for handling encoded columns such as S = Subscription and O = Open Access?
7. How much sample data should be provided to the model?
8. Is the table-selector → SQL-generator architecture appropriate, or is there a better approach?
9. What techniques are recommended for reliable structured output with local Ollama models?
10. How should context length be managed when schema + instructions + metadata become large?
11. For legacy SQL generation, what are the recommended validation and correction steps before executing generated SQL?
12. Would a semantic layer / knowledge graph / metadata catalog be beneficial for this use case?

The key challenge is that this is not a clean modern database. The AI agent must understand the business meaning behind a legacy schema rather than simply reading table and column names. I would appreciate recommendations for an architecture that makes the SQL generation reliable, grounded, and resistant to incorrect joins, incorrect coded values, and hallucinated schema information.