The self-correction loop: feeding the error back to the model and retrying

Description

Until now, when the model's SQL failed —a hallucinated column, the wrong table— our response was to reject it and return an error. But we have something better to do with that error: feed it back to the model so it can correct itself. This lesson builds the self-correction loop, the pattern that closes the assistant's circle: question → SQL → validate/run → if it fails, feed the error back → retry → answer.

It's the module's most important pattern, and one of the most used in real text-to-SQL (the literature calls it self-correction or self-debugging). The intuition is simple: a model that sees the exact error message —no such column: price— usually fixes its own SQL on the next attempt, the same way you'd fix a typo if the compiler told you where it is. And to avoid cycling forever if the model gets stuck on an error, the loop carries a cap on attempts.

Following the guide's hard rule, the model's part is conceptual: in a real assistant, each attempt is a call to claude-sonnet-5 with the previous attempt's error. Here we simulate the model's responses with a fixed list —we say so clearly— so that the loop itself, which is real code, runs end to end, with lesson 05's run_safely actually running the SQL and returning 211900 once it finally gets it right.

Connection to the module

Lessons 03-05 built validate and run_safely: the ability to judge and run SQL, returning a structured result with ok, stage, and errors. This lesson uses exactly that structured result —the error and its stage— as the input fed back to the model. The loop is the piece that turns a standalone validator and executor into an assistant that recovers from its own mistakes.

Analogy: the reviewer who returns the work with notes

Go back to the brilliant but careless intern from lesson 02. When they hand you SQL with a misspelled column name, you have two options. The bad one: throw out their work and do it yourself. The good one: return it with a note —"the column isn't called price, it's price_cents; fix it"— and let them fix it. The intern is fast and competent; with the right clue, they almost always get it right on the second try.

The self-correction loop is that annotated return, automated. The "reviewer" is your code: it validates and executes, and when something fails, it doesn't discard the SQL —it hands the model the exact error message and asks for another version—. The key is the quality of the note: the more precise the error (no such column: price instead of "something went wrong"), the easier it is for the model to correct. That's why the previous lessons worked hard to produce exact, enriched errors —that effort pays off here—.

And like any sensible reviewer, you don't hand back the work infinitely: if after three rounds the intern still hasn't gotten it right, you stop and escalate. That's the cap on attempts.

The pattern, step by step

The self-correction loop has a fixed shape:

   User's question
        │
        ▼
   ┌─────────────────────────────────────────────┐
   │  For each attempt (up to a cap):             │
   │                                              │
   │   [1] Ask the model for SQL                  │ ← conceptual: call to claude-sonnet-5
   │        (with the previous attempt's error,   │           (here, simulated)
   │         if there was one)                    │
   │        │                                     │
   │        ▼                                     │
   │   [2] run_safely(sql)                        │ ← real execution: validate + run
   │        │                                     │
   │        ├── ok  → return the answer ──────────┼──► DONE (success)
   │        │                                     │
   │        └── failed → save the error,          │
   │                    move to the next attempt  │
   └─────────────────────────────────────────────┘
        │
        ▼
   Attempts exhausted → give up gracefully

Step 2 is real (we run it). Step 1 —the call to the model— is conceptual. The magic is in the cycle: attempt N's error becomes part of attempt N+1's prompt.

What the call to the model with the error looks like (conceptual)

In a real assistant, the correcting attempt sends the model the previous SQL and the error, asking for a fixed version. The shape of that call, with Claude's Messages API, would look something like this (realistic example, not run):

# CONCEPTUAL — this is what the call to the model would look like on a correction attempt.
# Doesn't run in this guide (no network/API); it would in production.
import anthropic

client = anthropic.Anthropic()
response = client.messages.create(
    model="claude-sonnet-5",
    max_tokens=1024,
    system=(
        "You are an assistant that translates questions into SQLite SQL against "
        "the Reservo database. Reply ONLY with the SQL, no explanation.\n\n"
        + schema_context  # Module 2's schema context
    ),
    messages=[
        {"role": "user", "content": question},
        {"role": "assistant", "content": previous_sql},   # what it proposed before
        {"role": "user", "content":                        # the "note" with the error
            f"That SQL failed with this error: {error}. "
            f"Fix it and reply with only the corrected SQL."},
    ],
)
corrected_sql = response.content[0].text

Notice the structure: the user's turn with the question, the assistant's turn with the SQL that failed, and a new user turn with the exact error. That's all the model needs to correct itself. In this guide we don't run that call; we replace it with a function that returns fixed responses, so we can run the whole loop.

Worked example: the loop, run end to end

Let's build the loop and run it. We reuse validate and run_safely from the previous lessons (have them defined in the file). The only new thing is the solve function, which orchestrates the cycle, and a simulated model.

The simulated model. Since there's no API, we simulate the model with a function that takes the question and the last error, and returns SQL. We give it realistic behavior: on the first attempt it hallucinates the price column (a classic error —the column is price_cents—), and after seeing the error, on the second attempt it corrects. It's labeled as a simulation:

# --- SIMULATED model (in production: a real call to claude-sonnet-5) ---
# 1st attempt: hallucinates `price`. After receiving the error, 2nd attempt: fixes to `price_cents`.
def fake_model_selfcorrect(question, error):
    if error is None:                     # first attempt, no previous error
        return "SELECT SUM(price) AS revenue FROM bookings WHERE status = 'confirmed'"
    return "SELECT SUM(price_cents) AS revenue FROM bookings WHERE status = 'confirmed'"

The loop. solve asks the model for SQL, runs it through run_safely, and decides: if it succeeded, return the answer; if it failed, save the error and retry, up to a cap:

def solve(question, con, model, max_attempts=3):
    error = None
    for attempt in range(1, max_attempts + 1):
        sql = model(question, error)       # CONCEPTUAL: the call to the model would go here
        print(f"[Attempt {attempt}] the model proposes:")
        print(f"    {sql}")
        result = run_safely(sql, con)
        if result["ok"]:
            print(f"    -> OK ({result['stage']}). Answer: {result['rows'][0][0]}")
            return result
        error = result["errors"][0]        # this error feeds the next attempt
        print(f"    -> failed at {result['stage']}: {error}")
        print(f"       (this error is fed back to the model for the next attempt)")
    print("No success after exhausting the attempts.")
    return None

solve("How much did we bring in with confirmed bookings?", con, fake_model_selfcorrect)

What to expect:

[Attempt 1] the model proposes:
    SELECT SUM(price) AS revenue FROM bookings WHERE status = 'confirmed'
    -> failed at validation: column 'price' doesn't exist. Did you mean price_cents?
       (this error is fed back to the model for the next attempt)
[Attempt 2] the model proposes:
    SELECT SUM(price_cents) AS revenue FROM bookings WHERE status = 'confirmed'
    -> OK (executed). Answer: 211900

There's the complete loop, run for real. The first attempt failed with column 'price' doesn't exist —and notice the enriched error even suggests price_cents, the exact fix—. That error gets fed back to the model (conceptual), which on the second attempt proposes the correct SQL, and run_safely actually runs it, returning 211900: Reservo's confirmed-revenue anchor. The assistant recovered from its own error with no human intervention.

The cap: why the loop has to be able to give up

What happens if the model doesn't correct itself? A model can get stuck on the same error, or fall into a cycle of different errors that never converges. Without a limit, the loop would run forever —burning calls to the model (which cost money and time) without getting anywhere—. max_attempts is the brake.

Let's simulate a stubborn model that never corrects, and watch the cap act:

# SIMULATED model that gets stuck on the same error
def fake_model_stubborn(question, error):
    return "SELECT SUM(price) FROM bookings"   # always the same hallucinated column

solve("How much did we bring in?", con, fake_model_stubborn, max_attempts=3)

What to expect:

[Attempt 1] the model proposes:
    SELECT SUM(price) FROM bookings
    -> failed at validation: column 'price' doesn't exist. Did you mean price_cents?
       (this error is fed back to the model for the next attempt)
[Attempt 2] the model proposes:
    SELECT SUM(price) FROM bookings
    -> failed at validation: column 'price' doesn't exist. Did you mean price_cents?
       (this error is fed back to the model for the next attempt)
[Attempt 3] the model proposes:
    SELECT SUM(price) FROM bookings
    -> failed at validation: column 'price' doesn't exist. Did you mean price_cents?
       (this error is fed back to the model for the next attempt)
No success after exhausting the attempts.

Three attempts, three identical failures, and the loop stops gracefully instead of cycling. In a real assistant, that "no success" turns into an honest answer to the user ("I couldn't generate a valid query for that question") instead of a hang or a traceback. Giving up well is part of being trustworthy.

A cap of 2 or 3 is usually enough: if the model doesn't correct itself in two or three rounds with the error in hand, more attempts rarely help. The exact number is an engineering decision —you balance it against the cost of each call to the model—.

Why the exact error matters so much

The loop works because the error message is specific and actionable. Compare what we feed back to the model:

• Good (what we do): column 'price' doesn't exist. Did you mean price_cents? — the model knows what's wrong (price) and what the likely fix is (price_cents).
• Bad (what we avoid): Error or the query failed — the model has nothing to work with; its second attempt is as much a blind guess as the first.

All the work from lessons 03-05 —capturing the engine's exact error, enriching it with the catalog, structuring it with its stage— pays off here. A self-correction loop is only as good as the errors it feeds. This is the principle worth remembering: in a good system, the error isn't a dead end; it's the information that makes correction possible.

Common mistakes

1. A loop with no cap. Without max_attempts, a model that doesn't converge runs forever, burning calls. Every self-correction loop needs a brake and an honest exit when it hits it.

2. Feeding the model a vague error. "The query failed" doesn't tell the model what to fix. The loop's value depends on passing it the exact error (no such column: price), ideally enriched with a suggestion. A poor error produces a second attempt as bad as the first.

3. Running the call to the model in this guide. There's no network/API in the environment. The model is simulated with fixed responses, clearly labeled, so the real loop can run. In production, every model(question, error) call is a real call to claude-sonnet-5 with the previous attempt's error.

4. Confusing this loop with the complete agent loop. M4's loop is validate → run → if it fails, retry with the error. The agent's complete loop —tool-calling, multi-step, the model's decision about which tool to use and when to stop— is Module 6. Here the model only returns SQL; it doesn't orchestrate tools.

5. Retrying without changing anything. If attempt N+1 doesn't receive attempt N's error, it isn't self-correction: it's repeating the same call and hoping for a different result. The previous attempt's error has to go into the next prompt.

Exercises

Exercise 1: Counting the attempts (Easy)

Modify solve so it also returns the attempt number where it succeeded (or None if it failed). Run it with the self-correcting model and confirm it succeeds on attempt 2.

def solve_counting(question, con, model, max_attempts=3):
    error = None
    for attempt in range(1, max_attempts + 1):
        sql = model(question, error)
        result = run_safely(sql, con)
        if result["ok"]:
            return {"attempt": attempt, "value": result["rows"][0][0]}
        error = result["errors"][0]
    return None

print(solve_counting("How much confirmed revenue did we bring in?", con, fake_model_selfcorrect))

{'attempt': 2, 'value': 211900}

Exercise 2: A model that fixes the table (Medium)

Write a simulated model that on the first attempt uses the reservations table (which doesn't exist) and, after the error, corrects to bookings. Run it with solve and confirm the second attempt counts the 23 bookings.

def fake_model_fix_table(question, error):
    if error is None:
        return "SELECT COUNT(*) AS n FROM reservations"
    return "SELECT COUNT(*) AS n FROM bookings"

solve("How many bookings are there?", con, fake_model_fix_table)

[Attempt 1] the model proposes:
    SELECT COUNT(*) AS n FROM reservations
    -> failed at validation: table 'reservations' doesn't exist. Real tables: ['bookings', 'members', 'payments', 'rooms']
       (this error is fed back to the model for the next attempt)
[Attempt 2] the model proposes:
    SELECT COUNT(*) AS n FROM bookings
    -> OK (executed). Answer: 23

Exercise 3: A cap of a single attempt (Hard)

What happens if you set max_attempts=1 with the self-correcting model? Predict the result and then run it. Explain why a cap of 1 negates the loop's benefit.

solve("How much confirmed revenue did we bring in?", con, fake_model_selfcorrect, max_attempts=1)

[Attempt 1] the model proposes:
    SELECT SUM(price) AS revenue FROM bookings WHERE status = 'confirmed'
    -> failed at validation: column 'price' doesn't exist. Did you mean price_cents?
       (this error is fed back to the model for the next attempt)
No success after exhausting the attempts.

Summary and next step

• The self-correction loop closes the circle: question → SQL → validate/run → if it fails, feed the exact error back to the model → retry → answer. It's the central pattern of a robust assistant (self-correction/self-debugging in the text-to-SQL literature).
• The model's part is conceptual (in production, a call to claude-sonnet-5 with the previous attempt's error); the loop and run_safely are real and run. We saw the corrected SQL run and return 211900.
• The loop carries a cap on attempts (max_attempts) to avoid cycling if the model doesn't converge, and an honest exit when it hits it —giving up well is part of being trustworthy—.
• The loop is only as good as the errors it feeds: an exact, enriched message (no such column: price → price_cents?) makes the correction straightforward; a vague error turns it into another blind guess. All the work from lessons 03-05 pays off here.

Next lesson: Common failure modes — A map of everything that goes wrong in generated SQL (a hallucinated column, the wrong table, a quote, ambiguity in a JOIN, and the missing filter that runs but lies) and which validation layer catches each one... and which one none of them catch.

Additional resources

1. Chen et al., "Teaching Large Language Models to Self-Debug" — The paper that formalized feeding a model its execution error back so it can correct its own code; this loop's theoretical basis.
2. Claude API documentation — Messages — The shape of the call to the model (with the question, the previous SQL, and the error) we show as a concept.
3. SQLite — EXPLAIN — The validation that produces the exact error that feeds the loop.
4. BIRD: Big Bench for Large-Scale Database Grounded Text-to-SQL — Benchmark where self-correction techniques show measurable improvements in execution accuracy.