Update db session management

[cka-y]

Describe the problem

Objective: Reliable and deterministic database interactions maintaining data integrity and performance across all requests, irrespective of individual request outcomes.

Challenges: Our current architecture utilizes a global database session for handling transactions with our database. This approach has led to issues where failing requests that are not properly rolled back affect subsequent requests negatively, including those involving only SELECT operations. The root cause of this problem is the non-thread-safe nature of our global database session, which introduces state persistence issues between requests.

Proposed solution

Following the FastAPI documentation, the recommended practice is to create and close database sessions on a per-request basis. This logic should also be applied to all cloud functions accessing the database:

• extract-bounding-box
• batch-datasets
• batch-process-datasets

Alternatives you've considered

As an alternative, we considered implementing scoped_session for its thread safety. However, this solution does not fully address the issue of session isolation between requests, which is critical for preventing the cascading effects of request failures. The per-request session management approach is also suggested by SQLAlchemy's documentation here.

Additional context

This issue has been brought up as a result of fixing #283.

[cka-y]

Tasks:

• [x] Investigate db session management (suggestion: pooling) + share with the team
• [ ] Update API and cloud functions
• [ ] Load test