An air-gapped, privacy-first natural-language interface to enterprise SQL data warehouses.
AirLock — the controlled passage between your AI assistant and your data warehouse. Only validated, read-only queries get through.
A production-shaped Retrieval-Augmented Generation (RAG) system that lets internal users ask plain-English questions of structured data — and receive auditable, schema-grounded answers — without a single byte of business data leaving owned infrastructure. No cloud LLM APIs. No managed embedding services. No third-party telemetry. Every model invocation, every retrieval, every audit row stays inside the trust boundary.
The system pairs a local large-language model with a defense-in-depth SQL execution pipeline: an AST-level guardrail rejects any non-SELECT operation, a hardened read-only database role serves as the second line of defense, and an append-only audit ledger captures every question, every generated query, and every failure for forensic review. Schema awareness is provided by a pgvector-backed retrieval layer that maintains semantic embeddings of every table in the target warehouse, so the language model is grounded in real, current schema rather than hallucinated structure.
In short: the privacy posture of an air-gapped data-room with the conversational ergonomics of a modern AI assistant.
- Why this exists
- Architecture
- Privacy & security model
- Component reference
- Request lifecycle
- Quickstart
- Configuration
- HTTP API
- Web UI
- Operational tooling
- Production roadmap
- Project layout
- License
Most "Text-to-SQL" demos in 2026 hand your schema and your prompts to a third-party LLM provider, then return the answer through the same channel. For organizations with regulatory commitments — financial, healthcare, recruitment, public sector — that's a non-starter. The schema alone is sensitive; the questions users ask are sensitive; the rows returned are unequivocally sensitive.
privacy_model solves this constraint without sacrificing capability. The full pipeline — embedding, retrieval, SQL generation, validation, execution, summarization — runs on infrastructure under your direct control. The only network traffic the system originates is the validated, read-only SELECT to your data warehouse over an authenticated TLS connection. Nothing else leaves the perimeter.
┌──────────────────────────────────────────────────────────┐
│ USER (browser) │
│ http://<host>:8001 · single-page UI │
└───────────────────────────────┬──────────────────────────┘
│
▼ POST /rag/query
┌──────────────────────────────────────────────────────────┐
│ FastAPI application │
│ │
│ ┌────────────────────────────────────────────────┐ │
│ │ RAG orchestrator (7 stages) │ │
│ └─┬──────────┬─────────────┬─────────────┬───────┘ │
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ ┌──────┐ ┌────────┐ ┌─────────┐ ┌─────────┐ │
│ │embed │ │retrieve│ │ generate│ │guardrail│ │
│ └──┬───┘ └────┬───┘ └────┬────┘ └────┬────┘ │
│ │ │ │ │ │
└─────┼───────────┼────────────┼─────────────┼─────────────┘
│ │ │ │
┌─────────┘ │ └─────────────┘
▼ ▼ │
┌──────────────────┐ ┌──────────────────────┐ │
│ Local Ollama │ │ Local Postgres │ ▼
│ (no internet) │ │ + pgvector │ ┌──────────────────────┐
│ │ │ │ │ Enterprise SQL DB │
│ • Embedding │ │ • schema_docs │ │ (read-only role) │
│ (768 dims) │ │ (vector index) │ │ │
│ • SQL gen │ │ • rag_audit │ │ Validated SELECT │
│ • Summarization │ │ (forensic log) │ │ with hard timeout │
└──────────────────┘ └──────────────────────┘ └──────────┬───────────┘
│
▼ rows
┌──────────────────────┐
│ Summarizer (Ollama) │
│ → natural language │
└──────────────────────┘
Two storage tiers, by design:
- Local Postgres + pgvector stores the retrieval index (table descriptions and their 768-dimension embeddings) and the audit log. These never leave the host.
- Enterprise SQL warehouse holds the actual business data. The system reaches it only through a read-only, statement-timeout-bounded connection, and only after the LLM-generated query has been parsed, validated, and rewritten into a safe form.
The system enforces a layered defense — no single component is trusted to be sufficient.
| Layer | Mechanism | What it stops |
|---|---|---|
| L1: Network | All model inference is local (Ollama on localhost). No cloud LLM, no managed embedding API, no telemetry. |
Schema, prompts, and answers leaving the perimeter. |
| L2: Prompt design | LLM receives only retrieved schema slices, not the whole catalog. Temperature pinned to 0 for deterministic SQL generation. | Unbounded knowledge of the data model leaking into the LLM. |
| L3: AST guardrail | Generated SQL is parsed by sqlglot into a typed AST. The pipeline rejects anything that is not a top-level Select, walks the entire tree to refuse hidden DML in CTEs, and blocks references to system schemas (sys, information_schema, pg_*, msdb). A LIMIT/TOP is injected when missing. |
Injection attacks, CTE-with-DELETE bypass, system-catalog enumeration, runaway result sets. |
| L4: Database role | The actual SQL executes as a dedicated read-only login granted SELECT on whitelisted tables only. No privilege to write, drop, alter, or inspect server metadata. | Catastrophic failure of L3 — even if every parser check were bypassed, the database would refuse the operation. |
| L5: Statement timeout | LOCK_TIMEOUT 10000 on AGRIDW; statement_timeout = '5s' on Postgres. |
Long-running or accidentally pathological queries impacting shared infrastructure. |
| L6: Audit ledger | Every question — successful, blocked, or errored — is appended to rag_audit with the exact generated SQL, row count, and error detail. |
Loss of forensic visibility. Supports post-hoc review and incident response. |
All six layers are live in the prototype today. Layers 3 and 4 in particular form the core security boundary: a guardrail bug alone cannot lead to data modification.
| Module | Responsibility |
|---|---|
app/main.py |
Application entry. Wires lifespan-managed connection pools, mounts the RAG router, and serves the static UI. |
app/config.py |
Type-safe configuration via pydantic-settings. Reads from .env; assembles the ODBC DSN for the enterprise DB. |
app/db/rag_pg.py |
Async Postgres pool (admin role) backing the retrieval index and audit log. |
app/db/agridw.py |
Async ODBC pool (read-only role) for the enterprise SQL warehouse. Sets per-statement LOCK_TIMEOUT. |
app/services/ollama_client.py |
HTTP client for the local Ollama runtime. Exposes embed() and chat(); uses an extended read timeout to accommodate CPU inference. |
app/services/sql_guard.py |
The AST-level guardrail. Parses generated SQL, validates the tree, blocks forbidden operations and schemas, injects safety limits. |
app/services/rag_service.py |
The orchestrator. Coordinates the full seven-stage pipeline and writes the audit row. |
app/routers/rag.py |
FastAPI router exposing POST /rag/query and GET /rag/schema. |
app/schemas/rag.py |
Pydantic request/response models. |
app/static/index.html |
Self-contained single-page web UI (schema browser + Q&A panel). No build step. |
scripts/seed_agridw_schema.py |
Builds and embeds curated table descriptions (with foreign-key hints) from a live schema dump. Idempotent. |
scripts/count_tokens.py |
Token-budget analysis tool. Computes per-format prompt costs against the actual schema. |
sql/01_init.sql |
First-boot Postgres bootstrap — extensions, tables, roles, grants. |
docker-compose.yml |
Single-service compose file for pgvector/pgvector:pg16. |
run.py |
Development launcher. Forces SelectorEventLoop on Windows so psycopg's async driver works under Uvicorn. |
A single POST /rag/query traverses seven discrete stages:
- Embed — the user's question is converted to a 768-dimensional vector via
nomic-embed-text. - Retrieve — pgvector returns the top-K most semantically similar
schema_docsrows (cosine distance, HNSW index). This narrows the LLM's view to the relevant subset of the warehouse. - Generate — the SQL-coder LLM (
qwen2.5-coder:3blocally;Arctic-Text2SQL-R1-32Bis the production target) receives a templated system prompt containing the retrieved schema and few-shot exemplars, plus the user's question. Temperature 0 ensures determinism. - Guardrail —
sql_guard.validate_and_limit()parses the result into an AST, refuses any non-Selectstatement, walks the tree for forbidden nodes and schemas, and injects aTOP 100(T-SQL) if no limit was specified. - Execute — the validated query runs through the read-only ODBC pool. A
LOCK_TIMEOUTof 10 s caps any pathological query. - Summarize — the rows are passed back to the LLM with a tight system prompt asking for a 1–3 sentence natural-language answer. Row serialization is capped to prevent prompt-context blowups on wide result sets.
- Audit — the question, generated SQL, row count, and any error message are appended to
rag_audit. This step runs even on failure paths.
A GET /rag/query/stream variant emits per-stage NDJSON so the frontend can render progress in real time.
- Docker (for the pgvector container)
- Python 3.13+
- Ollama installed locally with the SQL-coder and embedding models pulled
- ODBC Driver 17 (or 18) for SQL Server, if connecting to a SQL-Server-class warehouse
# 1. Clone and configure
git clone <this-repo>
cd privacy_model
cp .env.example .env # then fill in AGRIDW_* values
# 2. Start the local retrieval store
docker compose up -d # pgvector on :5433, schema + roles auto-created
# 3. Pull local models
ollama pull qwen2.5-coder:3b
ollama pull nomic-embed-text
# 4. Python environment
python -m venv venv
source venv/Scripts/activate # bash on Windows; use bin/activate on *nix
pip install -r requirements.txt
# 5. Build and seed the schema retrieval index
python scripts/seed_agridw_schema.pypython run.py # listens on :8001Open http://localhost:8001 in a browser. The single-page UI loads automatically — left pane shows the full table catalogue (searchable, with column types and row counts), right pane accepts natural-language questions.
All configuration is .env-driven via pydantic-settings. The full key list is documented in .env.example. Key settings:
| Variable | Purpose |
|---|---|
OLLAMA_BASE_URL |
URL of the local Ollama runtime. |
OLLAMA_LLM_MODEL |
The SQL-generation and summarization model. |
OLLAMA_EMBED_MODEL |
The retrieval embedding model. |
RAG_DB_URL |
Admin connection to the local Postgres (used for retrieval-index and audit writes). |
RAG_DB_READONLY_URL |
Read-only Postgres role (security boundary for the toy local-DB demo). |
AGRIDW_SERVER / _DATABASE / _UID / _PWD / _DRIVER |
ODBC parameters for the enterprise SQL warehouse. The login should be granted SELECT on whitelisted objects only. |
| Route | Method | Purpose |
|---|---|---|
/health |
GET |
Liveness check. |
/rag/query |
POST |
The main endpoint. Body: {"question": "..."}. Returns the question, generated SQL, returned rows, and natural-language answer. |
/rag/schema |
GET |
Returns the full table-and-column catalogue used by the web UI. |
/docs |
GET |
Auto-generated OpenAPI docs (Swagger UI). |
/redoc |
GET |
ReDoc rendering of the same. |
A streaming variant (/rag/query/stream, NDJSON) is also wired so the frontend can show per-stage progress.
A single-file zero-build interface ships at app/static/index.html. It is served by FastAPI itself — no Node toolchain, no bundler.
- Schema browser — every table from the warehouse rendered alphabetically with row counts and column counts. Click to expand columns. Live search filters by table or column name.
- Q&A panel — natural-language input with quick-example chips. Each answered question becomes a card showing the natural-language answer, the generated SQL with keyword highlighting, and the full result set as a scrollable table.
- Stage tracker — when a query is in flight, a per-stage progress strip shows which step the request is currently in (embed → retrieve → generate → guard → execute → summarize → audit), with timings.
- Error surface — guardrail rejections and DB errors are rendered cleanly with a red status border, distinguishing safety-blocks from outright failures.
Reads a captured .agridw_schema.json (live INFORMATION_SCHEMA snapshot) and rebuilds schema_docs from scratch. For the core recruitment-domain tables, hand-curated descriptions and foreign-key hints are merged in to maximize retrieval precision. For the long-tail of less-queried tables, descriptions are auto-generated from the schema. Run after any meaningful warehouse schema change.
Computes the prompt-token cost of feeding the full schema to the LLM under four different serializations: raw JSON, human-readable per-table docs, CREATE-TABLE DDL, and minimal name(col,col,...). Useful for verifying that a planned model swap (different context window) actually fits the workload.
Auto-applied by Postgres on first container start. Creates the vector extension, the schema_docs and rag_audit tables (with an HNSW vector index on embedding), and the rag_reader role with explicit grants. Defaults are designed to deny by default — new tables added later are not automatically visible to the read-only role.
The local prototype rates 5.5/10 against an industry-grade air-gapped enterprise RAG system, and 8/10 as a learning-and-design prototype demonstrating the pattern. The repository's CLAUDE.md contains a detailed 13-item production-readiness audit. The top-five fixes that move the system from 5.5 → 7.5 are:
- Dedicated read-only SQL login on the enterprise database (replaces any shared admin credential).
- API-key authentication on
/rag/query*, with per-key audit attribution. - Larger SQL model —
Arctic-Text2SQL-R1-32B(current open-weights BIRD SOTA) drops into the same serving stack asQwen2.5-Coder-32B. - SQL verifier step — a second LLM pass that critiques the generated SQL before execution.
- Question→SQL semantic cache keyed on question-embedding similarity, to avoid re-running identical or near-identical questions.
Beyond that, the document describes the path to a 9/10 system: active-learning loops, multi-tenant isolation, DBT-style semantic-layer integration, dedicated SQL-critic models, OpenTelemetry instrumentation, and a Kubernetes deployment topology with Ollama on dedicated GPU nodes.
airlock-rag/
├── app/
│ ├── main.py # FastAPI entry, lifespan, static mount
│ ├── config.py # pydantic-settings configuration
│ ├── db/
│ │ ├── rag_pg.py # local Postgres pool (retrieval + audit)
│ │ └── agridw.py # enterprise SQL warehouse pool (read-only)
│ ├── services/
│ │ ├── ollama_client.py # local LLM HTTP client
│ │ ├── sql_guard.py # sqlglot AST validation + LIMIT injection
│ │ └── rag_service.py # the seven-stage orchestrator
│ ├── routers/
│ │ └── rag.py # POST /rag/query, GET /rag/schema
│ ├── schemas/
│ │ └── rag.py # request/response Pydantic models
│ └── static/
│ └── index.html # zero-build single-page UI
├── scripts/
│ ├── seed_agridw_schema.py # rebuild the retrieval index from live schema
│ └── count_tokens.py # token-budget analysis
├── sql/
│ └── 01_init.sql # Postgres bootstrap (extension, tables, roles)
├── docker-compose.yml # pgvector container
├── requirements.txt
├── run.py # dev launcher (Windows-safe event loop)
├── .env.example # configuration template
├── CLAUDE.md # architecture + production-readiness audit
└── README.md
Proprietary. See LICENSE for terms. Internal evaluation only; no public redistribution.
Designed for organizations where "where did the data go?" must always have a precise, defensible answer.