Content
# Tool List
## Project Overview
This project implements a bridge between the Xiao Zhi robot and multiple local PostgreSQL knowledge bases, allowing ESP32 voice devices to query engineering documents in natural language through the official MCP service (WebSocket).
> This version (-OK) is a production-stable version, which adds connection pooling, LRU caching, Ollama retry, and multi-database support compared to the original version, and integrates a complete document re-ingestion and OCR maintenance toolset.
## System Architecture
```
ESP32 Voice Device
↓ Voice→Text
Xiao Zhi Robot (Official Service)
↓ WebSocket (wss://api.xiaozhi.me/mcp/)
mcp_server_multi.py ← Multi-Database MCP Server (Local Resident)
↓ psycopg2 (Connection Pool ThreadedConnectionPool)
PostgreSQL (pgvector)
├── ahow_db AHOW Internal Documents (Construction Safety, Quality Control, and Penalty Items)
├── ahow_rag_pcc Public Engineering Committee (PCC) Regulatory Documents
└── mrt_knowledge_rag MRT Technical Documents
query_server_enhanced.py ← Independent Flask Query Service (ahow_db, port 11451)
management_tools/ ← Document Vectorization Management GUI (Three-Library Version)
```
## Project Structure
```
2-MCP - (Interface) -OK/
├── mcp_server_multi.py # Multi-Database MCP Server v2.1 (Recommended)
├── query_server_enhanced.py # Enhanced Flask Query Service (ahow_db, port 11451)
├── create_hnsw_indexes.sql # HNSW Vector Index Creation SQL (Performance Optimization)
├── start_all_services.bat # Unified Service Manager (Interactive Menu)
├── set_env.bat # Environment Variable Setting Script
│
├── management_tools/
│ ├── file_manager_gui.py # Three-Database File Vectorization GUI v2.1 (Dark Theme)
│ ├── main_menu.bat # Tool Menu
│ └── start_file_manager.bat # Quick Launch GUI
│
├── _reingest_with_ocr.py # Complete Re-ingestion Tool with OCR
├── _reingest_missing_pdfs.py # Supplementary Re-ingestion of Missing PDFs
├── _match_pdfs.py # PDF Comparison Tool
├── _ocr_poc.py # OCR Proof-of-Concept Script
├── _reingest_skipped.tsv # List of Skipped Files
│
├── downloads/ # Downloaded PDF / MD Files
├── .env # Environment Variables (Not Included in Version Control)
└── README.md # This Document
```
## Quick Start
### Method 1: Multi-Database MCP Server (Recommended)
```bash
# Ensure Ollama is started and .env is set with MCP_WS_URL
python mcp_server_multi.py
```
### Method 2: Unified Service Manager
```bat
# Run as administrator
start_all_services.bat
```
Menu Options:
| Option | Function |
|------|------|
| 1 | Start Enhanced Query Server (Flask port 11451) |
| 2 | Start Multi-Database MCP Server (Recommended) |
| 3 | Start Complete Service (Query Server + MCP Server) |
| 4 | Restart All Services |
| 5 | Stop All Services |
| 6 | View Status |
### Method 3: File Management GUI
```bat
management_tools\start_file_manager.bat
```
## MCP Server v2.1 Core Features
### Three Knowledge Bases and MCP Tools
| Database | MCP Tool Name | Description | Table Structure |
|--------|-------------|------|--------|
| `ahow_db` | `query_ahow_knowledge` | AHOW Internal Engineering Documents | `pdf_chunks` |
| `ahow_rag_pcc` | `query_pcc_documents` | PCC Public Engineering Regulatory Documents | `document_chunks` |
| `mrt_knowledge_rag` | `query_mrt_knowledge` | MRT Technical Documents | `chunks` |
### v2.1 Performance Improvement Items
1. **ThreadedConnectionPool** — 2–5 resident connections per database, replacing new connections each time
2. **Embedding LRU + TTL Cache** — Avoid repeated Ollama calls (TTL default 30 minutes)
3. **Ollama Retry** — Up to 3 retries, exponential backoff (1s / 2s)
4. **Score Threshold Filtering** — `SCORE_MIN = 1.3`, low-score results not returned
5. **Startup Preheating** — Ollama empty query + database count statistics
6. **run_in_executor** — Query changed to asynchronous, not blocking event loop
7. **Friendly Error Prompt** — Return Chinese prompt on failure, not original exception message
### Embedding Model
```
Source: Local Ollama (zero API fee)
Model: qwen3-embedding:4b
Endpoint: http://localhost:11434/api/embed
Similarity Operator: <#>
Value Range: High correlation ~1.58–1.67, unrelated ~1.21–1.22
```
### Message Length Limit
```python
CHUNK_TEXT_MAX = 1500 # Single chunk truncation limit (characters)
RESPONSE_TEXT_MAX = 12000 # Entire response limit (characters)
```
## Environment Variables (.env)
```env
PYTHONIOENCODING=utf-8
# PostgreSQL
PG_HOST=localhost
PG_PORT=5432
PG_DATABASE=ahow_db
PG_USER=<Your Username>
PG_PASSWORD=<Your Password>
# Xiao Zhi MCP WebSocket (need to obtain token from Xiao Zhi platform)
MCP_WS_URL=wss://api.xiaozhi.me/mcp/?token=<YOUR_TOKEN>
# Flask Query Service
QUERY_SERVER_URL=http://localhost:11451/query
```
## Vector Index Creation (First Deployment)
```sql
-- Execute create_hnsw_indexes.sql to create HNSW index
\i create_hnsw_indexes.sql
```
HNSW index can significantly improve vector similarity query performance, recommended to execute after data import.
## Maintenance Toolset (Underscore Prefix Scripts)
| Script | Purpose |
|------|------|
| `_reingest_with_ocr.py` | Complete re-vectorization with OCR (handle scanned PDFs) |
| `_reingest_missing_pdfs.py` | Compare downloads/ to find un-vectorized PDFs and supplement ingest |
| `_match_pdfs.py` | Compare local PDFs with existing documents in database |
| `_ocr_poc.py` | OCR function verification script (proof-of-concept) |
| `_reingest_skipped.tsv` | Record skipped file list (file name, skip reason) |
## query_server_enhanced.py Function Description
Provide REST API for MCP Server or external programs to query `ahow_db`:
- **Connection Pool**: psycopg2 connection pool (2–20 connections)
- **Circuit Breaker Pattern**: Prevent cascading failures, automatic recovery
- **Chinese Encoding Processing**: Automatic detection of GBK / Big5 / UTF-8
- **Embedding Cache**: LRU strategy cache Ollama embedding results (TTL 7200 seconds)
- **Performance Monitoring**: P95 / P99 latency statistics, cache hit rate
**API Endpoint**:
```
POST http://localhost:11451/query
Content-Type: application/json
{ "query": "施工围籬的設置規範" }
```
## System Requirements
| Component | Version / Description |
|------|-------------|
| Python | 3.8+ |
| PostgreSQL | 16, including pgvector extension |
| Ollama | Locally run, downloaded `qwen3-embedding:4b` |
| psycopg2 | `pip install psycopg2-binary` |
| websockets | `pip install websockets` |
| python-dotenv | `pip install python-dotenv` |
| PyMuPDF（fitz）| `pip install pymupdf` (required by file_manager_gui) |
| tiktoken | `pip install tiktoken` (required by file_manager_gui) |
