InsightAI: Ask Your Database Questions in Plain English

If you run a product with a real operational database — enrollments, classrooms, orders, users, inventory — you have probably watched analysts and engineers copy-paste SQL back and forth while everyone else waits. InsightAI is an open-source platform I built to close that gap: ask a question in natural language, get safe read-only SQL, real query results, and a plain-English summary grounded in those rows — not invented numbers.

This post covers what InsightAI is, who it is for, how the pipeline works, and how to run it yourself.

Repository: github.com/behzad-njf/InsightAI
License: MIT (attribution required — see NOTICE)

The problem

Operational databases hold the truth about your business, but accessing that truth still requires SQL fluency, schema knowledge, and patience. Generic chatbots hallucinate counts. BI dashboards answer only the questions someone already designed. What teams often want is simpler:

"How many students are in each classroom?"
"Who is enrolled in Sunrise Room?"
"What does our handbook say about campus closure policy?"

InsightAI targets exactly that middle ground: conversational analytics over your live schema, with guardrails that keep the database read-only and the answers tied to actual query output.

What InsightAI does

At a high level, every question flows through a single orchestrated pipeline:

Natural language question
    → Route: SQL | RAG (documents) | both
    → Relevant schema context and/or Knowledge base search
    → LLM generates read-only SQL (analytics path)
    → Multi-layer SQL safety validation
    → Execute SELECT on a readonly connection
    → LLM summarizes results (no invented numbers)
    → JSON answer via REST API (sync, SSE stream, or debug endpoints)

Typical end-to-end latency is on the order of 2–30 seconds, depending on schema size, query complexity, and which LLM provider you use.

Hybrid intelligence: SQL + documents

InsightAI is not only a text-to-SQL tool. When RAG is enabled, the system can:

• Answer policy, help, and product questions from your Knowledge/ folder (semantic search over .md, .txt, .pdf)
• Run SQL for counts, lists, and trends
• Combine both routes when a question needs documents and live data

That means "What is this system for?" pulls from your docs, while "How many active classrooms are there?" hits the database — and a question like "Per our handbook, how many classrooms do we operate?" can use both.

Grounded answers

The answer-generation step is deliberately constrained: cite real column names, respect row counts, handle empty results and truncated sets explicitly. The model is instructed not to invent data that was not in the query result.

Read-only by design

Security is layered, not optional:

1. Keyword blocklist for obvious write operations
2. sqlglot AST validation — only SELECT (and safe WITH … SELECT) pass
3. Composite validator — AST is authoritative; keyword checks are a fail-closed backup
4. Database credentials should use a read-only DB user in production

Even if the LLM misbehaves, dangerous SQL should not reach your database.

Who is it for?

InsightAI is built for teams that already have a structured operational database and want self-serve analytics without giving everyone write access or a SQL IDE.

The codebase is industry-agnostic. Each deployment is one customer / one business: one schema, one readonly database connection, one set of YAML configs. You customize config/, Knowledge/, and environment — not a multi-tenant monolith inside a single process.

Key features

Schema-aware SQL generation

InsightAI does not introspect your app at runtime. Instead, it consumes exported schema metadata — ideally from my companion project django-db-schema-doc:

• schema/schema.json — structured snapshot (preferred)
• schema/database_schema.md — human-readable fallback
• Optional schema_examples.json — boosts example queries in context

The system injects only relevant tables into the LLM prompt (with a configurable cap), which keeps prompts smaller and answers more accurate on large schemas.

Trusted semantic layer

For recurring business metrics — "active student count", "revenue this month" — you can author approved SQL and metrics in YAML under config/semantic/. When a user question matches, InsightAI can return trusted SQL without calling the LLM, or validate in dry_run mode. This is how you move from demo magic to production trust.

Governance and data policy

Row-level scope, table allow/deny lists, and column masks live in config/governance/. API keys can carry attributes (e.g. campus_ids=1,2) so the same deployment serves different principals with different data visibility.

Production API surface

Authentication supports none (dev), API keys, and JWT. Rate limiting, audit logs, optional OpenTelemetry tracing, and Prometheus metrics round out the ops story.

Admin UI

A Vite + React operator console ships with the project:

Human login uses email/password sessions; API keys are not stored in the browser.

Architecture

InsightAI follows hexagonal architecture (ports and adapters):

api/              → FastAPI routes, schemas, auth, rate limits
application/      → Use cases (ask, chat, generate SQL, run query, …)
domain/           → Models, ports, exceptions
infrastructure/   → LLM, DB, schema parser, prompts, safety validators
prompts/          → LLM system/user templates
schema/           → schema.json and/or database_schema.md
Knowledge/        → Business docs for RAG
config/           → Per-instance YAML (semantic, governance, LLM router)

The core orchestrator is AskUseCase: schema context → SQL → validate → execute → answer.

Tech stack: Python 3.12+, FastAPI, Pydantic v2, SQLAlchemy 2, sqlglot, LlamaIndex, LLM providers (Groq, OpenAI, OpenRouter), optional Redis, Docker Compose for local demos.

How to get started

1. Clone and configure

git clone https://github.com/mrhib/InsightAI.git
cd InsightAI
python3.12 -m venv .venv
source .venv/bin/activate

cp .env.example .env
# Set your LLM API key (GROQ_API_KEY, OPENAI_API_KEY, or OPENROUTER_API_KEY)
# Set readonly database connection settings

pip install -e ".[dev,mssql,rag]"
uvicorn insightai.main:create_app --factory --reload

Verify:

curl http://localhost:8000/api/v1/health
open http://localhost:8000/docs

2. Export schema from Django (recommended path)

If your operational data lives in Django:

# In your Django project:
pip install django-db-schema-doc
# Add "db_schema_doc" to INSTALLED_APPS

python manage.py generate_database_doc -o DATABASE.md --project-name "Your Product"
python manage.py export_schema_json -o schema.json

Copy into InsightAI:

cp DATABASE.md /path/to/InsightAI/schema/database_schema.md
cp schema.json   /path/to/InsightAI/schema/schema.json

Set in .env:

INSIGHTAI_SCHEMA_SOURCE=auto
INSIGHTAI_SCHEMA_JSON_PATH=schema/schema.json
INSIGHTAI_SCHEMA_MARKDOWN_PATH=schema/database_schema.md

Restart the API so the schema registry reloads.

3. Add business knowledge (optional)

Drop policies, help text, and reference material into Knowledge/, then sync:

insightai-knowledge-sync --force

Enable RAG in .env:

INSIGHTAI_RAG_ENABLED=true

4. Ask your first question

CLI:

python scripts/ask.py "How many active classrooms are there?"
python scripts/ask.py --stream "How many students per classroom?"

curl:

curl -s -X POST http://localhost:8000/api/v1/chat \
  -H 'Content-Type: application/json' \
  -d '{"question": "How many active classrooms are there?"}'

Admin UI:

insightai-app-db upgrade
insightai-admin create-user --email admin@example.com
cd apps/admin && npm ci && npm run build
insightai-serve
# → http://localhost:8000/admin/chat

5. Docker quick demo

cp .env.example .env   # set GROQ_API_KEY
docker compose up --build

The Compose stack includes a sample Postgres database for trying the pipeline without wiring your own DB first.

Production checklist

Before exposing InsightAI beyond localhost:

Full security guidance: SECURITY.md.

Tips for better answers

Natural language over SQL is powerful but not magic. From real end-to-end testing against multi-school operational data:

Practical advice:

• Match exact entity names as they appear in your schema
• Add disambiguation when a name could mean multiple things
• Use POST /api/v1/ask during development to inspect generated SQL
• Invest in django-db-schema-doc model docstrings and help_text — richer exports mean better context for the LLM

Example response

A sync chat response looks roughly like this (abbreviated):

{
  "question": "How many classrooms do I have?",
  "answer": "The query returned 40 rows, which means you have 40 classrooms...",
  "row_count": 40,
  "truncation_noted": false,
  "timings": {
    "sql_generation_ms": 2435.5,
    "query_execution_ms": 56.66,
    "answer_generation_ms": 871.54,
    "total_ms": 3365.41
  }
}

For streaming UIs, POST /api/v1/chat/stream emits status events (e.g. generating_sql, executing_query), token deltas for the answer text, and a final done event with the full payload.

Related projects

InsightAI pairs naturally with django-db-schema-doc — export schema once from Django, consume it in InsightAI. The two projects are independent: one generates metadata, the other answers questions against it.

Closing thoughts

InsightAI is my answer to a recurring engineering problem: how do you let non-SQL users query real operational data without risking writes, hallucinations, or ungoverned access? The approach is deliberately boring in the right places — read-only SQL, AST validation, grounded summaries, YAML-governed policies — and ambitious where it matters — hybrid RAG, trusted metrics, streaming chat, and an operator UI you can actually run in production.

If this sounds useful for your stack, clone the repo, point it at a readonly connection, export your schema, and ask your first question. Issues, feedback, and contributions are welcome on GitHub.

Links

• GitHub: github.com/behzad-njf/InsightAI
• django-db-schema-doc: github.com/behzad-njf/django-db-schema-doc
• OpenAPI docs: http://localhost:8000/docs when running locally

InsightAI by MrHiB — MIT License. If you use or build on this project, please credit InsightAI and link to the canonical repository.