[Bug] Database connections leak when exceptions are raised

[github-actions]

Description

Throughout the backend routers and services, database connections are obtained with get_db_connection() and closed manually at the end of each function. If any exception is raised between acquiring the connection and the close calls, the connection is never returned, causing a connection leak. Over time this will exhaust the PostgreSQL connection limit.

Location

Examples across multiple files:

• backend/routers/auth.py – login(), signup()
• backend/routers/cases.py – create_case(), apply_to_case(), get_recommended_cases_for_lawyer()
• backend/routers/lawyers.py – upsert_lawyer_profile(), get_watchlist()
• backend/services/matching_service.py – refresh_lawyer_responsiveness()

Recommendation

Wrap all database operations in a try/finally block to guarantee the connection is always closed:

conn = get_db_connection()
try:
    cur = conn.cursor()
    cur.execute(...)
    conn.commit()
finally:
    cur.close()
    conn.close()

Alternatively, implement get_db_connection() as a context manager, or use a connection pool that automatically recycles connections.

Severity

High