Google Review Intelligence

Problem

Google reviews carry real signal — rating drift, recurring complaints, owner-response gaps — but that signal is locked in a UI that doesn't do trend analysis, comparison, or export. The goal wasn't scraping reviews for its own sake; it was turning a pile of unstructured review text into something a human can filter, chart, and hand off to reporting.

Architecture

Two processes, one image, split so a slow scrape or import never blocks the dashboard:

• Web process — Next.js App Router: authentication, business/location CRUD, the manual scrape form, review browsing, analytics, and CSV streaming.
• Worker process — polls provider run status, imports completed datasets, rebuilds review terms, and reports a heartbeat.
• PostgreSQL — system of record and durable job queue via pg-boss, so jobs survive a process restart.
• DataForSEO / Apify — DataForSEO is the preferred provider when configured, with a cheap newest-review top-up mode capped at 50 reviews and an explicit historical-backfill mode up to 4,490. Apify (compass/google-maps-reviews-scraper) is the intentional fallback.

Every scrape is manually initiated — there are no schedules or automatic recurring runs. A transaction-safe advisory lock keeps at most one active run per location.

Interesting technical decisions

• Idempotency is enforced at the import layer, not assumed. Reviews upsert on a content hash covering rating, text, translated text, owner response, and response date — re-importing the same dataset, or a scrape window that overlaps a previous one, updates changed reviews and owner responses without creating duplicates.
• Phrase analysis is deterministic, not AI-generated. Unigram/bigram/trigram extraction, stopword removal, and rating-based sentiment classification (1–2★ negative, 3★ mixed, 4–5★ positive) all run without calling a model, so the phrase tables stay explainable and reproducible.
• CSV export is formula-injection safe and streamed. Cells beginning with =, +, -, or @ are prefixed before they leave PostgreSQL, and export streams rather than buffering the full result set in memory.
• The importer processes pages, not a full dataset in memory, continuing past malformed individual items while recording import warnings instead of failing the whole run.
• WTOS handoff is batch, not per-review. After an import completes, only reviews a business's response policy can't classify safely are sent to WTOS as review_response tasks in one batch — not a live call per review.

Deployment

Ships as separate Coolify resources for web, worker, and PostgreSQL, authenticated with single-use, 15-minute magic links emailed via Postmark instead of a password. As of September 2026, migrations, seed, the production Docker build, unit and integration test suites, and an authenticated Playwright smoke test all pass locally; the one acceptance step still open is a live end-to-end scrape against the selected provider with real operator credentials.