Add pagination to endpoints that read entire tables (alerts/messages)

[alex-mcgovern]

@danbarr has made frequent reports that the dashboard is very slow to load on his machine, including here stacklok/codegate-ui#300

The root cause is that his local DB has been in use since December, and has thousands of records in the alerts & messages table.

The request here is to devise a method of pagination for /alerts & /messages and implement it. I'm not sure that this ranks highly against our other priorities, but recording it for posterity.

[alex-mcgovern]

Related to #924

[alex-mcgovern]

Adding more context — when discussing this with @danbarr he mentioned a scenario where he experienced slow responses from the LLM with exceptions related to DB locks being printed into the logs. There is a dump of the related logs attached.

My understanding of the issue is that as the LLM was streaming it's response back to the IDE client, alerts were being created — these trigger server-sent-events, which in turn trigger a read from the DB via the API.

I think what we have here is negative feedback loop that drags down the user experience as a whole.

server-error.txt

[blkt]

About locking, these lines fire a concurrent db insert task for each new alert. This is not a problem in and of itself, since version 3+ of SQLite implements a multi-read/single-write concurrency model, but there's a catch.

The process of writing to the database requires the following logical steps

1. obtain a SHARED lock -> still readable, new readers allowed
2. obtain a RESERVED lock -> still readable, new readers allowed
3. write some stuff durably to the transaction log -> still readable, new readers allowed
4. obtain a PENDING lock -> still readable, no new readers allowed
5. obtain an EXCLUSIVE lock -> not readable anymore

Here's SQLite docs page.

Each of the statements goes through this pipe, which means we have a bunch of (green) threads who likely all acquired a PENDING lock, so no new readers are allowed, causing the issue that Dan faced.

I believe that, specifically for the locking issue, the very first thing we should do is implementing proper batch inserts, which are luckily trivial with sqlalchemy.