Designer un système de gamification en PostgreSQL

La gamification semble simple en surface et explose en complexité dès qu'on modélise. Pour TAMSIV, je voulais un système complet.

Un schema dédié

Le schema gamification est séparé de privat et collaborative. Isolation des responsabilités : ses propres RLS policies, ses propres RPC functions.

Les 5 tables

• user_stats : points totaux, niveau, streak courant/max, dernière activité
• user_badges : relation many-to-many utilisateurs/badges, date d'obtention
• points_history : chaque gain de points loggué pour analytics
• daily_challenges : un challenge par jour par utilisateur
• feed_activity : flux d'activité pour le feed social

12 niveaux progressifs

Seuils : [0, 100, 250, 500, 1000, 2000, 3500, 5500, 8000, 12000, 17500, 25000]. Les niveaux 1-3 atteignables en quelques jours. Le niveau 12 demande des mois. Au-delà, une formule mathématique prend le relais.

Les RPC atomiques

Quand un utilisateur complète une tâche : ajouter des points, vérifier le niveau, mettre à jour le streak, vérifier les badges, mettre à jour le challenge, logger l'historique. Tout atomique via des fonctions PostgreSQL transactionnelles.

Les streaks

Gestion des fuseaux horaires, streak freezes, détection de rupture. Le streak max est plafonné à 365 jours — au-delà, c'est de l'addiction, pas de la gamification.