Forecast Storage MCP Server

# Forecast Storage MCP Server A Model Context Protocol (MCP) server for storing weather forecasts in Google Cloud SQL PostgreSQL. ## Features - ✅ **Binary storage** for text and audio with unicode support - ✅ **Full internationalization** - supports all languages (English, Spanish, Chinese, Japanese, Arabic, etc.) - ✅ **TTL-based caching** with automatic expiration - ✅ **Cloud SQL integration** with secure connections - ✅ **Storage statistics** and per-city breakdown - ✅ **Automatic encoding detection** (utf-8, utf-16, utf-32) ## Setup ### 1. Create Cloud SQL Instance ```bash # Create PostgreSQL instance gcloud sql instances create weather-forecasts \ --database-version=POSTGRES_17 \ --tier=db-f1-micro \ --region=us-central1 \ --enable-auto-scaling \ --auto-scaling-min-cpu=1 \ --auto-scaling-max-cpu=2 # Create database gcloud sql databases create weather \ --instance=weather-forecasts # Set password for postgres user gcloud sql users set-password postgres \ --instance=weather-forecasts \ --password=YOUR_SECURE_PASSWORD ``` ### 2. Apply Database Schema ```bash # Get the instance IP (or use Cloud SQL Proxy) gcloud sql instances describe weather-forecasts --format="value(ipAddresses[0].ipAddress)" # Apply schema psql -h INSTANCE_IP -U postgres -d weather -f schema.sql ``` ### 3. Configure Environment ```bash # Copy example environment file cp .env.example .env # Edit .env with your values # GCP_PROJECT_ID=your-project-id # CLOUD_SQL_PASSWORD=your-secure-password ``` ### 4. Install Dependencies ```bash pip install -r requirements.txt ``` ### 5. Run MCP Server ```bash python server.py ``` ## MCP Tools ### 1. upload_forecast Upload a complete forecast (text + audio) to Cloud SQL. ```json { "city": "chicago", "forecast_text": "Weather in Chicago: Sunny, 75°F", "audio_data": "<base64-encoded-wav-audio-data>", "forecast_at": "2025-12-26T15:00:00Z", "ttl_minutes": 30, "language": "en", "locale": "en-US" } ``` **Note:** `audio_data` should be base64-encoded WAV audio data, not a file path. This allows the MCP server to work in remote/containerized environments. ### 2. get_cached_forecast Retrieve cached forecast if available and not expired. ```json { "city": "chicago", "language": "en" } ``` Returns: - `cached: true/false` - `forecast_text`: decoded unicode text - `audio_data`: base64-encoded audio - `age_seconds`: age of cached forecast ### 3. cleanup_expired_forecasts Remove expired forecasts from database. ```json {} ``` ### 4. get_storage_stats Get database storage statistics. ```json {} ``` Returns: - Total forecasts - Storage sizes - Encodings used - Languages used - Per-city breakdown ### 5. list_forecasts List forecast history. ```json { "city": "chicago", "limit": 10 } ``` ### 6. test_connection Test database connection. ```json {} ``` ## Integration with Weather Agent The MCP server is designed to integrate with the weather agent system. See the main project README for integration details. ## Database Schema The `forecasts` table stores: - **Binary text** (BYTEA) with encoding metadata - **Binary audio** (BYTEA) - **Unicode support** (utf-8, utf-16, utf-32) - **Internationalization** (language, locale) - **TTL management** (forecast_at, expires_at) - **Storage metadata** (sizes, encoding, metadata JSONB) ## Development ### Testing Connection ```bash # Run test connection python -c "from tools.connection import test_connection; import json; print(json.dumps(test_connection(), indent=2))" ``` ### Running Tests ```bash # Add tests in tests/ directory pytest tests/ ``` ## Troubleshooting ### Connection Issues 1. Verify Cloud SQL instance is running 2. Check firewall rules allow connections 3. Verify credentials in .env file 4. Test with `test_connection` tool ### Encoding Issues - Default encoding is utf-8 (works for most languages) - Use utf-16 for heavy CJK (Chinese/Japanese/Korean) text - Encoding is auto-detected if not specified ## Cost Estimation **Development (db-f1-micro)**: - Instance: ~$7/month (with auto-pause: ~$3.50/month) - Storage (10GB): ~$1.70/month - Total: ~$5-9/month **Production (db-custom-2-7680)**: - Instance: ~$130/month (with auto-pause: ~$65/month) - Storage (50GB): ~$8.50/month - Total: ~$70-140/month ## License Part of the weather-lab project.