The latest() Bug That Silently Duplicated Transaction IDs in Production

TL;DR: Using Model::latest()->first() to get the "latest" record by ID is wrong. latest() orders by created_at, not by the value of your ID column. When two rows are inserted within the same second, both reads return the same record, and you get duplicate IDs.

---

Background

I built a simple sequential ID generator for transaction IDs. The logic was straightforward: fetch the latest record, read its code, increment the number, return the next one.

$start = "XX00000001";
$latest = Model::latest()->first();

if (!$latest || !$latest->code) {
    return $start;
}

$value = $latest->code;

if (!preg_match('/^XX\d{8}$/', $value)) {
    return $start;
}

$num = (int) substr($value, -8);
return "XX" . str_pad($num + 1, 8, "0", STR_PAD_LEFT);

It worked. Passed local and dev branch testing. Got merged into production with no issues. Then one day, a user reported duplicate IDs on different transactions.

---

So What Happened?

My assumption was that latest() would always return the record with the highest ID. Logically, the latest record should have the highest code, right?

Wrong. latest() in Laravel is just a shorthand for orderBy('created_at', 'desc'). It doesn't care about your ID column, it orders by created_at.

This means the correctness of the whole function depends entirely on created_at being precise enough to differentiate between records. And it wasn't.

The created_at column was storing timestamps in DD:MM:YY hh:mm:ss format. The smallest unit is seconds. If two rows are inserted within the same second — whether by two users simultaneously, or by a backend loop calling this function in rapid succession, both calls to latest()->first() return the same record. Both read the same code. Both return the same next ID.

created_at	ID generated	note
20:04:01	XX00000001
20:04:01	XX00000002
20:04:01	XX00000002	DUPLICATE
20:04:01	XX00000002	DUPLICATE
20:04:02	XX00000003
20:04:02	XX00000003	DUPLICATE

The bug wasn't in the increment logic. It was in the assumption that "latest by time" equals "latest by value."

---

The Fix

Instead of relying on created_at ordering, query for the actual maximum code value directly.

$start = "XX00000001";

$maxValue = Model::where('code', 'LIKE', 'XX%')
    ->max('code');

if (!$maxValue) {
    return $start;
}

$num = (int) substr($maxValue, -8);
$next = $num + 1;

return "XX" . str_pad($next, 8, "0", STR_PAD_LEFT);

max('code') operates on the actual data value, not on metadata like created_at. Regardless of when a record was inserted, this always returns the highest code in the table, which is exactly what we need.

One thing worth noting: this fix doesn't eliminate the race condition entirely. Two requests can still call this function simultaneously, both read the same max before either one writes. For a truly bulletproof solution, you'd want a database-level lock (SELECT FOR UPDATE), a UNIQUE constraint on the column to let the DB reject duplicates, or just use AUTO_INCREMENT and format on read. The max() fix is a significant improvement, but it's not atomic.

---

What I Should Have Done

Looking back, there were two things that would have caught this before it hit production.

1. Write automated tests that simulate concurrent calls.

The bug was triggered by calling this function inside a loop — something that can't be reproduced through the UI or by a non-technical tester. A simple test that calls the function in a loop ten times and asserts all returned IDs are unique would have caught this immediately.

2. Look at the database earlier.

I spent too long staring at the code when the code itself was innocent. The real issue was in the data structure — specifically, the precision of the created_at column. Checking the actual column type and sample data earlier would have pointed me in the right direction much faster.

---

The most frustrating bugs are the ones where the code does exactly what you told it to. The problem is what you assumed it would do.