Postgres Comms Database
Introduction
In this database, we utilize PostgreSQL to store all the pertinent information related to our chatbot. This includes details such as the adapter we are using, the type of transformer employed, and the number of users to whom we will send notifications or SMS messages. In essence, this database exclusively contains information about the chatbot.
1. Table Schema
Table Name - adapter
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the record. |
createdAt | Timestamp | Timestamp indicating when the record was created. |
updatedAt | Timestamp | Timestamp indicating when the record was last updated. |
channel | Text | This field identifies the communication channel utilized, such as SMS, WhatsApp, or web-based platforms, among others. |
provider | Text | Name of the service provider, such as Netcore, Gupshup, Firebase, etc., responsible for delivering messages or services. |
config | Jsonb | JSON data structure storing configuration information related to service providers. This field may include vault key credentials and other provider-specific settings. |
name | Text | This field store name of adapter |
--- |
2. Table Schema
Table Name - Service
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the service record. |
createdAt | Timestamp | Timestamp indicating when the service record was created. |
updatedAt | Timestamp | Timestamp indicating when the service record was last updated. |
type | Text | Type of the service like odk, gql, get. use for user segment. |
config | JsonB | JSON data structure storing configuration information related to the service like user segment url, cadence, total records etc. |
name | Text | Name or title of the service. |
--- |
3. Table Schema
Table Name - Transformer
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the transformer record. |
createdAt | Timestamp | Timestamp indicating when the transformer record was created. |
updatedAt | Timestamp | Timestamp indicating when the transformer record was last updated. |
name | Text | Name or title associated with the transformer. |
tags | Text | Tags or labels associated with the transformer like Odk, Generic, Broadcast. |
config | Jsonb | JSON data structure storing configuration information related to the transformer. |
serviceId | UUID | Foreign key referencing the service to which the transformer is related. |
--- |
4. Table Schema
Table Name - TransformerConfig
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the transformer configuration record. |
createdAt | Timestamp | Timestamp indicating when the configuration record was created. |
updatedAt | Timestamp | Timestamp indicating when the configuration record was last updated. |
transformerId | UUID | Foreign key referencing the associated transformer. |
meta | Jsonb | JSON data structure storing additional metadata related to the configuration like title, body, hiddenFields, formID, form, serviceClass, templateType etc. |
conversationLogic | UUID | Foreign key referencing the conversation logic associated with the configuration. |
--- |
5. Table Schema
Table Name - ConversationLogic
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the conversation logic record. |
createdAt | Timestamp | Timestamp indicating when the conversation logic record was created. |
updatedAt | Timestamp | Timestamp indicating when the conversation logic record was last updated. |
description | Text | Description providing details about the conversation logic. |
adapterid | UUID | Foreign key referencing the associated adapter. |
name | Text | Name or title associated with the conversation logic. |
--- |
6. Table Schema
Table Name - bot
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the bot record. |
createdAt | Timestamp | Timestamp indicating when the bot record was created. |
updatedAt | Timestamp | Timestamp indicating when the bot record was last updated. |
name | Text | The name of the bot. |
startingMessage | Text | The initial message or greeting used by the bot when interacting with users. |
ownerID | Text | Identifier of the owner or creator of the bot. |
ownerOrgID | Text | Identifier of the organization to which the bot owner belongs. |
purpose | Text | A brief description of the purpose or function of the bot. |
description | Text | A detailed description of the bot, including its capabilities and use cases. |
startDate | Date | The date when the bot became active or started operating. |
endDate | Date | The date when the bot's operation is scheduled to end or expire. |
status | BotStatus | The current status of the bot, indicating whether it is enabled or disabled. (ENABLED, DISABLED) |
tags | Text | Tags or labels associated with the bot for categorization or organization. |
botImage | Text | Bot image icon. |
--- |
7. Table Schema
Table Name - UserSegment
| Column Name | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the user segment record. |
createdAt | Timestamp | Timestamp indicating when the user segment record was created. |
updatedAt | Timestamp | Timestamp indicating when the user segment record was last updated. |
name | Text | Name or title associated with the user segment. |
description | Text | Description providing details about the user segment. |
count | Int | Count or number of users in the segment. |
category | Text | Category or type of the user segment. |
allServiceID | UUID | Unique identifier referencing the associated service for all users in the segment. |
byPhoneServiceID | UUID | Unique identifier referencing the associated service for users in the segment by phone. |
byIDServiceID | UUID | Unique identifier referencing the associated service for users in the segment by ID. |
botId | UUID | Unique identifier referencing the associated bot for the user segment. |
--- |
Last updated