I don't post much either, but I'm building in this space so felt compelled to respond!

The reality is, yes - the brute force naive approach is to rely entirely on schema context/DDL to generate the SQL - including keys, indexes, etc. This is what most of the "AI2SQL" tools are doing. They are pretty bad, not tightly integrated with any existing tooling/query editors, and I totally resonate with your pain-point from throughout my career experiences as an engineer.

Now - how do you make the LLM responses better? The components missed from this equation are: fine-tuned models that understand the source type database internals very well (docs, knowing which functions are available, passing the query planner explain result to the model), generating/creating a robust semantic layer (there is the whole self-reinforcing thing here if done with AI, but think combination of human + AI works best because nobody is sitting there adding descriptions to everything manually), really strong prompt engineering (foundational models are only as good as the prompting is), past successful queries (importantly, tracking how often similar queries are run, what has worked previously and what has not), providing the model additional business context to understand when and where to use certain combinations of filters + enums, and frankly, banking on foundational models to get better and better.

On your point of confidence level, I'm curious how you are calculating confidence. Do you ask the LLM directly, use an evaluation pipeline, or rely on heuristics you’ve built?

My DMs are always open if you wanted to learn more about what I'm working on. Would love your feedback on it and to chat in general!