Feat: Thread-safe SQLite connection management for Python backend (FastAPI)

[prabalpatkar558412-collab]

Describe the feature

Problem / Motivation

PictoPy’s Python backend (FastAPI) uses SQLite to store metadata and embeddings.
Currently, SQLite connections may be shared across concurrent requests/threads, often relying on
check_same_thread=False.

Since FastAPI can handle multiple requests in parallel, sharing a single SQLite connection across
threads can lead to:

• race conditions
• inconsistent transactions
• subtle data corruption
• unstable behavior under concurrent image processing workloads

Given that PictoPy performs parallel operations such as object detection, face embedding generation,
and clustering, safe database access is critical.

---

Proposed Feature

Introduce a thread-safe SQLite connection management layer for the Python backend that ensures:

• each thread/request gets its own SQLite connection
• no global shared connection is reused across threads
• connections are properly created and cleaned up

This aligns with SQLite’s recommended concurrency model and improves backend reliability.

---

Technical Approach (High-Level)

Possible implementation:

• Remove global/shared SQLite connection usage
• Use per-thread connections via threading.local() or
• Use FastAPI dependency injection to provide a request-scoped database connection
• Ensure write operations are serialized safely if needed
• Avoid relying on check_same_thread=False for concurrency

The change will be internal to the backend and will not affect the frontend or API contracts.

---

Scope of Work

• Refactor SQLite connection handling in the Python backend
• Ensure compatibility with existing FastAPI routes
• Verify safe behavior under concurrent requests
• Update documentation/comments where necessary

---

Benefits

• Improved stability under parallel image analysis
• Prevents data corruption and hard-to-debug concurrency issues
• Aligns with PictoPy’s focus on efficient and safe data handling
• Prepares the backend for future scalability

---

Willingness to Contribute

I’d be happy to work on this feature if it aligns with the project’s direction.
Please let me know if I can proceed.

Add ScreenShots

Context Screenshot Explanation

The screenshot highlights the current SQLite connection implementation in the Python backend
where check_same_thread=False is used.

This configuration allows the same SQLite connection to be accessed across multiple threads,
which can lead to race conditions and inconsistent database state when FastAPI handles concurrent
requests.

The issue becomes more evident during parallel operations such as image processing, face
embedding generation, and metadata storage.

Record

• I agree to follow this project's Code of Conduct
• I want to work on this issue

[github-actions]

👋 Thanks for opening this issue! The maintainers will review it shortly. Till then, do not open any PR. This is a strict rule we like to follow around here :)

[hharshhsaini]

hi @prabalpatkar558412-collab please asign me this issue , i want to work over this

[hharshhsaini]

i have solved the issue , please check it out