SQLite: tolerate transient write contention in local deployments

[neubig]

Actual Behavior

Run the local SQLite service with uv run uvicorn openhands.automation.app:app, create several scheduled runs, and update automation definitions through the Canvas while dispatcher bookkeeping is writing. A short concurrent write can raise sqlite3.OperationalError: database is locked while persisting conversation_id. The entrypoint has already started, but the run remains RUNNING without its conversation, command, or sandbox identifiers and its Docker runtime requires manual recovery.

SQLite is the documented local/self-hosted database, so ordinary concurrent scheduler, dispatcher, watchdog, callback, and API writes should tolerate short-lived lock contention.

Acceptance Criteria

• SQLite connections wait through a bounded period of transient write contention instead of failing at the default short timeout.
• SQLite uses WAL mode so normal readers do not unnecessarily block the single local writer.
• Foreign-key enforcement remains enabled for every SQLite connection.
• A regression test holds a write lock briefly, releases it, and proves a second service connection completes its write without database is locked.
• PostgreSQL engine configuration is unchanged.

Live Reproduction

This occurred in the isolated Canvas factory on 2026-09-14 while four five-minute role schedules were being paused and recreated through the UI. The failed run had already started its Docker entrypoint, then failed while persisting conversation_id; the subsequent failure-state update encountered the same lock and left the run orphaned.

[github-actions]

✅ ready-for-dev label applied

This issue meets the readiness criteria and is ready for contribution.
A contributor can now open a PR that links this issue (Fixes #<number>).

This is an automated check — no AI was used to generate this comment.