# T013: HubSpot → Social Hub Translation Layer — Design Document

**Status:** DRAFT — awaiting review
**Author:** Claude (automated)
**Date:** 2026-03-24

---

## 1. Category → Track Mapping

### Problem

HubSpot campaigns have no native "track" concept. We need a deterministic mapping from HubSpot campaign metadata to one of seven Social Hub content tracks:

| Track | Purpose |
|---|---|
| `ENTERPRISE` | Board/exec, investor relations |
| `DEVELOPER` | Technical content, APIs, docs |
| `TECH_EDGE` | Research, TiDB innovations |
| `THOUGHT_LEADERSHIP` | CEO/exec commentary |
| `EVENT` | Conferences, webinars, meetups |
| `EMPLOYEE` | Culture, hiring, spotlights |
| `PRODUCT` | Feature launches, updates |

### Mapping Strategy

Use a **rules engine** driven by a JSON config (see §7). Each rule tests HubSpot campaign fields in priority order. First match wins.

**Input fields available from HubSpot campaigns:**

| HubSpot Field | API Property | Use |
|---|---|---|
| Campaign name | `hs_name` | Pattern matching (e.g., contains "launch") |
| UTM campaign | `hs_utm` | Structured tag (e.g., `product-launch-q2`) |
| Audience | `hs_audience` | Free text, keyword matching |
| Owner | `hs_owner` | Maps to internal role (exec → THOUGHT_LEADERSHIP) |
| Notes | `hs_notes` | Fallback keyword scan |
| Custom property | `social_track` | **Recommended**: add a custom HubSpot property with enum values matching Social Hub tracks |

**Resolution order:**

1. **Explicit override** — If the HubSpot workflow payload includes `properties.track` (the existing `social.post` format), use it directly. No rules evaluated.
2. **Custom property** — If `social_track` custom property is set on the campaign, use it.
3. **UTM prefix rules** — Match `hs_utm` against configurable prefixes (e.g., `dev-*` → DEVELOPER, `event-*` → EVENT).
4. **Name pattern rules** — Regex match on `hs_name` (e.g., `/launch|release|feature/i` → PRODUCT).
5. **Owner mapping** — Map specific HubSpot owners to tracks (e.g., CEO → THOUGHT_LEADERSHIP).
6. **Default track** — Configurable fallback (recommend: `PRODUCT`).

### Why This Approach

- **No code changes required** to add/modify mappings — edit the JSON config.
- **Explicit overrides** let the HubSpot admin bypass all rules when they know the track.
- **Custom property** is the cleanest long-term solution — HubSpot admins see the track in the campaign UI.
- **Pattern rules** catch campaigns that weren't explicitly tagged.

---

## 2. Queue & Scheduling

### Problem

HubSpot workflows can fire at any time. Social Hub has per-track weekly slot caps, optimal posting windows, and priority-based scheduling. We need to translate HubSpot timing into Social Hub queue positions.

### Scheduling Modes

| Mode | Trigger | Behavior |
|---|---|---|
| **Explicit** | `properties.scheduledAt` in webhook payload | Use the specified time. If it conflicts with an existing slot (within 30 min), bump to next available window. |
| **Campaign window** | `hs_start_date` / `hs_end_date` on campaign | Distribute posts evenly across the campaign window using `bestWindows` from TrackConfig. |
| **Immediate queue** | No timing info | Call `findNextSlot(track, priority)` — existing queue algorithm. |

### Priority Rules

| Condition | Priority | Rationale |
|---|---|---|
| `properties.priority = "EMERGENCY"` | EMERGENCY | Bypasses caps, publishes now |
| `properties.priority = "URGENT"` | URGENT | Next available slot, respects cap |
| Campaign with `hs_name` containing "urgent" or "breaking" | URGENT | Auto-escalation from campaign metadata |
| Default | NORMAL | Standard queue behavior |

### Batch Campaign Handling

When a HubSpot campaign maps to multiple posts (e.g., a thread, or multi-region variants):

1. **Thread posts** — Schedule the root post normally; subsequent posts get `scheduledAt = root.scheduledAt + 1 minute` per post (Twitter thread timing).
2. **Multi-region variants** — Each region gets its own slot via `findNextSlot()`, respecting per-track caps independently. Region variants are separate Post records.
3. **Drip campaigns** — If `hs_start_date` and `hs_end_date` span multiple days, distribute posts across `bestWindows` within that range, one per day maximum.

### Conflict Resolution

- **Same track, same window**: Second post bumps to next `bestWindows` slot.
- **Weekly cap reached**: Post enters `PENDING_APPROVAL` status with admin notification. Admin can override cap or reschedule.
- **Past `scheduledAt`**: If the specified time is in the past, treat as immediate queue (next available slot).

---

## 3. Region Binding

### Problem

HubSpot segments contacts by geography; Social Hub targets content by region (APAC/AMERICAS/EMEA/GLOBAL). We need to bridge these.

### Mapping Approach

**Source signals (priority order):**

1. **Explicit in payload** — `properties.region` from `social.post` webhook. Direct, no translation.
2. **Campaign audience field** — Parse `hs_audience` for geographic keywords:
   - `asia|apac|japan|india|singapore|australia|china|korea` → APAC
   - `america|us|usa|canada|brazil|latam|north america|south america` → AMERICAS
   - `europe|emea|uk|germany|france|middle east|africa` → EMEA
   - `global|worldwide|all regions|international` → GLOBAL
3. **Contact list geography** — If the campaign targets a HubSpot list, inspect the list name and filter criteria for country/region properties (`hs_analytics_source`, timezone, IP country).
4. **UTM tags** — Parse region hints from UTM (e.g., `product-launch-apac` → APAC).
5. **Default** — GLOBAL (safest fallback for untagged campaigns).

### Multi-Region Campaigns

When a campaign targets multiple regions:

- Create **separate Post records** per region, each with its own `region` field.
- Content can be identical or varied (see §5 content transformation).
- Each regional post goes through its own track queue and approval flow.
- Campaign record links all variants via `campaignId`.

**Example:** A product launch campaign tagged `APAC, AMERICAS` produces:
- Post A: region=APAC, scheduledAt=optimal APAC window (e.g., 10am SGT)
- Post B: region=AMERICAS, scheduledAt=optimal Americas window (e.g., 10am EST)

### Ownership

- **HubSpot admin** owns region assignment at campaign creation time (via `hs_audience` or custom property).
- **Social Hub admin** can override region on any post before publishing.
- **Translation layer** auto-assigns based on rules above, but never silently publishes to a region the admin didn't intend.

---

## 4. Account Targeting

### Problem

An organization may have multiple Twitter accounts (e.g., `@BrandHQ`, `@BrandDev`, regional accounts). The translation layer must determine which account(s) each campaign posts from.

### Account Selection Logic

| Priority | Signal | Example |
|---|---|---|
| 1 | Explicit in payload | `properties.accounts: ["@BrandHQ"]` |
| 2 | Track → account mapping | DEVELOPER → `@BrandDev`; ENTERPRISE → `@BrandHQ` |
| 3 | Region → account mapping | APAC → `@BrandAPAC` (if exists) |
| 4 | Default account | Primary brand account (configurable) |

### Multi-Account Support

- The existing `PostSocialAccount` join table already supports multi-account publishing.
- Translation layer creates one `PostSocialAccount` record per target account.
- Each account publishes independently (separate externalIds, separate analytics).

### Override Logic

```
payload.accounts > trackAccountMap[track] > regionAccountMap[region] > defaultAccount
```

- Admins can always override via the Social Hub UI before publishing.
- If a mapped account doesn't exist in the database (no OAuth connection), the post is created as `DRAFT` with a notification: "Account @X not connected — connect or reassign."

### Configuration (see §7)

```json
"accountTargeting": {
  "defaultAccount": "@BrandHQ",
  "trackOverrides": {
    "DEVELOPER": ["@BrandDev"],
    "ENTERPRISE": ["@BrandHQ"]
  },
  "regionOverrides": {
    "APAC": ["@BrandHQ", "@BrandAPAC"]
  }
}
```

---

## 5. Content Transformation

### Problem

HubSpot campaign copy (email subjects, body text, notes) doesn't map directly to tweet-sized content. We need rules for extracting, truncating, and formatting social posts.

### Content Sources (Priority Order)

1. **Explicit content** — `properties.content` from `social.post` payload. Used as-is.
2. **Campaign notes** — `hs_notes` field, if it starts with `[social]` prefix (convention for marketing teams to embed draft tweet copy).
3. **Email subject** — For campaigns with associated marketing emails, use the subject line as a starting point.
4. **Campaign name** — Last resort, used as headline. Requires manual editing.

### 280-Character Handling

**Strategy: Truncate + link**

1. If content ≤ 280 chars → use as-is.
2. If content > 280 chars → truncate at last word boundary before 257 chars, append `…` and a shortened link (23 chars for t.co).
3. If content > 560 chars → flag for **thread conversion** (see below).

**Preserved elements (deducted from char budget):**
- Hashtags: kept at end, max 3, deducted from 280 budget
- Mentions: preserved in-place
- URLs: count as 23 chars each (Twitter t.co wrapping)
- Media: doesn't count toward character limit

### Media Handling

| HubSpot Source | Social Hub Target |
|---|---|
| `properties.mediaUrls` (array) | Stored in `Post.mediaUrls` JSON, max 4 images or 1 video |
| Email hero image | Extract from email `widgets.hs_email_body` — first `<img>` with width > 400px |
| Campaign assets | Not available via API — requires manual upload or URL reference |

**Validation:**
- Reject unsupported formats (only jpg, png, gif, mp4, webp)
- Reject images > 5MB, videos > 15MB (Twitter limits)
- If more than 4 images provided, take first 4 and log warning

### Thread Support

When content exceeds 560 characters or contains explicit thread markers (`---` or `[thread]`):

1. Split at explicit markers first, then at paragraph boundaries, then at sentence boundaries.
2. Each segment must be ≤ 280 chars.
3. Number segments as `1/N`, `2/N`, etc. (deducted from char budget: 4 chars for `1/N` up to 5 chars for `10/N`).
4. First segment is the root post; subsequent segments are replies (handled by publisher).
5. Store as a single Post with `content` containing the full text and a `threadSegments` JSON field for the split version.

### Content Sanitization

- Strip HTML tags (from email body content)
- Convert `&amp;` → `&`, `&lt;` → `<`, etc.
- Remove tracking pixels and invisible elements
- Preserve emoji (pass through UTF-8)
- Remove internal-only markers like `[INTERNAL]`, `[DRAFT]`

---

## 6. Error & Edge Cases

### Failed Mappings

| Failure | Behavior | Recovery |
|---|---|---|
| Unknown track value | Create as DRAFT, set track to default, notify admin | Admin assigns correct track |
| Invalid region | Default to GLOBAL, log warning | Admin can override |
| Missing content | Create DRAFT with campaign name as placeholder, flag for review | Admin writes content |
| Account not connected | Create DRAFT, notify admin with "connect account" CTA | Admin connects OAuth |
| Invalid media URL | Create post without media, log warning | Admin can add media manually |
| Payload parse failure | Return 400, log full payload to `WebhookLog` | Replay from webhook log |

### Deduplication

HubSpot webhooks can send duplicate events (retries, workflow re-enrollments). Dedup strategy:

1. **Event ID dedup** — HubSpot includes `eventId` on standard webhook events. Store in `WebhookLog.externalId`, reject duplicates with 200 OK (idempotent).
2. **Content + time dedup** — For `social.post` actions (no eventId), hash `content + scheduledAt + track + region`. Check for existing posts with matching `sourceRef` hash within a 1-hour window.
3. **Campaign dedup** — For campaign-triggered posts, check `sourceRef = campaign:{hs_object_id}` + `track` + `region`. One post per campaign per track per region unless explicitly flagged as a series.

### Rate Limiting

| Layer | Limit | Handling |
|---|---|---|
| HubSpot → Social Hub | 100 events/10 seconds (HubSpot's own limit) | N/A — HubSpot throttles itself |
| Translation processing | 10 posts/minute (self-imposed) | Queue excess in `WebhookLog` with status=RECEIVED, process async |
| Social Hub → Twitter | 300 tweets/3 hours, 50 per 15 min (Twitter v2) | Existing publisher handles this; translation layer doesn't publish directly |

### Retry Logic

- HubSpot retries failed webhooks (non-2xx) up to 10 times over 24 hours.
- Translation layer should return 200 even for non-fatal errors (log and process later) to prevent unnecessary retries.
- Only return 5xx for true infrastructure failures (DB down, etc.).

### Stale Campaign Data

- If a campaign is updated in HubSpot after a post is created, the post is NOT auto-updated (posts are snapshots).
- To re-sync, the HubSpot admin should trigger a new webhook or use the bulk update endpoint.
- Consider adding a `lastSyncedAt` field to campaign-linked posts for audit purposes.

---

## 7. Configuration Schema

### Location

`/config/hubspot-translation.json` — loaded at runtime, hot-reloadable without restart.

### Schema

```json
{
  "$schema": "hubspot-translation-config-v1",
  "version": "1.0.0",
  "lastUpdated": "2026-03-24T00:00:00Z",

  "trackMapping": {
    "customPropertyField": "social_track",
    "defaultTrack": "PRODUCT",
    "utmPrefixRules": [
      { "pattern": "dev-*", "track": "DEVELOPER" },
      { "pattern": "event-*", "track": "EVENT" },
      { "pattern": "product-*", "track": "PRODUCT" },
      { "pattern": "enterprise-*", "track": "ENTERPRISE" },
      { "pattern": "thought-*", "track": "THOUGHT_LEADERSHIP" },
      { "pattern": "employee-*", "track": "EMPLOYEE" },
      { "pattern": "research-*", "track": "TECH_EDGE" }
    ],
    "namePatternRules": [
      { "regex": "(?i)(launch|release|feature|update|changelog)", "track": "PRODUCT" },
      { "regex": "(?i)(conference|webinar|meetup|summit|workshop)", "track": "EVENT" },
      { "regex": "(?i)(api|sdk|tutorial|docs|developer|hackathon)", "track": "DEVELOPER" },
      { "regex": "(?i)(hiring|culture|team|employee|spotlight)", "track": "EMPLOYEE" },
      { "regex": "(?i)(research|paper|benchmark|innovation)", "track": "TECH_EDGE" },
      { "regex": "(?i)(investor|board|quarterly|earnings|ipo)", "track": "ENTERPRISE" },
      { "regex": "(?i)(opinion|perspective|industry|future|vision)", "track": "THOUGHT_LEADERSHIP" }
    ],
    "ownerRules": [
      { "hubspotOwnerId": "12345", "name": "CEO", "track": "THOUGHT_LEADERSHIP" },
      { "hubspotOwnerId": "12346", "name": "CTO", "track": "TECH_EDGE" }
    ]
  },

  "regionMapping": {
    "defaultRegion": "GLOBAL",
    "audienceKeywords": {
      "APAC": ["asia", "apac", "japan", "india", "singapore", "australia", "china", "korea", "southeast asia"],
      "AMERICAS": ["america", "us", "usa", "canada", "brazil", "latam", "north america", "south america"],
      "EMEA": ["europe", "emea", "uk", "germany", "france", "middle east", "africa"],
      "GLOBAL": ["global", "worldwide", "all regions", "international"]
    },
    "utmRegionSuffix": true
  },

  "accountTargeting": {
    "defaultAccount": "@BrandHQ",
    "trackOverrides": {
      "DEVELOPER": ["@BrandDev"],
      "ENTERPRISE": ["@BrandHQ"],
      "EMPLOYEE": ["@BrandHQ"]
    },
    "regionOverrides": {
      "APAC": ["@BrandAPAC"]
    }
  },

  "scheduling": {
    "defaultPriority": "NORMAL",
    "urgentKeywords": ["urgent", "breaking", "critical", "hotfix"],
    "batchSpacingMinutes": 60,
    "threadDelaySeconds": 60,
    "maxPostsPerCampaign": 20
  },

  "contentTransformation": {
    "maxTweetLength": 280,
    "threadThreshold": 560,
    "maxHashtags": 3,
    "maxMediaItems": 4,
    "maxMediaSizeMb": 5,
    "maxVideoSizeMb": 15,
    "allowedMediaTypes": ["image/jpeg", "image/png", "image/gif", "video/mp4", "image/webp"],
    "socialPrefixMarker": "[social]",
    "threadMarkers": ["---", "[thread]"],
    "stripPatterns": ["\\[INTERNAL\\]", "\\[DRAFT\\]", "\\[DO NOT PUBLISH\\]"]
  },

  "deduplication": {
    "windowMinutes": 60,
    "hashFields": ["content", "scheduledAt", "track", "region"]
  },

  "rateLimiting": {
    "maxPostsPerMinute": 10,
    "burstAllowance": 5
  }
}
```

### Hot Reload

- Config file is read on each webhook invocation (with in-memory cache, TTL 60s).
- Cache invalidated on file modification (via `fs.stat` mtime check).
- Invalid config falls back to last known good config + admin alert.

### Validation

On load, validate against a JSON Schema:
- All track values must be valid `ContentTrack` enum members.
- All region values must be valid `Region` enum members.
- Regex patterns must compile without errors.
- Account names must be non-empty strings.

---

## Appendix A: End-to-End Flow

```
HubSpot Workflow fires
    │
    ▼
POST /api/v1/webhooks/hubspot
    │
    ├── Signature verification (v1/v2/v3)
    ├── Log to WebhookLog
    ├── Deduplication check (eventId or content hash)
    │
    ▼
Translation Layer
    │
    ├── 1. Resolve track (explicit → custom prop → UTM → name pattern → owner → default)
    ├── 2. Resolve region (explicit → audience keywords → UTM suffix → default)
    ├── 3. Resolve account(s) (explicit → track map → region map → default)
    ├── 4. Transform content (extract → sanitize → truncate/thread → attach media)
    ├── 5. Resolve scheduling (explicit time → campaign window → findNextSlot)
    │
    ▼
Create Post(s) in Social Hub
    │
    ├── Single post: status = SCHEDULED (or PENDING_APPROVAL if track requires it)
    ├── Thread: root post + thread segments
    ├── Multi-region: one Post per region
    │
    ▼
Existing queue/approval/publish pipeline takes over
```

## Appendix B: HubSpot Custom Properties to Create

To enable the cleanest integration, create these custom properties in HubSpot:

| Property | Internal Name | Type | Options |
|---|---|---|---|
| Social Track | `social_track` | Dropdown | DEVELOPER, ENTERPRISE, EMPLOYEE, TECH_EDGE, THOUGHT_LEADERSHIP, EVENT, PRODUCT |
| Social Region | `social_region` | Dropdown | APAC, AMERICAS, EMEA, GLOBAL |
| Social Accounts | `social_accounts` | Multi-line text | Comma-separated account handles |
| Social Priority | `social_priority` | Dropdown | NORMAL, URGENT, EMERGENCY |
| Social Content | `social_content` | Multi-line text | Tweet text (overrides auto-extraction) |

These properties appear in the HubSpot campaign UI, giving marketing teams direct control without needing to understand the translation rules.

## Appendix C: Open Questions

1. **AI-assisted content**: Should the translation layer call `/api/v1/ai/draft` when campaign copy exceeds 280 chars, instead of mechanical truncation? Tradeoff: better content vs. latency and cost.
2. **Approval bypass**: Should HubSpot-sourced posts to ENTERPRISE track skip approval if the HubSpot workflow already had an approval step? Need to detect HubSpot approval status.
3. **Campaign analytics rollup**: Should we aggregate post analytics back to the HubSpot campaign (via API write-back)? Useful but adds complexity and requires HubSpot write scopes.
4. **Multi-network**: Current design focuses on Twitter. LinkedIn/Facebook should follow the same translation pipeline but may need network-specific content variants (e.g., LinkedIn supports longer posts).