# Google Workspace CRM MCP Server

A Python MCP (Model Context Protocol) server that acts as a lightweight CRM by integrating **Google Sheets** (contact management & generic spreadsheet ops), **Gmail** (email communication), and **Google Docs** (document creation). Designed for hosting on Replit with Streamable HTTP transport, compatible with both **Claude** and **ChatGPT** as MCP clients.

---

## Features

- **30 MCP tools** for full CRM + workspace workflow
- **Google Sheets**: CRM contacts + generic read/write/append on any range, tab management, and new spreadsheet creation
- **Gmail integration** via OAuth2 for sending, searching, reading emails, replying, and drafting (with SMTP and domain-wide delegation fallbacks)
- **Google Docs**: Create new documents, append text, read document content
- **Agent Cache**: Thread-safe in-memory cache with TTL expiration, LRU eviction, namespace support, and bulk operations for fast batch processing
- **OAuth2 MCP endpoint protection**: Secure the `/mcp` endpoint with Client Credentials or Authorization Code + PKCE flow — compatible with Claude's Advanced Settings
- **Settings dashboard**: Web-based UI for credential management, OAuth2 authorization, Google Sheets selection, and comprehensive testing
- **Streamable HTTP transport** for remote hosting on Replit
- **Full audit logging**: Every tool call is tracked through 5 stages (received, API call, Google validated, result delivered, delivery confirmed)
- **Comprehensive test suite**: One-click tests for Service Account, MCP Tools, Gmail Send & Reply, Cache Pipeline, and CRM Contact Management — each writes results to a dedicated Google Sheet tab
- **Rate limiting & caching** to stay within Google API quotas
- **PostgreSQL-backed credentials**: All service account keys, OAuth tokens, and settings stored securely in the database — never in code or files

---

## MCP Tools (30 total)

### CRM Contact Management (8 tools)

| Tool | Description | R/W |
|------|-------------|-----|
| `crm_list_contacts` | List all contacts with optional field filtering | Read |
| `crm_get_contact` | Get a single contact by email or row number | Read |
| `crm_add_contact` | Add a new contact to the CRM | Write |
| `crm_update_contact` | Update an existing contact's fields | Write |
| `crm_delete_contact` | Soft-delete a contact (moves to "Deleted" sheet) | Destructive |
| `crm_search_contacts` | Full-text search across all contact fields | Read |
| `crm_add_note` | Append a timestamped note to a contact | Write |
| `crm_get_contact_with_emails` | Get contact profile + recent email history | Read |

### Google Sheets Operations (7 tools)

| Tool | Description | R/W |
|------|-------------|-----|
| `sheets_read_range` | Read data from a specific A1 range | Read |
| `sheets_write_range` | Write a 2D array to a specific A1 range | Write |
| `sheets_append_rows` | Append rows to the end of a sheet tab | Write |
| `sheets_read_all` | Read all data from an entire sheet tab | Read |
| `sheets_list_tabs` | List all worksheet tabs with dimensions | Read |
| `sheets_create_tab` | Create a new worksheet tab with optional headers | Write |
| `sheets_delete_tab` | Delete a worksheet tab (permanent, requires confirm) | Destructive |

### Google Workspace File Creation (4 tools)

| Tool | Description | R/W |
|------|-------------|-----|
| `sheets_create_spreadsheet` | Create a brand new Google Sheets file | Write |
| `docs_create_document` | Create a new Google Doc with optional initial text | Write |
| `docs_append_text` | Append text to an existing Google Doc | Write |
| `docs_read_document` | Read the full text of a Google Doc | Read |

### Email Communication (7 tools)

| Tool | Description | R/W |
|------|-------------|-----|
| `gmail_send_email` | Send an email (plain text or HTML) | Write |
| `gmail_search_emails` | Search emails using Gmail query syntax | Read |
| `gmail_get_email` | Get full email content by message ID | Read |
| `gmail_send_reply` | Reply to an existing email thread | Write |
| `gmail_draft_email` | Create a draft email without sending | Write |
| `crm_log_email_to_contact` | Log an email interaction on a contact's record | Write |
| `crm_get_contact_with_emails` | Get contact profile + recent email history | Read |

### Agent Cache (5 tools)

| Tool | Description | R/W |
|------|-------------|-----|
| `cache_put` | Store a value in the agent cache with optional TTL and namespace | Write |
| `cache_get` | Retrieve a value from the cache | Read |
| `cache_list` | List cache keys, optionally filtered by namespace | Read |
| `cache_delete` | Delete a cache entry | Write |
| `cache_stats` | View cache statistics (entries, hits, misses, hit rate) | Read |

### System & Audit (4 tools)

| Tool | Description | R/W |
|------|-------------|-----|
| `system_health_check` | Test connectivity to all Google services | Read |
| `audit_get_logs` | Query the audit log (filter by tool, stage, errors) | Read |
| `audit_trace_request` | Get the full lifecycle trace of a request by ID | Read |
| `audit_confirm_delivery` | Confirm the chat agent received a tool result | Write |

---

## OAuth2 MCP Endpoint Security

The `/mcp` endpoint can be protected with OAuth2 authentication, preventing unauthorized access.

### How It Works

When `MCP_CLIENT_ID` and `MCP_CLIENT_SECRET` are configured as secrets:

1. **Discovery**: Clients query `/.well-known/oauth-authorization-server` for metadata
2. **Authorization**: Clients redirect to `/authorize` with PKCE challenge
3. **Token Exchange**: Clients exchange the authorization code at `/token` for a Bearer token
4. **Authenticated Access**: All `/mcp` requests require `Authorization: Bearer <token>`

Without these secrets, the endpoint remains open (backward-compatible).

### Supported Flows

- **Authorization Code + PKCE** — Used by Claude, ChatGPT, and browser-based clients
- **Client Credentials** — Used by server-to-server integrations

### Security Features

- Credentials are only viewable/generatable in the development environment
- The live/deployed app hides Client IDs and blocks credential generation
- Tokens expire after 1 hour
- Authorization codes expire after 5 minutes and are single-use
- PKCE (S256) verification prevents code interception attacks
- Constant-time comparison (HMAC) prevents timing attacks

---

## Test Endpoints

The Settings UI provides one-click test buttons for all major services:

| Test | Endpoint | Steps | Description |
|------|----------|-------|-------------|
| **Service Account** | `/api/service-account/test` | 3 | Verifies Google API connectivity and file access |
| **MCP Tools** | `/api/mcp-tools/test` | 8 | Creates test tab, writes/reads data, cleans up |
| **Gmail Send & Reply** | `/api/gmail-test/full` | 7 | Sends email, verifies delivery, sends reply, checks thread |
| **Cache Pipeline** | `/api/cache/test` | 7 | Reads contacts, caches, creates jokes, drafts & sends email |
| **CRM Contacts** | `/api/crm-contacts/test` | 10 | Tests all 8 CRM tools, writes results to dated tab |
| **OAuth2 Auth** | Settings UI button | 3 | Verifies unauthenticated access is blocked |

---

## Audit Logging System

Every tool call is tracked through a 5-stage lifecycle:

```
1. REQUEST_RECEIVED   → Incoming tool invocation from the chat agent
2. API_CALL           → Each Google API / SMTP call made by the server
3. GOOGLE_VALIDATED   → Confirmation the action landed on the Google side
4. RESULT_DELIVERED   → Response payload returned to the chat agent
5. DELIVERY_CONFIRMED → (Optional) Chat agent confirmed receipt
```

Every tool response includes an `_audit` field with the `request_id`:

```json
{
  "contact": { ... },
  "message": "Contact added at row 5.",
  "_audit": { "request_id": "a1b2c3d4e5f6..." }
}
```

---

## Agent Cache System

The in-memory agent cache enables fast batch processing workflows:

- **TTL Expiration**: Default 30-minute TTL, configurable per entry
- **LRU Eviction**: Automatic eviction when cache exceeds 10,000 entries
- **Namespace Support**: Organize entries by namespace (e.g., `contacts`, `emails`)
- **Bulk Operations**: List and delete entries by namespace
- **Statistics**: Track hits, misses, and hit rate
- **Thread-Safe**: Safe for concurrent access across MCP sessions
- **Global Singleton**: Shared across all tool calls for cross-request persistence

---

## Google Sheet Schema

Create a Google Sheet and format the first row as headers:

| Column | Header | Example |
|--------|--------|---------|
| A | first_name | Ali |
| B | last_name | Smith |
| C | email | ali@example.com |
| D | phone | +1-555-0123 |
| E | company | OPTT Health |
| F | role | CEO |
| G | status | lead |
| H | notes | [2025-02-19] Initial outreach... |
| I | created_at | 2025-02-19T10:00:00Z |
| J | updated_at | 2025-02-19T10:00:00Z |

Valid `status` values: `lead`, `prospect`, `customer`, `churned`

**Important:** Share the Google Sheet with your service account email (shown in the Settings UI after uploading the service account JSON). Give it **Editor** access.

---

## Setup Instructions

### 1. Google Cloud Configuration

1. Go to [Google Cloud Console](https://console.cloud.google.com/)
2. Select the project associated with your service account
3. Enable the following APIs:
   - Google Sheets API
   - Google Drive API
   - Gmail API
   - Google Docs API
4. Download the service account JSON key (or copy the existing one)

### 2. Settings Dashboard (Recommended)

The easiest way to configure everything is through the **Settings UI** at the root path (`/`):

1. **Upload Service Account**: Upload your Google Cloud service account JSON
2. **Select CRM Sheet**: Browse and select a Google Sheet for CRM data
3. **Configure Gmail OAuth**: Upload OAuth client credentials and authorize Gmail access
4. **Enable MCP Auth**: Generate and configure OAuth2 credentials for endpoint protection

### 3. Gmail Auth — Choose Your Path

**Path C — OAuth2 (Recommended):**
1. Create OAuth2 credentials in Google Cloud Console (Web Application type)
2. Upload the Client ID and Secret in the Settings UI
3. Click "Authorize Gmail" and complete the browser OAuth flow
4. Tokens are stored securely in PostgreSQL and refresh automatically

**Path A — Google Workspace Domain-Wide Delegation:**
If your Gmail is on a Workspace domain (e.g., `user@yourdomain.com`):
1. Go to Google Workspace Admin Console → Security → API Controls → Domain-wide Delegation
2. Add the service account client ID with Gmail scopes
3. Set `DELEGATED_USER_EMAIL=user@yourdomain.com`

**Path B — SMTP Fallback:**
If you use a personal `@gmail.com` account:
1. Enable 2-Step Verification on your Google Account
2. Generate an App Password at https://myaccount.google.com/apppasswords
3. Set `USE_SMTP_FALLBACK=true`, `GMAIL_ADDRESS=your@gmail.com`, `GMAIL_APP_PASSWORD=xxxx xxxx xxxx xxxx`

### 4. MCP Endpoint Security (Recommended)

To protect the `/mcp` endpoint with OAuth2:

1. In the **Settings UI** (development environment), click **"Generate Credentials"** in the MCP Authentication card
2. Copy the generated `MCP_CLIENT_ID` and `MCP_CLIENT_SECRET`
3. Add them as **Replit Secrets** (Tools → Secrets)
4. Restart the server
5. In Claude's MCP connector **Advanced Settings**, enter the same Client ID and Client Secret

### 5. Replit Secrets

Add these as Replit Secrets (Tools → Secrets):

| Secret | Required | Description |
|--------|----------|-------------|
| `MCP_CLIENT_ID` | Recommended | OAuth2 Client ID for MCP endpoint protection |
| `MCP_CLIENT_SECRET` | Recommended | OAuth2 Client Secret for MCP endpoint protection |
| `MCP_SERVER_PORT` | No | Default: `5000` |

All other credentials (service account, OAuth tokens, spreadsheet selection) are managed through the **Settings UI** and stored in PostgreSQL.

### 6. Deploy on Replit

1. Fork or import this repository into Replit
2. Configure credentials via the Settings UI
3. Click **Run** — the server starts on port 5000
4. On startup, connectivity checks run automatically and log results
5. Your MCP endpoint URL will be shown on the Settings dashboard

---

## Connecting to MCP Clients

### Claude (Web / Desktop)

1. Go to Claude Settings → MCP Servers → Add
2. Enter your MCP endpoint URL: `https://your-app.replit.app/mcp`
3. In **Advanced Settings**, enter your `MCP_CLIENT_ID` and `MCP_CLIENT_SECRET`
4. Claude will auto-discover the 30 tools

### Claude Desktop (Config File)

Add to your `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "google-workspace-crm": {
      "url": "https://your-app.replit.app/mcp"
    }
  }
}
```

### Claude Code (CLI)

```bash
claude mcp add google-workspace-crm --transport http https://your-app.replit.app/mcp
```

### ChatGPT

ChatGPT supports MCP servers via its plugin/actions system:
1. In your Custom GPT configuration, go to **Actions**
2. Import the MCP endpoint URL
3. ChatGPT will auto-discover the available tools via Streamable HTTP

### Any MCP-Compatible Client

```
Endpoint: https://your-app.replit.app/mcp
Transport: Streamable HTTP
OAuth2 Metadata: https://your-app.replit.app/.well-known/oauth-authorization-server
Token Endpoint: https://your-app.replit.app/token
```

---

## Project Structure

```
├── main.py                     # MCP server entry point (30 tools, OAuth2 auth, startup checks)
├── config.py                   # Environment config loader
├── requirements.txt            # Python dependencies
├── tools/
│   ├── sheets.py               # CRM tools (8) + generic sheet tools (7)
│   ├── gmail.py                # Gmail tools (5) + workflow tools (2)
│   ├── workspace.py            # Spreadsheet/Doc creation (4) + audit tools (3)
│   └── cache.py                # Agent cache tools (5)
├── services/
│   ├── google_auth.py          # Service account credential loading
│   ├── sheets_client.py        # gspread client with caching & rate limiting
│   ├── gmail_client.py         # Gmail API / SMTP client wrapper
│   ├── docs_client.py          # Google Docs + Drive API client
│   ├── cache.py                # Thread-safe agent cache (TTL, LRU, namespaces)
│   ├── mcp_auth.py             # OAuth2 auth for MCP endpoint (PKCE, tokens)
│   ├── audit_logger.py         # 5-stage audit log with ring buffer
│   └── health_check.py         # Connectivity testing for all Google services
├── web/
│   ├── routes.py               # Settings UI routes & test endpoints
│   ├── settings_store.py       # PostgreSQL-backed settings storage
│   ├── oauth.py                # Gmail OAuth2 flow handler
│   ├── sheets_browser.py       # Google Sheets browser for selection UI
│   └── templates/
│       └── settings.html       # Settings dashboard UI
└── models/
    └── contact.py              # Pydantic models
```

---

## Rate Limits & Caching

- Google Sheets API: 60 requests/minute (gspread default) — the server enforces this with a token-bucket rate limiter at 55/min
- Sheet data is cached for 30 seconds to minimize API calls on repeated list/search operations
- Agent cache provides 30-minute TTL with 10,000 entry max and LRU eviction
- Audit log keeps last 500 entries in an in-memory ring buffer
- All timestamps are ISO 8601 UTC

---

## License

MIT