Achievement Unlock Pipeline

ELI5

Achievements are rows in an achievements table with a criteria_type (timeline_count, xp_total, streak_days, …) and a criteria_threshold. A background job sweeps every five minutes over recently active users and inserts into user_achievements when the threshold is crossed. The unique (user_id, achievement_id) constraint guarantees one unlock per achievement no matter how many times the checker runs.

Technical Deep Dive

Tables

stateDiagram-v2
    [*] --> Locked
    Locked --> Progressing: criteria partial
    Progressing --> Unlocked: threshold met
    Locked --> Unlocked: threshold met first sweep
    Unlocked --> [*]

achievements (002_gamification_schema.sql):

user_achievements:

CONSTRAINT unique_user_achievement UNIQUE (user_id, achievement_id)

Sweep Sequence

sequenceDiagram
    autonumber
    participant CRON as 5-min cron
    participant G as gamification-service
    participant PG as Postgres
    participant N as NATS GAMIFICATION

    CRON->>G: tick
    G->>PG: SELECT DISTINCT user_id FROM xp_transactions WHERE created_at > now()-'5 min'
    loop per active user
      G->>PG: SELECT achievement_id FROM user_achievements WHERE user_id=?
      G->>PG: SELECT * FROM achievements WHERE id NOT IN (locked set)
      loop per candidate
        G->>G: evaluate criteria_type vs threshold
        alt threshold met
          G->>PG: INSERT user_achievements (...) ON CONFLICT DO NOTHING
          G->>PG: INSERT xp_transactions (+xp_reward, event_type='achievement_unlocked')
          G->>N: publish events.achievement.unlocked
          G->>N: publish events.realtime.achievement_unlocked
        end
      end
    end

Criteria Resolution

Seed Achievements (excerpt)

Key Terms

• special criteria_type → not auto-evaluated; unlocked by a specific event handler (e.g. subscription created → benefactor)
• rarity → cosmetic / sort-order metadata; not used for unlock logic
• tier_required → eligibility gate; locked achievements still appear in progress lists but cannot fire below the tier
• ON CONFLICT DO NOTHING → idempotency on the unique (user_id, achievement_id) constraint

Q&A

Q: What stops an achievement from unlocking twice if the checker is racing with itself? A: The unique_user_achievement UNIQUE constraint plus ON CONFLICT DO NOTHING — at most one row can ever exist for a given (user_id, achievement_id).

Q: How often does the checker actually run? A: Every 5 minutes (background goroutine in gamification-service). It only scans users with XP activity in the last 5 minutes to bound the work.

Q: How is the benefactor achievement unlocked? A: Gamification consumes events.subscription.created; if the new tier is paid, the special-handler unlocks benefactor directly without going through criteria evaluation.

Q: Can an achievement award negative XP? A: The schema permits it (xp_reward INT) and xp_transactions.xp_delta is signed, but no seed achievement does. Treat it as theoretical.

Examples

Like Duolingo badges. The system periodically asks “did you cross 7-day streak today?” and stamps a foil sticker into a sticker book that physically cannot hold two of the same sticker. The sticker hand-out also drops loyalty points into the same XP ledger.