Pull back Messaging Delivery/Open/Click Data into MJ

[AN-BC]

• Create table(s) in MJ to store this data - see below
• Make sure that you add ms_descriptions for these new tables. Take this entire issue into ChatGPT and ask it to generate the CREATE TABLE sql and the ms_description sproc calls.
• Flag in provider table to determine if it supports event retrieval or not.
• Add new abstract method to the BaseProvider called RetrieveEvents that can be called anytime and will take in a ProviderMessageID string that the provider will use to get events. Alternatively we might see if we can do this by date range instead which might be more efficient (probably)
• Implement the new abstract methods in our existing providers

• Implement calling the providers generically in the Engine and store the results in the new tables, noted below

We have two relevant tables in the database right now CommunicationRun and CommunicationLog will use Run and Log for short here to make it simpler.

We will add a new table called CommunicationLogEvent which will store "events" that are available from the provider such as delivered, opened, and click among others that we might receive. Providers of various types might have different events. Some won't support all of these events, some will support other kinds. We have a database schema to support this as noted here.

Fields in the CommunicationLogEvent table will be:

• ID

• CommunicationLogID - fkey to CommunicationLog.ID

• TypeID - fkey to below table CommunicationProviderEventType.ID

• EventDate - tracks the date/time that the event occured, as per the provider

• EventInfo - nvarchar(max) - could store information like the URL that was clicked on but this would be possibly different on a per CommunicatiotionProviderEventType basis

• new table: CommunicationProviderEventType which will store the supported event types for each provider.

• Fields will be ID, Name (nvarchar(100), Description nvarchar(max), BaseType nvarchar(20)

• BaseType will have a CHECK CONSTRAINT limiting the possible values to Delivered, Opened, Clicked and Other. We can add new BaseTypes over time if want

We will add a new column to the CommunicationLog table as follows

• ProviderMessageID which will be an nvarchar(255) and be used to store whatever unique ID the provider gives us when a message is sent. This is useful for whenever we get event information back per the above

All New Tables will have TrackChanges = 1 which will result in the typical CreatedAt/UpdatedAt cols being auto-added when CodeGen runs

You will need to add CommunicationProviderEventType records to the system for SendGrid and MS365 providers.

[AN-BC]

@JS-BC I updated this quite a bit just now to give you the needed design info to execute this. @cadam11 and @hiltongr if you have any comments, please share them in the next day or so.