Blog
Engineering notes from building nlqdb
SQL traps, LLM-pipeline debugging, and honest comparisons — lessons from building a natural-language database, written up as we hit them. Every claim is verifiable, and every post names what nlqdb doesn't do.
-
Your link checker can't see your JavaScript.
A dead-link sweep over built HTML read '0 dead' while real clicks 307-redirected. The navigations were client-side location.assign() calls a checker never parses out of dist/.
-
Row-level security has a flavour. The default one is a silent breach.
Postgres RLS policies default to PERMISSIVE, so they OR together — a per-agent policy beside a tenant policy widens access instead of narrowing it. The one keyword that fixes it, and four traps.
-
Your docs promised a tool your server never shipped.
An agent product advertised a tool it never built — a new user's first call hit 'tool not found.' The fix isn't a better list; it's deriving the advertised set from the shipped artifact.
-
The redesign shipped. The smoke test kept walking the old UI.
Acceptance walkers that pin literal UI copy catch regressions — until a redesign turns them into a 0/9 that mixes real breakage with pure test-drift. The triage cost is the trap, not the literals.
-
Your recovery code runs once. Your failure doesn't.
A best-effort repair that runs once turns one silent skip into a permanent outage. Fix the root, keep it idempotent, and re-trigger from the steady-state symptom — the event never recurs.
-
A green checkmark has a half-life.
When an expensive test suite can't run on every push, passing stops being a state and becomes an event. Score each suite pass × freshness with a linear decay so the number rots until it re-runs.
-
We rebuilt staging's database every run. The registry remembered everything.
An environment is only as ephemeral as the most persistent store that references it. Enumerate every store that outlives the rebuild and reset it at spin-up — teardown can't be the invariant.
-
Ownership transfer was a one-row UPDATE. Then we added least-privilege.
Hardening queries to per-tenant roles and RLS quietly broke our ownership transfer: it retargeted one authorization store out of four. Transfer must move them all — idempotently, in one batch.
-
Your most active user is your test suite.
Pre-launch, synthetic traffic IS your traffic: e2e walkers register users and run real queries, so every dashboard quietly measures your robots. Three places it bit us, three fixes.
-
Your five fallback models are one point of failure.
A model-diverse fallback list on one gateway saturates as a unit — five models, one rack. Make the fallback unit a lane (base URL, key, candidates), not a longer list on the same pool.
-
An "open question" that's already decided is worse than one that's still open.
Decision logs rot at the seam between open and answered: a decided-but-unmarked bullet makes readers re-litigate closed calls. Make resolved a greppable state and count unmarked bullets as debt.
-
Your metric is only as honest as the layer you emit it from.
A destructive-op retry rate emitted at the HTTP route can go negative — the route can't tell reads from writes. Emit metrics at the lowest layer where the distinction is certain.
-
You need to rotate an encryption key. You don't need a key-version column.
Rotating a key-encryption key feels like it needs a key_version column. It doesn't — the ciphertext already describes itself, so put the version in the blob prefix and rotate with zero migration.
-
You added a second SQL engine. Your text-to-SQL model is still being told it's the first one.
A text-to-SQL planner emits whatever dialect you name it. Add a second engine and the bug is one hardcoded dialect literal the type never forced you to fix — so ClickHouse gets Postgres SQL.
-
You added ClickHouse. Your Postgres SQL validator now rejects valid queries — quietly.
A Postgres-pinned AST validator false-rejects valid ClickHouse grammar as parse_failed — a silent veto of correct SQL. The fix: split the security allowlist from the dialect parser.
-
We published 20 blog posts and never shipped a feed. Nothing could subscribe.
A blog with no RSS feed is a one-way street: feed readers and dev.to/Medium 'import from RSS' both need a feed URL. Without one, every venue re-post is a manual copy-paste that quietly stops.
-
We read the agent-memory benchmarks. Almost none measure analysis.
Agent-memory benchmarks score end-to-end recall of facts on mostly self-reported numbers. Almost nobody measures analysis over memory — the gap we found reading the papers.
-
We shipped 18 SEO pages and got 1 referral. The links only pointed one way.
The page count climbed; referrals stayed flat at ~1/week. Our internal link graph was a tree, not a mesh. The fix was one reciprocal link, derived from a field we already had.
-
Your database scales to zero. Your retry loop doesn't know that.
A scale-to-zero Postgres branch fails the first query while its compute wakes. Instant retries replay the cold connection. The fix: back off the DB stage, not the LLM stages.
-
The timeout that looked like a hallucination
Our NL→SQL benchmark scored a frontier model as junk on 5 hard questions. It never hallucinated — a 5s prod timeout aborted it mid-answer and the handler mislabeled the abort as a parse failure.
-
Your "best model" toggle quietly serves the cheap model. Ship a 409 instead.
When the premium lane isn't available, the tempting branch is to silently serve the default chain. A placebo knob is worse than no knob. The honest contract: pin, upgrade, or fail loud.
-
Your LLM health probe passed. Your agent still starved.
Six straight LLM-agent CI runs failed while our pre-flight probe stayed green. Lessons on gating CI on an LLM provider: probe the real shape, read the body not the status, never trust one model.
-
Your text-to-SQL model isn't as wrong as your benchmark says. The gold SQL is.
We bucketed 238 BIRD-dev losses with a structural differ: 46 differ from gold only by a DISTINCT the model rightly added. Audit gold quality before writing prompt directives, or you overfit to noise.
-
Your LLM fused the two columns you asked for — and the eval marked it wrong
Gold SQL returns first_name, last_name as two columns; the model returns one concatenated full name. Positional-tuple EX scoring can never match them, so a semantically right answer scores as a miss.
-
Your text-to-SQL eval is lying: the gateway returns HTTP 200 with the error in the body
A gateway commits 200 OK before the upstream model fails, so the error rides in the 200 body. A res.ok-only client counts it as a wrong answer, not an outage. res.ok is necessary, not sufficient.
-
Top N per group is the query `LIMIT` can't write
"Top 3 per category" reads like ORDER BY … LIMIT 3, but LIMIT caps the whole result set, not each group. The fix is ROW_NUMBER() OVER (PARTITION BY …) — and the hidden decision is how ties break.
-
Your BI tool got acquired. Your data layer shouldn't have to care.
BI notebooks get rolled up — Mode → ThoughtSpot, Looker → Google, Periscope → Sisense. Fine when it's a destination humans log into; a liability when your product's runtime calls its API.
-
The duplicate-rows query you re-Google every six weeks
Find duplicates hasn't changed in thirty years: GROUP BY the suspect columns, HAVING COUNT(*) > 1. Wanting the whole row, not just the key, quietly changes it to a window function.
-
The text-to-SQL demo takes an afternoon. The other 90% is why you should buy it.
Prompt + model + run the SQL is 10% of an 'ask your data' feature. The fail-closed validator, plan cache, and eval harness are the rest — yours forever. The real question: do you want that stack?
-
Your sitemap is advertising redirects — and your canonical tag points at one
A static host that serves route/index.html 307-redirects the bare path. Our sitemap advertised 27 redirecting URLs and every canonical tag pointed at one. The fix is one path-normalize helper.
-
Your offline LLM eval isn't measuring your model — it's measuring your rate limits
A free-model NL-to-SQL bench scored 17/20, then 6/20 ninety seconds later. The model didn't change — the providers got tired. How to keep availability out of your accuracy number.
-
AI made the internal-tool builder faster. It didn't ask whether you needed the tool.
Low-code AI scaffolds the admin tool in a prompt. But the output is still a destination a human operates — and often the answer belongs inline in your product, or the asker is an agent.
-
Your text-to-SQL accuracy is measured on schemas your users will never build
BIRD and Spider score NL-to-SQL over messy academic schemas. The same free-model chain that scores 0.52 on BIRD scores 0.96 on the schema shapes our users actually build — so we report both.
-
Every data tool shipped an MCP server this year. Your agent still can't build on most of them.
Two shapes of MCP server look identical in a feature matrix: a window into a human's app, or infrastructure the agent owns. The tell is what the agent owns after the call returns.
-
Your agent's memory is a vector store. Ask it "how many" and watch it fall over.
A vector store returns the top-k most similar memories — there is no GROUP BY, COUNT, or JOIN. Recall is similarity; reporting is aggregation. Agent memory needs both machines, not one.
-
You don't need a backend to store form submissions. You need a place to ask "how many."
Storing a signup is a trivial insert — no server needed. The part that wants a database is the reporting: "signups per day," "which referrer converted" — aggregations that want a query planner.
-
NOT IN returned zero rows. It wasn't your data — it was one NULL.
Why WHERE id NOT IN (SELECT …) silently returns nothing when the subquery contains a NULL, and the two anti-join shapes (NOT EXISTS, LEFT JOIN … IS NULL) that never lie to you.
-
Zep gives my agent perfect recall. It still can't answer "average per group" about its own memory.
A temporal knowledge graph is genuinely good at recall — and has no query planner. When the question about agent memory is a GROUP BY, retrieval and aggregation are different machines.
-
The NULL timestamp that broke a TTL sweep and a funnel metric at the same time
A backfill is not a default: one nullable timestamp column made an age-based eviction a silent no-op and pinned a funnel metric at zero — the same NULL, two different failure modes.
Prefer the answers to the war stories?
The solve pages answer one recurring search query each with a working snippet, and the comparisons are honest side-by-sides against adjacent tools.