Business Systems Integration

Salesforce integration uses REST API v62 for standard CRUD operations and Bulk API 2.0 for initial data loads and high-volume syncs (parallel batches of up to 100M records). Real-time triggers use Salesforce Change Data Capture (CDC): subscribing to /data/AccountChangeEvent and /data/OpportunityChangeEvent via the Streaming API (CometD protocol) delivers change events within 3-5 seconds of a record update. For deal-closed triggers, we subscribe to Opportunity.StageName = 'Closed Won' changes, the ERP record creation starts immediately, not on a polling interval. Salesforce API consumption is tracked against the 15,000 daily API call limit on standard orgs; bulk operations are scheduled in off-peak windows to preserve headroom for interactive users.

HubSpot CRM API v3 with the Associations API for contact-company-deal relationship mapping. A deal in HubSpot has an associated contact and company; the ERP order needs account_id and contact_id from both. HubSpot webhooks (deal.propertyChange, contact.propertyChange) deliver changes via HTTP POST within seconds. The pipeline stage-to-ERP-order-status mapping is stored in a configuration table, not hardcoded stage names, because every HubSpot instance uses different pipeline stage labels. NetSuite integration uses the REST API with SuiteQL for complex queries (SELECT * FROM SalesOrder WHERE lastModifiedDate >= :cursor AND status = 'SalesOrder:A') authenticated via TBA (Token-Based Authentication) with HMAC-SHA256. NetSuite has no native webhook mechanism; we poll on a 60-120 second interval using a lastModifiedDate cursor so only changed records are fetched. SAP S/4HANA integration uses OData v4 services (Business Partner API, Sales Order API) with CDS Event Mesh for event-based triggers, events published to the event mesh when a business partner is created or an order is confirmed, consumed via AMQP 1.0. On-premise SAP ECC uses RFC/BAPI via the JCo Java Connector.

Data model mapping: CRM "contact" maps to ERP "individual customer" or "person account" depending on B2B vs. B2C context. CRM "deal value" requires currency normalisation when the CRM stores amounts in the salesperson's local currency and the ERP books revenue in functional currency. Custom fields in both systems are mapped via a configuration table (not hardcoded) so the mapping updates without a code deployment when either system adds a field. Conflict resolution is configured per field: last-write-wins timestamp for infrequently-updated fields (phone, address); source-of-truth designation for fields owned by one system (credit limit owned by ERP, deal owner owned by CRM, the other system is always overwritten); manual conflict queue for fields where automatic resolution is too risky (customer legal name, billing address when both systems claim a recent update).

Shopify integration uses the Admin REST API (2024-01) and GraphQL Admin API with registered webhooks for orders/create, orders/updated, orders/fulfillments/create, and inventory_levels/update. Webhook payloads are verified using HMAC-SHA256 with the X-Shopify-Hmac-SHA256 header before processing. For multi-location inventory, the Shopify Inventory API tracks stock at the location level (inventory_item_id + location_id), a warehouse pick triggers an inventory_levels/update webhook that updates Shopify inventory within 2-3 seconds, preventing oversells. Shopify GraphQL bulk operations handle historical order imports and large inventory reconciliations without hitting the 2 request/second REST rate limit. For Shopify Plus multi-store setups, each store authenticates independently and order fulfilment events route to the correct warehouse by store ID.

WooCommerce integration uses REST API v3 with webhook subscriptions (woocommerce_order_status_changed, woocommerce_new_order) registered via the Webhook API, not the WP admin UI, which breaks during plugin updates. Stock updates push via the Products API and Product Variations API for variable products. Magento integration uses REST API v1 with async bulk endpoints for high-volume operations (POST /async/bulk/V1/products for batch inventory updates, up to 100 records per request), returning a bulk_uuid for polling the async operation result.

Order fulfilment flow: a new storefront order creates a record in the WMS via API within 30 seconds of the orders/create webhook. The WMS assigns the order to pick-and-pack, the picker scans items to confirm picks, the system generates a shipping label via the carrier API (EasyPost, ShipStation, or direct UPS/FedEx/DHL APIs), the carrier tracking number is written back to the storefront order via API, and a shipment confirmation email fires from the storefront. Standard in-stock orders run this flow end-to-end without human involvement. Backordered or partially-fulfillable orders route to a WMS exception queue rather than triggering a partial fulfilment that confuses the customer.

3PL integration: ShipBob API (REST, OAuth 2.0) sends orders via POST /order and receives fulfilment webhooks for shipped and cancelled events with inventory sync via GET /inventory. ShipStation API (REST, Basic Auth) uses POST /orders for order creation and GET /shipments?orderNumber=X for fulfilment confirmation. For 3PLs without API access, EDI 850 (purchase order) and EDI 856 (advance ship notice) integrations via SFTP with the X12 transaction set format. Return/RMA integration: a return request in the storefront triggers a WMS return receipt workflow; upon goods-received confirmation, the integration calls the Shopify Refunds API or WooCommerce refund endpoint to process the customer refund automatically, no manual refund step.

Stripe webhook events drive accounting entries via registered endpoints with HMAC-SHA256 Stripe-Signature header verification. The events that matter: payment_intent.succeeded (revenue recognition), invoice.paid (subscription revenue), invoice.payment_failed (dunning trigger), customer.subscription.updated (plan change requiring proration entry), customer.subscription.deleted (churn, subscription deactivation), charge.refunded (revenue reversal), charge.dispute.created (chargeback entry). Each event maps to specific double-entry accounting records: a payment_intent.succeeded with amount: 10000 (Stripe stores amounts in cents) maps to debit accounts receivable + credit revenue, split by product line when metadata.product_line is present in the Payment Intent. Stripe balance_transactions API provides net amounts after Stripe processing fees for the accounts payable entry recording the processing cost.

QuickBooks Online integration uses the QBO REST API v3 with OAuth 2.0 refresh token rotation (tokens expire every 60 minutes; proactive refresh 5 minutes before expiry via a background job with the new token stored in Redis). For payment-to-invoice reconciliation, a payment_intent.succeeded event creates a QBO Payment object linked to the corresponding Invoice by the invoice number stored in Stripe Payment Intent metadata. For new customers with no existing QBO invoice, the integration creates the Customer record, Invoice, and Payment in a single transaction, all three succeed or none are created, preventing orphaned partial records. Xero API v2 with OAuth 2.0 PKCE for multi-entity organisations (each Xero entity authenticates separately). The Xero Account (chart of accounts) code for each revenue line is stored in a mapping table, when finance adds a new revenue code in Xero, the mapping table updates without touching integration code.

Multi-currency: Stripe amounts are in the payment currency; Xero and QBO need the functional currency equivalent at the transaction exchange rate. The integration fetches the ECB (European Central Bank) daily reference rate for the transaction date via the ECB XML feed and stores it in a rates table, the rate used for a specific transaction is always retrievable for audit. Subscription billing complexity: Stripe billing cycle events require proration handling when a customer upgrades mid-cycle. The invoice.upcoming webhook provides the proration preview before finalisation, the integration creates a draft Xero/QBO invoice for finance review before the charge is made. Usage-based billing: Stripe Billing Meters API reports metered usage per subscription item; the integration maps meter event names to QBO/Xero product/service codes so usage charges are categorised correctly.

Payment failure and dunning: invoice.payment_failed triggers a QBO/Xero overdue invoice marker, a CRM activity log entry (payment failed), and the email dunning sequence via SendGrid (24h first reminder, 72h second, 7-day final notice). invoice.paid after a prior failure closes the dunning sequence and updates the accounting record. No human needs to monitor Stripe for failed charges or manually initiate dunning outreach.

Zendesk integration uses REST API v2 with incremental exports (GET /api/v2/incremental/tickets?start_time={unix_timestamp}) for efficient bulk sync, incremental exports return only records changed since the cursor timestamp, reducing API consumption by 90% compared to full scans. Zendesk webhooks (ticket.created, ticket.updated, ticket.solved, ticket.comment.created) are registered via the Webhooks API and signed with HMAC-SHA256. The Zendesk Apps Framework (ZAF) powers a Customer 360 sidebar inside the Zendesk agent UI: when a ticket is opened, the sidebar makes a single call to an internal API that aggregates subscription tier and MRR from Stripe, open orders from the ERP, and recent CRM interactions from Salesforce or HubSpot, the complete customer context displayed to the agent without switching applications or asking the customer to repeat account details. Sidebar data is fetched fresh on every ticket open to show current payment status, not a stale snapshot from a periodic sync.

ServiceNow integration uses the Table API (/api/now/table/incident) with sys_updated_on polling for environments without webhook support. ServiceNow Orlando+ instances use IntegrationHub event subscriptions for push-based notifications. Custom field mapping is handled dynamically via ServiceNow's sys_dictionary configuration table rather than hardcoded field names that vary across instances. For escalated product issues, a helpdesk ticket classified as "product defect" creates a ServiceNow Incident with the customer's contract tier in the business_criticality field, the ITSM team knows the ticket is from an enterprise customer before reading the description.

Freshdesk v2 REST API with webhook event subscriptions for all ticket lifecycle events. The Freshdesk-to-CRM sync maps requester_id to a CRM contact by email, when a contact is found, ticket creation logs a CRM activity with ticket ID, subject, and URL linked. When no CRM contact is found, the integration creates a new CRM contact flagged as "created from support ticket" for sales team follow-up. This ensures the CRM contains a complete support history even for customers who contacted support before appearing in the CRM.

Jira Service Management REST API v3 for issue creation with configurable field mapping: helpdesk priority → Jira priority, helpdesk affected_feature → Jira labels, customer tier → Jira customfield_customer_tier. When a helpdesk ticket meets escalation criteria (3+ customers reporting the same issue, or an enterprise customer reporting a P1 bug), the integration creates a Jira issue automatically with helpdesk ticket URLs attached as remote issue links. When the Jira issue resolves, the integration posts a comment on all linked helpdesk tickets notifying customers of the fix. SLA breach alerting: when a helpdesk ticket is within 30 minutes of breaching its first-response or resolution SLA, a Slack alert fires to the support team lead with the ticket link and the time remaining.

ELT architecture: data is extracted from source systems and loaded raw into the warehouse staging layer first, then transformed in the warehouse using dbt. This preserves raw source data for reprocessing, keeps transformation logic version-controlled in dbt rather than in extraction scripts, and allows transformations to run independently of extraction, a transformation error does not require re-extracting from the source. Snowflake Snowpipe (continuous loading from S3/GCS via SQS notifications) handles near-real-time event stream ingestion; standard COPY INTO handles scheduled batch loads. BigQuery uses the Storage Write API for high-throughput inserts (millions of rows per minute) and the BigQuery Data Transfer Service for native Salesforce and Google Ads connectors. Amazon Redshift uses COPY from S3 with compressed Parquet files, Parquet is typically 60-70% smaller than CSV for the same data, reducing load time and warehouse storage costs.

Extraction patterns by source: Salesforce via Bulk API 2.0 with SOQL cursor pagination (LastModifiedDate > :cursor); HubSpot via Objects API v3 with since filter; Stripe via Events API with created[gt] cursor; Shopify via GraphQL Admin API with updatedAt cursor (REST API doesn't support cursor pagination consistently across object types); custom databases via CDC using Debezium (MySQL binlog ROW format, PostgreSQL logical replication with pgoutput plugin) for sub-minute change propagation without polling. For sources without CDC or a reliable updated_at timestamp, nightly full reloads with deduplication in the warehouse staging layer. Airbyte connectors are used for sources with maintained community connectors; custom extraction is built only where connectors don't exist or don't support the required pattern.

dbt model layers: staging cleans raw source data (column renames to snake_case, data type casts, primary key deduplication); intermediate applies business logic (order line totals, contact-company joins, MRR calculations from Stripe events); mart produces analytics-ready datasets that BI tools query directly (mart_revenue joining Stripe, QBO, and Shopify; mart_customer_health joining CRM, support ticket counts, and product usage). dbt tests validate model output: not_null on primary keys, unique on join keys, accepted_values on status fields, and custom SQL tests for business rules. Great Expectations data quality checks run on raw ingested data before dbt models execute, row count within expected range, column completeness above 99%, referential integrity between facts and dimensions. Failed checks block the dbt run and alert via Slack before the bad data reaches any dashboard.

BI tool connectivity: Looker connects via persistent derived tables (PDTs) with LookML explore definitions ensuring all teams use identical metric definitions (MRR, ARR, churn rate) rather than each analyst computing their own version. Tableau connects via native Snowflake/BigQuery connectors with incremental extract refresh (every 15 minutes for operational dashboards, every 24 hours for strategic reports). Power BI uses Direct Query mode against Redshift/Snowflake for live dashboards or Import mode for smaller datasets requiring faster load. Metabase uses native connectors pointed at the dbt mart layer, the mart tables are designed so Metabase's SQL-free question builder constructs meaningful queries without analysts writing JOIN logic. Data freshness SLA: operational dashboards (sales pipeline, support queue, fulfilment status) require data no older than 1 hour; strategic dashboards (monthly revenue trends, churn analysis) tolerate 24-hour lag.

Authentication handling covers every pattern encountered in production integration work: OAuth 2.0 authorization code flow with PKCE (RFC 7636) for public clients where client_secret cannot be stored securely; client credentials flow for machine-to-machine integrations; token refresh rotation with Redis storing the access token, expiry, and refresh token with a mutex lock to prevent concurrent refresh races (two parallel calls both finding an expired token and both trying to refresh, invalidating one mid-flight); API key rotation support (the integration operates with old and new keys during rotation windows without downtime); SAML 2.0 for enterprise SSO-gated systems; session-based authentication for legacy web systems where a login POST establishes a session cookie that must be maintained across requests. Token expiry is detected proactively (5 minutes before expiry) rather than reactively (after receiving a 401), proactive refresh eliminates the latency spike caused by token expiry mid-workflow execution.

Rate limit management: each integration is configured with a rate limit budget (requests per second, requests per day, concurrent request limit) tracked via a token bucket counter in Redis. When a request approaches the budget ceiling (within 20% of the daily limit), subsequent requests are queued rather than sent immediately. When a 429 is received despite proactive management, the integration extracts the Retry-After header and waits exactly the specified duration before retrying, not a fixed backoff that may be shorter or longer than the API requires. For APIs without Retry-After, exponential backoff: 1s, 2s, 4s, 8s, 16s, 32s, then permanent failure to DLQ. The rate limit consumption per integration is visible in the monitoring dashboard, showing current usage as a percentage of the daily budget so operations teams can see if business activity spikes are approaching the Salesforce API budget ceiling before it becomes a hard failure.

Screen scraping for legacy systems without APIs: Playwright-based automation with browser fingerprint randomisation (user-agent rotation, viewport variation, JavaScript timing randomisation) for government portals and legacy vendor portals with anti-bot detection. Cookie session management handles multi-page authentication flows (login form → MFA prompt → dashboard → data export page). DOM selectors prioritise stable attributes (data-testid, aria-label, form name attributes) over brittle positional CSS selectors, the former survives UI redesigns, the latter breaks silently. Extracted data is validated against an expected schema before downstream processing, so a portal layout change that disrupts extraction triggers an alert rather than silently producing malformed data.

File-based integration for systems with no API and no web interface: SFTP polling with SHA-256 file fingerprinting to prevent reprocessing files already ingested even if the same file is uploaded multiple times. EDI transaction sets, 850 (purchase order), 810 (invoice), 856 (advance ship notice), 997 (functional acknowledgement), parsed with an X12 EDI library and transformed to JSON before downstream routing. XML exports from proprietary ERPs validated against XSD schema before processing, malformed XML rejects with a specific validation error rather than parsing with silent field omission. Integration health monitoring per data source: last successful file received timestamp, file count and row count per batch (deviation from expected range triggers alert), record acceptance rate, and per-field null rate tracking to detect when a source system starts exporting empty values for previously-populated fields.