Warehouse Direct synced a million invoices from SQL Server into HubSpot

The Customer

Warehouse Direct sells to business accounts, including schools. Its customer accounts, product library and sales invoices live in a SQL Server database, and its products carry class codes and categories, among them MRO (maintenance, repair and operations) items.

Before this project, the company’s sales reps logged their pre-sales calls in HubSpot while every invoice stayed in the database. A rep looking at a customer in the CRM could see the conversation but none of the buying history behind it.

The Challenge

Warehouse Direct wanted its sales team to see each account’s products and category spend in the CRM, and it wanted the platform to act on that data. One example set the bar for the whole build. An account with more than two invoices and over $500 of MRO spend should be flagged automatically, so a rep knows which customers are buying in that category and which ones might.

That meant moving a lot of history. The company asked for a rolling 36 months of invoices, which came to over one million records, on top of every account and every product.

The data also had edge cases that any invoice sync has to respect. School accounts sometimes carry invoice dates in the future, about 500 a year. Some product classes belong in the catalog and some do not. The integration had to handle those rules without anyone babysitting it.

The Solution

We built the integration on Lynton’s own sync engine, reading from SQL views the client’s database team created to our specification. Three maps carry the data.

• Map 1 moves SQL accounts to HubSpot companies, with ownership assigned from a sales rep email view so each company lands with the right owner.
• Map 2 moves the SQL product library to HubSpot products, excluding two product classes from the load and removing one of them if it later appears.
• Map 3 moves category sales from the invoice tables to deals, with line items, so spend by category is visible on every account.

Good views make a fast, reliable sync, so we spent the first weeks of the project on their design with the client’s DBA. We asked for trimmed whitespace, views filtered to records with a sales area set, the sales rep ID carried on the invoice header, a dedicated view for rep lookups, and ID columns wide enough to hold HubSpot’s identifiers for years to come.

The sync keeps its place by reading the latest invoice date it has seen. A future-dated invoice would move that marker ahead of real activity, so we recommended the invoice view exclude future dates. We also set up a monthly audit sync. A second view holds only last month’s invoices, and the integration reads it on the fifth of each month to pick up any header that was added or changed after the live sync passed.

On the platform side, we worked out the workflow logic that counts an account’s MRO invoices and sums their spend, then sets the flag once the account passes two invoices and $500. We also enrolled the client in HubSpot’s beta for a read-only deal pipeline, so synced invoices sit in their own pipeline where nobody can edit them by mistake.

Testing ran in two rounds. We opened with a handoff call that explained the scenarios and the feedback form, then the client’s team worked through real records while a strategist sorted every reported fail into a bug, a false negative or a change request. The historical load ran from September 2021. Accounts and products went first, then the invoices at an estimated 100,000 deals a day, monitored in two-week blocks until the full 36 months were in. When it finished in January 2022, we cleaned up the developer documentation, walked our support team through the back end of the build and handed the integration to them, with the client’s own admins able to pause the sync whenever they need to.

What They Got

Warehouse Direct’s sales team can now open a company in their CRM and see three years of invoices, the products on them and the spend by category, alongside the calls they logged themselves. Accounts that cross the MRO threshold are flagged without anyone running a report.

Every new invoice, account and product arrives on the live sync, and the monthly audit catches anything that changed after the fact. The company’s database stays the system of record, and the CRM keeps reading from it.

Wrapping Up

Our standard on an integration like this is to design the source views with the sync in mind, test with the client’s own records before anything goes live, and leave behind documentation a support team can run from. Lynton now builds AI-native websites on open infrastructure; see our current work at /case-studies.