RAG-Grounded Natural-Language-to-SQL for On-Premise Deployment in Regulated Batch Manufacturing

Natural-language access to structured manufacturing data could let plant personnel query batch records without structured query language (SQL) expertise, yet realistic multi-table industrial schemas remain hard for text-to-SQL systems, and regulated environments demand data sovereignty, traceability, and validatable behavior that cloud-hosted models cannot satisfy. This study presents a locally deployable large language model (LLM) system that translates questions into SQL over an ISA-88-aligned database built from a pharmaceutical tablet dataset of 1005 batches. The pipeline combines schema-aware prompting, retrieval-augmented generation (RAG) over validated query examples, execution-based checking, confidence scoring, and human review. Evaluation on 48 questions across three complexity tiers compared a schema-only baseline with five enhancements: controlled vocabularies, retrieved examples, analytical views, execution-guided self-consistency, and schema-specialized fine-tuning. Retrieved examples roughly doubled execution accuracy for two open-weight models, from 23–25% to 46–48%, without reducing accuracy on any question; gains concentrated the 15 two- and three-table join questions, reaching 80%. Fine-tuning a seven-billion-parameter code model on execution-validated synthetic queries raised its accuracy from 19% to 44%, and, combined with retrieval, it achieved the highest observed accuracy on the hardest tier. Results show that a lightweight, auditable, locally deployable pipeline can support regulated manufacturing analytics while flagging outputs that require human validation.