Schema-Mind — Schema-Aware RAG for Text-to-SQL
A schema-aware Retrieval-Augmented Generation system that combines semantic search, keyword retrieval, Reciprocal Rank Fusion, and foreign-key graph expansion to retrieve the right database context before generating safe, executable SQL.
2026 · AI/RAG project · Full-stack ownership
At a Glance
Retrieval
Hybrid + Graph
vector + keyword + RRF + FK expansion
Databases
2
PostgreSQL + MySQL
Validation
3 layers
structural + safety + semantic
Execution
Read-only
row limits + query timeouts
Architecture-focused project demonstrating schema-aware retrieval, graph-based context expansion, automatic synchronization, and guarded Text-to-SQL generation.
The Problem
Natural-language to SQL is not only a generation problem. An LLM may understand SQL syntax while still producing queries against tables, columns, or relationships that do not exist in the target database. Supplying an entire schema to the model also becomes inefficient as schema size grows.
The retrieval problem is therefore to identify the smallest useful subset of the database structure for a user's question. Simple semantic similarity is not sufficient on its own: exact table and column names matter, while foreign-key relationships can reveal additional tables required to answer a question correctly.
A second challenge is freshness. Database schemas evolve continuously, so a retrieval index that is not synchronized with the live database can provide stale context and lead to invalid SQL. The system needed automatic schema change detection and asynchronous re-ingestion while keeping the database itself as the source of truth.
Constraints
Architecture Overview
Source
PostgreSQL + MySQL schema introspection
Semantic
Gemini embeddings + MongoDB Atlas Vector Search
Keyword
Keyword retrieval + Reciprocal Rank Fusion
Graph
Foreign-key expansion with distance-decayed boosting
Generation
Gemini + dialect-aware read-only SQL
Validation
Structural + safety + semantic evaluation
Key Engineering Decisions
Combining semantic and keyword retrieval
Schema retrieval depends on both meaning and exact identifiers. A natural-language query may describe a concept semantically, while the database expresses that concept through exact table and column names. I therefore combined vector retrieval with keyword retrieval and used Reciprocal Rank Fusion to merge their ranked results without relying on incompatible raw score scales.
Retrieval can capture both conceptual relevance and exact database terminologyExpanding retrieval through the foreign-key graph
Relevant schema is often distributed across related tables. After the initial hybrid retrieval stage, the system expands through foreign-key relationships up to a bounded depth, applying distance-decayed boosting so directly relevant tables remain dominant while nearby relational context can still be included.
The generator receives related schema context without treating every table in the database as equally relevantKeeping the database as the source of truth
MongoDB is used as the retrieval layer, not as an authoritative representation of the database. The system introspects the live relational database and uses that structure to build its semantic units and graph, ensuring the AI layer remains grounded in the actual schema.
The retrieval index represents the database rather than becoming a competing source of truthAutomatically synchronizing schema changes
A static ingestion process would eventually diverge from the live database. PostgreSQL uses DDL event triggers with LISTEN/NOTIFY, while MySQL uses fingerprint-based change detection, allowing schema changes to trigger re-ingestion automatically.
The retrieval layer can evolve with the underlying database instead of depending on manual re-indexingValidating SQL before execution
Even grounded generation can produce malformed or unsafe SQL. The generated query therefore passes through structural validation, safety checks, and semantic quality evaluation before execution. Only queries that satisfy all gates are allowed to proceed.
Unsafe, malformed, or schema-inconsistent queries are blocked before reaching the databaseSeparating generation from execution safety
The LLM should not be trusted as the final authority over what can execute. SQL generation is constrained to a single read-only SELECT, while a separate execution layer enforces read-only mode, row limits, and timeouts.
The system treats generated SQL as untrusted input until it passes independent validation and execution controlsOutcomes
- Built a complete schema-aware RAG pipeline from live database introspection through SQL execution
- Implemented semantic retrieval using Gemini embeddings and MongoDB Atlas Vector Search
- Added keyword retrieval and Reciprocal Rank Fusion for hybrid ranking
- Implemented foreign-key graph expansion with bounded depth and distance-decayed boosting
- Built automatic schema synchronization for PostgreSQL and MySQL
- Integrated PostgreSQL DDL event triggers with LISTEN/NOTIFY for event-driven schema updates
- Implemented dialect-aware SQL generation with Gemini
- Added structural SQL validation using node-sql-parser
- Added safety validation for destructive statements, stacked queries, and common injection patterns
- Added semantic SQL quality evaluation before execution
- Implemented read-only execution with row limits and query timeouts
- Designed database adapters to support additional relational database engines
- Containerized the development environment with Docker Compose
Takeaway
“Schema-aware Text-to-SQL is fundamentally a retrieval and control problem as much as a generation problem. Semantic search finds conceptual matches, keyword search captures exact schema terminology, and graph expansion reconstructs the relationships needed for multi-table reasoning. Combined with automatic synchronization and independent SQL validation, the result is a retrieval layer that stays grounded in the live database instead of relying on the LLM's assumptions.”