HubSpot Data Architecture

Map of how HubSpot deal + engagement data flows from the live CRM into BigQuery, the Vectorize indexes, and the precomputed deal-brief cache that the Roam advisor and HubSpot Prospect Card read from. Includes the 18-month closed-deal history structure.

Counts/ranges verified live 2026-05-20. Dataset: listingmanager-1529856313699.fcr_operations.

Flow diagram

                          ┌─────────────────────────┐
                          │   HubSpot CRM (live)     │
                          │   deals + engagements    │
                          └───────────┬─────────────┘
              twice-daily 06:45+13:30 │ + every-min engagement drain
                          ▼
┌──────────────────────────────────────────────────────────────────────────┐
│ BigQuery  listingmanager-1529856313699.fcr_operations                      │
│                                                                            │
│  OPEN DEALS                          CLOSED HISTORY (18 months)            │
│  ┌────────────────────┐             ┌──────────────────────────────────┐  │
│  │ hubspot_deals       │            │ hubspot_deals_backcatalogue_18m  │  │
│  │ 1,473 rows          │            │ (VIEW) 10,810 rows               │  │
│  │ open only           │            │ 2024-11-01 → 2026-05-15          │  │
│  │ truncate+reload     │            │ Lost 9,603 / Won 1,193 / Open 14 │  │
│  │ 06:45+13:30 daily   │            │  = UNION of 3 six-month cohorts: │  │
│  │ last_modified:STR   │            │   • bc_2024_2025   3,311 (Nov–Apr)│ │
│  │ synced_at:TIMESTAMP │            │   • bc_2025_summer 4,213 (May–Oct)│ │
│  └─────────┬──────────┘             │   • bc_active      3,286 (Nov–now)│ │
│            │                        └──────────────┬───────────────────┘  │
│            │   ┌─ bc_2025_2026 3,133 (Nov25–Apr26) frozen, YoY-paired      │
│            │   │   with bc_2024_2025 (same window, +1yr) for win/loss YoY  │
│            │   └─ hubspot_deal_property_history_backcatalogue_* (same       │
│            │       cohort split) = stage-transition history for velocity   │
│            │                                                                │
│  ENGAGEMENTS                         BRIEF AUDIT                            │
│  ┌────────────────────┐             ┌──────────────────────────────────┐  │
│  │ hubspot_engagements │            │ deal_briefs                      │  │
│  │ 230,342 rows        │            │ audit log, 1 row / compose       │  │
│  │ EMAIL110k CALL80k   │            │ partition composed_at, 365d exp  │  │
│  │ TASK23k NOTE17k MTG │            │ brief_json STRING + sources[]    │  │
│  │ every-min cron drain│            └──────────────┬───────────────────┘  │
│  └─────────┬──────────┘                            │                       │
└────────────┼───────────────────────────────────────┼──────────────────────┘
             │                                         │
   ┌─────────┴──────────┐              compose-deal-brief reads:
   │ deals-vector-reindex│              hubspot_deals + backcatalogue_18m
   │ (daily 06:30 cron)  │              + engagements + active_clients
   ▼                     │              + live GBP/crawl  ──────────┐
┌────────────────────┐   │                                         ▼
│ VECTORIZE           │   │                            ┌────────────────────────┐
│ fcr-deal-history    │◄──┘                            │ KV  deal-brief:<id>    │
│ 1536d cosine        │                                │ hot blob, 35d TTL      │
│ embeds open+closed  │                                │ chunked cron 08-20/2h  │
│ meta idx: outcome,  │                                │ + prewarm + self-heal  │
│ close_year, county, │                                └───────────┬────────────┘
│ service_trade, owner│                                            │
└─────────┬──────────┘                                            │
          │              ┌──────────────────────┐                 │
          │              │ fcr-site-portfolio   │ (GP-listing-wide,│
          │              │ VECTORIZE — similar  │  feeds prospect_ │
          │              │ FCR sites/clients)   │  intel block)    │
          │              └──────────┬───────────┘                 │
          ▼                         ▼                             ▼
     ┌──────────────────────────────────────────────────────────────┐
     │  Roam advisor  +  HubSpot Prospect Card  (get_deal_brief, etc) │
     └──────────────────────────────────────────────────────────────┘

The 18-month closed-deal history

hubspot_deals_backcatalogue_18m is a VIEW equal to three sequential, non-overlapping 6-month cohort tables UNIONed (3,311 + 4,213 + 3,286 = 10,810):

Cohort table	Window	Role
hubspot_deals_backcatalogue_2024_2025	2024-11 → 2025-04	oldest 6m; also the frozen YoY baseline
hubspot_deals_backcatalogue_2025_summer	2025-05 → 2025-10	middle 6m
hubspot_deals_backcatalogue_active	2025-11 → 2026-05-15	current / rolling 6m
hubspot_deals_backcatalogue_2025_2026 (NOT in the view)	2025-11 → 2026-04	frozen snapshot of the current window, paired 1:1 with 2024_2025 for apples-to-apples YoY win/loss conversion
hubspot_deals_backcatalogue_staging	—	load staging

A parallel hubspot_deal_property_history_backcatalogue_* family (same cohort split + its own _18m view) holds property-change history — stage transitions over time — for cycle-time / conversion-velocity analysis, separate from the deal snapshots above.

18m view outcome split (verified 2026-05-20): Lost 9,603 / Won 1,193 / Open 14 (the 14 “Open” are stragglers mid-transition between the open table and the back-catalogue).

Stores

Store	Type	Rows	Role
hubspot_deals	BASE TABLE	1,473	Open deals only. Closed deals are NOT here — they live solely in the back-catalogue.
hubspot_deals_backcatalogue_18m	VIEW	10,810	Rolling 18m closed history (won + lost). UNION of 3 cohorts.
hubspot_deals_backcatalogue_{2024_2025,2025_summer,active,2025_2026,staging}	BASE	see above	Cohort tables behind the view + the frozen YoY pair.
hubspot_deal_property_history_backcatalogue_*	BASE + _18m VIEW	—	Property-change/stage-transition history for velocity analysis.
hubspot_engagements	BASE TABLE	230,342	Note/email/call/meeting/task bodies. EMAIL 110k, CALL 80k, TASK 23k, NOTE 17k, MEETING 300.
hubspot_tasks	VIEW	~33k	Portfolio-wide task queue over hubspot_engagements WHERE type='TASK'. Surfaces the fields buried in raw_payload as clean columns so “what tasks are due today for [rep]” is one query. See Task queue view.
deal_briefs	BASE TABLE	grows	Audit log of precomputed briefs (1 row/compose). Partitioned composed_at, clustered deal_id, 365-day expiry. brief_json STRING + sources_succeeded ARRAY.
graham_hubspot_deals / _staging	BASE	—	Separate working copy (not part of the advisor pipeline).
prospect_engagement_events	BASE	—	Telemetry, separate.

Vector indexes (Cloudflare Vectorize) — two distinct, don’t confuse

Index	Binding	Embeds	Notes
fcr-deal-history	DEAL_VECTORIZE	open (hubspot_deals) + closed (backcatalogue_18m) deals	1536-dim, cosine, text-embedding-3-small. Metadata indexes: outcome, close_year, county, service_trade, owner_name. Powers “deals like this one / what happened.”
fcr-site-portfolio	VECTORIZE	GP-listing-wide sites (not deals)	Feeds the prospect_intel “similar clients” block. GP-listing-wide → contains FRLs + churned, so consumers must split paying vs not (see prospect-intel).

Third index fcr-company-knowledge (COMPANY_KNOWLEDGE_VECTORIZE) exists but is company-knowledge, not HubSpot.

KV

Key	Namespace	TTL	Role
deal-brief:<deal_id>	CACHE	35 days	Hot precomputed brief blob. Written by compose-deal-brief, read by get-deal-brief (the advisor’s get_deal_brief tool).

Refresh cadence

Store	Refresh	Mechanism
hubspot_deals (open)	twice daily 06:45 & 13:30 UTC	full truncate + staging-swap rebuild (n8n Rk1WCogNesqYeUrr)
bc_active / 18m view	rolls forward as deals close	external sync
frozen cohorts (bc_2024_2025, bc_2025_2026)	static	one-time snapshots for YoY
hubspot_engagements	continuous	every-minute worker cron drains stragglers (batch 3)
hubspot_tasks (view)	continuous	none of its own — a view; fresh iff hubspot_engagements is (rides the every-minute drain)
deal_briefs + KV deal-brief:	chunked cron 08:00–20:00 every 2h + prewarm-enqueue + read self-heal	compose-deal-brief via BRIEF_QUEUE
fcr-deal-history (vector)	daily 06:30 UTC cron	deals-vector-reindex (paginated, incremental)
fcr-site-portfolio (vector)	manual reindex	n8n

Deal-brief compose inputs

compose-deal-brief (brief v1.2.x) reads, in parallel:

• hubspot_deals ∪ backcatalogue_18m — the deal row (open or closed)
• check-account (live CRM + active_clients) — CRM + active lines + MRR + tickets
• check-enrichment — cached GBP + keywords + ads + GSC + Ahrefs
• prospect-intel — category keywords, local area, similar paying clients (split from FRLs)
• deals-vector-search — similar past won/lost deals (from fcr-deal-history)
• hubspot_engagements — latest ~8 deduped engagements, real conversation ranked over automated
• live GBP (SerpAPI + Pleper) for unmapped prospects + home-page crawl

Output → KV deal-brief:<deal_id> (hot read) + deal_briefs BQ row (audit).

Task queue view (hubspot_tasks)

Barry Byrne asked (2026-07-07) for portfolio-wide task queries — “show me [rep]‘s tasks due today” — without looping get_deal_by_id per deal (43 calls for one book). The task data was already in hubspot_engagements (every TASK row, drained every minute); only the shape was missing — due_date, task_type, completed_at sat inside raw_payload. So fcr_operations.hubspot_tasks is a thin view, not a new sync: it rides the existing drain (fresh within a minute), adds no cron, and needs no separate freshness monitor.

DDL lives at repo data/hubspot_tasks_view.sql. Columns:

Column	Source	Notes
task_id	engagement_id	HubSpot task engagement id
deal_id	—	Joins hubspot_deals cleanly for deal context
subscriber_id, owner_id	—
owner_name	LEFT JOIN hubspot_deals	”Last, First”; NULL for reps with no open deals (~92% of task rows resolve)
task_subject, task_body	subject, body_text
status	task_status	NOT_STARTED / COMPLETED / DEFERRED
task_type	raw_payload.$.metadata.taskType	CALL / EMAIL / TODO
priority	raw_payload.$.metadata.priority
due_at / due_date	raw_payload.$.engagement.timestamp	due_date is a DATE → WHERE due_date = CURRENT_DATE() with no cast
created_at	raw_payload.$.engagement.createdAt	Real creation (works around the mislabeled base column)
completed_at	raw_payload.$.metadata.completionDate	Populated only when status = COMPLETED

Deduped with QUALIFY ROW_NUMBER() OVER (PARTITION BY engagement_id ORDER BY synced_at DESC) = 1.

Coverage / caveats: covers ~97% of the open book (1,026/1,059 open deals present); a brand-new deal’s task appears within the minute. It is a live task queue, not a complete historical audit of every task ever. owner_name resolves off open deals only. The advisor is wired to use it via a “TASK QUEUE” block in the shared staticBody (ai-advisor.js buildSystemAddendum) — all three surfaces route rep/task-queue questions here instead of per-deal calls.

Gotchas

• hubspot_deals is open-only. Closed deals resolve only via the back-catalogue, so get_deal_by_id keeps a live-HubSpot fallback.
• Pitched products live under TWO column names. The HubSpot property what_products_are_pitched_for_this_deal_ lands as what_products_pitched on the back-catalogue tables but was historically products_pitched on the open table. Since 2026-06-11 the open hubspot_deals carries both (identical values; what_products_pitched added after the Roam advisor hit “column not found” on its canonical UNNEST filter and wrongly reported the datapoint missing from BQ — Barry Byrne escalation). The sync workflow Rk1WCogNesqYeUrr writes both from the same source field. Sparse population is expected: only ~310/1,870 open deals have a pitch (Mid Stage 156/156, Late 129/149, Early 21/1,551 — early-stage deals carry no qualified pitch). v_hubspot_deals_all normalises both to products_pitched.
• hubspot_engagements.created_at is mislabeled for TASK rows — it holds the task due date (raw_payload.$.engagement.timestamp), not the creation time, which is why the table has future-dated rows (max observed 2027-05-10). Don’t trust raw MAX(created_at). The TASK normalizer in worker/src/handlers/hubspot-engagements-sync.js is the source of this (open follow-up to fix); the hubspot_tasks view works around it by reading real created from $.engagement.createdAt and exposing the due date as its own due_date/due_at columns.
• fcr-deal-history reindexes daily at 06:30 UTC (incremental), so brand-new deals still lag until the next run; compose-deal-brief tolerates a similar_deals miss.
• CF Queue auto-pauses the brief consumer if a large backlog (1,000+) of slow messages lands at once — drip in ~80-chunks; clear a pause via consumer delete + redeploy. See the deal-brief design notes.

---

Generated 2026-05-20; refresh schedules corrected 2026-06-29 (deal sync = twice daily 06:45 + 13:30 UTC via n8n Rk1WCogNesqYeUrr; deal-vector reindex = daily 06:30 cron, not manual); hubspot_tasks task-queue view added 2026-07-07 (Barry Byrne ask). Update the counts/ranges if the cohort windows roll.