Integration: Keep two systems in sync

I have a GIS system with 40 tables, ranging from 1,000 to 60,000 rows per table. The tables are the system of record for **assets** in a municipality. The GIS assets in the tables get integrated to a Workorder Management System (WMS) on a weekly basis. The integration is based on web services that serve up the GIS tables to the WMS. **Constraint #1:** The integration to the Workorder Management System is **multi-purpose**. 1. There is a single asset table in the WMS that gets updated, via **cron tasks**, with any edits that have been made to the GIS assets (new assets, changed assets, and decommissioned assets). Only assets that have been edited are updated in the WMS. 2. The integration is also used to dynamically serve up the assets to a **web map** in the WMS (all of the GIS assets are used in the map--not just the assets that have been edited). The map in the WMS connects directly to the GIS web services--it does not use the records in the asset table or the cron tasks. **Constraint #2:** The WMS cron tasks are **notoriously slow**. Given my organization's infrastructure, my vendor says that the WMS cron tasks will only be able to sync **150 records per minute**. - Testing is ongoing, but we have been told to only sync the records that actually need to be synced (edits) due to the significant performance concerns. In other words, we can't just integrate or copy *all* records, *all the time*. - *To give you an idea, this is what the cron task process looks like: REST GIS web service >> JSON object >> Parse the JSON into individual records >> Generate XML for each record >> Process the XML records with Java classes >> Insert the records into the database*. **Constraint #3:** GIS data is **notoriously messy**. In constraint #2, I mentioned that the records get processed with Java classes. The Java classes check for errors (parent/child, field rules, etc.) and flag any records that fail. - These records do not get integrated into the WMS. - It is up to the GIS teams to correct the errors in GIS tables, then we'll try again in the next integration instance (next week) to sync the GIS records to the WMS. --------------- **Question:** Given the constraints above, I think I need to figure out a way to integrate all the GIS assets to the WMS (constraint 1.2), but also flag the records that need to be synced due to edits (constraint 1.1). - For edited assets that failed to sync--I need to **retry** them in future syncs until they are successful. - And I need to avoid syncing records unnecessarily--due to the performance concerns. **How can I do this?**