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
# 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
# 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
# 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
pip install -r requirements.txt
5. Run MCP Server
python server.py
MCP Tools
1. upload_forecast
Upload a complete forecast (text + audio) to Cloud SQL.
{
"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.
{
"city": "chicago",
"language": "en"
}
Returns:
cached: true/falseforecast_text: decoded unicode textaudio_data: base64-encoded audioage_seconds: age of cached forecast
3. cleanup_expired_forecasts
Remove expired forecasts from database.
{}
4. get_storage_stats
Get database storage statistics.
{}
Returns:
- Total forecasts
- Storage sizes
- Encodings used
- Languages used
- Per-city breakdown
5. list_forecasts
List forecast history.
{
"city": "chicago",
"limit": 10
}
6. test_connection
Test database connection.
{}
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
# Run test connection
python -c "from tools.connection import test_connection; import json; print(json.dumps(test_connection(), indent=2))"
Running Tests
# Add tests in tests/ directory
pytest tests/
Troubleshooting
Connection Issues
- Verify Cloud SQL instance is running
- Check firewall rules allow connections
- Verify credentials in .env file
- Test with
test_connectiontool
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.