Phase 3: Blog SEO — Google Search Console Integration

Status: ✅ IMPLEMENTED
Prerequisites: Phase 1 complete (blog prompt must target search intent first)
Objective: Blog posts are generated around keywords that real users are actively searching for, sourced automatically from Google Search Console for the customer's own site.

Background

The blog generator currently produces SEO-friendly structure, but with no knowledge of what keywords the customer's audience is actually searching for. Google Search Console (GSC) provides this: it shows which queries are driving impressions to the site, their click-through rate, and average position — exactly the signal needed to prioritise which topics to write about.

This phase adds:

  1. GSC OAuth integration (stored in the existing customer_integration table)
  2. A keyword cache table polled by the blog generator
  3. Frontend UI to connect GSC and view/refresh cached keywords
  4. Injection of top keywords into createBlogPost() at generation time

9.1 — Database Migration

File to create: front-end/supabase/migrations/<timestamp>_gsc_keywords_cache.sql

-- Google Search Console keyword cache per customer
CREATE TABLE IF NOT EXISTS gsc_keywords_cache (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customer_customer(id) ON DELETE CASCADE,
  keyword     TEXT    NOT NULL,
  impressions INTEGER NOT NULL DEFAULT 0,
  clicks      INTEGER NOT NULL DEFAULT 0,
  position    NUMERIC(6, 2),
  fetched_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (customer_id, keyword)
);

CREATE INDEX idx_gsc_keywords_customer_impressions
  ON gsc_keywords_cache (customer_id, impressions DESC);

Tasks

  • [ ] Generate migration file with the correct timestamp prefix (run date -u +%Y%m%d%H%M%S to get it)
  • [ ] Apply migration: cd front-end && pnpm supabase db push
  • [ ] Verify the table and index exist in Supabase Studio

9.2 — Google Search Console Integration Adapter

File to create: api/src/integrations/google-search-console.ts

This adapter follows the same pattern as api/src/integrations/twitter.ts and linkedin.ts.
Credentials are stored in customer_integration with integration = 'google_search_console'.
The access_token and refresh_token are stored in metadata.

Key functions

/** Returns the Google OAuth2 consent URL for the webmasters.readonly scope */
export function getAuthorizationUrl(state: string, redirectUri: string): string

/** Exchanges an authorization code for access + refresh tokens */
export async function exchangeCode(
  code: string,
  redirectUri: string
): Promise<{ access_token: string; refresh_token: string; expires_in: number }>

/** Refreshes an expired access token using the stored refresh token */
export async function refreshAccessToken(
  refreshToken: string
): Promise<{ access_token: string; expires_in: number }>

/**
 * Fetches the top keywords from Search Console Data API.
 * siteUrl must be the exact property URL registered in GSC (e.g. "https://example.com/").
 * Returns up to `limit` rows ordered by impressions DESC (last 90 days).
 */
export async function getTopKeywords(
  accessToken: string,
  siteUrl: string,
  limit = 15
): Promise<Array<{ keyword: string; impressions: number; clicks: number; position: number }>>

Security requirements

  • SSRF protection: the siteUrl from customer_integration.metadata must be validated against the same private IP blocklist used in branded-template.service.ts before making any outbound request
  • Token storage: access/refresh tokens stored encrypted via INTEGRATION_ENCRYPTION_KEY (consistent with other integrations)
  • Scopes: request only https://www.googleapis.com/auth/webmasters.readonly — never write access

Environment variables required

VariableDescription
GOOGLE_CLIENT_IDOAuth2 client ID from Google Cloud Console
GOOGLE_CLIENT_SECRETOAuth2 client secret

Both are already used by the Blogger integration and are present in api/src/config/env.ts. No new variables are needed.

Google Cloud Console setup (manual, one-time)

  1. Open the project used for Gemini (GOOGLE_API_KEY owner)
  2. Enable the Google Search Console API
  3. In OAuth 2.0 Client ID (type: Web application) add the callback URI: {API_BASE_URL}/integrations/google-search-console/callback
  4. Ensure GOOGLE_CLIENT_ID and GOOGLE_CLIENT_SECRET are set in .env / Doppler (they should already be set for Blogger)

Tasks

  • [x] Created api/src/integrations/google-search-console.ts
  • [x] Uses existing GOOGLE_CLIENT_ID and GOOGLE_CLIENT_SECRET from api/src/config/env.ts
  • [x] GSC keyword cache service in api/src/services/gsc-keywords.service.ts
  • [x] OAuth routes in api/src/routes/integrations.ts
  • [x] Vue integration component in front-end/src/components/pages/integrations/GoogleSearchConsoleIntegration.vue

9.3 — GSC Keywords Service

File to create: api/src/services/gsc-keywords.service.ts

import { createSupabaseClient } from '../utils/supabase.js'
import { getTopKeywords, refreshAccessToken } from '../integrations/google-search-console.js'
import { decrypt } from '../utils/encryption.js'

/**
 * Fetches fresh keywords from GSC for the customer and upserts them into the cache.
 * Throws if the customer has no GSC integration connected.
 */
export async function fetchAndCacheKeywords(customerId: number): Promise<number>

/**
 * Returns the cached keyword strings for a customer, ordered by impressions DESC.
 * Returns an empty array if no GSC integration exists or cache is empty.
 * Never throws — callers should treat empty array as "no keywords available".
 */
export async function getCachedKeywords(customerId: number): Promise<string[]>

fetchAndCacheKeywords flow:

  1. Load customer_integration where customer_id = X AND integration = 'google_search_console'
  2. Decrypt tokens from metadata
  3. Check token expiry — call refreshAccessToken() if needed, persist new token
  4. Get siteUrl from metadata.site_url
  5. Call getTopKeywords(accessToken, siteUrl)
  6. Upsert results to gsc_keywords_cache (on conflict: update impressions, clicks, position, fetched_at)
  7. Return count of upserted rows

getCachedKeywords flow:

  1. Query gsc_keywords_cache where customer_id = X ORDER BY impressions DESC LIMIT 15
  2. Return keyword strings only

Tasks

  • [ ] Create api/src/services/gsc-keywords.service.ts
  • [ ] Implement fetchAndCacheKeywords()
  • [ ] Implement getCachedKeywords()

9.4 — API Routes

Target file: api/src/routes/integrations.ts

Add three endpoints following the existing LinkedIn/Twitter OAuth pattern in the same file.

GET /integrations/google-search-console/auth

Returns the OAuth2 authorization URL. The frontend navigates the user to this URL.

Query params:
  customer_id: number (required)

Response:
  { url: string }

State parameter encodes { customerId, userId } as a signed/encrypted token (or base64 for simplicity, matching how LinkedIn state is handled in the existing code).

GET /integrations/google-search-console/callback

Handles the OAuth redirect from Google. Exchanges the code for tokens and stores them.

Query params:
  code: string
  state: string (decoded to get customer_id)

On success: redirect to {FRONTEND_URL}/app/client/{customer_id}/integrations?tab=blog&gsc=connected
On error:   redirect to {FRONTEND_URL}/app/client/{customer_id}/integrations?tab=blog&gsc=error

Upserts to customer_integration:

{
  "customer_id": X,
  "integration": "google_search_console",
  "access_token": "<encrypted>",
  "metadata": {
    "refresh_token": "<encrypted>",
    "site_url": "<discovered or set to customer website URL>",
    "expires_at": "<ISO-8601 UTC string, e.g. 2026-05-05T14:30:00.000Z>"
  }
}

Note on site_url: GSC properties are registered per site. On first connect, attempt to discover the first verified property by calling GET https://www.googleapis.com/webmasters/v3/sites with the fresh access token. If multiple are found, store all and use the one matching customer_customer.url. If none match, store all and let the user select via the frontend later.

POST /client/:id/integrations/google-search-console/refresh

Manually triggers a keyword cache refresh.

Params:  { id: string }  (customer_id)
Auth:    user must own the customer record (same check as /plan_post_schedule)

Response:
  { refreshed: true, keyword_count: number }

Tasks

  • [ ] Add GET /integrations/google-search-console/auth to integrations.ts
  • [ ] Add GET /integrations/google-search-console/callback to integrations.ts
  • [ ] Add POST /client/:id/integrations/google-search-console/refresh to integrations.ts

9.5 — Blog Generator: Keyword Injection

Target files: api/src/agents/createPosts.ts, api/src/modules/posts.ts

createBlogPost() — keyword-aware prompt

Add seoKeywords?: string[] to PostContent. When present, inject at the top of the blog prompt:

SEO target keywords (use these naturally; H1 must contain the primary keyword):
Primary: {keywords[0]}
Related: {keywords.slice(1).join(', ')}

Modify the H1 and H2 instructions to reference the primary keyword explicitly:

- H1: must contain or closely match the primary keyword (the search query the post targets)
- H2s: phrase as questions a searcher would ask that relate to the primary keyword
- <meta name="description">: include the primary keyword within the first 60 characters

Graceful fallback: when seoKeywords is undefined or empty, use the existing topic-based H1/H2 instructions from Phase 7 unchanged.

getPlatformContent() — keyword fetch

Before the retry loop, for blog platforms:

const BLOG_PLATFORMS = new Set(['blog', 'ghost', 'wordpress', 'blogger'])

let seoKeywords: string[] | undefined

if (BLOG_PLATFORMS.has(platform)) {
  try {
    const keywords = await getCachedKeywords(customer.id)
    if (keywords.length > 0) seoKeywords = keywords
  } catch (err) {
    console.warn('getPlatformContent: failed to fetch GSC keywords', {
      customerId: customer.id,
      err,
    })
  }
}

// Pass to generator:
const result = await contentMap[platform]({
  campaign,
  customer,
  prompt,
  brandVoice,
  toneExamples,
  seoKeywords,
})

Tasks

  • [ ] Add seoKeywords?: string[] to PostContent type / createBlogPost() parameter
  • [ ] Update createBlogPost() prompt to use keywords when available
  • [ ] Import getCachedKeywords in posts.ts
  • [ ] Add keyword fetch block in getPlatformContent() for blog platforms

9.6 — Frontend: GSC Integration Card

Target file: front-end/src/pages/app/client/[id]/integrations.vue

Add a Google Search Console card to the Blog tab, alongside the existing Ghost/WordPress/Blogger integration cards.

Card states

Disconnected state:

[Google Search Console icon]
Google Search Console
Connect to automatically fetch the keywords your audience is searching for.
These keywords will be used to generate SEO-targeted blog posts.

[Connect Google Search Console]  ← button → opens OAuth flow

Connected state:

[Google Search Console icon]  ✓ Connected
Site: https://example.com/
Last synced: {relative date}

Top keywords (by impressions):
  [keyword 1]  1,240 impressions · pos 4.2
  [keyword 2]    892 impressions · pos 7.8
  …

[Refresh Keywords]  ← POST /client/:id/integrations/google-search-console/refresh
[Disconnect]        ← delete the customer_integration row

OAuth flow

  1. User clicks "Connect Google Search Console"
  2. Frontend calls GET /integrations/google-search-console/auth?customer_id={id}
  3. Receives { url } — navigates to it (window.location.href = url)
  4. Google redirects back to callback → API redirects to /app/client/{id}/integrations?tab=blog&gsc=connected
  5. On mount, check for ?gsc=connected query param and show a success toast

Data fetching

Load integration status and cached keywords via a single API call pattern consistent with other integration tabs:

  • Check customer_integration where integration = 'google_search_console' (exposed via GET /client/:id/integrations)
  • Load keywords: GET /client/:id/integrations/google-search-console/keywords (or read from gsc_keywords_cache via a new simple endpoint)

Add GET /client/:id/integrations/google-search-console/keywords to integrations.ts — returns gsc_keywords_cache rows for the customer ordered by impressions DESC.

Tasks

  • [ ] Add GET /client/:id/integrations/google-search-console/keywords endpoint to integrations.ts
  • [ ] Add GSC card component to the Blog tab in integrations.vue
  • [ ] Implement disconnected state with OAuth connect button
  • [ ] Implement connected state with keyword list and Refresh button
  • [ ] Handle ?gsc=connected / ?gsc=error query params on mount with toast notifications
  • [ ] Visual audit: start dev server and take a screenshot of the Blog tab with GSC connected and disconnected states

Testing Checklist

Unit / integration tests

  • [ ] getCachedKeywords() returns empty array when no integration exists (not an error)
  • [ ] fetchAndCacheKeywords() throws a meaningful error when no integration row found
  • [ ] Blog post with keywords: H1 contains the primary keyword
  • [ ] Blog post with keywords: <meta name="description"> contains primary keyword
  • [ ] Blog post without keywords (no GSC connected): generation succeeds normally

End-to-end

  • [ ] Connect GSC via OAuth in the UI for a test customer
  • [ ] Verify customer_integration row created with integration = 'google_search_console'
  • [ ] Click "Refresh Keywords" — verify gsc_keywords_cache rows appear
  • [ ] Generate a blog post — verify the customer_platform_post.content contains a keyword from the cache in the H1
  • [ ] Disconnect GSC — verify keywords are cleared from the cache table and card shows disconnected state
  • [ ] Run type check: cd api && pnpm build — no TypeScript errors
  • [ ] Run route tests: cd api && pnpm test:routes

Rollback

  1. Remove the seoKeywords fetch from getPlatformContent() in posts.ts
  2. Remove the keyword param from createBlogPost() (revert to Phase 7 prompt)
  3. Drop gsc_keywords_cache table (migration down script)
  4. Remove GSC OAuth routes from integrations.ts
  5. Remove GSC card from integrations.vue
  6. Remove GOOGLE_GSC_CLIENT_ID / GOOGLE_GSC_CLIENT_SECRET from env config