Migrate shared_db (sellers/market_comps) from SQLite to Postgres #44

New issue

Closed

opened 2026-04-19 21:26:40 -07:00 by pyr0ball · 0 comments

pyr0ball commented

2026-04-19 21:26:40 -07:00

Owner

Problem

Under load test (infra#12, 100 concurrent users), GET /api/search returns 500s due to sqlite3.OperationalError: database is locked in the post-scrape block.

Root cause: shared_db (sellers, market_comps) is a SQLite file. At 100 concurrent search requests, all writing seller data, the WAL write queue overflows the 30s busy timeout.

Immediate fix: e539427 wraps the post-scrape block in try/except OperationalError and returns raw listings with empty trust scores (degraded mode) instead of crashing.

Long-term fix

Migrate shared_db shared state (sellers, market_comps tables) to Postgres, reusing the COMMUNITY_DB_URL / CommunityDB pattern already wired up for community signals.

Keep per-user data (listings, trust_scores, saved_searches, alerts) in per-user SQLite — no write contention there.

Impact on enrich 504s

Separate issue: POST /api/enrich 504s are caused by eBay API connection timeouts (ConnectTimeoutError to api.ebay.com) backing up Playwright threads for 60s, hitting nginx proxy timeout. Fix: add connect_timeout to the eBay Browse API client, or cap enrich thread wall time before the proxy timeout.

Labels: bug, performance, database

## Problem Under load test (infra#12, 100 concurrent users), `GET /api/search` returns 500s due to `sqlite3.OperationalError: database is locked` in the post-scrape block. **Root cause**: `shared_db` (sellers, market_comps) is a SQLite file. At 100 concurrent search requests, all writing seller data, the WAL write queue overflows the 30s busy timeout. **Immediate fix**: `e539427` wraps the post-scrape block in `try/except OperationalError` and returns raw listings with empty trust scores (degraded mode) instead of crashing. ## Long-term fix Migrate `shared_db` shared state (sellers, market_comps tables) to Postgres, reusing the `COMMUNITY_DB_URL` / `CommunityDB` pattern already wired up for community signals. Keep per-user data (listings, trust_scores, saved_searches, alerts) in per-user SQLite — no write contention there. ## Impact on enrich 504s Separate issue: `POST /api/enrich` 504s are caused by eBay API connection timeouts (`ConnectTimeoutError to api.ebay.com`) backing up Playwright threads for 60s, hitting nginx proxy timeout. Fix: add `connect_timeout` to the eBay Browse API client, or cap enrich thread wall time before the proxy timeout. **Labels**: `bug`, `performance`, `database`