ERP AI Assistant: Plain-English Questions to Safe SQL
An assistant embedded in the client's ERP that lets managers ask business questions typed in their own language, with spoken input supported, and get answers straight from live company data. The model only writes the query. Every query is validated as read-only before it runs, and the numbers in every answer come straight from database rows.
- CLIENT
- a US consumer electronics brand
- INDUSTRY
- Distribution and e-commerce
- ENGAGEMENT
- Designed, architected and built by AppsXone. Accuracy rebuild, new modules and test automation delivered September 2026.
- STACK
- ASP.NET Core (.NET 8), Razor Pages, JavaScript, SQL Server, T-SQL, Microsoft ScriptDom, OpenAI GPT-4.1, Browser speech APIs
Managers wanted sales, receivables, inventory and order answers without waiting on reports. The first version gave wrong or empty answers too often to be trusted.
- The model guessed column names. It saw only column names, with no types, join keys or allowed values, and naming differed across tables, so it filled the gaps by inventing fields.
- Some rules were wrong. One rule searched a brand field that did not exist on the inventory table. Another defined open orders using a flag that was always zero.
- Correct SQL still displayed wrong. Quantities showed as dollars, paid columns were hidden, and a zero discount made the assistant report that no sales were found.
- The data underneath had problems. A reporting refresh could leave key tables empty, and inventory was loading from an outdated source.
Plan, validate, execute, present, with one repair loop.
The model's only job is to write a candidate query. It never produces the numbers. Everything after the planning stage is deterministic code that either proves the query is safe to run or rejects it.
A query that fails validation, or that errors at runtime, gets one repair attempt using the closest valid column names. If the repair also fails, the assistant says so rather than guessing. Only validated, read-only SQL ever reaches the data.
Fourteen business modules, answered from live data.
Each module maps to real tables and real business rules rather than a general-purpose prompt.
| Module | What users can ask |
|---|---|
| Sales and revenue | Any period, month and year to date, against last year, monthly and weekly trends, by customer, item, brand, rep or territory, margin percentage, growing and declining customers. |
| Customers | Profile, credit limit and terms, new customers, customers by state, lapsed customers. |
| Accounts receivable | Balances, aging buckets, 60 and 90 plus days past due, credit balances, receivables by rep. |
| Invoices | Paid and unpaid, line detail, totals, days to pay, due this week. |
| Sales orders and shipping | Open, pending and backordered, carrier, shipment cost, tracking numbers by order or purchase order. |
| Cash receipts and payments | Cash by period or customer, deductions, payment type, check and wire numbers. |
| Returns and credit memos | Returns by reason, customer and item, plus return rate. |
| Inventory and items | On hand, available to sell, low stock, inventory value, lookup by SKU, UPC or ASIN, warehouse and system discrepancies, slow movers. |
| Item size and weight | Unit and carton dimensions, volume and weight. Unmeasured items are named rather than shown as zeros. |
| Location-wise inventory | Stock per warehouse and sales channel, with closed locations excluded. |
| Purchasing and vendors | Open purchase orders and their value, order lines, next estimated arrival, unit cost, vendor contacts. |
| Data freshness | When each data area was last refreshed. Every answer shows its source's refresh time. |
| Conversation | Follow-ups that keep context, such as asking for last year, only the top three, or the same figure by customer, plus corrections. |
| Safety and governance | Read-only SQL, injection attempts ignored, write requests refused, impossible dates refused, and an audit log per signed-in user. |
One system, built around how the work actually happens.
A data dictionary the model can trust
Every column with its type, meaning, join keys and real values, pulled from the live database. Always-empty and misleading columns removed, business rules rewritten by domain, and forty golden question-to-SQL examples executed against the real database on every build.
Live schema checking
At startup the application reads the live schema and drops any column or table the database does not have. Examples whose SQL no longer validates switch off automatically, and rules for new columns switch on once the data mart adds them.
A validation layer in front of the database
Every query is parsed with Microsoft's T-SQL parser and bound against an allow-list of tables, columns, aliases and functions. Only read-only SELECT statements run. Validation errors and runtime errors each trigger one schema-aware repair.
Server-side conversation memory
Recent turns are kept per user on the server, so follow-up questions work even from clients that send no history of their own.
Fixing the data, not just the assistant
Inventory moved to the live item source. Refreshes now load into staging tables and swap in one transaction with a row-count guard, so a failed refresh cannot leave tables empty. Fields visible on ERP screens were added to the data mart: tracking numbers, shipment cost, carrier, rep names, return reasons and received quantities.
Governance and audit
Secrets kept out of committed configuration, API rate limiting, and an audit log of every step, the generated SQL and the signed-in ERP user.
Test automation
A deterministic check suite, a live evaluation harness that replays question sets end to end and asserts on both the SQL and the answer, and a one-click health check covering build, checks, live examples, evaluation and application start.
What using it is actually like.
- Questions typed in the user's own language, with spoken input supported.
- Tested end to end in English and Roman Urdu, including mixed English and Roman Urdu.
- Spoken replies, with key numbers read aloud.
- Single sign-on from the ERP through a signed integration token.
- Smart table formatting: currency only where the value is money, and identifiers such as UPCs never comma-formatted.
- Honest gaps. Where a field is not in the data, the assistant says so instead of guessing.
What it runs on.
What changed.
- 599 of 601 end-to-end test questions pass, at 99.7 percent. The two failures were fixed the same day.
- 247 automated code checks, up from 106.
- 40 verified example queries executed against the real database on every build.
- Short and aggregate answers return in 2 to 5 seconds, or 6 to 11 seconds when a repair runs, against a previous timeout path of up to 1.5 to 2 minutes.
- Zero write access. Every query is parsed and validated as read-only before it runs.
- Repeated questions across 9 test sets returned identical totals every time.
What changed, measured.
| Measure | Before | After |
|---|---|---|
| End-to-end test questions | None automated | 601 run, 599 pass (99.7 percent) |
| Deterministic code checks | 106 | 247 |
| Verified example queries | 0 | 40, executed on every build |
| Data areas | Several columns empty or missing | 19 tables, checked against the live schema |
| Brand queries | Returned nothing for some brands | Correct, matched against the ERP |
| Open-order count | Wrong, from a flag that was always zero | Matches the ERP screen |
| SQL errors at runtime | Dead-end error | One schema-aware automatic repair |
| Answer speed | Up to 1.5 to 2 minutes on the timeout path | 2 to 5 seconds, or 6 to 11 seconds with a repair |
Repeated questions across 9 test sets returned identical totals every time.
What we would tell the next team.
- Check the data before blaming the model. Most of what looked like model error was missing or misleading data.
- Schema with meaning beats schema with names. Types, join keys and real allowed values cut invented columns to near zero.
- Golden examples are the strongest lever, but only if they run against the live database on every build.
- Say what is missing. An honest answer that a field is not recorded builds more trust than a confident guess.
After go-live.
The assistant is in active use inside the ERP, alongside the wider multi-marketplace system described in our multi-marketplace ERP case study for the same client. Work in progress covers richer answer sentences for list results, an administration screen for model and table permissions, and user-facing chat history.
The work behind this project.
Running into something similar?
Describe what you are dealing with and we will tell you honestly whether this is a pattern we have solved before.