Westside MCP

story: Tournament Email Draft via claude.ai

Role

Admin (Lucas)

Key

tournament-email-draft

Want

As an admin, I want to describe a tournament conversationally inside claude.ai and have a draft email land in Marcus's inbox for approval

So That

So that I can capture tournament email work without leaving claude.ai or routing through Claude Code's multi-step pipeline — drafting becomes a 30-second chat instead of a 16-step orchestration.

Acceptance Criteria

• claude.ai connector for westside-mcp surfaces the draft_tournament_email tool with a typed parameter schema (tournament_name, tournament_dates, player_fee, teams)
• Calling the tool with all required params produces a plain-text draft rendered from the canonical template
• The draft is emailed FROM westsidebasktball@gmail.com TO Marcus (mldraney3@gmail.com) with a [Draft] subject prefix
• Marcus reads the draft on his phone and approves out-of-band — no automated player-pool send happens from this MCP tool
• Lucas can complete the entire flow (tool call → Marcus has draft) in under 30 seconds from claude.ai, no Claude Code, no terminal, no admin UI

Success Metric

Lucas drafts a tournament email through claude.ai in 30 seconds without opening Claude Code, a terminal, or any westside admin UI. Marcus receives the draft within 60 seconds of Lucas hitting send in claude.ai.

Related Architecture

• arch:mcp-tools — MCP server tools layer (canonical, see convention-architecture-ids)
• arch:postgres — token store (read-only access to basketball-api's oauth_tokens table)

Related

• page-westside-mcp — parent project page (to be created as a follow-up)
• board-westside-mcp — project board where tickets serving this story live
• westside-email-agent — sibling project (the actual email composition and blast logic; this MCP tool drafts something that flows into that pipeline)
• notion-mcp-remote, gdocs-daily-mcp-remote — pattern precedents for remote MCP servers

Deployment: westside-mcp

Diagram

graph TB
    subgraph phone["Marcus's iPhone"]
        iOS["Claude iOS App"]
    end

    subgraph cloud["Claude Cloud"]
        Web["claude.ai web"]
    end
    iOS -. connector sync .-> Web

    Web -->|"Streamable HTTP + OAuth"| Funnel["westside-mcp.tail5b443a.ts.net
Tailscale funnel"]

    subgraph k3s_mcp["k3s · westside-mcp namespace"]
        Pod["westside-mcp-remote pod"]
        Auth["mcp-remote-auth
OAuth proxy"]
        Ping["ping"]
        Catalog["get_catalog()"]
        Query["query(sql)"]
        Pod --> Auth
        Pod --> Ping
        Pod --> Catalog
        Pod --> Query
    end
    Funnel --> Pod

    subgraph k3s_db["k3s · CNPG cluster"]
        RO[("basketball-api-db-ro
read replica")]
        Primary[("basketball-api-db-rw
primary")]
        Primary -. streaming replication .-> RO
    end

    Catalog -->|"information_schema
as marcus_readonly"| RO
    Query -->|"SELECT only
as marcus_readonly"| RO

    subgraph k3s_api["k3s · basketball-api namespace"]
        API["basketball-api
untouched"]
    end
    API -->|"reads + writes"| Primary

    subgraph obs["Observability"]
        Prom["Prometheus"]
        Loki["Loki"]
        Graf["Grafana
westside-mcp dashboard"]
    end
    Pod -. "/metrics" .-> Prom
    Pod -. "audit + query logs" .-> Loki
    Prom --> Graf
    Loki --> Graf

    subgraph bootstrap["Consumed from pal-e-platform"]
        Harbor["Harbor registry"]
        Woody["Woodpecker CI"]
        ArgoCD["ArgoCD"]
    end
    Woody -. "builds + pushes" .-> Harbor
    Harbor -. "image pull" .-> Pod
    ArgoCD -. "syncs kustomize" .-> Pod

    classDef untouched fill:#eee,stroke:#999,stroke-dasharray: 3 3
    class API,Primary untouched

Components

Key Decisions

• pal-e-platform is untouched. Every capability we need (Tailscale, CNPG, Harbor, Woodpecker, Prom/Loki, ArgoCD) is already provided by the bootstrap repo. New work is Terraform in pal-e-services + kustomize in pal-e-deployments + new code repo. Three-repo split does its job.
• Connect to -ro replica, not primary. Physical write-impossibility. Slightly stale reads (milliseconds) are acceptable because Marcus is asking aggregate questions, not racing against transactions.
• DB credentials live in a k8s Secret provisioned by pal-e-services. Not in the MCP repo, not in the kustomize overlay. Rotations happen at the Terraform layer.
• Tailscale funnel, not an ingress controller. Matches platform convention for public-facing services. Zero cert-manager, zero Traefik — Tailscale terminates TLS.
• Single replica pod in v1. MCP traffic is one human asking occasional questions. HPA + multi-replica is a Stage 3 concern if QPS grows.
• Observability is "free." /metrics endpoint + stdout JSON logs automatically picked up by the cluster's Prom/Loki stack. ServiceMonitor is the only overlay addition.

Related

• project-westside-mcp — project page
• arch-domain-westside-mcp — entities
• arch-dataflow-westside-mcp — runtime flow
• sop-network-security — Tailscale funnel + network policy conventions
• service-onboarding-sop — pal-e-services + pal-e-deployments workflow

Data Flow: westside-mcp

Diagram

One primary flow: Marcus asks a natural-language question, MCP loads the catalog, Claude writes SQL, the replica answers, result comes back to Marcus's phone.

sequenceDiagram
    actor Marcus
    participant iOS as Claude iOS
    participant Web as claude.ai
    participant TS as Tailscale Funnel
    participant MCP as westside-mcp pod
    participant Auth as mcp-remote-auth
    participant RO as CNPG -ro replica
    participant Loki

    Marcus->>iOS: "How many players haven't paid jersey fees?"
    iOS->>Web: Streamable HTTP request (OAuth bearer)
    Web->>TS: POST /mcp (JSON-RPC)
    TS->>MCP: forward
    MCP->>Auth: validate token
    Auth-->>MCP: ok (westsidebasktball@gmail.com)

    Note over MCP: First call this session
    MCP->>RO: SELECT * FROM information_schema...
    RO-->>MCP: tables, columns, FKs
    MCP-->>Web: get_catalog() result
    Web->>Web: Claude reasons over schema

    Web->>TS: POST /mcp query(sql="SELECT ...")
    TS->>MCP: forward
    MCP->>MCP: sqlparse: SELECT only? ok
    MCP->>RO: BEGIN READ ONLY; SET statement_timeout=5000; SELECT ... LIMIT 1000
    RO-->>MCP: rows
    MCP->>Loki: audit log {session, sql, rows, ms}
    MCP-->>Web: {columns, rows}
    Web-->>iOS: "8 players haven't paid: ..."
    iOS-->>Marcus: answer

Components

Key Decisions

• First call in a session is always get_catalog(). Prevents Claude from guessing at the schema. Cheap (one information_schema query) and makes every subsequent query() grounded.
• SQL is parsed at the MCP layer before execution. Non-SELECT statements rejected before the DB sees them. This is belt-and-suspenders with the replica's physical write block.
• Every query writes an audit log, including failures. Loki is the source of truth for "what did Marcus ask?" — essential for debugging, compliance, and tuning the catalog.
• Two-layer write protection. Role-level SELECT-only + replica endpoint. A role bug alone wouldn't breach; the replica physically refuses writes.
• No streaming / no async results. JSON-RPC request/response is simpler than streaming partial results, and query timeouts are short enough that streaming wouldn't help. Revisit if queries ever legitimately need >5s.

Related

• project-westside-mcp — project page
• arch-domain-westside-mcp — entities
• arch-deployment-westside-mcp — where these services run
• story-westside-mcp-asks-data — the user story this flow satisfies

Domain Model: westside-mcp

Diagram

erDiagram
    CONNECTOR ||--|| OAUTH_CLIENT : "authenticates via"
    CONNECTOR ||--o{ SESSION : "opens"
    SESSION }o--|| USER : "belongs to"
    SESSION ||--o{ TOOL_CALL : "makes"
    TOOL_CALL }o--|| TOOL : "invokes"
    TOOL ||--o{ QUERY : "executes"
    QUERY }o--|| DB_ROLE : "runs as"
    DB_ROLE ||--|| CNPG_CLUSTER : "granted on"
    CATALOG }o--|| CNPG_CLUSTER : "introspected from"
    TOOL ||--o| CATALOG : "may load"
    AUDIT_LOG }o--|| TOOL_CALL : "records"

    CONNECTOR {
        string url "westside-mcp.tail5b443a.ts.net"
        string transport "streamable-http"
    }
    USER {
        string email "westsidebasktball@gmail.com"
        string claude_account
    }
    TOOL {
        string name "ping | get_catalog | query"
        string kind "health | schema | data"
    }
    QUERY {
        text sql
        int row_limit "1000"
        int timeout_ms "5000"
        bool read_only "true"
    }
    DB_ROLE {
        string name "marcus_readonly"
        string grants "SELECT only"
    }
    CATALOG {
        string layer1 "live information_schema"
        string layer2 "catalog/westside.yaml"
    }
    AUDIT_LOG {
        string session_id
        text sql
        int duration_ms
        int row_count
    }

Components

Key Decisions

• One USER in v1. Single-tenant keeps auth simple. Multi-user would require per-user roles and row-level security — out of scope until a second human needs access.
• CATALOG is derived, not stored. Treating it as an entity clarifies that Claude reads it, but physically it regenerates on pod start from information_schema. No schema drift possible between "what the catalog says" and "what the DB actually has."
• AUDIT_LOG is logical, not physical. Persisting to Postgres would require write grants — defeating the point. Loki gives us the same query surface ("what did Marcus ask yesterday?") without any write path.
• basketball-api is deliberately absent. The MCP does not talk to the API. Keeping it off the domain model makes this invariant visible.
• DB_ROLE is the enforcement boundary. Not the app, not OAuth — Postgres role grants. This is the only layer that physically cannot be bypassed by code bugs.

Related

• project-westside-mcp — project page
• arch-dataflow-westside-mcp — runtime flow
• arch-deployment-westside-mcp — infra topology
• story-westside-mcp-safety — Lucas's read-only guarantees
• convention-architecture-ids — how arch: labels derive from this table

Vision

A read-only remote MCP server that lets Marcus ask Claude any natural-language question about Westside Basketball data from his iPhone. The MCP connects directly to the CNPG Postgres read-replica as a marcus_readonly role and exposes a live schema catalog so Claude writes SQL dynamically — nothing is hardcoded, migrations can't silently break Marcus's tools. Deployed via the pal-e-platform pattern: Tailscale funnel, Harbor image, Woodpecker CI, ArgoCD rollout, Prometheus/Loki observability. Registered in claude.ai under westsidebasktball@gmail.com so the Claude iOS app auto-syncs the connector.

User Stories

Architecture

1. arch-domain-westside-mcp — entities: Connector, Tool, Catalog, Role, Query
2. arch-dataflow-westside-mcp — Marcus asks a question → iOS → claude.ai → MCP → Postgres → answer
3. arch-deployment-westside-mcp — Tailscale funnel → k3s pod → CNPG -ro replica, observability wired

Key decisions:

• Direct Postgres, not via basketball-api. Read-only enforced at the Postgres role level.
• Connects to CNPG -ro replica endpoint, not primary. Two-layer write protection.
• Live information_schema introspection (Layer 1 catalog). Semantic YAML annotations (Layer 2) added reactively only where Claude guesses wrong.
• Drift-check CI compares live schema against catalog YAML — migrations can't silently break the MCP.
• Streamable HTTP + OAuth via shared mcp-remote-auth pattern (same as gmail/gcal/notion remotes).
• Single-user gated to westsidebasktball@gmail.com.

Board

board-westside-mcp — the kanban for this project. 10 initial build tickets across three stages.

Status

2026-04-10: Project created. 10 backlog tickets pending /review-ticket. No code, no repos, no terraform yet.

Stages:

• Stage 0 — Discovery: schema audit + sensitive-column inventory + 10 sample questions corpus (docs only)
• Stage 1 — Hello-world remote MCP: repo scaffold, CI, pal-e-services + pal-e-deployments, ping tool, claude.ai URL registration, iPhone verification
• Stage 2 — Catalog + query: marcus_readonly CNPG role, get_catalog() tool, query(sql) tool with safety enforcement, 10-question validation
• Stage 3 (deferred): Grafana dashboard, rate limiting, alerting, runbook
• Stage 4 (deferred): semantic annotations + cross-repo drift check

Win condition: Live westside-mcp.tail5b443a.ts.net connector registered in claude.ai, Marcus uses it from his iPhone, and he can ask any natural-language question about Westside data and get a correct answer via a live schema index — zero hardcoded tables, zero manual SQL, zero write-path risk.

Milestones

• No milestones yet. First milestone will be milestone-2026-MM-DD-stage-1-hello-world-live — Marcus pings the MCP from his iPhone.

Repos

User Story

As Lucas, platform operator,
I want Marcus's data access to be read-only, audited, and migration-safe,
so that empowering Marcus never becomes a prod risk, a silent-breakage liability, or a compliance problem.

Guarantees this story demands

• No write path exists. Two-layer enforcement: marcus_readonly Postgres role has only SELECT grants, AND the connection targets the CNPG read-replica endpoint. Either layer alone would block writes; both together is defense in depth.
• Sensitive columns are invisible. Password hashes, Stripe IDs, OAuth tokens, internal notes — explicitly excluded via column-level GRANT or view layer. Listed in the Stage-0 sensitive column inventory.
• Migrations can't silently break the MCP. The drift-check CI (Stage 2+) compares live information_schema against the catalog YAML. Any schema change that orphans a referenced column fails the pipeline.
• Every query is audited. Loki records session ID, SQL, row count, duration, error state. "What did Marcus ask and when?" is always answerable.
• Runaway queries can't hurt the primary. Replica-only reads + 5s statement timeout + 1000 row limit. A bad query degrades the replica briefly, not the primary.
• Revoking access is a single step. Runbook documents: disable the OAuth client OR drop the Postgres role. Either cuts Marcus off immediately.

Success Metric

Zero write-path incidents. Zero silent-breakage incidents across at least one basketball-api schema migration after Stage 2 ships. Runbook rehearsed (access-revocation tested at least once).

User Story

As Marcus, operating Westside Basketball day-to-day from my iPhone,
I want to ask Claude natural-language questions about our players, teams, schedules, registrations, and payments,
so that I can answer parent/player questions on the spot without logging into an admin panel, DMing Lucas, or waiting for someone to pull data.

Examples of questions Marcus should be able to ask

• "How many players haven't paid their jersey fees yet?"
• "Which teams have no coach assigned?"
• "What's the total registration revenue for this tournament?"
• "Who signed up for the April 10-11 tryout but hasn't signed the contract?"
• "How many sessions did we book at SchoolSpace this month?"
• "Which players are on more than one team?"
• "What's the average age of players in the 5th-grade division?"
• "Who are the contacts for team X?"
• "What's the most recent payment from parent Y?"
• "How many active vs inactive players do we have?"

These 10 questions become the Stage-0 sample corpus and the Stage-2 validation gate.

Success Metric

Marcus can ask any of the 10 sample questions from the Claude iOS app on his phone, and Claude returns a correct answer derived from live Postgres data — without Marcus needing to know the schema, SQL, or which table to look in.

Out of Scope

• Writes (any kind). This is read-only.
• Questions spanning databases other than basketball-api's CNPG cluster (v1).
• Self-service admin UIs — Marcus interacts via natural language in Claude, not custom screens.