Gamify Your Fitness App: Streaks, Badges, and Leaderboards with Node.js & PostgreSQL

In the competitive world of fitness apps, user engagement is the name of the game. How do you keep users motivated and coming back day after day? Gamification is a powerful answer. By integrating game-like mechanics such as daily streaks, achievement badges, and competitive leaderboards, you can transform a routine fitness tracker into an exciting and addictive experience.

This tutorial will guide you through the backend implementation of these key gamification features. We will design a robust PostgreSQL database schema and build the necessary Node.js API endpoints to bring your fitness app to life.

Prerequisites:

• Basic understanding of Node.js and Express.
• Familiarity with PostgreSQL and SQL fundamentals.
• Node.js and PostgreSQL installed on your system.

Implementing gamification features presents some interesting backend challenges. We need to efficiently track user activity over time, calculate consecutive day streaks, award badges for specific achievements, and rank users on a leaderboard. Doing this in a scalable way that doesn't bog down your application as your user base grows is crucial.

This tutorial will address these challenges by leveraging the power of PostgreSQL's advanced querying capabilities, specifically window functions, to perform these calculations directly in the database, minimizing the processing load on our Node.js application.

Before we start, make sure you have Node.js, npm, and PostgreSQL installed.

Create a new project directory and initialize a Node.js project:

mkdir fitness-gamification-backend
cd fitness-gamification-backend
npm init -y
npm install express pg

This will create a package.json file and install the necessary dependencies: express for our web server and pg as the PostgreSQL client for Node.js.

First, let's design the database schema to support our gamification features. We'll need tables for users, their daily workout activities, the badges they've earned, and the definitions of the available badges.

Here is the SQL for creating our tables:

-- users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- workout_activities table to track daily user activity
CREATE TABLE workout_activities (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    activity_date DATE NOT NULL,
    calories_burned INTEGER,
    UNIQUE(user_id, activity_date)
);

-- badges table to define available badges
CREATE TABLE badges (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    criteria_value INTEGER
);

-- user_badges table to track earned badges
CREATE TABLE user_badges (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    badge_id INTEGER REFERENCES badges(id),
    earned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, badge_id)
);

A workout streak is the number of consecutive days a user has completed a workout. We'll create an API endpoint that calculates a user's current streak.

What we're doing

We'll write a PostgreSQL query that uses window functions to identify consecutive dates in the workout_activities table. This is more efficient than fetching all of a user's activities and processing them in the application.

Implementation

Create a file db.js to handle database connections and queries.

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  user: 'your_db_user',
  host: 'localhost',
  database: 'your_db_name',
  password: 'your_db_password',
  port: 5432,
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};

Now, create streaks.js to define the API endpoint.

// streaks.js
const db = require('./db');

async function getUserStreak(userId) {
  const query = `
    WITH date_diffs AS (
      SELECT
        activity_date,
        activity_date - ROW_NUMBER() OVER (ORDER BY activity_date) * INTERVAL '1 day' as date_group
      FROM workout_activities
      WHERE user_id = $1
      ORDER BY activity_date DESC
    )
    SELECT
      COUNT(*) AS streak
    FROM date_diffs
    WHERE date_group = (SELECT date_group FROM date_diffs LIMIT 1);
  `;
  const { rows } = await db.query(query, [userId]);
  return rows[0] ? parseInt(rows[0].streak, 10) : 0;
}

module.exports = { getUserStreak };

Finally, create your main server file index.js.

// index.js
const express = require('express');
const { getUserStreak } = require('./streaks');

const app = express();
const port = 3000;

app.get('/users/:userId/streak', async (req, res) => {
  try {
    const streak = await getUserStreak(req.params.userId);
    res.json({ streak });
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

How it works

The magic happens in the PostgreSQL query. By subtracting the row number (as a number of days) from the activity_date, we can create a date_group. Consecutive dates will have the same date_group. We then count the number of rows in the most recent date_group to get the current streak.

Badges are awarded for achieving specific milestones, for example, burning a total of 10,000 calories.

What we're doing

We'll create a function that checks a user's progress towards a badge and awards it if the criteria are met.

Implementation

First, let's add some badge definitions to our badges table.

INSERT INTO badges (name, description, criteria_value) VALUES
('Calorie Crusher 10k', 'Burn a total of 10,000 calories', 10000);

Now, let's create badges.js.

// badges.js
const db = require('./db');

async function checkAndAwardBadges(userId) {
  const { rows: badges } = await db.query('SELECT * FROM badges');
  const { rows: userBadges } = await db.query('SELECT badge_id FROM user_badges WHERE user_id = $1', [userId]);
  const earnedBadgeIds = userBadges.map(b => b.badge_id);

  for (const badge of badges) {
    if (earnedBadgeIds.includes(badge.id)) continue;

    if (badge.name === 'Calorie Crusher 10k') {
      const { rows } = await db.query(
        'SELECT SUM(calories_burned) as total_calories FROM workout_activities WHERE user_id = $1',
        [userId]
      );
      const totalCalories = parseInt(rows[0].total_calories, 10) || 0;

      if (totalCalories >= badge.criteria_value) {
        await db.query('INSERT INTO user_badges (user_id, badge_id) VALUES ($1, $2)', [userId, badge.id]);
        console.log(`Awarded badge "${badge.name}" to user ${userId}`);
      }
    }
  }
}

module.exports = { checkAndAwardBadges };

We can call checkAndAwardBadges after a user logs a new workout.

A leaderboard ranks users based on a specific metric, such as total calories burned.

What we're doing

We'll use PostgreSQL's RANK() window function to efficiently generate a leaderboard.

Implementation

Let's create leaderboard.js.

// leaderboard.js
const db = require('./db');

async function getLeaderboard() {
  const query = `
    SELECT
      u.username,
      SUM(wa.calories_burned) as total_calories,
      RANK() OVER (ORDER BY SUM(wa.calories_burned) DESC) as rank
    FROM users u
    JOIN workout_activities wa ON u.id = wa.user_id
    GROUP BY u.id
    ORDER BY rank;
  `;
  const { rows } = await db.query(query);
  return rows;
}

module.exports = { getLeaderboard };

And add the endpoint to index.js.

// index.js (add this to your existing file)
const { getLeaderboard } = require('./leaderboard');

app.get('/leaderboard', async (req, res) => {
  try {
    const leaderboard = await getLeaderboard();
    res.json(leaderboard);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

How it works

The RANK() function assigns a rank to each user based on the sum of their calories_burned. The OVER (ORDER BY ...) clause specifies how to order the rows before ranking.

Now you have the core backend logic for streaks, badges, and leaderboards. You can expand on this by adding more complex badge criteria, different types of leaderboards (e.g., weekly, monthly), and more.

• Indexing: For larger tables, ensure you have indexes on foreign keys (user_id, badge_id) and frequently queried columns (activity_date).
• Query Optimization: Use EXPLAIN ANALYZE to inspect your query performance and identify bottlenecks.
• Materialized Views: For very large leaderboards that don't need to be real-time, consider using materialized views to pre-calculate rankings.

By integrating streaks, badges, and leaderboards, you can significantly boost user motivation and retention in your fitness app. With Node.js and the powerful features of PostgreSQL, you can build a scalable and efficient backend to support these engaging gamification elements.

Now it's your turn to expand on this foundation. What other gamification features can you dream up?

• PostgreSQL Window Functions Documentation
• Node.js pg library (node-postgres)