Feature Request: Add New Column in table Cap.published for relation to table every single data publish

[ariakustama]

Problem Statement: Currently, when publishing data to CAP, there is no direct way to relate the published data back to the original table in the database. This makes it difficult to track the history of published events for each record in the original table.

Proposed Solution: To address this issue, I propose adding a new column to the CAP published table that stores the primary key of the record being published. Specifically, I suggest the following:

Introduce a new column in the published table, named something like record-key. Allow users to pass the primary key value of the original record in the headers when publishing a message. If the record-key header is provided, it should be inserted into the new column in the published table. Benefits:

Enhanced Traceability: Users will be able to trace back the published events to the original records in their database tables. Improved Auditability: This feature will facilitate better auditing and monitoring of published events. Simplified Data Management: Users can easily display the publish history for each record in their database.

[ariakustama]

[yang-xiaodong]

This request involves changes to the data structure, which we will handle with utmost caution. We need to consider factors such as our user base and the upgrade migration process. Therefore, it might be challenging to add this feature.

However, this does not mean there are no alternatives. Simply put, user can use database triggers to achieve this. For example, in SQL Server, user can add the following trigger to the Published table:

CREATE TRIGGER [cap].[insertRecordKey]
ON [cap].[Published]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE e
    SET e.RecordKey= JSON_VALUE(i.Content,'$.Headers."cap-msg-id"')
    FROM Published e
    INNER JOIN inserted i ON e.Id = i.Id;
END