Implement custom pagination for Sentinel history embeds #78

New issue

Closed

opened 2026-02-17 22:37:39 -05:00 by cswimr · 0 comments

cswimr commented

2026-02-17 22:37:39 -05:00

Owner

What currently is done:

Query goes out to SQLite, retrieving all moderations that satisfy the given filters (target IDs, moderator IDs, moderation types, expiry status, etc.).
- Foreign keys are also fetched, and every moderation will have at least three of them (guild ID, target ID, and moderator ID).
Query comes back and Piccolo loads the response into a list of ORM objects.
Sentinel counts that list to determine how many moderations there are, this number is then rendered in embeds and also used to determine how many pages to build.
Sentinel builds a discord.Embed instance for every pagesize moderations in the list, performing extensive string manipulation.
Sentinel compiles all of the embeds into a single SimpleMenu and sends it to the user. This menu allows the user to click through each page.

A lot of this could be, and should be, done lazily. We don't need to be building thousands of pages when the user will realistically only look at a small number of pages, if they look at any at all. Instead, we should do a fresh database query each time the page is changed by the user.

Proposal:

Query goes out to SQLite, retrieving <pagesize> moderations that satisfy the given filters.
- An offset is used for pagination.
- Foreign keys are also fetched, same as before.
Query comes back and Piccolo loads the response into a list of ORM objects.
Sentinel runs a separate COUNT query with identical filters to the first. This number is then rendered in embeds and also used to validate pagination logic.
Sentinel builds one embed, with each moderation being a field in that embed.
Sentinel sends that embed to the user, alongside a view containing buttons that can be used to move between each page.
- Clicking one of these buttons will restart this logic.

Right now, with ~14,500 moderations in the database for a single guild, it takes about ~35 seconds to query the database and do other related actions with those objects once they're loaded into Python. What currently is done: - Query goes out to SQLite, retrieving *all* moderations that satisfy the given filters (target IDs, moderator IDs, moderation types, expiry status, etc.). - Foreign keys are also fetched, and every moderation will have at least three of them (guild ID, target ID, and moderator ID). - Query comes back and Piccolo loads the response into a list of ORM objects. - Sentinel counts that list to determine how many moderations there are, this number is then rendered in embeds and also used to determine how many pages to build. - Sentinel builds a `discord.Embed` instance for every *`pagesize`* moderations in the list, performing extensive string manipulation. - Sentinel compiles all of the embeds into a single `SimpleMenu` and sends it to the user. This menu allows the user to click through each page. A lot of this could be, and should be, done lazily. We don't need to be building thousands of pages when the user will realistically only look at a small number of pages, if they look at any at all. Instead, we should do a fresh database query each time the page is changed by the user. Proposal: - Query goes out to SQLite, retrieving *`<pagesize>`* moderations that satisfy the given filters. - An offset is used for pagination. - Foreign keys are also fetched, same as before. - Query comes back and Piccolo loads the response into a list of ORM objects. - Sentinel runs a separate `COUNT` query with identical filters to the first. This number is then rendered in embeds and also used to validate pagination logic. - Sentinel builds one embed, with each moderation being a field in that embed. - Sentinel sends that embed to the user, alongside a view containing buttons that can be used to move between each page. - Clicking one of these buttons will restart this logic.