[Bug] Items created in the same second may not be shown in the bookmark grid due to pagination

[MohamedBassem]

When we're paginating using createdAt only, items created in the same second can result in duplicates or missing items.

[kamtschatka]

I ran into this issue also just now. Of course this happens a lot if you import a lot of bookmarks using the cli (or in my case the PR to allow importing bookmarks via the UI)

I noticed that it is an easy fix in schema.ts by changing timestamp to timestamp_ms in the createdAtField.
I tried running the database migrations (https://docs.hoarder.app/Development/database), but it only adds
CREATE INDEX `bookmarkLinks_url_idx` ON `bookmarkLinks` (`url`);

I was hoping that would also create a migration of the existing timestamps, but seems like it doesn't.

[MohamedBassem]

@kamtschatka Changing the mode of the column is a software layer thing. You'll have to create an empty migration with pnpm drizzle-kit generate:sqlite --custom and write some SQL to multiple all the createdAt columns by 1000. However, using milliseconds makes the chances of the problem happening lower but it doesn't really solve the problem. The proper fix is to paginate both on createdAt and the id. But we'll need to be careful if we're to do that in a backward compatible way.

As for what you got when you generated a migration, that's actually my fault. I apparently forgot to create a migration when I implemented the URL dedups :) Will get that fixed.

[MohamedBassem]

Pushed c9dc23f to fix the dangling migration problem.

[kamtschatka]

it would at least alleviate the problem, since that would then affect maybe 1 or 2 bookmarks instead of all of them, if you use the CLI.
I am not sure how much work it is though to improve the pagination using createdAt and id, maybe you can fix that really quick, then switching to timestamp_ms makes no sense.

[MohamedBassem]

I think fixing the pagination shouldn't be hard. I'll prioritize it specially in the context of your PR to support importing from the UI.