# 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.

<table data-header-hidden><thead><tr><th width="210">Column Name</th><th>Data Type</th><th>Not Null</th><th>Description</th></tr></thead><tbody><tr><td><strong>Column Name</strong></td><td><strong>Data Type</strong></td><td><strong>Not Null</strong></td><td><strong>Description</strong></td></tr><tr><td><strong>uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>The bot's UUID.</td></tr><tr><td><strong>environment_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID from the environment where the bot belongs</td></tr><tr><td><strong>name</strong></td><td>varchar(50)</td><td>Yes</td><td>The bot's Name</td></tr><tr><td><strong>image_url</strong></td><td>varchar(100)</td><td>No</td><td>The bot's image URL, if any</td></tr><tr><td><strong>removed</strong></td><td>tinyint(1)</td><td>No</td><td>True if the bot was deleted</td></tr><tr><td><strong>created_at</strong></td><td>datetime</td><td>No</td><td>The date this object was created</td></tr><tr><td><strong>updated-at</strong></td><td>datetime</td><td>No</td><td>The date this object was last updated</td></tr><tr><td><strong>created_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who created this object</td></tr><tr><td><strong>updated_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who updated this object</td></tr></tbody></table>

### 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.

<table data-header-hidden><thead><tr><th width="194.7142857142857">Column Name</th><th width="150">Data Type</th><th width="150">Not Null</th><th width="244.34032490232366">Description</th></tr></thead><tbody><tr><td><strong>Column Name</strong></td><td><strong>Data Type</strong></td><td><strong>Not Null</strong></td><td><strong>Description</strong></td></tr><tr><td><strong>id</strong></td><td>bigint(20)</td><td>Yes</td><td>This object's ID.</td></tr><tr><td><strong>org_uuid</strong></td><td>varchar(50)</td><td>Yes</td><td>A valid organization UUID.</td></tr><tr><td><strong>env_uuid</strong></td><td>varchar(50)</td><td>No</td><td>An optional environment UUID. Some resources, such as user images, does not require an environment.</td></tr><tr><td><strong>bucket_resource</strong></td><td>varchar(36)</td><td>No</td><td>Which kind of resource is stored in this bucket, such as bot_image, etc.</td></tr><tr><td><strong>bucket_name</strong></td><td>varchar(36)</td><td>No</td><td>The bucket's name. It is an uuid.</td></tr></tbody></table>

### Environment

Table containing all data regarding an environment, including which instance it's located in.

<table data-header-hidden><thead><tr><th width="203.47221520586007">Column Name</th><th width="150">Data Type</th><th width="150">Not Null</th><th>Description</th></tr></thead><tbody><tr><td><strong>Column Name</strong></td><td><strong>Data Type</strong></td><td><strong>Not Null</strong></td><td><strong>Description</strong></td></tr><tr><td><strong>uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>ID representing the entity value</td></tr><tr><td><strong>instance_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID of the instance this environment is hosted</td></tr><tr><td><strong>channel_instance_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID of the channel instance this environment is hosted</td></tr><tr><td><strong>connector_instance_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID of the connector instance this environment is hosted</td></tr><tr><td><strong>organization_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID of the organization this environment belongs to</td></tr><tr><td><strong>name</strong></td><td>varchar(50)</td><td>Yes</td><td>The environment's name</td></tr><tr><td><strong>removed</strong></td><td>tinyint(1)</td><td>No</td><td>True if this environment was deleted</td></tr><tr><td><strong>created_at</strong></td><td>datetime</td><td>No</td><td>The date this object was created</td></tr><tr><td><strong>updated_at</strong></td><td>datetime</td><td>No</td><td>The date this object was last updated</td></tr><tr><td><strong>created_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who created this object</td></tr><tr><td><strong>updated_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who updated this object</td></tr></tbody></table>

### 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.

<table data-header-hidden><thead><tr><th width="204">Column Name</th><th>Data Type</th><th>Not Null</th><th>Description</th></tr></thead><tbody><tr><td><strong>Column Name</strong></td><td><strong>Data Type</strong></td><td><strong>Not Null</strong></td><td><strong>Description</strong></td></tr><tr><td><strong>uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>ID representing the entity value</td></tr><tr><td><strong>organization_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID of the organization this user belongs to.</td></tr><tr><td><strong>identity_provider_ reference</strong></td><td>varchar(36)</td><td>Yes</td><td>This user's UUID within keycloak.</td></tr><tr><td><strong>name</strong></td><td>varchar(100)</td><td>Yes</td><td>The user's name</td></tr><tr><td><strong>email</strong></td><td>varchar(100)</td><td>Yes</td><td>The user's email and login key</td></tr><tr><td><strong>image_url</strong></td><td>varchar(255)</td><td>No</td><td>URL for it's image, if any.</td></tr><tr><td><strong>company</strong></td><td>varchar(50)</td><td>No</td><td>Identifies the company where the user works</td></tr><tr><td><strong>admin</strong></td><td>tinyint(1)</td><td>Yes</td><td>True, if the user has admin to this organization.</td></tr><tr><td><strong>removed</strong></td><td>tinyint(1)</td><td>Yes</td><td>True, if the user was deleted.</td></tr><tr><td><strong>created_at</strong></td><td>datetime</td><td>No</td><td>The date this object was created</td></tr><tr><td><strong>updated_at</strong></td><td>datetime</td><td>No</td><td>The date this object was last updated</td></tr><tr><td><strong>created_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who created this object</td></tr><tr><td><strong>updated_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who updated this object</td></tr></tbody></table>

### 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.&#x20;

| **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.

<table data-header-hidden><thead><tr><th width="206">Column Name</th><th>Data Type</th><th>Not Null</th><th>Description</th></tr></thead><tbody><tr><td><strong>Column Name</strong></td><td><strong>Data Type</strong></td><td><strong>Not Null</strong></td><td><strong>Description</strong></td></tr><tr><td><strong>id</strong></td><td>bigint(20)</td><td>Yes</td><td>The relationship ID</td></tr><tr><td><strong>user_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>The user's UUID</td></tr><tr><td><strong>environment_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>The environment's UUID</td></tr><tr><td><strong>bot_uuid</strong></td><td>varchar(36)</td><td>Yes</td><td>UUID of the VA this user is being granted access to</td></tr><tr><td><strong>created_at</strong></td><td>datetime</td><td>No</td><td>The date this object was created</td></tr><tr><td><strong>updated_at</strong></td><td>datetime</td><td>No</td><td>The date this object was last updated</td></tr><tr><td><strong>created_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who created this object</td></tr><tr><td><strong>updated_by</strong></td><td>varchar(36)</td><td>No</td><td>UUID representing the user who updated this object</td></tr></tbody></table>

### 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.

<table data-header-hidden><thead><tr><th width="209">Column Name</th><th>Data Type</th><th>Not Null</th><th>Description</th></tr></thead><tbody><tr><td><strong>Column Name</strong></td><td><strong>Data Type</strong></td><td><strong>Not Null</strong></td><td><strong>Description</strong></td></tr><tr><td><strong>id</strong></td><td>bigint(20)</td><td>Yes</td><td>This configuration's ID</td></tr><tr><td><strong>organization_uuid</strong></td><td>varchar(36)</td><td>No</td><td>Optional field. An organization UUID to better filter values.</td></tr><tr><td><strong>environment_uuid</strong></td><td>varchar(36)</td><td>No</td><td>Optional field. AN environment's UUID to better filter values.</td></tr><tr><td><strong>application</strong></td><td>varchar(200)</td><td>No</td><td>Name of the application this configuration refers to.</td></tr><tr><td><strong>profile</strong></td><td>varchar(200)</td><td>No</td><td>The configuration profile, usually set to default.</td></tr><tr><td><strong>label</strong></td><td>varchar(200)</td><td>No</td><td>Which instance this configuration refers to.</td></tr><tr><td><strong>key_</strong></td><td>varchar(200)</td><td>No</td><td>The configuration's Key</td></tr><tr><td><strong>value</strong></td><td>varchar(800)</td><td>No</td><td>The configuration's Value</td></tr></tbody></table>