Schema gamification PostgreSQL : 5 tables, 12 niveaux

Points cles a retenir : Un systeme de gamification solide repose sur un schema PostgreSQL dedie avec 5 tables, des RPC atomiques transactionnelles, et une progression calibree sur 12 niveaux. Le secret : isoler la gamification dans son propre schema, plafonner les streaks a 365 jours pour eviter l'addiction, et tout rendre atomique pour garantir la coherence des donnees.

La gamification semble simple en surface. Des points, des badges, un leaderboard — ca a l'air facile, non ? En realite, des qu'on commence a modeliser serieusement, la complexite explose. Pour TAMSIV, je voulais un systeme complet : niveaux, badges, streaks, challenges quotidiens, historique de points, et un feed d'activite. Le tout dans PostgreSQL, avec des garanties transactionnelles.

Voici comment j'ai concu le schema de gamification de TAMSIV, table par table, decision par decision.

Pourquoi un schema PostgreSQL dedie pour la gamification ?

TAMSIV utilise deja deux schemas dans Supabase : privat pour les donnees utilisateur (taches, memos, evenements) et collaborative pour les fonctionnalites de groupe. J'aurais pu ajouter les tables de gamification dans privat. Mais je ne l'ai pas fait.

Le schema gamification est un schema dedie et isole. Pourquoi ?

• Separation des responsabilites : la gamification a ses propres politiques RLS (Row Level Security), ses propres fonctions RPC, ses propres index. Pas de melange avec la logique metier.
• Evolution independante : je peux modifier le systeme de points ou ajouter des badges sans risquer de casser la creation de taches.
• Performance : les requetes de gamification (leaderboard, classement, statistiques) ne partagent pas les index avec les tables de contenu.
• Securite : les politiques RLS de gamification sont plus simples — un utilisateur ne voit que ses propres stats. Pas de permissions de groupe a gerer.

Cette approche suit le principe de schema separation recommande par PostgreSQL. Chaque schema est un namespace qui isole les tables, fonctions et politiques de securite.

Quelles sont les 5 tables du schema gamification ?

Le schema complet repose sur 5 tables interconnectees :

1. user_stats — le tableau de bord de chaque joueur

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

• total_points : le score cumule depuis le debut
• current_level : le niveau actuel (1 a 12+)
• current_streak : le nombre de jours consecutifs d'activite
• max_streak : le record personnel de streak
• last_activity_date : la date de la derniere activite (pour le calcul du streak)
• streak_freeze_count : le nombre de "gels" de streak disponibles

Pourquoi stocker le niveau plutot que le calculer a la volee ? Parce que la verification du niveau implique une comparaison avec les seuils, et cette verification doit etre atomique avec l'ajout de points. Si le calcul etait fait cote client, on risquerait des incoherences.

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 declenche le badge)

Les badges dans TAMSIV recompensent des comportements specifiques : premier memo vocal, 10 taches completees, 7 jours de streak, etc. Ils ne sont jamais retires — un badge obtenu l'est pour toujours.

3. points_history — le journal de chaque gain

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

• L'analytics (quelles actions generent le plus de points ?)
• Le debugging (un utilisateur conteste son score ? On a la trace)
• Les challenges quotidiens (verification des conditions)
• Le feed d'activite (affichage chronologique des gains)

4. daily_challenges — un defi par jour

Chaque jour, chaque utilisateur recoit un challenge personnalise. La table stocke :

• Le type de challenge (creer X taches, utiliser la voix, completer un memo, etc.)
• L'objectif numerique (3 taches, 5 memos, etc.)
• La progression actuelle
• Le statut (en cours, complete, expire)

Les challenges sont generes par une fonction RPC qui tient compte du niveau de l'utilisateur — un debutant recoit des challenges plus simples qu'un joueur niveau 10.

5. feed_activity — le flux social

Le feed d'activite affiche les actions recentes : 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 systeme de niveaux est le coeur 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 a 250 points) : atteignables en 2-3 jours d'utilisation normale. C'est la phase d'onboarding — l'utilisateur decouvre le systeme et recoit des gratifications rapides.
• Niveaux 4-7 (500 a 3500 points) : la phase d'engagement. 1 a 4 semaines. L'utilisateur a integre TAMSIV dans sa routine.
• Niveaux 8-10 (5500 a 12000 points) : la phase de maitrise. Plusieurs mois. Ces niveaux sont un signal de retention forte.
• Niveaux 11-12 (17500 a 25000 points) : les "achievers". Moins de 5% des utilisateurs les atteignent.

Au-dela du niveau 12 ? Une formule mathematique prend le relais : seuil = 25000 + (niveau - 12) * 10000. Ca evite 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 recompenses immediates en debut de parcours et une difficulte progressive pour maintenir l'engagement a long terme.

Pourquoi les fonctions RPC doivent-elles etre atomiques ?

Quand un utilisateur complete une tache dans TAMSIV, voici ce qui doit se passer cote gamification :

1. Ajouter les points correspondants
2. Verifier si le seuil du niveau suivant est atteint
3. Mettre a jour le streak (jour consecutif d'activite)
4. Verifier si de nouveaux badges sont debloques
5. Mettre a jour la progression du challenge quotidien
6. Logger l'historique des points

Si l'etape 3 echoue apres que l'etape 1 a reussi, les donnees sont incoherentes. C'est pourquoi toutes ces operations sont encapsulees dans des fonctions PostgreSQL transactionnelles. Soit tout reussit, soit tout echoue.

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 a jour le streak avec gestion des fuseaux horaires
• check_and_unlock_badges — verifie les conditions de chaque badge

Cote frontend, le GamificationService (singleton) appelle ces RPC et gere les notifications locales pour les badges, les niveaux et les milestones de streak.

Comment gerer les streaks sans rendre les utilisateurs addicts ?

Les streaks sont le mecanisme de gamification le plus puissant — et le plus dangereux. Un streak de 100 jours cree une pression enorme pour ne pas le "casser". C'est pourquoi j'ai pose des garde-fous :

• Plafond a 365 jours : au-dela d'un an, le streak se reinitialise. 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. Ca remet juste le compteur a zero.

La gestion des fuseaux horaires est un casse-tete technique. Un utilisateur a Paris et un utilisateur a Tokyo n'ont pas le meme "jour". La detection 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 schema gamification :

• Appels RPC : chaque completion de tache ou creation de memo declenche un appel a record_task_completed ou record_memo_created.
• Notifications locales : via NotificationService.notifyAchievement(), l'utilisateur est notifie des badges, level ups et milestones de streak.
• Cache optimise : les stats utilisateur sont cachees localement pour eviter un appel Supabase a chaque affichage du profil.
• Feed temps reel : le feed d'activite utilise Supabase Realtime pour afficher les accomplissements des autres utilisateurs en direct.

L'integration dans le Dictaphone est transparente : quand l'IA cree une tache par la voix, le useTaskDetail hook appelle automatiquement le GamificationService a la completion. L'utilisateur n'a rien a faire — les points tombent naturellement.

Quelles metriques surveiller pour ajuster la gamification ?

La table points_history permet d'analyser finement :

• Distribution des niveaux : si 80% des utilisateurs sont bloques au niveau 3, les seuils sont trop eleves.
• Taux de completion des challenges : en dessous de 30%, les challenges sont trop difficiles. Au-dessus de 90%, trop faciles.
• Streak moyen : un indicateur direct de retention quotidienne.
• Badges les plus rares : les badges que personne n'obtient doivent etre reevalues.

Tout est visible dans le dashboard admin avec des graphiques Recharts. Ca permet d'ajuster les seuils en production sans redeplier.

Comment eviter les pieges classiques de la gamification ?

La gamification mal concue fait plus de mal que de bien. Voici les pieges que j'ai evites :

• Pas de leaderboard public : les classements publics demoralisent 95% des utilisateurs pour motiver les 5% du top. TAMSIV montre la progression personnelle, pas la competition.
• Pas de gamification punitive : perdre des points pour inactivite est un dark pattern. Dans TAMSIV, l'inactivite n'a aucune consequence negative.
• Points significatifs : chaque point correspond a une action reelle (tache creee, memo enregistre, streak maintenu). Pas de points gratuits pour "se connecter".
• Feedback immediat : les notifications de badge et de level up arrivent instantanement, pas en fin de journee.

Comment appliquer ce schema a ton propre projet ?

Si tu veux implementer un systeme de gamification similaire, voici les principes :

1. Isole le schema : ne melange pas gamification et logique metier. Un schema dedie facilite les migrations et le debugging.
2. Tout atomique : chaque action utilisateur qui impacte la gamification doit etre une transaction unique.
3. Calibre la progression : teste les seuils de niveaux avec des donnees reelles. Les premiers niveaux doivent etre atteignables en jours, pas en semaines.
4. Prevois la maintenance : la table points_history grossit vite. Prevois une politique de retention 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 implementer un systeme de gamification complet ?

Pour TAMSIV, le schema et les RPC ont pris environ 4 jours. L'integration frontend (GamificationService, notifications, UI) a ajoute 3 jours supplementaires. Le plus long est la calibration des seuils de niveaux, qui necessite des donnees reelles d'utilisation.

PostgreSQL est-il adapte pour la gamification ou faut-il Redis ?

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

Comment eviter que la gamification devienne un dark pattern ?

Trois regles : pas de punition pour l'inactivite, pas de pression sociale via leaderboard public, et un plafond sur les streaks. La gamification doit recompenser l'action, pas punir l'absence. Le streak freeze est un mecanisme de securite essentiel.

Les points doivent-ils etre les memes pour toutes les actions ?

Non. Dans TAMSIV, completer une tache rapporte plus que creer un memo, et utiliser la voix donne un bonus. Le bareme reflecte la valeur de chaque action pour l'utilisateur. Un bareme plat (tout vaut 10 points) ne cree aucune hierarchie de comportement.

Faut-il afficher les points en temps reel ou en differe ?

En temps reel, toujours. Le feedback immediat est la base de la gamification efficace. Dans TAMSIV, les points s'affichent instantanement apres chaque action, avec une animation et un son optionnel. Le delai tue la dopamine.