High-Level Specification

Functional Overview

The reconciliation mechanism ensures reliability and traceability for custom integrations (e.g., Icertis to CPI) by logging failed messages, managing scheduled retries, tracking retry attempts, and notifying stakeholders of persistent failures. 

Objectives

• Reliability: Ensure that all failed integration messages are captured, retried, and escalated as needed to minimize data loss and integration gaps.
• Traceability: Maintain a comprehensive audit trail for each integration attempt, including message content, timestamps, error details, and a record of retries.
• Timely Escalation: Automatically notify relevant stakeholders when persistent failures occur, enabling prompt manual intervention.
• User Transparency: Provide authorized users with real-time visibility into the status of integration messages for proactive monitoring and troubleshooting.
• Compliance: Adhere to Syensqo’s IT security, data privacy, and regulatory requirements throughout the process.
• Reporting: Enable robust reporting and analytics on integration performance, failure rates, and resolution times to support continuous improvement

Assumptions

• Source systems (e.g., Icertis) are responsible for initial message transmission and may perform immediate retries, but scheduled reconciliation is handled separately.
• The Reconciliation and  ReconciliationRetry table  via Icertis masterdata is the single source of truth for failed integration attempts.
• Scheduled jobs (e.g., every 12 or 24 hours) are configured and maintained by IT operations.
• Users have appropriate access to view failed message statuses via the Icertis UI.
• All integrations adhere to Syensqo’s security and compliance policies.

Dependencies

• Scheduled batch jobs must run after source systems have completed their integration attempts.
• The Icertis email notification system must be operational for timely stakeholder alerts.

Special Requirements

• Integration with third-party systems (Icertis, CPI) requires secure authentication (e.g., OAuth, certificates).
• Audit trails must be maintained for all message processing and status changes.
• The solution must use ETags for optimistic concurrency control, as all API calls internally in Icertis uses ETags for CRUD operations. 
• All error messages and notifications must be clear, actionable, and compliant with internal communication standards.

Scope and Objectives

The scope of this development covers the design, implementation, and monitoring of a reconciliation mechanism for custom integrations, such as the Icertis to CPI interface. The solution encompasses:

• Logging of all failed integration messages in a centralized tables.
• Automated, scheduled retry of failed messages, with tracking of each attempt.
• Escalation via automated notifications to stakeholders if failures persist beyond a defined threshold.
• Providing visibility to authorised users for monitoring and manual intervention.
• Ensuring compliance with Syensqo’s security, data privacy, and audit requirements.
• Supporting reporting and analytics needs as defined in related Jira requests.

This scope encompasses integration with both internal and external systems, covering all technical, functional, and compliance aspects necessary for reliable and transparent message processing.

Component Diagram

Sequence Breakdown

1. Icertis Initiation (1): The process begins with "Icertis," which sends messages to the "Azure Service Bus." This is the starting point for events or data that need to be processed.

2. Azure Service Bus to Azure Event Grid (A1): Messages from the Azure Service Bus are then directed to the "Azure Event Grid." 

3. Azure Event Grid to Webhook (A2): The Azure Event Grid routes these events to a Icertis Webhook. This implies that the events are being pushed to an external service or application via an HTTP callback. 

4. Webhook to SAP (A3): The Webhook sends the information to "SAP" for sending updates or new data.

Icertis Task Service and Development Scope :

This section seems to describe a more detailed, internal process within Icertis, particularly focusing on handling events and ensuring data consistency. The "Icertis Task Service" acts as an orchestrator for the processes within the "Development Scope."

1. Icertis Task Service Orchestration (B1 & B2): The "Icertis Task Service" is windows service that triggers or manages icertis event based and scheduled tasks within the "Development Scope." It triggers the "Scheduled Jobs" (B2) and e.g. "CustomEventBasedTask" which sends messages to SAP sytem(B3).
2. CustomEventBasedTask (B3): The CustomEventBasedTask which invokes the 3rd Party API for integration.
3. Max Retry = 5 (B4): If the task fails, there's a retry mechanism with a Max Retry = 5 within Icertis via hangfire. The CustomEventBasedTask will attempt to re-execute up to 5 times if it encounters failures.
4. Conditional Logic (B5 ): If the API call fail, Log the fail record in the Reconciliation table in B6. Regardless of retries, if a task fails, the "Log Failed Records" process is activated to record details of the failure.
5. Reconciliation Table (B6): The logged failed records feeds into Reconciliation table.

6. Auto Delete (B8) : Auto delete the reconciliation and reconciliation retry records older than 90 days. 

7. ReconciliationTask (C2): These scheduled jobs trigger a "ReconciliationTask" to process the failed records for the integrations and add/update the ReconciliationRetry table. 
8. Retry Failed Messages (C3 & C4): If the ReconciliationTask loads all the failed records from the Reconciliation table and reprocess them to 3rd Party API

Key Features

1. Failure Logging

   • When an integration call (e.g., Icertis to CPI) fails, the failed message is logged in a reconciliation table.
   • The log includes details such as message content, timestamp, error details, and a retry count.

2. Reconciliation Data Tables

   • Reconciliation and ReconciliationRetry Table Acts as the single source of truth for all failed integration attempts.

3. Scheduled Retry Job

   • Runs in every 4 hr daily (Cron Expression in Worker Task table)
   • Picks up failed messages from the Reconciliation and ReconciliationRetry table and attempts to resend them to the target endpoint.
   • Updates the retry count and status after each attempt.

4. Retry Count & Failure Notification

   • Each message’s retry a new entry is created in ReconciliationRetry.
   • The success and failure of the retry is updated in the same ReconciliationRetry entry.
   • If a message fails more than 5 times, an automated failure notification email is sent to relevant stakeholders.
   • This ensures timely awareness and manual intervention if needed.

5. User Visibility

   • Users with access (e.g., via Icertis UI) can view the status of failed integrations tables directly from the master data tile.
   • This promotes transparency and allows for proactive monitoring.
6. Auto delete aged entries
   • Cleanup of entries in reconciliation and reconciliation retry tables 
   • Pick up the records older than 90 days and delete.

Sequence Diagram

Step-by-Step Flow of the Reconciliation Mechanism

1. Integration Attempt

   • Icertis (or another system) sends a message to the CPI endpoint (e.g., SAP or a third-party API).

2. Failure Detection

   • If the message fails (e.g., due to a network error or endpoint issue), the failure is immediately logged in the Reconciliation table with all relevant details.

3. Immediate Retry by Source System (Optional)

   • The source system (e.g., Icertis) may attempt a quick, immediate retry.
   • Note: This immediate retry is separate from the scheduled reconciliation process.

4. Scheduled Reconciliation Job

   • At scheduled intervals (e.g., every 12 or 24 hours), a reconciliation job scans the Reconciliation and ReconciliationRetry tables for messages with status “Pending” or “Failed.”
   • The job attempts to resend these failed messages to the endpoint.

5. Logging Retry Attempts

   • After each retry attempt, a new entry is created in the ReconciliationRetry table:
     • The retry count is incremented.
     • The status is updated (e.g., “Retried,” “Success,” or “Failed”).
     • The timestamp of the attempt is recorded.

6. Failure Escalation

   • If a message’s retry count exceeds the maximum allowed attempts (e.g., 5), the system automatically triggers a failure notification email to stakeholders.

7. User Monitoring

   • Users can access both the Reconciliation and ReconciliationRetry tables (e.g., via the Icertis UI) to view the status and history of all failed or retried messages.
8. Auto Delete aged entries : Auto delete the reconciliation and reconciliation retry records older than 90 days

Configuration
To ensure robust, reliable, and transparent integration between Icertis and downstream systems (such as CPI), it is essential to track and manage all integration attempts—especially failures. The IntegrationReconciliation table is designed to serve as the central repository for logging every failed integration message, capturing key details such as message content, timestamps, error information, retry attempts, and processing status.

This table enables:

• Automated Retry and Escalation: By recording each failure and retry, the system can automatically reprocess failed messages and escalate persistent issues to stakeholders.
• Traceability and Auditability: Every integration attempt is logged with comprehensive details, supporting audit requirements and root cause analysis.
• User Visibility: Authorized users can monitor the status of integration messages in real time, enabling proactive intervention and troubleshooting.
• Reporting and Compliance: The structured data supports analytics, reporting, and compliance with Syensqo’s IT and regulatory standards.

Reconciliation Table

ReconciliationRetry Table

Proposed Technology to Use

• Icertis custom event job & Scheduled Job 

Processing Logic

• When to Run

  • Reconciliation jobs are scheduled to run at regular intervals (e.g., every 12 or 24 hours).

  • These jobs are triggered automatically by the system to process failed or pending integration attempts.

• How Reconciliation Table Entries Are Added

  • Users can monitor the status of integration attempts and retries through the Icertis UI or other master data interfaces.

  • Both the Reconciliation and ReconciliationRetry tables are accessible for review, allowing users to:

    • Track the progress and outcome of each integration attempt.
    • View detailed error messages and retry history.
    • Identify records that require manual intervention or escalation.

• Scheduled Retry

  • The scheduled reconciliation job scans the Reconciliation table for entries with a status of “Pending” or “Failed.”

  • For each failed entry:

    • The job attempts to resend the message to the target endpoint.

    • Each retry attempt is logged in the ReconciliationRetry table, incrementing the retry count and updating the status and timestamps.

    • If the retry count exceeds the maximum allowed attempts (e.g., 5), an automated failure notification is sent to stakeholders.

• User Monitoring
  • Users can monitor the status of integration attempts and retries through the Icertis UI or other master data interfaces.
  • Both the Reconciliation and ReconciliationRetry tables are accessible for review, allowing users to:
    • Track the progress and outcome of each integration attempt.
    • View detailed error messages and retry history.
    • Identify records that require manual intervention or escalation.

Auto-Deletion of Aged Entries

• During Scheduled Retry Jobs:

  • Each time the scheduled reconciliation job runs (e.g., every 12 or 24 hours), it also checks for and deletes entries older than 90 days from both tables.

How It Works

• Entry Identification:

  • The system identifies records in the Reconciliation and ReconciliationRetry tables where the creation or last update timestamp is more than 90 days old.

• Automated Deletion:

  • These aged entries are automatically removed as part of the scheduled job, ensuring that only recent and actionable records are retained.

• Audit Logging:

  • Deletion actions are logged for audit purposes, capturing details such as the number of records deleted and the timestamp of the operation.

User Impact

• No Manual Action Required:

  • The cleanup process is fully automated and does not require user intervention.

• Data Retention Policy:

  • Users should be aware that records older than 90 days will no longer be available for review or audit through the UI or interfaces.

  • If longer retention is required for compliance or business needs, this policy should be reviewed and adjusted accordingly.

Benefits

• Reliability: Ensures failed integrations are not lost and are retried systematically.

• Auditability: Provides a clear history of all attempts and outcomes.

• Transparency: Stakeholders are notified of persistent failures, enabling timely resolution.

How to Test

Language Requirements

• All labels, logs, and error messages will be in English.

Development Details

Package

Enhancement Implementation

Other Development Objects