Schema gamification PostgreSQL : 5 tables, 12 niveaux

Points clés à retenir : Un système de gamification solide repose sur un schéma PostgreSQL dédié avec 5 tables, des RPC atomiques transactionnelles, et une progression calibrée sur 12 niveaux. Le secret : isoler la gamification dans son propre schéma, plafonner les streaks à 365 jours pour éviter l'addiction, et tout rendre atomique pour garantir la cohérence des données.

La gamification semble simple en surface. Des points, des badges, un leaderboard — ça a l'air facile, non ? En réalité, dès qu'on commence à modéliser sérieusement, la complexité explose. Pour TAMSIV, je voulais un système complet : niveaux, badges, streaks, challenges quotidiens, historique de points, et un feed d'activité. Le tout dans PostgreSQL, avec des garanties transactionnelles.

Voici comment j'ai conçu le schéma de gamification de TAMSIV, table par table, décision par décision.

Pourquoi un schéma PostgreSQL dédié pour la gamification ?

TAMSIV utilise déjà deux schémas dans Supabase : privat pour les données utilisateur (tâches, mémos, événements) et collaborative pour les fonctionnalités de groupe. J'aurais pu ajouter les tables de gamification dans privat. Mais je ne l'ai pas fait.

Le schéma gamification est un schéma dédié et isolé. Pourquoi ?

• Séparation des responsabilités : la gamification a ses propres politiques RLS (Row Level Security), ses propres fonctions RPC, ses propres index. Pas de mélange avec la logique métier.
• Évolution indépendante : je peux modifier le système de points ou ajouter des badges sans risquer de casser la création de tâches.
• Performance : les requêtes de gamification (leaderboard, classement, statistiques) ne partagent pas les index avec les tables de contenu.
• Sécurité : les politiques RLS de gamification sont plus simples — un utilisateur ne voit que ses propres stats. Pas de permissions de groupe à gérer.

Cette approche suit le principe de schema separation recommandé par PostgreSQL. Chaque schéma est un namespace qui isole les tables, fonctions et politiques de sécurité.

Quelles sont les 5 tables du schéma gamification ?

Le schéma complet repose sur 5 tables interconnectées :

1. user_stats — le tableau de bord de chaque joueur

C'est la table centrale. Elle stocke l'état actuel de chaque utilisateur :

• total_points : le score cumulé depuis le début
• current_level : le niveau actuel (1 à 12+)
• current_streak : le nombre de jours consécutifs d'activité
• max_streak : le record personnel de streak
• last_activity_date : la date de la dernière activité (pour le calcul du streak)
• streak_freeze_count : le nombre de "gels" de streak disponibles

Pourquoi stocker le niveau plutôt que le calculer à la volée ? Parce que la vérification du niveau implique une comparaison avec les seuils, et cette vérification doit être atomique avec l'ajout de points. Si le calcul était fait côté client, on risquerait des incohérences.

2. user_badges — la collection d'accomplissements

Une relation many-to-many entre utilisateurs et badges. Chaque ligne enregistre :

• L'ID du badge (parmi les 10 badges disponibles)
• La date d'obtention
• Le contexte d'obtention (quelle action a déclenché le badge)

Les badges dans TAMSIV récompensent des comportements spécifiques : premier mémo vocal, 10 tâches complétées, 7 jours de streak, etc. Ils ne sont jamais retirés — un badge obtenu l'est pour toujours.

3. points_history — le journal de chaque gain

Chaque gain de points est logué individuellement. Cette table est essentielle pour :

• L'analytics (quelles actions génèrent le plus de points ?)
• Le debugging (un utilisateur conteste son score ? On a la trace)
• Les challenges quotidiens (vérification des conditions)
• Le feed d'activité (affichage chronologique des gains)

4. daily_challenges — un défi par jour

Chaque jour, chaque utilisateur reçoit un challenge personnalisé. La table stocke :

• Le type de challenge (créer X tâches, utiliser la voix, compléter un mémo, etc.)
• L'objectif numérique (3 tâches, 5 mémos, etc.)
• La progression actuelle
• Le statut (en cours, complété, expiré)

Les challenges sont générés par une fonction RPC qui tient compte du niveau de l'utilisateur — un débutant reçoit des challenges plus simples qu'un joueur niveau 10.

5. feed_activity — le flux social

Le feed d'activité affiche les actions récentes : badges obtenus, niveaux atteints, streaks remarquables. C'est la dimension sociale de la gamification — voir que d'autres utilisateurs progressent motive.

Comment fonctionnent les 12 niveaux de progression ?

Le système de niveaux est le cœur de la gamification. Les seuils de points pour chaque niveau :

[0, 100, 250, 500, 1000, 2000, 3500, 5500, 8000, 12000, 17500, 25000]

Cette progression suit une courbe exponentielle adoucie. Les premiers niveaux sont volontairement faciles :

• Niveaux 1-3 (0 à 250 points) : atteignables en 2-3 jours d'utilisation normale. C'est la phase d'onboarding — l'utilisateur découvre le système et reçoit des gratifications rapides.
• Niveaux 4-7 (500 à 3500 points) : la phase d'engagement. 1 à 4 semaines. L'utilisateur a intégré TAMSIV dans sa routine.
• Niveaux 8-10 (5500 à 12000 points) : la phase de maîtrise. Plusieurs mois. Ces niveaux sont un signal de rétention forte.
• Niveaux 11-12 (17500 à 25000 points) : les "achievers". Moins de 5% des utilisateurs les atteignent.

Au-delà du niveau 12 ? Une formule mathématique prend le relais : seuil = 25000 + (niveau - 12) * 10000. Ça évite de hardcoder des centaines de niveaux tout en permettant une progression infinie.

Cette calibration s'inspire des travaux de Yu-kai Chou sur le framework Octalysis, qui recommande des récompenses immédiates en début de parcours et une difficulté progressive pour maintenir l'engagement à long terme.

Pourquoi les fonctions RPC doivent-elles être atomiques ?

Quand un utilisateur complète une tâche dans TAMSIV, voici ce qui doit se passer côté gamification :

1. Ajouter les points correspondants
2. Vérifier si le seuil du niveau suivant est atteint
3. Mettre à jour le streak (jour consécutif d'activité)
4. Vérifier si de nouveaux badges sont débloqués
5. Mettre à jour la progression du challenge quotidien
6. Logger l'historique des points

Si l'étape 3 échoue après que l'étape 1 a réussi, les données sont incohérentes. C'est pourquoi toutes ces opérations sont encapsulées dans des fonctions PostgreSQL transactionnelles. Soit tout réussit, soit tout échoue.

La fonction principale record_task_completed orchestre tout en une seule transaction :

• add_gamification_points — ajoute les points et logue l'historique
• check_and_update_streak — met à jour le streak avec gestion des fuseaux horaires
• check_and_unlock_badges — vérifie les conditions de chaque badge

Côté frontend, le GamificationService (singleton) appelle ces RPC et gère les notifications locales pour les badges, les niveaux et les milestones de streak.

Comment gérer les streaks sans rendre les utilisateurs addicts ?

Les streaks sont le mécanisme de gamification le plus puissant — et le plus dangereux. Un streak de 100 jours crée une pression énorme pour ne pas le "casser". C'est pourquoi j'ai posé des garde-fous :

• Plafond à 365 jours : au-delà d'un an, le streak se réinitialise. Un streak de 500 jours n'est plus de la gamification — c'est de l'addiction par design.
• Streak freezes : l'utilisateur peut "geler" son streak pour un jour (vacances, maladie). Le nombre de freezes disponibles augmente avec le niveau.
• Pas de punition : perdre un streak ne retire pas de points ni de badges. Ça remet juste le compteur à zéro.

La gestion des fuseaux horaires est un casse-tête technique. Un utilisateur à Paris et un utilisateur à Tokyo n'ont pas le même "jour". La détection de rupture de streak se fait en comparant last_activity_date avec la date actuelle dans le fuseau horaire de l'utilisateur, pas en UTC.

Comment le frontend consomme-t-il la gamification ?

Le GamificationService est un singleton qui centralise toutes les interactions frontend avec le schéma gamification :

• Appels RPC : chaque complétion de tâche ou création de mémo déclenche un appel à record_task_completed ou record_memo_created.
• Notifications locales : via NotificationService.notifyAchievement(), l'utilisateur est notifié des badges, level ups et milestones de streak.
• Cache optimisé : les stats utilisateur sont cachées localement pour éviter un appel Supabase à chaque affichage du profil.
• Feed temps réel : le feed d'activité utilise Supabase Realtime pour afficher les accomplissements des autres utilisateurs en direct.

L'intégration dans le Dictaphone est transparente : quand l'IA crée une tâche par la voix, le useTaskDetail hook appelle automatiquement le GamificationService à la complétion. L'utilisateur n'a rien à faire — les points tombent naturellement.

Quelles métriques surveiller pour ajuster la gamification ?

La table points_history permet d'analyser finement :

• Distribution des niveaux : si 80% des utilisateurs sont bloqués au niveau 3, les seuils sont trop élevés.
• Taux de complétion des challenges : en dessous de 30%, les challenges sont trop difficiles. Au-dessus de 90%, trop faciles.
• Streak moyen : un indicateur direct de rétention quotidienne.
• Badges les plus rares : les badges que personne n'obtient doivent être réévalués.

Tout est visible dans le dashboard admin avec des graphiques Recharts. Ça permet d'ajuster les seuils en production sans redéplier.

Comment éviter les pièges classiques de la gamification ?

La gamification mal conçue fait plus de mal que de bien. Voici les pièges que j'ai évités :

• Pas de leaderboard public : les classements publics démoralisent 95% des utilisateurs pour motiver les 5% du top. TAMSIV montre la progression personnelle, pas la compétition.
• Pas de gamification punitive : perdre des points pour inactivité est un dark pattern. Dans TAMSIV, l'inactivité n'a aucune conséquence négative.
• Points significatifs : chaque point correspond à une action réelle (tâche créée, mémo enregistré, streak maintenu). Pas de points gratuits pour "se connecter".
• Feedback immédiat : les notifications de badge et de level up arrivent instantanément, pas en fin de journée.

Comment appliquer ce schéma à ton propre projet ?

Si tu veux implémenter un système de gamification similaire, voici les principes :

1. Isole le schéma : ne mélange pas gamification et logique métier. Un schéma dédié facilite les migrations et le debugging.
2. Tout atomique : chaque action utilisateur qui impacte la gamification doit être une transaction unique.
3. Calibre la progression : teste les seuils de niveaux avec des données réelles. Les premiers niveaux doivent être atteignables en jours, pas en semaines.
4. Prévois la maintenance : la table points_history grossit vite. Prévois une politique de rétention ou d'archivage.
5. Mesure tout : sans analytics, tu ne sauras pas si ta gamification motive ou frustre tes utilisateurs.

FAQ

Combien de temps faut-il pour implémenter un système de gamification complet ?

Pour TAMSIV, le schéma et les RPC ont pris environ 4 jours. L'intégration frontend (GamificationService, notifications, UI) a ajouté 3 jours supplémentaires. Le plus long est la calibration des seuils de niveaux, qui nécessite des données réelles d'utilisation.

PostgreSQL est-il adapté pour la gamification ou faut-il Redis ?

PostgreSQL suffit largement pour une app de la taille de TAMSIV. Les fonctions transactionnelles garantissent la cohérence, et les index sur user_stats rendent les requêtes rapides. Redis serait utile uniquement pour un leaderboard temps réel avec des millions d'utilisateurs — ce qui n'est pas le cas ici.

Comment éviter que la gamification devienne un dark pattern ?

Trois règles : pas de punition pour l'inactivité, pas de pression sociale via leaderboard public, et un plafond sur les streaks. La gamification doit récompenser l'action, pas punir l'absence. Le streak freeze est un mécanisme de sécurité essentiel.

Les points doivent-ils être les mêmes pour toutes les actions ?

Non. Dans TAMSIV, compléter une tâche rapporte plus que créer un mémo, et utiliser la voix donne un bonus. Le barème reflète la valeur de chaque action pour l'utilisateur. Un barème plat (tout vaut 10 points) ne crée aucune hiérarchie de comportement.

Faut-il afficher les points en temps réel ou en différé ?

En temps réel, toujours. Le feedback immédiat est la base de la gamification efficace. Dans TAMSIV, les points s'affichent instantanément après chaque action, avec une animation et un son optionnel. Le délai tue la dopamine.