Improve SQLAlchemy API usage and db session management

[itallix]

Refactor the project’s SQLAlchemy integration to align with best practices and the latest API standards.

Acceptance Criteria:

• Session factory improvements: update SessionLocal to use autoflush=True behavior by default to reduce boilerplate.
• Session DI: define db_session as a dependency and inject into services. (this will help to avoid monkeypatching in tests)
• No caching for stateless services: most of the services are stateless, we can avoid using @lru_cache with it.
• Upgrade to the latest 2.0 SQLAlchemy API:
  • Identify and refactor all usages of legacy SQLAlchemy APIs (e.g., db.query) to use the recommended modern API (e.g., db.execute(select(...)))
  • Update repository code and ensure all queries use the latest patterns.
• Testing improvements: review unit tests and replace monkeypatching of database/session logic with dependency injection or fixtures where possible.

[leoll2]

use autoflush=True behavior by default to reduce boilerplate

About this, please take a look at this comment: in some cases, it is necessary to call flush() regardless of the autoflushing settings.

[itallix]

autocommit is deprecated - can be removed from a factory call.
We can probably handle it together with a session - I am not sure if we benefit from explicit control in the app code:

@contextmanager
def get_db_session() -> Iterator[Session]:
    """Context manager to get a database session."""
    db = SessionLocal()
    try:
        yield db
    except Exception:
        db.rollback()
        raise
    else:   # If no exceptions, we are safe to commit transaction
        db.commit()
    finally:
        db.close()