NCR CounterPoint to HubSpot with Skyvia, no custom code

The point of sale runs on a SQL Server inside the client’s network, in a data centre, and it cannot be exposed to the internet. There is no listing for it in the HubSpot App Marketplace, so the database is the only interface that exists. It holds more than 724 base tables, over 20 of them involved in a single transaction.

Three routes were open. The reseller could build a REST API, scoped at roughly four weeks of their development time, which puts a third party in the path of every future change. They could schedule CSV exports, which still leaves every mapping decision to make. Or we could read the database directly and accept the join complexity.

The schema survey settled it. The database ships 263 pre-built views that already flatten the joins, so the work needed no query design, no vendor build and no write access of any kind. Eight of those views carry everything the CRM needed.

One constraint outlasted all of that. Six of the ten deal stages the sales team wanted describe work that happens before an order exists: quoting, samples, test runs, artwork. The point of sale knows nothing about any of them, so whatever we built had to write some stages and keep its hands off the rest.

Eight views feed five objects. Customers become companies keyed on the customer number rather than the company name, which changes whenever the back office tidies a record. Ship to addresses become a custom object keyed on the customer number joined to the address id, because one customer can have several, though only 27 percent of orders name one. Products become products keyed on the SKU. Orders become deals keyed on the ticket number. Order lines become line items keyed on the ticket number joined to the line sequence.

Associations resolve by looking the business key up in HubSpot instead of carrying an internal record id, so there is no separate association pass. That only works when parents land before children, which is why every job runs in one chain rather than on its own schedule.

Deal stages shaped everything else. The sync writes only the four stages the source can prove, each derived from which view an order appears in: open, fulfilled, cancelled, and paid. It never writes the six stages a rep sets by hand. The step that handles open orders was later split in two for that reason, which is why seven numbered steps run as eight jobs: one pass closes out cancelled orders, and a separate pass creates new open orders and updates their fields without writing a stage at all. A deal a rep moved to artwork approval yesterday is still there after tonight’s run.

Closed won is derived, not synced. The accounts receivable view carries one row per invoice, and a zero balance on an invoice whose document number matches the deal advances that deal. Nobody retypes a payment into the CRM.

Three exclusions run on every step that touches them: eight retired customer records, eight deprecated category codes, one province written out in full.

The backfill loaded 20,004 orders reaching back to July 2020 along with 38,559 order lines, and the company record now carries the accounts receivable balance and its aging buckets beside them. A rep asking what a customer bought last spring reads it rather than asking someone to look.

Two operational facts came out of running it. The largest step, the order lines upsert, bursts through the portal read budget, so the step after it now waits 15 minutes instead of failing on a rate limit. And a run that fails without moving a single row is a connection that never opened, which is safe to retrigger, while a run that fails after moving rows is a data problem and is never retried automatically.

The run window moved for the same kind of reason. Starting in the early evening puts the whole chain, including a long night, clear of the overnight maintenance window on the client’s network that had been dropping the source connection mid run.

A managed cloud sync platform does the data movement. A lightweight agent runs on a Windows machine inside the client’s network and opens outbound HTTPS connections only, so no inbound firewall rule was needed and the database stays unreachable from outside. The SQL login is read only, and nothing is ever written back to the point of sale.

The one piece we wrote is a scheduled job that triggers each step in order and watches it to completion before starting the next. It attaches to an already running step rather than starting a second copy, treats a failure that loaded almost every row as a success, and raises an alert on a hard failure.

Operations is three things: the platform’s per record error log, the run history for each step, and a fixed order that puts a parent record in place before anything that points at it.

The picture is one line with a fan at the end. The point of sale database is read through the on-premises agent by the sync platform, which runs eight jobs in a fixed order and writes one HubSpot object per job. The only edge that runs backwards is the last one: it reads invoice balances and updates deals an earlier step created.

Every step is an upsert on its own key, so replaying a night that only half finished changes nothing that already landed correctly. A step that fails leaves the chain stopped rather than letting the next one run without its parent, and the fix is to rerun the chain from that step. Direction is one way throughout: nothing is written back to the point of sale, which is why the SQL login is read only rather than merely unused for writes. The one derived value that travels the other way is the deal stage, and it moves inside HubSpot.

One association the source cannot always supply is the link from a deal to a shipping location. The address id is set on 27 percent of orders, and the rest ship to the billing address, so the sync creates that link only where the source populates it and leaves the deal unlinked otherwise. Every other association key is populated on every row.

Eighty-six fields are mapped across the five objects. The excerpt below gives the match key and one or two fields per object rather than the whole sheet.