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.

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 query (SQL-like: select/where/group by/pivot/order by) against a Google Sheet.

Related MCP server: Google Workspace MCP

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