Database Schema

Every table, index, and Postgres role in sheets-mcp. Source: SQL migration files and route.ts.

sheets-mcp uses Supabase as a Postgres host only — not Supabase Auth. All authentication is handled by better-auth. Two separate pg.Pool instances exist in the codebase (one in lib/db.ts for MCP tools, one inside lib/auth.ts for better-auth) — consolidating them is a known roadmap item.

Migration files

File	Applies
`better-auth_migrations/2026-04-13T03-22-30.752Z.sql`	Core auth tables (`user`, `session`, `account`, `verification`, `oauthApplication`, `oauthAccessToken`, `apikey`)
`better-auth_migrations/2026-04-13T06-41-17.610Z.sql`	Adds `sheet_snapshots` table
`scripts/slice1_write_history.sql`	Adds `write_history` table + `prune_write_history()` function
`scripts/slice3_gin_index.sql`	GIN index on `sheet_snapshots.data`
`scripts/slice4_role_isolation.sql`	Creates `sheet_analyzer` Postgres role

All scripts are idempotent (IF NOT EXISTS). Safe to run multiple times.

Tables

`user`

Standard better-auth user table.

Column	Type	Notes
`id`	`text` PK	—
`name`	`text`	—
`email`	`text` unique	—
`emailVerified`	`boolean`	—
`image`	`text`	nullable
`createdAt`	`timestamptz`	—
`updatedAt`	`timestamptz`	—

`account`

Stores OAuth tokens per provider per user. The Google OAuth token that sheets-mcp uses for all Sheets/Drive API calls lives here.

Column	Type	Notes
`id`	`text` PK	—
`accountId`	`text`	Google sub (user ID)
`providerId`	`text`	`"google"`
`userId`	`text` FK → `user.id`	cascade delete
`accessToken`	`text`	Current Google access token
`refreshToken`	`text`	Used for proactive refresh
`accessTokenExpiresAt`	`timestamptz`	Refresh triggered if within 5 min
`scope`	`text`	Space-separated OAuth scopes

Warning: All column names in this table are camelCase and must be double-quoted in raw SQL queries. lib/mcp-google.ts uses "accessToken", "refreshToken", "accessTokenExpiresAt", "userId" — without quotes, Postgres silently folds to lowercase and returns no rows.

`session`

better-auth session tokens (used for browser cookie sessions).

Column	Type
`id`	`text` PK
`expiresAt`	`timestamptz`
`token`	`text` unique
`userId`	`text` FK → `user.id`
`ipAddress`	`text`
`userAgent`	`text`

`oauthApplication`

OIDC client registrations. Claude Desktop and Cursor each get a row here when they first complete the OAuth handshake.

Column	Type
`id`	`text` PK
`clientId`	`text` unique
`clientSecret`	`text`
`redirectUrls`	`text`
`type`	`text`

`oauthAccessToken`

JWTs issued to MCP clients (Claude, Cursor) after OIDC consent. These are what withMcpAuth verifies on every tool call.

Note

withMcpAuth intercepts the Bearer JWT and passes the decoded OAuthAccessToken as the session object. It does not call auth.api.getSession() (which relies on cookies that headless MCP clients lack). The userId is mapped manually from session.userId || session.user?.id.

`apikey`

API keys created from the dashboard. The apiKey plugin uses enableSessionForAPIKeys: true — a valid x-api-key header resolves to the same user context as an OIDC JWT.

`sheet_snapshots`

Dual-purpose cache table. Used for two distinct cache types identified by range_key:

`range_key` value	Cache type	TTL	Set by	Read by
`'__schema__'`	Sheet schema (frozen rows, protected ranges, ARRAYFORMULA columns)	300 seconds	`getSheetSchema()`	All write tools, `read_range`
A1 range string (e.g. `Sheet1!A:F`)	Range data (JSONB objects)	60 seconds	`analyze_range`	`analyze_range` only

Key columns:

Column	Type	Notes
`user_id`	`text`	—
`spreadsheet_id`	`text`	—
`range_key`	`text`	`'__schema__'` or normalized A1
`row_count`	`integer`	0 for schema cache
`data`	`jsonb`	Schema object or JSONB row array
`expires_at`	`timestamptz`	Checked on every read (`AND expires_at > now()`)
`cached_at`	`timestamptz`	Updated on upsert

Unique constraint: (user_id, spreadsheet_id, range_key) — uses ON CONFLICT ... DO UPDATE for upserts.

GIN index on data column (jsonb_path_ops) — from slice3_gin_index.sql. Accelerates JSONB key lookups inside the SQL execution path.

Invalidation: DELETE FROM sheet_snapshots WHERE user_id = $1 AND spreadsheet_id = $2 — runs after every successful write (write_range, transform_range, clear_range, manage_sheets). Clears both schema and range caches for that spreadsheet.

`write_history`

Pre-write snapshots. Populated before every write_range and transform_range call.

Column	Type	Notes
`id`	`uuid` PK	`gen_random_uuid()`
`user_id`	`text`	—
`spreadsheet_id`	`text`	—
`range_key`	`text`	Normalized A1 range
`tool_name`	`text`	`'write_range'` or `'transform_range'`
`before_values`	`jsonb`	2D array of cell values before write
`after_summary`	`jsonb`	`{ updatedRows, columns }` audit summary (nullable)
`created_at`	`timestamptz`	—

Index: (user_id, spreadsheet_id, created_at DESC) — for fast snapshot listing per spreadsheet.

Retention: prune_write_history(user_id, spreadsheet_id) is called after every insert. Deletes all rows beyond the most recent 50 per user_id + spreadsheet_id.

Snapshot size guard: Snapshot is only saved if rows ≤ 1,000 AND JSON.stringify(values).length ≤ 2MB. Larger ranges skip the snapshot silently.

Postgres role: `sheet_analyzer`

Created by scripts/slice4_role_isolation.sql. Used exclusively by analyze_range and transform_range via SET LOCAL ROLE sheet_analyzer.

Permissions:

GRANT USAGE ON SCHEMA public — allows using Postgres built-in functions
All other privileges explicitly revoked
Cannot read: account, user, session, apikey, sheet_snapshots, write_history

This role is the final defense layer: even if the regex SQL injection guard is bypassed, the query runs as a role with no access to any application data.

-- Applied in every analyze_range + transform_range call:
BEGIN READ ONLY;
SET LOCAL ROLE sheet_analyzer;
-- user SQL executes here
ROLLBACK;

Warning: If sheet_analyzer role does not exist, all analyze_range and transform_range calls fail with role "sheet_analyzer" does not exist. Run scripts/slice4_role_isolation.sql and GRANT sheet_analyzer TO CURRENT_USER before first deployment.

Database Schema

On this page