Articles

An article is a single communication record attached to a ticket — an inbound email, an outbound reply, a phone-call note, an internal note, or a chat message. Every article belongs to exactly one ticket.

Article storage is split across two tables: article holds the metadata common to all communication channels, and article_data_mime holds the email-specific content (subject, body, headers).

The article Table

One row per communication event. Contains channel and visibility metadata.

Column	Type	Description
id	BIGINT	Primary key. Referenced by article_data_mime.article_id and ticket_history.article_id.
ticket_id	BIGINT	The ticket this article belongs to. Joins to ticket.id.
article_sender_type_id	SMALLINT	Who sent this article (agent, customer, or system). Joins to article_sender_type.id.
communication_channel_id	BIGINT	Which channel the article came through. Joins to communication_channel.id.
is_visible_for_customer	SMALLINT	1 = visible in the customer portal, 0 = internal (agents only).
create_time	DATETIME	When the article was created.
create_by	INTEGER	Agent who created the article. Joins to users.id.

The article_data_mime Table

One row per MIME (email) article. Contains the actual message content. Always joined to article via article_id.

Column	Type	Description
id	BIGINT	Primary key.
article_id	BIGINT	Joins to article.id.
a_from	MEDIUMTEXT	The From header (sender name and address).
a_reply_to	MEDIUMTEXT	The Reply-To header.
a_to	MEDIUMTEXT	The To header.
a_cc	MEDIUMTEXT	The Cc header.
a_bcc	MEDIUMTEXT	The Bcc header.
a_subject	TEXT	The email subject line.
a_message_id	TEXT	The RFC 2822 Message-ID header. Used for threading.
a_body	MEDIUMTEXT	The message body. May be plain text or HTML depending on a_content_type.
a_content_type	VARCHAR(250)	MIME content type of the body (e.g. text/plain; charset=utf-8).
incoming_time	INTEGER	Unix epoch of when the message was received. 0 for outbound messages.
create_time	DATETIME	When the record was written.

Sender Type and Channel

article_sender_type — resolves article.article_sender_type_id

Name	Meaning
agent	Written or sent by a Znuny agent.
customer	Sent by a customer contact.
system	Generated automatically by the system.

communication_channel — resolves article.communication_channel_id

Standard channels:

Name	Meaning
Email	MIME email (inbound or outbound).
Phone	Phone call note created by an agent.
Internal	Internal note, not visible to customers.
Chat	Chat message (if the chat feature is enabled).

Typical Article Queries

All external emails on a ticket:

SELECT
    adm.a_from,
    adm.a_to,
    adm.a_subject,
    adm.a_body,
    a.create_time,
    ast.name  AS sender_type,
    cc.name   AS channel
FROM   article                a
JOIN   article_data_mime      adm ON adm.article_id = a.id
JOIN   article_sender_type    ast ON ast.id = a.article_sender_type_id
JOIN   communication_channel  cc  ON  cc.id = a.communication_channel_id
WHERE  a.ticket_id = :ticket_id
AND    a.is_visible_for_customer = 1
ORDER BY a.create_time;

Count of inbound emails received per day:

SELECT
    DATE(a.create_time)  AS day,
    COUNT(*)             AS inbound_emails
FROM   article               a
JOIN   article_sender_type   ast ON ast.id = a.article_sender_type_id
JOIN   communication_channel cc  ON  cc.id = a.communication_channel_id
WHERE  ast.name = 'customer'
AND    cc.name  = 'Email'
GROUP BY DATE(a.create_time)
ORDER BY day DESC;