CHATBOT_INTEGRATION_GUIDE.md•18 kB
# Chatbot Integration Guide - MCP Client for sahabat-ai
Complete guide for integrating the SQLite MCP Server with your sahabat-ai chatbot application.
## Table of Contents
1. [Architecture Overview](#architecture-overview)
2. [Quick Start](#quick-start)
3. [Installation](#installation)
4. [Running the MCP HTTP Server](#running-the-mcp-http-server)
5. [Using the MCP Client](#using-the-mcp-client)
6. [Integrating with sahabat-ai](#integrating-with-sahabat-ai)
7. [Full Chatbot Example](#full-chatbot-example)
8. [API Reference](#api-reference)
9. [Troubleshooting](#troubleshooting)
---
## Architecture Overview
```
┌─────────────────────────────────────────────────────────────┐
│ Your Chatbot (Python) │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SQLiteChatbot Class (chatbot_example.py) │ │
│ │ - Manages conversation with sahabat-ai │ │
│ │ - Handles function calling │ │
│ │ - Executes database operations │ │
│ └──────────────────────────────────────────────────────┘ │
│ ↓ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ sahabat-ai LLM (OpenAI-compatible API) │ │
│ │ - Receives natural language queries │ │
│ │ - Returns function calls (tool use) │ │
│ └──────────────────────────────────────────────────────┘ │
│ ↓ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ MCP Client Library (mcp_client.py) │ │
│ │ - Makes HTTP requests to MCP server │ │
│ │ - Handles responses and errors │ │
│ └──────────────────────────────────────────────────────┘ │
│ ↓ HTTP │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ MCP HTTP Server (http_wrapper.py) │
├─────────────────────────────────────────────────────────────┤
│ FastAPI Application │
│ - REST API endpoints for all tools │
│ - Request/response handling │
│ - Error management │
│ ↓ │
│ SQLite Database Operations (db.py) │
│ - CRUD operations │
│ - Query execution │
│ - Schema management │
│ ↓ │
│ SQLite Database (./data/sample_data.db) │
└─────────────────────────────────────────────────────────────┘
```
---
## Quick Start
For the impatient, here's how to get started in 5 minutes:
```bash
# 1. Install dependencies
pip install -r requirements.txt
# 2. Terminal 1: Start the MCP HTTP Server
python http_wrapper.py
# 3. Terminal 2: Run the example chatbot
export SAHABAT_AI_API_KEY="your-api-key"
export SAHABAT_AI_BASE_URL="http://your-sahabat-ai-server"
python chatbot_example.py
```
Then interact with the chatbot:
```
You: Show me all spam numbers with description 'Scam'
Bot: I'll query the database for you... [results]
```
---
## Installation
### Prerequisites
- Python 3.8 or higher
- pip
- sahabat-ai API access (API key and server URL)
- MCP Server running (localhost:8000 by default)
### Step 1: Install Python Packages
```bash
# Install from requirements.txt
pip install -r requirements.txt
# Or install individually
pip install fastapi uvicorn requests httpx openai pydantic
```
### Step 2: Verify Installation
```bash
# Test imports
python -c "from mcp_client import MCPClient; from openai_tools import get_openai_tools; print('✅ All imports successful')"
```
---
## Running the MCP HTTP Server
The MCP HTTP Server exposes the FastMCP server as REST API endpoints.
### Start the Server
```bash
# Method 1: Direct Python
python http_wrapper.py
# Method 2: Using uvicorn
uvicorn http_wrapper:app --reload --port 8000
# Method 3: Custom host/port
uvicorn http_wrapper:app --host 0.0.0.0 --port 8000
```
### Verify Server is Running
```bash
# Check health
curl http://localhost:8000/health
# Expected response:
# {
# "status": "healthy",
# "database_connected": false,
# "service": "SQLite MCP HTTP Wrapper"
# }
# List available tools
curl http://localhost:8000/tools
```
### API Documentation
FastAPI automatically generates documentation:
- **Swagger UI**: http://localhost:8000/docs
- **ReDoc**: http://localhost:8000/redoc
---
## Using the MCP Client
The `MCPClient` class is your interface to the MCP server.
### Basic Usage
```python
from mcp_client import MCPClient
# Create client (connects to MCP server)
client = MCPClient("http://localhost:8000")
# Open database
client.open_database("./data/sample_data.db")
# Query data
result = client.execute_query("SELECT * FROM Spam_number LIMIT 5")
print(f"Found {result.record_count} records")
for row in result.rows:
print(row)
# Insert data
insert_result = client.insert(
"Spam_number",
{
"mobileNumber": "081234567890",
"Name": "Example User",
"Description": "Test"
}
)
print(f"Inserted with ID: {insert_result.lastID}")
# Close database
client.close_database()
```
### Context Manager Pattern
```python
from mcp_client import MCPClient
# Auto-closes database on exit
with MCPClient("http://localhost:8000") as client:
client.open_database("./data/myapp.db")
tables = client.list_tables()
print(f"Tables: {tables}")
# Database auto-closes here
```
### Error Handling
```python
from mcp_client import MCPClient, MCPClientError
try:
client = MCPClient("http://localhost:8000")
client.open_database("./data/myapp.db")
except MCPClientError as e:
print(f"MCP Error: {e}")
except Exception as e:
print(f"Unexpected error: {e}")
```
---
## Integrating with sahabat-ai
sahabat-ai uses the OpenAI API format, so we can use OpenAI's function calling feature.
### 1. Install OpenAI Client
```bash
pip install openai>=1.0.0
```
### 2. Set Up Credentials
```bash
# Set environment variables
export SAHABAT_AI_API_KEY="your-api-key"
export SAHABAT_AI_BASE_URL="http://your-sahabat-ai-server:port"
# Or use in Python
import os
os.environ["SAHABAT_AI_API_KEY"] = "your-api-key"
os.environ["SAHABAT_AI_BASE_URL"] = "http://localhost:8000"
```
### 3. Initialize OpenAI Client for sahabat-ai
```python
from openai import OpenAI
client = OpenAI(
api_key="your-api-key",
base_url="http://your-sahabat-ai-server:port"
)
# Make a simple request
response = client.chat.completions.create(
model="sahabat-ai",
messages=[{"role": "user", "content": "Hello"}]
)
print(response.choices[0].message.content)
```
### 4. Add Function Tools
```python
from openai_tools import get_openai_tools
# Get all tools as OpenAI function definitions
tools = get_openai_tools()
# Use in API call
response = client.chat.completions.create(
model="sahabat-ai",
messages=[
{
"role": "user",
"content": "Query the database for all scam numbers"
}
],
tools=tools,
tool_choice="auto"
)
# Check if model wants to use a tool
if response.choices[0].message.tool_calls:
for tool_call in response.choices[0].message.tool_calls:
print(f"Calling: {tool_call.function.name}")
print(f"Args: {tool_call.function.arguments}")
```
---
## Full Chatbot Example
The `chatbot_example.py` file provides a complete implementation.
### Quick Example
```python
from chatbot_example import SQLiteChatbot
# Initialize
chatbot = SQLiteChatbot(
api_key="your-api-key",
base_url="http://your-sahabat-ai-server",
mcp_server_url="http://localhost:8000"
)
# Chat with automatic database access
response = chatbot.chat("Show me all users in the database")
print(response)
# Another query
response = chatbot.chat("Add a new user named John with email john@example.com")
print(response)
```
### Interactive CLI
```bash
# Run the built-in CLI
python chatbot_example.py
# It will prompt for:
# 1. sahabat-ai API key
# 2. sahabat-ai base URL
# 3. MCP server URL
# Then you can chat interactively:
# > Show me all spam numbers
# Bot: I'll query the database...
```
### Building Your Own Chatbot
```python
from chatbot_example import SQLiteChatbot
class MyCustomChatbot(SQLiteChatbot):
"""Custom chatbot with specific behavior."""
def _get_default_system_prompt(self) -> str:
"""Override system prompt."""
return """You are a database admin assistant for company X.
You help manage our customer and spam number database.
Be professional and concise."""
# Use it
chatbot = MyCustomChatbot(
api_key="your-api-key",
base_url="http://your-sahabat-ai-server"
)
response = chatbot.chat("How many users do we have?")
print(response)
```
---
## API Reference
### MCPClient Class
#### Initialization
```python
MCPClient(
base_url: str = "http://localhost:8000",
timeout: int = 30
)
```
#### Database Management
```python
# Open database
client.open_database(path: str) -> str
# Close database
client.close_database() -> str
# Health check
client.health_check() -> Dict[str, Any]
```
#### Query Operations
```python
# Execute SELECT query
client.execute_query(
query: str,
parameters: Optional[List[Any]] = None
) -> QueryResult
# QueryResult has:
# - rows: List[Dict[str, Any]]
# - column_count: int
# - record_count: int
# - success: bool
```
#### CRUD Operations
```python
# Insert
client.insert(
table: str,
data: Dict[str, Any]
) -> InsertResult
# InsertResult: lastID, changes, table, success
# Update
client.update(
table: str,
data: Dict[str, Any],
where: str,
where_params: Optional[List[Any]] = None
) -> UpdateResult
# UpdateResult: changes, table, where_clause, success
# Delete
client.delete(
table: str,
where: str,
where_params: Optional[List[Any]] = None
) -> DeleteResult
# DeleteResult: changes, table, where_clause, success
```
#### Schema Management
```python
# Create table
client.create_table(
table: str,
schema: str
) -> str
# List tables
client.list_tables() -> List[str]
# Get table schema
client.get_table_schema(
table: str
) -> List[Dict[str, Any]]
```
### OpenAI Tools
```python
from openai_tools import get_openai_tools, get_tool_by_name
# Get all tools
tools = get_openai_tools() # List of 9 tool definitions
# Get specific tool
tool = get_tool_by_name("execute_query") # Get one tool
# Get tool names
from openai_tools import TOOL_MAP
tool_names = list(TOOL_MAP.keys())
```
---
## Common Use Cases
### 1. Query Data with Natural Language
```python
chatbot = SQLiteChatbot(api_key="...", base_url="...")
# User query
response = chatbot.chat(
"How many spam numbers with description 'Scam' are in the database?"
)
# Bot automatically:
# 1. Opens database
# 2. Calls execute_query
# 3. Returns results in natural language
```
### 2. Insert Data via Chatbot
```python
response = chatbot.chat(
"Add a new spam number: 081234567890, name: John, description: Phishing"
)
# Bot automatically:
# 1. Parses the natural language input
# 2. Calls insert tool with correct parameters
# 3. Confirms the insertion
```
### 3. Batch Operations
```python
# Multiple operations in one conversation
chatbot.chat("Open the spam database")
chatbot.chat("How many records are there?")
chatbot.chat("Show me the top 5 by type")
chatbot.chat("Update record 1 to say 'Blocked'")
```
### 4. Admin Tasks
```python
# View database structure
chatbot.chat("What tables are in the database?")
chatbot.chat("Show me the schema for the KTP table")
# Create new table
chatbot.chat(
"Create a new table called 'blocked_numbers' with fields: "
"id INTEGER PRIMARY KEY, number TEXT, reason TEXT"
)
```
---
## Troubleshooting
### Issue: "Cannot connect to MCP server"
**Solution:**
```bash
# Check if MCP server is running
curl http://localhost:8000/health
# If not, start it
python http_wrapper.py
# Check the URL in MCPClient
client = MCPClient("http://localhost:8000") # Verify this URL
```
### Issue: "No database is open"
**Solution:**
```python
# Always open database before querying
client.open_database("./data/sample_data.db")
# Then use tools
client.execute_query("SELECT * FROM users")
```
### Issue: sahabat-ai API errors
**Solution:**
```python
# Check credentials
import os
print(os.getenv("SAHABAT_AI_API_KEY"))
print(os.getenv("SAHABAT_AI_BASE_URL"))
# Test connection
from openai import OpenAI
client = OpenAI(
api_key="your-key",
base_url="http://your-server"
)
response = client.chat.completions.create(
model="sahabat-ai",
messages=[{"role": "user", "content": "test"}]
)
print(response.choices[0].message.content)
```
### Issue: Tool calls not working
**Solution:**
```python
# Make sure tools are included in request
from openai_tools import get_openai_tools
tools = get_openai_tools() # Get all 9 tools
response = client.chat.completions.create(
model="sahabat-ai",
messages=[...],
tools=tools, # Must include tools
tool_choice="auto" # Enable auto tool selection
)
```
### Issue: SQL errors
**Solution:**
```python
# Use parameterized queries to prevent SQL injection
# Good:
client.execute_query(
"SELECT * FROM users WHERE id = ?",
parameters=[1]
)
# Bad (SQL injection risk):
client.execute_query(
f"SELECT * FROM users WHERE id = {user_id}"
)
```
---
## Performance Tips
1. **Connection Pooling**: For multiple concurrent chatbots, consider connection pooling
2. **Query Optimization**: Use LIMIT and WHERE clauses to limit results
3. **Async Operations**: For high-traffic scenarios, use async client (future enhancement)
4. **Caching**: Cache table schemas and table lists when possible
---
## Security Considerations
⚠️ **Important Security Notes:**
1. **API Keys**: Never commit API keys to version control
```bash
# Use environment variables
export SAHABAT_AI_API_KEY="your-key"
```
2. **Parameterized Queries**: Always use parameters to prevent SQL injection
```python
# Safe
client.execute_query("SELECT * FROM users WHERE name = ?", ["John"])
# Unsafe
client.execute_query(f"SELECT * FROM users WHERE name = '{user_input}'")
```
3. **Database Permissions**: Restrict database file permissions
```bash
chmod 600 ./data/sample_data.db
```
4. **Input Validation**: Validate user input before sending to database
---
## Next Steps
1. ✅ Install dependencies: `pip install -r requirements.txt`
2. ✅ Start MCP server: `python http_wrapper.py`
3. ✅ Test with example: `python chatbot_example.py`
4. ✅ Build your own chatbot using `SQLiteChatbot` class
5. ✅ Deploy to production with proper security measures
---
## Support
For issues or questions:
- Check the [README.md](README.md) for tool documentation
- Review [SAMPLE_DATA_GUIDE.md](SAMPLE_DATA_GUIDE.md) for data structure
- Check error messages and [Troubleshooting](#troubleshooting) section
- Review code examples in [chatbot_example.py](chatbot_example.py)
---
## Example Conversation
```
You: Open the spam database
Bot: I've opened the spam database at ./data/sample_data.db
You: How many records are there?
Bot: Let me query that for you...
The database contains 100 spam number records.
You: Show me the top 5 spam types
Bot: Here are the top 5 spam types:
1. Identity Theft - 10 records
2. Fake Government - 8 records
3. Prize Winner Scam - 7 records
4. Investment Fraud - 7 records
5. Money Transfer Scam - 6 records
You: Add a new spam number
Bot: I'll help you add a new spam number. What are the details?
(Phone number, name, and description)
You: 085234567890, Spam User, Lottery Scam
Bot: ✅ Successfully added the spam number!
ID: 101
Number: 085234567890
Name: Spam User
Description: Lottery Scam
```
Enjoy your MCP-powered chatbot! 🚀