DEV Community: ABU SUFIAN

Building an AI-Powered Natural Language SQL Interface: An MVP Journey

ABU SUFIAN — Sun, 22 Feb 2026 19:17:42 +0000

Have you ever wished you could just ask your database a question in plain English and get an answer? That's exactly what we built—an experimental AI-powered chat interface that translates natural language into safe, read-only SQL queries.

In this post, I'll walk you through how we built it, the security challenges we faced, and the performance optimizations that make it production-ready.

The Problem We're Solving

Most business users and even developers struggle with SQL. They know what data they want, but crafting the right query is time-consuming. Traditional BI tools require learning complex interfaces, and even SQL experts waste time on repetitive queries.

Our solution: A conversational interface where users ask questions like:

"Monthly revenue last 12 months"
"Top reps by revenue"
"Campaign ROI: budget vs revenue"
"Unpaid commissions per rep"

The system handles the SQL generation, execution, and visualization automatically.

What It's Capable Of

Core Features

Natural Language Processing: Accepts text or voice input
Intelligent SQL Generation: Uses LLM (Groq/Llama 3.1) to generate queries
Read-Only Safety: Only executes SELECT/WITH queries
Rich Visualizations: Tables, line charts, and bar charts
Conversational Context: Maintains conversation history
Clarification Flow: Asks follow-up questions when needed
Server-Side Execution: All database access is server-side (LLM never touches DB)

What It Doesn't Do (By Design)

No write operations (INSERT/UPDATE/DELETE)
No authentication or multi-tenant support
No dashboard builder or saved reports
No attempt to replace full BI tools

This is intentionally minimal—built for fast data exploration, not enterprise analytics.

Architecture Overview

┌─────────────┐
│   Frontend  │  Next.js + React + Recharts
│  (Chat UI)  │
└──────┬──────┘
       │ HTTP POST /api/chat
       ▼
┌─────────────────────────────────────┐
│         Next.js API Route           │
│    /api/chat (route.ts)             │
└──────┬──────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────┐
│      Chat Service Layer               │
│  - Schema introspection (cached)      │
│  - Schema shortlisting                │
│  - Prompt building                    │
│  - LLM call                           │
│  - JSON parsing & validation          │
└──────┬──────────────────────────────┘
       │
       ├──► SQL Guard (safety validation)
       │
       └──► PostgreSQL (read-only)

Key Components

Frontend (src/components/Chat/):

ChatShell.tsx - Main chat interface
MessageBubble.tsx - Message rendering with SQL preview
ResultChart.tsx - Recharts-based visualizations
VoiceButton.tsx - Web Speech API integration

Backend (src/Server/):

chat/service.ts - Core orchestration logic
chat/prompts.ts - LLM prompt engineering
db/introspect.ts - Schema introspection
db/schemaCache.ts - TTL-based caching
safety/sqlGuard.ts - SQL validation (247 lines of security)
llm/groq.ts - Groq API client

Security: The MVP Guardrails

This was our biggest challenge. How do you safely execute LLM-generated SQL without a full SQL parser?

Our Security Model

We built a defense-in-depth approach with multiple layers:

1. Statement Validation

// Only single statements allowed
const parts = sql.split(";").filter(Boolean);
if (parts.length !== 1) {
  throw new Error("Only single-statement SQL is allowed.");
}

2. Query Type Enforcement

// Only SELECT/WITH queries
if (!(sql.toLowerCase().startsWith("select") || 
      sql.toLowerCase().startsWith("with"))) {
  throw new Error("Only SELECT/WITH queries are allowed.");
}

3. Keyword Blocking

We block dangerous keywords using word-boundary regex:

const blockedKeywords = [
  "insert", "update", "delete", "drop", "alter",
  "truncate", "create", "grant", "revoke",
  "copy", "call", "execute", "prepare",
  "begin", "commit", "rollback", "set",
  // ... and more
];

for (const kw of blockedKeywords) {
  const re = new RegExp(`\\b${escapeRegExp(kw)}\\b`, "i");
  if (re.test(sql)) {
    throw new Error("Write/admin operations are not allowed.");
  }
}

4. Dangerous Function Blocking

We block Postgres functions that could be used for exfiltration:

const blockedFunctions = [
  "pg_read_file",
  "pg_read_binary_file",
  "pg_ls_dir",
  "dblink",
  "postgres_fdw",
  // ...
];

5. SELECT * Prevention

// Blocks accidental data dumps
if (/\bselect\s+\*\b/i.test(sql)) {
  throw new Error("SELECT * is not allowed. Please select specific columns.");
}

6. PII Column Blocking (Optional)

const piiTokens = ["email", "phone", "mobile", "ssn", "passport", "address"];

for (const tok of piiTokens) {
  const re = new RegExp(`\\b${escapeRegExp(tok)}\\b`, "i");
  if (re.test(sql)) {
    throw new Error(`Query references sensitive field '${tok}'. Not allowed.`);
  }
}

7. LIMIT Enforcement

// Adds default LIMIT if missing, clamps if too high
const limitMatch = sql.match(/\blimit\s+(\d+)\b/i);

if (!limitMatch) {
  sql = `${sql} LIMIT ${defaultLimit}`;
} else {
  const requested = parseInt(limitMatch[1], 10);
  if (requested > maxLimit) {
    sql = sql.replace(/\blimit\s+\d+\b/i, `LIMIT ${maxLimit}`);
  }
}

8. Comment Stripping

We remove SQL comments to prevent bypass attempts:

// Remove -- line comments
sql = sql.replace(/--.*$/gm, "");
// Remove /* block comments */
sql = sql.replace(/\/\*[\s\S]*?\*\//g, "");

Security Limitations

⚠️ Important: This is MVP-level safety. For production systems, you should:

Use a proper SQL parser (like node-sql-parser or pg-query-parser)
Implement allow-listing of tables/columns
Add query timeout enforcement
Use read-only database users
Implement rate limiting
Add audit logging

Our approach is best-effort accident prevention, not a security guarantee.

Performance Optimizations

LLM calls are expensive (time and money). Here's how we optimized:

1. Schema Caching

Schema introspection runs once and caches for 10 minutes:

const CACHE = new Map<string, Cached<any>>();

export async function getSchemaSummaryCached(ttlMs = 10 * 60 * 1000) {
  const hit = CACHE.get(key);
  if (hit && hit.expiresAt > Date.now()) return hit.value;

  const schema = await introspectSchema();
  CACHE.set(key, { value: schema, expiresAt: Date.now() + ttlMs });
  return schema;
}

2. Schema Shortlisting

Instead of sending the entire schema, we shortlist only relevant tables:

function shortlistSchema(schemaSummary: any, question: string, maxTables = 8) {
  const q = question.toLowerCase();
  const tokens = q.split(/[^a-z0-9_]+/).filter(w => w.length >= 3);

  // Score tables by relevance
  const scored = schemaSummary.tables.map((t: any) => {
    let score = 0;
    for (const tok of tokens) {
      if (t.name.toLowerCase().includes(tok)) score += 5;
      for (const col of t.columns) {
        if (col.name.toLowerCase().includes(tok)) score += 1;
      }
    }
    return { t, score };
  });

  // Return top N tables
  return scored
    .sort((a, b) => b.score - a.score)
    .slice(0, maxTables)
    .map(x => x.t);
}

3. Compact Schema Format

We convert schema to a compact text format instead of JSON:

function schemaToText(shortlisted: any) {
  return shortlisted.tables.map((t: any) => {
    const cols = t.columns
      .slice(0, 50)
      .map((c: any) => `${c.name}:${c.type}`)
      .join(", ");
    return `${t.name}(${cols})`;
  }).join("\n");
}

Before (JSON): ~5000 tokens

After (text): ~500 tokens

Savings: 90% reduction

4. Bounded History

Only the last 6 conversation turns are included in the prompt:

const historyForPrompt = (history || []).slice(-6);

5. Connection Pooling

We use PostgreSQL connection pooling with read-only transactions:

const client = new Client({ connectionString });
await client.connect();

try {
  const result = await client.query(sql);
  return result;
} finally {
  await client.end();
}

Technology Stack

Frontend: Next.js 16 (App Router), React 19, TypeScript
Backend: Next.js API Routes, Node.js
Database: PostgreSQL (pg library)
LLM: Groq SDK (Llama 3.1 8B Instant)
Visualization: Recharts
Validation: Zod schemas

Code Example: The Full Flow

Here's how a request flows through the system:

// 1. API Route receives request
export async function POST(req: Request) {
  const body = BodySchema.parse(await req.json());
  const out = await chatService(body);
  return NextResponse.json(out);
}

// 2. Service orchestrates the flow
export async function chatService(body: ChatBody) {
  // Get cached schema
  const schemaSummary = await getSchemaSummaryCached();

  // Shortlist relevant tables
  const shortlisted = shortlistSchema(schemaSummary, body.message, 8);
  const schemaText = schemaToText(shortlisted);

  // Build prompts
  const sys = systemPrompt();
  const prompt = userPrompt({ 
    schemaText, 
    resolved: { time_range: "all_time" },
    history: body.history.slice(-6),
    message: body.message 
  });

  // Call LLM
  let raw = await llmCall([
    { role: "system", content: sys },
    { role: "user", content: prompt }
  ]);

  // Parse and validate JSON
  let out = ModelOutSchema.parse(parseJsonRobust(raw));

  // If clarification needed, return early
  if (out.type === "clarify") {
    return { ok: true, ...out };
  }

  // Validate SQL safety
  const safeSql = assertSafeReadOnlySQL(out.sql);

  // Execute query
  const result = await runQuery(safeSql);

  // Return formatted response
  return {
    ok: true,
    type: "answer",
    sql: safeSql,
    answer: out.answer,
    insights: out.insights ?? [],
    followups: out.followups ?? [],
    visualization: out.visualization ?? { type: "table" },
    result,
  };
}

Lessons Learned

1. LLMs Are Not Deterministic

We had to build a "repair" mechanism for when the LLM returns invalid JSON:

try {
  out = ModelOutSchema.parse(parseJsonRobust(raw));
} catch {
  // Retry with repair prompt
  const repaired = await llmCall([
    { role: "system", content: sys },
    { role: "user", content: repairPrompt(raw) }
  ]);
  out = ModelOutSchema.parse(parseJsonRobust(repaired));
}

2. Token Costs Add Up Fast

Our optimizations reduced token usage by ~90%, which translates to:

Faster responses (less data to process)
Lower costs (fewer tokens = lower API bills)
Better reliability (shorter prompts = more consistent outputs)

3. Security Requires Multiple Layers

A single validation layer isn't enough. We use:

Keyword blocking
Function blocking
PII detection
LIMIT enforcement
Comment stripping
Statement validation

4. Schema Shortlisting Is Critical

Sending the entire schema (100+ tables) results in:

Higher token costs
Slower responses
More hallucination (LLM gets confused)

Shortlisting to 8 relevant tables dramatically improves quality.

Limitations & Future Work

Current Limitations

MVP-level security (not production-grade)
No authentication
No query history/saved reports
Limited to PostgreSQL
No support for complex joins across many tables

Potential Improvements

[ ] SQL parser-based validation (proper AST parsing)
[ ] Allow-listing system (configurable table/column access)
[ ] Query timeout enforcement
[ ] Rate limiting per user
[ ] Audit logging
[ ] Multi-database support
[ ] Query explanation (why this SQL was generated)

Conclusion

Building an AI-powered SQL interface is challenging but rewarding. The key is balancing:

Safety: Multiple validation layers
Performance: Aggressive caching and token optimization
User Experience: Fast, conversational, helpful

This project demonstrates that with the right guardrails, LLMs can safely generate and execute SQL queries. It's not production-ready for sensitive data, but it's a solid foundation for internal tools and data exploration.

Want to try it? Check out the GitHub repository

What do you think? Have you built similar systems? What security measures did you implement? Let me know in the comments below!

Rule-Based Decisions + Optional LLM Explanations: Building event-intel-engine

ABU SUFIAN — Sun, 22 Feb 2026 17:14:23 +0000

The problem we're solving

You have a stream of events—payment failures, API errors, timeouts. You might already have dashboards and counts. What's often missing is:

A clear, repeatable rule that turns those numbers into an action: allow, review, or block. Without it, "what should we do?" stays a manual or ad-hoc call.
A single place that both applies that rule and can optionally answer why in plain language. Often you either get a decision with no narrative, or you bolt on an LLM and risk using it for the decision itself—which makes behavior harder to reason about and audit.

So the problem is: deterministic decisions from event counts, plus optional human-readable explanation, without the LLM driving the outcome.

Existing solutions and our angle

Plenty of systems already do rule-based decisions and policy engines—feature flags, circuit breakers, fraud rules, and so on. We're not claiming to replace those. We wanted a small, self-contained service that:

Ingests events and keeps rolling counters (e.g. 5m / 30m).
Exposes a simple allow/review/block decision from those counters, with no external AI in that path.
Optionally adds a short explanation of that decision. That's where we brought in an LLM—for fun. We wanted to see if we could get a readable "why" and "what to do next" without ever letting the model change the decision. So the core value is the rule engine and the API; the LLM is an extra layer we tried on top.

How we keep the LLM out of the decision

We split the system into two layers:

Decision layer: Counts (e.g. failures in the last 5m and 30m) go into a fixed rule function. Same counts → same result. No LLM, no non-determinism. This is the part that actually "solves" the problem.
Explanation layer: After the decision is fixed, we optionally send that decision plus the same evidence to an LLM and ask it only to explain it in plain language and suggest next steps. If the LLM is down, slow, or returns invalid data, we fall back to a simple rule-based explanation. The decision never changes because of the LLM.

So: rules solve the problem; the LLM is there for fun and readability.

Architecture in a nutshell

Events → stored in SQLite (subject, event_type, fingerprint, status_code, latency_ms, meta_json, at).
Counters → for each subject we query the last 5m and 30m and aggregate by event_type (failure/success/latency).
Decision → pure function of (failures_5m, failures_30m, successes_5m) → allow | review | block.
Explain → we build an evidence payload (counters + recent events), hash it, and check the cache. On miss we call the LLM with (decision + evidence), validate the response with Zod, then cache. On LLM failure we return a rule-based fallback.

Stack and layout

Node 20+, Fastify (helmet, rate-limit, API-key auth), SQLite (better-sqlite3, WAL). Tables: events, subject_state, idempotency_keys, subject_explanations. Zod for request/response validation. The LLM is any OpenAI-compatible API (e.g. Groq); the app runs fine without it—explain just uses the fallback then.

Routes: GET /v1/events (list), POST /v1/events (ingest), GET /v1/subjects/:subject/decision (public), GET /v1/subjects/:subject/explain (cached, LLM or fallback), plus profile and ask. Auth via Bearer or x-api-key; health and decision are public by default.

How decisions are computed

Counters come from queries like:

SELECT event_type, COUNT(*) as c FROM events WHERE subject = @subject AND at >= @since GROUP BY event_type

We sum failure/success (and optionally latency) per window. The rule is something like:
failures_5m >= 5 or failures_30m >= 5 → block; failures_30m >= 3 → review; else → allow.

Thresholds live in code so you can tune or replace them.

How explain works (and why the API never depends on the LLM)

We build evidence (counters + last N events + a small breakdown), hash it with the decision, and look up the cache. On hit we return the stored explanation and set X-Explain-Cache: HIT. On miss we call the LLM with (decision + evidence), validate the JSON with Zod, then cache. If the LLM is unconfigured, times out, or fails validation, we return a rule-based explanation instead. So the API always returns 200 with some explanation; the LLM is best-effort and "for fun."

How to run and use it

Setup:

git clone https://github.com/Sufian-Abu/event-intel-engine cd event-intel-engine npm install cp .env.example .env

Set at least API_KEYS=your-key. Optionally: HOST, PORT, SQLITE_PATH, RATE_LIMIT_MAX. For the LLM explain path: LLM_PROVIDER, LLM_API_KEY, LLM_BASE_URL, LLM_MODEL. Without LLM config, explain uses the fallback.

npm run dev

Or with Docker: docker compose up --build.

Ingest an event:

curl -X POST http://localhost:8080/v1/events \ -H "Authorization: Bearer YOUR_KEY" \ -H "Content-Type: application/json" \ -d '{"subject":"user-42","event_type":"failure","fingerprint":"charge.failed","status_code":402}'

Get decision (no auth):
curl http://localhost:8080/v1/subjects/user-42/decision

Get explanation (LLM or fallback):
curl "http://localhost:8080/v1/subjects/user-42/explain?window=50" \ -H "Authorization: Bearer YOUR_KEY"

In short

We wanted a small service that does rule-based allow/review/block from event counts and exposes it over a simple API. Existing solutions do that; our twist was adding an optional LLM explainer for fun—so we get a readable "why" without ever letting the model decide.

GitHub: github.com/Sufian-Abu/event-intel-engine
The README has full setup, env vars, and API details.