Two agents, one failure mode

A planner needs audience data for a brief due Thursday: age, income, household size, what language gets spoken at home in a catchment. The ABS has all of it, free, at a detail nothing commercial matches. So the request goes to whoever owns data, and comes back in three days, or comes back asking which geography they meant. By then the brief is written and what went in was a guess.

I've watched a version of that for twenty years. The data is public, it's excellent, and almost nobody can use it, because using it means knowing that "suburb" isn't a thing the ABS measures, that the closest it has is called an SA2, and that the table you want is G02 rather than G01.

Good problem to point an agent at, and a good teacher too, because there are five steps between a plain-English question and a trustworthy answer and every one fails differently.

Failure mode per step

What the agent does with a question

Distilquestion → intent

Resolves the wrong place, or the wrong grain.

↓→

Runintent → SQL

Valid SQL against the wrong column.

↓→

RetrieveSQL → rows

Returns rows. They're about something else.

↓→

Understandrows → meaning

Residual buckets counted as real categories.

↓→

Presentmeaning → answer

A headline that contradicts its own table.

There's a second reason. At Alley Group we're working out what serious multi-source analytics agents look like for an agency, and I'd rather make those mistakes on my own time, on a system with no clients attached. So I picked the hardest single-source version I could find and built it properly. Plenty of people are doing this and several are doing it better; the point was to understand it, not to win.

What it is

It's at absai.com.au. You ask a question in plain English and you get back a number, a chart, the exact geography it used, and the database query it wrote to get there. That last part matters more than it sounds, and most of this post is about why.

Underneath it is all five 2021 Census profiles. Not the summary tables most people see, but the DataPacks — the ABS's own bulk download, free to anyone, and where the real detail lives.

22 GB

of Census data

409

tables

~17k

columns embedded

265

commits

Seventeen thousand columns decides everything else. The usual approach is to hand the model a description of the whole database and let it pick, and at that size the description doesn't fit in a single request. So it's off the table before you start.

Instead there's a shortlist built in two passes: a cheap keyword match narrows four hundred tables to a handful, then a second pass compares the meaning of the question against a description of every column and keeps the thirty most likely. Only those reach the model. Above all of it sits a hand-written file naming, for the measures people ask about constantly, exactly which column is right — and that file matters more than anything else here. The orchestration is deliberately boring, and the step deciding what to run is plain Python rather than another model.

VercelGlobal edge

webNext.js 16 front end

• /c/[id] — chat
• /answers — public library
• /census-2026

serverlessRoute handlers

• /api/chat — JWT-verified, NDJSON
• /api/usage · /api/health

service-account token · streamQuery▼

Google Cloudaustralia-southeast2 · Melbourne

managed runtimeVertex AI Agent Engine

• root agent — routing + prose
• deterministic Python orchestrator
• text-to-SQL → verifier → answer contract

model apiGemini

• 3.5-flash — routing + SQL
• 3.5-flash-lite — helpers

psycopg · read-only · 20s statement cap▼

Supabaseap-northeast-1 · Tokyo

databasePostgres 17 + pgvector

• census_2021_{gcp,pep,tsp,ip,wpp} — 409 tables
• metadata — ~17k embedded columns
• public — sessions, messages, rate limits

identitySupabase Auth

• Google · email · magic link

Three regions across three clouds, and that geography is a mistake I made without noticing. The full architecture is in the case study; this is the other half, what it cost me to find out.

I wrote almost none of it

Claude Code wrote effectively all of this. I should say so up front, because the rest of the post doesn't make sense otherwise — and it needs complicating straight away, because "AI wrote it" and "it wrote itself" are not the same claim.

The agent

• Wrote effectively all of the code, and the tests
• Held hundreds of table schemas in working memory
• Refactored across files without being asked twice
• Ran the benchmark and reported the number

Me

• Decided to delete an LLM from the hot path
• Asked 'is that fix hardcoded, or is it general?'
• Noticed it had answered a different question
• Chose what not to build

The left column is most of the labour and I won't be precious about that. The right column is four judgements, and each changed the outcome more than any code I could have typed. Take the first: the original design wrapped the routing step in a second model, an agent calling an agent, the way most framework examples do it. I ran the same question five times and got three different answers. Deleting that layer and passing the user's message through verbatim took the same probe to five out of five. The fix was removing a model, and no amount of prompting the model would have found it.

The failure both agents share

It took two incidents in unrelated parts of the system before I worked out they were the same incident. Three conditions have to hold for an answer to be right: the query runs, it comes back with rows, and the rows are about the question that was put. Only the third is hard, and only the third fails without saying anything.

• RunsNo syntax error, no exception, nothing in the logs.
• Returns rowsNot empty, so no emptiness guard trips.
• Answers itThe rows are about what was actually asked.

Two examples, from opposite ends of the stack.

The agent writing my Python needed Australia's capital cities, which in ABS terms means every area named "Greater" something. It wrote LIKE 'Greater %' into the query string, the driver read % as a parameter placeholder, and the query raised — where a soft-fail wrapper caught it, returned nothing and moved on. The whole feature did nothing for as long as it took me to notice, tests green throughout.

The agent writing my SQL was asked what share of households pay over $3,000 a month on their mortgage. The total in that table spans eleven repayment bands; it summed six, every second one as it happens, giving 1,089 over 1,631 instead of 1,089 over 2,673.

It answered 66.8%. The correct answer is 40.7%.

That query ran, returned rows, and produced a number any reasonable person would have dropped straight into a report. The benchmark marked it as a pass.

The agent writing the code

A percent sign in a LIKE, in a driver that reads it as a placeholder.

• The query raised
• A soft-fail swallowed it
• The feature silently did nothing
• The tests stayed green

The agent writing the SQL

A share built from a denominator that was only part of the whole.

• The query ran
• It returned rows
• Every result came back inflated
• Nothing reported an error

There's a third, and it's the purest of them. I'd built a planner that breaks a hard question into sub-queries, and its executor read the results out of a field called sql_results while the tool it called returned them under query_result. Every sub-query soft-failed at zero rows, so the entire path had never once executed in any run I'd ever done.

Twenty-two tests covered it. All passed, because a test like this doesn't talk to the real database — it talks to a stand-in you also write, and mine returned the same wrong field name the code was reading.

The twenty-two unit tests passed because their mocks agreed with the bugs.

A test written against the same misunderstanding that produced the code doesn't test anything; it writes the misunderstanding down a second time and then reports, truthfully, that both copies match. I had been reading that agreement as evidence that the thing worked.

The layer that fixed the most

Matching on meaning gets you to the point where the thing demos well and still can't be trusted. It's good at finding the right neighbourhood among seventeen thousand columns, and useless at picking between two columns that both describe the concept perfectly well, because to a similarity score they are the same answer.

• Matched by meaningCovers all ~17k columns. Cheap, general, approximate.
• Written down by hand55 measures, each one checked. Exact, and doesn't scale.

Two cases, both of which shipped wrong. Ask for Australia's total population and retrieval would sometimes reach for the Country of Birth table and take its grand total — a total population, but not the one the ABS publishes as the total population. The second is the one no model will reason its way to: in the cross-year tables the column for people aged 85 and over is age_grp_85over_C{year}_P, while in the general profile the identical concept is age_85ov_*. Nothing in the question tells you which convention applies and nothing in the column descriptions does either. No amount of reasoning gets you there, because it's a fact about a file, and a fact about a file can only live where a person wrote it down.

Holding everything else constant, adding that file moved the benchmark from 17 of 26 to 20 of 26. The single most load-bearing part of the whole system is a plain text file edited by hand.

There's a sting in it. My own design note says to move to a real semantic layer "if the measure count grows past ~50, or if non-ABS datasets are added". The file holds 55, and multi-source is the next thing I want to build.

It has a sharp edge too. The phrases I list against each measure do two jobs at once: steer the model toward the right column, and authorise a check to reject any answer that ignored it. Those powers shouldn't always travel together. "Biggest population" steers well, so I added it — then someone asked which suburb has the biggest population of Greeks, and the check rejected the Ancestry table, which was the correct one.

Verification beats prompting, and I have the receipts

The instinct when an agent gets something wrong is to tell it not to do that again, and I spent a fair while acting on that instinct before admitting it wasn't working. I have three reverted commits on the subject.

The planner is the instructive one. Not a bad idea — just one that measured out at zero benefit and real latency cost, and I only know that because I built the A/B harness before the feature. The one change that worked, worked because it was additive: the contract between the agent and the front end writes into session state and never touches the prompt the model reads.

So the fixes that hold don't ask the model to be better. The result checker runs after the query and applies fixed rules — did a "top 10" come back with one row, are residual buckets like "No usual address" polluting a ranking, is this percentage 18,253%, is a "median" question answered with a column that isn't one. Every number in the headline is separately checked against the rows that came back and dropped if absent, which kills the class where the title contradicts its own table.

Replayed over a clean benchmark run, that checker intervenes on about 8% of turns and costs 0.2 milliseconds. It isn't clever or expensive. It just sits there being right about a small number of things, and not one of its checks asks the model whether it did a good job, which is the only reason any of them are worth running.

Every check I trusted was lying to me

The evaluation machinery was the part I was most confident about, and presumably that confidence is why it took a deliberate audit to find out what it was doing. Four layers, and they do not deserve equal billing.

1. Unit and smoke testsRunning

   812 of them, on every push.

2. Benchmark — 38 questions, answers worked out by hand in advanceRunning

   The number I quote when someone asks how good it is.

   Four of the 38 have no stored answer, so they cannot fail.

3. Factuality marking against the ABS's own published figuresBuilt, switched off

   Runs weekly against the live agent.

   Exactly one green run in its entire history.

4. Self-improvement loop — diagnose, propose, validateScaffolded

   Reads its own failures and suggests fixes.

   Six suggestions ever, none merged. The judge that rules on them is never called.

• Running
• Built, switched off
• Scaffolded

Six things went wrong across those layers, in ascending order of how much they embarrassed me.

The pass rate was computed over a question set that had quietly shrunk. One check marks the live agent against the ABS's published figures and reported 89% — eight right out of nine, from a set of twenty. Eight of the missing eleven came back as "which did you mean?" rather than an answer and three couldn't be scored. It wasn't lying about the 89%. It never mentioned it had marked less than half the exam.

Two benchmark questions were marking nothing at all. Each question stores the correct answer alongside it and the checker compares the two — except it returned "pass" whenever that stored answer was blank, and two of my thirty-eight were blank. They'd been passing for months regardless of what the agent said.

So I fixed it and wrote a linter so it could never happen again. The linter walks the list of stored answers checking each is real — and four other questions aren't on that list at all, so it walks straight past them, and the checker treats a question it can't find as a pass. They are passing today. The tool I built to stop checks lying to me had the same shape of lie in it.

Four tables were only partly loaded, which is why those answers were blank. Two held one national row out of an expected three and a half thousand. The other two should worry you more: 0.04% and 0.5% below their sibling tables' row counts, invisible to any threshold you'd dare set. Ask which state has the largest workforce and you hit a table with no state rows.

The health check was pointed at my own laptop, so it reported a problem permanently while being incapable of noticing a real one, and on a dashboard those look identical. The nightly test run had failed fifty-three nights running, emailing me each time, which is precisely why I'd stopped reading the emails.

The self-improvement loop was marking its own homework. This is the one I'd have defended hardest. Its only working proposal step lifts new phrases out of the failing question's own wording, then tests the fix by re-running the set that question was in. Then I counted what it had produced: six proposals in its entire life, all from one afternoon in May, every one routed to a proposer that isn't finished and so carrying no patch. Nothing it suggested has ever been merged, and at least three of the six diagnoses were simply wrong, each stated at a confidence of 0.80. The proposals were never the bottleneck. The step deciding what had gone wrong was.

A check that passes when it has nothing to check isn't a check.

Which is the same sentence as the one about the tests and their stand-ins, from the other end. Green turns out to mean something much narrower than I was treating it as: this check did not fire. Whether it was in a position to fire is a separate question, and nothing on the dashboard answers it. Three of that dashboard's own numbers were wrong too. My favourite is the token counter, which tracks what a question costs to answer: it works, produces plausible numbers, and is wrong by more than a factor of ten, because it was wired to the model's synchronous call path while the agent runs on the asynchronous one.

Answers you can't fudge

If the checks lie, what can you trust? Four things held up.

Work it out twice, separately. For every benchmark question I wrote the query by hand, and the answer is also checked against the ABS's published QuickStats page — the page a sceptical user opens in the next tab to catch me out. Two workings that agree beat one argued for well.

Sample, don't argue. I fixed a class of geography bugs and claimed it was general. The obvious challenge back: general, or hardcoded to pass? The only honest answer was to pull seventy-five geographies the system had never seen and measure.

61/75

first attempt

which I'd have called general

73/75

after rebuilding it properly

Without that measurement I'd have shipped the first version believing it solved, and it would have gone on failing for one geography in five.

Turn it around and work backwards. The expensive part of a test set is knowing the right answer in advance, and hand-written answers are exactly where mistakes hide — the paper I quote below found error rates above 50% in the answers attached to the field's most-cited test sets. So don't write them.

The normal way

Someone writes the answer down in advance, then defends it.

Questiona person writes one

↓→

Querythe agent writes it

↓→

Checkagainst the stored answer

Backwards

The answer is right because you started from it.

Factone you already verified

↓→

Queryno model involved

↓→

Questionwritten from the fact

Three conditions come attached. The question must be built by fixed rules with no model involved, or the answer quietly stops matching it. The live system has to be able to route it, or you're testing something you don't ship. And the test is binned if a neighbouring column holds the same value, because then a correct score can't tell skill from luck. Those conditions throw away about a third of everything the generator makes (35%, 32% and 36% across three runs), and the middle one does nearly all the killing. The rejects proved more interesting than the survivors: each is a question built from a measure I verified by hand which the live system then routed somewhere else. A fairness check found a genuine routing inconsistency while looking for something entirely different.

Make cheating impossible rather than merely discouraged. This is the design I am most pleased with and the one I have to describe most carefully, because part of it isn't switched on. What is wired: the loop groups failures by what actually broke rather than how the question was worded, insists on three different questions with the same fault before proposing anything, and splits that group so the proposal step sees only half. What isn't: the function that rules on whether a fix genuinely generalised or merely memorised its own example. That code exists, it has tests, and nothing calls it — it needs fresh generated questions to judge against, and while I built the generator I never built the part that runs what it generates through the agent. The gate sits there, correct and unreachable.

I could have left that out and nobody would have known. It's a better illustration of the argument than the version where it works.

The decision the agent made that I didn't

The database is in Tokyo.

I did not decide that. Three places in my own documentation said Sydney and had for months; the provider's API says ap-northeast-1. Somewhere in a setup step an agent picked a region, it was never wrong enough to notice, and it got written down wrong on top.

Nothing here is a bug. Nothing errored, nothing failed a test, and the region is perfectly defensible. It was a reasonable decision made by something with no particular reason to know I'm in Australia, and the bill arrives months later as every answer taking longer than it should. Every query is a Melbourne-to-Tokyo round trip, and a turn issues fifteen to thirty of them.

4

trips to Tokyo

to run one read-only query

427 ms

per query, before

119 ms

after

three trips removed

~6 s

off a typical turn

Reading one row out of that database used to take four separate trips across the Pacific. A liveness probe on picking up the connection, an instruction to open a transaction, the query itself, and an instruction to close the transaction again — and the middle two are there because the database library wraps every read in a transaction whether you asked for one or not. Three of the four were removable. At roughly 107 milliseconds a trip that is the whole of the 427, and taking it to one trip is the whole of the 119. The test suite got two and a half times faster as a side effect.

What's left is distance, and distance doesn't optimise.

Both removals disarmed a safety net that had to be re-armed elsewhere, and both would fail silently if a future me tidied them away.

Know your league

When I say the benchmark passes, I want to be precise about what that's worth.

That last row needs its caveats stapled to it. Thirty-eight questions, one dataset, a benchmark I wrote myself and grade myself — and four of the thirty-eight have no stored answer to check against, so they cannot fail on a wrong number. The honest way to write the row is 34 of 38 checked, 38 of 38 passed, and the gap between those two statements is the subject of this post.

The table is calibration, not a score. When a vendor says their text-to-SQL product runs at 90%, the useful questions are which questions, who wrote the answers, and how many candidates were generated before one was picked. The published leaderboards are worse than optimistic: a CIDR 2026 paper found annotation error rates of 52.8% in BIRD Mini-Dev and 62.8% in Spider 2.0-Snow, and once a corrected subset was re-ranked the correlation with the uncorrected ordering fell from 0.85 to 0.32, with individual systems moving as many as nine places, which makes the ordering everyone optimises against close to noise.

One more caveat, one I nearly published without. That benchmark runs the agent inside the test process on my own machine, so its timings are not what a user gets: over the same window it reported a median of thirteen seconds and production measured thirty-four. If you take one habit from this post, make it asking what a number was measured against. I built the thing and still nearly quoted a local figure as though it were the product.

Nine things I'd do differently

The list I wish I'd had. The first four are about building it; the last five are about being able to trust it, and those are the ones I got wrong.

1. Put the fixed material at the front of the prompt. Schema, definitions, dialect rules are identical on every request, so they belong ahead of anything that varies, where they give you a stable cacheable prefix.
2. Widen the table shortlist rather than narrowing it. An extra irrelevant table costs almost nothing. A missing one costs you the answer.
3. Ground values against the real strings in your database. I maintained a hand-written whitelist of name suffixes instead, and wouldn't again.
4. Keep resolved context as structured fields in session state — the geography, the dataset, the measure — not buried in the query text of the last three turns.
5. Build the "can this complete one real user journey" check on day one. I didn't have one until after launch, and the health check I did have was pointed at my own laptop, so for months nothing was watching the thing users touch.
6. Never let a scheduled job report failure by email. You will filter it within a fortnight, and then it is worse than having no check at all.
7. Pin exact versions of anything that gets serialised. A loose dependency range let my deployment install a new major version against an object built by an older one. Every request died on arrival and the health probe stayed green throughout.
8. Write the generalisation test in the same commit as the fix, while you can still remember what you were tempted to hardcode.
9. Ask every check what it does when handed nothing. All six failures above answer that with "passes". Cheapest audit available, and I had never once run it.

The rest of what I'd change — which of the fashionable pipelines to skip, and the numbers for why — is a post of its own, and it's the one I'll write next.

What I'm fixing next

Writing this made the order of work obvious, which I wasn't expecting.

1. 1 · Make the benchmark unable to give free passesRunning

   Four questions still can't fail, and the linter guarding that walks past them.

   Smallest fix here, and the one this post is most embarrassed by.

2. 2 · Close the loop the generator half-builtBuilt, switched off

   Run generated questions through the agent, grade them, feed the judge that already exists.

   Turns a dead function into the only test that can't be memorised.

3. 3 · Fix the diagnoser before touching the proposersScaffolded

   At least three of six diagnoses were wrong at 0.80 confidence.

   No proposal step is worth building on top of a bad reading of the failure.

4. 4 · Read the twenty thumbs-down commentsScaffolded

   The best signal in the system, sitting in a queue with no reader.

5. 5 · Move the database to SydneyDesign only

   ~110 ms a query of pure distance, twenty-odd queries a turn.

   Pure latency, no cleverness required.

Four of those five are about the measuring apparatus rather than the thing being measured, which is probably the most useful thing I can hand anyone starting one of these.

One dataset, then many

Most of this transfers to the agency version: dumb predictable plumbing around the model, a fixed contract, a check after the query runs, and a written-down place for domain knowledge instead of prompts nobody can audit. What doesn't transfer is how that file is built — add a second source and it stops being something one person maintains.

The harder question is whether any of it is worth doing. General models answer the easy Census questions perfectly well now, so ask one for Australia's population and you don't need me. What's left worth holding is the multi-table multi-Census join that has to be exactly right, and this system is close without being all the way there.

Census night is Tuesday 11 August, and the first 2026 data lands in June 2027, so the 2021 Census stays the most complete picture of Australia we have for another year. The answer library is public.

We are all about to hand a great deal of work to systems that produce confident, plausible output faster than anyone can review it. Mine produced it in two languages, in two layers of the same application, and sailed through every test I had written to catch exactly that. What caught it in the end was a handful of cheap, mechanical checks I could run without asking the thing that did the work whether the work was any good — the one question it was never going to answer.