Database Schema & Patterns

The ticket system uses MySQL with the ost_ table prefix. It combines osTicket's core tables with ~20 custom tables added via migrations in the deploy/ directory.

Database Access Patterns

osTicket Standard — Simple Queries

$result = db_query(
    'SELECT * FROM ' . TICKET_TABLE . ' WHERE ticket_id = %d',
    db_input($ticketId)
);
while ($row = db_fetch_array($result)) {
    // Process row
}

Suma\Ticket — Medoo PDO Wrapper

The Suma\Ticket class uses Medoo (a PDO wrapper) via getDB(). Always use safeQuery() instead of chaining ->query()->fetchAll() directly — Medoo's query() returns null on failure, causing fatal errors.

// ✅ Correct — handles null gracefully
return $this->safeQuery("SELECT col FROM table WHERE id = ?", [$id]);

// ❌ Fatal error if query fails (null->fetchAll())
$this->getDB()->query("SELECT col FROM table WHERE id = ?", [$id])->fetchAll();

Core osTicket Tables

These tables are defined in bootstrap.php and form the base of the system:

Table	Purpose
`ost_ticket`	Main ticket records (number, status, dept, staff, created, updated)
`ost_ticket__cdata`	Ticket custom data (linked form field values)
`ost_thread`	Thread containers (one per ticket)
`ost_thread_entry`	Individual messages/responses/notes within threads
`ost_thread_event`	Audit trail events (assigned, transferred, status changes)
`ost_staff`	Staff/agent accounts
`ost_department`	Department definitions
`ost_team`	Team definitions
`ost_user`	End users (clients)
`ost_user_account`	User authentication accounts
`ost_organization`	Client organizations
`ost_form_entry`	Links custom forms to objects (`object_type='T'` for tickets)
`ost_form_entry_values`	Custom field values (AI fields stored here)
`ost_email`	Email account configurations
`ost_sla`	SLA plan definitions
`ost_queue`	Custom queue definitions
`ost_session`	Session management
`ost_config`	System configuration key-value store
`ost_syslog`	System log entries
`ost_file`	File attachment metadata
`ost_attachment`	Attachment-to-object linkage
`ost_faq`	Knowledge base FAQ entries
`ost_lock`	Record locking
`ost_filter`	Ticket filter rules
`ost_plugin`	Installed plugins
`ost_schedule`	Business hour schedules

Custom Tables

All custom tables are created via SQL migrations in the deploy/ directory:

AI & Automation

Table	Migration File	Purpose
`ost_gemini_queue`	`deploy-production-migration.sql`	Tickets queued for Gemini AI processing (`ticket_id`, `processed`)
`ost_ticket_ai_history`	`deploy-ai-history-table.sql`	AI field change log (`ticket_id`, `field_name`, `old_value`, `new_value`, `changed_at`)
`ost_ticket_embeddings`	`deploy-ai-assistant.sql`	AI embedding text cache for vector search
`ost_ai_conversations`	`deploy-ai-assistant.sql`	AI Assistant chat sessions
`ost_ai_conversation_messages`	`deploy-ai-assistant.sql`	Individual chat messages within conversations

Budget & Time Tracking

Table	Migration File	Purpose
`ost_ticket_over_budget_lock`	`deploy-over-budget-lock.sql`	Budget lock audit trail (lock/unlock events)
`ost_ticket_harvest_entries`	`deploy-harvest-time-entries.sql`	Harvest time entry sync tracking
`ost_manual_tickets`	(pre-existing)	Manual hour tracking (`assignee_id`, `estimated`)
`ost_suma_ticket_opened_first_time`	(pre-existing)	Tracks first staff acceptance per ticket

Escalation & Alerts

Table	Migration File	Purpose
`ost_ticket_escalations`	`deploy-escalation-tracking.sql`	Escalation tracking + daily digest queue
`ost_ticket_sms_alert`	`deploy-sms-tracking.sql`	Twilio SMS dedup (UNIQUE on `ticket_id`)
`ost_twilio_sms_optout`	`deploy-sms-tracking.sql`	SMS opt-out phone numbers
`ost_twilio_delivery_status`	`deploy-sms-tracking.sql`	Twilio webhook delivery events
`ost_twilio_daily_counter`	`deploy-sms-tracking.sql`	Daily SMS rate limiting counter

Documentation & Notifications

Table	Migration File	Purpose
`ost_ticket_documentation_ack`	`deploy-documentation-required.sql`	Documentation acknowledgments by PMs
`ost_ticket_docs_autoclose_skip`	`deploy-documentation-required.sql`	Auto-close skip tracking for doc-required tickets
`ost_ticket_client_closed_docs_pending`	`deploy-client-closed-docs-tracking.sql`	Client-closed tickets pending PM doc review
`ost_staff_notifications`	`deploy-browser-notifications.sql`	Notification rows for staff
`ost_staff_notification_prefs`	`deploy-browser-notifications.sql`	Browser push permission state
`ost_staff_notification_subscriptions`	`deploy-notification-subscriptions.sql`	Per-event notification subscriptions

Custom Form Fields (AI Fields)

These custom form fields are stored in ost_form_entry_values and updated by the Gemini AI processing system:

Field Name	Data Type	Purpose
`ai-urgency`	VARCHAR	Urgency level: `LOW`, `MEDIUM`, `HIGH`, `CRITICAL`
`ai-urgency-percentage`	INT	Numeric urgency score (0–100)
`ai-estimated-hours`	DECIMAL	AI-estimated effort in hours
`ticket-summary`	TEXT (HTML)	AI-generated summary of the ticket
`ai-timeline`	TEXT (HTML)	AI-generated timeline of ticket events
`ai-suggested-title`	VARCHAR	AI-suggested title for the ticket

How Form Fields Are Stored

ost_form_entry (links form to ticket)
  ├── object_type = 'T' (ticket)
  ├── object_id = ticket_id
  └── form_id → references the custom form definition

ost_form_entry_values (actual values)
  ├── entry_id → references ost_form_entry.id
  ├── field_id → references the field definition
  └── value = the stored value

AI fields are updated directly via:

INSERT INTO ost_form_entry_values (entry_id, field_id, value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE value = VALUES(value)

Thread Entry Types

Thread entries are the core communication records. Each ticket has a thread containing entries:

Type Code	Class	Description
`M`	`MessageThreadEntry`	Original client message
`R`	`ResponseThreadEntry`	Public staff reply (visible to client)
`N`	`NoteThreadEntry`	Internal staff note (hidden from client)

Fetching Thread Entries

// ✅ Correct — always specify types explicitly
$types   = ['M', 'R', 'N'];
$entries = $ticket->getThreadEntries($types);

// ❌ Never use $thread->getEntries() directly
// It is filtered by context and only returns type M in CLI/cron

Key Relationships

ost_ticket (1) ──── (1) ost_thread
                          │
                          ├── (N) ost_thread_entry (messages/responses/notes)
                          └── (N) ost_thread_event (audit trail)

ost_ticket (1) ──── (N) ost_form_entry ──── (N) ost_form_entry_values

ost_ticket (1) ──── (1) ost_gemini_queue (pending AI processing)
ost_ticket (1) ──── (N) ost_ticket_ai_history (AI change log)
ost_ticket (1) ──── (N) ost_ticket_escalations
ost_ticket (1) ──── (1) ost_ticket_sms_alert (dedup)
ost_ticket (1) ──── (N) ost_ticket_over_budget_lock

ost_staff (1) ──── (N) ost_staff_notifications
ost_staff (1) ──── (N) ost_staff_notification_subscriptions

ost_user (N) ──── (1) ost_organization

Running Migrations

Migrations are applied directly via MySQL. Each file in deploy/ is a standalone SQL script:

# Apply a specific migration
mysql -u root -p managerhinogroup < deploy/deploy-over-budget-lock.sql

# Or via a MySQL client
SOURCE deploy/deploy-browser-notifications.sql;

caution

Always review migration files before running. Some contain DROP TABLE IF EXISTS statements that will destroy existing data. Back up the database before applying migrations in production.

Table Prefix Convention

All tables use the ost_ prefix, defined as TABLE_PREFIX in bootstrap.php. Custom code should always reference the constant:

$table = TABLE_PREFIX . 'gemini_queue';  // 'ost_gemini_queue'

The GEMINI_QUEUE_TABLE constant is also pre-defined for convenience.

Database Access Patterns​

osTicket Standard — Simple Queries​

Suma\Ticket — Medoo PDO Wrapper​

Core osTicket Tables​

Custom Tables​

AI & Automation​

Budget & Time Tracking​

Escalation & Alerts​

Documentation & Notifications​

Custom Form Fields (AI Fields)​

How Form Fields Are Stored​

Thread Entry Types​

Fetching Thread Entries​

Key Relationships​

Running Migrations​

Table Prefix Convention​