How naveen-arul/sqltalk works
SQLTalk is a full-stack “NL → SQL → DB results → charts” analytics app:
• Frontend (React + TS + Vite + Tailwind/Shadcn + Recharts) collects a natural language question, renders SQL + tabular results + charts, and supports export.
• Backend (Node/Express) exposes REST endpoints, calls an LLM via Groq API to generate SQL, runs multi-layer SQL validation, executes read-only SELECT queries on PostgreSQL, and returns structured results.
• Deployed split: Frontend on Vercel, Backend on Render (see vercel.json, render.yaml).
Backend (Express service)
Key files:
• backend/server.js – likely bootstraps HTTP server and wiring
• backend/src/app.js – Express app configuration (middleware, routes)
• backend/src/routes/
• analyze.routes.js → /api/analyze
• data.routes.js → /api/data
• backend/src/controllers/
• analyze.controller.js – orchestrates NL query → SQL → results
• data.controller.js – raw data endpoint
• backend/src/services/
• nlp.service.js – Groq API interaction (prompting, response parsing)
• sql.service.js – DB execution / query helper
• backend/src/utils/
• promptTemplates.js – system/prompt templates for safe SQL generation
• sqlValidator.js – validation rules (SELECT-only, keyword blocklist, etc.)
• backend/src/config/db.js – PostgreSQL connection/pool config
This is a clean layered architecture: Routes → Controllers → Services → (Utils + DB config).
Frontend (React SPA)
Key areas:
• frontend/src/pages/AnalyzePage.tsx, HomePage.tsx – page-level composition
• frontend/src/components/
• QueryInput.tsx – captures NL query
• SQLDisplay.tsx – shows generated SQL
• ResultsTable.tsx, ResultsChart.tsx – render results
• ExportPanel.tsx – CSV/Excel/PDF export (ties to src/lib/export.ts)
• SchemaPanel.tsx – schema display (ties to src/lib/schema.ts)
• frontend/src/lib/api.ts – Axios client + base URL configuration
• frontend/src/lib/export.ts, schema.ts – domain utilities
• frontend/src/components/ui/* – Shadcn UI component library wrappers
• frontend/vite.config.ts, tailwind.config.ts, vercel.json – build/deploy configuration
Front-end is a typical SPA: Pages → Components → lib(api/utilities).
Infra / Deployment
• render.yaml – backend service definition on Render
• frontend/vercel.json – frontend hosting config on Vercel
• .env implied in README for both sides (API base URL, DB URL, Groq key)
NLP service nlp.service.js
• uses promptTemplates.js
• calls Groq API (external)
• returns candidate SQL (and maybe metadata)
Validation sqlValidator.js
• enforces SELECT-only, blocks keywords/comments/UNION, table allowlist, etc.
Frontend renders
• SQLDisplay (SQL)
• ResultsTable
• ResultsChart (Recharts)
• ExportPanel (export files)
Cross-cutting concerns to show as “sidecars” or “layers”
• Environment variables:
• Backend: DATABASE_URL, AI_KEY
• Frontend: VITE_API_BASE_URL
• Security boundary: validation between AI output and DB execution
Labels
Use labels that match the repo and endpoints:
• /api/analyze, /api/data, /health
• nlp.service.js, sqlValidator.js, sql.service.js, db.js
• “Groq API”, “PostgreSQL”
• Frontend → Backend: { question: string }
• Backend → Groq: prompt (schema + policy + user question)
• Groq → Backend: SQL string
• Backend → DB: validated SQL (SELECT)
• Backend → Frontend: { sql, rows, columns, chartHint? } (whatever your actual response is)
“Separate into as many components as possible” (without overthinking)
Given this repo, the best granularity is:
• Frontend: Page-level + the 5–6 domain components (QueryInput, SQLDisplay, ResultsTable, ResultsChart, ExportPanel, SchemaPanel) + API client
• Backend: app.js + routes + controllers + services + validator + prompt templates + db pool
• External: Groq + Postgres
• Infra: Vercel + Render + env vars
• AnalyzePage → QueryInput → api.ts
• SQLDisplay / ResultsTable / ResultsChart / ExportPanel / SchemaPanel
• server.js → app.js
• .routes → analyze.controller
• nlp.service (uses promptTemplates) → Groq API
• sqlValidator (gate)
• sql.service → db.js → PostgreSQL
• data.routes → data.controller → sql.service
• Groq API
• PostgreSQL