Database Schema
Core auth and application tables, indexes, and Postgres roles in Sheetbase. Source: SQL migration files and current server modules.
Sheetbase 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 | Historical Better Auth bootstrap without API keys; retained for provenance, not run by fresh installs |
better-auth_migrations/2026-04-13T06-41-17.610Z.sql | Adds apikey to a complete copy of the historical Better Auth bootstrap; use this file for fresh installs instead of running both bootstrap files |
migrations/2026-07-18-better-auth-oauth-forward.sql | Archives the legacy MCP OAuth tables and creates the current OAuth Provider, JWT, and rate-limit schema |
migrations/2026-07-18-better-auth-oauth-rollback.sql | Reviewed rollback for the OAuth Provider migration |
scripts/slice1_write_history.sql | Adds write_history table + prune_write_history() function |
scripts/slice2_sheet_snapshots.sql | Adds sheet_snapshots cache with RLS and no Data API grants |
scripts/slice3_gin_index.sql | GIN index on sheet_snapshots.data |
scripts/slice4_role_isolation.sql | Creates sheet_analyzer Postgres role |
For the hosted founder-preview database, run migrations only through the reviewed migration workflow. Independent fresh installs should follow the ordered commands in Self-Deploy, not infer an order from this reference table.
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 Sheetbase 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 |
oauthClient
OAuth Provider client registrations for compatible MCP clients.
| Column | Type |
|---|---|
id | text PK |
clientId | text unique |
clientSecret | text nullable |
redirectUris | jsonb |
scopes | jsonb |
userId | text FK → user.id |
type | text |
oauthAccessToken
Resource-bound OAuth access tokens issued to MCP clients after consent.
authenticateMcpRequest() verifies the Bearer token against the canonical
issuer, MCP audience, scopes, and JWKS before returning the minimal MCP
identity.
oauthRefreshToken
Refresh tokens associated with the registered client, user, session, expiry, and granted scopes.
oauthConsent
The scopes a user approved for a registered OAuth client.
jwks
Signing keys used to publish and verify the OAuth Provider JWT boundary.
rateLimit
Database-backed Better Auth request counters. Global auth, OAuth Provider, and
API-key limits are enabled in src/lib/auth.ts.
apikey
API keys created from the dashboard. A valid x-api-key header resolves
through the Better Auth API-key session path. OAuth access tokens use
Authorization: Bearer; authenticateMcpRequest() rejects requests that mix
both credential types.
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>' | 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__:<sheet>' 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: value writes clear range caches so subsequent reads see fresh values. Structural writes clear all cache entries for the spreadsheet, including per-sheet schema cache entries.
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.