Grid Map Worker Port — Plan (completed 2026-04-19)

Full-parity port of build_lrc_grid_map.py (Python, 1,489 lines) into a Worker endpoint so the Roam advisor can produce the same interactive grid map as the /prospect skill.

Status: All five phases landed 2026-04-19. Roam advisor tool publish_grid_map is live on both workers; end-to-end validation confirmed (1 tool call, URL returned, meta surfaced correctly). This doc is retained as reference for debugging / future iteration — not as a live plan.

Final commits:

• aed2062 — Phase 1 (shell HTML + BQ fetch)
• c8a63db — Phase 2 (tabbed sidebar + stats panels)
• f9f5de3 — Phase 3 (overlays + draw-a-circle + grid-area intro)
• 993f501 — Phase 4 (Competitor tab + InSites iframe drill-down)
• Phase 5 — publish_grid_map tool + SYSTEM_ADDENDUM (this commit)

---

Decisions already locked

Decision	Choice	Why
Data source	BQ (not S3)	dashboard-area-map handler already proves this works; keeps Worker BQ-native; no S3 sync
Fidelity	Full parity	Three people iterate on the Python skill; sales uses Roam. Parallel copies must match visually
Invocation	Explicit tool publish_grid_map	Don’t eagerly fire CSO/FB fetches; AM asks when they want a map
Multi-grid	Yes, with gate	Advisor surfaces cost/latency before building 2+ grids
fcr_link security	Stripped at build time	Map HTML is prospect-shareable; surface FCR intel in chat only

---

Current state (end of Phase 2)

Committed + deployed

• worker/src/handlers/grid-map-build.js (888 lines) — handler + shell HTML + BQ fetch + tabbed sidebar + collapsible stats blocks
• Registered in worker/src/index.js with NO_CACHE_ROUTES entry
• Commits: aed2062 (Phase 1), c8a63db (Phase 2)
• Deployed to both Worker accounts (cathaldempsey + fcrmedia)

Phase 2 validated

Run against proposals/_competitor_tab_smoke/grids.json:

• sa_count: 89, commercial_count: 852, fb_grids: 1, growth_grids: 1 — exact match with Python builder
• All AREA_STATS numeric values identical (only JSON cosmetic differences: pp_avg: 35 vs 35.0, radius_km: 10 vs "10" — both render identically)
• bytes: 28005 (up from 13547 in Phase 1), elapsed_ms: ~700 (baseline preserved)
• phase: 2, has_stats_panels: true, has_overlays: false, has_competitor_tab: false

What Phase 2 produces (on top of Phase 1)

• Three-tab sidebar (Rankings / Demographics / Competitors placeholder)
• Rankings tab retains Phase 1 stat-cards + position-key legend + per-point detail
• Demographics tab renders 9 expandable <details> blocks per grid
• Competitors tab has empty placeholder text (populated in Phase 4)
• setTab() switcher with Leaflet invalidateSize on rankings-tab activation
• AREA_STATS const embedded server-side (no client-side fetch)

Phase 2 fix worth noting

fetchGrowthData was using area_type='lea' per the plan’s original gotcha #5 — that’s wrong. Corrected to area_type='eircode' which is town-name-keyed with full 2023-2025 coverage. Portlaoise totals jumped from 817 → 2144, matching Python’s data/map/growth_data.json exactly. Gotcha #5 in this doc has been updated — don’t trust the pre-Phase-2 wording if you see it in git history.

---

BQ data reference (ground truth from schema inspection)

Keep this handy — I wasted 15 minutes in Phase 1 guessing column names. Always verify before writing SQL.

fcr_operations.cso_small_areas_v2

Primary key: sa_pub. Key columns:

• sa_pub, sa_geogid, ed, county, urban_area_name
• total_households, total_population, houses, flats
• centroid_lat, centroid_lng
• pct_professional, pct_degree_plus, pct_homeowner, pct_multicar, pct_employed
• pp_score, pobal_index, pobal_category, pobal_label

fcr_operations.cso_demographics

Primary key: sa_pub (joins cleanly with cso_small_areas_v2).

• age_0_4 through age_65_plus (7 buckets)
• families_total, families_with_children
• own_mortgage, own_outright, rent_private, rent_la, vacant
• heat_oil, heat_gas, heat_electric, heat_solid_fuel
• cars_none, cars_one, cars_two_plus
• commute_wfh, commute_car, commute_public
• employed, unemployed, retired, students

fcr_operations.valuation_properties

• property_number, category, uses, county, local_authority, address, eircode
• centroid_lat, centroid_lng (NOT lat/lng)

fcr_operations.cso_new_builds

Long-format. No town area_type exists.

• area_type ∈ {county, eircode, lea}
• metric ∈ {completions, commencements}
• year, value, area_name, county, eircode
• Use area_type='eircode' AND metric='completions' for town-proxy matching. Despite the name, eircode-typed rows are keyed by town name in area_name (139 towns, full 2023-2025 coverage) — matches the Python builder’s data/map/growth_data.json exactly. lea was my first guess in Phase 1 but LEA rows are missing 2025 data (332 rows, 2023-2024 only), so totals shrink.
• Pivot years with SUM(CASE WHEN year=YYYY THEN value END) AS y_YYYY

fcr_operations.facebook_reach_estimates

Already working in Phase 1. Query nearest non-zero row within ±0.15 lat / ±0.20 lng of grid centre; order by ST_DISTANCE ASC LIMIT 1.

---

File structure & patterns

Single file, not two

I almost split into grid-map-template.js + grid-map-build.js. Decided against: template fragments and assembly are tightly coupled, splitting adds import overhead, easier to diff against Python source when monolithic.

Template literal escaping

JS embeds most of the Python HTML/CSS/JS verbatim. One gotcha: when embedding Python’s JS blocks (which use ${...} interpolation) inside a JS backtick template literal, every ${ must be escaped as \${ to prevent the outer template literal from interpolating. Use the $ → \$ rule mechanically when porting.

Alternative if it becomes painful: use plain single-quote strings with \n line joins for the static JS blobs. Less readable but no escape gymnastics.

BQ query pattern

Always use parameterised queries via queryBQ(env, sql, {paramName: {type, value}}). queryBQ returns the rows array directly, not {rows: [...]}.

Safe value types: STRING, INT64, FLOAT64, BOOL, DATE, TIMESTAMP. Cast booleans via CAST(x AS STRING) and compare === "true" client-side — avoids JSON encoding edge cases.

No-cache registration

Every new Worker endpoint that publishes to KV or emits unique-per-request data must go in NO_CACHE_ROUTES in index.js:76-107. The router-level cache is on by default.

---

Phase 2 — Tabbed sidebar + stats panels ✅ DONE (commit c8a63db)

Actual delta: +215 lines (plan estimated ~400 — turned out more compact). Kept below as reference for diffing against Python source or debugging Phase 2 regressions.

Goal

Add three-tab sidebar (Rankings / Demographics / Competitors — Competitors stays empty until Phase 4), per-grid expandable stats blocks (Population, Age, Households, Families, Heating, Cars, Commercial, Demographics, New builds, FB audience).

Python source references

Fragment	Lines
EXTRA_CSS	811-866
SIDEBAR_STRUCTURE	772-808
renderStatsBlocks + renderAreaStats + setTab	955-1087
RENDER_REPLACEMENT	1284-1289

Deliverables

1. Inject EXTRA_CSS into the </style> block of the shell HTML. String replace: </style> → EXTRA_CSS + </style>.

2. Replace the sidebar <h2 id="sidebar-title"> block with SIDEBAR_STRUCTURE. This gives us three <section class="tab-pane"> panes and the tab strip.

3. Inject const AREA_STATS = {...} right after const GRIDS_DATA = ...; in the script block. This is the per-grid stats map from summariseGrid() (already computed in Phase 1, just not embedded).

4. Append renderStatsBlocks + renderAreaStats + setTab JS to the end of the <script> block, before the final render(); call.

5. Replace render()’s last 3 lines (sidebar-stats.innerHTML... through sidebar-detail.innerHTML = '') with RENDER_REPLACEMENT — same stat-card injection plus renderAreaStats(); call.

6. Call setTab('rankings'); at the end of init so the first tab shows by default.

Validation

Open smoke URL, confirm:

• Three tabs visible at top of sidebar
• Rankings tab shows stat-cards + legend (unchanged from Phase 1)
• Demographics tab shows 9 expandable <details> blocks per grid with Population / Age / Households / Families / Heating / Cars / Commercial / Demographics summary / New builds / FB audience
• All numbers match Python builder output against same fixture
• Competitors tab shows empty placeholder

---

Phase 3 — Overlays + draw-a-circle (~350 lines)

Goal

4 map overlay toggles (None / PP / Population / Households / Commercial), legend per overlay, draw-a-circle with radius picker (2/4/6km) that aggregates CSO stats inside the drawn circle.

Python source references

Fragment	Lines
TOOLBAR_OVERLAYS	755-770
Overlay JS (ppColor, popRadius, hhRadius, COM_COLORS, setOverlay)	868-953
Draw-a-circle (toggleDrawCircle, clearDrawing, haversineKm, aggregateInCircle, map.on('click'))	1207-1281

Deliverables

1. Inject TOOLBAR_OVERLAYS after <select id="kw-select"> — 5 overlay buttons + draw-a-circle controls. String replace.

2. Inject const SA_DATA = [...] and const COM_DATA = [...] after GRIDS_DATA (both already fetched in Phase 1, need embedding now).

3. Append overlay JS — ppColor, popRadius, hhRadius, COM_COLORS, setOverlay(kind) with legend rendering per overlay type.

4. Append draw-a-circle JS — drawnCircle, drawCircleMode, drawRadiusKm, setDrawRadius, toggleDrawCircle, clearDrawing, haversineKm, aggregateInCircle, map.on('click') handler.

5. Call setOverlay('none'); in init sequence (no overlay by default).

Validation

• Overlay buttons toggle circle markers on the map
• PP overlay: green/amber/red circles per small area
• Pop/HH overlays: circle-radius scales with count
• Commercial overlay: coloured dots by category
• Draw-a-circle: click sets circle, sidebar Demographics tab shows aggregate stats inside circle
• Legend in sidebar updates per overlay

---

Phase 4 — Competitor tab + InSites embed (~300 lines)

Goal

Competitor tab consumes competitors_enrichment (already stripped of fcr_link) and renders the same cards as the Python builder. Home-grid InSites backfill runs server-side. Click-pin on InSites aggregate grid surfaces a View true per-point grid on InSites → button that opens a modal iframe.

Python source references

Fragment	Lines
backfill_insites_top10	640-720
renderCompetitors	1077-1204
SHOW_DETAIL_REPLACEMENT (per-point detail rewrite)	1291-1330
openInsitesEmbed, closeInsitesEmbed	1332-1353

Deliverables

1. Server-side InSites backfill — for each (grid_id, report_id) in wrapper.insites_reports, call dashboard-insites-poll internally (existing handler). Merge business_data[keyword] into each grid point’s top10 with top10_scope = "grid_aggregate". Preserve website/phone/type/category fields.

2. Embed const INSITES_REPORT_IDS and const INSITES_EMBED_KEY alongside GRIDS_DATA. Read env.INSITES_PUBLIC_KEY secret — if absent, embed empty string and iframe button will be hidden client-side.

3. Embed const COMPETITORS_ENRICHMENT (with fcr_link already stripped via stripFcrLink()).

4. Replace showDetail() with SHOW_DETAIL_REPLACEMENT — per-point detail now includes scope note, InSites embed button for aggregate scope, and setTab('rankings') + scrollIntoView calls.

5. Append renderCompetitors() — consumes COMPETITORS_ENRICHMENT, renders category/website/phone/photos/response-rate/services per card. No FCR pill (fcr_link is stripped).

6. Append openInsitesEmbed + closeInsitesEmbed — modal with iframe pointing to app.insites.com/grid-embed?key=...&reportId=...&keyword=....

7. Call renderCompetitors(); in init sequence.

Validation against _competitor_tab_smoke/grids.json

Same fixture as Python builder’s test. Expected cards:

• Alpha Drains: Services chips, 📷 42, Response 74%, Hours set
• Beta Plumbing: “No services listed on GBP”
• Delta Cleaners: 2 service chips, 📷 3
• Gamma Services: Tier 1 only (no enrichment)
• No FCR pill anywhere (fcr_link stripped)

---

Phase 5 — Advisor tool + SYSTEM_ADDENDUM (~80 lines)

Goal

Roam advisor can trigger a grid map build when the AM explicitly asks.

Deliverables

1. New tool publish_grid_map in ai-advisor.js tools array:

{
  name: "publish_grid_map",
  description: "Build and publish the full interactive grid map (rank pins, CSO overlays, stats panels, Competitor tab) to /proposal/{slug}. Returns a Cloudflare URL (30-day TTL). Call ONLY when the AM explicitly asks to publish or share the map — this is a ~8-15s build that fetches CSO + commercial + FB data from BQ. Do NOT auto-fire after run_serp_grid; wait for explicit 'publish the map', 'give me the map link', 'share this'.",
  input_schema: {
    business_name: string required,
    grids: array required (same shape as grids.json wrapper),
    insites_reports: object optional,
    competitors_enrichment: object optional (pass through from enrich_competitors if called)
  }
}

2. execTool case — POSTs to dashboard-grid-map-build, returns {url, slug, meta} with _instruction telling Claude to present the URL as a markdown link and mention “expires 30 days, internal-only”.

3. SYSTEM_ADDENDUM block — new section, 25-30 lines:

   • WHEN TO CALL: explicit AM ask only (“publish”, “share the map”, “give me the link”)
   • DO NOT CALL: auto-fire after run_serp_grid, for rank spot-checks, as part of meeting-brief workflow (brief has its own save)
   • MULTI-GRID GATE: if >1 grid, confirm cost (“3 grids = ~25s, proceed?”) unless AM’s phrasing is explicit
   • IF enrich_competitors was called this session, pass its enrichments as competitors_enrichment
   • IF submit_insites_lrc was called, pass {grid_id: report_id} as insites_reports for home-grid backfill + iframe drill-down

Validation

• Ask in Roam: “publish the grid map for Laois Jetwash” → advisor calls publish_grid_map, returns URL
• Ask vague: “where does X rank?” → advisor does NOT call publish_grid_map
• Multi-grid: “publish maps for all 3 grids” → advisor gates cost before firing

---

Testing strategy

Side-by-side diff

Same fixture (proposals/_competitor_tab_smoke/grids.json) rendered by both:

1. Python: python build_lrc_grid_map.py --from-json <fixture> <out.html>
2. Worker: POST /dashboard-grid-map-build with fixture body, fetch /proposal/{slug}

After each phase, compare rendered HTML visually. Byte-equivalent not required; functional equivalent is. Key assertions per phase captured above.

Regression safety

Phase 1 smoke-test baseline (elapsed_ms: 690, sa_count: 89, commercial_count: 852) should stay roughly constant through Phase 4 — if it balloons to multiple seconds, a BQ query got unbounded.

---

Known gotchas (learned in prior phases)

1. queryBQ returns the rows array directly, not {rows: []}. Bit me in competitor-enrich.
2. BQ reserved keywords: rows, limit, order etc. — always alias as n, count_n, etc.
3. UTF-8 mojibake from SerpAPI — en-dash (–) comes through as â€". fixMojibake() helper in competitor-enrich.js handles it.
4. Template-literal ${ escape — inside backtick strings embedded in JS source, escape every ${ as \${ or the outer template will try to interpolate.
5. cso_new_builds has no town area_type — use area_type='eircode' (town-keyed, full 2023-2025). lea is LEA-granularity but missing 2025 data.
6. No pct_third_level — it’s pct_degree_plus.
7. Join key across cso_small_areas_v2 ↔ cso_demographics is sa_pub (not small_area_id).
8. valuation_properties uses centroid_lat/lng (not lat/lng).
9. Router NO_CACHE_ROUTES matters — every new KV-publishing or live-API endpoint must be listed or it’ll get cached by the router layer.
10. fcr_link MUST be stripped before the HTML leaves the Worker — stripFcrLink() helper is in grid-map-build.js.

---

Environment

Worker deployments

• Dev: https://fcr-dashboard-api.cathaldempsey.workers.dev
• Prod: https://fcr-dashboard-api.fcrmedia.workers.dev

Deploy both via /deploy-worker skill or manually:

cd worker
CLOUDFLARE_API_TOKEN=$(grep CF_API_TOKEN_FCR ../.env.local | cut -d= -f2) npx wrangler deploy --env fcr
CLOUDFLARE_API_TOKEN=$(grep CF_API_TOKEN_CD ../.env.local | cut -d= -f2) npx wrangler deploy

Secrets already set on both workers

• API_KEY — x-api-key for frontend auth
• BQ_SERVICE_ACCOUNT_JSON — Google service account
• SERPAPI_KEY — SerpAPI google_maps
• PLEPER_API_KEY, PLEPER_API_SIG — Pleper scraping
• META_ACCESS_TOKEN, META_AD_ACCOUNT_ID — Meta reach estimates
• ANTHROPIC_API_KEY — advisor tool loop
• INSITES_PUBLIC_KEY — check availability before Phase 4. If absent, iframe drill-down is disabled gracefully (same as Python builder’s behaviour).

Local env

.env.local at repo root contains:

• VITE_N8N_API_KEY — used for smoke tests
• CF_API_TOKEN_FCR + CF_API_TOKEN_CD — for deploys
• INSITES_PUBLIC_KEY — optional, for Python builder iframe mode

---

Session kickoff checklist

When picking up next session:

1. Read this file end-to-end. ~5 min.
2. git log --oneline -10 — confirm c8a63db (Phase 2) is HEAD or ahead. If not, merge conflict to resolve.
3. Verify Phase 2 still lives: curl -X POST .../dashboard-grid-map-build -d @proposals/_competitor_tab_smoke/grids.json → HTTP 200, meta.phase: 2, meta.has_stats_panels: true, meta.sa_count: 89. If broken, fix before adding.
4. Fetch the published URL and confirm the HTML contains data-tab="rankings", data-tab="demographics", data-tab="competitors", renderStatsBlocks, AREA_STATS = {. If anything is missing the last deploy didn’t land — redeploy before starting Phase 3.
5. Start Phase 3 (overlays + draw-a-circle). Work through deliverables 1-5 in the Phase 3 section above, deploy, smoke-test (expect ~700ms + 4 BQ queries unchanged; bytes will jump because SA_DATA + COM_DATA now embed), visually compare overlay behaviour against Python builder, commit.
6. Phase 4 (Competitor tab + InSites embed). Same pattern. Before starting, check INSITES_PUBLIC_KEY is set on both Workers (wrangler secret list) — absent means iframe drill-down stays disabled, matching Python’s graceful fallback.
7. Phase 5. Advisor tool + SYSTEM_ADDENDUM, deploy, ask Roam to publish a map, verify. Commit.
8. Update MEMORY.md with a pointer to this file once Phase 5 lands, noting the port is complete.

Estimated remaining session time (Phases 3-5) if run without interruptions: 2-3 hours. Budget for drift.