DEV Community

Jayant Chaudhary for eProxim

Posted on AI-assisted

A Salesforce–NetSuite Sync in Python That Proves the Two Systems Agree

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}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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]}")
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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={"Prefer": "transient"},
                          json={"q": INVOICE_SQL.format(ts=ts, last_id=int(last_id))})
        r.raise_for_status()
        page = r.json()
        if not page["items"]:
            return pulled
        async with db.transaction():
            for row in page["items"]:
                await enqueue_invoice(db, row)          # maps entity via account_xref
            last = page["items"][-1]
            await save_checkpoint(db, "ns_invoices", last["modified"], last["id"])
        pulled += len(page["items"])
        if not page["hasMore"]:
            return pulled
Enter fullscreen mode Exit fullscreen mode

The checkpoint moves in the same transaction as the enqueued work, so a crash only causes a harmless re-read, since the enqueue ignores unchanged payloads. Round-tripping the timestamp through NetSuite's own formatting avoids time zone issues, and truncating to whole seconds can cause re-reads at the boundary but never skips a row. An invoice whose customer isn't linked in account_xref goes straight to the exception queue. Check column names in the account's Records Catalog.

On that client's project, invoice status came from NetSuite transactions themselves, instead of from a status field that CRM users edited by hand. It reached Salesforce through scheduled polling of exactly this kind. The poll ran every five minutes from a durable checkpoint, with a small overlap at the boundary, and queued the Salesforce updates. Under normal load, the next poll plus processing put a change in Salesforce within 10 to 15 minutes. Large posting batches took longer, and the integration reported them as backlog. A nightly full reconciliation caught anything missed or delayed, so nobody mistook the five-minute interval for a promise that every invoice would appear within five minutes.

The reconciliation report

Everything above makes the sync more likely to be right, but only a scheduled comparison can show that it is. Each run snapshots NetSuite invoices (with SuiteQL) and their Salesforce mirrors (with SOQL) into Postgres, and classifies every invoice with a full outer join.

WITH ns AS (SELECT * FROM ns_invoice_snap WHERE run_id = %(run)s),
     sf AS (SELECT * FROM sf_invoice_snap WHERE run_id = %(run)s),
     classified AS (
  SELECT coalesce(ns.ns_invoice_id, sf.ns_invoice_id) AS invoice_id,
         CASE
           WHEN ns.ns_invoice_id IS NULL THEN 'orphan_in_salesforce'
           WHEN ns.modified_at > %(as_of)s - %(settle)s::interval THEN 'in_flight'
           WHEN sf.ns_invoice_id IS NULL THEN 'missing_in_salesforce'
           WHEN x.sf_account_id IS NULL THEN 'customer_not_linked'
           WHEN x.sf_account_id IS DISTINCT FROM sf.sf_account_id THEN 'wrong_account'
           WHEN ns.status IS DISTINCT FROM sf.status
             OR ns.total IS DISTINCT FROM sf.total THEN 'stale_in_salesforce'
           ELSE 'matched'
         END AS outcome,
         ns.status AS ns_status, sf.status AS sf_status,
         ns.total AS ns_total, sf.total AS sf_total
  FROM ns
  FULL OUTER JOIN sf ON sf.ns_invoice_id = ns.ns_invoice_id
  LEFT JOIN account_xref x
         ON x.ns_customer_id = ns.ns_customer_id AND x.match_status = 'linked'
)
SELECT outcome, count(*) AS n,
       jsonb_agg(jsonb_build_object('invoice', invoice_id, 'ns', ns_status, 'sf', sf_status,
                                    'ns_total', ns_total, 'sf_total', sf_total)
                 ORDER BY invoice_id) FILTER (WHERE outcome <> 'matched') AS detail
FROM classified
GROUP BY outcome
ORDER BY outcome = 'matched', n DESC;
Enter fullscreen mode Exit fullscreen mode

The settle interval should equal the window you promised the business; an invoice modified ten minutes ago that Salesforce hasn't caught up with is the integration working as designed, and flagging it trains people to ignore the report. I run the same pattern for account fields, one comparison per owned field, plus every Salesforce account with no linked customer. The outcome names matter because wrong_account and stale_in_salesforce go to different people. I've covered turning results like these into a report with persistent breaks and owners in a piece on reconciliation reports finance will sign off against.

Back to the client

On that project, the new flows were incremental, with durable checkpoints, retry-safe writes and an exception queue for ambiguous matches. Before switching the old connector off, we ran an initial reconciliation, made the corrections the business owners approved, and ran the old and new integrations in parallel.

The parallel run lasted three weeks and included a month-end close. During it, only the new integration wrote the fields in scope. We disabled the old connector's equivalent writes and captured what it would have written, because two writers racing each other would have made the comparison meaningless. The differences were telling. The old mapping still proposed overwriting legal names from CRM edits, a set of invoices pointed at the wrong bill-to account after earlier account merges, and several payment-status updates were missing because an overnight batch had failed without alerting anyone. We classified each difference by cause and corrected the cross-references with finance's approval. The old connector was retired only after two consecutive clean daily reconciliations and a month-end sign-off.

Invoice status used to lag by 24 to 48 hours, and afterward it normally arrived within 10 to 15 minutes. We had promised a bounded window from the start, because NetSuite's API limits and batch posting make real time a promise nobody can keep. Unresolved customer mismatches fell from several hundred to a few dozen reviewed exceptions, and finance retired its spreadsheet once it agreed that the daily report explained every remaining difference. That last outcome is the one I'd single out. A spreadsheet like that is what finance builds when it has stopped believing the integration, and it only goes away when something else is more believable.

For the business side, including the object-by-object ownership table, see my article on designing a NetSuite Salesforce integration that stays in agreement.

I'm Jayant Chaudhary, founder of eProxim, where we build CRM-to-ERP integrations and the reconciliation that keeps them honest for implementation partners and the firms they serve. This article was suggested by AI assistance, then created, reviewed and edited by me.

Top comments (0)