0
# Property History
1

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

4
## Capabilities
5

6
### Contacts Property History
7

8
Historical changes to contact properties with timestamps and change details.
9

10
```yaml { .api }
11
contacts_property_history:
12
  primary_key: ["contactId", "property", "timestamp"]
13
  cursor_field: "timestamp"
14
  sync_mode: incremental
15
  schema:
16
    type: object
17
    properties:
18
      contactId:
19
        type: string
20
        description: "Contact ID associated with the property change"
21
      property:
22
        type: string
23
        description: "Name of the property that changed"
24
      value:
25
        type: string
26
        description: "New property value"
27
      sourceType:
28
        type: string
29
        description: "Source of the change (e.g., API, IMPORT, CRM_UI)"
30
      sourceId:
31
        type: string
32
        description: "Identifier of the change source"
33
      sourceLabel:
34
        type: string  
35
        description: "Human-readable source description"
36
      timestamp:
37
        type: string
38
        format: date-time
39
        description: "When the property change occurred"
40
      updatedByUserId:
41
        type: string
42
        description: "ID of user who made the change"
43
```
44

45
**Usage Example:**
46

47
```yaml
48
streams:
49
  - name: contacts_property_history
50
    sync_mode: incremental
51
    cursor_field: ["timestamp"]
52
    destination_sync_mode: append
53
```
54

55
### Companies Property History
56

57
Historical changes to company properties with timestamps and change details.
58

59
```yaml { .api }
60
companies_property_history:
61
  primary_key: ["companyId", "property", "timestamp"]
62
  cursor_field: "timestamp"
63
  sync_mode: incremental
64
  schema:
65
    type: object
66
    properties:
67
      companyId:
68
        type: string
69
        description: "Company ID"
70
      property:
71
        type: string
72
        description: "Name of the property that changed"
73
      value:
74
        type: string
75
        description: "New property value"
76
      sourceType:
77
        type: string
78
        description: "Source of the change (e.g., API, IMPORT, CRM_UI)"
79
      sourceId:
80
        type: string
81
        description: "Identifier of the change source"
82
      sourceLabel:
83
        type: string
84
        description: "Human-readable source description"
85
      timestamp:
86
        type: string
87
        format: date-time
88
        description: "When the property change occurred"
89
      updatedByUserId:
90
        type: string
91
        description: "ID of user who made the change"
92
```
93

94
### Deals Property History
95

96
Historical changes to deal properties with timestamps and change details.
97

98
```yaml { .api }
99
deals_property_history:
100
  primary_key: ["dealId", "property", "timestamp"]
101
  cursor_field: "timestamp"
102
  sync_mode: incremental
103
  schema:
104
    type: object
105
    properties:
106
      dealId:
107
        type: string
108
        description: "Deal ID"
109
      property:
110
        type: string
111
        description: "Name of the property that changed"
112
      value:
113
        type: string
114
        description: "New property value"
115
      sourceType:
116
        type: string
117
        description: "Source of the change (e.g., API, IMPORT, CRM_UI)"
118
      sourceId:
119
        type: string
120
        description: "Identifier of the change source"
121
      sourceLabel:
122
        type: string
123
        description: "Human-readable source description"
124
      timestamp:
125
        type: string
126
        format: date-time
127
        description: "When the property change occurred"
128
      updatedByUserId:
129
        type: string
130
        description: "ID of user who made the change"
131
```
132

133
## Property History Extraction
134

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

137
### Custom Extraction Logic
138

139
```yaml { .api }
140
HubspotPropertyHistoryExtractor:
141
  type: RecordExtractor
142
  field_path: ["results"]
143
  entity_primary_key: string    # Entity ID field name (vid, companyId, dealId)
144
  additional_keys: array        # Additional fields to include from parent record
145
  
146
  # Extraction behavior:
147
  # 1. Iterates over each entity in results array
148
  # 2. Extracts propertiesWithHistory object for each entity
149
  # 3. Creates individual records for each property version
150
  # 4. Injects entity ID and timestamp into each property history record
151
  # 5. Skips hs_lastmodifieddate to avoid duplicate change tracking
152
```
153

154
### Property History Record Structure
155

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

158
```yaml { .api }
159
PropertyHistoryRecord:
160
  type: object
161
  properties:
162
    # Entity identifier (varies by stream)
163
    vid: string              # For contacts_property_history
164
    companyId: string        # For companies_property_history  
165
    dealId: string           # For deals_property_history
166
    
167
    # Property change details
168
    property: string         # Property name that changed
169
    value: string           # New property value
170
    timestamp: string       # When change occurred (ISO format)
171
    sourceType: string      # Change source type
172
    sourceId: string        # Source identifier
173
    sourceLabel: string     # Human-readable source
174
    updatedByUserId: string # User who made change
175
```
176

177
### Timestamp Handling
178

179
Property history streams handle multiple timestamp formats from HubSpot:
180

181
- **Millisecond timestamps**: Converted to ISO 8601 format
182
- **Second timestamps**: Converted to ISO 8601 format  
183
- **ISO strings**: Used directly
184
- **Invalid timestamps**: Logged and original value preserved
185

186
### Source Types
187

188
Common `sourceType` values include:
189

190
- `API`: Changes made via API calls
191
- `CRM_UI`: Changes made through HubSpot interface
192
- `IMPORT`: Changes from data imports
193
- `AUTOMATION`: Changes from workflows/automation
194
- `CALCULATED`: Changes from calculated properties
195
- `MIGRATION`: Changes from data migrations
196

197
### Usage Patterns
198

199
**Audit Trail Analysis:**
200
```sql
201
SELECT 
202
  vid as contact_id,
203
  property,
204
  value,
205
  timestamp,
206
  sourceType,
207
  updatedByUserId
208
FROM contacts_property_history 
209
WHERE property = 'lifecyclestage'
210
ORDER BY timestamp DESC;
211
```
212

213
**Property Change Frequency:**
214
```sql
215
SELECT 
216
  property,
217
  COUNT(*) as change_count,
218
  COUNT(DISTINCT companyId) as companies_affected
219
FROM companies_property_history
220
WHERE timestamp >= '2024-01-01'
221
GROUP BY property
222
ORDER BY change_count DESC;
223
```
224

225
**User Activity Tracking:**
226
```sql
227
SELECT 
228
  updatedByUserId,
229
  COUNT(*) as changes_made,
230
  MIN(timestamp) as first_change,
231
  MAX(timestamp) as last_change
232
FROM deals_property_history
233
WHERE sourceType = 'CRM_UI'
234
GROUP BY updatedByUserId;
235
```
236

237
## State Migration
238

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

241
```yaml { .api }
242
MigrateEmptyStringState:
243
  type: StateMigration
244
  cursor_field: "timestamp"
245
  cursor_format: "%ms"        # Millisecond timestamp format
246
  
247
  # Migration logic:
248
  # - If cursor_field is empty string, migrate to start_date
249
  # - Convert start_date to appropriate timestamp format
250
  # - Default to "2006-06-01T00:00:00.000Z" if no start_date
251
```
252

253
## Performance Considerations
254

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