From 40% to 100% SQL Generation Accuracy: Why Local AI Needs Self-Correction, Not Perfect Prompts

The author struggled to generate reliable SQL queries using a local AI model for a retail analytics copilot, initially achieving only a 40% success rate. The primary problem was the model's unreliability. The author discovered a repair loop, feeding error messages back to the model, which boosted accuracy by expecting and correcting errors. Defensive parsing was crucial, as LLM outputs are probabilistic and require robust error handling. The author then used DSPy for optimization, defining a success metric and automating prompt refinement instead of manual tuning. This resulted in a 100% success rate for generating valid SQL queries, although overall end-to-end success reached 66%. The project is a case study showing the potential of local AI, particularly in privacy-sensitive fields. The author emphasizes the importance of systems thinking, optimization, and defensive engineering over prompt engineering. The author highlights the need to design systems that handle failures and iteratively improve. The author provides actionable takeaways: implement a repair pattern, test parsing logic, and use DSPy for automated prompt optimization. Mastering local AI is a strategic advantage, especially for regulated industries where privacy is critical.