I reported the reviews were gone. Twice. Both were wrong

I run a bot that polls App Store reviews through the API, stores them, drafts replies with an LLM, and either auto-sends or queues them for approval based on the star rating. 43 apps are registered with it.

While auditing the fleet I found the review table frozen at 2026-06-22. 62 rows total, nothing after that. Seven weeks.

This post is the record of me being wrong twice after that.

One question first. When your pipeline says "there's no data," do you have any way to tell whether it's actually absent or the source just isn't giving it to you? I didn't.

Wrong diagnosis #1 — "the collector is dead"

pg_cron calls an Edge Function every six hours. To find failed responses I dug into net._http_response.

select status_code, count(*), max(created)
from net._http_response
where created > now() - interval '14 days' and (status_code is null or status_code >= 400)
group by 1;

Two failures. "Two in two weeks is normal — so why is nothing arriving?"

Wrong. That table's retention is about six hours.

select min(created), max(created), count(*) from net._http_response;
-- 2026-08-10 00:20 to 06:15, 24 rows

You can write interval '14 days' and still be querying six hours of data. The query succeeds, results come back, and the window doesn't exist. It's the kind of misreading that produces no error.

There's a second trap in the same spot. In pg_cron, job_run_details.status = 'succeeded' means net.http_post was enqueued. It says nothing about the HTTP response. "Every cron run succeeded" and "the function never once succeeded" are perfectly compatible states.

Wrong diagnosis #2 — "the reviews were removed"

Having lost trust in the collector, I went to the source. I read customerReviews for all 43 apps with my own API key.

reviews ASC returned        35
reviews in the DB           62
in DB but missing from ASC  27
in ASC but missing from DB   0

That last line looked decisive. The collector missed nothing, so the collector is innocent — and 27 reviews had disappeared. All 5★. 26 of them already had replies sent. The dates spanned the whole range, late April through June 22.

After seeing that 60 of the 62 were 5★ and all from one country, I reported that "Apple removing them via authenticity checks is the most plausible explanation, and it may be an account-level risk worth verifying."

What would you do here? File a support case with Apple, write one more query, or just open a browser?

The actual answer — the owner opened App Store Connect

The reviews were all still there.

Not a removal — the API was under-returning. I tried every combination I could on one map app. The UI shows 3 reviews.

All zero. And yet the same endpoint returned those exact 3 reviews back in May — the bot's stored rows prove it. Why it won't now is not knowable from the API side.

The rules I kept from this

The real remaining risk isn't that reviews are missing. The reply bot uses the same endpoint, so it can't see some apps' reviews. Across the 7 apps with auto-reply enabled, real reviews can sit unanswered. To the user that reads as "I wrote a review and the developer ignored it."

So:

• The ground truth for review count and recency is the ASC UI only. A zero from the API does not mean zero.
• Ratings (star count and average) aren't in the ASC API at all. customerReviews returns only reviews with written text — the majority who left a rating and moved on are excluded. The number actually displayed on the store comes from the unauthenticated public lookup endpoint. That story is split out → The US is my biggest install market and has zero ratings.

curl -s "https://itunes.apple.com/lookup?id=<appId>&country=kr" |
  python3 -c "import json,sys; x=json.load(sys.stdin)['results'][0]; \
print(x['averageUserRating'], x['userRatingCount'])"

• To know whether the Edge Function actually succeeded, read net._http_response, not cron's "succeeded" — and that window is six hours. For anything longer you have to persist responses yourself.

Three things to check right now

1. Do you have queries with a interval '14 days'-style window against a short-retention system table? Run select min(created), max(created) on it first.
2. Does your scheduler's "success" mean the call was made or the response was good? Make sure you know which one you're looking at.
3. In your pipeline, do "zero" and "unknown" land in the same cell? If they aren't distinguishable, the next person will misread them. Guaranteed.

The honest part

I was wrong twice, and both times I reported it confidently. The second one was a heavy conclusion — removal — resting on a single piece of evidence: the API doesn't return them. I never questioned the assumption that the API was ground truth.

I did not find the disproof. The owner opened a browser and it was over. Looking at the UI once was faster than hitting the API five times.

I still don't know the cause. Key role restrictions, an endpoint behavior change, indexing lag — the API gives me no way to separate them. And my first conclusion is void too: "no new reviews since 6-22" leans on the same untrustworthy endpoint. New reviews could exist and simply not be returned. The current state isn't "there are no reviews." It's "I don't know."

Think of the metric on your dashboard that's been sitting at zero the longest. Have you ever confirmed that zero in the source's own UI?