# shared-skill-mcp

An AWS-hosted MCP server for claude.ai custom connectors, built to hold more
than one tool over time — not restricted to the Google Sheets query tool it
started with. One shared Cognito auth layer (Google login) + one Bedrock
AgentCore Gateway (the actual MCP server) + one Lambda per tool.

Full spec, architecture diagram, and phase-by-phase history for the original
`query_sheet` tool: `Reel AI Workers/skills-spec/sheet-gviz/sheet-gviz.md`.

## Status (2026-08-24)

**Live and confirmed working end-to-end**, including a real claude.ai
connector completing Google login and calling the tool — not just curl.

| Thing | Value |
|---|---|
| MCP server URL (Gateway) | `https://sheets-gviz-gateway-63psdjvcgs.gateway.bedrock-agentcore.eu-west-1.amazonaws.com/mcp` |
| Cognito domain | `sheets-gviz-b24dc744.auth.eu-west-1.amazoncognito.com` |
| User Pool ID | `eu-west-1_sfGqYcC0a` |
| AWS account | `423566941862`, `eu-west-1` |

Get current values (including secrets) any time:
```
AWS_PROFILE=<your profile> terraform -chdir=terraform output
AWS_PROFILE=<your profile> terraform -chdir=terraform output -raw cognito_client_secret
```

Tools currently exposed: **`query_sheet`** — runs a
[gviz](https://developers.google.com/chart/interactive/docs/querylanguage)
query (SQL-like: `select`/`where`/`group by`/`pivot`/`order by`) against a
Google Sheet.

## Architecture

```
claude.ai connector
      │  OAuth 2.1 (real Google login, via Cognito's Hosted UI)
      ▼
Cognito User Pool ──federates to──> Google (login only)
      │  issues an access token (no "aud" claim — see gotcha below)
      ▼
AgentCore Gateway (CUSTOM_JWT authorizer, matches by client_id)
      │  invokes under its own service role
      ▼
Lambda tool target (gateway-tool-handler.mjs) ──> gviz.js ──> Google Sheets API
```

Two Google OAuth clients exist and must stay distinct: one Cognito uses for
login (federated identity), one `gviz.js` uses to read Sheets (a service
credential, refresh-token based, never used interactively). Reusing one for
both was deliberately avoided — see the spec doc's "two identities" framing.

## How this repo got here (worth reading before changing the auth setup)

The first working version used a hand-rolled Lambda Function URL as the MCP
server, with the Lambda itself verifying Cognito JWTs and serving OAuth
discovery metadata (RFC 9728 / RFC 8414) by hand. It worked over curl and a
manual Postman OAuth flow — full round-trip, real Google login, real Sheet
data — but claude.ai's actual connector client failed silently against it
every time (`Couldn't connect` / `Authorization failed`), with zero
indication of why: the Lambda's logs showed claude.ai fetching discovery
metadata once, then going silent — no token exchange, no error, nothing.

Working theory at the time: Cognito's access tokens don't carry an `aud`
claim (a known, real Cognito limitation — confirmed by decoding a real
token), and the MCP spec expects the `resource` parameter a client sends to
be reflected into that claim. Recent, credible-looking evidence from
elsewhere in the ecosystem seemed to back this up as *the* answer. It turned
out to be a red herring — a working reference from a separate project (same
person, different repo) proved Cognito + claude.ai connectors work fine,
using **Bedrock AgentCore Gateway** in front of Cognito instead of a
hand-rolled server, with its `CUSTOM_JWT` authorizer matching by
`allowed_clients` (Cognito's `client_id`), not `aud`. The actual root cause
of the original failure was never conclusively pinned down — most likely
something in the hand-rolled JSON-RPC/discovery implementation not quite
matching what claude.ai's client expects, despite passing every manual test
thrown at it.

**Lesson for next time:** a spec-compliant-looking hand-rolled MCP server
that passes curl and Postman tests is not proof it works against claude.ai's
actual client — the two can diverge in ways that produce zero error
signal. Prefer AWS's own MCP server implementation (AgentCore Gateway) over
reimplementing MCP + OAuth discovery by hand, even though it means one more
AWS service to learn and a currently-rougher Terraform provider surface
(see gotchas below).

The hand-rolled Function URL server (`sheets-gviz-mcp` Lambda,
`modules/mcp-lambda`, `src/lambda-handler.mjs`, `src/auth.mjs`) was
decommissioned once Gateway was confirmed working — torn down via Terraform
(zero impact to the still-live Cognito User Pool/domain/app client, which
the Gateway path reuses as-is) and deleted from the repo. It's preserved in
git history if the reasoning or code is ever useful again.

### Gotchas hit along the way (fixed in code, worth knowing before touching this again)

**Sheets/gviz layer** (`src/query-sheet/gviz.js`):
1. The OAuth token needs both `.../auth/spreadsheets` and
   `.../auth/spreadsheets.readonly` scopes — `readonly` alone gets a 401
   that looks like an HTML login page, not a clean error.
2. gviz's `/tq` endpoint needs an `/a/<domain>/` path segment even for OAuth
   bearer auth — `docs.google.com/a/google.com/spreadsheets/d/<id>/gviz/tq`.
   Configurable via `GVIZ_DOMAIN_SEGMENT` if `google.com` doesn't work for
   your account's domain.
3. Always pass `headers=1` (hardcoded in `querySheet`). Without it, gviz's
   header-row auto-detection can get it wrong and silently fold real data
   rows into `cols[].label` as one giant string, losing them entirely.

**AWS/Terraform layer:**
4. IAM identity-policy changes can take tens of seconds to a few minutes to
   actually take effect, even after `aws iam simulate-principal-policy`
   confirms them correct immediately. Expect a fresh `plan`/`apply` to 403
   once right after granting a new action — not a sign anything is wrong,
   just retry after a short wait.
5. ESM `.js` files need their own `package.json` (`{"type": "module"}`) if
   they're zipped without the repo root's — `gviz.js` uses `export`/`import`
   and only resolves as ESM because of that file being present alongside it
   in every Lambda's zip (`src/query-sheet/package.json`).
6. `aws_bedrockagentcore_*` Terraform resources are recent and evolving —
   confirm actual argument shapes against the provider's own schema
   (`terraform providers schema -json`) rather than trusting docs/blog posts,
   which lag. Requires provider `>= 6.0`.
7. AgentCore Gateway's `CUSTOM_JWT` authorizer matches callers by
   `allowed_clients` (Cognito's `client_id`), not `allowed_audience` — this
   is what makes it work with Cognito's non-standard (no `aud`) access
   tokens without any extra token-minting layer.
8. Reorganizing live Terraform resources into modules risked destroying
   them. Both the Phase 4→module refactor and the later Function URL
   decommission used `terraform state mv` and a real `plan` check (0
   destroy of anything meant to survive) before every `apply` — the app
   client claude.ai already has credentials for was moved twice this way
   without ever being destroyed/recreated.

## Setup

### 1. Prove the Sheets credential works (standalone, no AWS needed)

```
cp .env.example .env   # fill in GOOGLE_CLIENT_ID/SECRET/REFRESH_TOKEN, SPREADSHEET_ID
node scripts/phase1-test.mjs "select *"
```
**Done when:** it prints `{columns, rows}` for a real query. Failures here
are Google-side (scope, sharing, Sheets API not enabled) — cheapest place to
catch them before touching AWS.

### 2. Deploy Cognito + Gateway + the tool Lambda

Needs AWS credentials with the permissions in `terraform/iam-policy.json`,
and a **second Google OAuth client** (Web application, separate from the
Sheets-reading one) for Cognito login. Its redirect URI needs Cognito's
domain, which doesn't exist yet — break that chicken-and-egg with a partial
apply first:
```
scripts/tf.sh apply -target=module.auth.aws_cognito_user_pool.this \
  -target=module.auth.aws_cognito_user_pool_domain.this
```
Create the Google OAuth client with redirect URI
`https://<that domain output>/oauth2/idpresponse`, fill in
`GOOGLE_LOGIN_CLIENT_ID`/`GOOGLE_LOGIN_CLIENT_SECRET` in `.env`, then:
```
scripts/tf.sh apply
```

`CLAUDE_OAUTH_REDIRECT_URI` doesn't need setting — defaults to
`https://claude.ai/api/mcp/auth_callback`, confirmed working for a real
connector.

### 3. Add it as a claude.ai connector

Settings → Connectors → Add custom connector:
- Server URL: the `gateway_url` output
- Advanced settings → OAuth Client ID/Secret: `cognito_client_id` /
  `cognito_client_secret` outputs

Should trigger a real Google login via Cognito's Hosted UI, then let Claude
call `query_sheet` (visible in-chat as a tool-use block).

## Adding a new tool

1. Write a Lambda handler using the AgentCore Lambda-target contract — flat
   `event` = the tool's arguments, no JSON-RPC wrapping (Gateway handles MCP
   framing). See `src/query-sheet/gateway-tool-handler.mjs` for the pattern.
2. Add a `module "..." { source = "./modules/gateway-tool-lambda" ... }`
   block in `main.tf`.
3. Add an `aws_bedrockagentcore_gateway_target` block for it — either extend
   `modules/agentcore-gateway` to take a list of targets, or add the
   resource directly in `main.tf` pointed at `module.gateway.gateway_id`.

No new Google OAuth client, no new Cognito domain, no new Gateway needed —
everything in `module.auth` and `module.gateway` is shared.

## Layout

```
src/
  gviz.js                  Sheets-reading logic — token refresh, gviz query, response
                            parsing. Host-agnostic; used by gateway-tool-handler.mjs.
  gateway-tool-handler.mjs AgentCore Gateway Lambda-target contract for query_sheet —
                            flat event-in/JSON-out, no JSON-RPC framing (Gateway
                            handles MCP protocol translation itself).
  package.json              {"type": "module"} — required for gviz.js's ESM syntax to
                            resolve once zipped alone, without the repo root's
                            package.json alongside it.
scripts/
  phase1-test.mjs           Standalone local proof the Sheets credential + gviz query
                            round-trip works, no AWS involved.
  tf.sh                     Wraps `terraform` with GOOGLE_*/Cognito vars sourced from
                            .env — use this instead of calling terraform directly.
terraform/
  main.tf                   Root — provider, variables, the shared auth module, the
                            claude.ai connector's Cognito app client, the Gateway, and
                            the query_sheet tool Lambda.
  modules/mcp-auth/         Cognito User Pool + Google identity provider + Hosted UI
                            domain. Shared — instantiate once per AWS account.
  modules/agentcore-gateway/ The Gateway (CUSTOM_JWT authorizer) + the query_sheet
                            Gateway Target. Extend for more targets, or add more
                            gateways for a genuinely separate trust boundary.
  modules/gateway-tool-lambda/ A standalone tool Lambda for a Gateway target — no
                            Function URL, no public permissions, no own Cognito
                            client. Gateway is the only caller, via its service role.
  iam-policy.json            Deploy-time IAM policy for whatever AWS identity runs
                            scripts/tf.sh. Broad on bedrock-agentcore:* deliberately —
                            that service/provider surface is new and evolving.
```