When and how to load data for an infinite list when page/index of data is hard to know?

Question

I'm writing a Flutter web/mobile calendar application / todo list, the main feature of which is a long list of items (appointments, tasks, and the like).

Much of the time, the list won't be longer than a few hundred items. But it should be able to scale to thousands of items, as many as the user wants. Not too hard to do should the user make many repeating items. I'm using ReorderableListView.builder to virtualize the list. The part I'm struggling with is when to load the list data and how to store it.

Important: When the user picks a day, the list can jump to somewhere in the middle... and can scroll back to the top of the list.

The easiest thing to do would be to just load all data up to the users position. But this means storing a potentially very large list in memory at best, and in the web application it means requesting way more data than is really needed.

A good summary of the problem might be: Jumping to particular day is more challenging than jumping to a known index on the list. It's not easy to know what an item's index would be in a fully constructed version of the list without fully constructing the list up to that item. Yes, you can get items at particular date, but what if you wanted to get fifty items before a particular date, and fifty items after a particular date (useful for keeping scrolling smooth)? There could be a huge gap, or there could be a whole ton of items all clustered on one day.

A complication is that not all items in the list are items in the database, for example day headers. The day headers need to behave like regular items and not be attached to other items when it comes to the reordering drag animation, yet storing them as records in the database feels wrong. Here's an image of the web application to give you some idea:

Answer 1

THIS ANSWER IS MY OWN WORK IN PROGRESS. OPEN TO BETTER ANSWERS, CORRECTIONS.

I like the answer here (Flutter: Display content from paginated API with dynamic ListView) and would like to do something like it.

I would do this both for web app, where there's http bottlenecks. I would also do this for the mobile app, even when all data is in the database. Why keep the entire list in memory when you don't have to? (I have very little mobile development experience, so please correct me if I'm wrong)

Problem:

Jumping to particular day is more challenging than jumping to known index on the list. It's not easy to know what an item's index would be in a fully constructed version of the list without fully constructing the list up to that item.

Solution I'm leaning toward:

ReferencesList + keyValueStorage solution

Store the item ids in order as well as key value pairs of all items by id as a json list in NoSql. This list will include references to items, with day headings included and represented by something like 'dh-2021-05-21' or its epoch time. This list will be very lightweight, just a string per item, so you don't need to worry about holding it all in memory. Item data can be pulled out of storage whenever it's built. (Should this be a problem in Sembast or hive? Hive, and here's why: https://bendelonlee.medium.com/hive-or-sembast-for-nosql-storage-in-flutter-web-fe3711146a0 )

When a date is jumped to, run a binary search on this list to find its exact position and scroll to that position. You can easily preload, say 30 items before and 50 items after that position.

other solutions:

SplayTreeMap + QuerySQLByDate Solution:

When jumping, since you don't know the index, insert it into a new SplayTreeMap at an arbitrarily high index, say 100 * number_of_list_items_in_database just to be safe. Run two queries, on ascending from scrolled to date, and one descending from it. Cache these queries and return them in paged form. Should the user reach the beginning of the list, simply prevent them from scrolling further up the list manually with a ScrollController or something like it.