# GCP Sales Analytics POC
A proof of concept demonstrating an intelligent AI agent that can query both Google Cloud SQL (PostgreSQL) and BigQuery public datasets, automatically choosing the right data source based on the question.
## Architecture
```
┌─────────────────────────────────────────────────────────────┐
│ ADK Agent (Claude) │
│ Intelligently routes queries to appropriate data source │
└────────────────┬────────────────────────────────────────────┘
│
├─────────────────┬──────────────────────┐
│ │ │
▼ ▼ ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ MCP Server │ │ MCP Server │ │ Tools │
│ (Cloud SQL) │ │ (BigQuery) │ │ (Schema) │
└──────┬───────┘ └──────┬───────┘ └──────────────┘
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│ Cloud SQL │ │ BigQuery │
│ PostgreSQL │ │ thelook_ │
│ │ │ ecommerce │
│ • customers │ │ • products │
│ • orders │ │ • users │
│ • vendors │ │ • events │
└──────────────┘ │ • inventory │
│ • orders │
└──────────────┘
```
## Features
- **Dual Data Source Access**: Query both Cloud SQL and BigQuery seamlessly
- **Intelligent Routing**: Agent automatically determines which data source to use
- **MCP Server**: Standards-compliant Model Context Protocol server
- **Synthetic Data**: Pre-populated with 50 customers, 50 vendors, and 50 orders
- **Infrastructure as Code**: Terraform for Cloud SQL deployment
- **Production-Ready**: Error handling, logging, and security best practices
## Data Sources
### Cloud SQL (PostgreSQL)
Contains transactional data with three tables:
- **customers**: Customer information (name, email, address, etc.)
- **orders**: Order details (amounts, dates, status, products)
- **vendors**: Vendor information
Use for queries about:
- Specific customer information
- Recent order details
- Vendor data
- Current sales transactions
### BigQuery (thelook_ecommerce)
Public e-commerce analytics dataset with comprehensive data:
- products, users, events
- inventory_items, order_items
- distribution_centers
Use for queries about:
- Product analytics
- User behavior patterns
- Inventory analysis
- Historical trends
- Large-scale analytics
## Prerequisites
- Google Cloud Platform account with billing enabled
- GCP Project with appropriate permissions
- Tools installed:
- `gcloud` CLI
- `terraform` (>= 1.0)
- `python3` (>= 3.9)
- `psql` (PostgreSQL client)
- Anthropic API key for Claude
## Setup
### 1. Clone and Configure
```bash
cd /Users/matthewiames/Desktop/gcp_mcp
# Copy environment template
cp .env.example .env
# Edit .env with your values
# Required: ANTHROPIC_API_KEY, GCP_PROJECT_ID
nano .env
```
### 2. Install Python Dependencies
```bash
# Create virtual environment (recommended)
python3 -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate
# Install dependencies
pip install -r requirements.txt
```
### 3. Authenticate with GCP
```bash
# Login to GCP
gcloud auth login
# Set application default credentials
gcloud auth application-default login
# Set your project
gcloud config set project YOUR_PROJECT_ID
```
### 4. Deploy Infrastructure
```bash
# Run the automated deployment script
./scripts/deploy.sh
```
The deployment script will:
1. Deploy Cloud SQL instance with Terraform
2. Create database schema (customers, orders, vendors)
3. Seed database with synthetic data
4. Verify BigQuery access
This process takes approximately 5-10 minutes.
## Usage
### Running the MCP Server
```bash
# In terminal 1
python3 mcp_server/server.py
```
The MCP server provides tools for:
- `query_cloudsql` - Execute SQL against Cloud SQL
- `query_bigquery` - Execute SQL against BigQuery
- `list_cloudsql_tables` - List Cloud SQL tables
- `list_bigquery_tables` - List BigQuery tables
- `get_cloudsql_schema` - Get table schema from Cloud SQL
- `get_bigquery_schema` - Get table schema from BigQuery
### Running the Agent (Interactive Mode)
```bash
# In terminal 2
python3 adk_agent/agent.py
```
Example interactions:
```
📊 You: What are the total sales from our database?
🤖 Agent: I'll query the Cloud SQL database to calculate total sales...
[Uses query_cloudsql tool]
The total sales amount from all orders is $24,567.89 across 50 orders.
📊 You: Show me the top 5 products from BigQuery
🤖 Agent: I'll query the BigQuery thelook_ecommerce dataset...
[Uses query_bigquery tool]
Here are the top 5 products by sales:
1. Product A - $15,234
2. Product B - $12,456
...
```
### Running Tests
```bash
# Run automated tests
python3 scripts/test_agent.py
```
## Project Structure
```
gcp_mcp/
├── README.md # This file
├── requirements.txt # Python dependencies
├── .env.example # Environment template
├── .gitignore # Git ignore rules
│
├── terraform/ # Infrastructure as Code
│ ├── main.tf # Cloud SQL resources
│ ├── variables.tf # Input variables
│ ├── outputs.tf # Output values
│ └── terraform.tfvars.example
│
├── data/ # Database schema
│ └── schema.sql # Table definitions
│
├── scripts/ # Deployment and utilities
│ ├── deploy.sh # Automated deployment
│ ├── cleanup.sh # Resource cleanup
│ ├── generate_seed_data.py # Synthetic data generation
│ └── test_agent.py # Agent tests
│
├── mcp_server/ # MCP Server implementation
│ └── server.py # Database MCP server
│
└── adk_agent/ # ADK Agent implementation
└── agent.py # Sales analytics agent
```
## How It Works
### Agent Decision Making
The agent uses Claude's function calling capabilities with a specialized system prompt that guides data source selection:
1. **Question Analysis**: Agent analyzes the user's question
2. **Schema Discovery**: May first list tables to understand available data
3. **Source Selection**: Chooses Cloud SQL or BigQuery based on:
- Keywords (customers, vendors → Cloud SQL)
- Query type (analytics, trends → BigQuery)
- Data recency requirements
4. **Query Execution**: Formulates and executes appropriate SQL
5. **Result Presentation**: Formats and explains results
### Example Decision Flow
```
User: "What are my recent orders?"
↓
Agent thinks: "recent orders" + "my" suggests transactional data
↓
Decision: Use Cloud SQL
↓
Tool: query_cloudsql
↓
SQL: SELECT * FROM orders ORDER BY order_date DESC LIMIT 10
```
## Configuration
### Environment Variables (.env)
```bash
# GCP Configuration
GCP_PROJECT_ID=your-project-id
GCP_REGION=us-central1
CLOUDSQL_INSTANCE_NAME=sales-poc-db
CLOUDSQL_DATABASE=salesdb
CLOUDSQL_USER=salesuser
CLOUDSQL_PASSWORD=your-secure-password
# BigQuery
BIGQUERY_DATASET=bigquery-public-data.thelook_ecommerce
# Anthropic
ANTHROPIC_API_KEY=your-api-key
# Database connection (set after deployment)
DB_HOST=your-cloudsql-ip
```
### Terraform Variables
See [terraform/terraform.tfvars.example](terraform/terraform.tfvars.example)
## Security Considerations
This is a **proof of concept** with simplified security:
- Cloud SQL has public IP (uses authorized networks)
- Database credentials in environment variables
- No VPC or private networking
- SQL injection prevention (SELECT-only queries)
For production:
- Use Cloud SQL Proxy or private IP
- Store credentials in Secret Manager
- Implement VPC and private networking
- Add query validation and sanitization
- Enable Cloud SQL backups
- Use IAM authentication
- Implement rate limiting
## Cost Estimation
Approximate costs for running this POC:
- **Cloud SQL** (db-f1-micro): ~$7-10/month
- **BigQuery**: Pay per query (~$5/TB scanned, public datasets may be free)
- **Anthropic API**: Pay per token
**Important**: Run `./scripts/cleanup.sh` when done to avoid ongoing charges.
## Cleanup
To destroy all resources:
```bash
./scripts/cleanup.sh
```
This will:
- Destroy the Cloud SQL instance
- Remove all data
- Clean up Terraform state
## Troubleshooting
### Connection Issues
```bash
# Test Cloud SQL connection
psql -h $DB_HOST -U $CLOUDSQL_USER -d $CLOUDSQL_DATABASE
# Check instance status
gcloud sql instances describe sales-poc-db-XXXX
```
### API Access Issues
```bash
# Enable required APIs
gcloud services enable sqladmin.googleapis.com
gcloud services enable bigquery.googleapis.com
# Check authentication
gcloud auth list
```
### Terraform Issues
```bash
cd terraform
# Re-initialize
terraform init
# Check state
terraform show
```
## Extending the POC
Ideas for enhancement:
1. **Add More Data Sources**
- Cloud Spanner
- Firestore
- External APIs
2. **Enhanced Agent Capabilities**
- Data visualization
- Report generation
- Predictive analytics
3. **Production Features**
- Caching layer
- Query optimization
- Audit logging
- Monitoring and alerts
4. **Advanced MCP Features**
- Streaming responses
- Batch operations
- Transaction support
## Resources
- [Model Context Protocol (MCP)](https://modelcontextprotocol.io/)
- [Anthropic Claude API](https://docs.anthropic.com/)
- [Google Cloud SQL](https://cloud.google.com/sql)
- [BigQuery Public Datasets](https://cloud.google.com/bigquery/public-data)
- [Terraform GCP Provider](https://registry.terraform.io/providers/hashicorp/google/latest/docs)
## License
This is a proof of concept for demonstration purposes.
## Support
For issues or questions:
1. Check the troubleshooting section
2. Review logs in the terminal
3. Check GCP Console for resource status
