Architecture

Overview

sheets-mcp is a single Next.js 16 application on Vercel serving three simultaneous roles:

1. OIDC Provider — issues JWTs to MCP clients via better-auth's mcp() plugin
2. MCP Server — handles all 14 tool calls over SSE and HTTP transports
3. OAuth Proxy — exchanges and auto-refreshes Google OAuth tokens on every request

There is no separate auth service, API gateway, or sidecar. One repo, one Vercel deployment.

sequenceDiagram
  participant Client as MCP Client
  participant Proxy as proxy.ts
  participant Core as /api/[transport]
  participant DB as Supabase / Postgres
  participant Sheets as Google Sheets API

  Client->>Proxy: Bearer JWT or x-api-key
  Proxy->>Core: Forward request
  Core->>DB: Resolve auth + session
  Core->>Sheets: Call tool handler
  Sheets-->>Core: Result
  Core-->>Client: MCP response

Request flow

MCP Client (Claude / Cursor / API key script)
  |
  |  Bearer JWT   -- OIDC path
  |  x-api-key    -- API key path
  v
proxy.ts (Next.js middleware)
  |
  +-- Serves /.well-known/oauth-* metadata (OIDC discovery)
  +-- Passes all other requests through
        |
        v
/api/[transport]/route.ts   (MCP core)
  |
  +-- withMcpAuth()         -- resolves JWT or API key → session
  +-- createMcpHandler()    -- routes tool name → handler
  |
  +-- getGoogleClients()    -- fetches + proactively refreshes OAuth token
  |     +-- sheetsClient    (Sheets API v4)
  |     +-- driveClient     (Drive API v3)
  |     +-- scriptClient    (Apps Script API v1)
  |
  +-- Safety guards         -- formula check, schema guard, protected range
  |
  +-- Google Sheets API v4 → result → MCP tool response → client

Key source files

proxy.ts — OIDC discovery middleware

Intercepts all /.well-known/oauth-* paths and returns the correct OAuth metadata JSON. This is what allows Claude Desktop and Cursor to discover the OIDC endpoints automatically without manual config.

Served endpoints:

• /.well-known/oauth-authorization-server
• /.well-known/openid-configuration
• /.well-known/oauth-protected-resource
• /.well-known/oauth-protected-resource/{path} (per-resource variant)

/api/[transport]/route.ts — MCP transport

The core of the server. Key facts from the source:

• maxDuration: 60 — Vercel function timeout for long Sheets API operations
• MAX_RESPONSE_ROWS = 1000, MAX_RESPONSE_BYTES = 2MB — response clamp constants
• [transport] dynamic segment is required — mcp-handler uses it to negotiate SSE vs HTTP. Hardcoding /api/mcp would break SSE.
• Full CORS headers on every response (Access-Control-Allow-Origin: *)

lib/auth.ts — better-auth config

betterAuth({
  emailAndPassword: { enabled: true },   // email+password signup supported
  socialProviders: { google: {
    scope: ['spreadsheets', 'drive.readonly', 'drive.file'],
    prompt: "select_account consent"     // forces account picker every time
  }},
  plugins: [
    mcp({ loginPage: "/login", consentPage: "/authorize" }),
    apiKey({ enableSessionForAPIKeys: true, rateLimit: { enabled: false } })
  ],
  rateLimit: { enabled: false }          // intentionally disabled pre-launch
})

lib/mcp-google.ts — token retrieval + auto-refresh

Reads the Google OAuth token from the Supabase account table (camelCase columns — always quoted in raw SQL: "accessToken", "userId"). Proactively refreshes if the token expires within 5 minutes. Persists the refreshed token back to Supabase immediately.

// Proactive refresh condition (from source):
const isExpiredOrClose = !expiryDateMs || (expiryDateMs < Date.now() + 5 * 60 * 1000);

If refresh fails, continues with the existing token (may still be valid) and clears expiry_date to prevent the google-auth-library from looping on failed auto-refresh.

lib/db.ts — Postgres pool

new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 3,                    // max 3 connections — conservative for Vercel serverless
  idleTimeoutMillis: 5000,
  connectionTimeoutMillis: 5000,
})

Two separate Pool instances exist: one in lib/db.ts (used by MCP tools) and one inside lib/auth.ts (used by better-auth). This is a known technical debt item — they are not consolidated.

src/instrumentation.ts — OpenTelemetry

// ConsoleSpanExporter → stdout
// Designed specifically for agent-visible trace output:
// "Gives the Antigravity Agent native X-Ray vision to read traces
//  directly via 'command_status' output logs"

Traces are exported to console stdout only — not to a remote OTLP collector. This is intentional for the current build stage: traces are visible in Vercel function logs.

Database schema

Tables (from migrations + SQL scripts)

sheet_snapshots

Dual-purpose cache table:

• Range data cache (key = normalized A1 range, TTL = 60s) — used by analyze_range
• Schema cache (key = __schema__, TTL = 300s) — used by all write tools for formula/protected-range guards

Invalidated on every successful write by DELETE FROM sheet_snapshots WHERE user_id = $1 AND spreadsheet_id = $2.

write_history + prune_write_history()

Stores before_values JSONB snapshots before every write_range and transform_range call. A Postgres function prune_write_history() is called after every insert to keep the last 50 snapshots per user+spreadsheet. Not a trigger — called explicitly.

sheet_analyzer Postgres role

A restricted role with no table privileges, used by analyze_range and transform_range via SET LOCAL ROLE sheet_analyzer. Cannot read account, user, session, apikey, sheet_snapshots, or write_history — even if SQL injection bypasses the regex guard.

Deployment

Deployed to Vercel via CLI only (vercel --prod). No GitHub ↔ Vercel integration.

Required environment variables

Warning: NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY must be set. The @supabase/ssr package is in the dependency tree and the Edge runtime will throw 500 if these are missing, even if you don't use Supabase SSR middleware.

Google Cloud Console requirements

• Sheets API enabled
• Drive API enabled
• OAuth consent screen configured (add test users if in "Testing" mode)
• OAuth Client ID (Web Application) with redirect URI: https://sheets-mcp-xi.vercel.app/api/auth/callback/google

What to do next

• Authentication
• Safety Systems
• Tools Overview