Data Model Management
Introduction
While your Data Model and the profile/custom tables that make it are usually created by the Actito Integration experts (in coordination with you), the Actito API allows you to fully manage your tables.
This gives you full autonomy over your data model. For instance, you can retrieve the structure of an existing table to create a new one, you can add new fields to a table or create links between them.
The following use case will show you how to:
- Retrieve the structure of an existing custom table
- Create a new custom table
- Update a custom table
Modifying your data model can impact your automated data synchronizations.
Please consider that you might need update them too.
In case of doubt, we advise to contact account manager for the assistance of an Integration & Data Expert.
1. Retrieving the structure of an existing custom table
If you want to copy the structure of an existing custom table (used temporarily for testing, for instance), you can retrieve the structure of all custom tables in an entity with the call
GET /custom-table-structure/v5/entities/{entity}/custom-tables
If you already know the ID of the table, you can retrieve the structure of a single table with the call
GET /custom-table-structure/v5/entities/{entity}/custom-tables/{customTableId}
You will get the JSOn definition of the table as response.
{
"id": "a4aa57cd-64c4-49bc-a17b-34ac8dfa76f8",
"name": "OnlineOrders",
"type": "INTERACTIONS",
"primaryKeyAttribute": "orderId",
"creationTimeAttribute": "orderMoment",
"bigTable": false,
"attributes": [
{
"name": "orderId",
"valueType": "STRING",
"mandatory": true,
"indexed": true,
"unique": true,
"valueRestriction": {
"minLength": 20,
"maxLength": 20
}
},
{
"name": "storeId",
"valueType": "LONG",
"mandatory": true,
"indexed": true,
"unique": false
},
{
"name": "customerId",
"valueType": "LONG",
"mandatory": true,
"indexed": true,
"unique": false
},
{
"name": "orderMoment",
"valueType": "TIMESTAMP",
"mandatory": true,
"indexed": true,
"unique": false
},
{
"name": "amount",
"valueType": "NUMBER",
"mandatory": true,
"indexed": false,
"unique": false
},
{
"name": "status",
"valueType": "STRING",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": {
"acceptedValues": [
"NEW",
"SHIPPING",
"SHIPPED",
"CANCELED"
]
}
}
],
"foreignKeys": [
{
"name": "link-to-customer",
"attribute": "customerId",
"reference": {
"tableType": "PROFILE_TABLE",
"tableId": "97",
"attribute": "customerId"
},
"onDelete": "CASCADE"
},
{
"name": "link-to-store",
"attribute": "storeId",
"reference": {
"tableType": "CUSTOM_TABLE",
"tableId": "d4d85214-66d4-4072-b98b-a86c9253b980",
"attribute": "storeId"
},
"onDelete": "CASCADE"
}
],
"eventsToTrigger": [
{
"name": "shipped-online-order",
"triggerRules": [
{
"onOperation": "UPDATE",
"onAnyAttribute": [
"status"
],
"matchingConditions": {
"stateBeforeOperation": [
{
"attribute": "status",
"operator": "!=",
"value": "SHIPPED"
}
],
"stateAfterOperation": [
{
"attribute": "status",
"operator": "=",
"value": "SHIPPED"
}
]
}
}
]
}
],
"cleaningRule": {
"type": "OLDEST_RECORDS",
"numberOfRecordsToKeep": 200000
},
"displayOptions": {
"displayName": "Online Orders",
"description": "The table that hosts the orders from an online store. Those orders are linked to the Customers profile table.",
"forAttributes": [
{
"name": "orderId",
"description": "The unique id of the order",
"displayName": "Online Orders ID"
},
{
"name": "storeId",
"description": "The unique id of the online store on which the order has been created.",
"displayName": "Store ID"
},
{
"name": "customerId",
"description": "The unique id of customer who has created the order.",
"displayName": "Customer ID"
},
{
"name": "orderMoment",
"description": "The moment when the order has been created",
"displayName": "Order Moment"
},
{
"name": "amount",
"description": "The amount of the order",
"displayName": "Amount"
},
{
"name": "status",
"description": "The status of the order",
"displayName": "Status"
}
],
"forEvents": [
{
"name": "shipped-online-order",
"displayName": "Shipped Online Order"
}
]
},
"_audit": {
"createdAt": "2019-11-21T08:30:00.000Z",
"updatedAt": "2019-11-21T08:30:00.000Z"
},
"_links": {
"self": {
"href": "/custom-table-structure/v5/entities/MyEntity/custom-tables/69763d8f-2ff2-4fbb-aa9e-8c5ebccb258f"
}
}
}
2. Creating a new custom table
You can use the following call to create a new custom table:
POST /custom-table-structure/v5/entities/{entity}/custom-tables
You can copy the definition of a table you retrieved previously, or build a new table structure using the specs.
If you want to use the definition you retrieved at the previous step to create a new table, you will need to remove the "id", "creationMoment" and "updateMoment" objects from the "attributes" and the "displayOptions" arrays, because these fields are created automatically by Actito.
{
"name": "OnlineOrders",
"type": "INTERACTIONS",
"primaryKeyAttribute": "orderId",
"creationTimeAttribute": "orderMoment",
"compositeKey": null,
"bigTable": false,
"attributes": [
{
"name": "orderId",
"valueType": "STRING",
"mandatory": true,
"indexed": true,
"unique": true,
"valueRestriction": {
"minLength": 20,
"maxLength": 20
}
},
{
"name": "storeId",
"valueType": "LONG",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": null
},
{
"name": "customerId",
"valueType": "LONG",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": null
},
{
"name": "orderMoment",
"valueType": "TIMESTAMP",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": null
},
{
"name": "amount",
"valueType": "NUMBER",
"mandatory": true,
"indexed": false,
"unique": false,
"valueRestriction": null
},
{
"name": "status",
"valueType": "STRING",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": {
"acceptedValues": [
"NEW",
"SHIPPING",
"SHIPPED",
"CANCELED"
]
}
}
],
"valueAttribute": "amount",
"foreignKeys": [
{
"name": "link-to-customer",
"attribute": "customerId",
"reference": {
"tableType": "PROFILE_TABLE",
"tableId": "97",
"attribute": "customerId"
},
"onDelete": "CASCADE"
},
{
"name": "link-to-store",
"attribute": "storeId",
"reference": {
"tableType": "CUSTOM_TABLE",
"tableId": "d4d85214-66d4-4072-b98b-a86c9253b980",
"attribute": "storeId"
},
"onDelete": "CASCADE"
}
],
"eventsToTrigger": [
{
"name": "shipped-online-order",
"triggerRules": [
{
"onOperation": "UPDATE",
"onAnyAttribute": [
"status"
],
"matchingConditions": {
"stateBeforeOperation": [
{
"attribute": "status",
"operator": "!=",
"value": "SHIPPED"
}
],
"stateAfterOperation": [
{
"attribute": "status",
"operator": "=",
"value": "SHIPPED"
}
]
}
}
]
}
],
"cleaningRule": {
"type": "OLDEST_RECORDS",
"numberOfRecordsToKeep": 200000
},
"displayOptions": {
"displayName": "Online Orders",
"description": "The table that hosts the orders from an online store. Those orders are linked to the Customers profile table.",
"forAttributes": [
{
"name": "orderId",
"description": "The unique id of the order",
"displayName": "Online Orders ID"
},
{
"name": "storeId",
"description": "The unique id of the online store on which the order has been created.",
"displayName": "Store ID"
},
{
"name": "customerId",
"description": "The unique id of customer who has created the order.",
"displayName": "Customer ID"
},
{
"name": "orderMoment",
"description": "The moment when the order has been created",
"displayName": "Order Moment"
},
{
"name": "amount",
"description": "The amount of the order",
"displayName": "Amount"
},
{
"name": "status",
"description": "The status of the order",
"displayName": "Status"
}
],
"forEvents": [
{
"name": "shipped-online-order",
"displayName": "Shipped Online Order"
}
]
}
}
Field value types
Depending on their types, the fields must follow specific constraints:
-
STRING: string of characters [a-z, A-Z, 0-9]
- the default max number of characters for a STRING type is 255. It can be modified with a "typeValidator" parameter (see table above)
-
LONG:
- a 64bit integer (no space, dot or comma in number)
- maximum value: 9223372036854775808
- minimum value: -9223372036854775808
-
BOOLEAN: match one of the following values “true” or “false”
-
DATE: string of characters matching one of the following formats:
- YYYYMMDD
- YYYY-MM-DD
- dd/MM/yyyy
-
TIMESTAMP: string of characters matching one of the following formats:
- YYYYMMDD *
- YYYY-MM-DD *
- dd/MM/yyyy *
- YYYYMMDDhhmmss
- YYYY-MM-DD hh:mm:ss
- dd/MM/yyyy HH:mm:ss
- MM/dd/yyyy hh:mm:ss AM|PM
- If no values are specified for "hhmmss", the values "00:00:00" will be added
-
NUMERIC:
- separator must be '.'
- no character amount limit
Creating a table in the UI
Users of the Actito interface can also create new custom table in the "Manage table structure" app.
They can import a JSON file that follows the same structure as in the API.
The only difference is that they must add an "entityName" parameter to specify the entity on which the new table is created (in the API, the entity name is passed in the path of the call).
{
"name": "ORders24-12-1",
"entityName":"actito",
"type": "INTERACTIONS",
[...]
3. Updating a custom table
A custom table can be updated by applying an operation on the table meta data (change user name, description etc), on existing fields (change user name, data validation etc), or on the table structure (add a relation, index a field, etc).
These updates are called "change requests" and are applied asynchronously. By retrieving the status of a change request, you can check if it was successful.
A change request can be created with the following call:
POST /custom-table-structure/v5/entities/{entity}/custom-tables/{customTableId}/change-requests
The body of the call depends on the type of change request. In the API specs, use the One of tabs to display the relevant documentation.
Add an attribute
Example of JSON body to add a new field to a table:
A mandatory attribute cannot be added to an already populated table.
{
"on": "TABLE",
"type": "ADD_ATTRIBUTE",
"attribute": {
"name": "My_New_Attribute",
"valueType": "STRING",
"mandatory": false,
"indexed": false,
"unique": false,
"valueRestriction": {
"maxLength": 30,
"minLength": 0
}
},
"displayOptions": {
"name": "My_New_Attribute",
"displayName": "My New Attribute"
}
}
Delete an attribute
Example of JSON body to delete an attribute from a table:
{
"on": "TABLE",
"type": "REMOVE_ATTRIBUTE",
"attributeName": "My_Old_Attribute"
}
Add a foreign key
Adding a foreign key means linking two tables together (for instance, 2 "Orders" and "Order Lines" Interaction tables, or an Interaction table with a Repository table).
This link is made on an attribute of the source table (the foreign key) that must match a key attribute of the target table.
The type of the 2 fields making the link between both table must have the same value type.
{
"on": "TABLE",
"type": "ADD_FOREIGN_KEY",
"foreignKey": {
"name": "link-to-stores-repository",
"attribute": "store",
"reference": {
"tableType": "CUSTOM_TABLE",
"tableId": "1f74f230-9069-4137-8ae6-d2e328721fae",
"attribute": "storeID"
},
"onDelete": "CASCADE",
"integrityCheck": true
}}
Remove a foreign key
Removing a foreign key means deleting the link between 2 tables.
{
"on": "TABLE",
"type": "REMOVE_FOREIGN_KEY",
"name": "link-to-stores-repository"
}
Delete table
This change request will delete the table and all the data stored in it.
This operation is irreversible. Please use with caution.
{
"on": "TABLE",
"type": "DELETE"
}
Make attribute mandatory
This change request will make an attribute mandatory.
This operation only works if every single record in the table already have a value for this attribute.
Otherwise, you will first need to create a data import to update existing records with a value.
{
"on": "ATTRIBUTE",
"type": "MAKE_MANDATORY",
"attributeName" : "MyAttribute"
}
Add index on a attribute
This operation will index the target attribute, which will allow users of the interface to make searches on this field and optimize the calculation of targetings.
Max 15 fields can be indexed per table.
Unique and technical fields are indexed by default.
Having too many indexes can slow down performances when creating and updating data.
{
"on": "ATTRIBUTE",
"type": "ADD_INDEX",
"attributeName" : "MyNewIndexedField"
}
Add accepted values for attribute
This operation will add accepted values to an attribute. This only works for attributes that were created with value restrictions.
{
"on": "ATTRIBUTE",
"type": "ADD_ACCEPTED_VALUES",
"attributeName": "MyAttribute",
"acceptedValues" : ["newValue1", "newValue2", "newValue3"]
}
Update table parameters - creationTimeAttribute
By default, the creation time attribute is the technical creationMoment generated by Actito when records are created.
Updating the "creationTimeAttribute" allow you to set your own field (an "OrderMoment", for example) as reference creation moment. It will be used:
- to sort the order of interactions in the UI
- to calculate the
OLDEST_RECORDScleaning rule - to calculate the recency in the Activation Matrix
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"creationTimeAttribute": "orderMoment"
}
Update table parameters - valueAttribute
The value attribute sets the monetary value of an interaction. It is used in calculations of the Activation Matrix
The value attribute can only be set to INTERACTIONS type tables.
The corresponding attribute must be numeric (NUMBER or LONG).
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"valueAttribute": "amount"
}
Update table parameters - bigTable
This defines whether the table is considered as BIG, which means the maximum number of records is 10 times higher than standard tables, but with some restrictions.
- Big Interactions tables: Max 100M lines - Max 20 attributes - No STRING attributes allowed
- Big Linked Data tables: Max 100M lines - Max 20 attributes - No STRING attributes allowed
- Big Repository tables: Max 100 000 lines - No "recordDisplayExpression" allowed in the display options (STRING allowed)
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"bigTable: true
}
Update table parameters - eventsToTrigger
Custom Table Events can be triggered when the state of a record changes (create, update, delete) with defined criteria.
These events can be used to trigger specific automations such as starting a Scenario in the UI or pushing a webhook notification.
In the following example, we are creating 2 events:
- The event named "CREATE" is triggered when a new record is created, without any additional criteria.
- The event named "offerReady" is triggered when
- An existing record is updated with the "synchronized" attribute changing from
falsetotrue - A new record is created with the "synchronized" attribute =
true
- An existing record is updated with the "synchronized" attribute changing from
{
"eventsToTrigger": [
{
"name": "CREATE",
"triggerRules": [
{
"onOperation": "CREATE"
}
]
},
{
"name": "offerReady",
"triggerRules": [
{
"onOperation": "UPDATE",
"onAnyAttribute": [
"synchronized"
],
"onMatchingState": {
"stateBeforeOperation": {
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "synchronized",
"value": false
}
]
},
"stateAfterOperation": {
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "synchronized",
"value": true
}
]
}
}
},
{
"onOperation": "CREATE",
"onMatchingState": {
"stateAfterOperation": {
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "synchronized",
"value": true
}
]
}
}
}
]
}
]
}
Update table parameters - cleaningRules
As custom tables have a max volume, cleaning rules help making room for new records by deleting the data that aren't relevant anymore.
There are two way to define cleaning rules:
- deleting the oldest records on a FIFO basis
- deleting records that match a set of criteria
Here is an example of change request to add a FIFO cleaning rule:
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"cleaningRules": {
"oldestRecordsRule": {
"numberOfRecordsToKeep": 2000000
}
}
}
Here is a change request to add a cleaning rule that deletes records whose "status" is "cancelled".
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"cleaningRules": {
"matchingRule": {
"condition": {
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "status",
"value": "CANCELLED"
}
]
}
}
}
}
It is possible to mix both types of conditions:
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"cleaningRules": {
"oldestRecordsRule": {
"numberOfRecordsToKeep": 5000000
},
"matchingRule": {
"condition": {
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "status",
"value": "CANCELLED"
}
]
}
}
}
}
It is also possible to create complex conditions with interlocking criteria. In the example below, a record will be deleted if its "status" is "cancelled" and its "amount" is below 50, or if it was made in the store with id = 2 and the order id is not known.
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"cleaningRules": {
"matchingRule": {
"condition": {
"logicalOperator": "OR",
"conditions": [
{
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "status",
"value": "CANCELED"
},
{
"operator": "<",
"attribute": "amount",
"value": 50.0
}
]
},
{
"logicalOperator": "AND",
"predicates": [
{
"operator": "=",
"attribute": "storeId",
"value": "2"
},
{
"operator": "EMPTY",
"attribute": "orderId"
}
]
}
]
}
}
}
}
Update table parameters - displayOptions
Display options allow you to define the display names and the descriptions of objects such as they will appear to the users of the interface.
This helps defining a clearer picture of the purpose of each object than the technical name might convey.
{
"on": "TABLE",
"type": "UPDATE_PARAMETERS",
"displayOptions": {
"displayName": "Online Orders",
"description": "The table that hosts the orders from an online store. Those orders are linked to the Customers profile table.",
"forAttributes": [
{
"name": "orderId",
"description": "The unique id of the order",
"displayName": "Online Orders ID"
},
{
"name": "storeId",
"description": "The unique id of the online store on which the order has been created.",
"displayName": "Store ID"
},
{
"name": "customerId",
"description": "The unique id of customer who has created the order.",
"displayName": "Customer ID"
},
{
"name": "orderMoment",
"description": "The moment when the order has been created",
"displayName": "Order Moment"
},
{
"name": "amount",
"description": "The amount of the order",
"displayName": "Amount"
},
{
"name": "status",
"description": "The status of the order",
"displayName": "Status"
}
],
"forEvents": [
{
"name": "shipped-online-order",
"displayName": "Shipped Online Order"
}
]
}
}
Updating a custom table from the UI
All the above operation can also be carried in the Actito UI by uploading a JSON file with the same structure in the 'Manage tables structure' app (except the "Delete table" operation which has its own button).
4. Checking the result of a change request
As change requests are asynchronous, just because the call to create one is in success doesn't mean the operation was carried out successfully. For instance, when creating a change request to make an existing attribute mandatory, the system will asynchronously check that all records already have a value for this attribute.
You can check the result of a change request with the following call:
GET /custom-table-structure/v5/entities/{entity}/custom-tables/{customTableId}/change-requests/{changeRequestId}
The response displays both the content of the change request and its status.
{
"id": "ae808e05-01e7-4299-99cf-f7dc190d5562",
"on": "TABLE",
"type": "ADD_ATTRIBUTE",
"attribute": {
"name": "description",
"valueType": "STRING",
"mandatory": false,
"indexed": false,
"unique": false
},
"status": "SUCCEEDED",
"_audit": {
"createdAt": "2019-11-21T13:00:00.000Z",
"updatedAt": "2019-11-21T13:01:00.000Z"
},
"_links": {
"self": {
"href": "/custom-table-structure/v5/entities/MyEntity/custom-tables/a4aa57cd-64c4-49bc-a17b-34ac8dfa76f8/change-requests/ae808e05-01e7-4299-99cf-f7dc190d5562"
},
"table": {
"href": "/custom-table-structure/v5/entities/MyEntity/custom-tables/a4aa57cd-64c4-49bc-a17b-34ac8dfa76f8"
}
}
}