"alterIntegrationTable" (JSON Action)

{
    "result": {},
    "requestId": "1",
    "errorCode": 0,
    "errorMessage": "",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

{
    "result": {},
    "requestId": "5",
    "errorCode": 100,
    "errorMessage": "Not able to find integration table by name [test3/admin/faircom].",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

{
    "result": {},
    "requestId": "5",
    "errorCode": 100,
    "errorMessage": "Transform pipeline [firstCreateMe] was not found.",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

{
  "authToken": "replaceWithAuthTokenFromCreateSession",
  "result": {},
  "requestId": "00000001",
  "debugInfo": {
    "request": {
      "authToken": "replaceWithAuthTokenFromCreateSession",
      "api": "hub",
      "action": "alterIntegrationTable",
      "params": {
        "databaseName": "faircom",
        "tableName": "test1",
        "rebuildTable": false,
        "retentionPolicy": "doNotPersist",
        "retentionPeriod": 1,
        "retentionUnit": "day"
      },
      "requestId": "00000001",
      "debug": "max"
    }
  },
  "errorCode": 12006,
  "errorMessage": "Before you can change the data retention policy, period, or unit, you must use "alterIntegrationTable" and add "rebuildTable": true to the request. WARNING: rebuilding a table disrupts data collection while it rewrites all records."
}

The "addFields" property is optional. It is an array of objects. It allows you to add new fields to a table and specify their properties. Each object in the array defines the properties of a field being added.

"addFields": [
  {
    "autoValue": "none",
    "name": "field1",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false,
    "primaryKey":1
  }
]

The "alterFields" property is optional. It is an array of objects. It allows you to modify properties of existing fields in a table. Each object in the array defines the modifications to the named field.

In this example, the field named "field1" is being modified.

"alterFields": [
  {
    "autoValue": "none",
    "name": "field1",
    "type": "varchar",
    "newName": "address",
    "newPosition": 5,
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false
  }
]

The "databaseName" property is an optional string from 1 to 64 bytes that specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session, see "createSession" and the "defaultDatabaseName" property. 

You specify this property when you want to use a different database instead of the default. Your session's account must have the appropriate privileges to access the code package.

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

The "deleteFields" property is an optional array of strings. Each string is the name of a field to remove from a table.

The optional "disableTransformSteps" property has a true or false value. When true, it prevents the server from running an integration table's transform steps on newly inserted records. It is useful to quickly stop a broken transform process. It disables transform steps without removing them from the integration table.

The optional "logTransformOverwrites" property has a true or false value and defaults to true. When true and a record is inserted into the integration table, the server adds log entries to the log field when a transform step overwrites a field that already contains a value. When true, it protects fields, such as source_payload, from being overwritten accidentally. 

You typically set "logTransformOverwrites" to true when testing transform steps. Once they are working as expected, you can set it to false.

If multiple "recordPath" properties write to the same JSON properties in a "tableFieldsToJson" transform, the server will return an error because "recordPath" cannot overwrite. Multiple occurrences of the "recordPath" property may reference the same property as long as the property is in different fields/tables.

Additionally, non-JavaScript transform steps cannot overwrite protected fields or the source_payload field. The only exception is that a single "tableFieldsToJson" transform step may write to the source_payload field.

You may create transform steps to take the value of one JSON property and store it in multiple fields as long as no previous transform steps have already put values in these fields. Conversely, you may take the value of one field and store it in multiple JSON properties.

The "metadata" property is an optional JSON object. It exists primarily for the user interface to find integration information. By default, it is an empty JSON object.

{
  "description": "",
  "tags": [""],
  "yourOwnProperties": "usage, purpose, notes, location, etc.",
}

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object, such as a table or code package. If it is set to null or is omitted, it defaults to the account that created the JSON Action session, see "createSession" and the "defaultOwnerName" property. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

The "retentionPeriod" property specifies how many units of data to retain. It must be an integer value from 1 to 100. It refers to the unit of time specified by the "retentionUnit" property — for example, if "retentionPeriod" is 14 and "retentionUnit" is "day", then message data is retained for 14 days. This property is optional.

When partitions are auto-purged, some data are maintained "forever" in a global index. Auto-purge does not prevent these files from growing without bounds and filling up your hard drive.

If not specified, the default found in the services.json file is used. Initially, it is 4 (weeks).

Automatically purging data is important to ensure that retained data does not consume all storage and shut down the computer. The default value of 4 weeks allows FairCom's servers to store 1 TB of messages when 200 topics send one 2K message per second.

The "retentionPolicy" property controls how messages are persisted. This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "autoPurge".

Each time this unit cycles, FairCom purges expired messages. For example, if you want a week's worth of messages to be purged once a week, set "retentionUnit" to "week" . This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "week". 

The "tableName" property contains the name of the table in the database where the event occurred. It is a non-zero-length string.

See table name in System limits for table naming requirements and limitations.

A table in DBnotify is defined by "databaseName", "ownerName" and "tableName" or by "dataFilePath".

The "transformSteps" property is an array of transform step objects. 

Different actions interpret "transformSteps": [] differently.

Each transform step object contains the required "transformStepMethod" property. It also supports the following optional properties:

Each transform step method also supports unique properties that are defined below.

The "codeName" property is a required string from 1 to 64 bytes. It is the name of a code package that runs when a record is inserted into an integration table. See "codeName" for more information.

It is an error to set "codeName" to the empty string "".

The "databaseName" property is an optional string from 1 to 64 bytes that specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session, see "createSession" and the "defaultDatabaseName" property. 

You specify this property when you want to use a different database instead of the default. Your session's account must have the appropriate privileges to access the code package.

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object, such as a table or code package. If it is set to null or is omitted, it defaults to the account that created the JSON Action session, see "createSession" and the "defaultOwnerName" property. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

The "mapOfPropertiesToFields" property takes fields in the table and maps them to a field containing JSON properties. It is an array of property map objects. Each object maps a field in a table to a JSON property in another field.

The optional "dateFormat" property specifies the format of a date or a datetime embedded in a JSON string. It applies to user-provided JSON and to server-generated JSON. The server uses it when it needs to transform or convert a date or datetime embedded in a JSON string into a FairCom date or timestamp. It also uses it when it needs to convert a date or datetime field value into a date embedded in a JSON string.

It is an enumerated string with one of the following values: "ccyy.mm.dd", "mm.dd.ccyy", "mm.dd.yy", "dd.mm.ccyy", "dd.mm.yy", "ccyymmdd", "yymmdd", "iso8601", and "utc".

The default value for "dateFormat" is the "defaultDateFormat" property defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultDateFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "iso8601" because the ISO8601 date is the defacto standard in JSON.

The enumerated string defines how the server parses a date in a JSON string and how it writes a date into a JSON string. The following key explains the parts of each enumerated value:

The required "fieldName" property specifies a field in a record. Depending on the context, the server reads or writes the field's value. 

For example, in the "tableFieldsToJson" transform method, "fieldName" is the location where the transform step reads a value that it transforms.  In the "jsonToTableFields" and "jsonToDifferentTableFields" transform methods, "fieldName" is the location where the transform step writes a transformed value.

The "numberFormat" property is an optional, case-insensitive string enum. It defines the format of JSON numbers. The default value for "numberFormat" is the "defaultNumberFormat" defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultNumberFormat" property in the <faircom>/config/services.json file.

When "numberFormat" occurs in "mapOfPropertiesToFields", it tells the server how to encode or decode a number assigned to a JSON property.

For example, including "numberFormat" in a "tableFieldsToJson" transform step controls if the server encodes a number in a JSON property as a number or a number embedded in a string.

Example request

{
  "action": "someAction",
  "responseOptions":
  {
    "numberFormat": "string"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

The "recordPath" property specifies the location in a record where the server reads or writes a JSON value. It specifies a field name followed by an optional JSONPath. Depending on the context, it refers to a value the server reads or writes. 

For example, in the "tableFieldsToJson" transform method, "recordPath" is the location where the transform step writes a transformed value.  In the "jsonToTableFields" and "jsonToDifferentTableFields" transform methods, "recordPath" is the location where the transform step reads the value that it transforms.

Several FairCom APIs, such as the DB and Transform APIs, treat each record in each table as a JSON document. The top-level fields in the record are treated as top-level properties in the JSON document. In other words, you can think of each record as a JSON document, and you can use the "recordPath" property to refer to any field in the record and any JSON property or array item within a JSON field.

When the server writes a value to a "recordPath", it looks for the specified field and JSONPath and does the following.

When the server reads a value from a "recordPath", it looks for the specified field and JSONPath and does the following.

{
  "humidity": 
  [
    {
      "temperature": 20.1,
      "pressure": 1003
    }
  ]
}

{
  "id": 1,
  "source_payload": 
  {
    "humidity": 
    [
      {
        "temperature": 20.1,
        "pressure": 1003
      }
    ]
  },
  "create_ts": "2025-07-07T14:14:02.012"
}

{
  "humidity": 
  [
    {
      "temperature": 20.1,
      "pressure": 1003
    }
  ]
}

[
  {
    "temperature": 20.1,
    "pressure": 1003
  }
]

{
  "temperature": 20.1,
  "pressure": 1003
}

The optional "timeFormat" property specifies the format of a time or a datetime embedded in a JSON string. The server uses it when it needs to transform or convert a time or datetime embedded in a JSON string into a FairCom time or timestamp. It also uses it when it needs to convert a time or datetime field value into a time embedded in a JSON string.

It is an enumerated string with one of the following values: "hh.mm.ss.ttt", "hh.mm.am/pm", "hh.mm.ss.am/pm", "hh.mm.ss", "hh.mm", "hhmm", "iso8601", and "utc".

The default value for "timeFormat" is the "defaultTimeFormat" property defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultTimeFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "iso8601" because the ISO8601 date is the defacto standard in JSON.

The enumerated string defines how the server parses a time in a JSON string. The following key explains the parts of each enumerated value:

The "variantFormat" property tells the server how to format the values of variant fields in its response to your request.

The "variantFormat" property has one of the following values: "binary", "json", "string", and "variantObject". It tells the server how to store and return values stored in variant fields. The default value is "json".

The "targetDatabaseName" property is the optional name of the database that contains the target table. See

"databaseName" for more information.

The "targetOwnerName" property is the optional name of the account that owns the target table. See "ownerName" for more information.

The required "targetTableName" property specifies the name of the target table that has its transform steps replaced by this action.

The "transformStepMethod" property is a required string that specifies the type of transform, such as the "javascript" transform method that runs JavaScript to change the table's data or the "jsonToTableFields" transform method that extracts values from properties in a JSON field and stores them in other fields.

The value of the "transformStepMethod" affects the value of the "transformService" property. The following table defines the possible values of the "transformService" property when combined with the "transformStepMethod". Notice that some transform step methods are built into the server and do not require you to specify the "transformService".

The "transformStepName" property is an optional string that assigns a name to each transform step.

The "transformStepService" property is an optional string that specifies the name of a transform service, which is the user-defined name for a library (.dll, .so, .dylib) that implements the transform step method.

This property allows you to register your own transform libraries or use an older version of a FairCom library for backward compatibility.

Transform services are defined in the services.json file under the "transformServices" section. You can add and remove transform libraries to this list, such as your own transform libraries or specific versions of FairCom's transform libraries. Use the "serviceName" property to give each transform library a unique transform service name and use the "serviceLibrary" property to specify the filename of the library that implements the transform step method. On Windows the library filename ends with .dll. On Linux it ends with .so. On MacOS it ends with .dylib. Lastly, you must enable the transform service by setting "enabled" to true.

Example "transformServices" section in the services.json file.

"transformServices": [
  {
    "serviceName": "v8TransformService",
    "serviceLibrary": "v8transformservice.dll",
    "enabled": true
  }
],