Google Search Console Integration Architecture and Data Sync in Open SEO

Open SEO implements a layered GSC integration that separates OAuth credential storage, connection persistence, REST client logic, and service orchestration to enable on-demand access to Search Console data without background synchronization.

The Open SEO platform connects to Google Search Console through a clean GSC integration architecture that isolates authentication concerns from business logic. This design allows users to query real-time search analytics and perform URL inspections while securely managing OAuth tokens through Better Auth.

Architecture Overview

The integration follows a six-layer architecture that separates concerns from credential storage to public API exposure:

• OAuth / Credential Store: Manages Google OAuth grants using Better Auth, storing access and refresh tokens in the account table under the provider ID google-search-console defined in src/shared/gsc.ts
• Connection Record: Links projects to verified GSC properties via the gsc_connections table, handled by src/server/features/gsc/repositories/GscConnectionRepository.ts
• GSC Client: Thin wrapper around Google's Webmasters v3 and URL Inspection REST endpoints located in src/server/lib/gscClient.ts
• Service Layer: Orchestrates business rules and validation in src/server/features/gsc/services/GscService.ts
• MCP Tooling: Public-facing tools in src/server/mcp/tools/search-console-tools.ts used by the UI and agent interface
• Database Schema: PostgreSQL and SQLite table definitions in src/db/pg/gsc.schema.ts and src/db/schema.ts

OAuth Credential Management

Authentication begins when a user initiates a connection through the Better Auth system. The platform uses the provider constant GSC_OAUTH_PROVIDER_ID (set to google-search-console) from src/shared/gsc.ts to identify and store OAuth grants.

Tokens persist in the Better Auth account table (referenced in src/db/schema.ts), allowing the system to mint fresh access tokens on demand. When the createGscClient factory function needs authentication, it invokes getAuth().api.getAccessToken with the stored userId and optional gscAccountId. If token minting fails, the client throws a GscTokenError (lines 19-30 of src/server/lib/gscClient.ts), triggering the reconnection flow in the UI.

Connection Persistence

Project-property relationships reside in the gsc_connections table defined in src/db/pg/gsc.schema.ts. The schema enforces data integrity through foreign key constraints and unique indexes:

// src/db/pg/gsc.schema.ts
export const gscConnections = pgTable(
  "gsc_connections",
  {
    id: text("id").primaryKey(),
    projectId: text("project_id").notNull()
      .references(() => projects.id, { onDelete: "cascade" }),
    organizationId: text("organization_id").notNull()
      .references(() => organization.id, { onDelete: "cascade" }),
    siteUrl: text("site_url").notNull(),
    connectedByUserId: text("connected_by_user_id").notNull(),
    gscAccountId: text("gsc_account_id"),
    connectedAccountEmail: text("connected_account_email"),
  },
  (t) => [
    uniqueIndex("gsc_connections_project_idx").on(t.projectId),
    index("gsc_connections_organization_idx").on(t.organizationId),
  ],
);

The GscConnectionRepository.ts module provides CRUD operations for these records, supporting the service layer when users set a property via GscService.setSite (lines 140-188) or disconnect via GscService.disconnect (lines 90-110).

REST Client Implementation

The gscClient.ts file exports a factory function createGscClient that returns an authenticated API client. This client exposes four primary methods:

• getUserInfoEmail(): Retrieves the authenticated user's email address
• listSites(): Fetches all verified Search Console properties
• querySearchAnalytics(siteUrl, body): Queries performance metrics with filtering and aggregation
• inspectUrl(siteUrl, inspectionUrl, languageCode): Performs URL inspection for indexing status

Each method delegates to a low-level request<T>() helper that automatically injects the Bearer token and translates HTTP errors into typed exceptions. Non-2xx responses throw GscApiError (lines 38-58 of src/server/lib/gscClient.ts), while authentication failures raise GscTokenError.

Service Layer Orchestration

GscService.ts (lines 28-188) encapsulates business logic and validation rules, exposing high-level operations that coordinate the client and repository:

Method	Purpose	Implementation Details
userHasGrant	Checks if a user has linked a GSC account	Lines 56-69; used to toggle "Connect" vs "Pick Property" UI states
listSitesForUserWithGrantStatus	Lists verified sites per grant with reconnection status	Lines 95-135; marks grants requiring re-authentication
setSite	Validates and persists project-property mappings	Lines 140-188; verifies site ownership before upserting to gsc_connections
getPerformance	Fetches analytics data	Lines 28-50; builds searchAnalytics requests, handles date ranges and dimensions
inspectUrls	Batch URL inspection	Lines 64-100; calls URL Inspection API for each URL, propagating token failures
disconnect	Removes connections and optionally deletes orphaned grants	Lines 90-110; checks if grant is still referenced by other projects

The service also implements isExpectedGrantFailure (lines 84-93) to distinguish between permission errors and unexpected API failures, enabling appropriate UI messaging.

MCP Tools and Public API Surface

Open SEO exposes Search Console functionality through Model Context Protocol (MCP) tools defined in src/server/mcp/tools/search-console-tools.ts. These tools serve as the public API for both the web interface and autonomous agents:

• get_search_console_performance: Wraps GscService.getPerformance to return formatted markdown tables of search analytics data. Includes validation for self-hosted configuration and handles GscTokenError by prompting reconnection (lines 15-85, handler at 81-99).

• inspect_urls: Wraps GscService.inspectUrls to provide concise per-URL indexing summaries (lines 103-156, handler at 56-78).

Both tools utilize withMcpProjectAuth for authorization and buildProjectMeta for consistent metadata formatting.

Data Synchronization Strategy

Open SEO uses an on-demand synchronization model rather than background data pipelines. The architecture treats GSC data as read-only and fetches fresh metrics directly from Google's API for each request.

This approach eliminates the need for complex sync jobs and large analytic tables in Open SEO's database, though it requires careful handling of Google's rate limits. When the API returns a 429 status code, the GscApiError handler surfaces the restriction to the user. If a token expires or a user revokes access, the GscTokenError bubbles up through the MCP tools, triggering a "reconnect" message with a direct link to restore the OAuth grant.

Implementation Examples

Listing Available GSC Sites

To display verified properties for a user during the connection flow:

import { GscService } from "@/server/features/gsc/services/GscService";

async function showSites(userId: string) {
  const result = await GscService.listSitesForUserWithGrantStatus(userId);
  // `result.accounts` is an array of { accountId, email, requiresReconnect, sites[] }
  console.log(result);
}

Source: GscService.listSitesForUserWithGrantStatus (lines 95-135).

Connecting a Property to a Project

To establish a connection between a project and a verified GSC property:

import { GscService } from "@/server/features/gsc/services/GscService";

await GscService.setSite({
  projectId: "proj_123",
  organizationId: "org_456",
  siteUrl: "https://example.com/",
  accountId: "google-sub-a",          // grant ID the user selected
  userId: "user_abc",
});

Source: GscService.setSite (lines 140-188).

Retrieving Search Performance Data

To fetch click and impression metrics for a specific date range:

import { GscService } from "@/server/features/gsc/services/GscService";

const perf = await GscService.getPerformance({
  projectId: "proj_123",
  startDate: "2024-01-01",
  endDate: "2024-01-31",
  dimensions: ["query"],
  rowLimit: 500,
});
console.log(perf.rows);   // array of { keys?, clicks, impressions, ctr, position }

Source: GscService.getPerformance (lines 28-50).

Inspecting URL Indexing Status

To check the current indexing and enhancement status for specific URLs:

import { GscService } from "@/server/features/gsc/services/GscService";

const inspection = await GscService.inspectUrls({
  projectId: "proj_123",
  urls: [
    "https://example.com/post-1",
    "https://example.com/post-2",
  ],
});
console.log(inspection.results);

Source: GscService.inspectUrls (lines 64-100).

Summary

• Open SEO separates GSC integration into distinct OAuth, client, service, and tooling layers for maintainability and security.
• OAuth tokens store in Better Auth's account table under the google-search-console provider ID, with refresh logic handled automatically in src/server/lib/gscClient.ts.
• Project connections persist in the gsc_connections table with unique constraints preventing duplicate property mappings per project.
• Service methods like GscService.getPerformance and GscService.inspectUrls provide the primary interface for fetching search analytics and indexing data.
• MCP tools expose these capabilities to the UI and agents, handling configuration validation and error formatting.
• On-demand fetching ensures users always see current Search Console data without requiring background synchronization processes.

Frequently Asked Questions

How does Open SEO handle expired or revoked Google Search Console tokens?

When a token expires or a user revokes access, the createGscClient function throws a GscTokenError after getAuth().api.getAccessToken fails. This error bubbles up through the service layer to the MCP tools, which surface a specific "reconnect required" message in the UI with a link to re-authorize the OAuth grant.

What database table stores the Google Search Console project connections?

The gsc_connections table defined in src/db/pg/gsc.schema.ts (with unified schema exports in src/db/schema.ts) stores the mapping between Open SEO projects and verified GSC properties. It includes fields for projectId, organizationId, siteUrl, gscAccountId, and connectedByUserId, with unique indexes preventing multiple connections per project.

Why doesn't Open SEO use background synchronization for Search Console data?

Open SEO implements on-demand data fetching because Google Search Console data is read-only and already aggregated by Google. Fetching fresh data for each request eliminates the complexity of synchronization jobs, reduces database storage requirements, and ensures users always see the latest metrics. The architecture handles rate limiting through GscApiError exceptions when Google API quotas are exceeded.

Can users connect multiple Google accounts to a single Open SEO project?

While each project can only have one active GSC connection (enforced by the unique index on projectId in gsc_connections), users can select which specific Google account grant to use when establishing the connection. The listSitesForUserWithGrantStatus method retrieves verified sites across all available grants for that user, allowing them to choose the appropriate property during the connection setup process.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}