# How to Add a Private AI Layer to QAD Enterprise Edition On-Prem > Source: https://netray.co/erp-help/private-ai-layer-qad-ee-on-prem-architecture > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer QAD EE runs on a Progress OpenEdge database, which can be read through the OpenEdge SQL-92 engine over JDBC or ODBC without touching the 4GL application. A private AI layer reads a replica or reporting copy, indexes documents locally, serves an open-weight model on your own GPUs, and writes back only through QXtend or another supported QAD interface with human approval. ## Kind advanced ## System qad ## Erp QAD Enterprise Edition (Progress OpenEdge) ## Module AI integration / OpenEdge SQL and QXtend ## Query how to add AI to QAD Enterprise Edition on-prem ## Alternate Queries - QAD EE AI integration without cloud - QAD OpenEdge SQL read-only AI assistant - private LLM for QAD on premises ## Applies To QAD Enterprise Edition 2008 through 2021 EE on OpenEdge 10.2B, 11.x and 12.x; Adaptive ERP differs and is covered separately ## Steps ### Steps to add a private AI layer to QAD Enterprise Edition **Items:** Inventory the OpenEdge databases (typically a main QAD database plus any separate report or archive databases) and confirm the OpenEdge version and whether the SQL broker is licensed and running. Stand up a read-only copy: OpenEdge Replication to a target database, or a Pro2-style replication into SQL Server or PostgreSQL if you want a standard relational engine for the AI layer. Create a dedicated SQL-92 account with SELECT only on the tables the assistant needs, and run UPDATE STATISTICS so the SQL engine plans queries sensibly. Build a thin semantic layer: views or a documented query catalogue over pt_mstr, sod_det, pod_det, wo_mstr, ld_det, tr_hist and the customer and supplier masters, always filtered by domain. Index QAD attachments and linked documents (drawings, certificates, quality records) into a local vector store with the originating domain, site and document class stored as filter metadata. Serve an open-weight model with vLLM or Ollama on local GPUs and expose approved read actions to it through an MCP or tool server that logs every call. Map each QAD user to their domain and site access, and apply it as a mandatory filter on every query and every retrieval request. Release in phases: read-only questions first, then drafts (for example a draft PO change), then approved write-backs through QXtend inbound after a named user confirms. ## Sections ### How QAD EE exposes its data **Body:** QAD EE stores everything in Progress OpenEdge databases. The application is written in the Progress 4GL, but the database also has a SQL-92 engine, so any JDBC or ODBC client can query tables without going through application code. Tables are exposed in the pub schema with the familiar QAD names: pt_mstr for items, so_mstr and sod_det for sales orders, po_mstr and pod_det for purchase orders, wo_mstr for work orders, ld_det for inventory by location and lot, tr_hist for inventory transactions, cm_mstr and vd_mstr for customers and suppliers. Almost every table carries a domain column (pt_domain, ld_domain and so on) because QAD EE is multi-domain. A query without a domain predicate returns rows from every domain the account can see, which is exactly what you must prevent for a per-user assistant. Treat the domain predicate as non-optional in the semantic layer. For anything that changes data, QAD provides QXtend, its service layer for inbound and outbound business transactions. Writing to the OpenEdge tables directly with SQL bypasses 4GL validation, triggers and audit logic, so it is not an acceptable write-back path. **Code:** -- OpenEdge SQL-92, read-only account, domain-filtered SELECT ld_part, ld_site, ld_loc, ld_lot, ld_qty_oh, ld_status FROM pub.ld_det WHERE ld_domain = '10USA' AND ld_part = 'A-1042' AND ld_qty_oh <> 0; # JDBC URL pattern for the DataDirect OpenEdge driver jdbc:datadirect:openedge://dbhost:5100;databaseName=qaddb -- Refresh optimizer statistics for the SQL engine UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.ld_det; ### Reference architecture **Body:** The assistant never connects to the production database. Use OpenEdge Replication to keep a read-only target, or a Pro2-style replication product into a relational copy, and point the SQL account at that. Heavy aggregate questions then cannot slow shop-floor transactions, and a compromised assistant has no path to production. Around the replica sit four components: a retrieval index over documents and long-text fields, a tool server that exposes a short list of approved parameterised queries (open orders by customer, stock by lot, PO status by supplier), an open-weight model served with vLLM or Ollama, and an append-only audit log of prompt, tool calls, rows returned and user. Free-form SQL generation is possible, but start with the catalogue and widen it as you measure accuracy. For air-gapped sites, every component runs inside the enclave: model weights are carried in on approved media, the embedding model is local, and there are no outbound calls. The same design works in a private cloud tenant when policy allows it. ### Mirroring QAD security so users only see their own data **Body:** QAD controls access by menu function, domain and site, and many shops add their own restrictions in custom 4GL. The assistant has to enforce the data part of that model itself, because the SQL account sees everything it was granted. Sign users in through your identity provider, resolve their QAD user ID, and look up the domains and sites they are allowed to work in. Inject those domains and sites into every query as bound parameters, and store the same values as metadata on every document chunk so retrieval filters on them before the model sees any text. Field-level restrictions, such as hiding cost or margin from some roles, need explicit rules in the semantic layer because they live in application logic, not in the database. Test with deliberately restricted users. A question like 'what is our cost on part A-1042' must return nothing for a user without cost visibility. ### Write-back, sizing and phased rollout **Body:** Write-back goes through QXtend inbound or another supported QAD interface, never raw SQL. The assistant prepares a draft transaction, shows the exact payload and affected records, and a named user approves it. QAD then applies its own validation, so the audit trail stays inside QAD. Sizing is driven by concurrency, not named users. A plant with 100 to 300 named QAD users and 20 to 40 percent daily active use typically needs one to two 48 GB class GPUs for a 14B to 32B parameter model, plus a small CPU box for the vector store. Document volume sets index size; a few hundred thousand pages is a modest index measured in single-digit gigabytes. Phase one is read-only questions over inventory, orders and purchasing for 4 to 6 weeks. Phase two adds document Q and A and drafts. Phase three enables a short list of approved write-backs, each with its own sign-off. ### Honest limits **Body:** Custom tables and heavily customised 4GL logic are the main accuracy risk: the model can only reason about what the semantic layer describes. Budget time to document custom fields and status codes. Version matters too: very old OpenEdge releases and unlicensed SQL brokers can make SQL access impractical, in which case a replication-based extract is the fallback. If your questions are all standard reports, QAD's own reporting and browse tools may be enough. And if you are moving to QAD Adaptive ERP, plan the AI layer against that platform's interfaces rather than investing deeply in EE table-level access. ## Pitfalls - Querying without a domain predicate, which mixes data from every domain the SQL account can see. - Skipping UPDATE STATISTICS on the OpenEdge SQL engine, then blaming the model for slow answers. - Pointing the assistant at the production database instead of a replica. - Writing to QAD tables with SQL instead of QXtend, which bypasses validation and the audit trail. - Ignoring customised fields and status codes, so answers look fluent but are wrong. ## Ai Angle ERPray reads QAD through the read-only SQL path described here, shows the query it ran, and respects domain and site access. For custom QAD estates, Netray builds the semantic layer and tool server with your team, and keeps the model, index and logs on your hardware. ## Faq ### Can an AI assistant read QAD EE without changing the application? **Answer:** Yes. OpenEdge exposes a SQL-92 engine over JDBC and ODBC, so a read-only account on a replica can answer questions without any 4GL change. You still need a semantic layer, domain filtering and an audit log around it. ### Do I need QXtend for a read-only assistant? **Answer:** No. QXtend matters for write-back and event-style integration. Read-only phases use SQL on a replica plus a document index. Add QXtend only when you approve specific write actions. ### What GPU do I need? **Answer:** For a few hundred named users, one or two 48 GB class GPUs running a 14B to 32B open-weight model is a common starting point. Measure concurrency first; the calculator on this site gives a range. ### Does this work air-gapped? **Answer:** Yes. Model weights, embeddings, the vector store and the tool server all run inside the enclave. Updates arrive on approved media and nothing calls out. ## Search terms QAD AI integration, QAD EE on-prem AI, QAD OpenEdge SQL, QAD QXtend write-back, private LLM QAD, air-gapped QAD AI, QAD pt_mstr ld_det query, AI assistant QAD Enterprise Edition --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json