Database Allows Duplicate ticket_numbers - Data Integrity Issue

digibandit

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

wrongecho

Can you walk me through the process you used to import tickets?

digibandit

wrongecho n8n workflow: https://fst.dbits.ca/27bcd1064b25

wrongecho

digibandit

Which line in that json file deals with tickets?

digibandit

wrongecho Damn, wrong one lol

This is the n8n workflow I used for bulk ticket import: https://fst.dbits.ca/c2eedf79d46b

The Problem:

The "Create Ticket in ITFlow" node calls /api/v1/tickets/create.php directly with no duplicate checking. If you:

Run the workflow twice with same data
Have duplicate entries in your input JSON/CSV
Experience any workflow execution issues that cause re-runs

It creates duplicate tickets every time. There's no validation to check if a ticket with that subject or data already exists.

What Created My Duplicates:

I imported 18 tickets via this workflow. Three of them ended up with duplicate ticket_numbers - all with identical timestamps (2025-10-27 20:19:00) and created_by = 0 (system user).

The workflow needs:

A "Check if exists" step before ticket creation
Either skip duplicates or generate new ticket numbers
Better error handling to prevent re-runs from creating duplicates

The ITFlow API itself has no duplicate protection - it's up to the workflow/calling application to validate before creating.

wrongecho

digibandit

So I can see two issues you're presenting here:

The initial issue of duplicated ticket numbers
This secondary issue of duplicated ticket data

I would like to focus on the first, because the second isn't really our problem.

I am confused how you've managed to end up with duplicated ticket numbers. Yes, the field doesn't have a unique constraint, but the next number should always be updated before an insert.

I'm not against adding a unique constraint but I think this would more mask any potential problem than solve it.

Is this something you're able to consistently reproduce?

P.S. If you insist on using AI, can you ask it to at least be concise?

digibandit

wrongecho

human tl;dr:

I used a worflow becuase there is no import feature. Im not a devloper, im a IT guy, tried to figure it out, broke stuff.

let me know specificly what it is you need from me.

thanks.

my long winded ai:

I imported 18 tickets via n8n workflow (calling /api/v1/tickets/create.php). The workflow doesn't set ticket_number - ITFlow auto-generates it.

Somehow 3 pairs got the same ticket_number with identical timestamps (2025-10-27 20:19:00). All 18 tickets created in one batch, all by user_id 0.

Can't consistently reproduce - only happened once during that bulk import. Haven't tried importing 18+ tickets at once again to test.

What I can provide:

The exact workflow JSON (already shared)
Database query results showing the duplicates
Whatever logs/details you need

What I need:

How to safely clean up the 3 duplicate pairs
Whether ITFlow's ticket number generation has any known race condition issues with bulk API calls

Not trying to blame ITFlow - just trying to understand what went wrong so it doesn't happen again.

wrongecho

digibandit

I wasn't aware it was vulnerable to a race condition. We pull and increase the next number value in two separate queries, so I guess it is vulnerable, especially if the n8n workflow is sending 18 requests in parallel.

I guess we'll need to look at the client and agent portals too as they are technically vulnerable, but in reality I can't see this actually happening unless you have a lot of users/contacts raising tickets at once.

Quote and invoice counters will be equally vulnerable, but assets, contacts and basically everything else won't be - it only affects things that allow custom numbering.

I think the fix would be:

Adjust the counter logic to use transactions with a row lock when fetching the current next number for its use and increasing this for the ticket afterwards (start with the API, but also add to agent, client portal and parser). Repeat for quotes and invoices
Migrate any duplicate ticket/quote/invoice numbers automatically as part of a database update
Add the unique flag to the field across ticket/quote/invoice numbers

It does make me wonder why we even bother allowing this to be a custom-set field because just the act of updating it in settings could technically cause a duplicate if someone raises a ticket at the exact moment you save the settings.

Leave it with us.