Sometimes we need to store messages into the database for retry when messages cannot be pushed to the target at the first attempt.
Again here we need to think of what fields are required in the database table to store the messages. Otherwise you will keep on adding fields afterwards which may need changes of code and documents. So you should decide which fields are required in the database table along with storing the message payload at the first place. Typically you should have following fields.
Message ID: This is the primary key of the table. This is the unique id which to be retrieved from the message payload. Each message should have message id which uniquely identify the particular message. If you receive the message with same message id, it means it is duplicate and you should reject that message. Sometimes you may not get this unique message id in the message payload, in that case try to identify some unique field or combination of fields in the messages which will uniquely identify a message and act as a key.
Program: Name of the program. This is applicable for large initiative where under a single program, there are multiple projects. Otherwise for small project, program name and project name can be the same.
Project: Name of the project under the program.
Process: Name of the process producing the message
Message Type: Message or event type. You can correlate the messages which to be processed by the particular process. Though you can correlate with process name. But it is good to have this field.
Source System: Message source system which is sending the message to the destination
Destination System: Target system where message to be sent
Message-Payload: This is the message payload. This can be a text or clob type data.
Received-timestamp: Timestamp when message is received
Last-time retried timestamp: Timestamp when message are retried to push to target
Retry-flag: This is a Boolean flag. If denotes that messages to be retried to send to destination or not. If messages are successfully pushed to destination, retried flag should be set to “N”.
Retry count: Number of times messages are retried to push to destination. Message retry may not be successful in the first attempt. So you should keep on increasing the counter every time you attempt to push the message to the target.
Message status: Message are successfully sent or not. Store “success” if the message is successfully sent to destination. Otherwise store failure.
Error Code: This is optional. You may store the error code to know the reason of retry failure
Error Description: This is optional. You can store detail error messages.
Also remember you should call common audit process from retry process. Retry process should be the common reusable module of the main process which try to push the message to the destination.