Stop Using Publish Timestamps as Ordering Keys

This one is subtle enough that it survives load testing, code review, and production for months before anyone notices.

The symptom

A reader reports a post they saw on page two is now missing. You check: the post is published, the filters are correct, and the row exists. Nothing is broken — the reader saw a phantom.

The cause

Pagination with an offset assumes a stable total order. Ordering by published_at alone does not provide one.

SELECT * FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 9 OFFSET 9;

If three posts share the same published_at — which happens constantly with scheduled publishing or a batch import — the database is free to return them in any order within the tie. Page one might show post A before post B; page two might show B before A and therefore also include A again.

The reader did not misremember. The database genuinely returned the row twice.

Why it is hard to catch

Three properties conspire:

1. It requires a tie. With distinct timestamps the order is stable and everything works.
2. It requires a page boundary. A tie in the middle of a page is invisible.
3. It requires a second request. A single page load is always self-consistent.

So the bug needs a specific combination of data shape and user behaviour. Your test suite creates three posts with fake()->dateTimeBetween() and never hits it.

The fix

Make the sort key total by appending a tiebreaker:

#[Scope]
protected function recent(Builder $query): void
{
    $query->orderByDesc('published_at')->orderByDesc('id');
}

SELECT * FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 9 OFFSET 9;

Now every row has a deterministic position. id is unique, so the ordering is total, and pagination is stable.

Why not just use id

Because the fallback has to agree with the primary key. If you order by published_at DESC, id DESC, a later post with a lower id still sorts first. If you ordered by id DESC alone, a backdated post — one you published today but dated last month — would appear at the top, which is wrong.

The tiebreaker must be monotonically increasing in insert order, not in publish order, so that the immediately-next-inserted row wins a tie consistently.

The test that catches it

test('pagination is stable when posts share a publish timestamp', function () {
    $timestamp = now()->subDay();

Post::factory()->count(20)->published()->create([
'published_at' => $timestamp,
]);

$page> ->viewData('posts')->pluck('id');

$pageTwo = $this->get(route('blog.index', ['page' => 2]))->assertOk()
->viewData('posts')->pluck('id');

expect($pageOne->intersect($pageTwo))->toBeEmpty();
});
```

The assertion is intersection-emptiness, not a specific ordering. That way the test does not encode an implementation detail — it encodes the property that actually matters: a row appears on exactly one page.

The general rule

Any time you paginate with an offset, the ORDER BY must be a total order. If the columns in it can tie, the pagination is not stable, and "not stable" means "users will see duplicated and missing rows under a load pattern you have not tested yet."

Add the tiebreaker on the day you write the query. It costs one column in the sort and removes a class of bug that is genuinely painful to reproduce.