# Crosswalk

GA4 and Search Console for the SEO team, asked in plain language, with nothing
to install but one file.

Named for the crosswalk table: the join between a GA4 property, a Search
Console site and a client name, which is the spreadsheet this replaces.

A working prototype. The research and design that led here are in
`research/`, the running order for the stakeholder meeting is `docs/DEMO.md`,
and the connector's user guide is `docs/MCP.md`.

- `research/current-state.md`: what the process is today and why it does not scale
- `research/api-constraints.md`: the Google API facts that decide the design
- `research/auth-decision.md`: how the server authenticates to Google, and why it is not blocked
- `research/client-registry.md`: the thing that replaces the spreadsheet
- `docs/DEMO.md`: the running order for the meeting, with the pre-meeting checklist
- `docs/MCP.md`: installing and using the connector
- `docs/HOSTING.md`: what it takes to host it, on Render or on Scorpion's own infrastructure, and how the Google accounts are held
- `docs/PROTOTYPE-BRIEF.md`: the brief the prototype was built to

## The diagnosis in one paragraph

The current setup is painful in two very different ways, and only one of them
is what people complain about. The visible pain is JSON key files, pipx, and
hand-editing `claude_desktop_config.json`: genuinely awful, but one-time and
finite. The pain that actually does not scale is invisible in the guide,
compressed into the sentence *"Repeat for each client GSC property"*. Because
every team member creates their own service account, and because Search Console
has no permission API and no account tier, granting access is
**properties multiplied by people** of manual clicking, forever. At thousands
of properties that is the whole problem. Everything else is a nuisance.

## The fix

Stop giving each person their own Google credential. Hold the Google
credentials **once, server-side**, and give each person a revocable token to
the server instead.

```
SEO in Claude Desktop
  |   one bundle, double-clicked. Paste a URL and a token
  v
Crosswalk service          <- holds the 20-30 Google credentials
  |                            auto-discovers every property
  |                            joins GA4 <-> GSC <-> client
  |                            per-person tokens and audit trail
  v
GA4 Data + Admin API   |   Search Console API
```

The per-property work becomes O(properties), done once, by one person. A new
hire costs zero grants. A new client costs one onboarding step, not one per
team member.

This is the CWV Arena pattern applied to a different data source. CWV Arena
already proved every moving part: Workspace sign-in, hashed personal access
tokens with instant revocation, an MCPB bundle served from the app's own
settings page, and a connector that a non-technical person installs by
double-clicking. This prototype ports them rather than reinventing them.

## What is built

```
server/   Fastify API and Postgres: registry, metrics, sign-in, tokens, discovery
shared/   the rules both sides use: periods, join, formatting, diagnosis
web/      the app: sign-in, clients, exceptions, discovery, settings
mcp/      the connector, nine read-only tools, and the bundle build
scripts/  the seed, the one-account Google connect, the dash check
```

**The registry.** GA4 properties and Search Console sites are stored as the
APIs return them, and a client is the join between one of each. The join is
made on the domain and carries a confidence. It is a guess until a person
confirms it, and every screen and every tool says so. A client can be either
half alone: a property with no site, or a site with no property, named after
its domain. When the other half turns up later it attaches to the same
client.

**Vertical** is a stored field seeded from the credential that discovered the
client, correct about nine times in ten. Discovery fills it only when unset,
so a person's correction survives every later run.

**Periods.** Every comparison is two complete calendar months, chosen under
the Search Console lag so both sources describe the same months. The current
partial month is never used, and every answer names the month it excluded.

**The connector** returns compact text, quotes figures through the same
formatters the app uses, caps every list and says when it cut one, and calls
movement inside a five percent band flat. `get_portfolio` answers "which of
my clients lost organic traffic last month" in one call; the per-client tools
tell a Search Console story from a GA4 story.

**Vertical scoping** is a default view, not a wall. Tools answer for the
verticals set on a person's Settings page; asking for "all" or naming a
client elsewhere works and is written to the audit log against that person.

**Discovery** runs against one consented Google account at a time. A
credential that fails to answer marks nothing missing; only a successful
listing does, and it marks rather than deletes. **Ingestion** then pulls
Search Console and GA4 figures for everything discovery found, complete
months only, into the same tables the seed fills, so every tool answers for
a real client the same way it answers for a seeded one.

Not built, on purpose: the consent flow for all twenty-five accounts, enforced
access control, any write tool, scheduling, alerting, a polished exception
queue, or a third data source.

## Run it

Postgres, then the app. The database port is 55433 so this runs beside CWV
Arena on the same machine.

```bash
docker run -d --name crosswalk-pg -e POSTGRES_USER=crosswalk -e POSTGRES_PASSWORD=devpass \
  -e POSTGRES_DB=crosswalk_dev -p 127.0.0.1:55433:5432 -v crosswalk-pgdata:/var/lib/postgresql/data \
  postgres:17-alpine
# or, with the compose plugin: docker compose up -d

cp .env.example .env          # DATABASE_URL is the only required line
npm install
npm run seed                  # about 400 clients, 18 months, three planted stories
npm run dev                   # API on 5277, app on 5273
```

Sign-in is off until `GOOGLE_OAUTH_CLIENT_ID`, `GOOGLE_OAUTH_CLIENT_SECRET`
and `SESSION_SECRET` are set; without them every request is anonymous and
tokens cannot be created, because there is nobody to attribute one to. The
exact redirect URI to register on the Google client is logged at boot.

To run discovery and pull the reports from a shell or a scheduler rather than
the Discovery page:

```bash
npm run discover
npm run ingest      # fourteen complete months per site and property
```

For the demo, run the built app as one process so the address in the browser
matches the address in the bundle:

```bash
npm run build && npm run mcp:bundle && npm start   # everything on http://127.0.0.1:5277
```

## Green gates

```bash
npm test              # 133 tests: periods, join, format, diagnosis, seed, portfolio, discovery, connector
npm run typecheck     # server, shared, web, mcp and scripts
npm run build         # typecheck plus the production app
npm run mcp:bundle    # the installable connector
npm run check:dashes  # no em or en dash in any tracked file
```

## The connector without Claude Desktop

```bash
npx @modelcontextprotocol/inspector --cli npx tsx mcp/src/index.ts \
  -e CROSSWALK_URL=http://127.0.0.1:5277 -e CROSSWALK_TOKEN=xw_... \
  --method tools/call --tool-name get_portfolio --tool-arg only=declines
```

## Discovery against a real account

Needs a Google Cloud project with the Google Analytics Admin API, the Google
Analytics Data API and the Search Console API enabled, an OAuth consent screen with the
`analytics.readonly` and `webmasters.readonly` scopes and the consenting
account listed as a test user, and a Desktop-app OAuth client. Put the
client's id and secret in `.env` as `GOOGLE_DATA_CLIENT_ID` and
`GOOGLE_DATA_CLIENT_SECRET`, generate `CREDENTIAL_KEY` with
`openssl rand -hex 32`, then:

```bash
npm run google:connect -- --vertical Law
```

Consent once with the property-owning account. Then open Discovery in the
app and run it. Testing-mode refresh tokens expire after seven days, which is
fine for a demo and is why the real deployment publishes the app; see
`research/auth-decision.md`.

This OAuth client is for the property-owning accounts only. The SEO team
signs in to the app with Workspace and a personal token, which is a separate
client and uncapped.

## Before any of this: the shared account

The setup guide circulates the password of a shared consumer Gmail account in
plain text, with recovery pointed at one person's personal mobile and
two-factor codes held by another named employee. That should be fixed on its
own timetable: rotate the password now, replace the account with a Workspace
identity, and move two-factor off an individual's phone. Rotating the password
will not break this integration, because Google only invalidates these tokens
on a password change when Gmail scopes were granted, and none are.

## Still open

- **Is there a system of record for a client's vertical?** A CRM or billing
  system that authoritatively knows would beat inferring from a credential.
- **What is the real automatic match rate?** The seed lands at 97 percent by
  construction. The real number is unknown until discovery runs against the
  real accounts, and it decides how much confirmation UI is worth building.
- **Where does it run?** CWV Arena is on Render for reasons in its
  `docs/PLATFORM.md` that apply here. The Dockerfile pattern ports directly.
- **Sharing a client registry with CWV Arena later.** Deferred, not dismissed.