README.md

---
title: Sql Debug Environment Server
emoji: 🏒
colorFrom: pink
colorTo: red
sdk: docker
pinned: false
app_port: 8000
base_path: /web
tags:
  - openenv
  - sql
  - debugging
  - optimization
---

# 🏒 OpenEnv: SQL Debug Environment

An [OpenEnv](https://openenv.dev)-compliant environment where AI agents fix broken SQL queries and optimize slow ones against in-memory SQLite databases.

> ✅ **Validator:** `openenv validate` passes when the environment is wired up correctly
> 🚀 **Local API:** `https://abhinavthedev-sql-debug.hf.space`
> 📖 **Swagger UI:** `https://abhinavthedev-sql-debug.hf.space/docs`

---

## 🎯 Environment Description

This environment simulates the work of a SQL engineer who must repair syntax errors, correct logic bugs, and improve query performance. Agents receive a schema, a broken or slow query, and a natural-language target description. They submit SQL queries, observe the execution result and query plan, and are scored on correctness and efficiency.

The environment is intentionally practical: each task mirrors a real debugging pattern used in analytics, reporting, and data engineering workflows.

---

## 📋 Tasks

### Task 1 - Syntax Fix *(Easy)*
**Task ID:** `syntax_fix_001`

**Objective:** Fix a malformed query so it returns all orders where `amount > 500`.

| Field | Description |
|---|---|
| `schema` | `orders` table with `id`, `customer`, `amount`, `order_date` |
| `broken_query` | `SELEC * FORM orders WERE amount > 500` |
| `target` | Return all orders where amount is greater than 500 |

**Max steps:** 5 | **Difficulty:** Easy

---

### Task 2 - Logic Fix *(Medium)*
**Task ID:** `logic_fix_001`

**Objective:** Correct a join bug so only employees in valid departments are returned.

| Field | Description |
|---|---|
| `schema` | `employees` and `departments` tables |
| `broken_query` | Query uses `LEFT JOIN` but should exclude missing departments |
| `target` | Return employees in departments with budget > 400000 |

**Max steps:** 8 | **Difficulty:** Medium

---

### Task 3 - Query Optimization *(Hard)*
**Task ID:** `optimize_001`

**Objective:** Rewrite a correlated subquery into an efficient CTE or grouped subquery.

| Field | Description |
|---|---|
| `schema` | `transactions` table with generated sample rows |
| `broken_query` | Correlated subquery that scans per row |
| `target` | Return completed transactions above the user's average amount |

**Max steps:** 10 | **Difficulty:** Hard

---

## 🔌 API Reference

### Base URL
```text
https://abhinavthedev-sql-debug.hf.space
```

### Core Endpoints

| Method | Endpoint | Description |
|---|---|---|
| `POST` | `/reset` | Start a new episode; pass `task_id` to choose a task |
| `POST` | `/step` | Submit a SQL query and receive the next observation |
| `GET` | `/state/{session_id}` | Inspect the current episode state |
| `GET` | `/schema` | View action, observation, and state schemas |
| `GET` | `/ws` | WebSocket endpoint for low-latency sessions |
| `GET` | `/health` | Health check |
| `GET` | `/docs` | Swagger UI |

---

## 🎮 Action Space

The agent submits a single SQL query each step.

```json
{
  "query": "SELECT * FROM orders WHERE amount > 500"
}
```

### Example Actions

```json
{ "query": "SELECT * FROM orders WHERE amount > 500" }

{ "query": "SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id WHERE d.budget > 400000" }

{ "query": "WITH avg_amount AS (SELECT user_id, AVG(amount) AS avg_amount FROM transactions GROUP BY user_id) SELECT t.* FROM transactions t JOIN avg_amount a ON t.user_id = a.user_id WHERE t.status = 'completed' AND t.amount > a.avg_amount" }
```

---

## 📊 Observation Space

```json
{
  "task_id": "syntax_fix_001",
  "schema_sql": "CREATE TABLE orders (...)",
  "current_query": "SELEC * FORM orders WERE amount > 500",
  "error_message": "near \"SELEC\": syntax error",
  "query_result": [],
  "execution_plan": "",
  "step_count": 0,
  "target_description": "Return all orders where amount is greater than 500",
  "reward_so_far": 0.0,
  "available_tasks": ["syntax_fix_001", "logic_fix_001", "optimize_001"],
  "done": false,
  "reward": 0.05
}
```

---

## 💰 Reward Function

The reward is computed from syntax validity, result correctness, and query plan quality.

| Event | Reward |
|---|---|
| Query fails with syntax error | `0.05` |
| Query runs successfully | contributes to the main score |
| Correct row match on easy and medium tasks | up to `0.6` of the score |
| Good query plan on hard task | up to `0.2` of the score |
| Uses correlated-subquery pattern on hard task | heavy plan penalty |
| Excessively long query | length penalty |

Final scores are clamped to the range `[0.0, 1.0]`.

---

## 🚀 Setup & Usage

### Option 1 - Run Locally

```bash
pip install -e .
uvicorn server.app:app --host 0.0.0.0 --port 8000 --reload
# Open https://abhinavthedev-sql-debug.hf.space/docs
```

### Option 2 - Run with Docker

```bash
docker build -t sql-debug-env -f server/Dockerfile .
docker run -p 8000:8000 sql-debug-env
curl https://abhinavthedev-sql-debug.hf.space/health
```

### Option 3 - Run the Inference Loop

```bash
export SERVER_URL=https://abhinavthedev-sql-debug.hf.space
export API_KEY=sk-...
python inference.py
```

The inference script defaults to `syntax_fix_001`, logs each step, and stops when the episode ends or the step budget is reached.

---

## 🏗️ Project Structure

```text
sql_exp/
├── client.py              # OpenEnv client wrapper
├── inference.py           # LLM-driven inference loop
├── models.py              # Action and observation models
├── openenv.yaml           # OpenEnv manifest
├── pyproject.toml         # Project metadata and dependencies
├── runner.py              # SQLite query runner
├── server/
│   ├── app.py             # FastAPI app and OpenEnv wiring
│   ├── Dockerfile         # Container definition
│   └── sql_debug_environment.py  # Core environment logic
├── tasks/
│   ├── task_easy.py       # Syntax-fix task
│   ├── task_medium.py     # Join logic task
│   └── task_hard.py       # Query optimization task
├── graders/
│   ├── grader_easy.py       # Syntax-fix task
│   ├── grader_medium.py     # Join logic task
│   └── grader_hard.py       # Query optimization task
└── README.md              # Project overview
```

---

## 🛠️ Tech Stack

- **Python 3.10+** - Runtime
- **FastAPI** - HTTP framework
- **OpenEnv Core** - Environment server and client primitives
- **SQLite** - Query execution engine
- **Uvicorn** - ASGI server
- **Docker** - Containerization

---

## 📝 License

BSD-style license, matching the source headers in this repository.