# How to Add AI to Macola ERP On-Prem Without Sending Data to a Cloud

> Source: https://netray.co/erp-help/private-ai-layer-macola-on-prem-architecture
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

Macola 10 runs on SQL Server, so the safest pattern is a read-only reporting copy of the Macola company database, a small set of approved views, a retrieval index over attached documents, and an open-weight model served on a local GPU. Writes stay out of the first phases and, when added, go through Macola's supported import or SDK routes with a person approving each one.

## Kind

advanced

## System

small-erp

## Erp

Exact Macola 10 (ECI Macola)

## Module

SQL Server data access / AI architecture

## Query

how to add AI to Macola ERP on-prem

## Alternate Queries

- Macola AI integration without cloud
- Macola 10 ChatGPT alternative on-premise
- Exact Macola SQL Server read-only AI assistant
- private LLM for Macola ERP

## Applies To

Exact Macola 10.x on Microsoft SQL Server. Older Progress-based Macola ES releases need a different data path and are covered under limits below.

## Steps

### Reference build for a private AI layer on Macola

**Items:**

Confirm the Macola release and that the company databases are on SQL Server. List every company database the assistant must cover, because Macola keeps each company in its own database.

Create a reporting copy: an Always On readable secondary, log shipping with a restored standby, or a nightly restore. Pick based on how fresh answers must be. Never point the model at the production database.

On the copy, create a dedicated schema (for example ai) and expose only views over the tables you approve, such as the item master, item location, customer order header and lines, purchase order header and lines, customer and vendor masters.

Create a SQL login with SELECT on that schema only. No access to payroll, bank or credential tables, and no EXECUTE rights.

Build a retrieval index over documents tied to Macola records: drawings, PO PDFs, certs, quotes and SOPs on the file share. Store the Macola key (item number, order number) as metadata on every chunk.

Stand up a tool server (MCP or a plain REST wrapper) that offers a handful of parameterised read actions, for example order status, item availability, open POs by vendor. The model calls tools; it does not write free SQL against base tables.

Serve an open-weight model with vLLM or Ollama on a local GPU server. Put the tool server, retrieval index and model on the same network segment with no outbound route.

Log every question, the tool calls made, the SQL or view used, the rows returned and the user. Review the log weekly in phase 1.

## Sections

### Where Macola keeps its data and how to read it safely

**Body:**

Macola 10 stores business data in SQL Server tables whose names carry a module prefix and an _sql suffix, for example imitmidx_sql for the item master, oeordhdr_sql and oeordlin_sql for customer orders, poordhdr_sql and poordlin_sql for purchase orders, arcusfil_sql for customers and apvenfil_sql for vendors. Your exact column set depends on release and customisations, so verify names against your own database before writing views.

Do not let a language model generate free SQL against these tables. Wrap them in views with friendly column names and fixed filters (open orders only, current year, active items), then expose the views through tool functions with typed parameters. This keeps answers explainable and stops a hallucinated join from scanning a hundred million rows.

Macola also has its own integration and import options that differ by version. Treat them as the write path, and confirm with your reseller or the vendor documentation which ones your licence and release support.

**Code:**

-- Illustrative only. Verify table and column names in your Macola database.
CREATE SCHEMA ai;
GO
CREATE VIEW ai.open_order_lines AS
SELECT h.ord_no, h.cus_no, l.item_no, l.qty_ordered, l.request_dt
FROM dbo.oeordhdr_sql h
JOIN dbo.oeordlin_sql l ON l.ord_no = h.ord_no
WHERE h.status NOT IN ('C','X');
GO
CREATE USER ai_reader FOR LOGIN ai_reader;
GRANT SELECT ON SCHEMA::ai TO ai_reader;

### Reference architecture and the air-gapped option

**Body:**

The layers are: the SQL Server reporting copy, the ai views, a change feed or scheduled refresh, a vector index for documents, a tool server, the model server and an audit store. Everything sits inside your network. For a fully air-gapped site the model weights, embedding model and Python packages are carried in on approved media and updated on a schedule you control.

Freshness depends on the copy method. A readable secondary is near real time, log shipping is minutes, a nightly restore is a day old. Most shop-floor and purchasing questions tolerate a few minutes; say clearly in the answer when the data was last refreshed.

Hardware is modest for a Macola-sized user base. One 24 to 48 GB class GPU serves a quantised mid-size model for a few dozen active users, and a second card gives a standby. Retrieval and the tool server run comfortably on a normal virtual machine.

### Permissions: making the assistant respect Macola security

**Body:**

Macola security is enforced in the application, not in SQL Server, so a database login sees more than any single Macola user does. Mirror the rules in the tool layer: map each person's Macola user and company access to the assistant session and filter every tool call accordingly. For finance, cost and margin fields, default to deny and open them to named groups.

SQL Server row-level security on the reporting copy is a useful second fence, for instance restricting rows by company or site code. Document retrieval needs the same treatment: each chunk carries an access label inherited from its folder or record, and the retriever filters on the caller's groups before the model sees any text.

Write-back, when you reach it, goes through supported Macola import or API routes only, never by updating tables directly, and every proposed change appears as a draft the user approves.

### Sizing, rollout and honest limits

**Body:**

Plan for roughly 10 to 25 percent of named users active on a given day and a handful of questions each. That load fits one GPU for most Macola sites; the harder sizing question is the document index, which grows with the number of PDFs, not users.

Roll out in three phases: read-only questions over approved views (weeks 1 to 6), drafts such as an email to a vendor or a PO change suggestion the user copies (weeks 6 to 12), then approved actions through supported import routes with a named approver.

Limits: custom tables and heavily modified screens need their own views; Progress-based Macola ES releases lack the SQL path described here; and if you only need standard reports, Macola's own reporting tools may be enough. A language model adds value where people ask free-form questions across orders, stock and documents.

## Pitfalls

- Pointing the assistant at the production database and letting it run free SQL, which risks slow queries during order entry.
- Forgetting that Macola holds each company in a separate database, so answers silently cover only one entity.
- Relying on SQL logins alone and exposing cost or margin columns to people who cannot see them in Macola.
- Indexing documents without the Macola key, so retrieval cannot be filtered by customer or order.
- Letting the model write back through direct table updates instead of supported routes.
- Skipping an audit log, which makes later security reviews impossible.

## Ai Angle

ERPray can sit on this reporting copy to answer questions such as which open orders are past their request date for a customer, showing the view and filter it used and respecting the mirrored roles. Netray builds the tool server, retrieval index and local model serving inside your network, and your Macola reseller or consultants remain the experts on your configuration.

## Faq

### Can we run AI on Macola without any cloud service?

**Answer:** Yes. Macola 10 data lives in SQL Server on your servers, so a reporting copy, a retrieval index and an open-weight model on a local GPU can all run inside your network. Even model updates can be carried in manually for an air-gapped site.

### Does the AI write to Macola?

**Answer:** Not at first. Phase 1 is read-only. Later phases produce drafts, and any real change goes through a supported Macola route with a person approving it. Direct updates to tables are not recommended.

### How big a GPU do we need?

**Answer:** For a few dozen active users, one 24 to 48 GB class GPU running a quantised open-weight model is a typical starting point, with a second card for resilience. Document volume affects storage and indexing time more than GPU count.

### Is this possible on older Macola versions?

**Answer:** Releases not on SQL Server need a different extraction path, often scheduled exports into a reporting database. It is possible but slower and less fresh. Check your release first.

## Search terms

Macola AI, Macola ERP on-prem AI, Exact Macola 10 AI integration, Macola SQL Server AI assistant, private LLM Macola, Macola ChatGPT alternative, Macola air-gapped AI, AI for Macola without cloud

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json