Admin Data Structure
Admin schema
Bot
Base informations from a virtual agent (VA), internally named bot. Most information regarding it is actually found within the environment table, but some information are relevant for authentication and user access delegation and are thus mirrored in the Admin schema.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
The bot's UUID.
environment_uuid
varchar(36)
Yes
UUID from the environment where the bot belongs
name
varchar(50)
Yes
The bot's Name
image_url
varchar(100)
No
The bot's image URL, if any
removed
tinyint(1)
No
True if the bot was deleted
created_at
datetime
No
The date this object was created
updated-at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Bucket Address
A bucket name table for Minio. Rather than using the uuid's from organization and environments, we actually map them into a fully random uuid for each distinct kinf of resource for granularization; this resulted uuid is found on file resource urls, such as images.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
This object's ID.
org_uuid
varchar(50)
Yes
A valid organization UUID.
env_uuid
varchar(50)
No
An optional environment UUID. Some resources, such as user images, does not require an environment.
bucket_resource
varchar(36)
No
Which kind of resource is stored in this bucket, such as bot_image, etc.
bucket_name
varchar(36)
No
The bucket's name. It is an uuid.
Environment
Table containing all data regarding an environment, including which instance it's located in.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
ID representing the entity value
instance_uuid
varchar(36)
Yes
UUID of the instance this environment is hosted
channel_instance_uuid
varchar(36)
Yes
UUID of the channel instance this environment is hosted
connector_instance_uuid
varchar(36)
Yes
UUID of the connector instance this environment is hosted
organization_uuid
varchar(36)
Yes
UUID of the organization this environment belongs to
name
varchar(50)
Yes
The environment's name
removed
tinyint(1)
No
True if this environment was deleted
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Instance
Instance configuration store it's basic data but also it's DNS. All environments pointing to this instance will have the same DNS value.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
This instance's UUID
name
varchar(50)
Yes
This instance's name
dns
varchar(50)
No
This instance's DNS
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Organization
Organizations are the mother structure in Syntphony CAI 4.0.0. Objects belong to virtual agents, which in turn belong to environments, which belongs to organizations. All the data that the organization table stores are uuid and name.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
This organization's UUID.
name
varchar(50)
Yes
This organization's name.
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Permission
The Permissions table holds a flat out name map for several permissions, which are mapped for every single controller action in Syntphony CAI. They are associated to roles, which are attributed to users.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
This permission's ID
name
varchar(255)
Yes
The name of this permission. These match internal mappings in Syntphony CAI's microservices.
Role
Roles determine what Syntphony CAI's users may or not do within any given environment or virtual agent. They are not directly assigned to users, but rather to each resource and user, and mapped NxN to permissions. This table only stores their names and descriptions.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
This role's ID
description
varchar(255)
No
A short description of the role
name
varchar(255)
Yes
This role's name
User
The user table stores the Cockpit users created in the keycloak, which is an access control tool responsible for Syntphony CAI’s user authentication.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
ID representing the entity value
organization_uuid
varchar(36)
Yes
UUID of the organization this user belongs to.
identity_provider_ reference
varchar(36)
Yes
This user's UUID within keycloak.
name
varchar(100)
Yes
The user's name
varchar(100)
Yes
The user's email and login key
image_url
varchar(255)
No
URL for it's image, if any.
company
varchar(50)
No
Identifies the company where the user works
admin
tinyint(1)
Yes
True, if the user has admin to this organization.
removed
tinyint(1)
Yes
True, if the user was deleted.
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
User Configuration
This table stores all user's cockpit usage configuration. Those configurations reflect first sight of tooltips, "don't show me again" checkboxes and so on.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
ID of this configuration
user_uuid
varchar(36)
Yes
The user's UUID
key
varchar(50)
Yes
Name of the configuration key
value
varchar(255)
No
Value of the configuration key
Role_Permission relationship
This relationship table maps which permissions are mapped to which roles.
Column Name
Data Type
Not Null
Description
role_id
bigint(20)
Yes
A valid Role ID
permission_id
bigint(20)
Yes
a valid Permission ID
Relationhip Tables
user_bot relationship
This structure represents which user has access to which virtual agent. This table will always have no value for a given user if it's field 'admin' is true since it has access to all VAs and environments.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
The relationship ID
user_uuid
varchar(36)
Yes
The user's UUID
environment_uuid
varchar(36)
Yes
The environment's UUID
bot_uuid
varchar(36)
Yes
UUID of the VA this user is being granted access to
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
user_environment Relationship
This table maps which environment is accessible to which user. It is also responsible for storing the user's role within each environment. As with user_bot, this table will always have no value for a given user if it's field 'admin' is true since it has access to all environments with the admin role.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
ID representing the entity value
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Analytics Admin Tables
The following tables may be found within the Admin Schema, if you have an analytics engine in your Syntphony CAI system. Those tables are not found otherwise.
Analytics Pipeline
The analytics_pipeline table stores information regarding the availability and class of your analytics pipeline.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
ID of this analytics pipeline.
dag_class
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Analytics Configuration
The analytics_conf table stores information regarding your analytics' configurations, mainly in JSON structures.e.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
ID representing the entity value
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Analytics Pipeline Conf Organization Environment Relationship
The analytics_pipeline_conf_organization_environment e.
Column Name
Data Type
Not Null
Description
uuid
varchar(36)
Yes
ID representing the entity value
created_at
datetime
No
The date this object was created
updated_at
datetime
No
The date this object was last updated
created_by
varchar(36)
No
UUID representing the user who created this object
updated_by
varchar(36)
No
UUID representing the user who updated this object
Configuration Schema
Configuration
This table stores all environment configuration properties for each application. Most of it's data values are encrypted and this should not be read through database access.
Column Name
Data Type
Not Null
Description
id
bigint(20)
Yes
This configuration's ID
organization_uuid
varchar(36)
No
Optional field. An organization UUID to better filter values.
environment_uuid
varchar(36)
No
Optional field. AN environment's UUID to better filter values.
application
varchar(200)
No
Name of the application this configuration refers to.
profile
varchar(200)
No
The configuration profile, usually set to default.
label
varchar(200)
No
Which instance this configuration refers to.
key_
varchar(200)
No
The configuration's Key
value
varchar(800)
No
The configuration's Value
Last updated