Building a Production-Ready Optimised Text-to-SQL AI System Using LLMs, Vector Databases, and Schema Intelligence
Artificial Intelligence is changing the way users interact with databases.
Instead of writing SQL manually, users now expect to ask questions like:
“Show me revenue by city for the last 6 months.”
or
“Which candidates cleared the technical interview with scores above 80?”
and instantly receive the correct SQL query and chart.
This is called Text-to-SQL Generation.
In this blog, we will learn how to build a production-grade Text-to-SQL AI system using:
- LLMs (GPT/OpenAI)
- PostgreSQL
- pgvector
- Ruby on Rails
- Schema metadata
- Join intelligence
- Vector search
- SQL validation
- Semantic layers
We will also cover a real-world ATS (Applicant Tracking System) example with:
- Jobs
- Candidates
- Applications
- Interview stages
- Scores
- Recruiters
This article is designed for developers building:
- AI dashboards
- AI analytics platforms
- AI copilots
- BI systems
- Internal admin tools
- Enterprise AI products
Why Text-to-SQL Is Hard
Most developers initially think:
User Question → Send Full DB Schema → GPT → SQLBut this fails badly in production.
Problems:
- Large schemas exceed token limits
- LLM hallucinates table names
- Wrong joins are generated
- Business meanings are misunderstood
- SQL becomes slow and unsafe
- Different organizations have different schemas
For example:
A user asks:
Show top recruiters by candidate conversion.
But the database contains:
candidate_profiles
job_candidate_mappings
recruiter_users
interview_round_results
The AI may fail completely.
This is why modern AI analytics systems use:
Metadata + Vector Search + Join Graph + LLM
instead of sending raw databases directly.
Final Architecture
┌─────────────────┐
│ Client Database │
└────────┬────────┘
│
▼
┌─────────────────┐
│ Schema Extractor│
└────────┬────────┘
│
▼
┌─────────────────┐
│ Metadata Store │
└────────┬────────┘
│
▼
┌─────────────────┐
│ Vector Embedding│
└────────┬────────┘
│
▼
User Question ───► Relevant Table Retrieval
│
▼
┌─────────────────┐
│ Prompt Builder │
└────────┬────────┘
│
▼
┌─────────────────┐
│ LLM │
└────────┬────────┘
│
▼
┌─────────────────┐
│ SQL Validator │
└────────┬────────┘
│
▼
┌─────────────────┐
│ Query Execution │
└─────────────────┘
Step 1 — Extract Database Metadata
The first step is extracting schema information.
We never send the entire database to the AI every time.
Instead, we store metadata.
Example Rails Tables
create_table :organizations do |t|
t.string :name
end
create_table :schema_tables do |t|
t.references :organization
t.string :table_name
t.text :description
end
create_table :schema_columns do |t|
t.references :schema_table
t.string :column_name
t.string :data_type
t.string :business_name
t.text :description
end
Example Metadata
{
"table": "jobs",
"description": "Stores job openings created by recruiters",
"columns": [
{
"name": "id",
"type": "integer"
},
{
"name": "title",
"type": "string"
},
{
"name": "department",
"type": "string"
}
]
}
Step 2 — Store Relationships and Joins
This is the most important part.
Without relationship intelligence, AI generates incorrect joins.
ATS Example Database
jobs
id
title
recruiter_id
candidates
id
name
experience
applications
id
candidate_id
job_id
status
interview_rounds
id
application_id
round_name
score
Relationship graph:
recruiters
|
| recruiter_id
|
jobs
|
| job_id
|
applications
|
| application_id
|
interview_rounds
Relationship Storage
create_table :schema_relationships do |t|
t.references :organization
t.string :from_table
t.string :from_column
t.string :to_table
t.string :to_column
end
Example:
{
"from_table": "applications",
"from_column": "candidate_id",
"to_table": "candidates",
"to_column": "id"
}
Step 3 — Add Business Intelligence Layer
Raw database columns are often confusing.
Example:
cand_exp
AI may not understand this means:
Candidate Experience
So we add business labels.
{
"column": "cand_exp",
"business_name": "Candidate Experience",
"description": "Total years of professional experience"
}
This dramatically improves accuracy.
Step 4 — Create Vector Embeddings
Now we create embeddings for tables and columns.
Why?
Suppose database has:
- 400 tables
- 7000 columns
We cannot send everything to GPT.
Instead:
Question → Find Relevant Tables → Send Only Those Tables
Example Embedding Content
Table: applications
Description:
Stores candidate applications for jobs.
Contains status, candidate mapping, and timestamps.
Create embeddings using OpenAI.
PostgreSQL pgvector Setup
CREATE EXTENSION vector;
Table for Embeddings
CREATE TABLE schema_embeddings (
id BIGSERIAL PRIMARY KEY,
organization_id BIGINT,
table_name TEXT,
content TEXT,
embedding vector(1536)
);
Generate Embeddings in Ruby
client = OpenAI::Client.new
response = client.embeddings(
parameters: {
model: "text-embedding-3-small",
input: content
}
)
vector = response.dig("data", 0, "embedding")
Store in PostgreSQL.
Step 5 — Retrieve Relevant Tables
Now user asks:
Show candidates who cleared technical rounds.
Convert question into embedding.
Search nearest vectors.
Returns:
applications
candidates
interview_rounds
jobs
instead of entire database.
pgvector Similarity Query
SELECT *
FROM schema_embeddings
ORDER BY embedding <=> '[0.12, 0.98, ...]'
LIMIT 5;
Step 6 — Build Intelligent Prompt
Now we dynamically construct prompts.
Example Prompt
You are a PostgreSQL expert.
Database Schema:
Table candidates
- id
- name
- experience
Table applications
- id
- candidate_id
- job_id
- status
Table interview_rounds
- id
- application_id
- round_name
- score
Relationships:
applications.candidate_id -> candidates.id
interview_rounds.application_id -> applications.id
Question:
Show candidates who scored above 80 in technical rounds.
Return only SQL.
Generated SQL
SELECT
c.name,
ir.score
FROM candidates c
JOIN applications a
ON a.candidate_id = c.id
JOIN interview_rounds ir
ON ir.application_id = a.id
WHERE ir.round_name = 'Technical'
AND ir.score > 80;
Example 2 — ATS Analytics Query
Now let’s try something more advanced.
User asks:
Show recruiters whose jobs have highest candidate selection ratio.
Relevant tables:
recruiters
jobs
applications
Generated prompt:
Relationships:
jobs.recruiter_id -> recruiters.id
applications.job_id -> jobs.id
Generated SQL
SELECT
r.name,
COUNT(
CASE
WHEN a.status = 'selected' THEN 1
END
) * 100.0 / COUNT(a.id) AS selection_ratio
FROM recruiters r
JOIN jobs j
ON j.recruiter_id = r.id
JOIN applications a
ON a.job_id = j.id
GROUP BY r.name
ORDER BY selection_ratio DESC;
Step 7 — Validate SQL Before Execution
Never directly execute AI-generated SQL.
This is extremely dangerous.
Reject:
DROP TABLE users;
Reject:
DELETE FROM applications;
Allow only:
SELECT
initially.
Ruby SQL Validation
Use:
gem 'pg_query'
Validator Example
parsed = PgQuery.parse(sql)
statement = parsed.tree.stmts.first.stmt
unless statement['SelectStmt']
raise "Only SELECT queries allowed"
end
Step 8 — Execute SQL
Once validated:
results = ActiveRecord::Base.connection.execute(sql)
or
PG.connect(...)
Step 9 — Auto SQL Correction
LLMs still make mistakes.
Example:
SELECT candidate_name
FROM candidates;
Actual column:
name
Database error:
column candidate_name does not exist
Send error back to AI.
Prompt:
Fix this SQL.
Error:
column candidate_name does not exist
AI corrects query.
This dramatically improves reliability.
Step 10 — Semantic Layer (Very Important)
Enterprise systems do not expose raw SQL concepts.
Instead they define metrics.
Example:
{
"metric": "Selected Candidates",
"formula": "COUNT(CASE WHEN applications.status = 'selected' THEN 1 END)"
}
Another:
{
"metric": "Average Technical Score",
"formula": "AVG(interview_rounds.score)"
}
Now AI uses business metrics instead of raw columns.
This improves:
- Accuracy
- Explainability
- Consistency
- Reusability
Multi-Tenant SaaS Architecture
Suppose your SaaS has 500 organizations.
Each organization has different schemas.
Architecture:
organizations
schemas
schema_tables
schema_columns
schema_relationships
schema_embeddings
semantic_metrics
Flow:
User Question
↓
Find Organization
↓
Retrieve Relevant Tables
↓
Retrieve Relationships
↓
Build Prompt
↓
LLM Generates SQL
↓
Validate SQL
↓
Execute Query
Important Optimization — Join Path Finder
One advanced feature is automatic join path discovery.
Example:
User asks:
Show cities with highest candidate placements.
Possible join path:
cities
↑
addresses
↑
candidates
↑
applications
Instead of asking LLM to figure joins itself, your backend can calculate valid join paths.
This improves accuracy massively.
You can implement this using:
- Graph traversal
- BFS
- DFS
- Neo4j
- PostgreSQL recursive queries
Recommended Tech Stack
Backend
- Ruby on Rails
- Sidekiq
- PostgreSQL
- Redis
AI
- OpenAI GPT-5
- OpenAI Embeddings
Vector DB
- pgvector
- Qdrant
- Pinecone
SQL Parsing
- pg_query
Queue Processing
- Sidekiq
Security Best Practices
Never skip these.
1. Allow Only SELECT Queries
Never allow:
- UPDATE
- DELETE
- DROP
- ALTER
2. Timeout Long Queries
Prevent:
SELECT * FROM giant_table;
Use:
statement_timeout
3. Row Limits
Always add:
LIMIT 100
unless analytics specifically requires more.
4. Read-Only DB User
Never connect AI using admin credentials.
Use read-only users.
Future Improvements
Once basic system works, add:
1. Chart Recommendation
AI suggests:
- Bar chart
- Pie chart
- Line chart
- Heatmap
2. Follow-Up Questions
Example:
Show selected candidates.
Then:
Only for engineering department.
Maintain conversational memory.
3. Query Caching
Cache:
Question → SQL
for repeated queries.
4. Fine-Tuned Models
Store:
Question → SQL
pairs.
Train organization-specific models.
This is exactly how enterprise AI copilots evolve.
Real Production Insight
Most successful AI analytics systems are not “just GPT”.
They are:
Metadata System
+ Join Intelligence
+ Semantic Layer
+ Retrieval System
+ SQL Validation
+ LLM
The LLM is only one part.
The real intelligence comes from:
- schema understanding
- relationships
- business context
- retrieval quality
- query correction
Final Thoughts
Building a production-grade Text-to-SQL system is one of the most exciting areas in AI engineering today.
When done correctly, it allows non-technical users to interact with databases naturally.
Instead of learning SQL, users simply ask questions.
This creates massive opportunities in:
- SaaS analytics
- BI tools
- ATS platforms
- CRM systems
- Finance dashboards
- Healthcare analytics
- ERP systems
- Enterprise copilots
The key lesson is:
Do not send raw databases to LLMs.
Instead build:
Metadata + Retrieval + Relationships + Semantic Intelligence
around the LLM.
That is how modern AI data systems are actually built in production.
Bonus — Example End-to-End Flow
User:
"Show engineering jobs with highest technical interview scores"
↓
Vector Search Retrieves:
- jobs
- applications
- interview_rounds
↓
Join Graph:
jobs.id -> applications.job_id
applications.id -> interview_rounds.application_id
↓
LLM Prompt Built
↓
Generated SQL
↓
Validation
↓
Execution
↓
Chart Output
Conclusion
Text-to-SQL is much more than prompt engineering.
The real challenge is designing:
- metadata systems
- retrieval pipelines
- join intelligence
- semantic layers
- secure execution systems
Once these are designed properly, the LLM becomes extremely powerful.
And this is exactly the direction modern enterprise AI products are moving toward.
Comments
Post a Comment