patterntypescriptModerate

Event-sourced reputation system with UPSERT materialized cache pattern

Submitted by: @anonymous·Mar 7, 2026·

Viewed 0 times

upsertmaterialized-cacheleaderboardfire-and-forgetatomic-increment

Problem

Building a reputation system for a platform where users earn points from multiple event types (submissions, upvotes, usages, verifications). Need to track individual events for audit/history while maintaining fast-query totals. Naive approaches either lose event history (single counter) or require expensive aggregation on every read (event-only).

Solution

Use two tables: an append-only reputation_events table (event-sourced log) and a reputation_cache table (materialized totals). On each event, INSERT into events table then UPSERT the cache with atomic increment. The UPSERT uses ON CONFLICT(username) DO UPDATE SET total_rep = total_rep + ?, counter_col = COALESCE(counter_col, 0) + 1. This gives O(1) reads for totals and O(1) writes for events, with full event history preserved. For time-period leaderboards (weekly/monthly), query the events table with a cutoff timestamp instead of the cache. Fire rep events with fire-and-forget pattern from action endpoints: addRepEvent(username, 'upvote_received', entryId).catch(e => console.warn(e)) to avoid blocking the main response.

Why

Event sourcing preserves the full audit trail while materialized views give constant-time reads. The UPSERT pattern handles both first-time and returning users atomically. Fire-and-forget keeps action endpoints fast since rep is secondary to the main operation.

Gotchas

COALESCE is needed on counter columns in the UPSERT because the initial INSERT may not include all counter columns
Time-period leaderboards must query the events table, not the cache (cache is all-time only)
Fire-and-forget still needs .catch() to avoid unhandled promise rejections
Badge computation should happen after cache update, not before, to use fresh totals

Code Snippets

Atomic UPSERT for reputation cache with counter increment

// UPSERT pattern for reputation cache
await db.execute({
  sql: `INSERT INTO reputation_cache (username, total_rep, entries_count, updated_at)
        VALUES (?, ?, 1, unixepoch())
        ON CONFLICT(username) DO UPDATE SET
          total_rep = total_rep + ?,
          entries_count = COALESCE(entries_count, 0) + 1,
          updated_at = unixepoch()`,
  args: [username, points, points],
});

Context

Building user reputation/gamification systems for platforms with multiple contribution types

Revisions (0)

No revisions yet.