Data Dictionary

This guide contains the data dictionary for the main tables used in the uContact databases. It will help you understand the structure, data types, and purpose of each column, making it easier to integrate custom developments or query relevant system information.

ccrepo.whatsapp_messages

This table stores all WhatsApp messages managed through uContact, whether incoming or outgoing, manually sent or delivered via Outbound Hubs.

Field Name	Data Type	Description
id	bigint	Unique and auto-incremental identifier for each record in the table. It is the primary key.
id_whatsapp	varchar(200)	Unique ID of the message provided by the WhatsApp provider (e.g., Gupshup, Meta). This ID is essential for tracking. It may be empty for some initial outbound records.
time	datetime	Date and time when the message was processed by uContact. Always stored in UTC-0 timezone. Example: 2025-05-28 15:25:59
direction	varchar(8)	Indicates the direction of the message. Possible Values: - outbound: Message sent from uContact to the client. - inbound: Message received from the client.
callerid	varchar(100)	WhatsApp contact's phone number in international format, without the "+" character.
message	text	The content of the message. For template messages (HSM), it may contain a special format: the unique HSM ID followed by the parameters, separated by “:”. Examples: Hi! How are you?, hsm:1234:param1:param2
campaign	varchar(200)	Name of the uContact campaign from which the message was received or sent.
agent	varchar(45)	Username of the agent who sent the message in case of outbound messages; for inbound messages, it is the agent assigned to the interaction at the time of receipt. For messages sent from Outbound Hubs, this field is empty.
result	varchar(900)	Delivery status or message processing result. Possible Values: - sent: Sent. - delivered: Delivered to recipient. - error: Failed to deliver. - read: Message read by the client.
guid	varchar(64)	GUID of the uContact interaction associated with the message.
attachments	json	Contains information about attachments (images, documents, etc.) in JSON format. Each file represents the storage path in the instance. Example: ["20250606/image.jpg"]
quotedid	varchar(200)	If the message is a reply, this field contains the id_whatsapp of the original quoted message. It is empty if the message is not a reply.

ccrepo.interactions

This table stores statistical and contextual information for every interaction handled by uContact (calls, messages, emails, etc.), across all channels. Each row represents a single interaction identified by its guid.

Field Name	Data Type	Description
guid	varchar(64)	Unique identifier of the interaction. It is the primary key.
start_date	datetime	Date and time when the interaction started. Stored in UTC-0 timezone.
end_date	datetime	Date and time when the interaction ended. Stored in UTC-0 timezone.
campaign	varchar(200)	Name of the uContact campaign the interaction belongs to.
channel	varchar(45)	Channel through which the interaction took place (e.g. telephony, webchat, whatsapp, email, sms, instagram, messenger, facebook).
disposition_id	varchar(45)	Identifier of the disposition (typification) assigned to the interaction.
agent	varchar(45)	Username of the agent that handled the interaction.
data	json	JSON object with the full state and metadata of the interaction. Its internal structure — including the custom variables — is described below.
dialerList	varchar(200)	Name of the dialer list the interaction originated from, when launched by an Outbound Hub. null otherwise.
contactId	varchar(100)	Identifier of the contact associated with the interaction.
clientId	varchar(100)	Client identifier (phone number for SMS/WhatsApp/telephony, email address for Email/Webchat).
connectorId	varchar(100)	Identifier of the channel connector (source number, DID, etc.) used by the interaction.
finished	tinyint	Indicates whether the interaction has finished.

data column structure

The data column stores a JSON object with two kinds of information:

1. Native interaction fields at the root of the object — values that belong to the channel and the interaction itself (direction, durations, response times, subject, etc.). These are managed by the system and should be treated as read-only.
2. A nested data object that groups the custom variables of the interaction. Each variable is placed in a section according to where it was generated, so custom variables never get mixed with the native fields.

The sections inside data.data are:

Section	Description
automation	Custom variables defined and set during the execution of a bot flow or an IVR flow, through the Set data activity.
outboundhub	The custom parameters of the contact uploaded in the list.
aiagent	Custom variables generated by the AI Agent during the interaction.

Example of the data column:

{
  "guid": "a1b2c3d4-...",
  "clientid": "59899123456",
  "clientname": "John Doe",
  "channel": "whatsapp",
  "campaign": "Sales",
  "direction": "inbound",
  "holdtime": 12,
  "duration": 184,
  "subject": "...",
  "data": {
    "automation": { "varA": "value A", "varB": "value B" },
    "outboundhub": { "policyNumber": "12345", "agentName": "Jane" },
    "aiagent": { "intent": "billing", "sentiment": "neutral" }
  }
}

Accessing custom variables from a custom form

When a custom form reads the interaction with getInteraction(), the data property is exposed as interaction.data, so the custom variables are available directly under their section:

interaction.data.automation.varA;
interaction.data.outboundhub.policyNumber;
interaction.data.aiagent.intent;

The bot/IVR generates the variables → a custom form reads them through getInteraction().data. See the Variables of the Bot start activity for how those variables are produced → they are persisted under data.data.automation in the interaction.

ccrepo.channels_spool_repo

This table acts as historical messages for channels (WhatsApp, SMS, Email, Instagram, Messenger, etc.). Each record represents a message uploaded to an Outbound Hub.

Field Name	Data Type	Description
id	int	Unique and auto-incremental identifier for each record in the table. It is the primary key.
dialer	varchar(200)	Name of the Outbound Hub that generated this record.
dialerList	varchar(200)	Name of the contact list within the Outbound Hub from which the record originated.
clientId	mediumtext	Identifier of the contact to be reached (e.g., phone number, email address, or social media ID depending on the channel).
data	json	Additional metadata associated with the record in JSON format. Used to pass extra variables or context to the outbound message.
content	mediumtext	Body of the message to be sent to the contact.
contactId	varchar(200)	Internal uContact identifier of the contact linked to this record.
status	int	Current processing status of the record. See Status values.
priority	int	Numeric priority for processing order. Higher values indicate higher priority. Default is 0.
timezone	varchar(200)	Timezone of the contact, used to schedule the message delivery within permitted hours. Example: America/New_York
template	varchar(200)	Name or identifier of the message template to be used (e.g., a WhatsApp HSM template name).
subject	varchar(200)	Subject of the message. Primarily used for Email channel outbound messages.
attachments	varchar(1023)	Comma-separated list or path references of files to be attached to the outbound message.

ccrepo.calls_spool_repo

This table acts as historical for voice calls. Each record represents a call scheduled by an Outbound Hub to be dialed to one or more contacts.

Field Name	Data Type	Description
id	int	Unique and auto-incremental identifier for each record in the table. It is the primary key.
dialer	varchar(200)	Name of the Outbound Hub that generated this record.
dialerList	varchar(200)	Name of the contact list within the Outbound Hub from which the record originated.
clientIds	mediumtext	One or more phone numbers associated with the contact, separated by a delimiter. The dialer iterates over them using clientIndex.
data	json	Additional metadata associated with the record in JSON format. Used to pass extra variables or context to the outbound call.
content	mediumtext	Script or content to be used during the call (e.g., IVR script, agent script).
agent	varchar(200)	Username of the agent assigned to handle this call record. Empty if the call is handled automatically (e.g., predictive or IVR mode).
timezone	varchar(200)	Timezone of the contact, used to schedule the call within permitted hours. Example: America/New_York
contactId	varchar(200)	Internal uContact identifier of the contact linked to this record.
priority	int	Numeric priority for processing order. Higher values indicate higher priority. Default is 0.
status	int	Current processing status of the record. See Status values.
clientIndex	int	Zero-based index pointing to the current phone number being dialed from the clientIds list. Incremented on each failed attempt to a number.
tries	int	Total number of dial attempts made for this record across all numbers. Default is 0.
lastTry	timestamp	Timestamp of the most recent dial attempt. Used to enforce retry intervals. Default is 2003-06-08 00:00:00.

Spool status values

Shared by both channels_spool_repo and calls_spool_repo.

Value	Name	Description
0	BUFFER	Record is in the buffer, pending to be loaded into memory.
1	MEMORY	Loaded in memory by the ListManager and ready to be processed.
2	PROCESSING	Record is currently being processed.
3	ERROR	An error occurred while processing the record.
4	BLACKLIST	Contact is on the blacklist; the record will not be processed.
5	FINISHED	The interaction associated with this record has finished successfully.
6	EXPIRED	All attempts have been exhausted without success. In channels_spool_repo: all delivery attempts for the clientId. In calls_spool_repo: all dial attempts across every number in clientIds.
7	PAUSED	Processing has been paused.
8	ORIGINATE	The originate command was sent but the call has not yet entered the flow.
9	FLOW	The call has entered the IVR or agent flow.
10	ABANDONED	The interaction was abandoned.
11	TIMEOUT	The record timed out before completing.
12	AMD	The Answering Machine Detection determined the recipient is not human.
13	INTERNAL_ERROR	An internal error occurred (e.g., originate failure); this attempt is not counted toward the retry limit.