9 · Bugs That Shipped

1 · The eval that measured the quota, not the model

Symptom. A 23-case eval reported 26.1% execution accuracy. Every join case failed. The run took three hours.

Diagnosis. The obvious reading is “this model can’t write joins”. The result JSON said otherwise:

"validation_error": "generation failed: 429 RESOURCE_EXHAUSTED ...
  quotaId: 'GenerateRequestsPerDayPerProjectPerModel-FreeTier', quotaValue: '20'"

Gemini’s free tier allows 20 requests per day. Six cases ran; seventeen never reached the model.

Root cause. The retry policy treated all 429s as transient and honoured the retryDelay: 50s hint — four backoffs, ~180 s per case, for a quota that resets daily. Per-minute and per-day 429s carry identical prose and both carry a RetryInfo; only the quotaId distinguishes them.

Fix. Classify on the machine-readable quota ID:

_DAILY_QUOTA_MARKERS = ("perdayper", "requestsperday", "dailylimit", "insufficientquota")
squashed = re.sub(r"[^a-z]", "", message)   # survive casing, hyphens, underscores

Per-minute still retries; daily fails in under a second. Skipped cases are excluded from every rate and from latency percentiles, and both reports carry an abort banner.

Lesson. Before believing a bad eval score, check whether you measured the model or your own infrastructure. And match on structured identifiers, not vendor prose.

2 · Schema sync that hung forever

Symptom. None. That is what made it bad. POST /v1/sources/{id}/sync returned a job ID; the job stayed pending forever. No error, no log line.

Root cause. task_routes sent work to the schema and insight queues, but the documented worker command had no -Q, so it consumed only the default celery queue. Celery accepted each task and nothing ever ran it.

Since schema sync must succeed before any question can be asked, the deployment would have looked healthy and been completely unusable.

Fix. The queue list lives beside the routes, and both worker commands consume it:

DEFAULT_QUEUE = "celery"
QUEUES = ("schema", "insight", DEFAULT_QUEUE)

tests/test_celery_routing.py fails the build if a task routes somewhere no worker listens — and it reads docker-compose.yml and render.yaml, so dropping -Q breaks CI rather than production.

Lesson. Silent success is worse than a loud failure. When a component can accept work it will never do, add a test that config and execution agree.

3 · Connections attached to a different loop

Symptom. The first production schema sync failed with:

got Future <Future pending> attached to a different loop

Root cause. Celery tasks are synchronous and call asyncio.run(), which creates a fresh event loop. They used the module-level AsyncSessionLocal, whose engine caches pooled connections bound to the loop that opened them — and asyncpg connections cannot move between loops.

The subtle part: this was never specific to the inline worker; that just exposed it fastest. With INLINE_WORKERS the API’s loop had already populated the pool, so job one died. A real Celery worker survives task one and fails from task two onward — which presents as intermittent flakiness.

Fix. A private, pool-less engine per job:

engine = create_async_engine(settings.database_url, poolclass=NullPool)

Lesson. An event loop is part of a connection’s identity. Anything cached across loop boundaries — pools, clients, locks — is a latent bug.

4 · Prompts that changed between restarts

Symptom. Nothing visible. Found by reading, not by failing.

Root cause. Schema selection ended return list(top_set). Python randomises string hashing per interpreter run, so set iteration order differed between processes. The selected tables — and therefore the prompt — varied across restarts.

Two consequences: the same question produced different prompts, and eval runs were not reproducible, so you could not distinguish a prompt improvement from noise.

Fix. Score order, ties broken by name, FK additions appended sorted. Verified identical across four PYTHONHASHSEED values.

Lesson. Non-determinism upstream of a model makes everything downstream unmeasurable. Sets are unordered — never let one reach an output.

5 · The regex that rejected a German city

Symptom. SELECT * FROM stores WHERE city = 'Alter Markt' rejected as dangerous SQL.

Root cause. The DML keyword sweep ran over the raw SQL. 'Alter Markt' contains ALTER.

Fix. Blank out string literals, quoted identifiers and comments before the sweep:

_LITERAL_PATTERN = re.compile(
    r"'(?:[^']|'')*'|\"(?:[^\"]|\"\")*\"|--[^\n]*|/\*.*?\*/", re.DOTALL
)

Lesson. Lexical scanning without a lexer is a bug generator. The regex is a backstop; the AST walk is the real control — get that ordering right in your explanation.

6 · Data-modifying CTEs

Symptom. Caught while writing tests, not in production.

WITH evil AS (DELETE FROM orders RETURNING *)
SELECT * FROM evil

The root node is a Select. A root-shape check passes it.

Fix. Walk the entire AST for write nodes, not just the root.

Lesson. Validate the whole tree, not the top of it. This is the single best example of why an allow-list plus a full traversal beats pattern matching.

7 · Docker build failing before it started

Symptom. poetry install failed in the image build.

Root cause. pyproject declares packages = [{include = "src"}], but dependencies are installed before src/ is copied so the layer cache stays useful. Poetry tried to install the project itself and found nothing.

Fix. --no-root.

Lesson. Layer ordering for cache efficiency changes what exists at each step. The optimisation and the failure are the same decision.

8 · A CRLF byte that broke Linux

Symptom. Would have been: /bin/sh^M: bad interpreter: No such file or directory.

Root cause. The repo is developed on Windows, where git checks files out with CRLF. A shell script with a CRLF shebang is invalid on Linux — and the file looks perfectly correct in an editor.

Fix.

*.sh text eol=lf

Plus chmod +x in the Dockerfile, since Windows carries no POSIX execute bit.

Lesson. Cross-platform development has invisible failure modes. Anything executed inside a container needs its line endings pinned.

9 · Telemetry that failed the request

Symptom. POST /v1/query returned 500. POST /v1/query/stream returned the correct answer for the same question.

Diagnosis. Testing against a warm cache was decisive: a request that made no model call and ran no query still 500’d in 2.3 s. So the failure was entirely after the pipeline finished.

Root cause. The streaming path wrapped its audit-log write in try/except with the comment “logging must never break the response”. The sync path called it unguarded. A failed INSERT into query_logs therefore discarded a completed result — one the caller had already paid for in model tokens and database time.

Fix.

try:
    await self._log_query(ctx, source_id, provider_model.id, api_key_id)
except Exception as e:
    await self.db.rollback()   # a failed INSERT poisons the session
    log.warning("query_service.log_failed", error=str(e))

The rollback matters independently: without it the session is left in a state where any later statement errors too, so the next request fails for an unrelated reason and the real cause is lost.

Lesson. Telemetry must never be able to fail the thing it observes. And when two code paths make the same call, the guarantees around it should match — asymmetry between a sync and an async path is a reliable place to find bugs.

10 · Rate limiting that took down the API

Symptom. Found while writing HTTP tests: every request 500’d when Redis was unavailable.

Root cause. slowapi’s Redis storage connects lazily. The existing fallback only handled Redis being down at boot; an outage after startup turned every request into a 500.

Fix. swallow_errors=True — degrade open.

Lesson. A protective mechanism must not become a single point of failure. Losing rate limiting is bad; losing the API is worse. Be explicit that this is a deliberate availability-over-enforcement trade — an interviewer may well push back, and “it depends on whether the limiter protects against abuse or against cost” is the right response.

How to tell one of these

Use four beats, in this order:

1. Symptom — what was observed, concretely.
2. Investigation — what you checked, and what ruled things out. This is the part being assessed.
3. Root cause — the actual mechanism, one sentence.
4. Fix and prevention — what changed, and what test stops it returning.

Number 1, 3 and 9 are the strongest, because in each the obvious explanation was wrong and the investigation is what found the real one.