frontend wont open when failed transaction not rolled back: sqlalchemy.exc.PendingRollbackError

> [!NOTE]
> Migrated from [augurlabs/augur#3361](https://github.com/augurlabs/augur/issues/3361)
> Originally opened by `@MoralCode` on 2025-10-30

---

I encountered a weird exception when trying to load the frontend pages today. I got a 500 error before anything showed up on screen, even when just trying to get to the augur homepage.

It seems like this may have been caused by another exception that was not cleaned up/rolled back in the catch block.

We should check through our database calls to see if we can find all the places where we may not be correctly rolling back DB transactions on exceptions.

stack trace:
```
2025-10-30 16:24:38 f26c37058939 augur.api.server[406] ERROR Exception on / [GET]
augur-1 | Traceback (most recent call last):
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask/app.py", line 2073, in wsgi_app
augur-1 | response = self.full_dispatch_request()
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask/app.py", line 1518, in full_dispatch_request
augur-1 | rv = self.handle_user_exception(e)
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask_cors/extension.py", line 178, in wrapped_function
augur-1 | return cors_after_request(app.make_response(f(*args, **kwargs)))
augur-1 | ^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask/app.py", line 1516, in full_dispatch_request
augur-1 | rv = self.dispatch_request()
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask/app.py", line 1502, in dispatch_request
augur-1 | return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/augur/api/view/routes.py", line 76, in repo_table_view
augur-1 | if current_user.is_authenticated:
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/werkzeug/local.py", line 436, in __get__
augur-1 | obj = instance._get_current_object()
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/werkzeug/local.py", line 565, in _get_current_object
augur-1 | return self.__local() # type: ignore
augur-1 | ^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask_login/utils.py", line 26, in
augur-1 | current_user = LocalProxy(lambda: _get_user())
augur-1 | ^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask_login/utils.py", line 346, in _get_user
augur-1 | current_app.login_manager._load_user()
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask_login/login_manager.py", line 331, in _load_user
augur-1 | user = self._load_user_from_request(request)
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/flask_login/login_manager.py", line 390, in _load_user_from_request
augur-1 | user = self._request_callback(request)
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/augur/api/view/augur_view.py", line 103, in load_user_request
augur-1 | token = db_session.query(UserSessionToken).filter(UserSessionToken.token == token, UserSessionToken.expiration >= current_time).first()
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2748, in first
augur-1 | return self.limit(1)._iter().first() # type: ignore
augur-1 | ^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2847, in _iter
augur-1 | result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
augur-1 | ^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
augur-1 | return self._execute_internal(
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2188, in _execute_internal
augur-1 | result: Result[Any] = compile_state_cls.orm_execute_statement(
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
augur-1 | result = conn.execute(
augur-1 | ^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
augur-1 | return meth(
augur-1 | ^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
augur-1 | return connection._execute_clauseelement(
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
augur-1 | ret = self._execute_context(
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1812, in _execute_context
augur-1 | conn = self._revalidate_connection()
augur-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 669, in _revalidate_connection
augur-1 | self._invalid_transaction()
augur-1 | File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 659, in _invalid_transaction
augur-1 | raise exc.PendingRollbackError(
augur-1 | sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b)
```