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

New issue

Open

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

pyr0ball commented

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

Owner

Problem

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

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, returning raw listings with empty trust scores (degraded mode) instead of crashing.

Long-term fix

Migrate shared_db shared state (sellers, market_comps) to Postgres, reusing the COMMUNITY_DB_URL / CommunityDB pattern already wired up for community signals. Keep per-user data (listings, trust_scores, saved_searches) in per-user SQLite.

Separate: enrich 504s

POST /api/enrich 504s come from eBay API connection timeouts backing up Playwright threads for 60s, hitting the nginx proxy timeout. Add connect_timeout to the Browse API client or cap thread wall time.

## Problem Under load test (infra#12, 100 concurrent users), `GET /api/search` returns 500s due to `sqlite3.OperationalError: database is locked`. **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`, returning raw listings with empty trust scores (degraded mode) instead of crashing. ## Long-term fix Migrate `shared_db` shared state (sellers, market_comps) to Postgres, reusing the `COMMUNITY_DB_URL` / `CommunityDB` pattern already wired up for community signals. Keep per-user data (listings, trust_scores, saved_searches) in per-user SQLite. ## Separate: enrich 504s `POST /api/enrich` 504s come from eBay API connection timeouts backing up Playwright threads for 60s, hitting the nginx proxy timeout. Add `connect_timeout` to the Browse API client or cap thread wall time.