What I Got Wrong About Letting an AI Agent Write SQL

On October 10 I published a post about giving an AI agent SQL access to a multi-tenant ERP. It ended with a design: a restrictive policy per table, a validator, and a lint for the fan-out trap.

The same day, I accepted a decision that closed that design.

This is the post I owe the first one. Not because the first one was dishonest: it said, in as many words, that the restrictive-policy part was “a hypothesis I have not yet tested”. It is because a reader would still take its conclusion as the plan. It was not. Here is what changed, what I measured, and what I would do differently.

Table of contents

What the first post got right

One idea held up, and it is the one I would keep if I could keep only one: “may this session see this row?” and “does this number mean what it claims?” are two different failures with two different owners. The database owns the first, below the model. Something that knows grain and relationships owns the second. A security control cannot answer the second one.

Everything else in that post survived only in a smaller form, or not at all.

The mistake: I asked “how” before “should”

The first design started from a given: the agent writes SQL over the physical tables. All the effort went into making that safe. Reading the chain back, it is almost embarrassing:

Each step closed one hole and exposed the next. When every fix needs the next fix, the design is answering a question it should have rejected.

I looked at that chain and said what it was: an usine à gaz. I had already said it about the views, and I was right about the views. This time I did not stop at one step. I stopped the whole direction. I wrote a critique, checked what the vendors actually do, and wrote down a different design the same day. That call was mine. It came from the shape of the chain, not from a test.

The test that showed where the problem really was

By then the isolation work was finished and attacked. A script played an injected agent against the role, and every attack failed. That part worked, and it still does.

The decision to stop did not come from a test. The next day I ran the first end-to-end evaluation, this time of the replacement: real questions a small-business owner would ask, answered by the agent, scored against SQL ground truth computed on a test company.

Two answers out of eight were correct. Zero rows from another company.

That result did not change the direction. It showed what the new direction would have to be good at. The isolation gate had passed from day one, and it had told me nothing about whether the answers were right. The part of the system I had engineered was not the part that fails.

What actually moved the score

Not the database layer. Over the following days the score climbed because of work on meaning:

• A dictionary of what every lifecycle state means, written once for the whole schema, so “open” means the same thing for an order, a ticket and a work order.
• Discovery. With a few hundred entities the model has to find the right one, and look-alikes fool it: billing disputes against transport claims, reminders against payment requests. Short notes on the entities that people confuse fixed more answers than any prompt change.
• Named filters, and a rule that one state is filtered on its status. A filter that covers several states quietly answered the wrong question.
• Two bugs in the ledger itself, found only because the evaluation had ground truth. One left reversal lines active after a cancellation. The other counted each account several times when rolling up an account tree. On the test company the profit and loss showed about four times the income that the ledger held. No query was unsafe and no answer errored. The numbers were simply wrong, in a report the product had shown for a long time.

The rollup bug was the classic shape. Summing a tree by joining every account to all of its ancestors counts a leaf once per level, unless each row is pinned to exactly one parent:

-- wrong: a leaf appears once for every ancestor it is joined to
SELECT a.id, SUM(e.amount)
FROM accounts a
JOIN account_ancestors t ON t.ancestor_id = a.id   -- one row per (node, ancestor) pair
JOIN entries e           ON e.account_id = t.node_id
GROUP BY a.id;

-- right: sum each entry once at its own account, then roll up the totals
WITH own AS (
  SELECT account_id, SUM(amount) AS total
  FROM entries
  GROUP BY account_id
)
SELECT t.ancestor_id, SUM(own.total)
FROM account_ancestors t
JOIN own ON own.account_id = t.node_id
GROUP BY t.ancestor_id;

That last one is the argument for the second half of the first post, in its strongest form. The silent failure was not the model’s SQL. It was a number everyone trusted.

What the market actually does

The first post cited vendor practice as support. I checked it against primary documentation only after designing, which is the wrong order. What the documentation says is different from what I assumed:

• The leaders do let an agent write SQL (Microsoft’s ERP agent tools and NetSuite’s query tool are examples).
• They do it over the vendor’s business entities, not over physical tables.
• It is executed by the application, under the signed-in user’s security. None of them gives the agent its own authorization model.

That was the opposite of a separate database path. It also exposed the point I had missed: authorization lives in the service layer. Today my application has no read-side permissions beyond the tenant, so the agent could not leak inside a company. When read rules arrive (payroll, margins), an agent that reads through the application’s own path inherits them. An agent with its own database path would need every rule re-implemented in PostgreSQL.

What I built instead

Concretely, the agent no longer emits a statement. It emits something like this, and nothing else:

{
  "entity": "order",
  "fields": ["customer.name"],
  "filters": [{ "field": "status", "op": "=", "value": "open" }],
  "groupBy": ["customer.name"],
  "aggregates": [{ "metric": "order_total", "fn": "sum" }]
}

The application compiles it. The tenant predicate is added there, by code the model never sees, and the field list is checked against what the entity declares:

function compile(req: EntityRequest, ctx: { tenantId: string }): Query {
  const entity = catalog.get(req.entity); // unknown entity -> rejected
  const fields = req.fields.map(f => entity.resolve(f)); // undeclared field -> rejected

  return select(fields)
    .from(entity.table)
    .where(
      and(
        eq(entity.table.tenantId, ctx.tenantId),
        ...toFilters(req.filters, entity)
      )
    )
    .groupBy(...req.groupBy.map(f => entity.resolve(f)));
}
// executed as the signed-in user, inside a transaction scoped to ctx.tenantId

A request the catalog cannot express fails with a clear error. It never degrades into a looser query.

The fan-out trap from the first post is gone by construction, not by lint: joins are many-to-one only, and amounts that have a governed definition go through a metric, never a sum over records. The hostile-query problem disappears too, because the model never writes a statement. It writes a typed request.

I kept one door open. If real usage measures more than 15% of questions unsupported over 60 days, entity SQL (the agent writes a SELECT against the entities, parsed and rewritten by the application) opens. It is designed in, and it waits for evidence instead of for my enthusiasm.

The numbers, and what they do not prove

The release gate is 99 questions, scored against SQL ground truth, with a bar of 95% on supported questions and zero rows from another company. By model tier:

Three things I take from this:

1. The tenant boundary was never the weak point, in either design.
2. Price per token does not predict the result. A tier about twenty-five times cheaper than the large one passed twice. The previous small tier did not pass reliably. Only the evaluation tells you.
3. The harness lies too. Two of the three failures in one run were bugs in my scorer, not mistakes by the model. A check that only reads the last query penalised an agent that had grouped correctly and then double-checked a zero. A gate you cannot trust to fail correctly is worse than none.

The scorer bug fit in one line:

// before: judged only the final tool call
const grouped = calls.at(-1)?.groupBy.length > 0;

// after: the agent may verify a result with a later, simpler query
const grouped = calls.some(c => c.groupBy.length > 0);

What is still unproven

I would rather say this than have it found:

• One test company, 99 questions. It is a gate, not a forecast. Real users will ask things I did not.
• Two runs on the cheapest tier, one on the large one. That is a pass, not stability. The gate has to be rerun whenever the model changes.
• It is not in production yet. The measurement that decides whether entity SQL ever opens has no real data. It needs about a month of real use.
• Prompt injection is not solved. A structured request and read-only access remove the hostile-statement route and the write route. What the user’s own permissions can read is still reachable by an injected instruction.
• The restrictive-policy idea from the first post was never finished, so I cannot tell you whether it would have worked.

The rules I will use next time

1. Ask “should” before “how.” List the options, including “do not give the agent this capability.” Prefer removing an attack surface to defending it.

2. Measure on real questions before building infrastructure. Ten or twenty questions with ground truth would have shown on day one that the failing layer was meaning.

3. Gate correctness as hard as isolation. An isolation gate that passes says nothing about whether the answers are right.

4. Check the market in primary sources before copying a pattern, and label secondary numbers as such.

5. Treat “this is becoming a factory” as a stop signal. Look for the question being answered wrongly, not for the next hole to plug. Here it was the cheapest check I had: I said the words out loud, a day before any test could have told me.

6. Prove a test can fail. Remove the protection and watch the test go red. Do the same for the scorer.

   For the tenant gate that means dropping the policy on a scratch table and checking that the attack now returns rows:

   ALTER TABLE scratch_orders DISABLE ROW LEVEL SECURITY;
   -- rerun the attack: it must now FAIL, otherwise the test proves nothing

7. Put a price on an experiment before running it. A week of repeated live runs is a bill, and most of mine were repeats nobody needed.

The lesson

“Can the agent write SQL?” was never one question. The first post said so, and it was right. Where I went wrong was in believing that the answer to the security half could carry the design.

It could not. The part I had finished, and attacked, and was proud of, was the part that was not failing. The part that was failing was the part I had not measured yet.