Database Reference

This reference is a learning path for analysts and developers who want to query the Znuny database directly — to build reports, connect BI tools (Metabase, Power BI, Tableau), or write ad-hoc SQL for data extraction.

The Znuny database has 119 tables. For the vast majority of reporting use cases you need to know roughly 15 of them. This reference covers those 15 and explains how they relate to each other.

Prerequisites: You need SELECT access to the database. The built-in SQL Box (Admin → SQL Box) is the easiest way to run queries without direct database access — it allows SELECT, SHOW, and DESC statements by default.

• Data Model Overview
• The Ticket Table
• Lookup Tables
• Articles
• Dynamic Fields
• History and Audit Trail
• Roles, Groups, and Permissions
• SQL Query Examples

Sections at a glance

Data Model Overview

The four core entities — Ticket, Article, User/Customer, Queue — and how they relate. Start here.

The Ticket Table

Every column in the ticket table explained for a reporting audience, including escalation time fields and the archive flag.

Lookup Tables

The small reference tables that resolve ID columns to human-readable names. A join cheat-sheet you will use in every query.

Articles

The article and article_data_mime tables — where email bodies, subjects, senders, and recipients are stored.

Dynamic Fields

How dynamic fields are stored using the Entity–Attribute–Value (EAV) pattern across three tables, including the dynamic_field_obj_id_name bridge table for non-ticket objects.

History and Audit Trail

The ticket_history table — a complete audit trail of every state change, owner change, and communication event on every ticket.

Roles, Groups, and Permissions

Roles, groups, and the six permission keys — how to query which agents have access to which groups, directly or via roles, and the equivalent tables for customer access.

SQL Query Examples

Eight complete, tested SQL queries covering the most common reporting needs.