Reliable B2B Tracking Database Schema

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.

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.

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.

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.

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.

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.

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.

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.

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."
        }
      }
    }
  }
}