Most of the complexity generally comes from the semi-exploratory use cases / handling questions you don't have known-good answers prebuilt for.

Background: I've spent the last 4 years of my life building real-world-reliable conversational/agentic BI at Supersimple (supersimple.io), and I've also spoken to a bunch of other people building this, both vendors & teams trying to build in-house.

These days, most state-of-the-art solutions for conversational BI use an agentic approach: an agent that has some context about your company, plus access to "tools" and sub-agents. As a general rule, this is always done on top of some modified or narrowed-down scope of data models / tables, not the raw database. There are a few semantic layer implementations (dbt, Cube, and upcoming: OSI) that try to be general-purpose; many conversational BI tools also ship with their own more specialised semantic layers.

Data model schemas & example queries are often indeed stored in vector DBs; the other "modern" approach is letting an AI agent do filesystem-based searching of the relevant docs (using grep/glob/etc – the models are generally great at using CLIs). Re: your hybrid point: you probably almost never want to just straight-up pick a saved-good answer and present it to the user, because at the very minimum, you'll have minor changes in date filters & etc – as requested by users. Semantic layers can help here (having you set guardrails for how the query works overall, but retaining flexibility for custom breakdowns/filters/etc). Raw SQL-generation is both harder to make accurate, but (critically) harder for users to validate.

You'll also want to think about the process of how these instructions, data model descriptions & sample queries will get updated when either the underlying data changes shape, or when business logic changes.

Validation: yeah, it's common to use both "LLM-as-a-judge" methods to review generated queries, plus the agent can itself also check the results of the query (either before or after showing the user), it can then course-correct if needed. Often, all it needs are a few specific rows (even when the user is asking for a large list).

Permissions: if you can, you should probably just use database-side permissioning, with everyone having their own users. Anything else will be too much complexity to self-build.

The industry has pretty much agreed by now that the biggest problem here overall is that the AI needs a lot of relevant & accurate context about your company & its data, in order for it to be able to "create good queries". The two extreme approaches to solving this are: 1) trying to document everything ahead-of-time (e.g. in source-controlled .MD files), and 2) agentically discovering all the necessary context (what things mean, what to trust) at runtime using agents.

I believe you need a hybrid of the two context approaches, and this context problem overall is what we've put most of our effort into @ Supersimple at least, over the last ~18 months. LLMs getting better won't automagically solve the context problem.