AI Assistant for ERP: Analytics, Forecasts, Recommendations

AI Assistant for ERP: Analytics, Forecasts, Recommendations

ERP systems accumulate tons of data on sales, procurement, and production. A manager wants to know: "Which shop floor is eating the most budget?" or "When will raw material X run out?" Without SQL knowledge or a BI analyst, getting an answer takes hours. We build an AI assistant that understands natural language, constructs SQL itself, interprets numbers, and gives recommendations. Time savings for management personnel — up to 80%, with average budget savings on analytics of $3,200 per month.

Problems We Solve

Ad-hoc queries get stuck in the analyst queue. In a company with 15 managers, the average analyst spends 25 hours a week on repetitive "check revenue by customer" requests. The AI assistant handles 70% of such requests, freeing the analyst for deep research.

Forecasting turns into guesswork. Without automation, managers build forecasts in Excel based on intuition. The assistant pulls historical trends, seasonality, and external datasets, executing a chain of queries to ERP through an agent loop.

Alerts are missed. Critical deviations — overdue receivables, budget overruns — are often noticed too late. The daily health check we embed scans KPIs and sends alerts to Telegram or Slack.

Architecture of the ERP Assistant

The ERPAssistant class uses an LLM (e.g., Claude Sonnet 4-5) with tools: execute_query for safe SELECTs and get_kpi_metrics for pre-calculated metrics. The model decides which SQL is needed, executes it through a read-only connection, and returns an interpretation with recommendations.

from anthropic import Anthropic
import psycopg2
import json
from typing import Any
from pydantic import BaseModel

client = Anthropic()

class ERPQueryResult(BaseModel):
    sql: str
    data: list[dict]
    interpretation: str
    recommendations: list[str]
    alerts: list[str]

class ERPAssistant:

    def __init__(self, db_connection_string: str, erp_schema: dict):
        self.conn = psycopg2.connect(db_connection_string)
        self.schema = erp_schema  # ERP table and relationship description

        self.tools = [
            {
                "name": "execute_query",
                "description": "Execute an SQL query against the ERP database",
                "input_schema": {
                    "type": "object",
                    "properties": {
                        "sql": {"type": "string", "description": "SELECT query"},
                        "description": {"type": "string", "description": "What the query does"}
                    },
                    "required": ["sql"]
                }
            },
            {
                "name": "get_kpi_metrics",
                "description": "Get pre-calculated KPI metrics",
                "input_schema": {
                    "type": "object",
                    "properties": {
                        "metric_type": {
                            "type": "string",
                            "enum": ["revenue", "inventory", "expenses", "headcount", "orders"]
                        },
                        "period": {"type": "string", "description": "Period: week/month/quarter/year"}
                    },
                    "required": ["metric_type", "period"]
                }
            },
        ]

    def execute_query(self, sql: str) -> list[dict]:
        """Safe execution of SELECT queries only"""
        if not sql.strip().upper().startswith("SELECT"):
            raise ValueError("Only SELECT queries are allowed")

        with self.conn.cursor() as cur:
            cur.execute(sql)
            columns = [d[0] for d in cur.description]
            rows = cur.fetchall()
            return [dict(zip(columns, row)) for row in rows[:100]]

    def get_kpi_metrics(self, metric_type: str, period: str) -> dict:
        """Returns pre-calculated metrics"""
        # In a real system — queries to ERP tables
        # Simplified example below
        period_sql = {
            "week": "AND date >= CURRENT_DATE - INTERVAL '7 days'",
            "month": "AND date >= DATE_TRUNC('month', CURRENT_DATE)",
            "quarter": "AND date >= DATE_TRUNC('quarter', CURRENT_DATE)",
            "year": "AND date >= DATE_TRUNC('year', CURRENT_DATE)",
        }
        date_filter = period_sql.get(period, period_sql["month"])

        if metric_type == "revenue":
            sql = f"SELECT SUM(amount) as total, COUNT(*) as orders FROM sales WHERE 1=1 {date_filter}"
            return self.execute_query(sql)[0] if self.execute_query(sql) else {}
        # ... other metrics
        return {}

    def dispatch_tool(self, tool_name: str, tool_input: dict) -> Any:
        if tool_name == "execute_query":
            return self.execute_query(tool_input["sql"])
        elif tool_name == "get_kpi_metrics":
            return self.get_kpi_metrics(tool_input["metric_type"], tool_input["period"])
        raise ValueError(f"Unknown tool: {tool_name}")

    def answer(self, question: str, user_role: str = "manager") -> ERPQueryResult:
        """Answer an analytical question"""

        messages = [{
            "role": "user",
            "content": f"""Question: {question}
User role: {user_role}

ERP database schema:
{json.dumps(self.schema, ensure_ascii=False, indent=2)[:2000]}

Use tools to get data, then:
1. Interpret the results
2. Give recommendations if applicable
3. Highlight alerts if data requires attention"""
        }]

        sql_queries = []
        all_data = []

        while True:
            response = client.messages.create(
                model="claude-sonnet-4-5",
                max_tokens=4096,
                system=f"""You are a business analyst for the company's ERP system.
Analyze data accurately, cite specific numbers.
Give practical recommendations to management.""",
                tools=self.tools,
                messages=messages,
            )

            if response.stop_reason == "end_turn":
                final_text = next(
                    (b.text for b in response.content if hasattr(b, "text")), ""
                )
                return ERPQueryResult(
                    sql="; ".join(sql_queries),
                    data=all_data[:10],
                    interpretation=final_text,
                    recommendations=self._extract_list(final_text, "Recommendations"),
                    alerts=self._extract_list(final_text, "Alerts"),
                )

            tool_results = []
            for block in response.content:
                if block.type == "tool_use":
                    result = self.dispatch_tool(block.name, block.input)
                    if isinstance(result, list):
                        all_data.extend(result)
                    if block.name == "execute_query":
                        sql_queries.append(block.input.get("sql", ""))

                    tool_results.append({
                        "type": "tool_result",
                        "tool_use_id": block.id,
                        "content": json.dumps(result, ensure_ascii=False, default=str),
                    })

            messages.append({"role": "assistant", "content": response.content})
            messages.append({"role": "user", "content": tool_results})

    def _extract_list(self, text: str, section: str) -> list[str]:
        """Extract items from a text section"""
        import re
        if section not in text:
            return []
        section_text = text.split(section)[1].split("\n\n")[0]
        return [
            line.strip("- •*").strip()
            for line in section_text.splitlines()
            if line.strip() and line.strip().startswith(("-", "•", "*", "1", "2"))
        ]

Integration with 1C via API

import requests

class OneCIntegration:
    """Integration with 1C:Enterprise via HTTP service"""

    def __init__(self, base_url: str, username: str, password: str):
        self.base_url = base_url
        self.auth = (username, password)

    def get_sales_report(self, date_from: str, date_to: str) -> list[dict]:
        """Get sales report from 1C"""
        response = requests.get(
            f"{self.base_url}/hs/api/v1/sales",
            auth=self.auth,
            params={"dateFrom": date_from, "dateTo": date_to)
        )
        return response.json()

    def get_inventory_status(self) -> list[dict]:
        """Get item balances"""
        response = requests.get(
            f"{self.base_url}/hs/api/v1/inventory",
            auth=self.auth,
        )
        return response.json()

    def get_budget_execution(self, period: str) -> dict:
        """Get budget execution"""
        response = requests.get(
            f"{self.base_url}/hs/api/v1/budget/{period}",
            auth=self.auth,
        )
        return response.json()

Automatic Reports and Alerts

import asyncio
from datetime import datetime

class ERPAlertSystem:
    """Automatically detects anomalies and sends alerts"""

    def __init__(self, assistant: ERPAssistant):
        self.assistant = assistant

    async def daily_health_check(self) -> list[str]:
        """Daily audit of key metrics"""
        checks = [
            "Are there items with critically low stock (less than a week)?",
            "Are any department budgets exceeded this month?",
            "Is there overdue receivables older than 30 days?",
            "Which indicators differ significantly from last month?",
        ]

        alerts = []
        for check in checks:
            result = self.assistant.answer(check)
            if result.alerts:
                alerts.extend(result.alerts)

        return alerts

    def generate_executive_report(self, period: str = "month") -> str:
        """Generate an executive report for management"""
        result = self.assistant.answer(
            f"Prepare an executive report for {period}: key metrics, trends, risks, recommendations",
            user_role="ceo"
        )
        return result.interpretation

Practical Case: Manufacturing Company

Our client — a factory using 1C:ERP, with 15 managers and one part-time BI analyst. The analyst was drowning in ad-hoc queries. We deployed the assistant in two weeks. We integrated with 1C via HTTP service and set up an alert system for critical deviations.

Typical questions the assistant now solves in seconds:

• "When will materials for production X run out at the current pace?"
• "Which shop floor exceeds planned expenses?"
• "Top 5 customers by revenue for the quarter with dynamics"

Results after six months:

• Ad-hoc queries to the analyst dropped from 25 to 7 per week (72% reduction)
• Time to get a management answer fell from 1–2 hours to 30 seconds — 50x faster
• Daily alert on critical metrics prevented two cash gaps, saving the company $12,000

Why an AI Assistant for ERP is More Than Just Text-to-SQL

Simple Text-to-SQL models make mistakes in JOINs and context. Our agent loop with multiple queries allows data refinement: first get total amount, then break down by warehouse, then request trend. The LLM chooses the next action based on tools and history. This reduces hallucination and boosts accuracy to 90% on complex queries — 40% higher than standard solutions without an agent loop.

How to Ensure Query Security?

We apply three protection levels. First — parsing: the code blocks any query that does not start with SELECT. Second — a read-only database user with no INSERT/UPDATE/DELETE rights. Third — logging all generated SQL to an audit table. If the model attempts DDL, the parser raises an exception before execution.

How to Implement an AI Assistant in 3 Steps?

1. Data schema analysis — gain access to the ERP schema, identify key KPIs.
2. Assistant configuration — set up prompts, tools, and integration with 1C or another ERP.
3. Testing and launch — verify accuracy on 20 typical queries, enable the alert system.

"According to the factory data, time spent on routine reports decreased by 72%."

Timelines

What's Included in the Work

• Documentation: database schema description, prompts, and constraints.
• Access: read-only user, logging, monitoring dashboard.
• Training: a session for managers and the analyst.
• Support: 2 weeks post-production, fixing inaccuracies in generation.

Pricing is determined individually — depends on ERP schema complexity, number of agents, and need for fine-tuning. Contact us for a consultation and a turnkey project estimate. Order implementation now — our engineers have years of experience integrating with 1C and other ERPs.

LLM Development: Fine-Tuning, RAG, Agents, and Production Deployment

Using GPT‑4 or Claude 3.5 Sonnet through a public API is not a solution — it's just a tool. When the requirement is to "make it like ChatGPT, but on our data," there is a real engineering challenge behind it: from prompt engineering to training a 70B model on your own infrastructure. End-to-end LLM solution development is a complex stack, and we have been doing it for over 5 years. During this time, we have completed over 20 projects in generative AI: from RAG systems for legal departments to custom support agents. Where exactly your task falls depends on data, latency requirements, budget, and how critical confidentiality is.

A typical situation: the client has already tried ChatGPT, but results are unstable — sometimes accurate, sometimes hallucinating. Or they need integration into a corporate portal while complying with security policies. Let's break down each layer of the stack in detail — from RAG to production deployment.

Why Do RAG Systems Break and How to Fix It?

RAG (Retrieval-Augmented Generation) looks simple: find relevant documents, put them in context, get an answer. In practice, it fails in several places.

Chunking without overlap. Classic mistake: chunk_size=512, overlap=0. If the answer lies across two chunks, retrieval won't find either with sufficient confidence. Solution: overlap 15–25% of chunk_size, or better yet, sentence-aware splitting with spaCy or NLTK instead of naive character splitting.

Poor embedder. text-embedding-ada-002 is good for general use, but on legal or medical texts, specialized models like E5-large-v2, BGE-M3, or fine-tuned sentence-transformers on domain data outperform it. Recall@5 differences can be 15–25%.

No re-ranking. Vector search optimizes for speed, not relevance. A cross-encoder re-ranker (ms-marco-MiniLM-L-6-v2, bge-reranker-large) after initial retrieval improves top-3 accuracy with acceptable latency (+50–150ms). This is often more impactful than improving the embedding model.

Hybrid search. Dense vectors alone work poorly on exact queries: names, SKUs, codes. BM25 (sparse) finds exact matches but misses semantics. Hybrid via RRF (Reciprocal Rank Fusion) is the optimal compromise. Qdrant, Weaviate, and pgvector 0.7+ support hybrid search natively.

When to Fine-Tune Instead of Prompt Engineering?

Prompt engineering solves ~70% of LLM adaptation tasks for a domain. The remaining 30% require fine-tuning. Three indicators: the model ignores a specific output format even with detailed prompting; the task requires deep knowledge of specialized vocabulary (medicine, law); you need to significantly reduce token costs by replacing a large model with a smaller specialized one.

LoRA and QLoRA are the standard for SFT. LoRA adds trainable low-rank matrices to attention layers. A typical configuration for Llama-3 8B: r=64, lora_alpha=128, target_modules=["q_proj","v_proj","k_proj","o_proj"] yields ~0.8% trainable parameters, training on one A100 40GB. QLoRA adds 4-bit quantization (NF4) and allows fine-tuning 70B models on two A100 40GB, though speed drops by half compared to bf16.

DPO instead of RLHF. Direct Preference Optimization requires only (chosen, rejected) pairs, not scalar reward signals. DPOTrainer from the trl library (Hugging Face) implements it in a few dozen lines.

Common mistake. A dataset of 500 examples, 5 epochs, validation loss 0.8 — seems fine. But on test, the model degrades on general instructions. Cause: catastrophic forgetting. Solution: add 10–20% general instruction-following examples (Alpaca, FLAN) to the training set to preserve original capabilities.

How to Choose a Base Model: 8B or 70B?

For most tasks, fine-tuning an 8B model is sufficient. 70B is needed when deep reasoning is required or the 8B baseline does not reach the required quality even after fine-tuning. Inference cost for Llama-3 8B via vLLM on A100 is efficient; the exact cost depends on volume.

What Does PagedAttention Bring to Production?

vLLM is the first choice for serving open-source models. PagedAttention is the key technical innovation: KV-cache is managed like virtual memory in an OS, without fragmentation. This yields 2–4x higher throughput compared to naive HuggingFace Transformers inference. The vLLM documentation confirms that continuous batching and PagedAttention are the standard for high-load LLM services.

Typical numbers on A100 80GB for Llama-3 8B (bf16): 400–600 req/s, P50 latency 200–400ms, P99 latency 600–900ms at concurrency 64. For 70B on two A100 with tensor parallelism: 80–120 req/s, P99 latency 1.5–2.5s. AWQ or GPTQ quantization reduces memory consumption by 2x with quality loss within 1–3%.

Multi-Agent Systems

Agents are LLMs with access to tools: search, code execution, API calls, database interaction. Common patterns:

• ReAct (Reason + Act): the model reasons → chooses a tool → observes the result → reasons again. LangChain and LlamaIndex implement it out of the box.
• Multi-agent orchestration: multiple specialized agents with a coordinator on top. Example: coordinator → researcher (search + summarization) → coder (code generation and execution) → critic (verification). Tools: AutoGen (Microsoft), CrewAI, custom implementation on LangGraph.

In production, agent systems are non-deterministic. Essential: guardrails, step limits, logging of each step, human-in-the-loop for critical actions.

How We Work: Stages, Timeline, Deliverables

What Is Included

We deliver:

• Technical documentation (model card, configs, deployment instructions)
• Access to infrastructure (code repository, trained weights)
• 1 month of post-deployment support (consultations, bug fixes)
• Customer team training (2–3 sessions on system operation)

Timeline: basic RAG prototype — 1–2 weeks. Fine-tuning with customer data — 3–6 weeks (including data preparation). Production system with monitoring and retraining — 2–4 months. Cost is calculated individually based on data volume, model complexity, and infrastructure requirements.

We guarantee the quality of the final model with performance benchmarks and ongoing monitoring. Our engineers have hands‑on experience with dozens of production LLM systems.

Want to evaluate your project? Leave a request — we will prepare a preliminary summary within 1–2 business days. Or get a consultation on choosing the approach: RAG, fine-tuning, or hybrid — we will tell you what works best for you. Contact us to discuss your LLM development needs. Schedule a free consultation today.