ITFlow Version: 25.10.1
Database: MariaDB 10.11.11
Environment: Debian 12
Severity: HIGH - Database Integrity

Summary

The tickets table has no UNIQUE constraint on ticket_number, allowing duplicate ticket numbers to be created. This is a critical data integrity issue that breaks multiple features and can cause data loss.

The problem was discovered when ticket merge returned wrong data, but the root cause is that duplicates should never be allowed to exist in the first place.

Root Cause - The Real Problem

Database Schema Has No Protection Against Duplicates

Current schema:

ticket_number int(11) NOT NULL,

This should be:

ticket_number int(11) NOT NULL UNIQUE,

Without a UNIQUE constraint:

Bulk imports can create duplicates
API calls can create duplicates
Race conditions can create duplicates
No validation prevents duplicates

How Duplicates Get Created

In our case, a bulk import created 18 tickets simultaneously, resulting in 3 pairs of duplicate ticket numbers:

ticket_number 100001: ticket_ids 680, 681 (identical timestamp, created_by = 0)
ticket_number 100002: ticket_ids 692, 693 (identical timestamp, created_by = 0)
ticket_number 100003: ticket_ids 694, 695 (identical timestamp, created_by = 0)

All duplicates have the exact same timestamp and were created by system user (0) during bulk import.

Impact - What Breaks When Duplicates Exist

Once duplicates exist in the database, multiple features break:

1. Ticket Merge Returns Wrong Ticket

The merge lookup query in /agent/ajax.php:

WHERE ticket_number = $merge_into_ticket_number

Returns first match (lowest ticket_id), not the correct ticket.

Example:

GET /agent/ajax.php?merge_ticket_get_json_details=true&merge_into_ticket_number=100003

Returns: ticket_id 694 ("Task E", Closed)
Expected: ticket_id 695 ("Task F", Open)

2. Ticket Lookups Return Inconsistent Data

Direct URL access shows one ticket
API lookups return different ticket
Search results may show wrong ticket
Links become unreliable

3. Reporting and Analytics Corrupted

Ticket counts incorrect
Status reports show wrong data
Time tracking associated with wrong tickets
SLA calculations affected

4. Client Portal Issues

Clients may see wrong ticket when clicking links
Email notifications may link to wrong ticket
Status updates go to wrong ticket

5. Data Integrity Cascade

Cannot trust ticket_number as identifier
Breaks integrations relying on ticket_number
API responses inconsistent
Automation workflows fail

Real-World Discovery Example

Discovered when: Attempting to merge tickets via UI
Symptom: Merge dialog showed completely different ticket data than direct ticket view
Investigation: Found 3 duplicate pairs out of ~1,500 tickets
All created: Same timestamp during bulk import

ticket_number 100003 has two tickets:

ticket_id 694: "Task E", Status Closed
ticket_id 695: "Task F", Status Open

Viewing ticket 100003:

Direct link: Shows ticket_id 695, "Task F", Status Open
Merge lookup: Returns ticket_id 694, "Task E", Status Closed
Result: Cannot safely merge, risk of data loss

REQUIRED Solution - Prevent Duplicates from Being Created

1. Add UNIQUE Constraint (PRIMARY FIX)

This must be implemented to prevent future data corruption:

ALTER TABLE tickets ADD UNIQUE INDEX unique_ticket_number (ticket_number);

However, this will fail if duplicates already exist. Need migration first.

2. Pre-Migration: Detect and Resolve Existing Duplicates

Detection query:

SELECT ticket_number, COUNT(*) as count FROM tickets GROUP BY ticket_number HAVING count > 1 ORDER BY count DESC;

Resolution options for each duplicate pair:

Option A: Renumber the older ticket (change ticket_number to next available)
Option B: Merge tickets (combine into one, close duplicate)
Option C: Manual review (let admin decide)

Suggested migration script approach:

-- Find next available ticket number SET @next_number = (SELECT MAX(ticket_number) + 1 FROM tickets);

-- Renumber older duplicates (lower ticket_id) UPDATE tickets t1 SET ticket_number = @next_number + ( SELECT COUNT() FROM (SELECT ticket_id FROM tickets t2 WHERE t2.ticket_id < t1.ticket_id) as count ) WHERE ticket_id IN ( SELECT MIN(ticket_id) FROM tickets GROUP BY ticket_number HAVING COUNT() > 1 );

-- Then apply UNIQUE constraint ALTER TABLE tickets ADD UNIQUE INDEX unique_ticket_number (ticket_number);

3. Add Validation to Prevent Creation

Locations that need duplicate checking:

A. Bulk Import Functions:

// Before inserting $check = mysqli_query($mysqli, "SELECT ticket_id FROM tickets WHERE ticket_number = $new_ticket_number"); if (mysqli_num_rows($check) > 0) { // Generate new ticket number or throw error }

B. API Ticket Creation:

// In /api/v1/tickets/create.php // Add duplicate check before INSERT

C. Manual Ticket Creation:

// In ticket creation form handler // Validate ticket_number is unique

D. Ticket Number Generation:

// Make ticket number generation atomic // Use transactions or database locks to prevent race conditions

Secondary Fixes - Handle Duplicates If They Exist

Fix Merge Query (Temporary Workaround)

Until UNIQUE constraint is added, queries should handle potential duplicates:

WHERE ticket_number = $merge_into_ticket_number ORDER BY ticket_id DESC LIMIT 1

This ensures most recent ticket is returned, but this is a band-aid, not a solution.

Add Duplicate Detection to Admin Dashboard

Warning banner if duplicates detected:

$duplicate_check = mysqli_query($mysqli, "SELECT COUNT() as dupe_count FROM ( SELECT ticket_number FROM tickets GROUP BY ticket_number HAVING COUNT() > 1 ) as dupes");

if ($dupe_count > 0) { // Display warning: "Database integrity issue: X duplicate ticket numbers detected" }

Critical Questions for ITFlow Developers

Was this intentional? Is there ANY use case where duplicate ticket_numbers should be allowed? If not, this needs immediate fixing.
How widespread is this? How many ITFlow installations might have duplicate ticket numbers without knowing it?
Why no validation? The ticket_number field should have had a UNIQUE constraint from day one. What was the reasoning for allowing duplicates?
Other affected tables? Are there other tables with similar issues? (clients, contacts, assets, invoices, quotes?)
Migration path? What's the official recommendation for installations that already have duplicates?
Version timeline? Can this be fixed in a patch release (25.10.2) rather than waiting for 25.11?

Urgency - Why This Needs Immediate Attention

Silent corruption: Duplicates can exist without visible errors
Cascading failures: Breaks multiple features once duplicates exist
Data loss risk: Merge/update operations may affect wrong tickets
Trust erosion: Users cannot trust ticket_number as identifier
Growing problem: Each bulk import/API call risks creating more duplicates
No user warning: System doesn't alert admins that duplicates exist

Every day without this fix, more installations risk duplicate creation.

Diagnostic Queries

For others wanting to check their installations for this issue:

Check for duplicate ticket numbers:

SELECT ticket_number, COUNT(*) as count FROM tickets GROUP BY ticket_number HAVING count > 1 ORDER BY count DESC;

Get details on duplicates:

SELECT ticket_id, ticket_number, ticket_subject, ticket_status, ticket_created_at FROM tickets WHERE ticket_number IN ( SELECT ticket_number FROM tickets GROUP BY ticket_number HAVING COUNT(*) > 1 ) ORDER BY ticket_number, ticket_id;

Action Items

For ITFlow Developers:

Add UNIQUE constraint to ticket_number in next release
Provide official migration script for existing duplicates
Add duplicate detection to health check dashboard
Add validation to all ticket creation paths
Review other tables for similar issues

For ITFlow Users:

CHECK YOUR INSTALLATION FOR DUPLICATES:

SELECT ticket_number, COUNT(*) as count FROM tickets GROUP BY ticket_number HAVING count > 1 ORDER BY count DESC;

If you have duplicates, do not use merge or bulk operations until this is resolved.

Additional Context

Issue discovered when merge dialog showed contradictory ticket data
All duplicates in this case created during single bulk import operation
No UI/API warnings that duplicates exist
Investigating in read-only mode to preserve evidence