The "content database" Robert references is NOT a single SQL table — it's the union of mining systems, MH log entries, per-page playbooks, and live API outputs already shipped Apr 17–23. This document maps each Phase 1 page to its existing content sources by MH entry ID + playbook URL + script path. Cite verbatim. Don't reinvent.
1. Service Page Mining System — Apr 21 ship
bsp-apr21-service-page-mining-system/opt/nexus/titan/service_page_reviews_apply.pyMining totals (verbatim from MH bsp-apr21): 27,665 customer phrases · 2,080 emergency-flagged · 1,478 playbook hits · 17 Daniel transcript items · 1,923 field notes · 49 keyword matches · 5 Google reviews. No fabrication. Tuple per page: target_post_id, figma_file_key, figma_desktop_node, copy_brief_path.
Open P3 (verbatim): "orchestrator copy-injection layer" — connects mining output to Bricks page meta. This is the "last-mile" layer that needs wiring for the 9 service-page drafts to populate from mining JSON. Not yet built per Apr 21 entry; verify against Apr 23+ MH.
2. Location Page Mining Pipeline — Apr 23 ship
/opt/nexus/titan/location_page_mining.py (41,404 bytes · 600+ lines · production Python)bsp-apr23-location-page-mining-pipeline-shippedbsp-apr23-overland-park-location-page-shipped (post 258 · 146 elements · 3 snippets · 4-viewport verified)Runtime: ~30 sec per city via python3 /opt/nexus/titan/location_page_mining.py --city overland-park. Outputs BSP_{City}_Location_Page_Brief.html + /tmp/s7_loc_{slug}_content_brief.json.
9 cities currently supported in CITY_ZIPS dict: overland-park, olathe, lenexa, shawnee, leawood, prairie-village, kansas-city-mo, kansas-city-ks, lees-summit. To extend to 15 (Apr 18 TIER A scope): add 6 more city ZIP arrays.
| # | Source | Path | Sample / OP run output |
|---|---|---|---|
| 1 | Review intelligence DB | review_intelligence.db | 29,800 review_aspects · phrases filtered by city/slug match in customer_language · top-10 by score |
| 2 | Keyword north-star DB | keyword_north_star.db | 234 keywords + 28 clusters · OP returned 7 city-modified terms (e.g. "plumber Overland Park" vol=590 diff=55, "sewer repair Overland Park" vol=170) |
| 3 | Field-notes intelligence | field_notes_intelligence.json | Customer quotes filtered by ZIP match against CITY_ZIPS · OP=12 ZIPs · 5 quotes matched |
| 4 | HCP phone index | hcp_phone_index.json (2.9 MB · 4267 customers) | ⚠️ GAP: HCP customer records lack city field. OP returned 0 matches. Fix needed: phone-to-ZIP lookup before Friday cutover |
| 5 | City landmarks | city_landmarks.json (Google Places API output) | 63 landmarks across 9 cities · top-3 by review count · OP: Oak Park Mall (10,986 rev), Game Show Battle Rooms (5,749), Town Center Plaza (3,963) |
| 6 | GSC city baseline | /api/gsc/city/{slug} (router: /opt/nexus/titan/api/gsc_city.py) | Per-city organic baseline from titan.gsc_search_performance · OP: 10 clicks / 348 impressions / 28d window |
| 7 | Fleet availability | /api/fleet/availability/{slug} (router: /opt/nexus/titan/api/fleet.py) | Live ETA · OP: 60 min average · backed by fleet_availability.json (cron every 15 min) |
| 8 | City performance composite | /api/city-performance/{slug} (router: /opt/nexus/titan/api/city_performance.py) | Composite: GSC + GA4 + fleet + landmarks + UX score |
| + | GBP reviews | /api/gbp/reviews/recent | 396 total reviews · cohort logic: city_mentioned + top_five_star_recent · OP: 1 explicit mention, 20 recent 5-star |
| + | Perplexity SERP gap | Perplexity sonar-pro (optional, --no-serp to skip) | Per-city SERP gap analysis · adds ~20 sec per city |
Backed by bsp-apr20-memory-backfill-phase2-location-stack — Apr 17 stack ship: 6 new API routers + supporting crons.
Mining → Bricks page meta last-mile push
The mining systems output JSON briefs (e.g. /tmp/s7_loc_{slug}_content_brief.json). The Bricks pages need element-tree writes via /bsp/v3/bricks/native-save. The connector script that reads brief JSON and writes to Bricks is NOT yet shipped:
nexus_populate_service_pages.py — wraps service-page mining + Template A/B selection + native-save push. GAPnexus_populate_location_pages.py — wraps location-page mining + location template + native-save push. GAPReference pattern: the OP 258 page (bsp-apr23-overland-park-location-page-shipped) was built MANUALLY using the location mining brief — proving the brief-to-Bricks path works end-to-end. The "last-mile" layer would automate that manual step.
Estimated build time: ~4-6 hours per script × 2 = 8-12 hours. Reuses weather_budget_mutator.get_gads_client() for auth pattern, calls /bsp/v3/bricks/native-save for writes (Apr 22 stable primitive).
| Page | Mining source | Playbook | Last-mile push | Kalen review |
|---|---|---|---|---|
| /services/sewer/sewer-repair/ | service-page mining | Mining-system playbook | GAP | Required |
| /services/drains/drain-cleaning/ | service-page mining | Drain Cleaning brief | GAP | Required |
| /services/sewer/trenchless-sewer-repair/ | service-page mining | Mining-system playbook | GAP | Required |
| /services/sewer/sewer-cleaning/ | service-page mining | Mining-system playbook | GAP | Required |
| /services/water-heaters/water-heater-repair/ | service-page mining | Mining-system playbook | GAP | Required |
| /services/emergency/sump-pump-emergency/ | service-page mining | Mining-system playbook | GAP | Required |
| /services/other/leak-repair/ | service-page mining | Mining-system playbook | GAP | Required |
| /sewer-camera-inspection/ | BUILT Bricks page 8 already complete (Audrey faithful) | — | — | |
| /emergency-plumbing/ | BUILT Bricks page 12 (clone-swap from page 8) | — | — | |
| /about-us/ | per-page playbook | BSP_About_Page_Playbook | GAP | Required |
| /contact-us/ | per-page playbook | BSP_Contact_Page_Playbook | GAP | Required |
| /faqs/ | per-page playbook + field knowledge | BSP_FAQ_Page_Playbook (7→15) | GAP | Required |
| /financing/ | per-page playbook | BSP_Financing_Page_Playbook | GAP | Required |
| /reviews/ | GBP API + reviews DB | Reviews v2.1 brief (verify) | GAP | Required |
| /plumber-in-overland-park/ | BUILT First location ship Apr 23 · 146 elements | — | — | |
| /map/plumber-in-{olathe,lenexa,shawnee,leawood,prairie-village,kcmo,kck,lees-summit}/ (8) | location-page mining (9-city set) | Mining brief per city | GAP | Batched review |
| /map/plumber-in-{6 TIER B cities}/ | location-page mining (extend CITY_ZIPS) | Mining brief per city | GAP + extend pipeline | Batched review |