PostgreSQL Gamification Schema: 5 Tables, 12 Levels

Key takeaways: A robust gamification system relies on a dedicated PostgreSQL schema with 5 tables, transactional atomic RPCs, and a calibrated progression across 12 levels. The secret: isolate gamification in its own schema, cap streaks at 365 days to prevent addiction, and make everything atomic to ensure data consistency.

Gamification seems simple on the surface. Points, badges, a leaderboard — looks easy, right? In reality, as soon as you start modeling seriously, complexity explodes. For TAMSIV, I wanted a complete system: levels, badges, streaks, daily challenges, points history, and an activity feed. All within PostgreSQL, with transactional guarantees.

Here's how I designed TAMSIV's gamification schema, table by table, decision by decision.

Why a dedicated PostgreSQL schema for gamification?

TAMSIV already uses two schemas in Supabase: privat for user data (tasks, memos, events) and collaborative for group features. I could have added the gamification tables to privat. But I didn't.

The gamification schema is a dedicated and isolated schema. Why?

• Separation of concerns: Gamification has its own RLS (Row Level Security) policies, its own RPC functions, its own indexes. No mixing with business logic.
• Independent evolution: I can modify the points system or add badges without risking breaking task creation.
• Performance: Gamification queries (leaderboard, ranking, statistics) do not share indexes with content tables.
• Security: Gamification RLS policies are simpler — a user only sees their own stats. No group permissions to manage.

This approach follows the principle of schema separation recommended by PostgreSQL. Each schema is a namespace that isolates tables, functions, and security policies.

What are the 5 tables in the gamification schema?

The complete schema relies on 5 interconnected tables:

1. user_stats — each player's dashboard

This is the central table. It stores each user's current state:

• total_points: the cumulative score since the beginning
• current_level: the current level (1 to 12+)
• current_streak: the number of consecutive days of activity
• max_streak: the personal streak record
• last_activity_date: the date of the last activity (for streak calculation)
• streak_freeze_count: the number of available "streak freezes"

Why store the level rather than calculate it on the fly? Because level verification involves comparing with thresholds, and this verification must be atomic with point addition. If the calculation were done client-side, we would risk inconsistencies.

2. user_badges — the achievement collection

A many-to-many relationship between users and badges. Each row records:

• The badge ID (among the 10 available badges)
• The acquisition date
• The acquisition context (which action triggered the badge)

Badges in TAMSIV reward specific behaviors: first voice memo, 10 tasks completed, 7-day streak, etc. They are never removed — a badge earned is earned forever.

3. points_history — the log of every gain

Each point gain is logged individually. This table is essential for:

• Analytics (which actions generate the most points?)
• Debugging (a user disputes their score? We have the trace)
• Daily challenges (condition verification)
• The activity feed (chronological display of gains)

4. daily_challenges — one challenge per day

Each day, each user receives a personalized challenge. The table stores:

• The challenge type (create X tasks, use voice, complete a memo, etc.)
• The numerical objective (3 tasks, 5 memos, etc.)
• The current progress
• The status (in progress, completed, expired)

Challenges are generated by an RPC function that takes into account the user's level — a beginner receives simpler challenges than a level 10 player.

5. feed_activity — the social feed

The activity feed displays recent actions: badges earned, levels reached, remarkable streaks. This is the social dimension of gamification — seeing other users progress is motivating.

How do the 12 progression levels work?

The level system is the heart of gamification. The point thresholds for each level:

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

This progression follows a softened exponential curve. The first levels are intentionally easy:

• Levels 1-3 (0 to 250 points): achievable in 2-3 days of normal use. This is the onboarding phase — the user discovers the system and receives quick gratifications.
• Levels 4-7 (500 to 3500 points): the engagement phase. 1 to 4 weeks. The user has integrated TAMSIV into their routine.
• Levels 8-10 (5500 to 12000 points): the mastery phase. Several months. These levels are a strong retention signal.
• Levels 11-12 (17500 to 25000 points): the "achievers". Less than 5% of users reach them.

Beyond level 12? A mathematical formula takes over: threshold = 25000 + (level - 12) * 10000. This avoids hardcoding hundreds of levels while allowing infinite progression.

This calibration is inspired by Yu-kai Chou's work on the Octalysis framework, which recommends immediate rewards at the beginning of the journey and progressive difficulty to maintain long-term engagement.

Why do RPC functions need to be atomic?

When a user completes a task in TAMSIV, here's what needs to happen on the gamification side:

1. Add the corresponding points
2. Check if the next level's threshold is reached
3. Update the streak (consecutive day of activity)
4. Check if new badges are unlocked
5. Update the daily challenge's progress
6. Log the points history

If step 3 fails after step 1 succeeds, the data is inconsistent. That's why all these operations are encapsulated in transactional PostgreSQL functions. Either everything succeeds, or everything fails.

The main function record_task_completed orchestrates everything in a single transaction:

• add_gamification_points — adds points and logs history
• check_and_update_streak — updates the streak with timezone management
• check_and_unlock_badges — checks the conditions for each badge

On the frontend, the GamificationService (singleton) calls these RPCs and manages local notifications for badges, levels, and streak milestones.

How to manage streaks without making users addicted?

Streaks are the most powerful — and most dangerous — gamification mechanism. A 100-day streak creates enormous pressure not to "break" it. That's why I put safeguards in place:

• Cap at 365 days: beyond a year, the streak resets. A 500-day streak is no longer gamification — it's addiction by design.
• Streak freezes: the user can "freeze" their streak for a day (vacation, illness). The number of available freezes increases with level.
• No punishment: losing a streak does not remove points or badges. It just resets the counter to zero.

Managing time zones is a technical headache. A user in Paris and a user in Tokyo don't have the same "day". Streak break detection is done by comparing last_activity_date with the current date in the user's time zone, not in UTC.

How does the frontend consume gamification?

The GamificationService is a singleton that centralizes all frontend interactions with the gamification schema:

• RPC calls: each task completion or memo creation triggers a call to record_task_completed or record_memo_created.
• Local notifications: via NotificationService.notifyAchievement(), the user is notified of badges, level ups, and streak milestones.
• Optimized cache: user stats are cached locally to avoid a Supabase call every time the profile is displayed.
• Real-time feed: the activity feed uses Supabase Realtime to display other users' achievements live.

Integration into the Dictaphone is seamless: when the AI creates a task by voice, the useTaskDetail hook automatically calls the GamificationService upon completion. The user doesn't have to do anything — points are earned naturally.

What metrics to monitor to adjust gamification?

The points_history table allows for detailed analysis:

• Level distribution: if 80% of users are stuck at level 3, the thresholds are too high.
• Challenge completion rate: below 30%, challenges are too difficult. Above 90%, too easy.
• Average streak: a direct indicator of daily retention.
• Rarest badges: badges that no one earns need to be re-evaluated.

Everything is visible in the admin dashboard with Recharts graphs. This allows thresholds to be adjusted in production without redeploying.

How to avoid common gamification pitfalls?

Poorly designed gamification does more harm than good. Here are the pitfalls I avoided:

• No public leaderboard: public rankings demoralize 95% of users to motivate the top 5%. TAMSIV shows personal progress, not competition.
• No punitive gamification: losing points for inactivity is a dark pattern. In TAMSIV, inactivity has no negative consequences.
• Meaningful points: each point corresponds to a real action (task created, memo recorded, streak maintained). No free points for "logging in".
• Immediate feedback: badge and level up notifications arrive instantly, not at the end of the day.

How to apply this schema to your own project?

If you want to implement a similar gamification system, here are the principles:

1. Isolate the schema: don't mix gamification and business logic. A dedicated schema facilitates migrations and debugging.
2. Everything atomic: each user action that impacts gamification must be a single transaction.
3. Calibrate progression: test level thresholds with real data. The first levels should be achievable in days, not weeks.
4. Plan for maintenance: the points_history table grows quickly. Plan a retention or archiving policy.
5. Measure everything: without analytics, you won't know if your gamification motivates or frustrates your users.

FAQ

How long does it take to implement a complete gamification system?

For TAMSIV, the schema and RPCs took about 4 days. Frontend integration (GamificationService, notifications, UI) added another 3 days. The longest part is calibrating the level thresholds, which requires real usage data.

Is PostgreSQL suitable for gamification or should I use Redis?

PostgreSQL is more than sufficient for an app the size of TAMSIV. Transactional functions ensure consistency, and indexes on user_stats make queries fast. Redis would only be useful for a real-time leaderboard with millions of users — which is not the case here.

How to prevent gamification from becoming a dark pattern?

Three rules: no punishment for inactivity, no social pressure via public leaderboards, and a cap on streaks. Gamification should reward action, not punish absence. Streak freeze is an essential safety mechanism.

Should points be the same for all actions?

No. In TAMSIV, completing a task earns more than creating a memo, and using voice gives a bonus. The scoring reflects the value of each action for the user. A flat scoring system (everything is worth 10 points) creates no hierarchy of behavior.

Should points be displayed in real-time or delayed?

In real-time, always. Immediate feedback is the basis of effective gamification. In TAMSIV, points are displayed instantly after each action, with an animation and optional sound. Delay kills dopamine.