v0.2.0rc17 - Release Candidate: Halve the latency of each question, report every token, stop opening database connections per question, and fail fast on errors that retrying cannot fix

Summary

0.2.0rc16 made the documented path work. This release is about what each question costs once it works: LLM calls, tokens, database connections, and how long an error takes to surface. The numbers below were measured with the same harness on 0.2.0rc16 and on this code: a local fake of the OpenAI API (so each request can be counted), pg_stat_database for connections, and live runs against the real OpenAI and Anthropic APIs. The last two rows of the table describe behavior covered by tests, not measurements.

Behavior changes:

• The LLM no longer reviews its own SQL by default (highlight 1).
• tokens_used now counts every LLM call, so it is higher than before whenever a question needed retries or repairs (highlight 2).
• Authentication and request errors are no longer retried (highlight 5).

Why this release matters

Measured per question	rc16	rc17
LLM calls per ask()	2 (generation + LLM review)	1 (2 with llm_validation=True)
ask() latency, live OpenAI	~6 s	~4 s
ask() latency, live Anthropic	~4 s	~2 s
tokens_used vs tokens actually spent, simple question	698 of 1220	698 of 698
tokens_used vs tokens actually spent, multi-table question	1380 of 2452	1380 of 1380
New database connections per ask() in the default mode, simple / multi-table	10 / 41	0 / 0
ANALYZE of the whole database on the first connect() and every 24 h	yes	no
Invalid API key	8.1 s and 3 requests to fail	0.01 s, 1 request
Model answer that is not the expected JSON	3 attempts with 4-10 s waits	asked again once, no wait, both answers counted
Model answer cut off at max_tokens	3 attempts, "Invalid JSON response"	1 attempt, says to raise max_tokens

For the two measured questions, the schema context sent to the model is byte-identical to rc16's: the prompt did not change, only what happens around it. Executing the query (validate=True) still opens one connection, as in rc16.

Highlights

1. The LLM no longer reviews its own SQL by default (behavior change)

Every ask() made a second LLM call that sent the whole schema context back to the model to review the SQL. It doubled the latency of each question, and its tokens were not reported. That review predates every other check: column validation, dialect rules, semantic rules, the read-only check and execution against the database were all added later and run without extra LLM calls. Execution modes still validate against the database, which is a stronger signal than asking the model.

In the default generate_only mode nothing executes the query, so the generated SQL is now checked only by those deterministic rules. If you relied on the LLM review in that mode, use validate=True to run the query against the database, or turn the review back on.

Turn the review back on with any of:

nlp = await nlp2sql.connect(database_url, provider=provider, llm_validation=True)

export NLP2SQL_LLM_VALIDATION=true
nlp2sql query --llm-validation ...

NLP2SQL.validate(sql) still asks the LLM: there the review is the request.

2. tokens_used counts every LLM call

It used to report only the last generation call. It now adds up the generation, the column and semantic retries, the execution repair and the LLM review when enabled. result.metadata["token_usage"] has the breakdown: total, generation, validation, llm_calls.

3. No database connections per question

Each ask() called get_table_info() for every table in the schema context: 5 queries, each on a new connection, per table. The context is now built from the table list that connect() already loaded with one bulk query. There is still no connection pool, on purpose: an event-loop-bound pool broke nlp2sql inside agent tools (#35).

4. No ANALYZE on connect

PostgreSQLRepository.refresh_schema() ran ANALYZE on the whole database on the first connect() and every 24 hours. Nothing the library reads depends on it. refresh_schema() now drops the cached table list instead, so the next get_tables() reads the database again. RedshiftRepository.refresh_schema(), which did nothing, does the same.

5. Typed provider errors, retried only when retrying can help

New subclasses of ProviderException, raised by the OpenAI, Anthropic and Gemini adapters and available as the __cause__ of the QueryGenerationException raised by ask():

Exception	When	Retried
ProviderAuthenticationError	HTTP 401/403	no
ProviderRequestError	other 4xx (unknown model, bad request), Anthropic refusals, Gemini safety blocks	no
ProviderRateLimitError	HTTP 429	yes, with backoff
ProviderUnavailableError	408/409/5xx, timeouts, connection errors	yes, with backoff
ProviderResponseFormatError	the model answered twice without the expected JSON	asked again once, no wait

An answer cut off at max_tokens is not asked for again (the same limit would cut it again); it fails with a ProviderException that says to raise max_tokens.

Major code changes

Library

• src/nlp2sql/schema/manager.py: schema context from the cached table list
• src/nlp2sql/services/query_service.py: optional LLM review, token accounting across every call
• src/nlp2sql/config/settings.py: NLP2SQL_LLM_VALIDATION
• src/nlp2sql/__init__.py, src/nlp2sql/client.py, src/nlp2sql/cli.py: llm_validation option and --llm-validation flag
• src/nlp2sql/adapters/postgres_repository.py, src/nlp2sql/adapters/redshift_adapter.py: refresh_schema() drops the table cache, no ANALYZE
• src/nlp2sql/adapters/_provider_errors.py (new): error classification and the malformed-output retry
• src/nlp2sql/adapters/openai_adapter.py, anthropic_adapter.py, gemini_adapter.py: typed errors, transient-only retries, truncation checks, tokens of the LLM review; Anthropic and Gemini build TokenLimitException correctly
• src/nlp2sql/exceptions/__init__.py: the five new exceptions

Tests and CI

• New: test_cost_per_question.py (LLM calls, tokens, database sessions and last_analyze per question, in the CI integration lane), test_provider_errors.py, test_schema_context_cache.py, test_refresh_schema.py
• Extended: test_query_service_contract.py, test_retrieval_pipeline.py, fakes/fake_openai.py (token usage, raw answers, finish_reason)
• .github/workflows/ci.yml: the integration lane runs the cost-per-question suite

Documentation changes

• docs/API.md: llm_validation, tokens_used and token_usage, and a corrected error-handling example (errors arrive as the __cause__ of QueryGenerationException)
• docs/CONFIGURATION.md: NLP2SQL_LLM_VALIDATION and what each execution mode checks
• CHANGELOG.md: 0.2.0rc17 entry

Versioning

Version updated to 0.2.0rc17 in pyproject.toml, src/nlp2sql/__init__.py, src/nlp2sql/config/settings.py and uv.lock. MCP server aligned in mcp_server/pyproject.toml ([project] and [tool.mcp]) and mcp_server/__init__.py. tests/test_version_consistency.py checks all of them.

Verification

• Unit suite: 344 passed.
• Integration (pytest -m integration, Docker database): 47 passed; 9 environment skips (Redshift/LocalStack not running).
• CI green on pull requests #49 and #50 (test, pgvector-integration, clean-install).
• The rc16 field-test harness re-run against the wheel in a fresh environment without the lock file: the rows for LLM calls, tokens, database connections, ANALYZE and the invalid API key. The malformed and truncated answer rows are covered by unit tests through the real OpenAI SDK and mocked Anthropic and Gemini clients.
• Live gates against the real APIs: OpenAI (gpt-5.6-luna, on the #49 wheel) with tokens_used=1568 over 1 call, and Anthropic (claude-sonnet-5, on the #50 wheel) with tokens_used=1125 over 1 call; both SQL valid and executed. Both models answered with valid JSON, so the malformed-output retry was not exercised live.
• The new tests fail on the code they fix: the #49 tests on 0.2.0rc16, the #50 tests on #49.
• Unit and integration suites re-run on the release commit (5790899).

Known limitations and follow-ups

• The library still logs to stdout without configuration, including user questions and raw model responses; NLP2SQL_LOG_LEVEL is not used.
• QueryResult does not expose validation reasons or result rows.
• import nlp2sql takes about 3 s (scikit-learn is imported at module level), and a warm start with OpenAI embeddings pays one embedding request.
• CLI: inspect prints the database password and ignores its filter flags, providers test reports success without calling the API, cache info does not find the FAISS index; NLP2SQL.stats fails inside a running event loop.
• Gemini still uses the end-of-life google-generativeai SDK; the Redshift integration suite is tracked in #45.

Closes

• None. Shipped as pull requests #49 and #50 (no tracking issues).

Files changed

• 28 files changed, 1096 insertions(+), 129 deletions(-)
• .github/workflows/ci.yml
• CHANGELOG.md
• docs/API.md
• docs/CONFIGURATION.md
• mcp_server/__init__.py
• mcp_server/pyproject.toml
• pyproject.toml
• src/nlp2sql/__init__.py
• src/nlp2sql/adapters/_provider_errors.py (new)
• src/nlp2sql/adapters/anthropic_adapter.py
• src/nlp2sql/adapters/gemini_adapter.py
• src/nlp2sql/adapters/openai_adapter.py
• src/nlp2sql/adapters/postgres_repository.py
• src/nlp2sql/adapters/redshift_adapter.py
• src/nlp2sql/cli.py
• src/nlp2sql/client.py
• src/nlp2sql/config/settings.py
• src/nlp2sql/exceptions/__init__.py
• src/nlp2sql/schema/manager.py
• src/nlp2sql/services/query_service.py
• tests/fakes/fake_openai.py
• tests/test_cost_per_question.py (new)
• tests/test_provider_errors.py (new)
• tests/test_query_service_contract.py
• tests/test_refresh_schema.py (new)
• tests/test_retrieval_pipeline.py
• tests/test_schema_context_cache.py (new)
• uv.lock