Data Mapping & Transformation

When syncing data between Otesse and a third-party provider, field names and formats rarely match perfectly. Otesse calls it firstName while Stripe calls it name. Otesse stores dates as ISO strings while QuickBooks uses MM/DD/YYYY. Field mappings and transformations bridge these differences, ensuring data flows correctly between systems.

Field Mapping Structure

Each sync job has one or more FieldMapping records that define the entity-level relationship:

Field	Description
Local Entity Type	The Otesse entity (e.g., Customer, Invoice, Product)
External Entity Type	The provider entity (e.g., stripe.customer, quickbooks.customer)
Sync Direction	Inbound, outbound, or bidirectional (can override the job-level direction)
Conflict Resolution	Strategy for handling bidirectional conflicts
Enabled	Whether this mapping is active

Within each field mapping, individual FieldMappingRule records define the field-level translations:

Field	Description
Local Field Path	Dot-notation path to the Otesse field (e.g., firstName, address.city)
External Field Path	Dot-notation path to the provider field (e.g., name, billing_address.city)
Transform Type	How to convert the value (see Transform Types below)
Transform Config	JSON configuration for the transform
Default Value	Value to use if the source field is null
Required	If true, the entire record fails if this field is missing
Sort Order	Processing order (important when one transform depends on another)

Transform Types

The sync engine supports nine transform types, from simple direct copies to custom expressions:

Direct Copy

The value is copied as-is with no modification. This is the default when no transform is specified.

Config	None
Example	email to email — the value passes through unchanged
Use when	Field names differ but the data format is identical

Format String

Apply a format pattern that combines multiple source fields:

Config	{ "pattern": "{firstName} {lastName}" }
Example	Combine firstName and lastName into a single name field
Use when	The target system uses a combined field that the source splits

Value Mapping

Map discrete values between systems using a lookup table:

Config	{ "mappings": { "active": "ACTIVE", "inactive": "CLOSED" } }
Example	Otesse uses "active"/"inactive" while the provider uses "ACTIVE"/"CLOSED"
Use when	Systems use different codes or labels for the same concept

Date Format

Convert between date formats:

Config	{ "sourceFormat": "YYYY-MM-DD", "targetFormat": "MM/DD/YYYY" }
Example	ISO date to US date format
Use when	Systems use different date representations

Currency Conversion

Convert currency amounts between currencies:

Config	{ "sourceCurrency": "USD", "targetCurrency": "EUR" }
Example	Convert USD amounts to EUR
Use when	Syncing financial data between systems operating in different currencies

Concatenation

Combine multiple source fields into one:

Config	{ "fields": ["firstName", "lastName"], "separator": " " }
Example	"Nathaniel" + " " + "Maddox" = "Nathaniel Maddox"
Use when	Multiple source fields need to merge into one target field

Split

Split a single field into multiple parts:

Config	{ "delimiter": " ", "index": 0 }
Example	Split "Nathaniel Maddox" by space, take index 0 = "Nathaniel"
Use when	The source has a combined field that the target needs split

Lookup

Map values using an ExternalReference lookup:

Config	{ "referenceType": "customer_id" }
Example	Convert a local customer ID to the provider's customer ID using stored references
Use when	Syncing records that reference other entities (e.g., an invoice references a customer)

Custom Expression

User-defined transformation logic:

Config	{ "expression": "value.toUpperCase()" }
Example	Convert any string value to uppercase
Use when	None of the built-in transforms handle the specific conversion needed

Custom expressions are sandboxed and have access only to the current field value. They cannot access other fields, make API calls, or modify state.

Conflict Resolution Strategies

When syncing bidirectionally, the same record may be modified on both sides between sync runs. Conflict resolution determines which version wins:

Strategy	Behavior
Last Write Wins	The record with the most recent updatedAt timestamp overwrites the other
Source Wins	The source system (determined by processing order) always overwrites the target
Target Wins	The target system's version is preserved. Source changes are discarded
Manual	Conflicting records are flagged for user review. Neither side is overwritten until resolved

For inbound or outbound-only sync, the default is "source wins" — the sending system overwrites the receiving system. Conflict resolution is only relevant for bidirectional sync.

External References

The ExternalReference table links local Otesse entities to their counterparts at each provider:

Field	Description
Local Entity ID	The Otesse record ID
Local Entity Type	The Otesse entity type (e.g., "Customer")
Provider Reference ID	The provider's record ID
Provider Reference Type	The provider's entity type (e.g., "stripe.customer")
Integration ID	Which integration instance this reference belongs to
Sync Status	Current status: synced, stale, or conflict

These references are created during initial sync (when a record is first synced) and updated on subsequent syncs. When an integration is disconnected, references are marked as "stale" but preserved — if the integration is reconnected, they can be revalidated and reused.

Best Practices

1. Map required fields first — Ensure all fields marked as required by the target system have mappings configured
2. Use default values — For optional fields that may be null in the source, set sensible defaults to prevent validation errors
3. Test with a small batch — Run a full sync with a limited record set before enabling scheduled incremental sync
4. Review transform output — Check the first few synced records manually to verify transforms are producing the expected results
5. Keep mappings up to date — When a provider updates their API schema, review and update your field mappings accordingly