Designing a Gamification System in PostgreSQL

Gamification seems simple on the surface and explodes in complexity as soon as you model it. For TAMSIV, I wanted a complete system.

A Dedicated Schema

The gamification schema is separate from privat and collaborative. Isolation of responsibilities: its own RLS policies, its own RPC functions.

The 5 Tables

• user_stats: total points, level, current/max streak, last activity
• user_badges: many-to-many user/badge relationship, acquisition date
• points_history: every point gain logged for analytics
• daily_challenges: one challenge per day per user
• feed_activity: activity feed for the social feed

12 Progressive Levels

Thresholds: [0, 100, 250, 500, 1000, 2000, 3500, 5500, 8000, 12000, 17500, 25000]. Levels 1-3 are achievable in a few days. Level 12 requires months. Beyond that, a mathematical formula takes over.

Atomic RPCs

When a user completes a task: add points, check level, update streak, check badges, update challenge, log history. All atomic via transactional PostgreSQL functions.

Streaks

Time zone management, streak freezes, break detection. The max streak is capped at 365 days — beyond that, it's addiction, not gamification.