Event-Sourced XP Ledger

ELI5

XP is never stored as a single number on a user — it’s stored as an append-only ledger of +10, +5, -20 rows, just like a bank account. To get a balance, sum the rows (or read the cached user_xp_summary materialised view). Append-only means concurrent writers cannot clobber each other and every change is auditable.

Technical Deep Dive

xp_transactions (002_gamification_schema.sql)

There is no UPDATE trigger and no PK on (user_id, event_id) — duplicate prevention is the application’s responsibility (Gamification dedupes on the source event’s event_id).

Aggregation View

CREATE MATERIALIZED VIEW user_xp_summary AS
SELECT
  user_id,
  SUM(xp_delta) AS total_xp,
  COUNT(*) AS transaction_count,
  MAX(created_at) AS last_xp_earned,
  FLOOR(SUM(xp_delta) / 1000.0) AS level
FROM xp_transactions
GROUP BY user_id;

Refreshed CONCURRENTLY (requires the unique index on user_id) by refresh_user_xp_summary() — scheduled via the scheduled_jobs table; Gamification additionally pushes a 5-minute Redis cache keyed user:stats:{user_id}.

Class View

classDiagram
    class XPTransaction {
      +bigint id
      +uuid user_id
      +text event_type
      +uuid event_id
      +int xp_delta
      +int xp_total
      +int xp_multiplier
      +text reason
      +jsonb metadata
      +bool is_bonus
      +text bonus_reason
      +timestamptz created_at
    }
    class UserXPSummary {
      <<materialized view>>
      +uuid user_id
      +int total_xp
      +int transaction_count
      +timestamptz last_xp_earned
      +int level
    }
    XPTransaction "*" --> "1" UserXPSummary : aggregated

Award Sequence

sequenceDiagram
    autonumber
    participant N as NATS GAMIFICATION
    participant G as gamification-service
    participant PG as Postgres
    participant RD as Redis (ZSET)
    participant N2 as NATS (xp + realtime)

    N->>G: events.timeline.completed (event_id=E)
    G->>PG: SELECT 1 FROM xp_transactions WHERE event_id=E
    alt already processed
      G-->>N: ACK (no-op)
    else first time
      G->>G: base=10, mult=tier_mult(user)
      G->>PG: INSERT xp_transactions (+10×mult/100, event_id=E)
      G->>RD: ZINCRBY leaderboard:global +amount user_id
      G->>RD: ZINCRBY leaderboard:weekly:{week} +amount
      G->>N2: publish events.xp.earned
      G->>N2: publish events.realtime.xp_earned
      G-->>N: ACK
    end

Key Terms

• Event sourcing → store the deltas, derive the state; opposite of the more common “store the state, log the changes”
• xp_total denormalised → snapshot of running total at insert time so you can reconstruct historical balances without re-aggregating
• CONCURRENTLY refresh → does not lock readers; requires UNIQUE INDEX on the view (provided on user_id)
• xp_multiplier integer encoding → avoids floating-point drift on sums

Q&A

Q: Can a row in xp_transactions ever be UPDATEd? A: No. The schema declares no UPDATE trigger and the architectural invariant is append-only. Corrections are made by inserting a compensating negative row.

Q: What does xp_multiplier=120 mean? A: 1.2× — multipliers are integer hundredths. A xp_delta already reflects the multiplier (base × xp_multiplier / 100); the column is stored for audit, not re-calculation.

Q: What stops two NATS deliveries of the same events.timeline.completed from awarding XP twice? A: Gamification SELECTs by event_id before INSERT. JetStream duplicate_window=30s on GAMIFICATION is a second line of defence.

Q: Why is level derived as FLOOR(total_xp / 1000) in the view but defined exponentially in levels? A: The view’s level is a coarse fast-path estimate; the authoritative level comes from joining total_xp against levels.xp_required (which uses 1000 × 1.1^(level-1)). Treat the view’s level as cache, not truth.

Examples

Like a chequebook register. You don’t erase your balance and overwrite it; you write each cheque (-50) and each deposit (+200) on a new line. Your balance is the sum. If the balance looks wrong you re-add the lines — you don’t argue with the latest number.