bench get-app https://github.com/Gifted87/Virgo-AI-Assistant-for-ERPNext

[![Listed on Frappe Gems](https://frappegems.com/api/method/frappe_gems.seo.badge?app=Gifted87%2FVirgo-AI-Assistant-for-ERPNext)](https://frappegems.com/gems/apps/Gifted87/Virgo-AI-Assistant-for-ERPNext)

🌟 Virgo: AI Database & Server Assistant for ERPNext

Virgo is a secure, interactive AI database and server assistant integrated into ERPNext. Leveraging a ReAct (Reasoning and Acting) agent framework, Virgo is fully LLM-agnostic and compatible with any OpenAI-compliant API endpoint (including DeepSeek, local models via Ollama/vLLM, Groq, or OpenAI). Virgo lets system administrators query tables, perform complex data calculations, plot charts, and execute server scripts using plain natural language—all with real-time security approvals for modifying actions.

---

🏗️ Core Architecture & Flow

Virgo operates as a bridge between the user interface, the LLM (whether cloud-based or locally hosted), and the ERPNext database/server environment. It streams its reasoning process and tool executions to the client using Server-Sent Events (SSE).

1. The ReAct Loop (Reasoning + Acting)

sequenceDiagram
    autonumber
    actor User
    participant FE as React Frontend
    participant BE as Frappe (virgo.py)
    participant LLM as LLM (OpenAI Compatible)
    participant DB as MariaDB (ERPNext)

    User->>FE: Enter prompt (e.g., "Find top wines sold last December")
    FE->>BE: POST run_ai_chat (Initiates SSE Stream)

    rect rgb(20, 24, 33)
        note right of BE: SSE Stream / ReAct Loop
        BE->>LLM: Send history + system prompt + tool definitions
        LLM-->>BE: Return reasoning_content (Thought) & tool_calls
        BE-->>FE: Stream 'thought_delta' & 'tool_call' events

        alt Tool is Read-Only (SELECT/list_tables/etc.)
            BE->>DB: Execute read-only SQL query / python check
            DB-->>BE: Return result
            BE->>LLM: Provide observation data
        else Tool requires Modification Approval
            BE-->>FE: Stream 'permission_request' event & PAUSE
            FE->>User: Display prompt to "Approve & Run" / "Reject"
            User->>FE: Click "Approve & Run"
            FE->>BE: POST execute_approved_tool
            BE->>DB: Execute modifying Python / SQL & commit
            BE-->>FE: Return operation status (success/error)
            FE->>BE: Re-trigger run_ai_chat with observation
        end
    end

    BE-->>FE: Stream 'done' event with updated history
    FE-->>User: Display final response & downloadable reports (.xlsx/.pdf)

2. Action Authorization Safety Check

All SQL statements and Python commands are analyzed before execution:

graph TD
    A[Agent requests execution] --> B{Does tool modify data?}

    B -- "No (e.g., SELECT, SHOW, describe)" --> C[Execute instantly & return result]

    B -- "Yes (e.g., UPDATE, DELETE, or run_python)" --> D[Stream permission_request to FE & Pause]

    D --> E{User Decision}

    E -- "Approve & Run" --> F[Call execute_approved_tool API]
    F --> G[Execute with full database commit]
    G --> I[Return result to chat history]

    E -- "Reject Action" --> H[Return rejection state to Agent]

---

📸 Feature Walkthrough

1. Main Chat Interface

Virgo provides a dark-themed interactive chat console. Users can ask questions directly or click quick action buttons (like "Find User tables" or "Plot sales chart") to prompt the assistant.

2. Summaries and Interactive Reports

When asked for calculations or reports, Virgo parses the data, draws plots (using matplotlib), and compiles structured tables. It can write Python code to generate download links for Excel (.xlsx), Word (.docx), or PDF files, which are saved in the ERPNext public downloads directory.

3. Action Authorization Dialog

For actions that modify database entries or execute Python code (such as renaming records or modifying configurations), Virgo intercepts the tool call, displays the code snippet, and prompts the administrator for permission.

4. Interactive Execution Progress

Once the administrator clicks Approve & Run, the interface updates to reflect that permission was granted, starts executing the Python script or SQL statement, and resumes the streaming interaction.

---

🔌 API Reference (aqi)

Virgo exposes two primary server-side APIs through Frappe for chat interactions and approved tool executions.

1. run_ai_chat

Initiates or resumes a chat session. Streams events as they occur in real-time.

• Endpoint: /api/method/ai_assistant.ai_assistant.page.virgo.virgo.run_ai_chat
• Method: POST
• Access: System Manager role required.
• Headers: Standard Frappe session credentials or bearer tokens.
• Request Arguments:

  {
    "prompt": "Show me the wines we sold most last december",
    "history": "[]", 
    "api_key": "sk-..." 
  }
  

  (Note: history must be a JSON-stringified array of previous message structures; api_key is optional if configured on the server).

Server-Sent Event (SSE) Stream Structure

The endpoint returns text/event-stream. Each message is prefixed with data: and contains a JSON object: * {"type": "thought_delta", "content": "..."}: The reasoning process stream (e.g., thinking paths from reasoning models like DeepSeek-R1 or o1/o3). * {"type": "content_delta", "content": "..."}: The user-facing conversational response stream. * {"type": "tool_call", "name": "...", "arguments": "...", "id": "..."}: Triggered when the agent decides to invoke a database tool. * {"type": "permission_request", "name": "...", "arguments": "...", "id": "..."}: Sent when a tool execution modifies the database and requires user consent. The stream pauses until authorized. * {"type": "observation", "content": "...", "plot_url": "...", "id": "..."}: Represents the result of a tool execution. * {"type": "done", "history": [...]}: Final message indicating completion, containing the full conversation history. * {"type": "error", "content": "..."}: Error message if the API key is missing or standard network errors occur.

---

2. execute_approved_tool

Executes database-modifying scripts or SQL queries after the user approves them in the frontend dialog.

• Endpoint: /api/method/ai_assistant.ai_assistant.page.virgo.virgo.execute_approved_tool
• Method: POST
• Access: System Manager role required.
• Request Arguments:

  {
    "tool_name": "run_python",
    "tool_args": {
      "code": "import frappe\nwarehouse = frappe.get_doc('Warehouse', 'VIP Bar - SL&B')\n..."
    }
  }
  

• Success Response:

  {
    "message": {
      "success": true,
      "stdout": "Warehouse renamed successfully",
      "stderr": "",
      "plot_url": null,
      "download_urls": {}
    }
  }
  

---

🛠️ Available Agent Tools

Virgo has access to four core tools within the ERPNext context:

Tool Name	Arguments	Description	Permission Level
list_tables	search_query (optional)	Lists table names in the database matching a string.	Auto-Execute
get_table_schema	table_name	Returns columns, types, and constraints for a table.	Auto-Execute
run_sql_query	sql_query	Executes SQL queries. If it contains read-only keywords (SELECT, SHOW, etc.), it executes automatically. If it contains write operations (INSERT, UPDATE, ALTER, etc.), it requires approval.	Auto-Execute (Read) / Requires Approval (Write)
run_python	code	Executes Python code under the Frappe environment. Allows calculations, pandas operations, plotting with matplotlib, and document generation.	Requires Approval

---

⚙️ Configuration & Setup

LLM Endpoint & API Key Configuration

Virgo is designed to be LLM-agnostic. You can use any OpenAI-compatible API endpoint by configuring the base URL, model name, and API key.

By default, the backend environment variables are named with a DEEPSEEK_ prefix, but they accept configurations for any provider (e.g. Local Ollama, Groq, OpenAI, etc.).

Environment Variable	Purpose	Default Value	Example for Local Ollama	Example for OpenAI
DEEPSEEK_BASE_URL	The API Endpoint base URL	https://api.deepseek.com	http://localhost:11434/v1	https://api.openai.com/v1
DEEPSEEK_MODEL	The model name to request	deepseek-chat	llama3 or qwen2.5-coder	gpt-4o
DEEPSEEK_API_KEY	Authentication key	""	ollama (ignored by Ollama)	sk-proj-...

Virgo checks for configuration values in the following locations, in order:

1. Direct Parameter: Provided during the chat call in the settings menu.
2. Frappe Configuration: Inside common_site_config.json:

   {
     "deepseek_api_key": "your-api-key-here"
   }
   

3. Environment Variables: Set in your terminal or process manager:

   export DEEPSEEK_API_KEY="your-api-key-here"
   export DEEPSEEK_BASE_URL="http://localhost:11434/v1"
   export DEEPSEEK_MODEL="qwen2.5-coder"
   

4. Local Env File: In .env inside the ai_assistant app folders:

   DEEPSEEK_API_KEY=your-api-key-here
   DEEPSEEK_BASE_URL=your-custom-endpoint-here
   DEEPSEEK_MODEL=your-model-name-here