Skipped is not failed, and that one distinction is what made our nightly job alertable

CogniPrep ranks your score against everyone else's, so something has to work out what everyone else's scores actually are. That something is a cron at 02:00 UTC that recalculates mean, median, standard deviation and nine percentiles for every game in the library.

The library currently holds 344 games. You can see the output of last night's run without an account, because the endpoint it feeds is public:

curl https://cogniprep.app/api/games/population-stats/balloon

{"gameId":"balloon","mean":54.32,"median":54,"stdDev":6.55,
 "percentiles":{"10":45,"20":49,"25":50,"40":53,"50":54,"60":56,"75":59,"80":60,"90":63},
 "sampleSize":1792,"lastUpdated":"2026-10-03T02:00:14.800Z"}

That lastUpdated is the cron. Now try a game nobody has played much:

curl https://cogniprep.app/api/games/population-stats/thm-hpti

"sampleSize": 0. Hold onto that, because it is the whole post.

The bug was a counter, not a query

The job's original summary had two numbers: how many games it updated, and how many failed. A game with too few scores to publish percentiles for was counted as a failure, because from the loop's point of view it had not produced an update.

So every night the run reported something like "47 updated, 297 failed", which is not an alert, it is wallpaper. Nobody reads a number that is large and expected. And the day a real query error took out a handful of games, it moved that number from 297 to 302.

The fix is three lines and one extra word:

const updated = results.filter((r) => r.updated).length;
const skipped = results.filter((r) => !r.updated && r.skipped).length;
const failed  = results.filter((r) => !r.updated && !r.skipped).length;

skipped means the sample was below the floor, which is the correct and expected outcome for a game nobody has finished ten times yet. failed means the query errored or timed out. The first number is allowed to be large. The second is supposed to be zero, and now it visibly is.

If you take one thing from this: a counter that is usually non-zero cannot be an alert. Any batch job over a heterogeneous set of items has at least three outcomes, and collapsing "nothing to do" into "error" destroys the only signal you had.

Why failure here is silent, and what that forces

Here is the property that makes this job worth being careful about. When a game's aggregation fails, it keeps its previous stats row. Users carry on being ranked against last week's percentiles, and there is no outward symptom at all. No error page, no empty state, no missing number. Just a quietly stale comparison.

Silent degradation is the failure mode that logging does not cover, because nobody greps logs for an absence. So a non-zero failed count escalates:

if (failed > 0) {
  const failures = results.filter((r) => !r.updated && !r.skipped);
  logMonitoredError(new Error(`Population stats failed for ${failed}/${results.length} games`), {
    failedGames: failures.map((f) => `${f.gameId}: ${f.error ?? 'unknown'}`).join('; '),
  });
}

There are two loggers imported in that file under different names, one for the console and one that reports to the error tracker, and the import is aliased with a comment saying which is which. That looks like fussiness until you have shipped a silent-degradation bug and found the evidence sitting in a log line nobody subscribed to.

The HTTP status is a separate decision from the summary

The cron route then has to decide what to return, and "any failure is a 500" is the wrong rule.

const everythingFailed = summary.failed > 0 && summary.updated === 0 && summary.skipped === 0;

A partial failure returns 200. The run did real work, and a platform-level retry would redo all the successes to re-attempt a few failures, which is the expensive way to achieve nothing. The counts are in the response body and the error tracker already has the detail.

A run where everything failed returns 500, so the platform's cron view shows red and the retry fires. That is a different condition: it means the database was unreachable, not that three games had a bad night.

And note what everythingFailed requires: skipped === 0 as well. Without that clause, a brand new deployment where almost nothing has been played yet would report total failure every single night.

The query, and the two bounds on it

Each game's statistics come out of Postgres in one round trip. Nine percentile_cont ordered-set aggregates plus count, avg and stddev_pop, computed in the database, zero rows transferred:

select count(*)::int,
       avg(raw_score::numeric),
       stddev_pop(raw_score::numeric),
       percentile_cont(0.10) within group (order by raw_score::numeric),
       ...
from (
  select raw_score from game_sessions
  where game_id = $1
  order by completed_at desc
  limit 20000
) as recent_scores

The inner LIMIT matters twice. It rides a (game_id, completed_at) index so the scan stops early rather than reading a game's entire history and sorting all of it, and it puts a ceiling on a cost that otherwise grows forever against a 60 second function limit and a role-level statement timeout.

The size of that ceiling was the actual decision. 20,000 is deliberately generous rather than minimal, because these figures are what users are ranked against. At 20k the percentiles are effectively as stable as all-time. At a few hundred, p90 would swing night to night and whoever happened to practise most recently would get to define the baseline. A bound chosen purely for speed would have been a bound chosen against the feature.

The floor is 10. Below that, percentiles are noise, so the game is skipped and its row is left alone.

Concurrency, capped on purpose

The games are independent, so they run four at a time rather than one after another:

const POPULATION_STATS_CONCURRENCY = 4;

Not unbounded. These are heavy aggregate scans over the same table, and firing 344 of them at once would saturate the connection pool and have them compete with each other. A small window overlaps the round trips without turning the nightly job into a self-inflicted load spike. The helper preserves input order, and the per-game worker never rejects, so one bad game cannot abort the rest of the run.

One cache gotcha worth stealing

The read path wraps each game's lookup in unstable_cache with a five minute revalidate and a per-game tag. The subtlety is that the cached function reference has to be stable for deduplication to work, so the functions live in a module-scope Map keyed by game id rather than being created per call:

const statsCache = new Map<string, () => Promise<SelectPopulationStats>>();

And the cron does not wait for that TTL. After the run it invalidates the tag for every game it actually updated, so the first request after 02:00 gets fresh figures instead of up to five minutes of yesterday:

const updatedGameIds = summary.results.filter((r) => r.updated).map((r) => r.gameId);
for (const gameId of updatedGameIds) {
  revalidateTag(`population-stats-${gameId}`, 'default');
}

Only the updated ones. Invalidating a skipped game's tag would throw away a perfectly good cache entry to re-read a row that did not change.

See it

Pick any game from cogniprep.app/games, take the id out of the URL, and hit the endpoint:

curl https://cogniprep.app/api/games/population-stats/<id>

No auth, because these are population figures and not anybody's data. Compare lastUpdated against 02:00 UTC, and compare sampleSize across a few games. A number in the thousands is a game the job updated last night. A 0 is a game it skipped, and the figures you are looking at are not standing on a real sample. That difference being visible in the response, rather than hidden behind a fallback that looks like data, is the same decision as the counter.

If you want to see percentiles applied to your own attempt rather than read about them, the hub at cogniprep.app/games lists all 52 providers it covers, and the plans are on cogniprep.app/pricing.