name: sql-debug-env
version: "1.0.0"
description: >
  A real-world OpenEnv environment where AI agents learn to debug and fix
  broken SQL queries. The agent receives a database schema, sample data,
  a buggy SQL query, and an error message, then must produce a corrected
  query that runs without error and returns the expected result set.
author: hackathon-submission
tags:
  - openenv
  - sql
  - debugging
  - code-repair
  - real-world
  - database

environment:
  type: http
  port: 8000

tasks:
  - id: easy_missing_from
    name: "Easy: Missing FROM clause"
    description: "List the first name and last name of all employees in department 10, ordered by last name ascending."
    difficulty: easy
  - id: easy_wrong_column
    name: "Easy: Wrong Column Name"
    description: "Find the total salary budget for each department. Return department_id and the total salary, ordered by department_id."
    difficulty: easy
  - id: easy_missing_groupby
    name: "Easy: Missing GROUP BY"
    description: "Count the number of orders placed by each customer. Return customer_id and order_count, only for customers with more than 1 order."
    difficulty: easy
  - id: medium_wrong_join
    name: "Medium: Wrong JOIN Condition"
    description: "List each employee's full name and their department name. Return first_name, last_name, department_name ordered by last_name."
    difficulty: medium
  - id: medium_wrong_aggregate
    name: "Medium: Wrong Aggregate Function"
    description: "Find the average order amount per customer. Return customer_id and avg_amount for customers whose average is above 100. Order by avg_amount descending."
    difficulty: medium
  - id: medium_inner_vs_left
    name: "Medium: INNER vs LEFT JOIN"
    description: "List ALL customers along with the total amount they've spent. Customers who haven't placed any orders should show 0.0 as total_spent. Return customer_id, customer_name, total_spent ordered by customer_id."
    difficulty: medium
  - id: hard_window_function
    name: "Hard: Window Function Lag"
    description: "For each employee, compute a running_avg_salary: the average of the current employee's salary and the employee immediately before them in the same department (ordered by id). For the first employee in each department, it equals their own salary. Return dept_id, first_name, id, salary, running_avg_salary ordered by dept_id, id."
    difficulty: hard
  - id: hard_correlated_subquery
    name: "Hard: Correlated Subquery"
    description: "Find all products that have never been ordered. Return product_id and product_name ordered by product_id."
    difficulty: hard
  - id: hard_multi_cte
    name: "Hard: Multi-CTE Logic"
    description: "Calculate month-over-month revenue change. For each month (in YYYY-MM format), return the month, total_revenue, and revenue_change (current month minus previous month). For the first month, revenue_change should be 0. Order by month ascending."
    difficulty: hard

action_space:
  type: object
  description: "Agent submits a corrected SQL query string"
  properties:
    query:
      type: string
      description: "The corrected SQL query"
    explanation:
      type: string
      description: "Optional explanation of what was fixed"

observation_space:
  type: object
  description: "Current debugging context"
  properties:
    schema:
      type: string
      description: "CREATE TABLE statements for all tables"
    sample_data:
      type: string
      description: "INSERT statements with sample rows"
    buggy_query:
      type: string
      description: "The SQL query that needs to be fixed"
    error_message:
      type: string
      description: "Error from running the buggy query, or empty if semantic bug"
    task_description:
      type: string
      description: "Natural language description of what the query should do"
    expected_columns:
      type: array
      description: "Column names expected in the result"
    attempts_remaining:
      type: integer
      description: "How many fix attempts remain"
    last_attempt_result:
      type: string
      description: "Result of last submitted query, if any"
    reward:
      type: number
      description: "Reward from last action"
    done:
      type: boolean
      description: "Whether the episode is complete"

reward_function:
  description: >
    +1.0 for a fully correct query (runs without error AND returns expected rows).
    +0.5 for a query that runs without error but returns wrong results.
    +0.2 for a query that fixes the original error but introduces a new one.
    0.0 for a query that still fails with the same error.
    -0.1 penalty per wasted attempt beyond the first.
    Episode ends when correct or max_steps (5) reached.