I built an "Agentic" SQL Generator because LLMs are bad at syntax.

#webdev #ai #dataengineering #saas

I’ve been quiet for the last week. No marketing, no tweets.
I’ve been heads-down refactoring the core engine of my tool, NeoShift BI.

Building a "Text-to-SQL" tool is easy. There are 100 tutorials on how to do it.
Building one that actually works in production—handling messy APIs, SQL errors, and client-facing dashboards—is a nightmare.

Here is a breakdown of the 3 major engineering challenges I solved this week.

1. The "Self-Healing" SQL Engine (Agentic AI)

The Problem:
LLMs are great, but they hallucinate.
Sometimes they try to query a column that doesn't exist. Sometimes they mess up a JOIN syntax.
In the old version, if the generated SQL failed, the user just got a red error message: Postgres Error: Column 'revenue' does not exist.

The user would then leave. Churn. 📉

The Fix:
I implemented an Agentic Loop.
Now, when the user asks a question:

AI Generates SQL.
System Executes SQL.
IF Error: The System catches the error message (e.g., Syntax Error).
Loop: The System feeds the original SQL + the Error Message back to the AI.
AI Fixes: "Oh, I see. I used the wrong alias. Here is the corrected query."
Success.

The user never sees the error. They just see the chart. It’s a "Junior Dev" that fixes its own bugs.

2. The "Real World" API Connector

The Problem:
Most BI tools have a "Generic API Connector" that is useless.
They usually only support a basic GET request.
But if you've ever worked with real SaaS APIs (Stripe, Shopify, or internal tools), you know they require:

• Cursor-based Pagination (fetching 10,000 rows).

• Dynamic Headers (Auth tokens).

• State-based Syncing (Only fetch data that changed since last_sync).

The Fix:
I re-wrote the ingestion engine to support all of this.
You can now define:

• Path/Query Parameters

• Body Parameters (for complex POST search queries)

• Incremental Syncing via Cursor + State

Basically, if you can curl it, I can chart it.

3. Architecture for "Shared Dashboards" (Solving the Cost Problem)

The Problem:
I wanted to let users share dashboards with their clients (Client Portals).
The industry standard (Power BI) charges a "View License" per user. This is because every time a user views a dashboard, it hits the database.

If I let my users have unlimited viewers, and they all hit BigQuery at once, I’d go bankrupt on compute costs. 💸

The Fix: Smart Caching Strategy
I implemented a middleware caching layer using MongoDB.

Data Ingestion: We sync data from your source (API/SQL) into BigQuery.
View Caching: When a dashboard is loaded, we check the TTL (Time To Live).

• Pro Plan: 1 Hour TTL.

• Business Plan: 30 Minute TTL.

The Fetch: If the cache is valid, we serve the data instantly from MongoDB (Fast & Cheap). If expired, we hit BigQuery once, update the cache, and serve.

The Result:

• My users get Unlimited Viewers for a flat rate.

• Dashboards load in milliseconds (no waiting for heavy SQL queries).

• My BigQuery bill doesn't explode.