Architecture¶

webanalytics is a single Hono-on-Workers app. Everything is server-rendered JSX — no SPA, no JS bundle (except inline form submissions).

Bindings¶

Binding	Resource	Purpose
DB	D1	Business data: users, sites, link config, analyses, recommendations, comments
SESSIONS	KV	HMAC-signed session ids → user JSON, 14d TTL
TOKENS	KV	Encrypted Google access-token cache + per-user listing caches (GA4 properties, GTM containers, SC sites) + per-(site, range) snapshot caches
OAUTH_STATE	KV	PKCE verifier + post-login redirect, 10 min TTL
AI	Workers AI	LLM inference for analysis + recommendation help

Non-secret vars in wrangler.jsonc: GOOGLE_CLIENT_ID, OAUTH_REDIRECT_URL, PUBLIC_BASE_URL, AI_MODEL. Secrets (wrangler secret put): GOOGLE_CLIENT_SECRET, TOKEN_ENCRYPTION_KEY (AES-GCM, base64 32-byte), SESSION_SIGNING_KEY (HMAC-SHA256, base64 32-byte).

OAuth flow (per-user identity)¶

Browser                Worker                                   Google
  │                       │                                        │
  │  GET /auth/google/    │                                        │
  │      login            │                                        │
  ├──────────────────────►│                                        │
  │                       │  random PKCE verifier + state →        │
  │                       │  KV (OAUTH_STATE, 10 min TTL)          │
  │                       │                                        │
  │  302 → accounts.google.com (code_challenge=SHA256(verifier))   │
  │◄──────────────────────┤                                        │
  │                                                                │
  │  → consent screen (6 scopes), user approves                    │
  │  ◄─── 302 with ?code=...&state=...                             │
  │                       │                                        │
  │  GET /auth/google/    │                                        │
  │      callback         │                                        │
  ├──────────────────────►│                                        │
  │                       │  POP state from KV → verify code_verifier│
  │                       │  POST /token (code + verifier)         │
  │                       ├───────────────────────────────────────►│
  │                       │  ◄─── access_token + refresh_token     │
  │                       │  GET userinfo (sub, email, name, picture)│
  │                       ├───────────────────────────────────────►│
  │                       │                                        │
  │                       │  upsert users row (google_sub PK)      │
  │                       │  encrypt refresh_token (AES-GCM) →     │
  │                       │  oauth_grant row                       │
  │                       │  create session in SESSIONS KV         │
  │                       │  HMAC-sign session id → wa_session cookie│
  │  302 → /sites                                                  │
  │◄──────────────────────┤                                        │

Subsequent API calls hit getAccessToken(env, userId):

1. Read oauth_grant. If access_token_expires_at - 60s > now, decrypt the cached token and return.
2. Otherwise refresh: POST to /token with the decrypted refresh token, store the new encrypted access token + expiry, return it.

D1 schema¶

-- identity + profile
users (id, google_sub UNIQUE, email, name, picture,
       full_name, company, title, phone, profile_completed_at, last_org_id,
       created_at, updated_at)
oauth_grant (id, user_id, scope, refresh_token_enc, access_token_enc, access_token_expires_at)

-- organizations (sites live under an org, not directly under a user)
organization (id, name, owner_user_id, created_at, updated_at)
organization_member (org_id, user_id, role,  -- owner|admin|member
                     joined_at, PRIMARY KEY (org_id, user_id))
organization_invite (id, org_id, invited_by_user_id, email, token UNIQUE,
                     role, expires_at, accepted_at,
                     status,  -- pending|accepted|revoked|expired
                     created_at)

-- sites + their GA4/GTM/SC binding
site (id, user_id, org_id, domain, display_name,
      objectives_json, objectives_dismissed_at,
      created_at, updated_at)                         -- UNIQUE (org_id, domain)
site_link (site_id PK, ga4_property_id, ga4_property_name,
           gtm_account_id, gtm_container_id, gtm_container_name,
           sc_site_url, updated_at)

-- analysis + the to-do workflow
analysis (id, site_id, user_id, created_at, snapshot_json, result_markdown,
          result_json, model)
recommendation (id, analysis_id, site_id, user_id, title, rationale,
                impact, effort, source, steps_json,
                status DEFAULT 'suggested',  -- suggested|skipped|in_progress|done
                created_at, updated_at)
todo_comment (id, recommendation_id, user_id, body, is_ai 0|1, created_at)

-- lead capture (pre-sign-in form; future Pipedrive sync)
lead (id, email, full_name, company, title, phone, source, campaign_code,
      country, user_agent, website, has_website,
      converted_user_id, converted_at,
      pipedrive_person_id, pipedrive_synced_at, created_at)

Migrations live in migrations/ (0001_init.sql … 0008_lead_website.sql). Apply with wrangler d1 migrations apply webanalytics --local|--remote. Notable steps after the initial schema:

• 0003_organizations — adds the profile columns + organization* tables and back-fills one personal org per existing user, moving their sites under it (sites become org-scoped).
• 0004_site_unique_per_org — swaps the site uniqueness from (user_id, domain) to (org_id, domain) by recreating the table (D1 can't drop a constraint in place).
• 0006_repair_site_link — repairs site_link rows wiped by the FK cascade in 0004, and the link-save path is now an idempotent upsert.
• 0005_objectives, 0007_lead, 0008_lead_website — per-site business objectives and the pre-sign-in lead form.

Migration safety note: 0004/0006 are a worked example of D1's constraint-recreation footgun — a DROP TABLE cascades through foreign keys. Always snapshot the remote DB (wrangler d1 export, see the Operations runbook) before applying a table-recreating migration to production.

Snapshot pipeline¶

src/ai/analyze.ts:buildSnapshot() produces a SiteSnapshot for (site, range):

• setup — current state (GTM tag/trigger/variable counts + GA4 conversion events configured). Independent of the date range.
• ga4 (when linked) — totals + previous-period totals + by-channel (with previous deltas) + top pages + top converting pages + new vs returning + device split.
• sc (when linked) — totals + previous-period totals + top queries + top pages + rank opportunities (positions 8–15, ≥50 impressions) + CTR opportunities (high impressions, CTR below 70% of site average).

The snapshot is cached in KV under snap:{siteId}:{start}:{end} with a 10 min TTL so the date-range picker is cheap to play with.

Each listing endpoint (GA4 account summaries, GTM containers, SC sites) is also cached per-user under cache:{kind}:{userId} for 10 min. GTM in particular needs this — the per-minute-per-user quota is 30, and agency accounts blow through it with N+1 list calls; we fall back to a sequential walk that stops gracefully on 429 and returns partial results.

AI: structured analysis¶

runAnalysis() calls Workers AI with response_format: { type: "json_object" } and a system prompt that prescribes the schema:

{
  headline: string,
  winning: { title, detail? }[],
  hurting: { title, detail? }[],
  quick_wins: …,
  cross_source: …,
  tracking_hygiene: …,
  recommendations: {
    title, rationale, impact, effort, source, steps[]
  }[]
}

Both the markdown raw response and the parsed JSON are stored on the analysis row. Each recommendation in the JSON spawns a recommendation row with status='suggested'. The user moves it to skipped or in_progress via the dashboard; the workspace at /sites/:siteId/recs/:recId then hosts the comment thread.

helpWithRecommendation() is a second, separate Workers AI call invoked from the Ask AI for help button. It receives the recommendation, the snapshot, and the full comment thread, and returns a short pragmatic next-step message. The reply is persisted as a todo_comment with is_ai=1.

Routing¶

GET  /                              → marketing home (unauthed) / 302 /sites (authed)
GET  /healthz                       → ok
GET  /auth/google/login             → 302 to accounts.google.com
GET  /auth/google/callback          → token exchange, session, → next
POST /auth/logout

# wizard
GET  /sites/new                     → step 1 (domain)
POST /sites/new                     → create site, → /sites/:id/setup?step=ga4
GET  /sites/:id/setup?step=ga4|gtm|sc → pill picker

# dashboard
GET  /sites                         → site list
GET  /sites/:id                     → live dashboard (range from ?start/&end)
POST /sites/:id/link/{ga4,gtm,sc}   → save link (honors hidden wizard_next)
POST /sites/:id/analyze             → build snapshot, runAnalysis, persist recs, → /sites/:id

# recommendations workspace
GET  /sites/:siteId/recs/:recId         → workspace
POST /sites/:siteId/recs/:recId/skip    → status=skipped
POST /sites/:siteId/recs/:recId/start   → status=in_progress, → workspace
POST /sites/:siteId/recs/:recId/reopen  → status=in_progress
POST /sites/:siteId/recs/:recId/complete→ status=done
POST /sites/:siteId/recs/:recId/comment → user comment
POST /sites/:siteId/recs/:recId/ask-ai  → AI helper, persists AI comment

Design tokens¶

Branding is pulled directly from the Swapps design system at /Users/andres/workspace/SWAPPS/swapps-design-system/project/colors_and_type.css:

• Primary: --ink:#122D3F (navy), --yellow:#F0FF64 (accent)
• Surfaces: --cloud:#F3F5F5, borders --mist:#DAE1E6
• Type: Ofelia Text body, Ofelia Display headings (woff2 served from /fonts/*), JetBrains Mono for tabular values
• Buttons: pill radius 42px, lowercase, ink fill / yellow secondary
• Cards: radius 15px, white surface, soft shadow

All styles are inline <style> in layout.tsx (single CSS string, no bundler step).