Fix N+1 Queries in a Flask App

The agent starts by learning how the app loads data, because an N+1 fix in the wrong place adds a join nobody needed. In a Flask forum shaped like flaskbb/flaskbb, pyproject.toml requires Python 3.12 or newer and pins Flask at 3.1.3 or later, Flask-SQLAlchemy 3.1.1 or later and SQLAlchemy 2.1.4 or later, with a tests dependency group of pytest, pytest-cov, pytest-xdist and freezegun and a uv.lock beside it. The Makefile runs the suite with uv run pytest, and flaskbb/configs/testing.py points the tests at an in-memory sqlite:// database with SQLALCHEMY_ECHO = False. The views in flaskbb/forum/views.py are MethodView classes: ViewTopic.get calls Topic.get_posts, ViewForum.get calls Forum.get_topics, ForumIndex.get calls Category.get_categories, and TopicTracker.get builds its own sa.select inline.

Then it reads the queries behind those views in flaskbb/forum/models.py rather than assuming the worst. Topic.get_posts selects (Post, User) with an outerjoin on Post.user_id == User.id and selectinload(Post.attachments), so the topic page already gets authors and attachments in a fixed number of statements. Forum.get_topics outerjoins Post on Topic.last_post_id and adds selectinload(Post.user). User.primary_group is declared lazy="joined", which is why topic.html can print user.primary_group.name per post without a query each. The agent writes this map down before changing anything, because a codebase that already batches its hot pages needs a measurement, not a rewrite.

The measurement comes from the repository's own test helpers. tests/fixtures/helpers.py defines a selects fixture that attaches a before_cursor_execute listener to the engine and records every SELECT, and tests/unit/forum/test_topic_page_permissions.py already uses it to assert that the topic page loads all authors' group memberships in one groups_users.user_id IN ( query and zero per-user ones. The agent copies that pattern: for each page it seeds N rows through the fixtures in tests/fixtures/forum.py (category, forum, topic, user), loads the page with N and then 2N rows, and compares the counts. A count that grows with N is an N+1; a flat count is left alone. One candidate it checks is the topic tracker: TopicTracker.get selects Topic, Post, TopicsRead, ForumsRead with outerjoins but, unlike Forum.get_topics, adds no selectinload(Post.user), while templates/forum/topictracker.html prints last_post.user.username for every row.

Each confirmed N+1 gets the narrowest fix SQLAlchemy 2 offers: a selectinload on the relationship the template reads, a contains_eager where the join is already in the statement, or a lazy="joined" on a relationship that every page reads, in that order of preference. The agent keeps the fix in the model or view, never in the template, and adds one test per fix in the style of the existing one, asserting the exact number of statements for the relationship in question. It runs uv run pytest tests/unit/forum for the pages it touched and then the whole suite the way CI does, uv sync --group dev then uv run tox run -e py3.12, and opens the pull request with the before and after statement counts from its tests.

What comes back is a pull request on its own branch that touches flaskbb/forum/models.py or flaskbb/forum/views.py where a relationship needed an eager load, and adds query-count tests under tests/unit/forum/ built on the existing selects fixture. Templates are unchanged. The description lists each page the agent measured, whether its SELECT count grew with the number of rows, and for each fix the relationship, the loader strategy it chose and the statement count its test now asserts. Pages that were already flat are listed as checked and left alone.

A reviewer reads the loader choices against the pages that render them: a selectinload adds one IN query per page, a lazy="joined" changes every query that loads that model, so the second deserves a closer look. They check that each new test seeds rows through the fixtures in tests/fixtures/forum.py rather than raw SQL, and that it clears cache the way test_topic_page_permissions.py does, since ForumIndex caches its board statistics. The session's transcript holds the uv run pytest and tox output, so the reviewer can see the suite that ran rather than take the description's word for it.

When your monitoring flags a page whose database time rose, a handler on your server receives that alert and sends POST /o/{org}/sessions with this prompt, the page's route written into input, and the repository as the resources URL. Send a request_id for each alert so a retried delivery returns the run it already started instead of a second one.

Set a webhook_url on the API key and Rush POSTs run.completed when the turn finishes, with session_url to read the pull request from, or run.failed when the agent could not reproduce a growing count and said what it measured.

Hunting N+1 queries by hand means running the app with SQLALCHEMY_ECHO on, scrolling logs, and guessing which loop caused which burst. The agent does the same work with a listener on the engine, in its own sandbox with the app's own test database, and turns every guess into a test that fails before the fix and passes after it. Your laptop runs nothing.

The result is a pull request whose claims are tests, not a paragraph about performance. Several pages or several services can be measured at once, each in its own sandbox and branch, and the transcript keeps every pytest run so the numbers in the description can be checked against the output that produced them.

What if the pages are already optimized?

Will it switch relationships to lazy="joined" everywhere?

Does it need my production database?

How does it count queries without changing the app?

Which Python and test command does it use?