Gamification System

Overview

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: userId + achievementId.

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: userId + periodType + periodStart.

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 = floor(totalXP / 500) + 1

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 lastActiveDate with today
• If lastActiveDate is yesterday → streak continues
• If lastActiveDate is today → no change
• If lastActiveDate is older than yesterday → streak resets to 1
• Update longestStreak 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

SELECT u.id, u.name, u.image, p.xpEarned, p.tasksCompleted, p.pomodorosCompleted
FROM period_stats p
JOIN users u ON u.id = p.userId
WHERE p.periodType = 'weekly'
  AND p.periodStart = <this_week_monday>
ORDER BY p.xpEarned DESC
LIMIT 10

Monthly

SELECT u.id, u.name, u.image, p.xpEarned, p.tasksCompleted, p.pomodorosCompleted
FROM period_stats p
JOIN users u ON u.id = p.userId
WHERE p.periodType = 'monthly'
  AND p.periodStart = <this_month_1st>
ORDER BY p.xpEarned DESC
LIMIT 10

Period Start Calculation

• Weekly: date('now', 'weekday 0', '-6 days') (Monday of current week)
• Monthly: date('now', 'start of month') (1st of current month)

Query Patterns

Increment XP + Check Level

// After action, atomically increment XP
db.update(userStats)
  .set({
    xp: sql`${userStats.xp} + ${xpAmount}`,
    totalTasksCompleted: sql`${userStats.totalTasksCompleted} + 1`,
  })
  .where(eq(userStats.userId, userId))

// Then check level
const newLevel = Math.floor((currentXp + xpAmount) / 500) + 1
if (newLevel > currentLevel) {
  db.update(userStats).set({ level: newLevel }).where(...)
}

Update Streak

const today = startOfDay(new Date())
const lastActive = userStats.lastActiveDate

if (isSameDay(lastActive, today)) {
  // Already active today, no change
} else if (isYesterday(lastActive)) {
  // Streak continues
  db.update(userStats).set({
    streakCount: sql`${userStats.streakCount} + 1`,
    longestStreak: sql`MAX(${userStats.longestStreak}, ${userStats.streakCount} + 1)`,
    lastActiveDate: today,
    xp: sql`${userStats.xp} + 15`, // streak bonus
  })
} else {
  // Streak broken
  db.update(userStats).set({
    streakCount: 1,
    lastActiveDate: today,
  })
}

Check Achievement Unlock

// 1. Single query: find all newly unlocked achievements not yet earned
const newAchievements = await db
  .select({ id: achievements.id, xpReward: achievements.xpReward })
  .from(achievements)
  .leftJoin(
    userAchievements,
    and(
      eq(userAchievements.userId, userId),
      eq(userAchievements.achievementId, achievements.id),
    ),
  )
  .where(
    and(
      eq(achievements.category, 'task'),
      lte(achievements.threshold, userStats.totalTasksCompleted),
      isNull(userAchievements.userId), // not yet earned
    ),
  )

// 2. Bulk insert unlocked achievements
if (newAchievements.length > 0) {
  await db.insert(userAchievements).values(
    newAchievements.map((a) => ({
      userId,
      achievementId: a.id,
    })),
  )

  // 3. Grant total XP reward in one update
  const totalReward = newAchievements.reduce((sum, a) => sum + a.xpReward, 0)
  await db
    .update(userStats)
    .set({ xp: sql`${userStats.xp} + ${totalReward}` })
    .where(eq(userStats.userId, userId))
}

Update Period Stats

const weekStart = getMonday(new Date())
const monthStart = getFirstOfMonth(new Date())

// Upsert weekly
await db
  .insert(periodStats)
  .values({
    userId,
    periodType: 'weekly',
    periodStart: weekStart,
    xpEarned: xpAmount,
    tasksCompleted: 1,
  })
  .onConflictDoUpdate({
    target: [
      periodStats.userId,
      periodStats.periodType,
      periodStats.periodStart,
    ],
    set: {
      xpEarned: sql`${periodStats.xpEarned} + ${xpAmount}`,
      tasksCompleted: sql`${periodStats.tasksCompleted} + 1`,
    },
  })

// Same for monthly with periodType: 'monthly'

---

User Stats Initialization

When a new user signs up, create their stats row:

await db.insert(userStats).values({
  userId: newUser.id,
  xp: 0,
  level: 1,
  streakCount: 0,
  longestStreak: 0,
  totalTasksCompleted: 0,
  totalPomodoros: 0,
  totalProjectsCompleted: 0,
})