Can’t Write SQL or Debug Logs at 2 AM? Meet Our Production‑Ready Open‑Source AI Assistant

Problem

Business users often cannot write SQL and need to ask natural‑language questions such as “which host had the most alerts last week?”. Operators need to analyse thousands of log lines during incidents. The three practical blockers are:

Generated SQL or commands lack an execution authority check.

API keys and SSH passwords are often stored in plain text configuration files.

When a failure occurs there is no reliable audit trail of what actions were taken.

Solution Overview (xi‑ops)

xi‑ops is a self‑contained platform that bundles “intelligent querying” and “intelligent operations” behind a controlled boundary providing deployment, authentication, and audit capabilities.

Scenario 1 – Querying without writing SQL

Natural‑language question → LLM generates SQL → platform gate executes the statement → result is returned as a table or chart together with a plain‑language summary. Execution rights are part of the product; the system does not merely output SQL for copy‑paste.

Scenario 2 – Log analysis and root‑cause suggestion

Uploaded or pasted logs are pre‑processed, summarised, and abnormal patterns are detected. The AI produces root‑cause hypotheses and actionable steps, cross‑referencing internal manuals and historical cases. When the host is reachable the system may collect additional read‑only evidence (e.g., disk or memory metrics) to strengthen the analysis. Commands that would change state, such as systemctl restart, are deliberately rejected – analysis can be automated, but remediation must remain manual.

Deliberately Excluded Features

Automatic repair, package installation, or configuration changes.

Browser‑based WebShell.

Direct integration with external MySQL/Oracle or unified alert platforms.

Embedding MCP’s stdio or adding an external Nacos gateway.

These omissions keep the focus on query, analysis, and operable tooling that can be containerised and started with a single Docker command.

Architecture

The design favours a single‑machine docker compose up setup for rapid iteration rather than a full micro‑service registry.

Inside the Box (core services)

Vue 3 frontend served by Nginx.

Spring Boot backend.

PostgreSQL with the pgvector extension; vector data share the same database but are isolated by schemas platform, business, and vector.

Outside the Box (extensible capabilities)

Large language model gateway.

Embedding service.

Remote MCP.

Operational hosts accessed via SSH.

All external components are accessed through unified façade gateways – LlmGateway , McpGateway , and SshGateway . Business code only sees the façade and scenario identifiers; switching providers (e.g., DeepSeek → self‑hosted vLLM) requires only configuration changes.

Technical Details

Server‑Sent Events (SSE) stream conversational responses, query summaries, and operation reports. Nginx proxy buffering must be disabled to avoid “typewriter” buffering.

Chat and embedding services are deployed separately because many chat APIs lack vector capabilities.

Backend layering follows the direction bootstrap → trigger → case → domain ← infrastructure. Packages are split by domain ( auth, chat, text2sql, ops, knowledge, llm, mcp, host) to keep SQL generation, SSH handling, and key‑policy logic isolated.

Frontend uses Vue 3 sidebars segmented by domain (dialogue, querying, operations, host management, knowledge base, metadata, LLM/MCP administration).

Architectural Principles

Separate generation from execution. The platform acts as a gate so that model‑generated statements cannot run unchecked.

Encrypt transport and store keys as ciphertext. Keys are never exposed in APIs or logs.

Backend‑enforced authentication. Horizontal privilege checks ensure resources are owned by the requesting user.

Hot‑reloading configuration does not abort ongoing SSE streams. New requests see the updated config while existing streams continue.

All external tools, hosts, and models are accessed via gateway façade with audit logging.

Platform stays lightweight; variable models, tools, and hosts remain outside the box, accessed through gateways, while execution rights stay inside the platform.

First‑Phase Feature List

Platform Basics

Login authentication with JWT (access + refresh tokens).

Roles admin and user with backend‑enforced checks.

Multi‑turn conversation via SSE and Markdown rendering.

Session management with ownership verification and deletion.

Comprehensive audit trails for login, queries, operations, SSH, and tool calls.

Intelligent Querying (Text2SQL)

Conversational flow: question → SQL → controlled execution → table/graph + natural‑language summary.

Metadata layer provides field semantics and synonym mapping.

Few‑shot examples with up/down‑vote feedback to improve bad cases.

Secure execution limited to SELECT statements, whitelist tables, LIMIT, read‑only database accounts, and query timeout.

Intelligent Operations

Log analysis: upload or paste logs, preprocessing, summarisation, and anomaly detection.

Root‑cause and solution generation using a Retrieval‑Augmented Generation (RAG) knowledge base with historical cases.

Markdown report generation; manual confirmation required before persisting the report.

Host SSH access (direct or via jump host) with read‑only whitelist data collection.

Knowledge Base

Document ingestion pipeline: split → vectorise → store in pgvector.

Similarity search for retrieval/Q&A the system refuses to answer when no evidential basis is found.

LLM / MCP Gateways

LLM gateway supports multiple models, per‑scenario binding, and encrypted keys stored in configuration.

MCP gateway provides remote MCP access, tool synchronization, and scenario whitelist; query functions do not expose tools by default.

Deployment

Multi‑stage Docker images combined with Docker‑Compose (PostgreSQL, backend, frontend). Build, start, and smoke‑test scripts are located in the repository’s docs and deploy directories (https://gitcode.com/qq_37953312/xi-ops).