# wisenheim/nl2sql architecture

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

- Interactive diagram: https://gitdiagram.com/wisenheim/nl2sql
- Repository: https://github.com/wisenheim/nl2sql

## Overview

##### Project type and purpose
- This is a full-stack web application named “NL2SQL” whose goal is to translate natural-language queries into executable SQL, run them against a demo database, and show results.
- Key indications: a Next.js 13 “app” folder (layout.tsx, page.tsx), a REST API route (app/api/sql/route.ts), a SQL schema file (create_tables.sql), and a docker-compose.yml spinning up Postgres.

##### File structure
- Top-level:
- README.md & tracking.md: project overview and notes
- create_tables.sql: DDL for sample tables
- docker-compose.yml: orchestration for database (and possibly the Next.js app)
- my_app/: the main Next.js 13 application
- Inside my_app:
- app/ folder:
– layout.tsx, globals.css, page.tsx: root UI shell
– api/sql/route.ts: serverless endpoint handling NL→SQL translation & execution
- components/:
– SQLPlayground.tsx: the interactive UI for entering natural-language queries
– ui/: primitive UI building blocks (Button, Card, Input)
- lib/:
– sqlSchema.ts: representation of the database schema passed to the translator
– utils.ts: helper functions (e.g., calling an LLM API, sanitizing input)
- next.config.ts, tsconfig.json, postcss.config.mjs, eslint.config.mjs: build & lint configuration

##### README for additional insights
- README simply states “Natural language to SQL.”
- We infer that the API route invokes an external LLM (e.g., OpenAI) to generate SQL based on the schema defined in sqlSchema.ts, then executes the SQL against the Postgres instance defined in docker-compose.yml.

a. Main components:
- Frontend (Browser & Next.js app)
- Backend API (Next.js serverless route)
- External AI Service (e.g., OpenAI GPT)
- Database (PostgreSQL container)
- Dev/Deployment (Docker Compose orchestration)
b. Relationships & interactions:
- User’s browser sends HTTP requests to Next.js front end
- Front end calls the /api/sql endpoint with NL query
- API route uses lib/utils to:
– Load schema from lib/sqlSchema.ts
– Call the external AI service to translate NL → SQL
– Connect to Postgres and run the generated SQL
- API returns query results or error back to front end
- Front end renders results in SQLPlayground component
c. Architectural patterns & principles:
- Layered separation: presentation (React components), application logic (API route + utils), infrastructure (Postgres, Docker)
- Single-repository modular structure using Next.js “app” conventions
- Serverless endpoint pattern for backend logic
d. Key technologies/frameworks:
- Next.js 13 (app directory, serverless API routes)
- React & TypeScript for UI components
- Docker & docker-compose for local Postgres
- External LLM API (e.g., OpenAI)
- ESLint, PostCSS for code quality & styling

## Diagram (Mermaid)

```mermaid
flowchart TD
    subgraph "NL2SQL Full-Stack Application"
        direction TB

        subgraph "Next.js Application"
            direction TB
            NApp["App Shell (layout.tsx, page.tsx, globals.css)"]:::frontend
            click NApp "https://github.com/wisenheim/nl2sql/tree/main/my_app/app"

            subgraph "UI Components"
                direction TB
                SQLPlayground["SQLPlayground Component"]:::frontend
                click SQLPlayground "https://github.com/wisenheim/nl2sql/blob/main/my_app/components/SQLPlayground.tsx"
                Button["Button"]:::frontend
                click Button "https://github.com/wisenheim/nl2sql/blob/main/my_app/components/ui/button.tsx"
                Card["Card"]:::frontend
                click Card "https://github.com/wisenheim/nl2sql/blob/main/my_app/components/ui/card.tsx"
                Input["Input"]:::frontend
                click Input "https://github.com/wisenheim/nl2sql/blob/main/my_app/components/ui/input.tsx"
            end

            subgraph "Backend API"
                direction TB
                APIRoute["API Route (/api/sql)"]:::backend
                click APIRoute "https://github.com/wisenheim/nl2sql/blob/main/my_app/app/api/sql/route.ts"
                SchemaLoader["Schema Loader (sqlSchema.ts)"]:::backend
                click SchemaLoader "https://github.com/wisenheim/nl2sql/blob/main/my_app/lib/sqlSchema.ts"
                Utils["LLM Client & Utils (utils.ts)"]:::backend
                click Utils "https://github.com/wisenheim/nl2sql/blob/main/my_app/lib/utils.ts"
            end

            NApp --> SQLPlayground
            NApp --> APIRoute
            APIRoute --> SchemaLoader
            APIRoute --> Utils
        end

        Browser["Web Browser"]:::external
        Browser -->|HTTP POST: NL Query| NApp

        Utils -->|gRPC/HTTPS: NL→SQL| LLM["LLM Service (e.g., OpenAI)"]:::external

        APIRoute -->|Execute SQL| DB["PostgreSQL DB"]:::infra
        DB -->|Query Results| APIRoute

        subgraph "Docker Compose"
            direction TB
            DC["Compose: docker-compose.yml"]:::infra
            click DC "https://github.com/wisenheim/nl2sql/blob/main/docker-compose.yml"
            DC --> DB
        end
    end

    subgraph "Project Documentation and Config"
        direction TB
        Docs["README.md & tracking.md"]:::infra
        click Docs "https://github.com/wisenheim/nl2sql/blob/main/README.md"
        click Docs "https://github.com/wisenheim/nl2sql/blob/main/tracking.md"
        ConfigNext["Next.js & Tooling Config"]:::infra
        click ConfigNext "https://github.com/wisenheim/nl2sql/blob/main/my_app/next.config.ts"
        click ConfigNext "https://github.com/wisenheim/nl2sql/blob/main/my_app/tsconfig.json"
        click ConfigNext "https://github.com/wisenheim/nl2sql/blob/main/my_app/postcss.config.mjs"
        click ConfigNext "https://github.com/wisenheim/nl2sql/blob/main/my_app/eslint.config.mjs"
    end

    classDef frontend fill:#9f6,stroke:#333,stroke-width:1px
    classDef backend fill:#6cf,stroke:#333,stroke-width:1px
    classDef external fill:#ccc,stroke:#333,stroke-width:1px
    classDef infra fill:#f96,stroke:#333,stroke-width:1px
```
