[Bug] Corner case for post retrieval by last activity: cursor won't be set sufficiently

[reallytiredofclowns]

When the number of records with a last_activity_at exceeds the post limit for /api/posts?feed=all&sort=activity, the returned cursor will never advance to fetch the next batch of posts. This is currently not an issue because the site activity is not high enough to have enough comments for this to occur, for reasonable limit arguments. However, it will become an issue for higher traffic.

Reproduction steps:

• Fudge the post database so it has many rows with the same last_activity_at.

MariaDB [discuit]> select title, last_activity_at from posts;
+-------+---------------------+
| title | last_activity_at    |
+-------+---------------------+
| 14    | 2024-06-15 14:00:00 |
| 1     | 2024-06-15 14:00:00 |
| 10    | 2024-06-15 14:00:00 |
| 22    | 2024-06-15 14:00:00 |
| 20    | 2024-06-15 14:00:00 |
| 19    | 2024-06-15 14:00:00 |
| 9     | 2024-06-15 14:00:00 |
| 2     | 2024-06-15 14:00:00 |
| 18    | 2024-06-15 14:00:00 |
| 15    | 2024-06-15 14:00:00 |
| 7     | 2024-06-15 14:00:00 |
| 4     | 2024-06-15 14:00:00 |
| 6     | 2024-06-15 14:00:00 |
| 17    | 2024-06-15 14:00:00 |
| 21    | 2024-06-15 14:00:00 |
| 8     | 2024-06-15 14:00:00 |
| 16    | 2024-06-15 14:00:00 |
| 13    | 2024-06-15 14:00:00 |
| 11    | 2024-06-15 14:00:00 |
| 12    | 2024-06-15 14:00:00 |
| 3     | 2024-06-15 14:00:00 |
| 5     | 2024-06-15 14:00:00 |
+-------+---------------------+

• Make an API call to retrieve latest activity: http://localhost/api/posts?feed=all&sort=activity&limit=5. This returns a next value of 1718460000000000000 for my fudged data.
• Fetch the next batch of latest activity: http://localhost/api/posts?feed=all&sort=activity&limit=5&next=1718460000000000000. This returns the same batch of posts as the first API call, with the same next value of 1718460000000000000.
• The API is unable to ever progress past the first fetch.

I think this can be fixed by adding the post publicId to the cursor, since that should make things unique and sortable, but I'm not l33t enough to implement it.

[previnder]

Good catch. It isn't possible at the moment, however, to fix this without breaking the API. Your solution would work, but right now the next field of the API output is of type int, and we'd have to convert it into a string, which would break backward-compatibility. Since this is not going to cause any problems at the moment, I'm going to leave it for now.