How to Backfill Achievements for Existing Users (2026)

You've shipped an achievement system, but your app already had users before you built it. Some of them completed 500 lessons before achievements existed. Others hit a 30-day streak last month with no milestone to mark it. The question is how to retroactively credit the users who already qualify — without spamming everyone, without double-crediting on retries, and without locking up your database for an hour.

The problem is more tractable than it appears, but the details matter. This post covers the data model you need, the three query patterns that cover most achievement types, the production concerns that will catch you if you ignore them, and where Trophy removes most of this work for metric and streak-based achievements.

The Data Model

Before you write a single backfill query, your schema needs to support it. Three things have to be true.

You need a way to evaluate the qualifying condition from historical data. For cumulative achievements ("completed 500 lessons"), this means a table that records individual events — not just a denormalized total. SUM(value) FROM lesson_events WHERE user_id = X is backfillable. A users.lesson_count integer that gets incremented in-place is not, unless you can reconstruct the history from somewhere else.

You need an achievements table that records completions, not just current state. The minimum schema:

CREATE TABLE user_achievements (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES users(id),
  achievement_key VARCHAR(255) NOT NULL,
  completed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  backdated       BOOLEAN NOT NULL DEFAULT false,

  -- Prevents double-crediting on re-runs
  UNIQUE (user_id, achievement_key)
);

CREATE INDEX ON user_achievements (user_id);
CREATE INDEX ON user_achievements (achievement_key);

The UNIQUE (user_id, achievement_key) constraint is load-bearing. It means a backfill script can be re-run after a partial failure and the database will reject duplicates, rather than requiring you to track which users were already processed in application code.

The backdated column is optional but useful for analytics — it lets you distinguish organic completions from retroactive credits when measuring the achievement system's impact.

You need to know which achievements a user has already seen. Backdating should be silent by default: you credit the user, but you don't immediately fire a notification. The next time they open the app, your client needs to know which completed achievements haven't been surfaced yet. A shown_at column on user_achievements, or a separate shown_achievements table, is the clean way to track this.

The Three Backfill Patterns

Most achievements fall into one of three categories. Each has a different query pattern.

Cumulative achievements

These are milestones based on a running total: lessons completed, km run, words written, items purchased. The backfill query aggregates your event history and finds users who already qualify.

// Step 1: find all users who qualify but haven't been credited yet
async function findQualifyingUsers(
  achievementKey: string,
  metricTable: string,
  metricColumn: string,
  threshold: number
): Promise<string[]> {
  const result = await db.query(`
    SELECT e.user_id
    FROM (
      SELECT user_id, SUM(${metricColumn}) AS total
      FROM ${metricTable}
      GROUP BY user_id
    ) e
    LEFT JOIN user_achievements ua
      ON ua.user_id = e.user_id
      AND ua.achievement_key = $1
    WHERE e.total >= $2
      AND ua.id IS NULL
  `, [achievementKey, threshold]);

  return result.rows.map(r => r.user_id);
}

// Step 2: credit them in bulk
async function creditUsers(
  userIds: string[],
  achievementKey: string
): Promise<void> {
  // INSERT ... ON CONFLICT DO NOTHING makes this safe to re-run
  const placeholders = userIds
    .map((_, i) => `($${i * 3 + 1}, $${i * 3 + 2}, $${i * 3 + 3})`)
    .join(', ');

  const params = userIds.flatMap(id => [id, achievementKey, true]);

  await db.query(`
    INSERT INTO user_achievements (user_id, achievement_key, backdated)
    VALUES ${placeholders}
    ON CONFLICT (user_id, achievement_key) DO NOTHING
  `, params);
}

For large user bases, batch the INSERT rather than inserting one row at a time. Add a WHERE e.user_id > $cursor clause to paginate through the qualifying set rather than loading all user IDs into memory at once.

One-time action achievements

These cover discrete events: completing onboarding, linking a social account, making a first purchase. The qualifying condition is a row existing somewhere, not a cumulative sum.

async function backfillOnboardingAchievement(): Promise<void> {
  // Find users who completed onboarding before the achievement existed
  const qualifying = await db.query(`
    SELECT u.id AS user_id
    FROM users u
    LEFT JOIN user_achievements ua
      ON ua.user_id = u.id
      AND ua.achievement_key = 'onboarding-complete'
    WHERE u.onboarding_completed_at IS NOT NULL
      AND ua.id IS NULL
  `);

  if (qualifying.rows.length === 0) {
    console.log('No users to backfill');
    return;
  }

  console.log(`Backfilling ${qualifying.rows.length} users`);

  const placeholders = qualifying.rows
    .map((_, i) => `($${i * 3 + 1}, $${i * 3 + 2}, $${i * 3 + 3})`)
    .join(', ');

  const params = qualifying.rows.flatMap(r => [r.user_id, 'onboarding-complete', true]);

  await db.query(`
    INSERT INTO user_achievements (user_id, achievement_key, backdated)
    VALUES ${placeholders}
    ON CONFLICT (user_id, achievement_key) DO NOTHING
  `, params);

  console.log('Backfill complete');
}

Streak achievements

Streak achievements require a bit more care. A user's current streak at the time of the backfill might not reflect their longest ever streak. If the achievement is "reach a 30-day streak" and a user hit 45 days six months ago but is currently at day 3, querying users.current_streak would miss them entirely.

The correct backfill queries against streak history rather than current state:

-- You need a streak history table, not just current streak length
CREATE TABLE streak_events (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id),
  streak_length INTEGER NOT NULL,
  recorded_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Find users whose maximum historical streak meets the threshold
SELECT se.user_id
FROM (
  SELECT user_id, MAX(streak_length) AS max_streak
  FROM streak_events
  GROUP BY user_id
) se
LEFT JOIN user_achievements ua
  ON ua.user_id = se.user_id
  AND ua.achievement_key = 'streak-30-days'
WHERE se.max_streak >= 30
  AND ua.id IS NULL;

If you only store current streak and have no streak history, you cannot backfill streak achievements accurately. The options are to accept the gap (credit users who currently qualify and accept that lapsed high-streakers miss out), or to defer the backfill until enough streak history accumulates in the new table.

Production Concerns

Order matters for tiered achievements. If you have Bronze (100 completions), Silver (500), and Gold (1,000), backfill them in ascending order within each user's processing. Inserting Gold before Bronze works at the database level, but it breaks any downstream logic that expects to see the lower tiers already present.

Rate limit your downstream effects. The backfill INSERT is cheap. What's expensive is everything triggered next: achievement emails, XP awards, leaderboard updates. A backfill that credits 50,000 users and each triggers an email queues 50,000 emails in seconds. Either suppress downstream effects entirely for backdated completions using the backdated flag as a gate, or process them at a controlled rate.

Don't send real-time notifications. Achievement notifications land well when they're proximate to the action that earned them. A push notification about a 30-day streak the user hit eight months ago is confusing rather than motivating. Surface backdated completions passively on next app open — a "here's what you've earned" summary — rather than firing the same celebration that new completions trigger.

Test on a small cohort first. Before running across your full user base, scope the backfill to a single test user ID, then a 1% sample. Verify the counts look correct, check that downstream effects aren't misfiring, and confirm the ON CONFLICT DO NOTHING behaviour holds on a second run. A bad script run against 200,000 users is considerably harder to recover from than one run against 2,000.

How Trophy Solves This

If you're using Trophy' achievements system, the backfill story for metric and streak achievements is handled automatically. When you activate a metric or streak achievement in Trophy's dashboard, Trophy checks every existing user's stored metric totals and streak history and completes the achievement silently for any user who already qualifies. No script, no query, no production concern about ordering or rate limiting.

This works because Trophy stores the cumulative metric totals and streak records that the queries above are trying to reconstruct from raw event tables. The qualifying condition evaluation happens inside Trophy rather than in your application code.

For API achievements — the one-time action type — you still need a script, because the qualifying condition is defined by your application's own data. The script is simpler than the DIY version, since you only need to find qualifying users and call Trophy's completion API for each one. The idempotency key pattern ensures re-runs are safe:

import { TrophyApiClient } from '@trophyso/node';

const trophy = new TrophyApiClient({ apiKey: process.env.TROPHY_API_KEY });

async function backfillAPIAchievement(achievementKey: string): Promise<void> {
  const qualifying = await db.query(`
    SELECT id FROM users
    WHERE onboarding_completed_at IS NOT NULL
    ORDER BY created_at ASC
  `);

  for (const user of qualifying.rows) {
    await trophy.achievements.complete(achievementKey, {
      user: { id: user.id },
      // Stable key per user and achievement — re-running returns 202 for
      // users already credited, with no change to their state
      idempotencyKey: `backfill-${achievementKey}-${user.id}`,
    });

    // Brief pause to avoid rate limit pressure on large user bases
    await new Promise(resolve => setTimeout(resolve, 50));
  }
}

One important detail: Trophy does not fire the achievement.completed webhook for backdated completions, whether backdating happens automatically or through the completion API with an idempotency key. If your downstream effects depend on that webhook, call them explicitly in the script for each user, or suppress them and accept that backdated users skip those flows.

FAQ

What if I only have denormalized totals, not individual event records?

You can't backfill cumulative achievements accurately without event history. The options are to use the current total as a proxy (credit users whose count now meets the threshold, accepting that churned users who once qualified will be missed), to look for event history in logs or analytics tools, or to start recording individual events now and run the backfill once enough history exists. Going forward, storing event rows rather than only incrementing totals is the right schema choice.

Should backdated achievements trigger XP awards?

It depends on whether XP inflation matters to your economy. Retroactively awarding XP to long-tenured users can distort leaderboards and level distributions if the amounts are significant. A common approach is to award a reduced amount for backdated completions, or to skip XP entirely for achievements completed before a certain date. The backdated flag on user_achievements is the clean gate for this logic.

How do I recover from a backfill script that fails partway through?

The ON CONFLICT DO NOTHING pattern makes the script re-runnable from the beginning — users already credited are silently skipped. For very large tables where re-running from scratch is expensive, add cursor-based pagination using the last successfully processed user_id as a restart point, stored in a checkpoint table or flat file between runs.

Can I backfill composite achievements directly?

Composite achievements complete automatically when all prerequisites are met. Backfill the prerequisite achievements first, and any user who then satisfies all prerequisites will have the composite credited automatically as a side effect, whether you're managing the system yourself or using Trophy.

How do I tell users about retroactively credited achievements without it feeling strange?

Avoid real-time push notifications for backdated completions. Surface them on next app open with a summary screen — "You've earned 3 achievements based on your activity" — and let users navigate to them rather than interrupting with a notification about something that happened months ago. The celebration mechanic works best when it's close to the qualifying action, so use it sparingly for credits that aren't.

Where to Go Next

For Trophy's full achievement configuration reference — trigger types, status management, and the completion API — see the Trophy Achievements documentation. The idempotency key pattern used in the backfill script is documented in the Trophy API idempotency guide.

For connecting achievement completions to your XP system, How to Sync XP Across Devices Without Firebase covers the server-authoritative model that ties the two together. And for the measurement question of whether backfilling is moving your retention numbers, How to Measure Points and Levels Across All Your Users covers the analytics layer.