BUG: Improve SQLite concurrency handling to prevent database is locked errors

[sujal111]

Is there an existing issue for this?

• I have searched the existing issues

What happened?

Under concurrent access (multiple requests / threads), SQLite can raise intermittent database is locked errors.
The current connection setup does not configure SQLite’s recommended concurrency and timeout PRAGMAs, which makes the application more fragile under parallel reads/writes.

could be solved using db optimisations which sqlite3 provides :)

@rahulharpal1603 - let me know if I can pick this up

Record

• I agree to follow this project's Code of Conduct

[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 :)

[rishit-codes]

After reviewing the codebase, I noticed several areas that could lead to SQLite concurrency issues, especially under parallel or multi-request workloads.

Observations:

• Required SQLite concurrency PRAGMAs are not configured in connection.py.
• Database connection handling is inconsistent: some modules use get_db_connection(), while others create connections directly via sqlite3.connect().
• No timeout is configured for handling database locks, which may result in database is locked errors.
• WAL (Write-Ahead Logging) mode is not enabled, which can limit concurrent read/write performance.

Addressing these points should improve database stability and make concurrent access more reliable.

therefore i would like this issue on.. @sujal111 @rahulharpal1603

[sujal111]

already worked on !
waiting for @rahulharpal1603 to assign me the issue :)
but yeah you can of few other db optimisations - lmk in case you need any help

[rishit-codes]

@rahulharpal1603 I’ve opened a PR (#982) that addresses this issue by centralizing SQLite connection handling and enabling WAL mode with a bounded busy timeout to reduce lock contention under concurrent access and reload scenarios.

I tested the changes locally across the backend and sync microservice and didn’t observe any locking or startup issues.
Happy to adjust the approach if there are architectural preferences I should align with — feedback welcome.