AI Agent for webERP - POC

[iangrech]

I have created an AI Agent for webERP as a proof of concept. You can find it here.

The application is developed in Python and uses a Flask server. Note that the application interfaces directly with the webERP database and does not include a login page.

Edit: Login page added.

[timschofield]

Hi Ian, this looks fascinating stuff. Any chance this could be brought behind a webERP login for security puposes?

Tim

[iangrech]

Hi Tim. Added a login page using the xml-rpc facilities.

[iangrech]

Hi Tim. I was just thinking about it. Will try. Give me a week.

Ian

[timschofield]

Thanks Ian, any help I can give, just shout

[dalers]

Very cool!

in your understanding @iangrech would it be feasible to use self-hosted free software (e.g. Google Gemini?) instead of OpenAI? Would would that entail? My knowledge of providing an AI backend is practially noob (and AI agents not much better). Does it mean starting from the very beginning training the model on PHP software, financial systems and language in general, and then ingest the webERP schema and source? Again, very cool you are looking into this.

Continuing from @timschofield,

Any chance this could be brought behind a webERP login for security puposes?

I would go further to say I think most webERP users (i.e. companies) would insist on total control over their data. Salesforce AI (based on marketing, not practical experience) offers suggestions and recommendations relating to supply chain and company metics in general based on aggregated data from many many companiesm but I don't think that's webERP. I think webERP users in general would insist on their data staying inhouse.

Fwiw, personally, I think there is more benefit long-term to the webERP project by supporting a fully-free AI stack (if that is even possible) than by interfacing to a commercial stack, but supporting a free AI stack in addition to a commercial stack could require more effort than we have.

[iangrech]

Hi Dale,

There is an Ollama Python module which allows for same function. Looking at the link the development looks similiar making a move in that direction possibe. Note that I have not personally tested.

The webERP agent presented here does not work with the data but the schema. The AI is not aware of the data, it just generates SQL statements which then the application executes against the database. In this respect there is no security risk. As is, it can be used to help write queries, especially for someone new to working with webERP or as a basic reporting tool.

I imagine the SalesForce AI is much more developed, integrated deep into thier systems and fully data aware hence the ability to offer recommendations. I would also think they built and trained an LLM themselves.

[dalers]

The webERP agent presented here does not work with the data but the schema.

I see. Thanks for clarifying. Iiuc, the AI agent would primarily be a developers tool for providing help to create queries or perhaps a power user developing a new report, but not something the average user would make use of.

It seems if a webERP user has not modified or added scripts, they wouldn't need to send any data to ChatGPT if the schema came directly from the webERP project (i.e. the github repo). There could be a benefit to the project to claim NO data leaves a webERP site.

I'm curious how well the schema is understood (i.e. what the quality is for the AI generated queries). As I understand it, some of the "schema" may be the code (i.e. not in the SQL schema).

Yes, the Salesforce AI is fully aware of the data. At Dreamforce presentation last year, one use case was using AI to warn of a potential upcoming cash shortage based on sales and revenue, including an offer to purchase receivables to provide needed cash. Another use case was monitoring shipping trends and re-routing orders accordingly.

[dalers]

From the perspective of the webERP development project and if the AI agent also worked with the data, in order to test the AI agent would it be necessary to first create a large non-proprietary dataset? The webERP API could perhaps be used to script a going concern business e.g. create sales orders, shipping orders and enter customer payments, create purchase orders, goods receipt notices and enter supplier payments, create and close manufacturing orders, etc.

[pakricard]

Hi @iangrech : I find this extremely interesting. Thanks for sharing it.

As I see it, the ultimate goal should be to be shipped inside webERP distribution. I have no clue if it's feasible.

If it “only” generates SQL queries, I don't see any risk of data leakage, as the user should go to their own DB via phpMyAdmin or similar and run the query.

But, if it can create the SQL query, and it is protected behind webERP security, I don't see any potential risk (or problem) for the module to go and execute the SQL and offer the results to the user. Just one caution here: we should probably check with user role, if he is allowed to see certain data, but it seems reasonably easy to adapt.

In my case (and I guess I'm not alone here) users of a certain level always ask for data presented in a slightly different way, and the to do list of new report is a never-ending story. But if we can give this tool to final users, that would be great.

R

[hinjolicious]

Interesting feature!

[dalers]

I did some research into how we could support user questions like "Analyze these transactions for unusual spending patterns and summarize the top 3 cost drivers" into webERP with all data remaining local and using open source software (no community, freemium, non-commercial use only or commercial software).

Automated periodic reports could also be easily generated and it all seems very reasonable and doable, but then I don't know what I don't know..... ;-)

Thoughts?

AI Integration

To implement a fully open-source, private, and locally-hosted AI analysis system for webERP, you can combine the existing webERP API with a Python-based "AI sidecar" running Ollama.

This approach respects the permissive licensing requirements (BSD/MIT) and ensures that sensitive financial data never leaves your local network.

1. The Open Source Stack

Component	Software	License
ERP	webERP	GNU GPL
AI Engine	Ollama	MIT (permissive)
Model	Llama 3 (8B)	Meta Llama 3 Community License ("openly available"[1])
Middleware	Python 3.x	Permissive, GPL
Libraries	pandas, requests, xmlrpc.client	BSD / MIT (Permissive)

[1] Meta Llama 3 Commnity License (paraphrased) - Commercial Use permitted for most users. More than 700 million monthly active users requires a special license. Improving or training other (competing) LLMs is not permitted. Must include “Built with Meta Llama 3” in application or product. Trained models must include "Llama 3" in the name (e.g. “webERP Llama 3 LLM”). Must comply with Acceptable Use Policy. Derivative works must be shared under same license.

2. Prepare the webERP API

webERP uses a "KISS" (Keep It Simple, Stupid) XML-RPC API. The built-in API that allows external programs to extract and post data without touching the database directly.

• Locate the API: The server-side script is located at webERP/api/api_xml-rpc.php.
• Configuration: You must define your company database name in api/api_php.php by setting $api_DatabaseName = "your_db_name";.
• Access: Ensure your web server allows POST requests to the /api/ directory.

1. Open api/api_php.php in your webERP directory.

2. Set $api_DatabaseName to your active company database.

3. Ensure you have an admin user/password ready for API authentication.

3. Setup Local AI (Ollama)

Install Ollama on your server and pull the Llama 3 model:

# Install Ollama (Linux example)
curl -fsSL https://ollama.com/install.sh | sh

# Pull the model
ollama run llama3

The AI is now reachable via a local REST API at http://localhost:11434.

4. Build an AI Anaysis Middleware (The "Brain") (Python)

Since webERP is PHP-based and most AI libraries (like LangChain, OpenAI SDKs, or Scikit-Learn) are Python-based, you should create a small Python middleware to bridge the two.

• Data Extraction: Use a Python library like xmlrpc.client to call webERP's API. For example, you can call GetCustomerTrans or GetStockBalance to pull raw financial data.

• AI Logic:

  • Financial Summarization: Use a Generative AI API (like OpenAI's GPT-4o or Anthropic's Claude) to send "Year-to-Date" P&L reports for a natural language summary.
  • Anomaly Detection: Use Pandas and Scikit-Learn to analyze transaction history and flag outliers (potential fraud or errors).
  • Forecasting: Use Prophet or Statsmodels to predict future cash flows based on historical ledger data.

This script acts as the bridge. It pulls data from webERP, sends it to Llama 3 for analysis, and outputs a financial summary.

import xmlrpc.client
import requests
import json

# 1. Connect to webERP API
weberp_url = "http://your-server/webERP/api/api_xml-rpc.php"
db = "your_company_db"
user = "admin"
password = "your_password"

server = xmlrpc.client.ServerProxy(weberp_url)

# 2. Extract Data (Example: Get Stock Balances or GL Trans)
# webERP API requires parameters passed as an array
try:
    stock_data = server.webERP.xmlrpc_GetStockBalance({'StockID': 'ALL', 'Location': 'ALL'}, user, password)
except Exception as e:
    print(f"Error fetching data: {e}")
    stock_data = []

# 3. Analyze with Local Llama 3 via Ollama
def analyze_with_llama(data_summary):
    prompt = f"""
    You are a financial analyst. Analyze the following inventory data from webERP:
    {data_summary}
    
    Identify:
    1. Items with critically low stock.
    2. Any unusual patterns or overstocked items.
    3. Suggested actions for the warehouse manager.
    """
    
    response = requests.post('http://localhost:11434/api/generate', 
                             json={
                                 "model": "llama3",
                                 "prompt": prompt,
                                 "stream": False
                             })
    return response.json().get('response')

# Format a small subset of data for the AI
data_text = str(stock_data[:10]) # Sending first 10 items for brevity
analysis = analyze_with_llama(data_text)
print("--- AI Financial Analysis ---")
print(analysis)

5. Display Results in webERP

You have two primary ways to display these insights back to the user in webERP:

Option A: The "Report Link" (Easiest)

Create a new script in webERP (e.g., AI_Financial_Analysis.php) that simply provides an iframe or a link to a dashboard hosted by your Python middleware (using Streamlit or Flask). This avoids complex UI changes within webERP itself.

E.g. create a simple PHP "wrapper" page inside webERP that calls your Python script and displays the result.

1. Create webERP/AI_Dashboard.php.

2. Use shell_exec() to run your Python script:

<?php
include('includes/session.php');
$Title = _('Local AI Financial Analysis');
include('includes/header.php');

echo '<h3>Llama 3 Financial Insights</h3>';
$output = shell_exec('python3 /path/to/your_ai_script.py');
echo '<pre>' . htmlspecialchars($output) . '</pre>';

include('includes/footer.php');
?>

Option B: Inject AI Insights into webERP Screens

If you want the AI analysis to appear directly on existing pages (like CustomerInquiry.php):

1. Modify the specific PHP file to make a background curl request to your AI middleware.

2. Pass the relevant IDs (Customer ID, Invoice ID) to the middleware.

3. Display the returned text/JSON in a new

   at the top of the page.

6. Why this works well

• Privacy: Since you are using Ollama, no financial data is sent to OpenAI or any cloud provider.

• Zero License Cost: Every tool in this stack is free and permissively licensed.

• Low Overhead: You don't need to learn the complex webERP database schema; you simply use the existing API functions.

7. Example Workflow for "AI Monthly Summary"

1. Trigger: A user clicks "AI Summary" on the webERP Profit & Loss report.

2. Fetch: A PHP script calls the XML-RPC API to gather the last 3 months of GL transactions.

3. Process: The script sends this JSON to an OpenAI/Local LLM endpoint with the prompt: "Analyze these transactions for unusual spending patterns and summarize the top 3 cost drivers."

4. Display: The result is rendered in a simple modal window within the webERP interface.

[dalers]

More musings with Gemini..

How well would the meta Llama 3 model perform when used to report on financial data in a webERP database?

Meta's Llama 3 (specifically the 70B and 405B variants) is highly capable of reporting on financial data from a webERP database, particularly when deployed in a Text-to-SQL or Agentic RAG workflow.

Here is how Llama 3 performs across key reporting dimensions:

• SQL Generation Performance: Llama 3 exhibits strong "Text-to-SQL" capabilities, allowing it to translate natural language questions (e.g., "What was the total revenue for North America last quarter?") into complex SQL queries against a structured database. For non-standard SQL dialects or specific schemas like webERP, fine-tuning Llama 3 8B or 70B can further improve accuracy.

• Financial Accuracy: Benchmarks show that while Llama 3 is robust for fact-based queries, it currently achieves around 75-85% accuracy for specialized financial tasks like ratio calculation. More complex "agentic" tasks, such as multi-step financial research, may still see accuracy levels below 50% without heavy optimization.

• Data Privacy & Security: A major advantage of Llama 3 for financial data is the ability to run locally. This ensures that sensitive webERP financial records never leave your infrastructure, addressing compliance concerns that often block the use of closed-source models like GPT-4.

• Speed and Scalability: Using inference engines like Groq, Llama 3 can generate reports at speeds up to 800 tokens per second (8B model), making real-time dashboards and instant financial summaries highly feasible.

• Reasoning and Insights: Llama 3 can go beyond just fetching data; it can generate descriptive narratives, identify trends, and automate report summarization, which makes financial insights more accessible to non-technical stakeholders.

Implementation Recommendations

1. Use a Semantic Layer: Map your webERP database schema (e.g., gltrans, salesorderdetails) to a semantic layer or use LlamaIndex to help the model understand the relationships between tables before querying.

2. Hybrid RAG: Combine SQL for structured data retrieval with a RAG pipeline for unstructured data (like invoice notes) to provide a holistic financial report.

3. Validation: Implement a "Human-in-the-loop" review process or automated LLM-as-a-judge validation for any reports that involve critical financial decision-making.

Describe a suitable server for performing one query at a time.

For a single-query-at-a-time environment, such as a local financial reporting station for webERP, a "Prosumer" or entry-level workstation is sufficient. The hardware focus should be on VRAM capacity to store the model and NVMe storage for fast loading.

Recommended Server Specifications (Single-User Inference)

1. GPU (The Critical Component):

• Best Value: A single NVIDIA RTX 4090 (24GB VRAM) can run a 4-bit quantized version of Llama 3 70B with some "spillover" to system RAM, or it can run the 8B model at lightning speeds entirely in VRAM.
• Professional Choice: An NVIDIA RTX A6000 (48GB VRAM) allows you to fit a 4-bit quantized 70B model entirely in VRAM, ensuring the fastest possible single-query response times (approx. 10–15 tokens/sec).

2. System RAM:

• Minimum: 64GB DDR5.
• Recommended: 128GB DDR5. This provides a "safety net" for the OS and the webERP database engine to run concurrently with the LLM.

3. CPU:

• Minimum: 8-core modern processor (e.g., Intel i7-13700K or AMD Ryzen 7 7700X). For single queries, the GPU does 95% of the work; high core counts (32+) are only necessary if you aren't using a GPU.

4. Storage:

• Primary: 1TB NVMe M.2 SSD (Gen 4 or Gen 5). Fast read speeds are essential for loading the 40GB+ model weights into memory upon startup.

5. Software Stack:

• Inference Engine: Ollama or LM Studio for easy, single-query management.
• Database Integration: LlamaIndex to bridge the LLM and your webERP SQL database.

[timschofield]

OK, an idea that would use this AI agent.

I have been messing around trying to create an improved report generator for a while. The only hard part is creating the SQL query in an easily used and simple way. This would seem to be something that this AI agent can easily do from a users description. Then it passes it to the report generator which runs the query, creates an HTML table, and can then export it directly to the screen, or as a PDF using DomPDF or as a spreadsheet using PHPSpreadsheet.

What do we think?

Tim

[dalers]

Imho the first step is to spend time with the POC @iangrech created asking "typical" questions to

1. Evaluate the quality of the generated SQL.
2. Determine the feasibility of converting the query results into an acceptable report.

As the queries will be arbitrary, I suspect the report generator will only be able to do basic prettifying of the query response (because query responses could vary greatly).

Not considering server requirements, I think running a "private" LLM will provide much greater user value than a third-party AI service that only generates report SQL queries because

• it could use the data in a company db to produce more inciteful analysis reports wirhout leaking confidential data.
• it could presumably generate complete reports without an intermediary report generator.

However, the third-party approach will be simpler and faster (@iangrech has already done the heavy lifting), not leak data and be a good place to start.

Sample Questions

From https://github.com/iangrech/weberp_ai_agent

1. Which customer spent the most this year to date?
2. Which are the top 5 spending customers last year listed? Show the list in descending order by spend.
3. What is the value of the items in stock?
4. List the open purchase orders, showing the supplier and value.
5. List the open purchase orders, showing the supplier and value and the order date sorted by oldest on top.

[timschofield]

I'm not suggesting the report writer is the only use for this, just one use.

1. Evaluate the quality of the generated SQL.

The report queries are pretty simple really. I have been using the cli copilot and I haven't noticed any bad queries. The great thing is that the user can just describe the sql in normal language.

2. Determine the feasibility of converting the query results into an acceptable report.

This is the easy bit. The existing report writer already does something similar. Look at something like PDFStockNegatives.php to see how it goes from query to report. Yes a bit of fluff will need adding, but it's not hard.

Tim

[dalers]

What is the user interface? If I was a new junior bookkeeper hired to create a bunch of reports that people had wanted since forever, how do you envision me doing that?

• Do I just need a Copilot account (or any AI account) to generate an SQl query and manually copy the SQL into an example (aka template) script (which resembles PDFStockNegatives.php)?
• Or, do I need to edit some config file to add my Copilot/Gemini/ChatGPT account credentials, which allows me to use some new "AI Reports" script that generates an SQL query based on my free text question and then coies the SQL into a template script (that resembes PDFStockNegatives) to create a new report?

If the AI is hidden from the user behind a webERP script, and assuming you were the one writing the code, which AI service would you want to support first?

• Copilot? (it seems the one you are most familiar with)
• ChatGPT? (probably the most expedient if the core of @iangrech 's POC flask app can be easily extracted and pasted into a webERP script),
• Gemini? (could consolidate services as I expect we all have Google accounts and already use many other of their "free" services)

[timschofield]

Hi @dalers hasn't @iangrech already created an agent that can produce an SQL query from a plain text question? So that is then just used to produce the report.

Tim