I run Prisma Reviews, a small API that returns customer reviews from 84 platforms in one JSON format. This post is not about the product. It is about three engineering problems that took most of our time: designing a schema that fits sources with very different shapes, keeping IDs stable so the same review never shows up twice, and testing the contract so that a change upstream does not quietly break clients downstream.
One thing first, because it shapes everything else: not every review source is an API. Of our 84 sources, 59 have an official API. The other 25 do not, and we read their public pages or feeds instead.
Not every source is an API
Every platform in our config has three properties: how a business connects it, whether we can publish a reply, and how new reviews reach us. Sorting the 84 sources by how we read them gives four groups:
| Group | Sources | Examples |
|---|---|---|
| Official API, business's own credentials or OAuth | 50 | Google Business Profile (access pending), App Store, Google Play, Trustpilot (access pending), Judge.me, Yotpo, eBay, Wix, WooCommerce, Shopware, property managers such as Hostaway and Guesty |
| Official public API, no business credentials | 9 | Steam, Etsy, Salesforce AppExchange, Atlassian Marketplace, VS Code Marketplace, Open VSX, JetBrains Marketplace, Thunderbird Add-ons, Viator |
| No API: public page or feed | 3 | Shopify App Store (HTML reviews page), WordPress.org (RSS reviews feed), Apple Podcasts (public reviews feed) |
| No API: paid data provider | 22 | Google Maps, Yelp, Tripadvisor, Booking.com, Facebook, Airbnb, Expedia, Capterra, OpenTable |

The groups differ in ways that leak into the API design:
- Authentication. The first group needs an OAuth flow or a key the business pastes in. The other three only need the public URL of a listing.
- Replies. Only 25 of the 84 sources let a third party publish a reply, and two of those, Google Business Profile and Trustpilot, are still waiting for the platform to approve our access. Having an API is not enough: Atlassian's reply endpoint now answers
410 API_DEPRECATED, and the Microsoft Store reply API is broken on Microsoft's side, so both are read only. - Cost and cadence. Official APIs cost us nothing per call, so we check them often. Reading through a data provider is billed per review, and some providers charge per run whatever is new, so those sources are checked once a day.
- Why there is no API. Expedia's partner API with replies is only open to full channel managers, and it declined us. Airbnb's is invitation only. Shopify has no reviews API; app developers answer in their own dashboard.
Some platforms show up twice. Airbnb and Booking.com reviews can come from a public page (read only), or through a property manager's official API, which does allow replies. That is the reason for one of the fields below.
One schema for all of them
Every review, from every source, has the same 14 fields, and all of them are always present. When a platform does not give us something, the field is null, never missing. A missing key and a null mean the same thing to a person, but not to a typed client, and "always present" is easy to test.
{
"id": "rev_01KJ8Q2M4T7HX3VB9CWD6NRZ5E",
"platform": "google",
"location_id": "loc_01KJ8P9W2C4D6F8G0H2J4K6M8N",
"rating": 4,
"title": null,
"text": "Great flat white, a bit slow at lunchtime.",
"author": "Marta G.",
"language": "en",
"url": null,
"source": null,
"created_at": "2026-10-19T12:41:07.000Z",
"updated_at": "2026-10-19T12:41:07.000Z",
"deleted_at": null,
"reply": null
}rating is an integer from 1 to 5, or null. Booking.com scores out of 10, so we divide by two, round and clamp to 1-5. Steam has no stars, only "recommended" or "not recommended", which we map to 5 and 1. Some sources allow a review with no rating at all, and then the field is null. We never write a 0: it looks like a real score and drags averages down.
text and title can be null. Rating-only reviews are common on app stores, and only some platforms have titles. We do not make one up from the first sentence.
url means something specific on read-only platforms. When we cannot publish a reply, url is where the business can answer it by hand. If a client tries to reply anyway, the error carries the same URL.
source exists because of aggregators. Viator returns reviews written on Viator and on Tripadvisor. A property manager returns Airbnb, Booking.com and Vrbo reviews through one connection. platform says which connection the review came through; source says which site it was written on.
created_at and updated_at are separate because people edit reviews. An edited one-star review that becomes a four-star review should be an update, not a new row.
reply is an object with a status, not a string. The status is published, queued, failed or unknown. The last one needs explaining. Sometimes the request to the platform goes out and no answer comes back, and the platform has no way to read the reply back. Retrying could publish the same reply twice, under the business's name. So we stop, mark it unknown, and tell the client to check the platform before posting again. A status that says "we don't know" is more useful than a guess.

Reply length limits are checked per platform before a reply is queued: 4,096 bytes on Google Business Profile, 350 characters on Google Play, 80 on eBay. An emoji is one character but four bytes, so we count code points for character limits and bytes for byte limits.
Stable IDs and deduplication
Duplicates are the bug clients notice first. Our rule is simple: each connector must return the platform's own ID for every review, and the database has a unique index on (location_id, external_id). A second sync of the same review is an update, never an insert. Locations work the same way, with a unique index on (connection_id, external_id).
The hard part is that "the platform's own ID" does not always exist:
- Etsy reviews have no ID of their own. Each review belongs to exactly one transaction, so the transaction ID is the review ID.
- WordPress.org gives no review ID in its feed. Each review is a forum topic, so we use the topic slug.
- The Shopify App Store page carries a review content ID in its HTML. We rely on it, knowing that a page redesign could change it.
- Google Play has real IDs, but its API only returns reviews created or edited in the last seven days. The stored history is ours, and we cannot re-read it.
Clients never see platform IDs as primary keys. They get our own IDs with a type prefix and a time-ordered body (rev_01KJ…, loc_01KJ…, req_01KJ…). The prefix makes a misplaced ID obvious in logs, and the ordering helps pagination: the cursor encodes the timestamp and ID of the last item, so a page boundary stays put while new reviews arrive.
Reconnection. A business disconnects an account and connects it again a week later. Creating new locations would duplicate every review. Instead, a location disconnected less than 30 days ago with the same external ID is reactivated: same IDs, no duplicate reviews.
Deletions. A review removed from the platform simply stops appearing. Nothing tells you. For 60 of the 84 sources we can read the full history, so we re-read it periodically (every 25 days for Google Business Profile, whose terms do not let us keep content unconfirmed for more than 30; weekly for most others) and mark reviews the platform no longer returns with deleted_at, plus a review.deleted webhook. The key safeguard: if a read stopped early, because of a timeout or a page limit, we mark nothing. Treating "I didn't see it" as "it's gone" would delete real reviews. Google Play is excluded because of its seven-day window.
Contract testing against the OpenAPI spec
The contract is an OpenAPI 3.1 document with 18 paths, 30 schemas and 9 webhook events. Two kinds of test keep it honest.
The first is cheap and runs on every commit: the spec's enums must match the code exactly. The platform IDs in the spec must equal the list in the platform config, and the webhook events in the spec must equal the event types the code can emit. Generated clients reject enum values they do not know, so a platform added to the code but not to the spec would break those clients on their first sync.
The second is a test mode. API keys come in two kinds, pk_live_… and pk_test_…, against the same server. A test key routes every platform to a test connector: deterministic sample locations and reviews shaped like the real thing (a café on Google, an app on the App Store, a hotel on Booking.com), replies that are accepted and never published anywhere, and on read-only platforms a url on every review, because the docs promise one. Anyone can run the whole contract, write paths included, without touching a real account.
The examples below are the ones in the spec, with URLs replaced by example.com. A list request:
GET /v1/reviews?platform=google&limit=1
Authorization: Bearer pk_test_…returns the review shown earlier inside data, plus a next_cursor. A reply to a platform that supports replies is accepted with 202, and the review comes back with its reply in the queue (other fields omitted):
POST /v1/reviews/rev_01KJ8Q2M4T7HX3VB9CWD6NRZ5E/reply
Authorization: Bearer pk_test_…
Content-Type: application/json
{ "text": "Thanks Marta! We have added a second barista at lunch." }{
"id": "rev_01KJ8Q2M4T7HX3VB9CWD6NRZ5E",
"platform": "google",
"reply": {
"text": "Thanks Marta! We have added a second barista at lunch.",
"created_at": null,
"status": "queued",
"error": null
}
}The interesting cases are the failures. Every error has the same shape (type, code, message, retryable, request_id, docs_url), and some codes add fields a client can act on. Replying to a Yelp review returns 422:
{
"error": {
"type": "invalid_request_error",
"code": "reply_not_supported",
"message": "Yelp has no reply API for third parties, so Prisma only reads its reviews. Answer this one on the platform: https://example.com/biz/example-cafe-madrid",
"retryable": false,
"request_id": "req_01KJ8T3C5D7E9F1G3H5J7K9M1N",
"docs_url": "https://example.com/docs/errors#reply_not_supported",
"platform": "yelp",
"url": "https://example.com/biz/example-cafe-madrid"
}
}A reply over the platform's limit also returns 422, with code: "reply_too_long", platform: "google_play" and max_length: 350. Clients should branch on code, never on message: the message is for people and can change.
The same shape holds on live traffic. This is a real response from production to a request with no key (headers trimmed, docs URL replaced):
HTTP/1.1 401 Unauthorized
Content-Type: application/json
Request-Id: req_01K6VVGDM8CFFJEQ3MNWWR582S
{"error":{"type":"authentication_error","code":"missing_api_key","message":"Send your API key as \"Authorization: Bearer pk_live_…\".","retryable":false,"request_id":"req_01K6VVGDM8CFFJEQ3MNWWR582S","docs_url":"https://example.com/docs/errors#missing_api_key"}}The body's request_id matches the Request-Id header. For each operation we check the status code, that the body validates against its schema (all 14 review fields present, nullable types where declared), the error shape on every non-2xx response, and the Request-Id and rate-limit headers on success.
Run the same checks with Apidog CLI
The same checks work on any API that ships an OpenAPI file. Apidog CLI imports the spec, and a coding agent such as Claude Code writes and runs the test cases. Set it up once:
npm install -g apidog-cli@latest
apidog login --with-token <YOUR_TOKEN>
apidog skill install
Then give the agent one prompt:
Import ./openapi.json into Apidog project <projectId>. Create test cases for the
list reviews and reply endpoints that assert the status code, that the body
validates against its schema with all 14 review fields present, and that the
Request-Id header is set. Add a case that replies to a Yelp review and expects
422 with code reply_not_supported. Run them against the test environment and
summarize any failures.The agent imports the spec, reads the test case schema, validates its payload, creates the case and runs it:
apidog import --project <projectId> --format openapi --file ./openapi.json
apidog cli-schema get test-case-create
apidog cli-schema validate test-case-create --file case.json
apidog test-case create --project <projectId> --file case.json
apidog run --test-case <caseId> --project <projectId> -e <envId> -r cli,junit
The junit reporter writes a file your CI can read, so the same run can gate every pull request. The Apidog CLI CI/CD guide covers the pipeline setup.
Lessons
Model "has an API" and "can reply" as separate properties. Atlassian and Microsoft show that an API can exist and still not take replies. One flag for both would promise replies we cannot deliver.
Plan for the unknown reply state before the first reply goes out. A state that admits uncertainty costs one enum value; a duplicate public reply costs a customer's trust.
Don't trust that a platform ID exists or stays stable. Write down, per connector, where the ID comes from and what would change it.
Test the spec against the code, not just the code against the spec. The enum test is about ten lines, and it guards the failure that hurts generated clients most.



