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/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.

{}

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_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.

Forecast Storage MCP Server