Iris — Talk to Your Database

Overview

Iris is a conversational natural language to SQL system with memory, preferences, and result-based evaluation.

"Show me customer orders from last month, sorted by total"
→ SQL generated → executed → results displayed

"Actually, always sort results by date descending"
→ Preference saved → applies to all future queries

Key capabilities:

• Conversational chat with persistent memory and context
• User preferences learned from natural language
• Intent routing (SQL query vs conversation vs preference)
• Schema ranking (embeddings) for large databases (56+ tables)
• Local-first storage (SQLite for user data, no cloud dependency)
• Auto database onboarding (single command to connect any PostgreSQL)

Evaluation is based on executed results, not SQL text matching.

---

Requirements

• Python 3.10+
• PostgreSQL (Iris queries your databases — see Database Setup below)
• Either:
  • Apple Silicon Mac (for local MLX inference — fastest on Mac)
  • NVIDIA GPU + CUDA (for HF Transformers backend via Docker)

Quick Start

1. Install

git clone https://github.com/maxpar1/iris-project.git
cd iris-project
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

2. Configure

cp .env.example .env
# Edit .env — set IRIS_MODEL_BACKEND, model paths, and database passwords

3. Set up a database

Iris ships configured for four PostgreSQL sample databases. The easiest to try is Pagila (DVD rental schema):

# Install Pagila locally — https://github.com/devrimgunduz/pagila
createdb pagila
psql pagila -f pagila-schema.sql
psql pagila -f pagila-data.sql

# Create a read-only user
psql pagila -c "CREATE USER pagila_reader WITH PASSWORD 'your_password';"
psql pagila -c "GRANT CONNECT ON DATABASE pagila TO pagila_reader;"
psql pagila -c "GRANT USAGE ON SCHEMA public TO pagila_reader;"
psql pagila -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO pagila_reader;"

Set PAGILA_READER_PASSWORD=your_password and DB_HOST=localhost in .env.

4. Run

# Start the engine (loads models — ~30s first run with cache, longer without)
python -m iris_core serve

# In another terminal, check health
python -m iris_core health

# Ask a question
python -m iris_core ask "How many customers are there?" --database pagila

# Or launch the Streamlit chat UI
streamlit run scripts/gui.py

Docker (GPU)

docker compose up --build
curl http://localhost:8002/health

Uses the HF Transformers backend. Requires NVIDIA Container Toolkit. Configure IRIS_MODEL_ID in .env to point to any HF chat model (e.g. Qwen/Qwen2-7B-Instruct). Databases still need to be reachable from the container.

API

• /ask — question → SQL → execute. Fields: question, database, optional tenant_id, top_k, max_tokens. Returns sql, rows, row_count, error_code.
• /health — service status + metrics snapshot
• /metrics — JSON metrics snapshot
• /generate — low-level text generation (internal)

Observability

• Logs: JSON to stdout (set IRIS_LOG_LEVEL, default INFO).
• Stage logs emitted for rank, schema_pack, generation, execution with latency and basic stats.
• Metrics: /health returns per-stage counts, success rate, p50/p95/pmax, and average schema size.
• /metrics: JSON metrics snapshot (same data as /health.metrics).

Latency & Query Protection (Phase 4)

• Schema caching + hard cap: formatted schemas are cached by signature and trimmed to IRIS_SCHEMA_MAX_CHARS (default 15k).
• Query protection: statement timeout (IRIS_STATEMENT_TIMEOUT_SECONDS, default 60s) and max row limit (IRIS_MAX_ROW_LIMIT, default 100).

Model Stack

The engine loads 3 specialized models at startup:

Model	Purpose	Backend
SQL Generator (configurable; default mlx-community/gpt-oss-20b-MXFP4-Q8)	NL → SQL generation	MLX (local) or HF Transformers (Docker/GPU)
Embedder (configurable; default Qwen/Qwen3-Embedding-0.6B)	Schema ranking, table/column scoring	sentence-transformers (PyTorch, uses MPS on Mac)
Arch-Router (katanemo/Arch-Router-1.5B.gguf)	Intent classification (SQL/conversation/preference)	llama-cpp-python (GGUF)

All models are downloaded from Hugging Face on first run and cached locally. No API keys or cloud calls required.

Override any of these via env vars: IRIS_MODEL_ID, IRIS_EMBEDDER_MODEL, IRIS_SCHEMA_RANKER_MODEL, IRIS_ROUTER_MODEL.

---

Features

1. Conversational Chat with Memory

The chat system maintains conversation history with semantic retrieval:

from iris_core.chat import ChatSession, ChatStore

store = ChatStore()  # SQLite at ~/.iris/chat.db
session = ChatSession(store, user_id="user1", database="pagila")

response = session.process_input("Show me top 5 customers by spend")
# → Routes to SQL generation, executes, returns results

response = session.process_input("What about the bottom 5?")
# → Uses conversation context to understand "bottom 5"

Context management:

• Recent 10 messages always included
• Older messages retrieved via semantic similarity (RAG)
• Token budget management for prompt limits

2. User Preferences

Users state preferences in natural language:

User: "Always show amounts in USD"
→ Preference extracted: {category: "formatting", rule: "Show amounts in USD"}

User: "Sort results by date descending by default"
→ Preference extracted: {category: "sorting", rule: "Sort by date descending"}

User: "Forget that last preference"
→ Most recent preference deactivated

Preferences are:

• Extracted using LLM (JSON output, no regex)
• Stored per-user, optionally per-database
• Injected into SQL generation prompts

3. Intent Routing

The Arch-Router model classifies user input:

Intent	Example	Action
SQL_QUERY	"How many orders this month?"	Generate SQL, execute
CONVERSATIONAL	"Thanks!" / "Can you explain?"	LLM response
PREFERENCE	"Always sort by date"	Extract and save preference

Low-confidence inputs default to SQL_QUERY (safer for data product).

4. Schema Ranking (embeddings)

For large schemas (StackOverflow: 56 tables), the embedding ranker reduces what the LLM sees:

1. Converts tables to searchable documents
2. Scores relevance to the question
3. Selects top-k tables (default: 10)
4. FK-aware expansion: Adds linking tables via BFS

Reduces prompt size from 50k+ chars to ~10k while preserving join paths.

Used by the Streamlit UI (scripts/gui.py) and directly via the /generate + /route API endpoints. The CLI ask command sends the full schema to the model, which works fine for small databases like Pagila (~22 tables).

5. LLM Schema Enrichment

At database onboarding, every table and column gets an LLM-generated description:

Table: film_actor
Description: "Links films to actors in a many-to-many relationship"

Column: rental.return_date
Description: "When the rental was returned, NULL if still out"

Enrichment uses sample data for context and runs once (cached).

6. Local-First Storage

All user data stored locally:

Data	Storage	Location
Chats & messages	SQLite	~/.iris/chat.db
User preferences	SQLite	~/.iris/chat.db
Message embeddings	SQLite BLOB	~/.iris/chat.db
Schema cache	JSON files	.schema_cache/

No cloud accounts or API keys required for user data.

---

Add a New Database

Single command to onboard any PostgreSQL database:

python scripts/add_database.py mydb \
    "postgresql://user:pass@host:5432/dbname" \
    --schema public

This automatically:

• Tests connection
• Discovers non-public schemas
• Introspects tables, columns, PKs, FKs (via pg_catalog)
• Enriches with LLM-generated descriptions
• Updates databases.yaml and .env

---

Databases

Database	Tables	Description
pagila	21	DVD rental business (customers, films, payments)
chinook	11	Music store (artists, albums, invoices)
support_tickets	3	IT support tickets (priorities, queues, types)
stackoverflow	56	StackOverflow dump (100GB+, 1M users, 5M posts)

All databases are read-only PostgreSQL accessed via Tailscale.

---

Architecture

System Flow

User Input
    │
    ▼
┌──────────────────┐
│  Intent Router   │ ← Arch-Router model
│  (classify)      │
└────────┬─────────┘
         │
    ┌────┴────┬────────────┐
    ▼         ▼            ▼
SQL_QUERY  PREFERENCE  CONVERSATIONAL
    │         │            │
    ▼         ▼            ▼
┌────────┐ ┌────────┐ ┌────────┐
│Ranker  │ │Extract │ │  LLM   │
│ (embed)│ │ & Save │ │Response│
│ Schema │ └────────┘ └────────┘
│ Build  │
│Generate│ ← SQL model
│Validate│
│Execute │
└────────┘
    │
    ▼
  Results

Module Overview

iris_core/
├── chat/                    # Conversational system
│   ├── session.py          # Main orchestrator
│   ├── router.py           # Intent classification (Arch-Router)
│   ├── context.py          # Conversation context + RAG
│   ├── preferences.py      # Preference extraction & management
│   ├── store.py            # SQLite persistence
│   ├── embeddings.py       # BGE-micro-v2 client
│   └── models.py           # Data models (Chat, Message, Preference)
│
├── schema/                  # Schema processing
│   ├── model.py            # Schema, Table, Column dataclasses
│   ├── enricher.py         # LLM-based descriptions
│   ├── ranker.py           # Table ranking (embeddings) + FK expansion
│   └── formatter.py        # Schema → prompt text
│
├── db/                      # Database connectivity
│   ├── connection.py       # DbTarget, DbRegistry
│   ├── introspection.py    # pg_catalog queries
│   ├── cache.py            # Hash-based schema cache
│   └── sampler.py          # Sample data for enrichment
│
├── sql/                     # SQL generation & execution
│   ├── engine.py           # FastAPI server (4 models)
│   ├── generator.py        # LLM → SQL extraction
│   ├── validator.py        # Safety checks (no DROP/DELETE)
│   └── executor.py         # Execute on live DB
│
├── prompt/                  # Prompt construction
│   ├── templates.py        # System prompts
│   └── builder.py          # Assemble schema + context + question
│
└── eval/                    # Evaluation framework
    ├── result_compare.py   # Result-based comparison
    └── cases/              # Test cases by level (1-5)

---

Evaluation

Run Evals

# All databases, level 1
python scripts/run_eval.py --rerank        # --rerank enables embedding-based ranking

# Single database
python scripts/run_eval.py pagila --rerank

# Specific level (1-5)
python scripts/run_eval.py --level 3 --rerank

# Detailed logging
python scripts/run_eval_with_log.py stackoverflow

Eval Levels

Level	Difficulty	Skills
1	Basic	SELECT, COUNT, WHERE
2	Intermediate	JOINs, GROUP BY, ORDER BY
3	Advanced	Multi-hop JOINs, NOT EXISTS, aggregations
4	Master	Window functions, date arithmetic, CTEs
5	Edge	Recursive CTEs, self-joins, complex ratios

Current Results

Database	Level 1-2	Level 3	Level 4	Level 5	Total
pagila	100%	100%	100%	100%	100%
chinook	100%	100%	100%	100%	100%
support_tickets	100%	100%	100%	100%	100%
stackoverflow	100%	60%	40%	60%	70%

---

Configuration

databases.yaml

Supports two formats:

targets:
  # Decomposed credentials (original)
  pagila:
    host: "100.x.x.x"
    port: 5432
    dbname: "pagila"
    user_env: "PAGILA_READER_USER"
    password_env: "PAGILA_READER_PASSWORD"

  # Connection string (simpler)
  stackoverflow:
    connection_string_env: "STACKOVERFLOW_DATABASE_URL"
    schemas: ["se"]  # Non-public schema support

Environment Variables

# .env file
PAGILA_READER_USER="pagila_reader"
PAGILA_READER_PASSWORD="..."
STACKOVERFLOW_DATABASE_URL="postgresql://user:pass@host:5432/db"

---

Repository Structure

iris-project/
├── iris_core/
│   ├── chat/               # Conversational system
│   ├── schema/             # Schema processing
│   ├── db/                 # Database connectivity
│   ├── sql/                # SQL generation & execution
│   ├── prompt/             # Prompt construction
│   ├── eval/               # Evaluation framework
│   └── config/             # databases.yaml
│
├── scripts/
│   ├── add_database.py        # Auto database onboarding
│   ├── run_eval.py            # Evaluation runner
│   ├── run_eval_with_log.py   # Detailed logging
│   ├── update_cache_facts.py  # Refresh schema enrichment
│   └── gui.py                 # Streamlit interface
│
├── tests/                  # Test suite
└── .env                    # Secrets (not committed)

---

Design Principles

1. Result-based correctness — Evaluated on executed results, not SQL text
2. Conversational first — Memory, context, and preferences matter
3. Local-first — User data stays local, no cloud dependency
4. Runtime introspection — No hardcoded schema assumptions
5. Deterministic pipeline — LLM is one step in a reproducible flow

---

Commands Reference

# Start the engine (loads all models — required before `ask` or GUI)
python -m iris_core serve

# Health check
python -m iris_core health

# Ask a question (requires engine running)
python -m iris_core ask "How many customers are there?" -d pagila
python -m iris_core ask "Top 5 films by rental count?" -d pagila --sql-only

# Launch the Streamlit chat UI
streamlit run scripts/gui.py

# Add a new database target (updates databases.yaml + runs schema enrichment)
python scripts/add_database.py NAME "postgresql://..." --schema SCHEMA

# Run evals against benchmark questions
python scripts/run_eval.py                       # All DBs, level 1
python scripts/run_eval.py pagila                # Single DB
python scripts/run_eval.py --level 4 --rerank    # Level 4 with reranking
python scripts/run_eval_with_log.py pagila       # Detailed log

# Streamlit GUI
streamlit run scripts/gui.py