# naveen-arul/sqltalk architecture

> Architecture diagram of the GitHub repository naveen-arul/sqltalk, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-02-22). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/naveen-arul/sqltalk
- Repository: https://github.com/naveen-arul/sqltalk

## Overview

**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

## Diagram (Mermaid)

```mermaid
flowchart TD
  %% =========================================================
  %% Deployment / Context (Vertical)
  %% =========================================================
  U["User (Browser)"]:::external

  subgraph "Vercel (Frontend Deployment)"
    direction TB
    FIndex["Static assets (index.html)"]:::frontend
    FMain["Vite Bootstrap (main.tsx)"]:::frontend
    FApp["App Shell (App.tsx)"]:::frontend

    subgraph "Pages"
      direction TB
      HomePage["HomePage"]:::frontend
      AnalyzePage["AnalyzePage"]:::frontend
      NotFoundPage["NotFound"]:::frontend
    end

    subgraph "Shared UI / Layout"
      direction TB
      Layout["Layout"]:::frontend
      Navbar["Navbar"]:::frontend
      NavLink["NavLink"]:::frontend
      UIWrappers["Shadcn UI wrappers (components/ui)"]:::frontend
    end

    subgraph "Analyze UI Components"
      direction TB
      QueryInput["QueryInput (NL question)"]:::frontend
      SchemaPanel["SchemaPanel"]:::frontend
      SQLDisplay["SQLDisplay"]:::frontend
      ResultsTable["ResultsTable"]:::frontend
      ResultsChart["ResultsChart (Recharts)"]:::frontend
      ExportPanel["ExportPanel"]:::frontend
    end

    subgraph "Frontend lib"
      direction TB
      ApiClient["api.ts (Axios client)"]:::frontend
      ExportUtil["export.ts"]:::frontend
      SchemaUtil["schema.ts"]:::frontend
      ViteApiEnv["Env: VITE_API_BASE_URL"]:::config
    end
  end

  subgraph "Render (Backend Deployment)"
    direction TB
    RenderEnv["Env: DATABASE_URL, AI_KEY"]:::config

    Server["server.js (HTTP bootstrap)"]:::backend
    App["src/app.js (Express app)"]:::backend

    subgraph "Routes"
      direction TB
      AnalyzeRoute["/api/analyze (analyze.routes.js)"]:::backend
      DataRoute["/api/data (data.routes.js)"]:::backend
    end

    subgraph "Controllers"
      direction TB
      AnalyzeController["analyze.controller.js"]:::backend
      DataController["data.controller.js"]:::backend
    end

    subgraph "Services"
      direction TB
      NLPService["nlp.service.js (Groq client)"]:::backend
      SQLService["sql.service.js (DB query helper)"]:::backend
    end

    subgraph "Safety & Prompting"
      direction TB
      PromptTemplates["promptTemplates.js (schema + policy)"]:::backend
      SQLValidator["sqlValidator.js (Validation Gate)"]:::security
    end

    subgraph "DB Access"
      direction TB
      DBConfig["config/db.js (pg pool)"]:::backend
    end
  end

  subgraph "External Services"
    direction TB
    Groq["Groq API (LLM)"]:::external
    Postgres[("PostgreSQL (Managed DB)")]:::datastore
  end

  %% =========================================================
  %% Primary Flow: /api/analyze (numbered)
  %% =========================================================
  U -->|"1)AskNLquestion"| QueryInput
  QueryInput -->|"2)POST/api/analyze{question}"| ApiClient
  ApiClient -->|"3)HTTPrequest"| AnalyzeRoute
  AnalyzeRoute -->|"4)delegate"| AnalyzeController

  AnalyzeController -->|"5)buildPrompt"| NLPService
  NLPService -->|"uses"| PromptTemplates
  NLPService -->|"6)prompt(schema+policy+question)"| Groq
  Groq -->|"7)returnSQLstring"| NLPService
  NLPService -->|"8)candidateSQL"| SQLValidator

  SQLValidator -->|"9)validatedSQL(SELECT-only)"| SQLService
  SQLService -->|"10)query"| DBConfig
  DBConfig -->|"11)read-onlySELECT"| Postgres
  Postgres -->|"12)rows"| SQLService
  SQLService -->|"13)resultSet"| AnalyzeController

  AnalyzeController -->|"14)JSON{sql,rows,columns,chartHint?}"| ApiClient
  ApiClient -->|"15)renderSQL"| SQLDisplay
  ApiClient -->|"16)renderTable"| ResultsTable
  ApiClient -->|"17)renderChart"| ResultsChart
  ResultsTable -->|"18)exportData"| ExportPanel
  ExportPanel -->|"uses"| ExportUtil

  SchemaPanel -->|"uses"| SchemaUtil
  SchemaUtil -.->|"schemaContext(optional)"| QueryInput

  %% =========================================================
  %% Secondary Flow: /api/data
  %% =========================================================
  ApiClient -->|"GET/api/data"| DataRoute
  DataRoute -->|"delegate"| DataController
  DataController -->|"query"| SQLService
  DataController -->|"JSON(rows/schema/preview)"| ApiClient

  %% =========================================================
  %% Wiring / Runtime hosting (context)
  %% =========================================================
  FIndex -->|"boot"| FMain
  FMain -->|"mount"| FApp
  FApp -->|"routes"| HomePage
  FApp -->|"routes"| AnalyzePage
  FApp -->|"routes"| NotFoundPage

  AnalyzePage -->|"compose"| Layout
  Layout -->|"nav"| Navbar
  Navbar -->|"links"| NavLink
  Layout -->|"uses"| UIWrappers

  AnalyzePage -->|"compose"| QueryInput
  AnalyzePage -->|"compose"| SchemaPanel
  AnalyzePage -->|"compose"| SQLDisplay
  AnalyzePage -->|"compose"| ResultsTable
  AnalyzePage -->|"compose"| ResultsChart
  AnalyzePage -->|"compose"| ExportPanel
  QueryInput -->|"calls"| ApiClient
  AnalyzePage -.->|"config"| ViteApiEnv

  Server -->|"creates"| App
  App -->|"registers"| AnalyzeRoute
  App -->|"registers"| DataRoute
  App -.->|"config"| RenderEnv

  %% =========================================================
  %% Security boundary annotations (vertical + explicit)
  %% =========================================================
  UntrustedAI["Untrusted AI output zone"]:::note
  PolicyGate["Safety boundary: validate before DB access\n-SELECT-only\n-blocklist(DROP/INSERT/UPDATE/DELETE)\n-blockUNION/comments\n-table allowlist"]:::note
  ReadOnly["DB constraint: Read-only SELECT queries only\n(No writes permitted)"]:::note

  Groq --> UntrustedAI
  UntrustedAI --> SQLValidator
  SQLValidator --> PolicyGate
  Postgres --> ReadOnly

  %% =========================================================
  %% Click Events (from component_mapping)
  %% =========================================================
  click FIndex "https://github.com/naveen-arul/sqltalk/blob/master/frontend/index.html"
  click FMain "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/main.tsx"
  click FApp "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/App.tsx"

  click HomePage "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/pages/HomePage.tsx"
  click AnalyzePage "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/pages/AnalyzePage.tsx"
  click NotFoundPage "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/pages/NotFound.tsx"

  click QueryInput "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/QueryInput.tsx"
  click SQLDisplay "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/SQLDisplay.tsx"
  click ResultsTable "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/ResultsTable.tsx"
  click ResultsChart "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/ResultsChart.tsx"
  click ExportPanel "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/ExportPanel.tsx"
  click SchemaPanel "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/SchemaPanel.tsx"

  click ApiClient "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/lib/api.ts"
  click ExportUtil "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/lib/export.ts"
  click SchemaUtil "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/lib/schema.ts"

  click Layout "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/Layout.tsx"
  click Navbar "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/Navbar.tsx"
  click NavLink "https://github.com/naveen-arul/sqltalk/blob/master/frontend/src/components/NavLink.tsx"
  click UIWrappers "https://github.com/naveen-arul/sqltalk/tree/master/frontend/src/components/ui/"

  click Server "https://github.com/naveen-arul/sqltalk/blob/master/backend/server.js"
  click App "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/app.js"
  click AnalyzeRoute "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/routes/analyze.routes.js"
  click DataRoute "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/routes/data.routes.js"
  click AnalyzeController "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/controllers/analyze.controller.js"
  click DataController "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/controllers/data.controller.js"
  click NLPService "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/services/nlp.service.js"
  click PromptTemplates "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/utils/promptTemplates.js"
  click SQLValidator "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/utils/sqlValidator.js"
  click SQLService "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/services/sql.service.js"
  click DBConfig "https://github.com/naveen-arul/sqltalk/blob/master/backend/src/config/db.js"

  click RenderEnv "https://github.com/naveen-arul/sqltalk/blob/master/render.yaml"
  click ViteApiEnv "https://github.com/naveen-arul/sqltalk/blob/master/frontend/vercel.json"

  %% =========================================================
  %% Styles (color is critical)
  %% =========================================================
  classDef frontend fill:#1e3a8a,stroke:#93c5fd,stroke-width:1.5px,color:#ffffff
  classDef backend fill:#166534,stroke:#86efac,stroke-width:1.5px,color:#ffffff
  classDef external fill:#c2410c,stroke:#fdba74,stroke-width:1.5px,color:#ffffff
  classDef datastore fill:#6d28d9,stroke:#c4b5fd,stroke-width:1.5px,color:#ffffff
  classDef security fill:#7f1d1d,stroke:#fecaca,stroke-width:4px,color:#ffffff
  classDef config fill:#0f172a,stroke:#94a3b8,stroke-width:1.5px,color:#e2e8f0
  classDef note fill:#111827,stroke:#fbbf24,stroke-width:1.5px,color:#fef3c7,stroke-dasharray: 4 3
```
