User Rewind rollups

1. Foundational mental model

Rewind is a year-in-review: “Your Rewind” for a signed-in player and “Flags.games Rewind” for the whole community. The data it needs is spread across tables with different lifetimes. xpAwards is the only record of which mode earned XP, and the 03:00 UTC cleanup deletes rows older than 90 days. activityFeed drops non-daily events after 30 days. The rolling globeSnapshotCounters overwrite themselves every UTC day.

So the system runs in three tiers. Short-lived sources get folded into durable monthly rollups before they expire. In January, a cron reads the rollups plus the tables that are kept forever and writes one year snapshot per player and one for the site. A Rewind page then needs a single indexed read.

Three tiers. Rollups rescue data before purges; year snapshots turn a year of rows into one document per reader.

The design comes from docs/plans/2026-flags-rewind.md, which targets a /rewind/2026 page for January 1, 2027 UTC. The data side is built. The page is not.

2. Implementation status & known gaps

Active invariants & limitations

• Active today: four crons in crons.ts feed Rewind: snapshot-daily-play-volume (daily 23:55 UTC), rollup-monthly-xp (day 1, 04:00), rollup-monthly-community (day 1, 04:30), trigger-year-end-snapshots (day 1, 05:00, acts only in January).
• Active today: every XP claim through insertXpAwardAndIncrementRollup (solo, multiplayer, daily, weekly spin) also increments the player’s monthlyXpRollups row in the same transaction. The monthly cron then recomputes the previous month from xpAwards while those rows still exist.
• Active today: public queries userRewind.getUserRewindYearSummary, userRewind.getGlobalRewindYearSnapshot, and globalRewindRollups.getGlobalRewindYearSummary exist with return validators.
• Known gap: no /rewind route exists under apps/web/src/routes/, and nothing in the web client calls the three queries. They are exercised only by userRewind.test.ts.
• Known gap: the first real run is January 1, 2027 at 05:00 UTC. Before then, userYearSnapshots and globalYearSnapshots are empty unless someone runs the internal mutations by hand.
• Known gap: solo and multiplayer game counts come from XP awards, so guest plays and games that did not claim XP are not counted. Per-user per-country accuracy is derived only from Daily rounds.

Account deletion (account.ts) removes the player’s monthlyXpRollups and userYearSnapshots rows, and the account export (accountExport.ts) includes both under a rewind section.

3. Concrete implementation

Cron schedule

apps/web/src/convex/crons.ts

crons.daily(
  "snapshot-daily-play-volume",
  { hourUTC: 23, minuteUTC: 55 },
  internal.globalRewindRollups.snapshotDailyPlayVolume,
  {}
);

crons.monthly(
  "rollup-monthly-xp",
  { day: 1, hourUTC: 4, minuteUTC: 0 },
  internal.globalRewindRollups.rollupMonthlyXpForPreviousMonth,
  {}
);

crons.monthly(
  "rollup-monthly-community",
  { day: 1, hourUTC: 4, minuteUTC: 30 },
  internal.globalRewindRollups.rollupMonthlyCommunityForPreviousMonth,
  {}
);

crons.monthly(
  "trigger-year-end-snapshots",
  { day: 1, hourUTC: 5, minuteUTC: 0 },
  internal.userRewind.triggerYearEndSnapshotCron,
  {}
);

The order on the 1st matters: December’s XP and community rollups are rewritten at 04:00 and 04:30, so the 05:00 snapshot run reads a finished December.

January gate

crons.monthly has no month field, so the year-end job fires twelve times a year and returns early in eleven of them:

apps/web/src/convex/userRewind.ts

export const triggerYearEndSnapshotCron = internalMutation({
  args: {},
  returns: v.null(),
  handler: async (context) => {
    const now = new Date();
    // 0 is January. We run it on January 1st to generate snapshots for the previous year.
    if (now.getUTCMonth() !== 0) {
      return null;
    }
    const year = now.getUTCFullYear() - 1;

    // 1. Generate global year snapshot
    await context.scheduler.runAfter(0, internal.userRewind.generateGlobalYearSnapshot, {
      year,
    });

    // 2. Start user snapshots generation action
    await context.scheduler.runAfter(
      0,
      internal.rewindSnapshotAction.runUserYearSnapshotsGeneration,
      {
        year,
        cursor: null,
      }
    );

Per-user fan-out

A single mutation cannot build a snapshot for every player inside Convex’s transaction limits, so an action pages through players in batches of DEFAULT_USER_SNAPSHOT_BATCH_SIZE = 30. Each player gets its own mutation, and the action reschedules itself 100 ms later with the next cursor:

apps/web/src/convex/rewindSnapshotAction.ts

for (const userId of page.userIds) {
  await context.runMutation(internal.userRewind.generateUserYearSnapshot, {
    year: args.year,
    userId,
    force: args.force,
  });
}

if (!page.isDone && page.continueCursor) {
  await context.scheduler.runAfter(
    100,
    internal.rewindSnapshotAction.runUserYearSnapshotsGeneration,
    {
      year: args.year,
      cursor: page.continueCursor,
      batchSize: numItems,
      force: args.force,
    }
  );
}

January 1 run. The global snapshot and the first user batch are scheduled together; user batches chain until the players table is exhausted.

What a user snapshot contains

buildSnapshotForUser loads the year’s monthlyXpRollups, raw xpAwards, dailyChallengeCompletions, learnReviewEvents, and the matching dailyChallengeDayArchives, then hands them to the pure builder in $lib/rewind. The stored document:

Field	Source
totalXpEarned, xpByMode (solo, multiplayer, daily, weekly)	monthlyXpRollups, plus raw xpAwards for months with no rollup row
gamesByMode (solo, multiplayer, daily, weekly, learn)	Award counts per sourceType; learn is a session count from review timestamps
dailiesPlayed, dailiesWon, bestDailyStreak	dailyChallengeCompletions in the year
hardestCountries, easiestCountries	Daily resultPattern bits joined with the day archive’s rounds
countriesLearned, learnReviewCount	Distinct countries rated good or easy, and total reviews
levelAtYearEnd, totalXpAtYearEnd	Current players.xp minus XP awarded after December 31
favoriteMode	pickFavoriteMode over solo, multiplayer, daily, learn counts

The global snapshot stores totalDailiesPlayed, totalPlaysAllModes, hardestFlagOfYear, busiestUtcDay, mostFrequentDailyCountry, hardestRound, and totalRegisteredPlayers.

Read contract

getUserRewindYearSummary returns a three-state union, so a future page can tell “sign in” from “not built yet”:

apps/web/src/convex/userRewind.ts

const userId = await getAuthUserId(context);
if (!userId) {
  return { status: "unauthenticated" as const, year: args.year };
}

const [existing] = await context.db
  .query("userYearSnapshots")
  .withIndex("by_user_year", (queryBuilder) =>
    queryBuilder.eq("userId", userId).eq("year", args.year)
  )
  .take(1);

if (!existing) {
  return { status: "pending" as const, year: args.year };
}

4. Internal mechanics & mathematics

Two writers, one monthly row

monthlyXpRollups has two writers. insertXpAwardAndIncrementRollup adds a delta on every new award. On the 1st, upsertMonthlyXpRollup scans xpAwards through by_awarded_at for the previous month and overwrites each user’s row with the recomputed totals. The incremental path gives live-ish numbers; the cron corrects any drift while the 90-day source is still there.

Both writers handle duplicate rows the same way. They read up to INDEXED_DEDUPE_TAKE = 8 rows for (userId, yearMonth) and call keepOldestIndexedDoc, which keeps the row with the smallest _creationTime and deletes the rest. If two concurrent inserts both create a row, the loser’s delta is re-applied to the winner:

apps/web/src/convex/lib/xpAwards.ts

const winner = await keepOldestIndexedDoc(context, claimedRows);
if (winner && winner._id !== insertedId) {
  await context.db.patch(winner._id, {
    soloXp: winner.soloXp + delta.soloXp,
    // ...same for every counter...
    totalXp: winner.totalXp + delta.totalXp,
    lastUpdatedAt: Date.now(),
  });
}

When the snapshot builder merges rollups and raw awards, it skips any raw award whose UTC month already has a rollup row. That prevents double counting for every month after incremental rollups shipped.

XP at year end

A player’s XP on December 31 is not stored anywhere. The snapshot reconstructs it from the current total:

$$\text{XP}_{\text{year end}} = \max\!\big(0,\ \text{XP}_{\text{now}} - \textstyle\sum_{a \,:\, t_a > t_{\text{end}}} \text{xp}_a\big)$$

where $t_{\text{end}}$ is Date.parse("<year>-12-31T23:59:59.999Z") from yearBounds. On January 1 the subtracted sum covers about five hours of play. levelAtYearEnd is getLevelFromXp of that value.

Country accuracy

For each Daily completion with a finite, non-negative resultPattern, round $i$ counts as solved when bit $i$ is set (pattern & (1 << index)). The round’s country comes from that day’s archive. Per country $c$:

$$r_c = \frac{\text{timesCorrect}_c}{\text{timesShown}_c}, \qquad \text{eligible if } \text{timesShown}_c \ge 5$$

MIN_COUNTRY_APPEARANCES_FOR_HIGHLIGHT = 5 and MAX_COUNTRY_HIGHLIGHTS = 3. Hardest sorts by $r_c$ ascending, then timesShown descending, then country code A→Z. Easiest sorts by $r_c$ descending, then timesShown descending, then country code Z→A (b.countryCode.localeCompare(a.countryCode)), even though the inline comment says “alphabetical”. The two lists can overlap for a player with few eligible countries.

Streaks and Learn sessions

calculateBestStreakForYear dedupes and sorts the year’s completion dates, parses each at T12:00:00Z, and extends the run when Math.round(diff / 86 400 000) is exactly 1. The streak resets on January 1 by construction, since only in-year dates are passed.

Learn sessions count gaps between consecutive reviews larger than LEARN_SESSION_GAP_MS = 15 * 60 * 1000:

$$\text{sessions} = 1 + \big|\{\, i : t_i - t_{i-1} > 900\,000 \text{ ms} \,\}\big|$$

Favorite mode

pickFavoriteMode compares solo games, multiplayer games, daily completions, and learn sessions. If the maximum is 0, or two or more modes tie for the maximum, it returns "daily". Weekly spins are not a candidate.

Global aggregates

• hardestFlagOfYear takes each month’s stored hardestCountries list and keeps the lowest monthly solve rate per country, then the lowest overall. It is a minimum of monthly rates, not a year-weighted rate. With no data it falls back to "US" at 100%.
• hardestRound sums roundAggregates for round indexes 0–4 across months and picks the lowest $\text{solved}/\text{attempted}$. With no attempts it reports round 0 at 100%.
• mostFrequentDailyCountry counts correctCountryCode across the year’s day archives, ties broken A→Z, fallback "US" with 0 appearances.
• Percentages are rounded to two decimals with Math.round(rate * 10000) / 100.

Query cost

User snapshot reads are bounded by the year. monthlyXpRollups and monthlyCommunityRollups are 12 point lookups each. Day archives switch from point lookups to one range scan once a player has DAY_ARCHIVE_POINT_LOOKUP_THRESHOLD = 30 or more dated completions. Paged reads use INDEXED_QUERY_PAGE_SIZE = 500.

5. Threat model, failure modes & edge cases

Where a year-end number can go wrong

• Idempotency. generateUserYearSnapshot and generateGlobalYearSnapshot return early ("skipped", or the stored snapshot) when a row for that key exists and force is not set. Re-running the January job, or the action resuming partway, does not duplicate rows. snapshotDailyPlayVolume returns "skipped" when the value is unchanged and patches otherwise. Community rollups are upserts keyed by yearMonth.
• Five-minute blind spot. The play-volume snapshot runs at 23:55 UTC. Plays between 23:55 and midnight bump playsToday after the snapshot, and the counter resets on the next day’s first play, so those plays are never recorded.
• Quiet days. If nobody plays on a UTC day, globeSnapshotCounters.utcDay still names the previous day. The 23:55 job then writes (or skips) the previous day’s row and produces no row for the quiet day. Year sums treat the missing day as 0.
• Forced rebuilds late in the year. force: true recomputes from current data. After 90 days, raw xpAwards are gone. Months with a rollup row are unaffected, but the “XP after year end” subtraction loses purged awards, so totalXpAtYearEnd and levelAtYearEnd read high. Rebuild in January if you need to rebuild at all.
• Missing resultPattern. Older completions predate the field. They count toward dailiesPlayed and streaks but are skipped for country accuracy.
• Who gets a snapshot. Every players row, including players with no activity that year. Their snapshot is mostly zeros with favoriteMode: "daily". The plan asks the UI to lead with the global Rewind in that case.
• Partial runs. If the action throws mid-batch, players already processed keep their rows and later ones have none. getUserRewindYearSummary reports "pending" for them. Restart with runUserYearSnapshotsGeneration({ year, cursor: null }); existing rows are skipped.
• Deleted accounts. Account deletion removes the player’s rollups and snapshots. globalYearSnapshots stores only aggregates, so it is unaffected. totalRegisteredPlayers is a count at generation time.
• Year validation. Every public query calls yearBounds, which throws for non-integers and years outside 1970–9999 (userRewind.test.ts checks year: 0).

6. Architectural tradeoffs & non-goals

Choice	Alternative	Why this one
Precomputed year snapshot, one read per page view	Aggregate at read time	Raw xpAwards for most of the year no longer exist in December; read-time aggregation would also scan hundreds of rows per viewer.
Monthly XP rollup written on every award and recomputed monthly	Monthly cron only	The incremental write keeps rollups correct even if a monthly cron run fails; the recompute corrects drift while source rows remain.
Action paginates players, one mutation per user	One big mutation	Convex mutations have read and write limits; per-user mutations also make partial progress durable.
Monthly cron with a January check	A yearly schedule	Convex crons.monthly has no month argument; the early return costs one no-op mutation per month.
Snapshot on January 1 at 05:00 UTC	Snapshot on December 31	Waits for December’s rollups at 04:00 and 04:30, and UTC matches every other day boundary in the game.
Pure builders in $lib/rewind/	Logic inside Convex handlers	Vitest covers streaks, sessions, accuracy, and global picks without a Convex test harness.

Non-goals: response-speed percentiles (no per-user timing is stored in Convex), solo and multiplayer per-country accuracy, local-time year boundaries, and live Rewind numbers during the year. The UI itself (/rewind/2026 card flow, §4 of the plan) is still to be built.