Message Level Data - Recommended Workarounds

Overview

We have identified an issue related to missing records for message-level data and conversation data in the production environment. To address this effectively, it is highly recommended to first upgrade to release version CX4.4.11 before initiating any corrective actions as it contains disposition fixes for conversation data.

Recommended Workarounds and Considerations:

Drop Foreign Key Constraints (Message-Level Data):

Dropping foreign key constraints on the message-level data table is a recommended approach to enable the system to catch up on data ingestion post 25-03-2025.
Impact:

Conversation data for the missed messages will not be available as conversation data is somehow synced to the latest date.

However, the conversation_id can still be retrieved directly from the message-level data table, despite the broken linkage.

Note:
This option is preferable as it will prevent tight coupling of the data. Moreover, future data architecture changes from EF will also follow the same principle — aiming for a loosely coupled design without foreign key constraints. This approach will enhance flexibility and reduce dependency issues in subsequent releases.

Insert Dummy Record (Record Creation Time):

By inserting a dummy record with a record_creation_time set to a recent date (or 2–3 days after 25-03-2025), data ingestion can resume.
Impact:

Data for the missing period will not be recovered.

This will only allow new data to flow into the system moving forward.

Purge and Reload Conversation table Data:

Another alternative is to delete all conversation records dated after 25-03-2025 and re-ingest the data from that point onward to fully trace the root cause.
Impact:

Backup storage will be required prior to deletion.

Potential downtime may occur during database operations, especially if consumers are actively reading this data.

Please evaluate the above options carefully and proceed based on the criticality of the data and system availability constraints