Gamification System
2026-02-15
By @boooshir, @AI
Overview
One of my web app "Sifokus" uses gamification to motivate users through XP, levels, streaks, achievements, and leaderboards.
Tables
user_stats
Stores per-user gamification data. One row per user.
| Column | Type | Description |
|---|---|---|
| userId | text (FK → users) | Unique, one stats row per user |
| xp | integer | All-time XP earned |
| level | integer | Current level (derived from XP) |
| streakCount | integer | Current consecutive active days |
| longestStreak | integer | Best streak ever achieved |
| lastActiveDate | timestamp | Last day user earned XP |
| totalTasksCompleted | integer | All-time tasks completed |
| totalPomodoros | integer | All-time pomodoro sessions |
| totalProjectsCompleted | integer | All-time projects completed |
| updatedAt | timestamp_ms | Auto-updated on change |
achievements
Badge definitions with tiers. Admin-seeded data.
| Column | Type | Description |
|---|---|---|
| name | text | Display name |
| description | text | How to earn it |
| icon | text | Lucide icon name |
| category | enum | pomodoro, task, streak, project, xp |
| tier | enum | bronze, silver, gold, platinum |
| threshold | integer | Target number to unlock |
| xpReward | integer | XP granted on unlock |
Example Achievements
| Name | Category | Tier | Threshold | XP Reward |
|---|---|---|---|---|
| First Step | task | bronze | 1 | 10 |
| Getting Things Done | task | silver | 50 | 50 |
| Task Master | task | gold | 500 | 200 |
| Task Legend | task | platinum | 5000 | 1000 |
| Focus Starter | pomodoro | bronze | 1 | 10 |
| Deep Work | pomodoro | silver | 50 | 50 |
| Pomodoro Master | pomodoro | gold | 500 | 200 |
| Focus Legend | pomodoro | platinum | 5000 | 1000 |
| 7 Day Streak | streak | bronze | 7 | 50 |
| 30 Day Streak | streak | silver | 30 | 200 |
| 100 Day Streak | streak | gold | 100 | 500 |
| 365 Day Streak | streak | platinum | 365 | 2000 |
| Project Kickoff | project | bronze | 1 | 25 |
| Project Slayer | project | silver | 5 | 100 |
| Project Legend | project | gold | 20 | 500 |
| Rising Star | xp | bronze | 100 | 0 |
| Power User | xp | silver | 1000 | 0 |
| Elite | xp | gold | 10000 | 0 |
| Legendary | xp | platinum | 100000 | 0 |
user_achievements
Tracks which badges a user has earned. Composite PK: .
| Column | Type | Description |
|---|---|---|
| userId | text (FK → users) | Part of composite PK |
| achievementId | integer (FK → achievements) | Part of composite PK |
| count | integer | Times earned (supports repeatable) |
| firstEarnedAt | timestamp | When first unlocked |
| lastEarnedAt | timestamp | When last earned (for repeatable) |
period_stats
Weekly and monthly leaderboard data. Composite PK: .
| Column | Type | Description |
|---|---|---|
| userId | text (FK → users) | Part of composite PK |
| periodType | enum | weekly, monthly |
| periodStart | timestamp | Monday (weekly) or 1st of month (monthly) |
| xpEarned | integer | XP earned in this period |
| tasksCompleted | integer | Tasks completed in this period |
| pomodorosCompleted | integer | Pomodoros completed in this period |
XP System
XP Sources
| Action | XP |
|---|---|
| Complete a task | +10 |
| Complete a pomodoro (work session) | +25 |
| Complete a project | +100 |
| Daily streak bonus | +15 |
Level Calculation
| Level | XP Required |
|---|---|
| 1 | 0 |
| 2 | 500 |
| 3 | 1000 |
| 5 | 2000 |
| 10 | 4500 |
| 20 | 9500 |
Streaks
Rules
- User must earn at least 1 XP in a day to maintain streak
- Checked on first action of each day by comparing with today
- If is yesterday → streak continues
- If is today → no change
- If is older than yesterday → streak resets to 1
- Update if current streak exceeds it
Daily Streak Bonus
When streak continues: +15 XP bonus (on top of action XP)
Leaderboard
Visibility
- All users appear on leaderboard (no opt-in required)
- Free and paid users included
Categories
Top 10 users displayed for each period.
Weekly
1SELECT u.id, u.name, u.image, p.xpEarned, p.tasksCompleted, p.pomodorosCompleted
2FROM period_stats p
3JOIN users u ON u.id = p.userId
4WHERE p.periodType = 'weekly'
5 AND p.periodStart = <this_week_monday>
6ORDER BY p.xpEarned DESC
7LIMIT 10Monthly
1SELECT u.id, u.name, u.image, p.xpEarned, p.tasksCompleted, p.pomodorosCompleted
2FROM period_stats p
3JOIN users u ON u.id = p.userId
4WHERE p.periodType = 'monthly'
5 AND p.periodStart = <this_month_1st>
6ORDER BY p.xpEarned DESC
7LIMIT 10Period Start Calculation
- Weekly: (Monday of current week)
- Monthly: (1st of current month)
Query Patterns
Increment XP + Check Level
1// After action, atomically increment XP
2db.update(userStats)
3 .set({
4 xp: sql`${userStats.xp} + ${xpAmount}`,
5 totalTasksCompleted: sql`${userStats.totalTasksCompleted} + 1`,
6 })
7 .where(eq(userStats.userId, userId))
8
9// Then check level
10const newLevel = Math.floor((currentXp + xpAmount) / 500) + 1
11if (newLevel > currentLevel) {
12 db.update(userStats).set({ level: newLevel }).where(...)
13}Update Streak
1const today = startOfDay(new Date())
2const lastActive = userStats.lastActiveDate
3
4if (isSameDay(lastActive, today)) {
5 // Already active today, no change
6} else if (isYesterday(lastActive)) {
7 // Streak continues
8 db.update(userStats).set({
9 streakCount: sql`${userStats.streakCount} + 1`,
10 longestStreak: sql`MAX(${userStats.longestStreak}, ${userStats.streakCount} + 1)`,
11 lastActiveDate: today,
12 xp: sql`${userStats.xp} + 15`, // streak bonus
13 })
14} else {
15 // Streak broken
16 db.update(userStats).set({
17 streakCount: 1,
18 lastActiveDate: today,
19 })
20}Check Achievement Unlock
1// 1. Single query: find all newly unlocked achievements not yet earned
2const newAchievements = await db
3 .select({ id: achievements.id, xpReward: achievements.xpReward })
4 .from(achievements)
5 .leftJoin(
6 userAchievements,
7 and(
8 eq(userAchievements.userId, userId),
9 eq(userAchievements.achievementId, achievements.id),
10 ),
11 )
12 .where(
13 and(
14 eq(achievements.category, 'task'),
15 lte(achievements.threshold, userStats.totalTasksCompleted),
16 isNull(userAchievements.userId), // not yet earned
17 ),
18 )
19
20// 2. Bulk insert unlocked achievements
21if (newAchievements.length > 0) {
22 await db.insert(userAchievements).values(
23 newAchievements.map((a) => ({
24 userId,
25 achievementId: a.id,
26 })),
27 )
28
29 // 3. Grant total XP reward in one update
30 const totalReward = newAchievements.reduce((sum, a) => sum + a.xpReward, 0)
31 await db
32 .update(userStats)
33 .set({ xp: sql`${userStats.xp} + ${totalReward}` })
34 .where(eq(userStats.userId, userId))
35}Update Period Stats
1const weekStart = getMonday(new Date())
2const monthStart = getFirstOfMonth(new Date())
3
4// Upsert weekly
5await db
6 .insert(periodStats)
7 .values({
8 userId,
9 periodType: 'weekly',
10 periodStart: weekStart,
11 xpEarned: xpAmount,
12 tasksCompleted: 1,
13 })
14 .onConflictDoUpdate({
15 target: [
16 periodStats.userId,
17 periodStats.periodType,
18 periodStats.periodStart,
19 ],
20 set: {
21 xpEarned: sql`${periodStats.xpEarned} + ${xpAmount}`,
22 tasksCompleted: sql`${periodStats.tasksCompleted} + 1`,
23 },
24 })
25
26// Same for monthly with periodType: 'monthly'User Stats Initialization
When a new user signs up, create their stats row:
1await db.insert(userStats).values({
2 userId: newUser.id,
3 xp: 0,
4 level: 1,
5 streakCount: 0,
6 longestStreak: 0,
7 totalTasksCompleted: 0,
8 totalPomodoros: 0,
9 totalProjectsCompleted: 0,
10})