Customer Reminder MCP

# Customer Reminder MCP A Model Context Protocol (MCP) server that helps business owners manage and automatically send customer reminders via email. This system integrates with Google Sheets to read customer data and sends scheduled reminders based on due dates. ## Features 🚀 **Google Sheets Integration** - Connect to Google Sheets to read customer reminder data - Support for multiple date formats (YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY) - Automatic data validation and error handling 📧 **Email Reminders** - Send personalized email reminders to customers - Customizable message templates with dynamic placeholders - Support for Gmail SMTP with app passwords ⏰ **Intelligent Scheduling** - APScheduler-based background processing - Configurable interval checking (minutes, hours, days) - Prevents duplicate reminder sends with tracking system - Immediate execution or scheduled background service 🔌 **Model Context Protocol (MCP) Integration** - Full MCP server implementation - Tools for checking reminders, sending reminders, and retrieving data - Resources for accessing customer reminders and sent reminder history - Test mode to verify sheet connection and server status ## Project Structure ``` reminder-mcp/ ├── reminder_app.py # Main application with all classes and logic ├── mcp_reminder_server.py # MCP server implementation ├── reminder_app.log # Application logs ├── reminder_jobs.sqlite # APScheduler job storage ├── credentials.json # Google Sheets service account credentials ├── .env # Environment variables (create this) ├── requirements.txt # Python dependencies ├── mcp_config.json # MCP server configuration └── README.md # This file ``` ## Prerequisites 1. **Python 3.8+** installed 2. **Google Sheets API credentials** (service account JSON file) 3. **Gmail account** with app password enabled 4. **Google Sheet** with customer reminder data ## Setup Instructions ### 1. Install Dependencies ```bash pip install -r requirements.txt ``` Or install individually: ```bash pip install python-dotenv gspread oauth2client apscheduler ``` ### 2. Set up Google Sheets API Credentials 1. Copy the provided example file: ```bash cp credentials.example.json credentials.json ``` 2. Replace the placeholder values in `credentials.json` with your actual Google service account credentials. 3. **SECURITY WARNING**: Never commit your `credentials.json` file to version control or share it publicly! ### 3. Create Environment File 1. Copy the provided example file: ```bash cp .env.example .env ``` 2. Edit the `.env` file and update with your actual values: ```env # Email Configuration (Gmail) EMAIL_HOST=smtp.gmail.com EMAIL_PORT=587 EMAIL_USERNAME=your_email@gmail.com EMAIL_PASSWORD=your_app_password # Google Sheets Configuration GOOGLE_CREDENTIALS_FILE=credentials.json GOOGLE_SPREADSHEET_NAME=Customer_reminder # Scheduler Configuration DEFAULT_INTERVAL_MINUTES=6 ``` ### 4. Test Your Configuration Test your connection to Google Sheets and verify the setup: ```bash python mcp_reminder_server.py --test ``` This will verify your connection and display the available MCP tools and resources. ### 3. Setup Google Sheets API 1. Go to [Google Cloud Console](https://console.cloud.google.com/) 2. Create a new project or select existing 3. Enable Google Sheets API 4. Create service account credentials: - Navigate to "IAM & Admin" > "Service Accounts" - Click "Create Service Account" - Provide a name and description - For role, select a minimal role like "Viewer" or create a custom role with specific permissions 5. Create a key for the service account: - Select your new service account - Go to the "Keys" tab - Click "Add Key" > "Create new key" - Choose JSON format 6. Download the JSON credentials file and save it as `credentials.json` in your project directory 7. Share your Google Sheet with the service account email address (limited to "Viewer" or "Editor" as needed) 8. **IMPORTANT**: Keep your credentials secure: - Never commit this file to version control - Restrict access to the credentials file on your local system - Consider encrypting the file when not in use ### 4. Prepare Your Google Sheet Create a Google Sheet with these columns: - `customer_name` - Customer's full name - `customer_email` - Customer's email address - `due_date` - When reminder is due (YYYY-MM-DD format) - `message_template` - Custom message (use {customer_name} and {due_date} placeholders) - `reminder_type` - Set to "email" Example: | customer_name | customer_email | due_date | message_template | reminder_type | |---------------|----------------|----------|------------------|---------------| | John Doe | john@example.com | 2025-08-15 | Hi {customer_name}! Your appointment is on {due_date}. | email | ## Usage ### Run Once (Immediate) ```bash python reminder_app.py # or python reminder_app.py --run-once ``` ### Start Scheduler (Background Service) ```bash # Default 6-minute intervals python reminder_app.py --schedule # Custom intervals python reminder_app.py --schedule 10 # Every 10 minutes python reminder_app.py --schedule 30 # Every 30 minutes python reminder_app.py --schedule 1 # Every 1 minute (testing) ``` ### Run MCP Server ```bash # Normal operation - starts the MCP server for integration with applications python mcp_reminder_server.py # Test mode - verifies Google Sheets connection and shows available tools python mcp_reminder_server.py --test ``` ### Help ```bash python reminder_app.py --help ``` ## How It Works 1. **Data Source**: Reads customer data from Google Sheets 2. **Date Checking**: Identifies reminders due today or overdue 3. **Duplicate Prevention**: Tracks sent reminders to prevent duplicates 4. **Email Sending**: Sends personalized emails via SMTP 5. **Scheduling**: Runs automatically at specified intervals 6. **Logging**: Provides detailed console output for monitoring 7. **MCP Integration**: Exposes functionality via Model Context Protocol ### Scheduler Settings - **DEFAULT_INTERVAL_MINUTES**: Default check interval (default: 6) ### MCP Server Tools The MCP server exposes the following tools: - **send_reminder**: Sends a single reminder to a customer - **check_due_reminders**: Checks for and sends all due reminders - **get_customer_reminders**: Retrieves customer reminder data - **get_sent_reminders**: Retrieves history of sent reminders - **get_sheet_status**: Checks connection status with Google Sheets ### MCP Server Resources - **reminders://customer-reminders**: Customer reminder data - **reminders://sent-reminders**: History of sent reminders ## Troubleshooting ### Common Issues **Gmail Authentication Error (535)** - Ensure 2-Step Verification is enabled - Use app password, not regular password - Check username/password in .env file **Google Sheets Not Found** - Verify spreadsheet name matches exactly - Ensure service account has access to the sheet - Check credentials.json file exists **No Reminders Found** - Verify date format in Google Sheet (YYYY-MM-DD) - Check if reminders are due (today or overdue) - Ensure required columns exist with correct names ## Development ### Testing Use short intervals for testing: ```bash python reminder_app.py --schedule 1 # Check every minute ``` Test the MCP server functionality: ```bash python mcp_reminder_server.py --test ``` This will show: 1. Available tools and resources 2. Example tool calls 3. Google Sheets connection status 4. Current reminders in the system ### Adding Features The modular design allows easy extension: - Add new reminder types (SMS, etc.) - Integrate with different data sources - Add web dashboard - Implement advanced scheduling - Create new MCP tools and resources ## License This project is part of the Model Context Protocol ecosystem and is designed for business automation and customer relationship management for Preshify. ## Security Best Practices ### Protecting Sensitive Information 1. **Never commit credentials to version control** - Both `credentials.json` and `.env` files contain sensitive information - Always keep these files in your `.gitignore` - Use example files (provided) to show the structure without exposing real data 2. **Regularly rotate credentials** - Periodically generate new Google service account keys - Update your Gmail app password regularly - Revoke unused or potentially compromised credentials 3. **Limit permissions** - Your Google service account should have minimal required permissions - Share your Google Sheet with the service account email using the most restrictive permissions necessary 4. **Monitor for unauthorized access** - Regularly check the Google Cloud Console for unusual activity - Review application logs for unexpected behavior - Be alert to unplanned or unexpected emails being sent ### Data Handling Recommendations 1. **Minimize data retention** - Only store the customer data you need - Consider implementing a data retention policy - Regularly clean up old reminder data 2. **Implement logging best practices** - Don't log sensitive customer information - Use log rotation to prevent log files from growing too large - Regularly review logs for security issues ## Support For issues, questions, or feature requests, please create an issue in the repository.