Integrating Hubspot and Custom ERP using SiddhiQL

What is a custom ERP?

Custom ERP is a project management tool which helps its users to plan, organize and manage resources. Across management levels from the CEO to team members down the chain, custom ERP is used to consume and enter the relevant information.

Custom ERP need a backend database. It can be MS-SQL or MySQL or any other RDBMS. This database can be connected to Kafka - which is based on a publisher-subscriber model and can be used as a Kafka connector to custom ERP database. The integration of these two tools can be done by stream processing.

Here comes in picture stream processing studio – SiddhiQL.

What is SiddhiQL?

SiddhiQL is a complex event processor designed to identify complex events in the stream, processes the complex events and notify them in real time. SiddhiQL can have input stream events right from HTTP, Kafka, TCP, in-memory, WSO2Event, e-mail, JMS, file, RabbitMQ, MQTT, WebSocket, Twitter and Amazon SQS. Basically, SiddhiQL can accept event inputs from many different types of sources, process it to generate insights and publish them to many types of a sink.

In our case, Kafka source of SiddhiQL is used to identify the complex stream coming from Kafka connector connected to custom ERP database. The stream contains the data in the payload that could be of created through the new project, milestones – which can be achieved in a project or any other functionality in which custom ERP works. SiddhiQL identifies the required events from Kafka source and it will send that particular event from Kafka stream to Hubspot.

Sample SiddhiQL snippet for identifying an event from Kafka is as follows:

@App:name("HelloKafka")
@source(type='kafka', topic.list='kafka_topic', partition.no.list='0',
threading.option='single.thread', group.id="group", bootstrap.servers='localhost:9092', @map(type='json'))
define stream SweetProductionStream (projectCode string, projectName string, totalBid int, expectedDuration int, expectedStartDate string, expectedEndDate string, over bool);


HubAPI:

Hubspot supports API through which deals, companies, contact can be created, updated and deleted. Various APIs can be called to Hubspot; basically, in two ways – using unique Hapikey in URL of API call and OAuth 2.0. In our case, we will be using Hapikey of Hubspot account.

Some of the APIs include:


Workflow:

In a custom ERP, the project milestones can be created through simple steps. Similarly, milestones can be created as a custom deal property in Hubspot which offers flexibility to create custom properties as we need. As ERP has backend database in some RDBMS, it provides different views from which project tables are created. One such view is Milestone view which contains fields like milestone name, project name, milestone amount and other required details. Kafka can be connected to this ERP RDBMS database view. This access provides the integration of source Kafka and SiddhiQL that will identify the project update stream and pass it to the extension program which eventually is intended to update deal related to that project.

• SiddhiQL identifies events from a custom ERP
• Get all deals APIs from which dealId is extracted
• Using dealId call to get a deal API from which initial milestones and their values can be identified
• Form JSON for updated milestone amount and total deal amount
• Update the deal with payload in JSON using deal API

Sample update a deal API can be as follows:
Example URL:
https://api.hubapi.com/deals/v1/deal/19320611?Hapikey=demo

Note: Your "content-type" must be application/JSON.
Example deal update JSON:
{
"properties": [
{ "name": "amount",
"value": "70000"
}
]
}

• Alert notification via email when the project is not present in Hubspot
• Total deal amount equals to the sum of all milestone amount
• Notifications in Hubspot when a deal is updated via API
• Upon successful updating, the event can be replicated in another Kafka topic or any other source/sink