Skip to main content

Ticket Table

Description: Contains all scalar properties of a ticket and flattened one-to-one related objects (Owner, Details, Source, Referral, TicketSummary).

Table Definition:​

CREATE TABLE Ticket (
    id INT PRIMARY KEY,
    departmentId VARCHAR(255) NOT NULL,
    field1 VARCHAR(255) NOT NULL,
    field2 VARCHAR(255) NOT NULL,
    field3 VARCHAR(255) NOT NULL,
    field4 VARCHAR(255) NOT NULL,
    field5 VARCHAR(255) NOT NULL,
    field6 VARCHAR(255) NOT NULL,
    field7 VARCHAR(255) NOT NULL,
    field8 VARCHAR(255) NOT NULL,
    field9 VARCHAR(255) NOT NULL,
    field10 VARCHAR(255) NOT NULL,
    culture VARCHAR(50) NOT NULL,
    ticketType VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    open DATETIME NULL,
    close DATETIME NULL,
    lastActivity DATETIME NULL,
    uniqueArgument VARCHAR(255) NOT NULL,
    primaryProtocolType VARCHAR(50) NOT NULL,
    movedToTicketId INT NULL,
    movedToDepartmentId VARCHAR(255) NULL,
    movedFromTicketId INT NULL,
    movedFromDepartmentId VARCHAR(255) NULL,
    firstCustomerMessageDateTime DATETIME NULL,
    firstAgentMessageDateTime DATETIME NULL,
    firstAgentMessageUserId VARCHAR(255) NULL,
    lastCustomerMessageDateTime DATETIME NULL,
    queueTimeGross VARCHAR(50) NULL,
    queueTimeNet VARCHAR(50) NULL,
    firstAgentResponseTimeGross VARCHAR(50) NULL,
    firstAgentResponseTimeNet VARCHAR(50) NULL,
    agentAssignToResponseTimeGross VARCHAR(50) NULL,
    agentAssignToResponseTimeNet VARCHAR(50) NULL,
    firstAgentAllocationTimestamp DATETIME NULL,
    lastAgentAllocationTimestamp DATETIME NULL,
    agentMediaCount INT NOT NULL DEFAULT 0,
    customerMediaCount INT NOT NULL DEFAULT 0,
    agentMessagesCount INT NOT NULL DEFAULT 0,
    customerMessagesCount INT NOT NULL DEFAULT 0,
    durationNet VARCHAR(50) NULL,
    durationGross VARCHAR(50) NULL,
    agentResponseAverageTimeNet VARCHAR(50) NULL,
    totalConversationTimeNet VARCHAR(50) NULL,
    agentCannedRepliesCount INT NOT NULL DEFAULT 0,
    botConversationId VARCHAR(255) NULL,
    isIncoming BOOLEAN NOT NULL DEFAULT FALSE,

    -- Flattened Owner fields
    owner_id VARCHAR(255) NOT NULL,
    owner_gender VARCHAR(50) NULL,
    owner_userName VARCHAR(255) NULL,
    owner_culture VARCHAR(50) NULL,
    owner_isAnonymous BOOLEAN NULL,
    owner_uniqueArgument VARCHAR(255) NULL,
    owner_type VARCHAR(50) NULL,

    -- Flattened Details fields
    details_userAgent VARCHAR(500) NULL,
    details_ipAddress VARCHAR(50) NULL,
    details_location VARCHAR(255) NULL,
    details_cardsPath VARCHAR(500) NULL,
    details_externalLink VARCHAR(500) NULL,
    details_isMobile BOOLEAN NULL,
    details_identityTokenClaims VARCHAR(1000) NULL,

    -- Flattened Source fields
    details_source_title VARCHAR(255) NULL,
    details_source_uri VARCHAR(500) NULL,

    -- Flattened Referral fields
    details_referral_utmParameters VARCHAR(500) NULL,
    details_referral_adId VARCHAR(255) NULL,
    details_referral_adTitle VARCHAR(255) NULL,
    details_referral_refData VARCHAR(500) NULL,
    details_referral_productId VARCHAR(255) NULL,
    details_referral_fbPostId VARCHAR(255) NULL,
    details_referral_instagramStoryId VARCHAR(255) NULL,
    details_referral_instagramStoryMediaUrl VARCHAR(500) NULL,
    details_referral_appleIntentId VARCHAR(255) NULL,
    details_referral_appleGroupId VARCHAR(255) NULL,
    details_referral_twitterRootTweetId VARCHAR(255) NULL,

    -- Flattened TicketSummary fields
    ticketSummary_userId VARCHAR(255) NULL,
    ticketSummary_lastUpdateTimestamp DATETIME NULL,
    ticketSummary_value VARCHAR(1000) NULL
);

Fields:​

Field NameData TypeNullabilityConstraintsDescription
idINTNOT NULLPRIMARY KEYUnique identifier for each ticket.
departmentIdVARCHAR(255)NOT NULLIdentifier of the department.
field1VARCHAR(255)NULLCustom field 1.
field2VARCHAR(255)NULLCustom field 2.
field3VARCHAR(255)NULLCustom field 3.
field4VARCHAR(255)NULLCustom field 4.
field5VARCHAR(255)NULLCustom field 5.
field6VARCHAR(255)NULLCustom field 6.
field7VARCHAR(255)NULLCustom field 7.
field8VARCHAR(255)NULLCustom field 8.
field9VARCHAR(255)NULLCustom field 9.
field10VARCHAR(255)NULLCustom field 10.
cultureVARCHAR(50)NOT NULLCulture code (e.g., "en-US").
ticketTypeVARCHAR(50)NOT NULLType of ticket (e.g., "Regular", "GlassixBot").
stateVARCHAR(50)NOT NULLState of the ticket (e.g., "Open", "Closed").
openDATETIMENULLOpen date and time.
closeDATETIMENULLClose date and time.
lastActivityDATETIMENULLLast activity timestamp.
uniqueArgumentVARCHAR(255)NOT NULLUnique argument associated with the ticket.
primaryProtocolTypeVARCHAR(50)NOT NULLPrimary protocol type (e.g., "Web", "Mail").
movedToTicketIdINTNULLID of the ticket moved to.
movedToDepartmentIdVARCHAR(255)NULLDepartment ID moved to.
movedFromTicketIdINTNULLID of the ticket moved from.
movedFromDepartmentIdVARCHAR(255)NULLDepartment ID moved from.
firstCustomerMessageDateTimeDATETIMENULLTimestamp of first customer message.
firstAgentMessageDateTimeDATETIMENULLTimestamp of first agent message.
firstAgentMessageUserIdVARCHAR(255)NULLUser ID of first agent message sender.
lastCustomerMessageDateTimeDATETIMENULLTimestamp of last customer message.
queueTimeGrossVARCHAR(50)NULLGross queue time.
queueTimeNetVARCHAR(50)NULLNet queue time.
firstAgentResponseTimeGrossVARCHAR(50)NULLGross first agent response time.
firstAgentResponseTimeNetVARCHAR(50)NULLNet first agent response time.
agentAssignToResponseTimeGrossVARCHAR(50)NULLGross agent assign-to-response time.
agentAssignToResponseTimeNetVARCHAR(50)NULLNet agent assign-to-response time.
firstAgentAllocationTimestampDATETIMENULLTimestamp of first agent allocation.
lastAgentAllocationTimestampDATETIMENULLTimestamp of last agent allocation.
agentMediaCountINTNOT NULL, DEFAULT 0Number of media items sent by agents.
customerMediaCountINTNOT NULL, DEFAULT 0Number of media items sent by customers.
agentMessagesCountINTNOT NULL, DEFAULT 0Number of messages sent by agents.
customerMessagesCountINTNOT NULL, DEFAULT 0Number of messages sent by customers.
durationNetVARCHAR(50)NULLNet duration of the ticket.
durationGrossVARCHAR(50)NULLGross duration of the ticket.
agentResponseAverageTimeNetVARCHAR(50)NULLNet average agent response time.
totalConversationTimeNetVARCHAR(50)NULLTotal net conversation time.
agentCannedRepliesCountINTNOT NULL, DEFAULT 0Number of canned replies used by agents.
botConversationIdVARCHAR(255)NULLIdentifier for the bot conversation.
isIncomingBOOLEANNOT NULL, DEFAULT FALSEIndicates if the ticket is incoming.

Flattened Owner Fields:​

Field NameData TypeNullabilityConstraintsDescription
owner_idVARCHAR(255)NOT NULLOwner's unique identifier.
owner_genderVARCHAR(50)NULLOwner's gender.
owner_userNameVARCHAR(255)NULLOwner's username.
owner_cultureVARCHAR(50)NULLOwner's culture code.
owner_isAnonymousBOOLEANNULLIndicates if the owner is anonymous.
owner_uniqueArgumentVARCHAR(255)NULLOwner's unique argument.
owner_typeVARCHAR(50)NULLOwner's type (e.g., "User", "Client").

Flattened Details Fields:​

Field NameData TypeNullabilityConstraintsDescription
details_userAgentVARCHAR(500)NULLUser agent string.
details_ipAddressVARCHAR(50)NULLIP address.
details_locationVARCHAR(255)NULLGeographical location.
details_cardsPathVARCHAR(500)NULLPath to cards.
details_externalLinkVARCHAR(500)NULLExternal link associated with the ticket.
details_isMobileBOOLEANNULLIndicates if accessed via mobile.
details_identityTokenClaimsVARCHAR(1000)NULLIdentity token claims.

Flattened Source Fields (Part of Details):​

Field NameData TypeNullabilityConstraintsDescription
details_source_titleVARCHAR(255)NULLTitle of the source.
details_source_uriVARCHAR(500)NULLURI of the source.

Flattened Referral Fields (Part of Details):​

Field NameData TypeNullabilityConstraintsDescription
details_referral_utmParametersVARCHAR(500)NULLUTM parameters from referral.
details_referral_adIdVARCHAR(255)NULLAdvertisement ID.
details_referral_adTitleVARCHAR(255)NULLAdvertisement title.
details_referral_refDataVARCHAR(500)NULLReferral data.
details_referral_productIdVARCHAR(255)NULLProduct ID from referral.
details_referral_fbPostIdVARCHAR(255)NULLFacebook post ID.
details_referral_instagramStoryIdVARCHAR(255)NULLInstagram story ID.
details_referral_instagramStoryMediaUrlVARCHAR(500)NULLInstagram story media URL.
details_referral_appleIntentIdVARCHAR(255)NULLApple intent ID.
details_referral_appleGroupIdVARCHAR(255)NULLApple group ID.
details_referral_twitterRootTweetIdVARCHAR(255)NULLTwitter root tweet ID.

Flattened TicketSummary Fields:​

Field NameData TypeNullabilityConstraintsDescription
ticketSummary_userIdVARCHAR(255)NULLUser ID associated with the summary.
ticketSummary_lastUpdateTimestampDATETIMENULLLast update timestamp.
ticketSummary_valueVARCHAR(1000)NULLSummary value/content.

Summary​

  • Tables: The database consists of seven main tables (Ticket, Tags, TicketTags, TagsParentTags, Participants, DynamicParameters, BotConversationSteps).
  • Fields: Each table includes fields with specified data types, nullability, and constraints.
  • Relationships: Defined through foreign keys and associative tables to model complex relationships (one-to-many, many-to-many, self-referential).
  • Nullability: Critical fields are marked NOT NULL, while optional fields are NULLABLE.
  • Constraints: Primary keys uniquely identify records; foreign keys enforce referential integrity.