Reliable B2B Tracking Database Schema

Microsoft

Mar 21, 2025

The Azure Data Explorer (ADX) cluster database used for Reliable B2B Tracking stores transaction data in a structured format.

AS2 transactions are stored in a table named AS2TrackRecords.
X12 and EDIFACT transactions are stored in a table named EdiTrackRecords.

These tables enable efficient querying and retrieval of B2B tracking data, providing structured insights into message flow, processing status, and troubleshooting details.

Since B2B tracking data is stored in an Azure Data Explorer database, users can leverage Azure Workbook to create visually rich custom dashboards for analyzing their B2B transactions.

If users choose to use rest API to create tracking store in integration account, then these two tables need to be created manually in the ADX database and integration account needs to have 'Ingester' permission to the database.

AS2 Tracking Table – AS2TrackRecords

All AS2 tracking data are stored on the AS2TrackRecords table. Below is the table creation query:

.create table AS2TrackRecords (
    IntegrationAccountSubscriptionId: string, /Subscription ID for the integration account.
    IntegrationAccountResourceGroup: string, // Resource group for the integration account.
    IntegrationAccountName: string, // Integration Account name.
    IntegrationAccountId: string, // Integration Account id.
    WorkflowRunOperationInfo: dynamic, // Workflow run operation information.
    ClientRequestId: string, // Client request id.
    EventTime: datetime, // Time of event.
    Error: dynamic, // Error if any.
    RecordType: string, // Tracking record type.
    Direction: string, // Direction of the message flow, which is either receive or send.
    IsMessageFailed: bool, // Whether the message is failed.
    MessageProperties: dynamic, // Message properties.
    AdditionalProperties: dynamic, // Additional properties.
    TrackingId: string, // Any custom tracking id.
    AgreementName: string, // Name of the agreement to which the messages are resolved.
    As2From: string, //AS2 message receiver’s name.
    As2To: string, //AS2 message sender’s name.
    ReceiverPartnerName: string, //AS2 message receiver's partner name.
    SenderPartnerName: string, // AS2 message sender's partner name.
    MessageId: string, // AS2 message ID.
    OriginalMessageId: string, // AS2 Original message ID.
    CorrelationMessageId: string, // AS2 message ID, to correlate messages with MDNs.
    IsMdnExpected: bool // whether the MDN is expected.
)

X12/EDIFACT Tracking Table – EdiTrackRecords

X12 and EDIFACT tracking data are stored in the EdiTrackRecords table. Below is the table creation query:

.create table EdiTrackRecords (
    IntegrationAccountSubscriptionId: string, // Subscription id of the Integration Account.
    IntegrationAccountResourceGroup: string, // Resource group for the integration account.
    IntegrationAccountName: string, // Integration Account name.
    IntegrationAccountId: string, // Integration Account id.
    WorkflowRunOperationInfo: dynamic, // Workflow run operation information.
    ClientRequestId: string, // Client request id.
    EventTime: datetime, // Time of event.
    Error: dynamic, // Error if any.
    RecordType: string, // Tracking record type.
    Direction: string, // Direction of the message flow, either receive or send.
    IsMessageFailed: bool, // Whether the message is failed.
    MessageProperties: dynamic, // Message properties.
    AdditionalProperties: dynamic, // Additional properties.
    TrackingId: string, // Any custom tracking id.
    AgreementName: string, // Name of the agreement to which the messages are resolved.
    SenderPartnerName: string, // Message sender's partner name.
    ReceiverPartnerName: string, // Message receiver's partner name.
    SenderQualifier: string, // Send partner qualifier.
    SenderIdentifier: string, // Send partner identifier.
    ReceiverQualifier: string, // Receive partner qualifier.
    ReceiverIdentifier: string, // Receive partner identifier.
    TransactionSetControlNumber: string, // Transaction set control number.
    FunctionalGroupControlNumber: string, // Functional group control number.
    InterchangeControlNumber: string, // Interchange control number.
    MessageType: string, // Transaction set or document type.
    RespondingTransactionSetControlNumber: string, //The responding transaction set control number in case of acknowledgement message.
    RespondingFunctionalGroupControlNumber: string, // The responding interchange control number in case of acknowledgement.
    RespondingInterchangeControlNumber: string, // The responding interchange control number in case of acknowledgement.
    ProcessingStatus: string // Processing status of the acknowledgment with these permitted values: Received, Generated, and Sent
}

Message Properties JSON schema

Both As2TrackRecords and EdiTrackRecords table has a MessageProperties column which is of dynamic type having different JSON schema for different types of track records.

AS2 Message Track Record Message Properties schema

{  
      "direction": "", 
       "messageId": "", 
       "dispositionType": "",  
       "fileName": "", 
       "isMessageFailed": "", 
       "isMessageSigned": "",  
       "isMessageEncrypted": "", 
       "isMessageCompressed": "", 
       "correlationMessageId": "", 
       "incomingHeaders": {}, 
       "outgoingHeaders": {},  
       "isNrrEnabled": "", 
       "isMdnExpected": "",  
       "mdnType": ""  
}

The following table describes the properties in the JSON schema for message properties in an AS2 message track record.

Property	Type	Description
direction	String	Direction of the message flow, which is either receive or send
messageId	String	AS2 message ID.
dispositionType	String	Message Disposition Notification (MDN) disposition type value
fileName	String	File name from the header of the AS2 message
isMessageFailed	Boolean	Whether the AS2 message failed
isMessageSigned	Boolean	Whether the AS2 message was signed
isMessageEncrypted	Boolean	Whether the AS2 message was encrypted
isMessageCompressed	Boolean	Whether the AS2 message was compressed
correlationMessageId	String	AS2 message ID, to correlate messages with MDNs
incomingHeaders	Dictionary of JToken	Incoming AS2 message header details
outgoingHeaders	Dictionary of JToken	Outgoing AS2 message header details
isNrrEnabled	Boolean	Whether to use default value if the value isn't known
isMdnExpected	Boolean	Whether the MDN is expected.
mdnType	Enum	Allowed values: NotConfigured, Sync, and Async

As2 MDN Track Record Message Properties schema

{
      "direction": "",
      "messageId": "",
      "originalMessageId": "",
      "dispositionType": "",
      "isMessageFailed": "",
      "isMessageSigned": "",
      "isNrrEnabled": "",
      "statusCode": "",
      "micVerificationStatus": "",
      "correlationMessageId": "",
      "incomingHeaders": {
      },
      "outgoingHeaders": {
      }
   }

The following table describes the properties in the JSON schema for message properties in an AS2 MDN track record.

Property	Type	Description
direction	String	Direction of the message flow, which is either receive or send
messageId	String	AS2 message ID
originalMessageId	String	AS2 original message ID
dispositionType	String	MDN disposition type value
isMessageFailed	Boolean	Whether the AS2 message failed
isMessageSigned	Boolean	Whether the AS2 message was signed
isNrrEnabled	Boolean	Whether to use the default value if the value isn't known
statusCode	Enum	Allowed values: Accepted, Rejected, and AcceptedWithErrors
micVerificationStatus	Enum	Allowed values: NotApplicable, Succeeded, and Failed
correlationMessageId	String	Correlation ID, which is the ID for the original message that has the MDN configured
incomingHeaders	Dictionary of JToken	Incoming message header details
outgoingHeaders	Dictionary of JToken	Outgoing message header details

X12 transaction set Track Record Message Properties schema

{
      "direction": "",
      "interchangeControlNumber": "",
      "functionalGroupControlNumber": "",
      "transactionSetControlNumber": "",
      "CorrelationMessageId": "",
      "messageType": "",
      "isMessageFailed": "",
      "isTechnicalAcknowledgmentExpected": "",
      "isFunctionalAcknowledgmentExpected": "",
      "needAk2LoopForValidMessages": "",
      "segmentsCount": ""
   }

The following table describes the properties in the JSON schema for message properties in a X12 transaction set track record.

Property	Type	Description
direction	Enum	Direction of the message flow, which is either receive or send
interchangeControlNumber	String	Interchange control number
functionalGroupControlNumber	String	Functional control number
transactionSetControlNumber	String	Transaction set control number
CorrelationMessageId	String	Correlation message ID, which is a combination of {AgreementName}{GroupControlNumber}{TransactionSetControlNumber}
messageType	String	Transaction set or document type
isMessageFailed	Boolean	Whether the X12 message failed
isTechnicalAcknowledgmentExpected	Boolean	Whether the technical acknowledgment is needed
isFunctionalAcknowledgmentExpected	Boolean	Whether the functional acknowledgment is needed
needAk2LoopForValidMessages	Boolean	Whether the AK2 loop is required for a valid message
segmentsCount	Integer	Number of segments in the X12 transaction set

X12 transaction set acknowledgment Track Record Message Properties schema

{
      "direction": "",
      "interchangeControlNumber": "",
      "functionalGroupControlNumber": "",
      "respondingfunctionalGroupControlNumber": "",
      "respondingFunctionalGroupId": "",
      "respondingtransactionSetControlNumber": "",
      "respondingTransactionSetId": "",
      "statusCode": "",
      "processingStatus": "",
      "CorrelationMessageId": "",
      "isMessageFailed": "",
   }

The following table describes the properties in the JSON schema for message properties in a X12 transaction set acknowledgement track record.

Property	Type	Description
direction	Enum	Direction of the message flow, which is either receive or send
interchangeControlNumber	String	Interchange control number of the functional acknowledgment. The value populates only for the send side where functional acknowledgment is received for the messages sent to partner.
functionalGroupControlNumber	String	Functional group control number of the functional acknowledgment. The value populates only for the send side where functional acknowledgment is received for the messages sent to partner.
respondingfunctionalGroupControlNumber	String	The responding interchange control number
respondingFunctionalGroupId	String	The responding functional group ID, which maps to AK101 in the acknowledgment
respondingtransactionSetControlNumber	String	The responding transaction set control number
respondingTransactionSetId	String	The responding transaction set ID, which maps to AK201 in the acknowledgment
statusCode	Boolean	Transaction set acknowledgment status code
segmentsCount	Enum	Acknowledgment status code with these permitted values: Accepted, Rejected, and AcceptedWithErrors
processingStatus	Enum	Processing status of the acknowledgment with these permitted values: Received, Generated, and Sent
CorrelationMessageId	String	Correlation message ID, which is a combination of {AgreementName}{GroupControlNumber}{TransactionSetControlNumber}
isMessageFailed	Boolean	Whether the X12 message failed

X12 interchange Track Record Message Properties schema

{
      "direction": "",
      "interchangeControlNumber": "",
      "isTechnicalAcknowledgmentExpected": "",
      "isMessageFailed": "",
      "isa09": "",
      "isa10": "",
      "isa11": "",
      "isa12": "",
      "isa14": "",
      "isa15": "",
      "isa16": ""
   }

The following table describes the properties in the JSON schema for message properties in a X12 interchange track record.

Property	Type	Description
direction	Enum	Direction of the message flow, which is either receive or send
interchangeControlNumber	String	Interchange control number
isTechnicalAcknowledgmentExpected	Boolean	Whether the technical acknowledgment is configured in the X12 agreement
isMessageFailed	Boolean	Whether the X12 message failed
isa09	String	X12 document interchange date
isa10	String	X12 document interchange time
isa11	String	X12 interchange control standards identifier
isa12	String	X12 interchange control version number
isa14	String	X12 acknowledgment is requested
isa15	String	Indicator for test or production
isa16	String	Element separator

X12 interchange acknowledgment Track Record Message Properties schema

{
      "direction": "",
      "interchangeControlNumber": "",
      "respondingInterchangeControlNumber": "",
      "isMessageFailed": "",
      "statusCode": "",
      "processingStatus": "",
      "ta102": "",
      "ta103": "",
      "ta105": ""
   }

The following table describes the properties in the JSON schema for message properties in a X12 interchange acknowledgement track record.

Property	Type	Description
direction	Enum	Direction of the message flow, which is either receive or send
interchangeControlNumber	String	Interchange control number of the technical acknowledgment that's received from partners
respondingInterchangeControlNumber	String	Interchange control number for the technical acknowledgment that's received from partners
isMessageFailed	Boolean	Whether the X12 message failed
statusCode	Enum	Interchange acknowledgment status code with these permitted values: Accepted, Rejected, and AcceptedWithErrors
processingStatus	Enum	Acknowledgment status with these permitted values: Received, Generated, and Sent
ta102	String	Interchange date
ta103	String	Interchange time
ta105	String	Interchange note code

X12 functional group Message Properties schema

{
      "direction": "",
      "interchangeControlNumber": "",
      "functionalGroupControlNumber": "",
      "isTechnicalAcknowledgmentExpected": "",
      "isFunctionalAcknowledgmentExpected": "",
      "isMessageFailed": "",
      "gs01": "",
      "gs02": "",
      "gs03": "",
      "gs04": "",
      "gs05": "",
      "gs07": "",
      "gs08": ""
   }

The following table describes the properties in the JSON schema for message properties in a X12 functional group track record.

Property	Type	Description
direction	Enum	Direction of the message flow, either receive or send
interchangeControlNumber	String	Interchange control number
functionalGroupControlNumber	String	Functional control number
isTechnicalAcknowledgmentExpected	Boolean	Whether the technical acknowledgment is configured in the X12 agreement
isFunctionalAcknowledgmentExpected	Boolean	Whether the functional acknowledgment is configured in the X12 agreement
isMessageFailed	Boolean	Whether the X12 message failed
gs01	String	Functional identifier code
gs02	String	Application sender's code
gs03	String	Application receiver's code
gs04	String	Functional group date
gs05	String	Functional group time
gs07	String	Responsible agency code
gs08	String	Identifier code for the version, release, or industry

X12 functional group acknowledgment Message Properties schema

{
      "direction": "",
      "interchangeControlNumber": "",
      "functionalGroupControlNumber": "",
      "respondingfunctionalGroupControlNumber": "",
      "respondingFunctionalGroupId": "",
      "isMessageFailed": "",
      "statusCode": "",
      "processingStatus": "",
      "ak903": "",
      "ak904": "",
      "ak9Segment": ""
   }

The following table describes the properties in the JSON schema for message properties in a X12 functional group acknowledgement track record.

Property	Type	Description
direction	Enum	Direction of the message flow, which is either receive or send
interchangeControlNumber	String	Interchange control number, which populates for the send side when a technical acknowledgment is received from partners
functionalGroupControlNumber	String	Functional group control number of the technical acknowledgment, which populates for the send side when a technical acknowledgment is received from partners
respondingfunctionalGroupControlNumber	String	Control number of the original functional group
respondingFunctionalGroupId	String	Maps to AK101 in the acknowledgment functional group ID
isMessageFailed	Boolean	Whether the X12 message failed
statusCode	Enum	Acknowledgment status code with these permitted values: Accepted, Rejected, and AcceptedWithErrors
processingStatus	Enum	Processing status of the acknowledgment with these permitted values: Received, Generated, and Sent
ak903	String	Number of transaction sets received
ak904	String	Number of transaction sets accepted in the identified functional group
ak9Segment	String	Whether the functional group identified in the AK1 segment is accepted or rejected, and why

WorkflowRunOperationInfo Column JSON schema

WorkflowRunOperationInfo column of both AS2TrackRecords and EdiTrackRecords table captures details about the standard logic apps action. Please find JSON schema for Workflow run operation information.

{
"title": "WorkflowRunOperationInfo",
"type": "object",
"properties": {
"Workflow": {
"type": "object",
"properties": {
"SystemId": {
"type": "string",
"description": "The workflow system id."
},
"SubscriptionId": {
"type": "string",
"description": "The subscription id of the workflow."
},
"ResourceGroup": {
"type": "string",
"description": "The resource group name of the workflow."
},
"LogicAppName": {
"type": "string",
"description": "The logic app name of the workflow."
},
"Name": {
"type": "string",
"description": "The name of the workflow."
},
"Version": {
"type": "string",
"description": "The version of the workflow."
}
}
},
"RunInstance": {
"type": "object",
"properties": {
"RunId": {
"type": "string",
"description": "The logic app run id."
},
"TrackingId": {
"type": "string",
"description": "The tracking id of the run."
},
"ClientTrackingId": {
"type": "string",
"description": "The client tracking id of the run."
}
}
},
"Operation": {
"type": "object",
"properties": {
"OperationName": {
"type": "string",
"description": "The logic app operation name."
},
"RepeatItemScopeName": {
"type": "string",
"description": "The repeat item scope name."
},
"RepeatItemIndex": {
"type": "integer",
"description": "The repeat item index."
},
"RepeatItemBatchIndex": {
"type": "integer",
"description": "The index of the repeat item batch."
},
"TrackingId": {
"type": "string",
"description": "The tracking id of the logic app operation."
},
"CorrelationId": {
"type": "string",
"description": "The correlation id of the logic app operation."
},
"ClientRequestId": {
"type": "string",
"description": "The client request id of the logic app operation."
},
"OperationTrackingId": {
"type": "string",
"description": "The operation tracking id of the logic app operation."
}
}
}
}
}