One of our clients, a software services company, had run an off-the-shelf Salesforce–NetSuite connector for several years when we were brought in to replace it. By then, customer names, invoice statuses and billing contacts no longer agreed between the two systems, and finance kept a spreadsheet of the accounts it actually trusted. The connector did broad two-way updates with no notion of who owned which field, so an account edit in Salesforce would sometimes overwrite the legal billing name in NetSuite. Invoice status came back to Salesforce only overnight, and now and then that job failed without raising an exception anybody saw.
None of those problems shows up as an error in a log. What the connector couldn't do was guarantee that a record and its counterpart described the same customer, in the same state, with each field coming from the system that owned it. I think most CRM-to-ERP integrations fail this way. They rarely crash; they drift, and an accountant usually finds the drift months later.
Below I rebuild it in code, from field ownership and external IDs through a retry-safe queue and NetSuite's concurrency limits to a reconciliation report. The code is Python 3.11 with httpx and psycopg 3, keeps its state in PostgreSQL 14 or later, and assumes authenticated httpx clients for both APIs.
Field ownership is data, and the code should enforce it
Start by writing down which system owns each field that crosses the boundary. On that client's project, NetSuite owned the legal entity, billing terms, invoice balances and payment status, and Salesforce owned segmentation, opportunities and account-manager assignments. That decision is worth something only if the code enforces it, so every payload is built from a table of rules.
from collections.abc import Callable from dataclasses import dataclass from enum import Enum from typing import Any class Side(Enum): SALESFORCE = "salesforce" NETSUITE = "netsuite" @dataclass(frozen=True) class FieldRule: sf_field: str ns_field: str owner: Side convert: Callable[[Any], Any] = lambda v: v def ref_name(v: dict | None) -> str | None: return v["refName"] if v else None # NetSuite returns references as {id, refName} CUSTOMER_RULES = ( FieldRule("Segment__c", "custentity_segment", Side.SALESFORCE), FieldRule("Account_Manager_Email__c", "custentity_acct_mgr_email", Side.SALESFORCE), FieldRule("Legal_Name__c", "companyName", Side.NETSUITE), FieldRule("Payment_Terms__c", "terms", Side.NETSUITE, ref_name), FieldRule("Credit_Limit__c", "creditLimit", Side.NETSUITE), ) def outbound(rules: tuple[FieldRule, ...], source: Side, record: dict[str, Any]) -> dict[str, Any]: """Payload for the other system, built only from fields the source owns.""" if source is Side.SALESFORCE: return {r.ns_field: r.convert(record[r.sf_field]) for r in rules if r.owner is source and r.sf_field in record} return {r.sf_field: r.convert(record[r.ns_field]) for r in rules if r.owner is source and r.ns_field in record} The field names are illustrative. A Salesforce change can only produce a payload of Salesforce-owned fields, so no CRM rename can reach NetSuite's companyName. The Salesforce account name isn't mapped at all; sales can call the customer whatever it likes, as long as the legal name lives in its own field that NetSuite fills. I make those mirrored fields read-only in Salesforce with field-level security.
On that project, the damage came from an ordinary edit. A sales rep changed an account's name in Salesforce from the registered legal entity to the shorter trading name the customer used on its purchase orders, and the old connector, treating the edit as a master-data update, wrote it into NetSuite's company name field. Finance found out when a customer asked why a new invoice carried the trading name and not the entity that had signed the contract. An audit of the connector's change history turned up other accounts with the same problem. We restored the legal names from the approved customer master and kept the CRM's account name and finance's legal name apart from then on.
External IDs on both sides, and a cross-reference you own
Matching on names eventually turns two spellings into two customers, so every record that crosses the boundary carries the other system's identifier. In NetSuite, the customer's external ID derives from the Salesforce account ID, as in sfacct-001.... NetSuite's REST web services accept an external ID, written eid:<value>, anywhere a URL would take an internal ID. In Salesforce, records from NetSuite are keyed by a custom External ID field holding the NetSuite internal ID. The integration also keeps its own cross-reference.
CREATE TABLE account_xref ( sf_account_id char(18) PRIMARY KEY, ns_external_id text NOT NULL UNIQUE, -- 'sfacct-' || sf_account_id ns_customer_id bigint UNIQUE, -- filled once NetSuite has the record ns_subsidiary int, match_status text NOT NULL DEFAULT 'linked' CHECK (match_status IN ('linked', 'needs_review', 'retired')), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE sync_state ( target text NOT NULL CHECK (target IN ('netsuite', 'salesforce')), record_type text NOT NULL, -- 'customer', 'salesOrder', 'NetSuite_Invoice__c' external_id text NOT NULL, payload jsonb NOT NULL, -- latest desired state for this record version bigint NOT NULL DEFAULT 1, sent_version bigint NOT NULL DEFAULT 0, attempts int NOT NULL DEFAULT 0, next_try_at timestamptz NOT NULL DEFAULT now(), lease_until timestamptz, status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'exception')), last_error text, PRIMARY KEY (target, record_type, external_id) ); CREATE INDEX sync_state_due ON sync_state (next_try_at) WHERE status = 'active' AND version > sent_version; The needs_review status is the exception queue for matching: when an existing NetSuite customer might match a Salesforce account but nothing proves it, the row waits for a person. On that project, subsidiaries and bill-to entities needed rules of their own, which is why the subsidiary sits on the cross-reference row instead of being inferred at write time.
The distinction that mattered was between a commercial account and a bill-to customer. A national customer could appear in Salesforce as one parent account with regional service sites beneath it, while its eastern and western operations were invoiced by two different legal subsidiaries. In NetSuite those were two customer records, each with its own internal ID, terms and receivables. So the Salesforce parent stayed a relationship record, each billing entity or site mapped to the right subsidiary-specific customer, and every order had to name its bill-to explicitly. Nothing inferred the subsidiary from the parent account, and a change on the parent could never move a bill-to. An order without a valid mapping was held for review. The account_xref table above covers the simple one-to-one case; an arrangement like this one needs a separate bill-to mapping keyed by Salesforce account or site and NetSuite subsidiary.
A queue that holds one desired state per record
The second table, sync_state, is the work queue. Unlike the usual one-row-per-event outbox, it holds one row per target record, with the latest desired payload and a version number. Ten edits to one account in five minutes produce one write to NetSuite, which matters when every request counts against a shared limit.
-- enqueue: called whenever a source record changes INSERT INTO sync_state (target, record_type, external_id, payload) VALUES (%(target)s, %(record_type)s, %(external_id)s, %(payload)s) ON CONFLICT (target, record_type, external_id) DO UPDATE SET payload = EXCLUDED.payload, version = sync_state.version + 1, status = 'active', attempts = 0, next_try_at = now() WHERE sync_state.payload IS DISTINCT FROM EXCLUDED.payload; -- claim: a batch of due rows, leased so no other worker takes them WITH due AS ( SELECT target, record_type, external_id FROM sync_state WHERE status = 'active' AND version > sent_version AND next_try_at <= now() AND (lease_until IS NULL OR lease_until < now()) ORDER BY next_try_at LIMIT %(batch)s FOR UPDATE SKIP LOCKED ) UPDATE sync_state s SET lease_until = now() + interval '5 minutes', attempts = s.attempts + 1 FROM due WHERE (s.target, s.record_type, s.external_id) = (due.target, due.record_type, due.external_id) RETURNING s.target, s.record_type, s.external_id, s.payload, s.version, s.attempts; -- done: record the version we sent; a newer edit leaves the row due again UPDATE sync_state SET sent_version = %(version)s, lease_until = NULL, attempts = 0, last_error = NULL WHERE (target, record_type, external_id) = (%(target)s, %(record_type)s, %(external_id)s); The IS DISTINCT FROM condition absorbs echoes: when the integration writes NetSuite's terms onto a Salesforce account, the resulting change carries an unchanged payload, so nothing is sent.
One caveat: if a worker stalls past its lease, another can send a newer version, and the stalled request might land afterward with older data. Keep the lease well above the HTTP timeout, and let reconciliation catch the rare leftover.
Upserts make a retry harmless
The classic duplicate comes from a timeout: NetSuite creates the record, the response never arrives, the integration retries, and now there are two. Upserting by external ID fixes that, because the retry finds the record the first request created.
NetSuite's REST record API upserts when you PUT to a URL that uses an external ID, such as /record/v1/customer/eid:sfacct-001..., and a successful call returns 204 No Content. Salesforce does the same with a PATCH to /sobjects/{object}/{externalIdField}/{value}: it creates the record and returns 201 when nothing matches, updates it when exactly one matches, and returns 300 without changing anything when several match.
import httpx SF_API = "/services/data/v62.0" class Retryable(Exception): """Safe to retry: the write is an upsert.""" class NeedsReview(Exception): """Needs a person before it is sent again.""" async def ns_upsert(ns: httpx.AsyncClient, record_type: str, eid: str, body: dict) -> None: # ns.base_url is https://<account>.suitetalk.api.netsuite.com/services/rest r = await ns.put(f"/record/v1/{record_type}/eid:{eid}", json=body) if r.status_code == 204: return if r.status_code == 429 or r.status_code >= 500: raise Retryable(f"netsuite {r.status_code}: {r.text[:300]}") raise NeedsReview(f"netsuite {r.status_code}: {r.text[:1000]}") async def sf_upsert(sf: httpx.AsyncClient, sobject: str, ext_field: str, ext_value: str, body: dict) -> None: r = await sf.patch(f"{SF_API}/sobjects/{sobject}/{ext_field}/{ext_value}", json=body) if r.status_code in (200, 201, 204): return if r.status_code == 300: raise NeedsReview(f"{sobject}.{ext_field}={ext_value} matches several records") if r.status_code >= 500: raise Retryable(f"salesforce {r.status_code}: {r.text[:300]}") raise NeedsReview(f"salesforce {r.status_code}: {r.text[:1000]}") Transactions work the same way. A sales order from a closed-won opportunity gets an external ID derived from the opportunity ID, so one opportunity produces one order however often the message arrives.
Oracle documents omitted fields as unchanged only for PATCH, so test upserts in a sandbox, especially with sublists.
Staying inside NetSuite's concurrency limit
NetSuite governs concurrency per account, and the limit covers web services and RESTlet requests together, so you share it with every other connector the client runs. A request over the limit is rejected with HTTP 429 and the error code CONCURRENCY_LIMIT_EXCEEDED. The limit depends on service tier and SuiteCloud Plus licenses and is shown under Setup > Integration > Integration Governance; take only a share.
import asyncio import random import psycopg from psycopg.rows import dict_row GATES = {"netsuite": asyncio.Semaphore(4), "salesforce": asyncio.Semaphore(8)} SF_EXTERNAL_ID_FIELD = {"NetSuite_Invoice__c": "NS_Invoice_Id__c"} MAX_ATTEMPTS = 12 async def push(job: dict, ns: httpx.AsyncClient, sf: httpx.AsyncClient) -> None: async with GATES[job["target"]]: if job["target"] == "netsuite": await ns_upsert(ns, job["record_type"], job["external_id"], job["payload"]) else: field = SF_EXTERNAL_ID_FIELD[job["record_type"]] await sf_upsert(sf, job["record_type"], field, job["external_id"], job["payload"]) def backoff_seconds(attempts: int) -> float: return min(900, 2 ** attempts) * random.uniform(0.5, 1.0) # capped, with jitter async def run_batch(db: psycopg.AsyncConnection, ns, sf) -> int: async with db.transaction(): cur = await db.cursor(row_factory=dict_row).execute(CLAIM_SQL, {"batch": 50}) jobs = await cur.fetchall() results = await asyncio.gather(*(push(j, ns, sf) for j in jobs), return_exceptions=True) async with db.transaction(): for job, res in zip(jobs, results): if res is None: await db.execute(DONE_SQL, job) elif isinstance(res, (Retryable, httpx.TransportError)) and job["attempts"] < MAX_ATTEMPTS: await db.execute(RETRY_SQL, {**job, "delay": backoff_seconds(job["attempts"]), "err": str(res)}) else: await db.execute(PARK_SQL, {**job, "err": repr(res)}) return len(jobs) RETRY_SQL clears the lease and sets next_try_at = now() + delay; PARK_SQL sets status = 'exception' and keeps the error. httpx.TransportError covers timeouts and dropped connections, which the upsert makes safe to repeat. The semaphore of four is a deliberate under-use, leaving room at month-end for whatever other connectors and scheduled scripts the client runs. The semaphore is per process, so run one worker per target, and alert when 429 responses climb.
Pulling changes with SuiteQL and a durable checkpoint
Reading NetSuite records one at a time wastes requests. A SuiteQL query through the REST query/v1/suiteql resource, sent with Prefer: transient, returns up to a thousand rows per page. I page with a keyset on the last-modified timestamp and ID, keeping the offset at zero, because rows that change mid-paging move to the end of the ordering and offset paging would silently skip others.
from datetime import datetime INVOICE_SQL = """ SELECT t.id, t.tranid, t.entity, BUILTIN.DF(t.status) AS status, t.foreigntotal, TO_CHAR(t.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS modified FROM transaction t WHERE t.type = 'CustInvc' AND (t.lastmodifieddate > TO_DATE('{ts}', 'YYYY-MM-DD HH24:MI:SS') OR (t.lastmodifieddate = TO_DATE('{ts}', 'YYYY-MM-DD HH24:MI:SS') AND t.id > {last_id})) ORDER BY t.lastmodifieddate, t.id """ async def pull_invoices(ns: httpx.AsyncClient, db: psycopg.AsyncConnection) -> int: pulled = 0 while True: ts, last_id = await load_checkpoint(db, "ns_invoices") datetime.strptime(ts, "%Y-%m-%d %H:%M:%S") # refuse anything that isn't a timestamp r = await ns.post("/query/v1/suiteql", params={"limit": 1000, "offset": 0}, headers={"<...