Abstract
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 fully on-premise large language model (LLM) system that translates questions into SQL over an ISA-88-aligned database built from a pharmaceutical tablet dataset of 1,005 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 local models, from 23–25% to 46–48%, without reducing accuracy on any question; gains concentrated in two- and three-table joins, where accuracy reached 80%. Fine-tuning a 7-billion-parameter code model on execution-validated synthetic queries was the only intervention that improved the hardest tier, and combined with retrieval it achieved the highest accuracy there. Results show that a lightweight, auditable local pipeline can support regulated manufacturing analytics while flagging outputs that require human validation.