Integrating Salesforce With a Legacy System: Part 3

This is the third and final part of a three-part series for a large international client that partnered with our team at LaunchPad Lab for their digital transformation. In this final part, we explore additional considerations for designing a data-syncing architecture using Postgres and Heroku Connect. We discuss approaches to monitoring and logging, managing dependencies and conflicts, and testing the system for optimal integration between Salesforce and the client’s custom applications.

For a complete understanding of this project, be sure to check out part one where we went over the background of the client and overview of the project, and part two where we discussed the design and development of a complex data-syncing architecture.

Final Architecture

As covered in the second installment of this content series, the team at LaunchPad Lab worked to develop the optimal data-syncing strategy for our client to successfully integrate Salesforce with their custom applications using Heroku Connect. After exploring different options for keeping data in each system in sync, the team landed on a combination strategy using Postgres’ triggers and listen and notify functions.

To implement this strategy, we wrote triggers for each table in each schema that needed to be synced. For example, a trigger was written both for the portal.users table as well as the salesforce.account table. These triggers invoked functions that captured the event details and payload of each of INSERT, UPDATE, and DELETE transactions that happened on each table. These event details were then dispatched via Postgres NOTIFY to one of two channels, depending on the schema the transaction occurred in.

While there are some helpful libraries to execute these SQL statements using Ruby, the team needed more flexibility than they provided and wrote these statements by hand. However, we were able to use familiar Ruby code to open up a database connection through ActiveRecord and expose the Postgres LISTEN command, which would receive the notification payload.

From there, we could pass that payload to the functions and services that could do additional custom logic as needed, such as performing any aggregates or transformations needed when aligning fields from one schema’s table to another. This allowed us to keep key business logic in the language we know and love.

The resulting final architecture of this strategy is depicted below. The diagram illustrates the flow of data from each system through the system architecture, ending in our Ruby services containing custom business logic.

Other Considerations

Once we determined our data-syncing approach, we could tackle other important facets of a robust data-syncing process such as:

• • Monitoring and Logging
  • Dependency Management
  • Automated and Manual Testing

It is important to carefully consider all of these aspects to ensure the Salesforce integration into an existing legacy system is successful, efficient, and optimized for expected outcomes.

Monitoring and Logging

Having a way to track when and what data was synced is a common feature for data syncing processes. It’s particularly helpful for auditing and debugging if something goes wrong. Heroku Connect does this as well and provides a TriggerLog model in your database, surfaced as an event log in the Heroku Connect dashboard, for this purpose.

While Heroku Connect captures events for changes between the Postgres Salesforce schema and the Salesforce instance, we also wanted to capture events for the changes between schemas. As a result, we included a small DatabaseTriggerLog model that simply captured the payload and time of the change and status for whether the mutated record was synced or not. If a record makes it all the way through the sync logic, this DatabaseTriggerLog is updated to a status of synced. Otherwise, it remains in a pending status. We can periodically query and review these pending records to trace sync failures and errors.

The key here is that a new DatabaseTriggerLog record is created within the Postgres trigger function so that even if the listener server cuts out, we have a record that a change occurred. We can then have a script that runs any DatabaseTriggerLog still in pending status through the sync service when the listener server restarts.

Dependency Management

As you design your data syncing strategy, it’s important to understand the dependencies between data elements and any potential conflicts that can occur.

A common dependency is between parent and child relationships. If both records are created in one system, they may not reach the other system in the “correct” order, resulting in what is called a “race condition”. That is, the child record may reach the other system prior to the parent, and may throw an error because it cannot find the parent record yet.

Heroku Connect solves this by managing transient data with unique identifiers, to maintain a reference to new records prior to persisting them in Salesforce. These identifiers can also be used to coordinate parent-child relationships through Heroku Connect. Finally, Heroku Connect also offers the ability to configure its sync or poll intervals, so you could intentionally poll the parent table more frequently than the child table.

Another way we solved this issue was to leverage a “retry flow”. In this process, if a child record arrives before its parent, we could capture the “missing parent record” error, and send the child record off to try the sync process again. This buys the system time for the parent record to arrive. You can retry the flow as many times as needed. This retry flow could also be leveraged for other captured sync errors.

Automated and Manual Testing

As with coding any feature, it’s important to test that code. While working locally, you can run manual tests in the console to verify each step is happening correctly as you build. On this project, we also leveraged Rails’ structure.sql file (instead of schema.rb) and reviewed the database directly to validate migrations and SQL functions were captured correctly.

For the core syncing logic, automated unit tests are useful to assert that data alignment is happening as you expect, e.g., are the right conditionals lined up, are individual fields mapped correctly, and is the log record updated?

For the end-to-end process, an automated integration test can be written to start the listener servers, perform a database transaction, and assert the final expected outcome (e.g., the correct object is updated in the corresponding schema).

In addition to automated tests, it’s helpful to include repeatable manual tests to ensure that data syncing between systems still results in the user experience desired. This is especially useful in uncovering edge cases introduced by nuanced business logic. For this project, we wrote a suite of manual test cases we could collaboratively work through with the client to verify each aspect of the integration between Salesforce and the custom application was working as intended.

Conclusion

This case study illustrates the complexities of data syncing and explores a variety of considerations and approaches to manage those complexities. As we’ve seen, Heroku Connect is a powerful tool for bi-directional data syncing between Salesforce and your custom application. Combining Heroku Connect with additional Postgres functionalities, we are able to achieve our client’s digital transformation goals and successfully integrate Salesforce with their legacy application.

Let’s Work Together!

Are you looking to build a similar solution or need help managing data synchronization? Our team at LaunchPad Lab is expertly equipped to combine the most efficient technologies and develop a workable, custom solution for your project’s needs. Contact us today and learn how we could assist you in integrating Salesforce with your custom application.

×

Subscribe to Our Newsletter

If you made it this far, you should sign up for our monthly newsletter. Get our latest articles and insights delivered each month.

First Name*

Last Name*

Email*