Custom Integration - Handling Delta Syncs and Webhooks for nested data

Hi all, I am continuing to work on a Quickbooks Custom Integration. I have recently run into what I expect may be a common issue when modeling data into Fibery from other systems. The integration has Invoice and Invoice Line databases in a one-to-many relationship. Quickbooks does not have a way to query only Invoice Line data so it must be extracted from Invoices. That works fine for full sync. However, for both webhook notifications and change data capture queries, Invoice objects are returned with the change method (created, updated, deleted) and all lines, but no indication of line changes. This makes it very difficult to perform any kind of deletions on delta sync since it’s impossible (not impossible, just a major pain) to know what lines have been deleted from Quickbooks. For a small dataset I would happily full sync every time, but as some accounts may have 1000s of Invoices or Purchases (and Purchase Lines) that seems like excessive work for each sync.

Has anyone, including the Fibery Team, found a solution for this class of integration issues?

My current plan is to add an Invoice version (Quickbooks sync token) to each Invoice Line and delete lines who’s version doesn’t match it’s parent Invoice using automations, but I would prefer to solve the issue in the integration code if possible.

Hi,

Unfortunately, integration won’t solve this at the moment. We don’t support nested data syncs for this case.

BTW, your solution with automation is nice idea.

1 Like

Understood, unfortunate that it can’t be done in a simpler way.

Would you consider adding a feature that allows for a filtered full sync? Meaning that a full sync is performed for entities match a certain criteria (invoice line’s invoice id is __). This could be kept really simple so you can only filter by 1 field and it’s a value match only (not <,>,!=,ect.). I think this would eliminate an entire type of integration issue since it’s seems that many other systems model this kind of data as an object with and array if objects rather than two queryable datatypes.

I understand this may not be a small job and I can make a separate feature request if appropriate.

1 Like

Update: Since integration sync order is not predictable and databases update one-by-one using automations did not work out as I expected. I may be able to do it with javascript but I am avoiding that since it won’t be available on my plan.

It seems like your CI app should be able to deduce the individual line changes if it stores the Quickbooks data in a local DB.

…though this approach would probably introduce a lot of complexity in your CI app.

1 Like

I had the same thought process and that may be what I end up with. However, the idea of storing every line id, (not uuids of course) for every datatype that has lines, for every Quickbooks account that the integration is used for and handling all of the race conditions seems like a bad way to solve the problem. Although it may be the only way without an API change.

I added a feature request for the filtered full sync, please consider it if this would be helpful for you. Filtered Full Sync for Custom Integrations