Data model

Table message

Table for storing asynchronous messages:

Attribute	Type	NULL	Description	Value description
msg_id	number	No	Unique identifier, primary key.	Automatically generated.
msg_timestamp	datetime	No	Message timestamp, generated by source system. This timestamp determines processing order of messages.	Source system, part of TraceHeader TraceHeaderProcessor.TRACE_HEADER
receive_timestamp	datetime	No	Timestamp when message arrives to integration platform, generated by OpenHub.	Automatically generated when input request arrives.
service_name	string	No	Service name, e.g. customer Implements org.openhubframework.openhub.core.entity.ServiceExtEnum interface	Source system AsynchConstants.SERVICE_HEADER
operation_name	string	No	Operation name, e.g. createCustomer	Source system AsynchConstants.OPERATION_HEADER
object_id	string	Yes	Object ID that is impacted by processing of this message. For example when this message updates customer object then it can be customer ID. This values serves for searching messages which impact identical object. When there are two messages in processing queue, both want to change identical object, then first message will be skipped and second one will be processed.	AsynchConstants.OBJECT_ID_HEADER
entity_type	string (enum)	Yes	Entity type (implements org.openhubframework.openhub.api.entity.EntityTypeExtEnum interface). In general it's enough to detect identical changed data by objectId and operation name but there are few different operations which can change the same data (e.g. setCustomer, setCustomerExt). If defined then it will be used for "obsolete operation call" detection instead of operation name.	AsynchConstants.ENTITY_TYPE_HEADER
correlation_id	string	No	ID generated by source system that is important for pairing request and asynchronous response in source system. Combination correlation_id and source_system is unique.	Source system, part of TraceHeader TraceHeaderProcessor.TRACE_HEADER
process_id	string	Yes	Process identifier serves for pairing messages of same process.	Source system, part of TraceHeader TraceHeaderProcessor.TRACE_HEADER
payload	string (XML)	No	Message payload
envelope	string (XML)	No	The whole input request, e.g. SOAP envelope including headers and body
source_system	string (enum)	No	Source system (implements org.openhubframework.openhub.core.entity.ExternalSystemExtEnum interface)	Source system, part of TraceHeader (applicationID) TraceHeaderProcessor.TRACE_HEADER
state	string (enum)	No	Message state (enum value: NEW: new saved message PROCESSING: message is being processed OK: successfully processed message PARTLY_FAILED: last processing ended with error, there will be next try FAILED: finally failed message, no next processing WAITING: parent message that waits for child messages WAITING_FOR_RES: message that waits for confirmation/response from external system CANCEL: message was canceled by external system or by administrator. This state isn't set by this application. POSTPONED: message is postponed because there was another message that was processed at the same time with same funnel values. See overview of operations which change message state.
failed_count	number	No	Count of unsuccessful tries. Default value is 0.
failed_error_code	string	Yes	Error code when message processing failed (implements org.openhubframework.openhub.core.common.exceptions.ErrorExtEnum interface)
failed_desc	string	Yes	Error description when message processing failed.
last_process_timestamp	datetime	Yes	Timestamp when this message was lasttime updated.
custom_data	string	Yes	Field for saving custom data, suitable for sharing information between more tries of processing.
parent_msg_id	number	Yes	Reference to parent message (parent-child binding).	Useful for parent-child binding, ChildMessage#createMessage
parent_binding_type	string (SOFT)	Yes	Binding type defines how tightly child message does influence parent message: HARD: result of child message influences result of parent message (for example when child message ends in FAILED state then parent message will in FAILED state too) SOFT: result of child message has no effect to parent message Useful for parent-child binding only, see asynch-child component.	Useful for parent-child binding, ChildMessage#createMessage
funnel_value	string	Yes	Funnel value is for finding out if two or more concurrent messages impact identical target object. Funnel value can be anything, e.g. customer ID, subscriber mobile number, custom string etc. This value is for msg-funnel component use. What is difference between object_id and funnel_value? Object_id represents real object identifier and serves for analysis if any message should be skipped because message data are obsolete. On the other hand funnel value represents virtual value (it can be anything) and is used to ensure that there will be only one message with funnel value at the moment being processed in specific route point.	AsynchConstants.FUNNEL_VALUE_HEADER
funnel_component_id	string	Yes	Funnel identifier that specifies unique point during message processing. Messages are checked and filtered out to this point. This value is for msg-funnel component use.	Useful for msg-funnel component.
guaranteed_order	boolean (false)	No	When this flag is true then mesage is processed in guaranteed order.	AsynchConstants.GUARANTEED_ORDER_HEADER
exclude_failed_state	boolean (false)	No	Messages in FAILED state are taking into consideration for guaranteed order by default. If FAILED state should be excluded then this flag will be true. Value of this attribute has sense only when guaranteed_order is true.	AsynchConstants.EXCLUDE_FAILED_HEADER

Table external_call

Table stores calls to external systems when extcall component is used.

Attribute	Type	NULL	Description
call_id	number	No	Unique identifier, primary key.
msg_id	number	No	Reference to asynchronous message.
operation_name	string	No	Operation name.
state	string (enum)	No	External call state: PROCESSING: external call is just processing OK: external call is successfully done FAILED: external call failed, try it next time FAILED_END: all confirmation tries failed, no further processing
entity_id	string	No	Entity identifier
msg_timestamp	datetime	No	Message timestamp (from source system).
creation_timestamp	datetime	No	Timestamp when this record was created.
last_update_timestamp	datetime	No	Timestamp when this record was lasttime updated.
failed_count	number	No	Count of unsuccessful external calls.

Tables request and response

These tables stores requests/responses in communication with external systems.

Attribute	Type	NULL	Description
req_id	number	No	Unique request identifier, primary key.
msg_id	number	Yes	Binding to asynchronous message (NULL for synchronous messages).
res_join_id	string	Yes	Identifier for pairing/joining request and response together. It can be Message or correlation ID or exchange ID or some ID that is unique with uri. This attribute helps to associate response to the right request.
uri	string	No	Endpoint/target URI
req_envelope	text	No	The request content
req_timestamp	datetime	No	Timestamp when request was send to target URI.

Unique key: uri and res_join_id

Attribute	Type	NULL	Default	Description
res_id	number	No		Unique response identifier, primary key.
req_id	number	Yes		Binding to corresponding request to this response.
res_envelope	text	Yes		The response itself. If not specified then failed reason must be filled.
failed_reason	string	Yes		Reason (SOAP fault, exception, stackTrace, ...) why communication failed.
res_timestamp	datetime	No		Timestamp when response/failed reason was received back.
failed	boolean	No	false	Has been communication failed? If yes then failed reason must be filled.
msg_id	number	Yes		Binding to asynchronous message (NULL for synchronous messages).

Backups

With regards to stability of the database schema, database performance and the whole OpenHub framework solution is recommended to perform regular archiving of records with regards to the utilization of individual tables and the number of records to be found in the tables.
As part of the solution is a distributed database procedure archive_records that has one input parameter. This parameter indicates the number of months, which is derived from the old record, which has to include archiving (default value is 2 months). The script can be expanded by additional tables to be archived. The actual procedure now includes database archiving tables: message, external_call, request and response. These records are stored in tables archive_message, archive_external_call, archive_request and archive_response.