AI Hotel MCP Server

Production-quality MCP (Model Context Protocol) server for an AI-driven hotel booking system with hybrid database architecture.

🎯 Project Overview

This project showcases an intelligent hotel management system where "Poe," an AI entity, orchestrates operations, learns from guest interactions, and makes autonomous decisions. The system demonstrates the benefits of a hybrid database architecture through live performance benchmarking.

Key Features

• 30+ MCP Tools: Complete hotel operations accessible via Claude Code
• Hybrid Architecture: PostgreSQL + MongoDB + Redis working together
• Live Benchmarking: Real-time performance comparisons showing 10-20x improvements with caching
• Realistic Data: 100 guests, 500 bookings, 1000+ conversations ready for testing
• 12 Core Queries: All SQL queries from the PDF specification
• AI Decision Tracking: Complete learning loop implementation

📊 Database Architecture

┌─────────────┐     ┌──────────────┐     ┌─────────┐
│ PostgreSQL  │────▶│ MCP Server   │────▶│  Redis  │
│ (ACID Data) │     │  (Python)    │     │ (Cache) │
└─────────────┘     └──────────────┘     └─────────┘
                           │
                           ▼
                    ┌──────────────┐
                    │   MongoDB    │
                    │ (Documents)  │
                    └──────────────┘

Database Roles

• PostgreSQL: Transactional data (10 tables: Guest, Room, Booking, AIPersonality, AIConversation, AIDecision, AIMemory, GuestProfile, SensorData, ProactiveAction)
• MongoDB: Flexible document storage (Conversation transcripts with nested messages, AI memories with rich content)
• Redis: Ultra-fast caching layer (Guest preferences, Recent sentiment, Sensor readings)

🚀 Quick Start

Prerequisites

• Docker & Docker Compose
• Python 3.11+
• Claude Code (for MCP client)

1. Start Databases

# Start all services (PostgreSQL, MongoDB, Redis)
docker-compose up -d

# Verify all services are healthy
docker ps

# Check logs
docker-compose logs -f postgres
docker-compose logs -f mongodb
docker-compose logs -f redis

2. Verify Database Setup

# Connect to PostgreSQL
docker exec -it hotel-ai-postgres psql -U hotel_admin -d hotel_ai

# List tables (should show 10 tables)
\dt

# Check sample data
SELECT COUNT(*) FROM Guest;  -- Should show 100
SELECT COUNT(*) FROM Booking;  -- Should show 500
SELECT COUNT(*) FROM AIConversation;  -- Should show 1000

\q

# Connect to MongoDB
docker exec -it hotel-ai-mongodb mongosh -u hotel_admin -p hotel_password_2026 --authenticationDatabase admin

use hotel_ai
show collections  -- Should show: conversations, ai_memories
db.conversations.countDocuments()  -- Should show at least 1
exit

# Connect to Redis
docker exec -it hotel-ai-redis redis-cli
PING  -- Should return PONG
exit

3. Install Python Dependencies

# Create virtual environment
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

4. Configure Environment

# Copy example environment file
cp .env.example .env

# Edit .env if needed (defaults should work with Docker)
# For local Docker: hosts are already set to localhost

5. Run MCP Server

# Start MCP server (connects via stdio to Claude Code)
python src/main.py

# Or run in background
nohup python src/main.py > mcp_server.log 2>&1 &

Configure in Claude Code:

Add to your Claude Desktop config file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json Linux: ~/.config/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "hotel-ai": {
      "command": "python",
      "args": ["/absolute/path/to/HotelAiProject/src/main.py"],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "MONGO_HOST": "localhost",
        "REDIS_HOST": "localhost"
      }
    }
  }
}

Test MCP Connection:

In Claude Code, you should see 19 available tools:

• 13 query tools (12 from PDF + get_guest_profile)
• 6 testing/benchmark tools

📁 Project Structure

hotel-ai-mcp-server/
├── docker-compose.yml                 # Multi-container orchestration
├── .env.example                       # Environment template
├── README.md                          # This file
├── requirements.txt                   # Python dependencies
│
├── docker/                            # Docker configuration
│   ├── postgres/init/                 # PostgreSQL initialization
│   │   ├── 01-create-schema.sql      # 10 tables (exact from PDF)
│   │   ├── 02-create-indexes.sql     # 14 performance indexes
│   │   └── 03-seed-data.sql          # Realistic sample data
│   └── mongodb/init/                  # MongoDB initialization
│       └── init-collections.js       # Conversation & memory collections
│
├── src/                               # Source code
│   ├── main.py                       # MCP server entry point ✅
│   ├── config.py                     # Configuration management ✅
│   ├── database/                     # Database clients
│   │   ├── postgres_client.py        # PostgreSQL (12 queries) ✅
│   │   ├── mongodb_client.py         # MongoDB operations ✅
│   │   ├── redis_client.py           # Redis caching ✅
│   │   └── models.py                 # Pydantic models ✅
│   └── mcp/                          # MCP implementation
│       ├── server.py                 # MCP server setup ✅
│       └── tools/                    # MCP tools
│           ├── query_tools.py        # 13 query tools ✅
│           └── testing_tools.py      # 6 benchmark tools ✅
│
└── tests/                            # Test suite
    └── test_database_clients.py      # Database tests ✅

🗄️ Database Schema

PostgreSQL Tables (10 tables)

Core Hotel Data:

• Guest: 100 guests with profiles
• Room: 150 rooms (10 floors × 15 rooms)
• Booking: 500 bookings (350 completed, 50 active, 100 future)

AI Core:

• AIPersonality: Poe's configuration (learning_rate=0.75)
• AIConversation: 1000 conversations with sentiment analysis
• AIDecision: 1000 autonomous decisions (~85% success rate)
• AIMemory: 500 memories about guests, patterns, events

AI Intelligence:

• GuestProfile: 100 AI-generated profiles with preferences
• SensorData: 10,000+ sensor readings (last 7 days)
• ProactiveAction: 500 autonomous service actions

Indexes (14 strategic indexes)

All indexes from PDF Section 4.4 implemented:

• Guest profile fast retrieval (<100ms)
• Conversation timeline
• Sensor time-series
• Decision analysis
• Memory retrieval
• Room availability
• And 8 more...

🔧 Configuration

Edit .env file for custom configuration:

# Toggle Redis for benchmarking
REDIS_ENABLED=true  # Set to false to test without cache

# Cache TTLs
CACHE_TTL_GUEST_PREFERENCES=3600  # 1 hour
CACHE_TTL_RECENT_SENTIMENT=1800   # 30 minutes
CACHE_TTL_SENSOR_DATA=60          # 60 seconds

# Benchmark iterations
BENCHMARK_ITERATIONS=100

📊 Sample Data

The system includes realistic sample data for immediate testing:

• 100 Guests: Mix of frequent (10), regular (10), and new guests (80)
• 150 Rooms: All 4 types (Single, Double, Suite, Deluxe) across 10 floors
• 500 Bookings: 70% AI-assigned, 30% manual
  • 350 completed (past 6 months)
  • 50 active (currently checked in)
  • 100 future (confirmed)
• 1000 Conversations: Various topics, sentiments, channels
• 1000 AI Decisions: 5 types, 85% success rate
• 100 Guest Profiles: JSON preferences and behavior patterns
• 10,000+ Sensor Readings: Temperature, occupancy, sound, light, door status
• 500 Proactive Actions: With guest response feedback
• 500 AI Memories: Linked to guests, bookings, conversations

🎯 MCP Tools (19 Tools Available)

Query Tools (13 tools)

From PDF Section 4.3 (12 queries):

1. ✅ search_available_rooms - Find rooms for date range
2. ✅ get_recent_conversations - Conversation history
3. ✅ get_ai_decision_success_rate - Performance metrics
4. ✅ find_low_satisfaction_guests - At-risk guests needing attention
5. ✅ get_proactive_action_counts - Action type frequencies
6. ✅ get_guest_stay_history - Complete booking history
7. ✅ get_recent_sensor_data - IoT sensor readings
8. ✅ count_successful_decisions_month - Monthly success count
9. ✅ compare_ai_vs_manual_bookings - AI vs manual assignment
10. ✅ get_recent_memory_access - Recently accessed memories
11. ✅ find_high_confidence_failures - Learning opportunities
12. ✅ get_proactive_actions_per_guest - Actions per active guest

Bonus: 13. ✅ get_guest_profile - Profile with cache demonstration

Testing/Benchmark Tools (6 tools)

1. ✅ benchmark_query_with_redis - Measure with caching
2. ✅ benchmark_query_without_redis - Measure without caching
3. ✅ compare_redis_performance - Side-by-side Redis comparison (shows 10-20x speedup)
4. ✅ validate_performance_requirements - Check <100ms/<500ms SLAs
5. ✅ get_cache_statistics - Redis stats (hit rate, memory)
6. ✅ clear_redis_cache - Clear cache for testing

Example Usage

# Find available rooms
User: "Show me available Double rooms for March 1-5, 2026"
→ Calls: search_available_rooms(check_in_date="2026-03-01", check_out_date="2026-03-05", room_type="Double")

# Benchmark Redis performance
User: "Prove that Redis improves performance for guest profile retrieval"
→ Calls: compare_redis_performance(guest_id=1, iterations=100)
→ Result: "12.1x faster with Redis (12ms vs 145ms), 92% latency reduction"

# Find at-risk guests
User: "Which guests need proactive attention right now?"
→ Calls: find_low_satisfaction_guests(threshold=3.0)

# Validate SLAs
User: "Are we meeting our performance requirements?"
→ Calls: validate_performance_requirements(guest_id=1, iterations=50)

🧪 Testing

Manual Database Tests

# Test PostgreSQL queries
docker exec -it hotel-ai-postgres psql -U hotel_admin -d hotel_ai -c "
SELECT r.room_number, r.room_type, r.floor
FROM Room r
WHERE r.status = 'Available'
  AND r.room_type = 'Double'
LIMIT 5;
"

# Test MongoDB queries
docker exec -it hotel-ai-mongodb mongosh -u hotel_admin -p hotel_password_2026 --authenticationDatabase admin --eval "
db = db.getSiblingDB('hotel_ai');
db.conversations.findOne();
"

# Test Redis
docker exec -it hotel-ai-redis redis-cli PING

Performance Benchmarks (Expected)

Based on PDF specifications:

Operation	Without Redis	With Redis	Improvement
Guest Profile Retrieval	145ms	12ms	12.1x faster
Room Search	285ms	N/A	Index optimized
Conversation History	420ms (relational)	95ms (MongoDB)	4.4x faster

📖 Documentation

Based on PDF Specification

This implementation follows the complete database design from: DB_Semesterprojekt_AI_Hotel.pdf

Key sections implemented:

• Section 2: Requirements Analysis (100%)
• Section 3: Information Modeling (ER diagram, 10 entities)
• Section 4: Relational Implementation (10 tables, 12 queries, 14 indexes)
• Section 5: NoSQL Discussion (MongoDB + Redis integration)

Architecture Decisions

1. PostgreSQL for Transactions: ACID compliance for bookings, ensures no double-bookings
2. MongoDB for Conversations: Nested message arrays, flexible schema for AI evolution
3. Redis for Caching: Sub-100ms access to frequently read data

🐛 Troubleshooting

Database Connection Issues

# Check service health
docker-compose ps

# Restart services
docker-compose restart postgres mongodb redis

# View logs
docker-compose logs postgres

Port Conflicts

If ports 5432, 27017, or 6379 are already in use:

# Edit docker-compose.yml and change ports:
ports:
  - "5433:5432"  # PostgreSQL
  - "27018:27017"  # MongoDB
  - "6380:6379"  # Redis

Then update .env:

POSTGRES_PORT=5433
MONGO_PORT=27018
REDIS_PORT=6380

🔄 Development Status

✅ Phase 1-3: Infrastructure & Database Layer (COMPLETE)

• [x] Docker infrastructure (PostgreSQL, MongoDB, Redis)
• [x] Database schemas (10 tables, exact from PDF)
• [x] Indexes (14 strategic indexes)
• [x] Sample data generation (100 guests, 500 bookings, 1000+ items)
• [x] MongoDB collections with validation
• [x] Configuration management
• [x] Pydantic models for type safety
• [x] Database clients (PostgreSQL, MongoDB, Redis)
• [x] Database client tests (all passing ✓)

✅ Phase 4-6: MCP Server Core (COMPLETE)

• [x] MCP server with stdio transport
• [x] 13 query tools (12 from PDF + get_guest_profile)
• [x] 6 performance benchmarking tools
• [x] Redis cache-aside pattern with toggle
• [x] Connection pooling for all databases

📊 Current Capabilities

Total: 19 MCP Tools Ready

• Query operations: 13 tools
• Testing/benchmarking: 6 tools
• Performance: <100ms cached, <500ms queries
• Sample data: 100 guests, 500 bookings, 1000+ conversations

📋 Future Enhancements (Optional)

• [ ] Mutation tools (create_booking, log_ai_decision, etc.)
• [ ] Analytics tools (trends, accuracy analysis)
• [ ] MCP resources (browsable data)
• [ ] MCP prompts (interaction templates)
• [ ] Web UI for monitoring
• [ ] Additional unit tests

📝 License

This is an academic project for demonstrating database design and MCP server implementation.

🤝 Contributing

This is a semester project. For questions or suggestions, please open an issue.

📚 References

• MCP Python SDK
• PostgreSQL 15 Documentation
• MongoDB 7.0 Documentation
• Redis Documentation
• PDF Specification: DB_Semesterprojekt_AI_Hotel.pdf

---

Built with: Python, PostgreSQL, MongoDB, Redis, Docker, MCP SDK Purpose: Demonstrate hybrid database architecture with AI-driven hotel operations Author: Iyad Elwy Institution: PSE Master - KnowledgeFoundation@HSReutlingen