Refactor and Stabilize Asset Synchronization Flows

[mjabascal10]

Describe the feature

Improve the reliability, consistency, and concurrency‑safety of the asset synchronization logic used by both scheduled tasks (synchronizeAgents and synchronizeSourcesToAssets).
Currently, both schedulers write to the same utm_network_scan table using the same saveAll() method, which leads to:

• Duplicate key violations (uk_asset_name)
• Stale state exceptions
• Rollback-only transactions contaminating the Hibernate session
• Race conditions between schedulers
• Full-table scans (findAll()) and in-memory matching
• Batch failures that roll back entire sync cycles

The goal is to redesign the synchronization flow to be idempotent, concurrent-safe, and database-efficient, eliminating the root causes of these failures.

Use Case

As a system, I need to:

1. Allow both schedulers to update assets without stepping on each other.
2. Prevent duplicate asset creation when two schedulers process the same hostname/IP.
3. Avoid loading the entire utm_network_scan table into memory.
4. Ensure that a failure in one scheduler does not corrupt the Hibernate session for the other.
5. Guarantee that asset updates are atomic, consistent, and isolated.
6. Support safe upsert behavior when an asset already exists.
7. Maintain accurate asset status (CHECK, MISSING, etc.) without race conditions.
8. This ensures stable ingestion of agents and data sources, prevents database corruption, and improves system reliability.

Proposed Solution

1. Add concurrency control

• Use ShedLock or PostgreSQL advisory locks to prevent schedulers from running simultaneously.
• Ensure each scheduler runs in its own clean transaction.

2. Remove findAll() and replace with targeted DB lookups

• Query assets by hostname/IP directly.
• Avoid in-memory joins and reduce race windows.

3. Replace saveAll() with safer persistence

• Use per-entity saves or chunked batches.
• Add DB-level UPSERT (ON CONFLICT DO UPDATE) where appropriate.
• Ensure saveAll() does not wrap exceptions and does not hide Hibernate errors.

4. Add optimistic locking

• Add @Version to UtmNetworkScan to detect concurrent modifications.

5. Refactor synchronization logic

• Make both sync flows idempotent.
• Normalize matching rules (IP vs hostname).
• Ensure new assets are only created after checking DB for existing records.

6. Clean up transaction boundaries

• Annotate scheduler entry points with @transactional.
• Remove try/catch blocks that swallow Hibernate exceptions.
• Ensure session is cleared after failures.

7. Improve maintainability

• Split logic into smaller, testable service methods.
• Add logging around decision paths (create/update/missing).

Other Information

No response

Acknowledgements

• I may be able to implement this feature request
• This feature might incur a breaking change