Safe Schema-Aware Natural-Language-to-SQL

Safe Schema-Aware Natural-Language-to-SQL is a production-oriented analytics application that lets users ask questions about an existing PostgreSQL database in plain English. It retrieves the relevant schema, proposes SQL with a configurable language model, validates the SQL deterministically, and executes only safe read-only queries against the source database.

This is deliberately not an unrestricted database chatbot. The model proposes; the backend decides what is valid and executable.

What It Does

• Accepts natural-language analytics questions.
• Detects materially ambiguous questions and asks for clarification.
• Introspects an existing PostgreSQL source database dynamically.
• Builds searchable schema documents from tables, columns, constraints, relationships, comments, and semantic metadata.
• Retrieves compact, relevant schema context with hybrid vector and keyword search.
• Generates structured SQL proposals through LangChain and LCEL.
• Validates read-only SQL against PostgreSQL safety policy and the live source schema.
• Supports Review Mode with SQL inspection and approval.
• Supports Auto Mode without bypassing validation or resource limits.
• Revalidates edited SQL immediately before execution.
• Returns normalized results, grounded explanations, warnings, and suitable visualizations.

Architecture

React frontend
     |
     v
FastAPI backend
  |       |        |
  |       |        +--> Ollama or OpenRouter models
  |       |
  |       +-----------> Local PostgreSQL + pgvector index
  |
  +-------------------> External PostgreSQL source database
                         (introspection and read-only execution)

The two databases have separate responsibilities:

Database	Purpose
External source PostgreSQL	Existing business data, schema introspection, and read-only query execution
Local index-db PostgreSQL + pgvector	Schema documents, metadata, embeddings, and retrieval indexes

The local index database never receives generated business SQL. The source database is not created, migrated, seeded, or modified by this repository.

Application Flow Diagram

The diagram below shows the complete path from a natural-language question to a validated read-only query and grounded result. It also shows the separate schema-indexing pipeline that prepares technical and semantic schema context before users submit questions.

The example business names visible in the diagram are illustrative only. The application discovers the actual source schema at runtime and does not require tables with those names.

Safety Model

The application uses defense in depth:

1. A deterministic workflow owns state transitions, approval gates, retries, limits, connections, and execution policy.
2. The LLM is limited to bounded application capabilities: schema retrieval, SQL generation, and read-only execution.
3. Backend validation rejects writes, DDL, multiple statements, unsafe references, unauthorized schemas, and resource-limit violations.
4. The exact SQL supplied by the frontend is revalidated before every execution.
5. PostgreSQL permissions provide the final read-only boundary.

Database credentials, connection URLs, and model API keys stay on the backend. They are not sent to React or included in model prompts.

Quick Start With Docker

Prerequisites

• Docker Desktop with Docker Compose.
• An existing PostgreSQL database reachable from the backend.
• A PostgreSQL role with read-only access to the approved source schema.
• Node.js if developing the frontend outside Docker.
• uv and Python 3.12 if developing the backend outside Docker.
• Ollama, if using local models.

Configure the environment

Copy the example configuration:

Copy-Item .env.example .env

Set at least these backend-only values in .env:

SOURCE_DATABASE_URL=postgresql+psycopg://<user>:<password>@<host>:<port>/<database>
INDEX_DB_PASSWORD=<local-index-password>
INDEX_DATABASE_URL=postgresql+psycopg://index_user:<local-index-password>@index-db:5432/schema_index
SOURCE_SCHEMA_SCOPE=public

For a local Ollama setup, use:

MODEL_PROVIDER=ollama
OLLAMA_BASE_URL=http://host.docker.internal:11434
EMBEDDING_PROVIDER=ollama
QUESTION_MODEL=qwen3.5:4b
SQL_MODEL=qwen3.5:4b
SQL_CORRECTION_MODEL=qwen3.5:4b
ANSWER_MODEL=qwen3.5:4b
EMBEDDING_MODEL=mxbai-embed-large

Pull the models before indexing or querying:

ollama pull qwen3.5:4b
ollama pull mxbai-embed-large

To use OpenRouter instead, set MODEL_PROVIDER=openrouter, provide OPENROUTER_API_KEY, and configure the model IDs. Embeddings are configured independently with EMBEDDING_PROVIDER and EMBEDDING_MODEL.

Start the application

docker compose up --build

Open:

• Frontend: http://localhost:5173
• Backend health: http://localhost:8000/api/health
• Runtime database settings: http://localhost:5173/settings

The settings page can discover, test, and save a source PostgreSQL connection for the running backend. This is intended for a single-user local environment. The connection is backend-only and is cleared when the backend restarts.

Build the schema index

After configuring the source database and models, run:

docker compose run --rm backend index-schema

Indexing reads technical metadata from the external source, merges semantic metadata from schema_index/metadata, generates embeddings, and writes only to the local index-db. The operation is repeatable and never changes the source database.

Refresh the index after changing the source schema, semantic metadata, or embedding model:

docker compose run --rm backend index-schema

Changing the embedding provider or model requires a full index refresh because vector dimensions and representations may change.

Source Database Requirements

The source database must be provisioned externally. Use a dedicated role with only the privileges needed by the approved schema:

CREATE ROLE nl2sql_reader LOGIN PASSWORD '<strong-password>';
GRANT CONNECT ON DATABASE <source-database> TO nl2sql_reader;
GRANT USAGE ON SCHEMA <approved-schema> TO nl2sql_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA <approved-schema> TO nl2sql_reader;
REVOKE CREATE ON DATABASE <source-database> FROM nl2sql_reader;
REVOKE CREATE ON SCHEMA <approved-schema> FROM nl2sql_reader;

Adapt these statements to the deployment's ownership and default-privilege policy. The application does not create roles, alter privileges, or issue write probes. It verifies source connectivity and read-only access through PostgreSQL catalog metadata.

There is no fixed business schema in this project. Names such as customers, orders, or products are not application requirements and must not be added to production setup.

Query Lifecycle

1. The user enters a plain-English question in the React frontend and chooses Review Mode or Auto Mode.
2. React sends only the question and execution mode to the FastAPI backend.
3. The backend creates a query ID and analyzes the requested metric, entities, filters, time range, grouping, and possible ambiguity.
4. If the question is materially ambiguous, the workflow pauses and asks for clarification. No SQL is generated or executed until the user responds.
5. The backend embeds the question and combines vector retrieval, keyword retrieval, and bounded relationship expansion from the local pgvector index.
6. The model receives only the compact relevant schema context and proposes structured SQL, an interpretation, tables used, assumptions, and warnings.
7. Deterministic backend validation checks that the exact proposal is one safe, read-only statement using approved source schemas, tables, and columns.
8. If validation finds a correctable issue, the workflow can regenerate the proposal at most two times.
9. In Review Mode, the SQL Inspector displays the proposal and waits for an explicit approval. The user may edit or regenerate it. Auto Mode can continue without a click only after the same validation and limit checks pass.
10. Any edited SQL is treated as untrusted input and fully revalidated by the backend. Approval is tied to the exact validated SQL version.
11. The backend executes only the validated SQL against the external PostgreSQL source using a read-only role. It never executes generated business SQL on the local index database.
12. Returned rows are normalized, displayed in a table, and used to generate a grounded answer and an optional visualization based on the result shape.

What Happens Before the First Query

The schema index must be initialized once before normal querying:

1. The backend connects to the externally provisioned PostgreSQL source.
2. Schema introspection reads approved schemas, tables, columns, constraints, relationships, comments, and other technical metadata.
3. Optional application-controlled semantic metadata is loaded from schema_index/metadata/.
4. The backend builds rich schema documents and generates embeddings.
5. Documents, metadata, and vectors are written to the local index-db PostgreSQL + pgvector service.

Only schema metadata is indexed. Business rows are not copied into the local index, and indexing never modifies the source database.

Using the Application

After the stack is running and the index is ready:

1. Open http://localhost:5173.
2. Open Settings at http://localhost:5173/settings if a runtime source database has not already been configured.
3. Enter the external PostgreSQL connection details in Settings, discover the available non-system schemas, select the approved schema scope, test the connection, and save it. Credentials remain in backend memory and are not returned to the browser.
4. Run the schema indexing command after saving the source connection, or use the schema-index control in Settings.
5. Return to the query workspace and ask a question such as Show the monthly revenue trend for the previous year using terminology that exists in the connected database.
6. In Review Mode, inspect the generated SQL, interpretation, tables, warnings, and validation status. Approve it, edit it, or regenerate it.
7. Review the returned answer, result table, and visualization when the result shape supports one.
8. Use Auto Mode only when automatic execution is appropriate. It still runs through every backend validation, source-scope, timeout, and result-limit check.

If the source schema or semantic metadata changes, refresh the index before relying on newly added tables, columns, relationships, or definitions:

docker compose run --rm backend index-schema

The health endpoint reports whether the source and index services are reachable and whether the index is ready or stale.

Semantic Metadata

Optional business definitions live in:

schema_index/metadata/metadata.yaml

Technical metadata from PostgreSQL remains authoritative. Semantic metadata can describe schemas, tables, columns, relationships, and concepts without creating fictional source objects. See schema_index/metadata/README.md.

Development

Backend

cd backend
uv sync --extra dev
uv run uvicorn app.main:app --reload
uv run ruff format --check app tests
uv run ruff check app tests
uv run mypy app tests
uv run pytest
uv run pytest tests/integration -m integration

Frontend

cd frontend
npm install
npm run dev
npm run format:check
npm run lint
npm run typecheck
npm test
npm run build

The frontend uses VITE_API_BASE_URL to locate FastAPI. This is a public API origin only and must never contain credentials.

Service lifecycle

docker compose ps
docker compose down

Use docker compose down -v only when intentionally deleting the local index_db_data volume.

API Overview

The main endpoints are:

Method	Endpoint	Purpose
GET	/api/health	Report service, database, model, and index health
POST	/api/query	Start a query workflow
POST	/api/query/{query_id}/clarify	Apply a clarification response
POST	/api/query/{query_id}/edit	Validate edited SQL
POST	/api/query/{query_id}/approve	Approve the current SQL version
POST	/api/query/{query_id}/execute	Execute an approved query
POST	/api/query/{query_id}/regenerate	Generate another proposal
GET	/api/query/{query_id}	Retrieve workflow state
POST	/api/settings/database/discover	Discover source database options
POST	/api/settings/database/test	Test a source connection
POST	/api/settings/database	Save runtime source settings
POST	/api/settings/database/index	Refresh the schema index

Detailed contracts are documented in docs/api/backend-api.md.

Repository Layout

backend/       FastAPI transport, workflow, retrieval, validation, and services
frontend/      React application and presentation components
schema_index/  Semantic metadata, schema documents, embeddings, and index tools
tests/         Shared test fixtures and representative schemas for tests only
evaluation/    Benchmark datasets, runners, metrics, and reports
docs/          Architecture, security, workflow, API, and development guides

Documentation

• Local development
• Architecture
• Security
• Schema retrieval
• Backend API
• Review and approval workflow
• Model replacement
• Evaluation
• Demo walkthrough

Current Scope and Limitations

• PostgreSQL is the only supported source database engine.
• The source database and read-only role must exist outside this repository.
• Query workflow state is held in backend process memory and is lost on restart.
• Runtime database settings are single-user and currently process-local.
• The project does not provide multi-user authorization or tenant isolation.
• LLM output can be semantically imperfect; backend validation prevents unsafe execution but cannot guarantee that every valid query expresses the user's intended business meaning.
• LangSmith tracing, query-plan explanations, metric governance, and unrestricted autonomous agents are outside the MVP scope.

License

No license has been declared for this repository yet.