Building RAG Over Your ERP Data: The Architecture That Actually Survives Production

Every few months a client asks the same thing: "can we just chat with our ERP?" The demo is easy. You dump a few hundred invoices into a vector database, wire up an LLM, and by Thursday afternoon somebody in finance is getting fluent, confident, plausible answers. Then they ask "what did we bill the Nakuru branch in Q2?" and the system says KES 4.2 million when the real figure is KES 11.8 million, and the project is dead.

That failure is not a prompt problem. It is an architecture problem, and it is the most common reason ERP RAG projects die. Here is the architecture I use instead, the code that implements it, and the failure modes that will bite you if you skip a layer.

Why ERP data breaks generic RAG

Document RAG assumes a corpus that is textual, append-only and permission-flat. An ERP is none of those things:

• The data is mostly relational. A revenue question is an aggregation over thousands of rows, not a semantic lookup over three chunks.
• It mutates. Invoices get credited, prices change, customers get merged. A pipeline that assumes documents never change serves stale numbers forever.
• Identifiers matter more than meaning. "INV-2026-00873" and "SKU-4410-BLK" are exactly the tokens dense embeddings are worst at. Cosine similarity does not care about a single digit.
• Row-level security is non-negotiable. A branch manager in Kisumu must not retrieve a chunk from Mombasa payroll. Ever.

The architecture, drawn in prose

Picture it left to right in five stages.

Stage one, extraction. A scheduled job, or change-data-capture if your ERP allows triggers, pulls changed rows since the last watermark. Not a full dump: a delta keyed on updated_at plus a tombstone list for deletes.

Stage two, rendering. Each business object becomes one self-contained natural-language "fact card" naming the customer, branch, date, currency, line items, tax and status. This is the step people skip, and it matters most. Raw table rows embed terribly; a rendered sentence embeds well.

Stage three, dual indexing. Every fact card goes into Postgres with a dense vector, a full-text search vector, and metadata carrying tenant_id, branch_id, entity_type, doc_date and a source_pk so you can upsert on change rather than duplicating.

Stage four, the router. This is the part that saves the project. Before anything is retrieved, the model chooses between two planes: a report plane running parameterised SQL against the live schema, and a search plane doing hybrid retrieval over the fact cards. Numeric and aggregate questions go to SQL; narrative questions go to search.

Stage five, rerank and assemble. Hybrid retrieval returns 40 to 60 candidates. A cross-encoder reranker scores each against the full query and you keep the top 6 to 10. Then you build the prompt with explicit citations and a hard instruction that unlisted facts do not exist.

Hybrid retrieval, with reciprocal rank fusion

Dense-only retrieval fails silently on exact terms. Keyword-only retrieval fails on paraphrase. You want both, fused by rank rather than score, because a cosine similarity of 0.81 and a BM25 score of 14.2 are not on the same scale and any weighted blend of them is a fiction. Reciprocal rank fusion sidesteps this: each result earns 1/(k + rank) from each list, with k around 60, and you sum. In one Postgres query:

WITH dense AS (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rnk
  FROM erp_facts
  WHERE tenant_id = $2
    AND branch_id = ANY($3)
    AND doc_date BETWEEN $4 AND $5
  ORDER BY embedding <=> $1
  LIMIT 60
),
sparse AS (
  SELECT id,
         ROW_NUMBER() OVER (
           ORDER BY ts_rank_cd(fts, websearch_to_tsquery('english', $6)) DESC
         ) AS rnk
  FROM erp_facts
  WHERE tenant_id = $2
    AND branch_id = ANY($3)
    AND doc_date BETWEEN $4 AND $5
    AND fts @@ websearch_to_tsquery('english', $6)
  LIMIT 60
)
SELECT f.id, f.source_pk, f.body, f.entity_type,
       COALESCE(1.0 / (60 + d.rnk), 0)
     + COALESCE(1.0 / (60 + s.rnk), 0) AS rrf
FROM erp_facts f
LEFT JOIN dense  d ON d.id = f.id
LEFT JOIN sparse s ON s.id = f.id
WHERE d.id IS NOT NULL OR s.id IS NOT NULL
ORDER BY rrf DESC
LIMIT 40;

Two details that are easy to get wrong. First, <=> is pgvector's cosine distance operator, so smaller is better and you sort ascending. Second, the tenant predicate must live inside both branches. Retrieve first and filter afterwards and you will not leak data, but you will silently return three results where the user should have had thirty, because the top 60 was consumed by another tenant's rows - a bug nobody catches, because the answer still looks reasonable.

Chunking that does not destroy context

Fixed-size chunking is the root cause of most retrieval failures. A 512-token window slices a table away from its header and a line item away from its invoice number. For ERP data, do not chunk business objects at all: render each one whole. Only chunk genuinely long free text - contracts, correspondence, scanned PDFs - and attach a parent pointer so retrieval matches the chunk but generation sees the document.

from dataclasses import dataclass

@dataclass
class Fact:
    source_pk: str
    entity_type: str
    tenant_id: str
    branch_id: str
    doc_date: str
    body: str

def render_invoice(inv, lines) -> Fact:
    items = "; ".join(
        f"{l['qty']} x {l['description']} (SKU {l['sku']}) at "
        f"{inv['currency']} {l['unit_price']:,.2f}"
        for l in lines
    )
    outstanding = inv["total"] - inv["paid"]
    body = (
        f"Invoice {inv['number']} issued {inv['issue_date']} by "
        f"{inv['branch_name']} branch to customer {inv['customer_name']} "
        f"(account {inv['customer_code']}). Status: {inv['status']}. "
        f"Due {inv['due_date']}. Line items: {items}. "
        f"Total {inv['currency']} {inv['total']:,.2f} including VAT "
        f"{inv['tax']:,.2f}. Settled {inv['paid']:,.2f}, "
        f"outstanding {outstanding:,.2f}."
    )
    return Fact(
        source_pk=f"invoice:{inv['id']}",
        entity_type="invoice",
        tenant_id=inv["tenant_id"],
        branch_id=inv["branch_id"],
        doc_date=inv["issue_date"],
        body=body,
    )

Notice two things. The renderer repeats customer, branch and currency inside every card, because redundancy is cheap in storage and expensive to omit at retrieval time. And it precomputes outstanding in Python, because a retrieval system should never ask a language model to do arithmetic a subtraction could have done.

The router, and why aggregates must never be retrieved

The highest-value guardrail in an ERP assistant is this: if the answer is a number derived from more than a handful of rows, it comes from SQL, not from retrieval. Top-k retrieval is a lossy sample, and summing a sample then presenting it as a total is how you get 4.2 million instead of 11.8. Implement it with tool calling rather than a classifier prompt:

import anthropic, json

client = anthropic.Anthropic()

TOOLS = [
    {
        "name": "run_report",
        "description": (
            "Run a pre-approved analytical report against the live ERP database. "
            "Use this for ANY question involving totals, counts, averages, ageing, "
            "trends or period comparisons. Never estimate these from search results."
        ),
        "strict": True,
        "input_schema": {
            "type": "object",
            "properties": {
                "report": {
                    "type": "string",
                    "enum": ["revenue_by_period", "ar_ageing",
                             "top_customers", "stock_on_hand"],
                },
                "date_from": {"type": "string", "format": "date"},
                "date_to": {"type": "string", "format": "date"},
                "branch_code": {"type": ["string", "null"]},
            },
            "required": ["report", "date_from", "date_to", "branch_code"],
            "additionalProperties": False,
        },
    },
    {
        "name": "search_documents",
        "description": (
            "Hybrid keyword plus semantic search over invoices, contracts and "
            "correspondence. Use for narrative or find-the-record questions, "
            "never for computing totals."
        ),
        "strict": True,
        "input_schema": {
            "type": "object",
            "properties": {"query": {"type": "string"}},
            "required": ["query"],
            "additionalProperties": False,
        },
    },
]

SYSTEM = (
    "Every figure you state must come from a tool result. If the tools return "
    "nothing relevant, say so plainly. Cite the source_pk of each document used."
)

def answer(question: str, principal: dict) -> str:
    messages = [{"role": "user", "content": question}]
    while True:
        resp = client.messages.create(
            model="claude-sonnet-5",
            max_tokens=4096,
            thinking={"type": "adaptive"},
            system=SYSTEM,
            tools=TOOLS,
            messages=messages,
        )
        if resp.stop_reason != "tool_use":
            return "".join(b.text for b in resp.content if b.type == "text")

        messages.append({"role": "assistant", "content": resp.content})
        results = []
        for block in resp.content:
            if block.type != "tool_use":
                continue
            if block.name == "run_report":
                out = run_named_report(principal, **block.input)
            else:
                out = hybrid_search(principal, block.input["query"])
            results.append({
                "type": "tool_result",
                "tool_use_id": block.id,
                "content": json.dumps(out, default=str),
            })
        messages.append({"role": "user", "content": results})

run_named_report takes an enum, not SQL. The model never writes a query. Each report is a hand-written parameterised statement that injects the caller's tenant and branch scope from principal, which comes from your session, not the model. Text-to-SQL is a lovely demo and a dangerous production surface: an arbitrary-query tool wearing a friendly hat, and exactly what an attacker reaches for once they can influence any text the model reads.

strict: True guarantees the tool input validates against the schema, so run_named_report never receives a report name you did not implement.

Reranking is the cheapest accuracy you will ever buy

Retrieve 40, rerank, keep 8. A cross-encoder scores query and passage together with full attention, unlike the bi-encoder that embedded them independently, and it reliably pulls the genuinely relevant chunk from rank 17 up to rank 2. You can run an open-weight reranker such as bge-reranker-v2 on a modest GPU, or call a managed one. Either way it adds tens of milliseconds, removes a whole category of "it retrieved the wrong branch's invoice" bugs, and lets you use a smaller top-k at generation time - fewer, better chunks beat more, noisier chunks on both accuracy and bill.

Failure modes to test for before you demo

• Aggregation by retrieval. Ask for a total spanning more rows than your top-k. Correct behaviour is a SQL call, not a sum of chunks.
• Exact identifier lookup. Ask for "INV-2026-00873". Dense-only retrieval will miss it; your keyword leg should return it at rank 1.
• Cross-tenant probe. Sign in as a Kisumu user, ask about Mombasa. The correct answer is "no records found", not a refusal and definitely not data.
• Stale record. Credit an invoice, wait for the next sync window, ask about it. An append-only index will return the pre-credit figure forever.
• Out of scope. "What is our headcount?" when HR is not indexed. The system must say it does not know.

Write those five as an eval set on day one, not day ninety. Ten cases per category, run on every prompt change, gate the deploy on them. Retrieval quality and generation quality are separate metrics, so log recall at k for the retriever alongside groundedness for the generator. When an answer is wrong you want to know within seconds which half broke.

The practical shape of it

For most mid-sized businesses running Odoo, Sage, Tally or a bespoke MySQL schema, this whole thing fits on one Postgres instance with pgvector, a delta sync job, a small reranker container, and API calls to a hosted model for generation. No vector database vendor required.

The hard parts were never the embeddings. They are the delta sync, the permission model, and the discipline to route numbers to SQL. Get those three right and finance will trust the system. Get any one wrong and you will spend six months explaining why the chatbot said four point two.