Data Structure Documentation
Official documentation for extracting raw data from the database
Official documentation for extracting raw data from the database
This document aims to provide access and a description of the main data structures available to the company to facilitate proper integration, management, and analysis of information related to interactions and participating agents.
In addition to describing each table structure, it includes recommendations and warnings regarding the responsible handling of personal data that may be found in the records, highlighting the importance of complying with current privacy and information security regulations.
We suggest carefully reviewing each field definition and the notes on handling personal data before starting any integration or analysis process to ensure the safe and proper use of the provided database.
The data presented is raw and unprocessed.
Accessing the Data in ClickHouse via HTTP and BI Tools
This section explains how to connect to the Botslovers ClickHouse database, either through direct HTTP access (for example using curl) or via graphical tools and Business Intelligence tools like DBeaver, Power BI, or Tableau.
Access via HTTP (example using curl)
You can access the data by executing SQL queries directly against the ClickHouse database via its HTTP interface.
Basic Example:
To retrieve the first 10 records from the messages_<company> table:
Replace user and password with your personal credentials.
The query parameter must be URL-encoded.
Response Formats:
By default, the response is in TabSeparated format. You can request other formats by adding FORMAT at the end of the query:
JSON Example:
CSV Example:
Access from BI Tools or Database Clients (DBeaver, Power BI, Tableau, etc.)
ClickHouse supports JDBC and ODBC connectors, allowing you to use graphical tools to explore and analyze the data.
Access via DBeaver
- Open DBeaver and create a new connection.
- Select ClickHouse as the database type.
- Choose HTTP as the connection type.
- Fill in the following fields:
- Host:
public-clickhouse.botslovers.com - Port:
443 - User: your username
- Password: your password
- SSL: enabled (make sure the box is checked)
- Host:
- Test the connection and save.
Access from BI Tools (Power BI, Tableau, etc.)
Power BI
- Download and install the official ClickHouse ODBC driver.
- Configure an ODBC DSN pointing to:
- Host:
public-clickhouse.botslovers.com - Port:
443(or8443depending on configuration, check with your administrator) - SSL Mode: enabled
- Username/Password: your credentials
- Host:
- In Power BI, create a new ODBC data source and select the configured DSN.
Tableau
- You can use the ClickHouse ODBC driver as in Power BI.
- Alternatively, use the native ClickHouse connector.
Security and Best Practices
- Your credentials are personal and must not be shared.
- Consult your administrator if you have questions about usage limits or data structure.
- We recommend avoiding queries that extract large volumes of data to prevent potential service performance issues.
Tables
messages
Acts as the central repository where each individual message generated in a conversation is stored. This document details its fields, purpose, and specific recommendations for managing sensitive data securely.
agent_activity
Tables that concentrate relevant information about agents — human or automated — participating in conversations. They facilitate associating events and metrics to each agent for individual activity analysis, key indicator calculations (e.g., response times, session closures, agent availability), and action traceability across conversations, even when transfers occur.
Table Descriptions
messages (Table: messages_<company>)
Stores every message exchanged during conversations.
| Field | Type | Description |
|---|---|---|
sender_id | String | Unique sender identifier linking each message to a specific entity. |
sender_type | String | Message origin: "user", "bot", "system", or "human". |
id | String | Unique key of each message, ensuring duplicate detection. |
created_at | DateTime | Timestamp in UTC for when the message was generated. |
channel | String | Channel through which the message was sent: "whatsapp" or "web" (widget). |
session | String | Conversation identifier grouping multiple messages. |
direction | String | Message direction: "inbound" or "outbound". |
type | String | Communicative type: "inbound" (received) or "outbound" (sent). |
text | String | Full free-text content of the message. May include text, emojis, emails, IDs, etc. Note: May be null for images or documents. |
language | String | Language code (e.g., "es", "en"). Note: May be null if undetected. |
agent_activity (Table: chat_agent_log_<company>)
Records events occurring during agents’ interaction with chats.
| Field | Type | Description |
|---|---|---|
id | String | Unique event identifier. |
chat_id | String | Associated chat identifier. |
agent_id | String | Unique agent identifier involved in the event. |
joined_at | DateTime | UTC timestamp when the agent joined the chat. |
left_at | Nullable(DateTime) | UTC timestamp when the agent left the chat, or null if still active. |
event | String | Type of event. Possible values: bot_assigned, agent_timeout, user_timeout, agent_joined, chat_escalated, manual_close, resolved, assigned_to_human, transferred. |
response_rating | Nullable(Int16) | Rating given for the interaction, if any. |
created_at | DateTime | Record creation timestamp. |
updated_at | DateTime | Record last update timestamp. |
deleted_at | Nullable(DateTime) | Logical deletion timestamp, if applicable. |
agent_status_logs (Table: agent_status_logs_<company>)
Tracks the history of agent status changes over time, such as transitioning from “online” to “break”.
| Field | Type | Description |
|---|---|---|
id | String | Unique status change identifier. |
agent_id | String | Unique agent identifier. |
old_status | String | Previous agent status: training, offline, break, ending_shift, inactive, online. |
new_status | String | New agent status: training, offline, break, ending_shift, inactive, online. |
status_changed_at | DateTime | UTC timestamp when the status changed. |
duration_seconds | Nullable(Int64) | Duration in seconds of the previous status, or null if not applicable. |
created_at | DateTime | Record creation timestamp. |
Handling of Personal Data
The messages_table, especially the text column, retains the full content of customer interactions, potentially containing personal data such as names, email addresses, ID numbers, or other details about the products or services offered by your company.
These records have not been anonymized or pseudonymized to preserve the full context of each conversation and ensure analysis accuracy.
It is essential to process this data according to applicable regulations, always maintaining confidentiality, security, and the rights of data subjects.