How We Built OAuth for Google Sheets & Gmail

When we set out to integrate Google Sheets and Gmail into Flikk, the question wasn't whether to use OAuth — it was how to build an OAuth system that scales to dozens of providers without duplicating flow logic for each one.

This post walks through the architecture we landed on: a manifest-driven OAuth system where adding a new provider is a data declaration, not a code change.

The problem

Flikk is a workflow engine. Users build pipelines that read from Google Sheets, send emails via Gmail, post to Slack, and so on. Each of those providers needs credentials — and asking users to manually paste API keys is a terrible experience.

OAuth solves this with the familiar "Connect with Google" button. But implementing OAuth naively means writing a bespoke authorize/callback pair for every provider. We needed something more composable.

Provider manifests

Every provider in Flikk is defined as a typed manifest in packages/core. Here's the Google Sheets definition:

export const googleSheets: Provider = {
	id: "google-sheets",
	name: "Google Sheets",

	credentials: [
		{
			name: "google_sheets_token",
			description: "Google Sheets OAuth access token",
			fields: ["token"],
		},
	],

	oauth: {
		authorizeUrl: "https://accounts.google.com/o/oauth2/v2/auth",
		tokenUrl: "https://oauth2.googleapis.com/token",
		scopes: [
			"https://www.googleapis.com/auth/spreadsheets",
			"https://www.googleapis.com/auth/drive.readonly",
		],
		credentialName: "google_sheets_token",
		extraAuthorizeParams: {
			access_type: "offline",
			prompt: "consent",
		},
	},
};

Gmail is nearly identical — same Google OAuth endpoints, different scopes (gmail.readonly), different credential name (gmail_token). Both share a single Google OAuth app (one GOOGLE_CLIENT_ID and GOOGLE_SECRET in env), with scopes determining what each connection can access.

The key insight: the OAuth flow handler doesn't know or care which provider it's working with. It reads the manifest and acts accordingly.

The three-stage flow

Our OAuth implementation follows the standard authorization code flow, handled by a single Hono router at /internal/oauth/:provider.

Stage 1: Initiate

When a user clicks "Connect" on Google Sheets, we hit GET /internal/oauth/google-sheets. The handler:

1. Resolves the provider manifest and client credentials from environment
2. Validates the user's session (they must be authenticated with an active organization)
3. Encodes a state token — a base64url-encoded JSON payload carrying context through the redirect:

const state = encodeState({
	provider: slug,
	orgId,
	orgSlug,
	userId,
	nonce: crypto.randomUUID(),
});

4. Constructs Google's authorize URL with client_id, redirect_uri, scope, state, and any extraAuthorizeParams (like access_type: "offline" for refresh tokens)
5. Redirects the user to Google's consent screen

Stage 2: Consent

Google shows the user which scopes are being requested. They click "Allow". Google redirects back to our callback URL with an authorization code and the state we sent.

Stage 3: Callback + token exchange

GET /internal/oauth/google-sheets/callback does the heavy lifting:

1. Validate state — decode, verify the provider and org context match. The nonce prevents CSRF replay.
2. Exchange the code for tokens — a server-side POST to Google's token endpoint with the authorization code, client secret, and redirect URI.
3. Fetch provider account info — for Google providers, we call the userinfo endpoint to grab the user's email. This is stored for display/audit purposes (showing "Connected as user@gmail.com" in the UI).
4. Store the grant — persist everything to Postgres.
5. Push to engine — sync the access token to the execution runtime.
6. Redirect — send the user back to their integrations page with a success indicator.

Dual-credential storage

This is where our architecture diverges from typical OAuth implementations. Flikk has two runtimes that need credentials:

The SaaS layer (apps/server + Postgres) manages the OAuth lifecycle — storing grants, tracking expiry, handling revocation, and eventually automating token refresh. This is the oauth_grant table:

export const oauthGrant = pgTable("oauth_grant", {
	id: text("id").primaryKey(),
	organizationId: text("organization_id").notNull(),
	provider: text("provider").notNull(),
	credentialName: text("credential_name").notNull(),
	accessToken: text("access_token").notNull(),
	refreshToken: text("refresh_token"),
	scopes: text("scopes").notNull(),
	accessTokenExpiresAt: timestamp("access_token_expires_at"),
	providerAccountId: text("provider_account_id"),
	connectedBy: text("connected_by").notNull(),
	revokedAt: timestamp("revoked_at"),
	// ...timestamps
});

The engine (apps/engine) executes workflows and needs access tokens at runtime. It has its own credential store, decoupled from Postgres. After a successful OAuth callback, we push the token:

await engineFetch("/credentials", orgSlug, {
	method: "POST",
	body: JSON.stringify({
		name: config.credentialName,
		value: { token: tokens.access_token },
		expiresAt: expiresAtUnix,
	}),
});

This separation is intentional. The engine trusts the server via a service token and never touches Postgres directly. If we swap the engine's storage layer tomorrow, the OAuth flow doesn't change.

Schema design decisions

A few things worth calling out in the oauth_grant schema:

Org-scoped with unique constraint. (organizationId, credentialName) is a unique index. Each org can have one active grant per credential type. Re-connecting overwrites the previous grant via upsert.

Soft revocation. The revokedAt field lets us mark grants as revoked without deleting data. All queries filter on isNull(revokedAt). This preserves an audit trail — useful when debugging why a workflow stopped working.

Expiry tracking with index. accessTokenExpiresAt has a dedicated index. This powers the getExpiringGrants(windowMinutes) query that finds grants about to expire — infrastructure for the automatic refresh job we're building next.

Provider account ID. For Google, this is the user's email. It's not used for auth — it's purely for the UI to show which Google account was connected.

What's next: automatic token refresh

The infrastructure for token refresh is in place but not yet automated. We have:

• refreshToken stored in every grant (Google provides this when access_type: "offline")
• getExpiringGrants() to find grants expiring within a time window
• updateGrantTokens() to write refreshed tokens back

The missing piece is a background job that periodically calls Google's token endpoint with grant_type: "refresh_token", updates Postgres, and pushes the new access token to the engine. That's coming soon.

Adding a new OAuth provider

This is the payoff of the manifest-driven approach. To add a new OAuth provider:

1. Define the provider in packages/core with its oauth config (authorize URL, token URL, scopes)
2. Add the client credential mapping in apps/server/src/oauth/config.ts
3. Set the env vars

No new routes. No new callback handlers. The same /internal/oauth/:provider flow handles everything.

---

The full implementation lives in apps/server/src/oauth/ if you want to dig deeper. We'll follow up with a post on the token refresh system once it's live.