Skip to main content

Integration Tables Concepts

Concepts for the Integration Tables API

The integration table is the core element of an event streaming platform. The following sections detail the information necessary to use the FairCom Integration Tables API actions effectively.

Debug

The debug field is a JSON array to which a JavaScript transform can append debugging information for troubleshooting purposes. It is designed as a flexible storage space for developers to add variable values, intermediate steps, or other relevant data during the transformation process. The transform system ensures the debug field is always an array before running each JavaScript transform, even if its initial value is null or another non-array JSON type, to preserve information from previous steps. To save space in the record, the platform automatically sets the debug field to null if it remains an empty array after all transformations are complete.

["some debug message",
  {
    "debugSource": "transformSteps",
    "transformStepName": "step1",
    "message": "variable2 = 33561"
  },
  {
    "variable1": "myString",
    "variable2": 33561
  },
  "End of transform step"
]

Integration fields

Each integration table record contains the fields shown in Figure 1, “Integration table fields”.

Integration table core fields:

  • Each record is automatically given a unique ID in the "id" field.

  • The server’s current UTC time is recorded in the "create_ts" field.

  • The incoming payload is stored in the "source_payload" field.

  • Unlimited transform processes can store their results in any field (other than the built-in fields).

  • Error messages and transform logs are stored in the "error"and "log" fields.

Figure 1. Integration table fields
Integration table fields


Since the MQTT engine requires additional fields to track message data and since any integration table can be connected to the MQTT engine, each integration table contains MQTT fields.

Plugins can also add fields to each integration table. This occurs automatically when such a plugin is added to FairCom Edge. It is done invisibly in the background with no performance impact because of the FairCom Hot Alter Table feature.

The SQL and JSON DB APIs should only write to the "source_payload" field and user-defined fields. They can query all fields.

Built-in fields in FairCom Edge integration tables

Table 1. Built-in fields in FairCom Edge integration tables

Field name

Field type

Nullable

Indexed

User Writable

Description

changeid

"bigint"

The server automatically populates its value with the server's transaction ID when a record is inserted or updated.

create_ts

"timestamp"

N

Y

The server automatically populates its value with a millisecond-precision UTC date and time when a record is inserted.

error

"bit"

N

N

true when there is an error. An error may occur when a transform process reports a failure.

id

"bigint"

N

Y

The identity field uniquely identifies each record. The server automatically populates its value with a unique, incremented integer when a record is inserted.

log

"lvarchar"

Y

N

null when there are no errors; otherwise, it contains a JSON object that records information about each transform error.

metadata

"json"

Y

Y

mqtt_client_id

"integer"

Y

Y

Foreign key to the unique identifier of a row in the mqtt_client table. Its value is equal to the client ID for the FairCom server minus 1. You can join an integration table with the mqtt_client table to connect messages with their publishers.

mqtt_content_type

"varchar(65500)"

Y

(MQTT 5) The publisher can specify a string that identifies the type of content in the MQTT payload (i.e. source_payload field). It is typically an IANA Media Type, such as "application/json".

mqtt_correlation_data

"lvarbinary"

Y

(MQTT 5) The publisher can assign unique correlation data to a message that subscribers should use when they reply to the message.

mqtt_expiry_seconds

"integer"

Y

(MQTT 5) The publisher optionally defines a number of seconds before a message expires. The server does not deliver messages to subscribers after they expire. The server always retains expired messages in the integration table until the table's retention policy removes them.

mqtt_forward_topic_id

"integer"

Y

Foreign key to the unique identifier of a row in the mqtt_topic table.

mqtt_packet_id

"integer"

Y

(MQTT) The publisher assigns a unique packet identifier to each QoS 1 and QoS 2 message to ensure its packets are processed together.

mqtt_payload_format

"tinyint"

Y

(MQTT 5) The publisher can specify the format of the payload.

0 means no payload format is specified, which implies a binary payload.
1 means the payload is UTF-8 encoded character data, such as JSON.

mqtt_published_qos

"tinyint"

Y

(MQTT) The publisher can set the QoS of each message it publishes:

0 provides no guarantees.
1 guarantees delivery at least once.
2 guarantees delivery once.

mqtt_response_topic

"varchar(65500)"

Y

(MQTT 5) The publisher can assign a response topic to a message, which is the topic subscribers should use for replies.

mqtt_retain

"tinyint"

Y

(MQTT) The publisher can set the retained message flag on an MQTT message to tell the server to deliver it automatically to new subscribers of that message's topic. The server only delivers the last retained message to new subscribers, but the integration table contains the history of which messages are marked as retained messages.

1 for a retained message.
0 for a normal message.

mqtt_sent_reason

"char(1)"

Y

Contains "c" when a client sends the message

mqtt_topic_id

"integer"

Y

Y

Foreign key to the unique identifier of a row in the mqtt_topic table. You can join an integration table with the mqtt_topic table to connect messages with their topics.

mqtt_user_properties

"json"

Y

(MQTT 5) The publisher can add user-defined properties to a message. These properties are metadata about the message. The properties are stored in this field as an array of JSON objects.

Plugin fields

plugin-defined

Y

N

A plugin can add additional fields. Each field is prefixed with a unique prefix — for example, MQTT uses the prefix "mqtt_".

source_payload

"variant"

Y

N

Y

The source_payload field contains the record's primary data. It is typically a JSON object. The payload of an MQTT message is stored in the source_payload field. When you insert records into an integration table, you should put a JSON object in the source_payload field.

Transform fields

transform-defined

Y

The transform pipeline assigned to an integration table may add fields to store its results. 

User-defined fields

user-defined

Y

N

Y

An application can create additional fields. The field names must not conflict with FairCom Edge field names.



Additional fields from the "jsonToDifferentTableFields" transform

Warning

These fields should not be manually updated.

Field

Description

source_database

The name of the database the data was pulled from.

source_id

The unique identifier of the record in the source table.

source_owner

The name of the owner of the source table the data was pulled from.

source_table

The name of the table the data was pulled from.

Metadata

The metadata field is a JSON object that attaches supplementary information to a record in an integration table. It is a flexible storage space that allows you to attach context, flags, or other relevant data that isn't part of the main data in the record. A JavaScript transform can access and manipulate this field through the record.metadata variable. The transform system ensures the metadata field is always an object before running each JavaScript transform step, and to optimize storage, the platform automatically sets the metadata field to null if it remains an empty object after all transformations are complete.

metadata:{
  "myProperty": "myValue",
  "propertyFromAPreviousStep": 27
}

The integration table

Integration tables are the heart of FairCom Edge. They are database tables that are enhanced and optimized for inserts, transformation processes, queries, and forwarding data asynchronously to output services. They make it possible to bridge data across otherwise incompatible protocols, data serialization formats, data shapes, and data types. All integration tables are created with only insert and read permissions. This is because an integration table is an immutable, audit log of events.

Input services insert data into integration tables. Output services read and deliver data from the integration tables.

Input services insert records, they are not allowed to change or delete records because that would destroy the integrity of the audit log. When an input source submits data for insertion, automatic transforms are created and stored in the record with the original data. It becomes read-only and can be queried by SQL and JSON DB APIs.

When an input service is configured, it is assigned to an existing integration table or it creates a new integration table. MQTT automatically creates an integration table for each new topic it creates. Integration processes can be refined and integration tables can be renamed to better label the data they hold. The "configureTopic" and "alterIntegrationTable" actions in the API are used to rename an integration table.

Figure 2, “The integration table” identifies the FairCom Edge components that interact with the integration table.

Figure 2. The integration table
The integration table