Skip to content

README.md

Latest commit

 

History

History
148 lines (107 loc) · 4.22 KB

README.md

File metadata and controls

148 lines (107 loc) · 4.22 KB

AI SQL Analyst

Production-style text-to-SQL analytics app for asking business questions over structured SaaS data.

AI SQL Analyst console

Why This Project Exists

AI SQL Analyst is built to demonstrate practical AI engineering plus real software engineering: FastAPI service design, SQL guardrails, Postgres-backed data access, auth, workspace scoping, Docker, Kubernetes, Terraform, CI, observability, and automated text-to-SQL evals.

It answers questions like:

  • Show the top customers by revenue
  • Show monthly revenue trend
  • How many support tickets were opened by priority?
  • Break down revenue by customer segment

Technical Highlights

  • AI application layer: Natural-language questions are converted into SQL using an LLM when configured, with deterministic fallback for repeatable demos and tests.
  • SQL safety: Generated SQL is validated as read-only, blocked from unknown tables, limited by row count, and scoped by workspace_id.
  • Backend: FastAPI, Pydantic models, service-layer separation, health checks, API docs, and static app serving.
  • Data layer: SQLite fallback for quick local work and PostgreSQL support for production-style deployment.
  • Product surface: Analyst console with query input, generated SQL, result table, chart preview, query history, metrics, and eval runner.
  • Auth and tenancy: Protected endpoints use X-API-Key; requests include workspace_id and guardrails inject workspace predicates.
  • Operations: Dockerfile, Docker Compose, Kubernetes manifests, Terraform example, liveness/readiness probes, HPA, and CI.
  • Quality: Unit/API tests, SQL guardrail tests, text-to-SQL eval suite, and GitHub Actions with Postgres integration.

Architecture

flowchart LR
    UI[Analyst Console] --> API[FastAPI API]
    API --> Auth[API Key Auth]
    API --> Generator[LLM or Fallback SQL Generator]
    Generator --> Guardrails[Read-only SQL Guardrails]
    Guardrails --> Scope[Workspace Scope Injection]
    Scope --> DB[(SQLite or PostgreSQL)]
    API --> Logs[Query Log and Metrics]
    API --> Evals[Text-to-SQL Eval Suite]
Loading

See Architecture for the deeper walkthrough.

Quick Start

SQLite

pip install -r requirements.txt
python manage.py init-db
uvicorn ai_sql_analyst.main:app --reload

Open:

http://127.0.0.1:8000

PostgreSQL With Docker Compose

docker compose up --build

The app runs at:

http://127.0.0.1:8000

Configuration

AI_SQL_ANALYST_DATABASE_BACKEND=sqlite
AI_SQL_ANALYST_POSTGRES_DSN=postgresql://ai_sql:ai_sql_password@localhost:5432/ai_sql_analyst
AI_SQL_ANALYST_API_KEYS=dev-api-key
AI_SQL_ANALYST_BROWSER_API_KEY=dev-api-key
OPENAI_API_KEY=your-key-here

If no OpenAI key is set, the app uses deterministic fallback SQL for common analytics questions.

API

Protected endpoints require:

X-API-Key: dev-api-key

Example:

curl -X POST http://127.0.0.1:8000/ask \
  -H "Content-Type: application/json" \
  -H "X-API-Key: dev-api-key" \
  -d "{\"question\":\"Show the top customers by revenue\", \"workspace_id\":\"demo\"}"

Core endpoints:

  • GET /health
  • GET /schema
  • POST /ask
  • GET /history
  • GET /metrics
  • POST /evals/run

Verification

pytest tests -q
python manage.py evals
kubectl kustomize k8s/base
docker compose config

Current local verification:

tests: 11 passed
eval suite: 6/6 passed
kubectl kustomize: ok
docker compose config: ok

Deployment

See Deployment for commands and environment notes.

Project Docs

Tech Stack

Python, FastAPI, Pydantic, PostgreSQL, SQLite, Docker, Docker Compose, Kubernetes, Kustomize, Terraform, GitHub Actions, HTML, CSS, JavaScript, OpenAI-compatible LLM integration.