Conversational AI Assistant for Complex SQL Database Interaction

Team 11 — KGiSL Institute of Technology, Dept. of AI & DS Community Outreach Programme, 2025–2026 Even Semester.

A three-tier NL2SQL platform. A floor supervisor types a question in plain English — "show me all machines overdue for service in Unit B" — and gets back a table, a chart and a plain-English summary. If the question would modify data, nothing runs until the user confirms the generated SQL against a preview of exactly which rows change.

Tier	Stack	Job
Web (apps/web)	React 18 + Vite + Tailwind + TS	Chat UI, tables, charts, confirmation card, voice input
Gateway (apps/api)	Node 20 + Express + TS	Auth, RBAC, SQL guardrails, query execution, audit log
AI engine (services/ai)	Python 3.11 + FastAPI	Schema retrieval (ChromaDB), LLM → SQL, summaries, chart specs
Data (db/)	PostgreSQL 16	Industrial demo schema (machines, sensors, inventory, maintenance)

Repo layout

apps/
  web/            React 18 + Vite + Tailwind + TypeScript
  api/            Node 20 + Express + TypeScript (gateway, auth, guardrails)
services/
  ai/             Python 3.11 + FastAPI (NL2SQL, embeddings, summaries)
db/
  migrations/     Numbered .sql files, forward-only
  seed/           Seed data (deterministic)
docs/             The numbered *.md specs at the repo root are the authoritative
                  specification and source of truth for scope and phase order.

Prerequisites

• Node.js 20 LTS+
• Python 3.11+
• Docker Desktop (Docker + Compose) — runs PostgreSQL and ChromaDB
• One LLM API key: Groq (recommended), Google AI Studio, or Ollama (offline)

Getting started

cp .env.example .env          # then fill in secrets and an LLM key
docker compose up -d          # starts postgres (5433) + chromadb (8001)

npm install                   # installs the apps/web and apps/api workspaces
npm run db:migrate            # applies db/migrations (Phase 1)
npm run db:seed               # loads deterministic seed data (Phase 1)

Run the three services (three terminals, or docker compose up once Phase 10 packages the full stack):

npm run dev:api               # gateway   → http://localhost:4000
npm run dev:ai                # AI engine → http://localhost:8000
npm run dev:web               # web app   → http://localhost:5173

Open http://localhost:5173 and log in.

Demo credentials (demo-only, do not reuse)

Email	Role
viewer@kite.local	viewer — read-only, limited tables
analyst@kite.local	analyst — read-only, all business tables
supervisor@kite.local	supervisor — can write with confirmation
admin@kite.local	admin — everything incl. audit log

Password for all four: Demo@123

Scripts

Command	What it does
npm run dev	Prints the sub-commands below
npm run dev:api / dev:web / dev:ai	Run one service
npm run db:migrate / db:seed	Apply migrations / load seed data
npm test	Run all workspace tests (vitest / pytest)
npm run lint	Lint all workspaces
npm run format	Prettier format

Environment variables

Everything lives in .env (template: .env.example), grouped by service. The gateway refuses to start if JWT_SECRET or AI_SERVICE_TOKEN are missing, too short, or still set to the placeholder values.

Docs / specs

The numbered *.md files at the repo root are the spec pack: project brief (00), architecture (01), data model (02), API spec (03), NL2SQL engine (04), security and guardrails (05), frontend spec (06), build plan (07), prompts (08), testing and demo (09), setup and run (10). AGENTS.md contains the rules every AI coding agent in this repo must follow.

Status

Phase 0 (scaffold) is complete: monorepo shape, workspace tooling, Docker infrastructure and environment template. No application source code yet.