Why we did not point an LLM at ERPNext: building a semantic layer for a business agent

There is a demo that has been doing the rounds for two years now. Someone types "show me last month's revenue" into a chat box, a model writes some SQL, a chart renders, and the room is satisfied. I have given a version of that demo myself.

What the demo never includes is somebody checking the number against the books.

I have now spent a long time checking those numbers against real ERPNext installations, and the failure rate is high enough that it shaped an entire product decision. Not because the models are bad at SQL. They are increasingly good at SQL. The problem is that being good at SQL and knowing what a business means are unrelated skills, and only one of them can be inferred from a schema.

The schema is the trap

ERPNext is a serious system and its schema reflects double-entry bookkeeping rather than English. That is correct engineering. It is also precisely why an agent that reads table names confidently gets things wrong.

Ask for "revenue last month" and a model will find tabSales Invoice, sum grand_total, and give you a figure. Consider what that figure quietly does not know:

• Credit notes. In ERPNext a return is a Sales Invoice with is_return = 1 and a negative total. Whether your sum includes them depends entirely on whether the model thought to filter, and there is no reason it would.
• Draft documents. docstatus = 0 rows are not real. They are sitting in the same table looking exactly like real ones.
• Where revenue actually lives. The reconciled, reportable figure is the credit side of your income accounts in tabGL Entry, which is also where journal adjustments land. The invoice table does not know about those.

Stock is worse. The quantity on an Item is a cached figure; the truth is the running balance in tabStock Ledger Entry, and valuation depends on the valuation method configured per item. And gross margin does not exist as a stored value at all - you reconstruct it from COGS, which means knowing how your cost postings are configured.

None of this is exotic. It is just invisible from outside, and a model that has never seen a particular installation cannot infer it. So it does the only thing available to it: it guesses, fluently, in well-formatted SQL.

Why a quiet failure is the expensive one

I want to be precise about the risk, because "the AI is sometimes wrong" understates it.

Software that fails loudly costs time. An exception, a 500, a blank chart - irritating, contained, and honest. You know immediately that you cannot rely on the output.

Software that fails quietly costs decisions. The chart renders. The number is formatted like every correct number the tool has ever produced. There is no signal at all. The shop owner prices against it, reorders against it, and finds out at year end when the audited figures disagree with a quarter of their choices.

This is why an accuracy rate is not a reassuring statistic here. If you cannot tell which answers fall in the wrong minority, ninety percent accuracy is not a tool, it is a trap with good manners.

The catalog

So Rai - the analyst agent we are building for retail - is not permitted to invent arithmetic. It reasons over a canonical business model (sales, items, inventory, purchases, customers, suppliers, payments, expenses) and a curated metric catalog that maps each business concept to a governed query somebody has checked.

The v1 catalog for electronics, hardware and spare parts:

• Revenue and gross margin, by SKU and category, by day/week/month
• Stock turnover and days of stock
• Dead and slow stock, with capital tied up
• Top and bottom movers
• Supplier price variance for the same SKU across suppliers
• Receivables aging
• Till and cash position
• Serial-level lookup and history - source supplier, invoice, warranty window, return status

A metric is a small, boring artefact. Roughly:

- name: gross_margin_by_category
  description: Revenue minus cost of goods sold, grouped by item group.
  grain: [company, item_group, period]
  params:
    period: {type: date_range, required: true}
    item_group: {type: string, required: false}
  sql: templates/gross_margin_by_category.sql
  notes: |
    Revenue from GL Entry against income accounts (excludes drafts and
    cancelled). COGS from the stock ledger valuation at posting time, not
    from current item valuation.

The agent's job is now to map "am I making money on power tools?" to gross_margin_by_category with item_group = Power Tools. That is a classification problem over a small, closed set. It is dramatically easier than SQL generation, and dramatically easier to test.

Catalog-first, as a rule the code enforces

A catalog only helps if using it is mandatory. In the agent loop this is not a suggestion in a prompt; it is the tool surface. The model gets a find_metric tool and a run_metric tool. It does not get a run_sql tool unless find_metric has already returned nothing and the model has produced an explicit statement of why no catalog metric applies.

That gate matters because prompt instructions decay. A model under pressure to be helpful will route around an instruction it can route around. It cannot route around a tool that is not in its schema yet.

The fallback, and the cost of admitting it

Genuinely unmodelled questions exist, and refusing them all would make the product useless. So there is a fallback, fenced on four sides:

• SELECT only. Enforced at the connection, on a role that has no write grants at all, not by inspecting the generated string.
• Forced LIMIT injected into the statement regardless of what the model wrote.
• Statement timeout so a bad join cannot pin the shop's database on a Saturday.
• Company scope applied server-side. Multi-tenant leakage through a generated query is the failure that ends a company, not a feature.

And when the fallback runs, the answer carries a collapsible "how I got this" panel: the query, and the assumptions it made. This is an explicit admission that a particular answer is less certain than the others, and it costs us something in polish.

I think it is the correct trade by a wide margin. The alternative is an interface where every answer projects identical confidence and some of them have not earned it. Once a shop owner catches one confident wrong number, the product is finished - correctly, and permanently.

What the catalog is really worth

There is a side effect worth naming, because it turned out to be more valuable than expected.

Writing a metric catalog forces somebody to write down what the business actually means by its own words. Is revenue invoiced or collected? Does margin include freight? Is a branch transfer a sale? Which adjustments are excluded?

Most organisations have never written those definitions down. They live in the head of whoever prepares the monthly figures and quietly change when that person leaves - which is why two people in the same company can produce two different revenue numbers and both be honest.

So the catalog is not really an AI artefact. It is the semantics of the business, made explicit and version-controlled. The agent is just the first consumer that could not proceed without it.