I'm building a chatbot inside the ERP to help on the production floor with simple questions: break down a customer's order, say how much they owe. The plan is to get the assistant in and then keep tuning it for better answers. I'd just come off a week of reading up on how to use pgvector for semantic search over the ERP's descriptions, so I started where I'd been looking: embed the tables, search by similarity, let the model write the answer.
Today I realized that for half of those questions this is wrong from the start. It isn't a bug I hit while running the system: it's a design mistake I caught before putting it into production, and thankfully before rather than after.
What an embedding is, in one line
An embedding turns a piece of text into a list of numbers —a vector— such that two texts meaning similar things end up close together in that space. That's all it does. Searching by embedding means asking "which records look like this one" and pulling back the k nearest. In pgvector that comes down to ORDER BY embedding <=> query_vector LIMIT k.
Written out like that, the problem is obvious: it retrieves by resemblance. It can't add, it can't count, and it has no idea whether what it brought back is complete.
Why this blows up specifically in an ERP
If I ask how much a customer owes, the vector search hands me the k most similar records. If that customer has 23 invoices and k is 5, the model adds up 5 and reports a total. No exception, nothing in the logs, nothing at all: just a wrong number, written with the same confidence as a right one.
For the same reason, several other things don't work either:
- Exact identifiers are the blind spot. Order 4821 and order 4812 are almost the same vector, because changing one digit barely moves the representation. It will hand you the neighbor's order without blinking.
- The ERP changes constantly. A balance embedded on Tuesday is wrong on Wednesday. The snapshot goes stale and nobody notices.
- "Customer Pérez" isn't a customer. If there are two of them, similarity search picks one and moves on.
What bothered me most is that it's the same problem I'd already written about with the research system: a number with no source sounds just as convincing when it's right as when it's made up. I had it pinned down in one project and I was repeating it in another, one layer further down.
How I'm fixing it
Split the questions into two classes and route them, instead of pushing everything through the same pipe.
- Exact or numeric —balances, order details, stock, due dates— don't go through embeddings. They go through SQL functions I write myself:
get_saldo_cliente(cliente_id),get_detalle_pedido(pedido_id). The model doesn't write the query and doesn't do the arithmetic: it picks which function to call and fills in the parameters. The SQL returns the total already summed and the model just phrases it. Read-only database user, no free-form SQL against production. - Descriptive —how the machine was set up for a given material, which products resemble this one, process documentation— that's where embeddings and pgvector belong, because that's what they're good at.
- In between, resolve the entity first. "Customer Pérez" becomes a
cliente_idbefore any arithmetic happens, using trigram search over the customer table, and if there are two, the assistant asks which one instead of guessing.
On top of that I'm going to make it show its sources —order numbers, invoice ids— so whoever uses it on the floor can check. It's the same idea as the reliability index I'd already built into the research system.
That last part is designed, not finished. I'm building the functions now and I haven't tested them against real balances yet.
What I'd tell someone else
Before embedding anything, split the questions people will ask you into two lists: the ones answered by a resemblance and the ones answered by a calculation. The second list isn't RAG, it's SQL, and if you push it through the vector you'll get answers that read beautifully and are wrong. Embeddings are great for "show me something like this" and they're the wrong tool for "how much". And the test that counts is comparing the assistant's answer against a SUM from the database: if they don't match, it doesn't matter how well written it is.