MCP Server with LLM Integration

# MCP Application Structure & API Documentation ## Overview This is a comprehensive Model Context Protocol (MCP) application with clean architecture, featuring smart database search capabilities, conversation memory, and multiple API interfaces. ## Architecture Overview ``` ┌─────────────────────────────────────────────────────────────┐ │ CLIENT INTERFACES │ ├─────────────────────┬─────────────────────┬─────────────────┤ │ HTTP Bridge │ Smart Search API │ MCP Server │ │ (Port 8000) │ (Port 8002) │ (MCP Client) │ ├─────────────────────┴─────────────────────┴─────────────────┤ │ SERVICE LAYER │ ├─────────────────────┬─────────────────────┬─────────────────┤ │ Smart Search │ Memory Service │ Chat Service │ │ SQL Service │ Schema Service │ Synthesis │ ├─────────────────────┴─────────────────────┴─────────────────┤ │ REPOSITORY LAYER │ ├─────────────────────┬─────────────────────┬─────────────────┤ │ PostgresRepository │ Vector Store │ Config Mgmt │ ├─────────────────────┴─────────────────────┴─────────────────┤ │ DATA LAYER │ ├─────────────────────┬─────────────────────┬─────────────────┤ │ PostgreSQL DB1 │ PostgreSQL DB2 │ PostgreSQL DB3 │ │ (Remote) │ (Remote) │ (Local) │ └─────────────────────┴─────────────────────┴─────────────────┘ ``` ## Directory Structure ``` Z:\Code\MCP\ ├── 📁 presentation/ # Presentation layer │ └── mcp_server.py # MCP server implementation ├── 📁 services/ # Business logic layer │ ├── smart_search.py # Core smart search orchestration │ ├── schema_service.py # Database schema operations │ ├── sql_service.py # SQL query generation & execution │ ├── semantic_service.py # Vector/semantic search │ ├── synthesis_service.py # Response generation │ └── memory_service.py # Conversation memory management ├── 📁 repositories/ # Data access layer │ └── postgres_repository.py # PostgreSQL data access ├── 📁 utils/ # Utility functions │ └── (various utilities) ├── 📄 Core Files │ ├── config.py # Database configuration │ ├── http_bridge.py # FastAPI HTTP server │ ├── smart_search_api.py # Dedicated smart search API │ └── server.py # Main application server ├── 📁 testing/ # Organized testing suite │ ├── 📁 api/ # API endpoint testing │ │ ├── test_all_endpoints_simple.py # Comprehensive API tester ⭐ │ │ ├── test_streaming.py # Streaming endpoints │ │ └── test_conversation_chat.py # Conversation memory │ ├── 📁 core/ # Core functionality testing │ │ ├── test_core_smart_search.py # Core search testing ⭐ │ │ ├── test_smart_search_simple.py # Basic search validation │ │ └── simple_answer_test.py # Legacy answer testing │ ├── 📁 integration/ # Integration testing │ │ ├── test_complete_server.py # Full stack testing │ │ └── test_mcp.py # MCP protocol testing │ ├── 📁 phase_tests/ # Development phase testing │ │ ├── test_phase1.py # Repository layer │ │ ├── test_phase2.py # Service layer │ │ ├── test_phase3.py # Production features │ │ └── test_phase4.py # Enterprise features │ ├── 📁 legacy/ # Legacy/deprecated tests │ ├── 📄 README.md # Testing documentation │ └── 📄 run_tests.py # Test runner script └── 📄 Documentation ├── FRONTEND_INTEGRATION.md # Frontend integration guide └── APP_STRUCTURE.md # This file ``` ## Core Files & Their Purposes ### 🌐 API Servers #### `http_bridge.py` - Main HTTP API Server (Port 8000) **Purpose**: FastAPI server providing HTTP bridge to MCP functionality - **Chat endpoints**: Basic chat, streaming chat, conversation with memory - **Memory management**: Store, retrieve, search conversation history - **MCP integration**: Access to database tools and capabilities - **Health monitoring**: Service status and model availability #### `smart_search_api.py` - Smart Search API (Port 8002) **Purpose**: Dedicated HTTP API for core smart search functionality - **Direct access** to smart search answer() method - **Database selection**: db1, db2, db3 configuration - **Real model detection**: Integration with Ollama API - **Frontend ready**: CORS enabled for web integration #### `presentation/mcp_server.py` - MCP Server **Purpose**: Model Context Protocol server implementation - **Tool definitions**: Database query, schema, search tools - **Protocol compliance**: Standard MCP interface - **Client integration**: For MCP-compatible clients ### 🧠 Business Logic Layer #### `services/smart_search.py` - Smart Search Orchestrator **Purpose**: Core intelligent search orchestration - **Question classification**: Determines optimal search strategy - **Multi-strategy search**: SQL, semantic, schema, hybrid approaches - **Response synthesis**: Combines results into markdown responses - **Performance tracking**: Processing time and source metrics #### `services/schema_service.py` - Database Schema Service **Purpose**: Database structure analysis and operations - **Schema discovery**: Table, column, relationship analysis - **Metadata extraction**: Data types, constraints, indexes - **Structure queries**: Information schema operations #### `services/sql_service.py` - SQL Service **Purpose**: SQL query generation and execution - **Query generation**: Natural language to SQL conversion - **Safety validation**: SQL injection protection - **Result formatting**: Structured data presentation #### `services/semantic_service.py` - Semantic Search Service **Purpose**: Vector-based semantic search capabilities - **Embedding generation**: Text to vector conversion - **Similarity search**: Vector database operations - **Context matching**: Semantic relevance scoring #### `services/synthesis_service.py` - Response Synthesis Service **Purpose**: Intelligent response generation - **Multi-source synthesis**: Combine SQL, semantic, schema results - **Markdown formatting**: Rich text response generation - **Context optimization**: Relevant information prioritization #### `services/memory_service.py` - Memory Service **Purpose**: Conversation memory management - **Memory storage**: Persistent conversation history - **Context retrieval**: Relevant history for current conversation - **Importance scoring**: Memory relevance weighting ### 🗄️ Data Access Layer #### `repositories/postgres_repository.py` - PostgreSQL Repository **Purpose**: Database access abstraction - **Connection management**: Multi-database configuration - **Query execution**: SQL execution with error handling - **Schema introspection**: Database structure analysis - **Transaction management**: ACID compliance #### `config.py` - Configuration Management **Purpose**: Application configuration and database connections ```python SQLALCHEMY_BINDS = { 'db1': 'postgresql://admin:password@192.168.230.101/defaultdb', 'db2': 'postgresql://admin:password@192.168.230.102/defaultdb', 'db3': 'postgresql://postgres:postgres@localhost/postgres' } ``` ### 🧪 Testing Files #### `test_all_endpoints_simple.py` - Comprehensive API Tester **Purpose**: Complete endpoint validation - **Service availability**: Health checks across all servers - **Endpoint testing**: All HTTP endpoints validation - **Performance monitoring**: Response time tracking - **Error detection**: Failure identification and reporting #### `test_core_smart_search.py` - Smart Search Testing **Purpose**: Core smart search functionality validation - **Direct testing**: Smart search answer() method - **Multiple scenarios**: Various question types - **Performance analysis**: Processing time measurement ## API Documentation ### 🌐 HTTP Bridge API (Port 8000) #### Health & Info ``` GET /health # Service health check GET /api/capabilities # Available MCP tools GET /api/models # Available LLM models ``` #### Chat & Conversation ``` POST /api/echo # Echo test endpoint POST /api/chat # Basic chat POST /api/chat/stream # Streaming chat POST /api/conversation/chat # Chat with memory POST /api/conversation/chat/stream # Streaming chat with memory GET /api/test/stream # Test streaming endpoint ``` #### Memory Management ``` POST /api/memory/store # Store conversation memory POST /api/memory/get # Retrieve memories GET /api/memory/search # Search through memories ``` #### Database Integration ``` POST /api/answer # Original smart search (legacy) ``` ### 🔍 Smart Search API (Port 8002) #### Core Endpoints ``` GET /health # Health check GET /api/info # API information GET /api/databases # Available databases GET /api/models # Available LLM models POST /api/smart-search # Core smart search ``` #### Smart Search Request Format ```json { "question": "What tables are available in the database?", "database": "db3", // db1, db2, or db3 "include_sql": true, // Include SQL queries "include_semantic": true, // Include semantic search "include_schema": true // Include schema information } ``` #### Smart Search Response Format ```json { "success": true, "question": "What tables are available?", "database": "db3", "answer_markdown": "## Database Structure\n\nThe database contains...", "strategy": "schema_only", // sql, semantic, hybrid, schema_only "sql_queries": [], // SQL queries executed "semantic_results": [], // Semantic search results "sources_used": {}, // Source utilization metrics "processing_time": 0.25, // Processing time in seconds "error": null // Error message if any } ``` ## Database Configuration ### Database Connections - **db1**: PostgreSQL Remote 1 (192.168.230.101) - **db2**: PostgreSQL Remote 2 (192.168.230.102) - **db3**: PostgreSQL Local (localhost) - Default ### Supported Features - **pgvector extension**: Vector similarity search - **Information schema**: Metadata queries - **ACID transactions**: Data consistency - **Connection pooling**: Performance optimization ## Search Strategies ### 1. SQL Strategy - **Use case**: Structured data queries, aggregations, specific lookups - **Process**: Natural language → SQL generation → Execution → Results - **Examples**: "How many users are there?", "Show recent orders" ### 2. Semantic Strategy - **Use case**: Contextual search, content discovery, fuzzy matching - **Process**: Query embedding → Vector similarity → Relevant documents - **Examples**: "Find documents about pricing", "Similar customer issues" ### 3. Schema Strategy - **Use case**: Database structure questions, metadata queries - **Process**: Schema introspection → Structure analysis → Documentation - **Examples**: "What tables exist?", "Show table relationships" ### 4. Hybrid Strategy - **Use case**: Complex queries requiring multiple approaches - **Process**: Multi-strategy execution → Result combination → Synthesis - **Examples**: "Analyze sales trends and find related documentation" ## Memory System ### Memory Storage - **Conversation tracking**: Per-conversation memory isolation - **Importance scoring**: Automatic relevance weighting - **Persistent storage**: Database-backed memory - **Context retrieval**: Automatic relevant history inclusion ### Memory Features - **Search capabilities**: Query conversation history - **Context limits**: Configurable history inclusion - **Role tracking**: User/assistant message separation - **Metadata support**: Extensible memory attributes ## Performance Characteristics ### Response Times (from testing) - **Health checks**: ~0.01s - **Simple queries**: ~0.02s - **Basic chat**: ~0.5s - **Smart search**: ~0.25s - **Complex operations**: ~2.5s - **Streaming**: Progressive response ### Model Integration - **Ollama models**: Real-time detection - **Available models**: gemma3:12b, gemma:latest, gpt-oss:20b, bge-large:latest, mistral:latest - **Default model**: mistral:latest - **Fallback handling**: Graceful degradation ## Frontend Integration ### React Integration Ready - **CORS enabled**: Cross-origin requests supported - **JSON APIs**: Standard REST interfaces - **Streaming support**: Server-sent events for real-time responses - **Error handling**: Comprehensive error responses ### Example Usage ```javascript // Smart Search const result = await fetch('http://localhost:8002/api/smart-search', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ question: "What tables exist?", database: "db3" }) }); // Conversation Chat const response = await fetch('http://localhost:8000/api/conversation/chat', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ message: "Hello", conversation_id: "user-123" }) }); ``` ## Development & Testing ### Running the Application ```bash # Start HTTP Bridge (Port 8000) py http_bridge.py # Start Smart Search API (Port 8002) py smart_search_api.py ``` ### Testing the Application ```bash # Quick health check (recommended) py testing/api/test_all_endpoints_simple.py # Test core functionality py testing/core/test_core_smart_search.py # Run priority tests py testing/run_tests.py --priority # Run specific category py testing/run_tests.py --category api py testing/run_tests.py --category core py testing/run_tests.py --category integration # Run all tests py testing/run_tests.py --all ``` ### Current Status - **✅ 94.4% endpoint success rate** (17/18 working) - **✅ Real-time model detection** - **✅ Multi-database support** - **✅ Memory system operational** - **✅ Smart search strategies working** - **❌ 1 legacy endpoint needs fixing** (/api/answer) ## Future Enhancements ### Planned Features - **Caching layer**: Redis integration for performance - **Authentication**: API key and JWT support - **Rate limiting**: Request throttling - **Analytics**: Usage pattern tracking - **Auto-scaling**: Container orchestration - **Advanced security**: SQL injection prevention enhancement ### Architecture Evolution - **Microservices**: Service decomposition - **Event-driven**: Async message processing - **Monitoring**: Observability stack - **CI/CD**: Automated deployment pipeline --- *Last updated: September 2025* *API Version: 1.0.0* *MCP Protocol: Compatible*