CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/airbyte-airbyte-source-hubspot

HubSpot source connector for Airbyte that syncs CRM data including contacts, companies, deals, and marketing activities with support for OAuth and Private App authentication

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview
Eval results
Files

property-history.mddocs/

Property History

Historical tracking streams for CRM entity property changes, providing detailed audit trails and change tracking capabilities for contacts, companies, and deals.

Capabilities

Contacts Property History

Historical changes to contact properties with timestamps and change details.

contacts_property_history:
  primary_key: ["contactId", "property", "timestamp"]
  cursor_field: "timestamp"
  sync_mode: incremental
  schema:
    type: object
    properties:
      contactId:
        type: string
        description: "Contact ID associated with the property change"
      property:
        type: string
        description: "Name of the property that changed"
      value:
        type: string
        description: "New property value"
      sourceType:
        type: string
        description: "Source of the change (e.g., API, IMPORT, CRM_UI)"
      sourceId:
        type: string
        description: "Identifier of the change source"
      sourceLabel:
        type: string  
        description: "Human-readable source description"
      timestamp:
        type: string
        format: date-time
        description: "When the property change occurred"
      updatedByUserId:
        type: string
        description: "ID of user who made the change"

Usage Example:

streams:
  - name: contacts_property_history
    sync_mode: incremental
    cursor_field: ["timestamp"]
    destination_sync_mode: append

Companies Property History

Historical changes to company properties with timestamps and change details.

companies_property_history:
  primary_key: ["companyId", "property", "timestamp"]
  cursor_field: "timestamp"
  sync_mode: incremental
  schema:
    type: object
    properties:
      companyId:
        type: string
        description: "Company ID"
      property:
        type: string
        description: "Name of the property that changed"
      value:
        type: string
        description: "New property value"
      sourceType:
        type: string
        description: "Source of the change (e.g., API, IMPORT, CRM_UI)"
      sourceId:
        type: string
        description: "Identifier of the change source"
      sourceLabel:
        type: string
        description: "Human-readable source description"
      timestamp:
        type: string
        format: date-time
        description: "When the property change occurred"
      updatedByUserId:
        type: string
        description: "ID of user who made the change"

Deals Property History

Historical changes to deal properties with timestamps and change details.

deals_property_history:
  primary_key: ["dealId", "property", "timestamp"]
  cursor_field: "timestamp"
  sync_mode: incremental
  schema:
    type: object
    properties:
      dealId:
        type: string
        description: "Deal ID"
      property:
        type: string
        description: "Name of the property that changed"
      value:
        type: string
        description: "New property value"
      sourceType:
        type: string
        description: "Source of the change (e.g., API, IMPORT, CRM_UI)"
      sourceId:
        type: string
        description: "Identifier of the change source"
      sourceLabel:
        type: string
        description: "Human-readable source description"
      timestamp:
        type: string
        format: date-time
        description: "When the property change occurred"
      updatedByUserId:
        type: string
        description: "ID of user who made the change"

Property History Extraction

Property history streams use a specialized extractor that processes HubSpot's propertiesWithHistory API response format:

Custom Extraction Logic

HubspotPropertyHistoryExtractor:
  type: RecordExtractor
  field_path: ["results"]
  entity_primary_key: string    # Entity ID field name (vid, companyId, dealId)
  additional_keys: array        # Additional fields to include from parent record
  
  # Extraction behavior:
  # 1. Iterates over each entity in results array
  # 2. Extracts propertiesWithHistory object for each entity
  # 3. Creates individual records for each property version
  # 4. Injects entity ID and timestamp into each property history record
  # 5. Skips hs_lastmodifieddate to avoid duplicate change tracking

Property History Record Structure

Each property change generates a separate record with the following structure:

PropertyHistoryRecord:
  type: object
  properties:
    # Entity identifier (varies by stream)
    vid: string              # For contacts_property_history
    companyId: string        # For companies_property_history  
    dealId: string           # For deals_property_history
    
    # Property change details
    property: string         # Property name that changed
    value: string           # New property value
    timestamp: string       # When change occurred (ISO format)
    sourceType: string      # Change source type
    sourceId: string        # Source identifier
    sourceLabel: string     # Human-readable source
    updatedByUserId: string # User who made change

Timestamp Handling

Property history streams handle multiple timestamp formats from HubSpot:

  • Millisecond timestamps: Converted to ISO 8601 format
  • Second timestamps: Converted to ISO 8601 format
  • ISO strings: Used directly
  • Invalid timestamps: Logged and original value preserved

Source Types

Common sourceType values include:

  • API: Changes made via API calls
  • CRM_UI: Changes made through HubSpot interface
  • IMPORT: Changes from data imports
  • AUTOMATION: Changes from workflows/automation
  • CALCULATED: Changes from calculated properties
  • MIGRATION: Changes from data migrations

Usage Patterns

Audit Trail Analysis:

SELECT 
  vid as contact_id,
  property,
  value,
  timestamp,
  sourceType,
  updatedByUserId
FROM contacts_property_history 
WHERE property = 'lifecyclestage'
ORDER BY timestamp DESC;

Property Change Frequency:

SELECT 
  property,
  COUNT(*) as change_count,
  COUNT(DISTINCT companyId) as companies_affected
FROM companies_property_history
WHERE timestamp >= '2024-01-01'
GROUP BY property
ORDER BY change_count DESC;

User Activity Tracking:

SELECT 
  updatedByUserId,
  COUNT(*) as changes_made,
  MIN(timestamp) as first_change,
  MAX(timestamp) as last_change
FROM deals_property_history
WHERE sourceType = 'CRM_UI'
GROUP BY updatedByUserId;

State Migration

Property history streams include state migration to handle legacy empty cursor states:

MigrateEmptyStringState:
  type: StateMigration
  cursor_field: "timestamp"
  cursor_format: "%ms"        # Millisecond timestamp format
  
  # Migration logic:
  # - If cursor_field is empty string, migrate to start_date
  # - Convert start_date to appropriate timestamp format
  # - Default to "2006-06-01T00:00:00.000Z" if no start_date

Performance Considerations

  • Large History: Property history streams can contain millions of records for active portals
  • Incremental Sync: Always use incremental sync to avoid reprocessing historical data
  • Filtering: Consider filtering by specific properties if only tracking certain changes
  • Timestamp Precision: Uses millisecond precision for accurate chronological ordering

Install with Tessl CLI

npx tessl i tessl/airbyte-airbyte-source-hubspot

docs

additional-streams.md

authentication.md

crm-streams.md

custom-objects.md

engagements.md

index.md

marketing.md

property-history.md

tile.json