Architecture

Overview

Sheetbase 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 and one Vercel project serve branch-scoped deployments for beta and production.

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__:<sheet>, TTL = 300s) — used by all write tools for formula/protected-range guards

Range caches are invalidated on every successful write. Structural writes clear all cache entries for the spreadsheet.

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

The hosted Sheetbase project uses GitHub-connected Vercel deployments with separate main and beta branch domains. Self-hosted deployments can still use the Vercel CLI flow described in the self-deploy guide.

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://sheetbase.flonest.app/api/auth/callback/google

See Also

• Authentication
• Safety Systems
• Tools Overview