Understanding Review Sync
FigureOut's reputation management module relies on a robust review synchronization engine. This document explains the underlying mechanics of our review ingress system, how background sync schedules operate, how we handle Google Business Profile API quotas, and the database-level unique index protections designed to prevent duplicate review creation or double-response postings.
1. The Review Sync Polling Architecture
Google Business Profile APIs do not currently provide webhooks for real-time review notifications. To keep your dashboard updated, FigureOut uses a scheduled polling architecture managed by a background cron runner.
Every 4 hours, our master cron worker triggers a synchronized sync sequence. This sequence iterates through all active, connected location records in the database. For each location, the system initiates a backend API call to the list reviews endpoint (mybusiness.googleapis.com/v4/accounts/{accountId}/locations/{locationId}/reviews).
The API request includes a pageSize parameter (configured to 50 items by default) and retrieves reviews ordered by update time (updateTime desc). This ensures that recently written customer reviews and edited reviews are captured during each polling loop, while older, already-synced reviews are ignored to conserve Google API rate limits and execution resources.
2. Ingress Review Mapping & Database Schema
| Google Review Field | Local Database Column | Data Type & Processing Details |
|---|---|---|
| reviewId | google_review_id | VARCHAR(255). A unique string value assigned by Google. Acts as the primary deduplication key. |
| reviewer.displayName | reviewer_name | VARCHAR(255). Public display name of the reviewer. |
| starRating | rating | INT(1). Transformed from Google's enum (e.g. FIVE maps to 5). |
| comment | review_text | TEXT. The actual text review written by the customer. Stripped of malicious HTML elements. |
| createTime | created_at | DATETIME. Timestamp of when the review was written, converted to Indian Standard Time (IST). |
| reviewReply | reply_text | TEXT. Store your currently active published reply. Used to compute response SLAs. |
3. Double-Reply Prevention & Unique DB Indexes
- Database Constraints (Unique Indexes): The database
reviewstable enforces a strictUNIQUE KEYconstraint on thegoogle_review_idcolumn. If a background sync thread and a manual sync request attempt to insert the same review at the same millisecond, the MySQL engine rejects the second transaction, preventing duplicate entries. - Transactional Locks (GET_LOCK): During review response execution, our system issues a MySQL session lock (
SELECT GET_LOCK('reply_lock_{review_id}', 10)) before updating the response. This ensures that even if two users click "Publish Reply" simultaneously, only one transaction can acquire the lock and post to the Google API.
4. Triggering a Manual Synchronization
Access Locations Panel
Navigate to the Locations page in your dashboard console.
Execute Sync Trigger
Locate the target location card and click the Sync Now button.
Refresh and Respond
Once complete, navigate back to the Reviews inbox. The new reviews will populate the list, ready for AI drafting or manual replies.
5. Understanding API Quotas & Sync Replication Delays
- Locations with active review activity are prioritized for sync.
- Inactive listings (no reviews in the last 60 days) are polled at a lower frequency (e.g. twice daily).
- Manual sync is limited to once every 10 minutes per location to protect API capacity.
A review published on Google Maps may take anywhere from 5 to 30 minutes to propagate to Google's API servers. If a manual sync does not retrieve a review immediately after it was posted by a customer, wait 15 minutes and trigger the sync again.