构建Node.js + Postgres健康游戏化API：完整技术指南

引言：游戏化在健康科技中的力量

游戏化（Gamification）将游戏设计元素应用于非游戏场景，在健康领域已被证明能显著提高用户参与度和行为改变效果。研究表明，良好的游戏化机制可以将健康应用的用户留存率提高30-50%，将日常健康行为的完成率提高25-40%。

本指南将详细介绍如何使用Node.js + PostgreSQL构建一个完整的健康游戏化API系统。

为什么选择Node.js + PostgreSQL？

技术栈	优势	健康应用适用性
Node.js	高并发处理、丰富的生态系统、实时能力	实时健康数据同步、快速API响应
PostgreSQL	ACID事务、复杂查询、JSON支持	复杂游戏规则、数据分析需求
TypeScript	类型安全、更好的IDE支持	减少运行时错误、提高代码质量

核心游戏化机制

本指南实现的系统包括：

积分系统：健康行为积分奖励
成就系统：里程碑式成就解锁
排行榜：社交竞争机制
每日挑战：短期目标激励
等级系统：长期进步追踪
徽章收集：可视化成就展示

系统架构设计

整体架构

code

┌──────────────────────────────────────────────────────────────┐
│                         客户端应用                            │
│  (移动应用 / Web应用 / 可穿戴设备)                             │
└────────────────────┬─────────────────────────────────────────┘
                     │ HTTP/REST API
                     │ WebSocket (实时通知)
┌────────────────────┴─────────────────────────────────────────┐
│                    API网关层 (Express.js)                     │
│  - 身份认证 (JWT)                                             │
│  - 速率限制                                                   │
│  - 请求日志                                                   │
└────────────────────┬─────────────────────────────────────────┘
                     │
┌────────────────────┴─────────────────────────────────────────┐
│                    业务逻辑层 (Services)                      │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐        │
│  │ Gamification │  │  Health      │  │  Social      │        │
│  │   Service    │  │  Tracking    │  │  Service     │        │
│  │              │  │  Service     │  │              │        │
│  └──────────────┘  └──────────────┘  └──────────────┘        │
└────────────────────┬─────────────────────────────────────────┘
                     │
┌────────────────────┴─────────────────────────────────────────┐
│                    数据访问层 (Repository)                    │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐        │
│  │   User       │  │   Points     │  │ Achievement  │        │
│  │ Repository   │  │ Repository   │  │ Repository   │        │
│  └──────────────┘  └──────────────┘  └──────────────┘        │
└────────────────────┬─────────────────────────────────────────┘
                     │
┌────────────────────┴─────────────────────────────────────────┐
│              PostgreSQL 数据库 (带 pgBouncer 连接池)          │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐        │
│  │   Users      │  │   Points     │  │ Achievements │        │
│  │   Table      │  │   Table      │  │   Table      │        │
│  └──────────────┘  └──────────────┘  └──────────────┘        │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐        │
│  │  Badges      │  │  Leaderboard │  │  Challenges  │        │
│  │   Table      │  │   Table      │  │   Table      │        │
│  └──────────────┘  └──────────────┘  └──────────────┘        │
└──────────────────────────────────────────────────────────────┘
                     │
┌────────────────────┴─────────────────────────────────────────┐
│                    Redis 缓存层                               │
│  - 会话管理                                                  │
│  - 排行榜缓存                                                │
│  - 实时统计                                                  │
└──────────────────────────────────────────────────────────────┘

Code collapsed

技术栈

code

{
  "backend": {
    "runtime": "Node.js 20 LTS",
    "framework": "Express.js 4.18",
    "language": "TypeScript 5.3"
  },
  "database": {
    "primary": "PostgreSQL 16",
    "cache": "Redis 7.2",
    "orm": "Prisma 5.7"
  },
  "libraries": {
    "authentication": "jsonwebtoken, bcrypt",
    "validation": "zod",
    "websocket": "socket.io",
    "testing": "jest, supertest"
  }
}

Code collapsed

数据库设计

Prisma Schema定义

code

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// 用户表
model User {
  id            String    @id @default(cuid())
  email         String    @unique
  username      String    @unique
  passwordHash  String
  displayName   String
  avatarUrl     String?
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  // 游戏化字段
  level         Int       @default(1)
  totalPoints   Int       @default(0)
  currentExp    Int       @default(0)

  // 关系
  points        Point[]
  achievements  UserAchievement[]
  badges        UserBadge[]
  challenges    UserChallenge[]
  leaderboardEntry Leaderboard?

  @@index([email])
  @@index([username])
  @@map("users")
}

// 积分记录表
model Point {
  id          String   @id @default(cuid())
  userId      String
  user        User     @relation(fields: [userId], references: [id])

  // 积分类型和来源
  type        PointType
  source      String   // 例如："daily_steps", "water_intake", "medication_adherence"
  amount      Int
  description String?

  // 元数据（JSON字段存储额外信息）
  metadata    Json?

  timestamp   DateTime @default(now())

  @@index([userId, timestamp])
  @@index([type])
  @@map("points")
}

enum PointType {
  DAILY_STEPS
  WATER_INTAKE
  MEDICATION_ADHERENCE
  SLEEP_TRACKING
  EXERCISE
  MEAL_LOGGING
  BLOOD_PRESSURE
  BLOOD_SUGAR
  WEIGHT_LOGGING
  CHALLENGE_COMPLETION
  ACHIEVEMENT_UNLOCK
  STREAK_BONUS
  SOCIAL_INTERACTION
}

// 成就表
model Achievement {
  id          String   @id @default(cuid())
  name        String
  description String
  iconUrl     String?
  category    AchievementCategory

  // 解锁条件
  requirement Json     // 存储解锁条件的JSON配置
  pointsReward Int    @default(0)

  // 难度级别
  difficulty  DifficultyLevel @default(MEDIUM)

  createdAt   DateTime @default(now())

  // 关系
  userAchievements UserAchievement[]

  @@index([category])
  @@map("achievements")
}

enum AchievementCategory {
  STEPS
  NUTRITION
  MEDICATION
  SLEEP
  EXERCISE
  SOCIAL
  STREAK
  MILESTONE
}

enum DifficultyLevel {
  EASY
  MEDIUM
  HARD
  EXPERT
}

// 用户成就关联表
model UserAchievement {
  id            String      @id @default(cuid())
  userId        String
  user          User        @relation(fields: [userId], references: [id])
  achievementId String
  achievement   Achievement @relation(fields: [achievementId], references: [id])

  progress      Int         @default(0)      // 当前进度
  target        Int                          // 目标值
  unlocked      Boolean     @default(false)
  unlockedAt    DateTime?

  @@unique([userId, achievementId])
  @@index([userId])
  @@index([achievementId])
  @@map("user_achievements")
}

// 徽章表
model Badge {
  id          String   @id @default(cuid())
  name        String
  description String
  iconUrl     String
  rarity      BadgeRarity @default(COMMON)

  // 解锁条件（多个成就的组合）
  requirements Json     // 成就ID数组和条件

  createdAt   DateTime @default(now())

  // 关系
  userBadges  UserBadge[]

  @@map("badges")
}

enum BadgeRarity {
  COMMON
  RARE
  EPIC
  LEGENDARY
}

// 用户徽章关联表
model UserBadge {
  id        String   @id @default(cuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  badgeId   String
  badge     Badge    @relation(fields: [badgeId], references: [id])

  earnedAt  DateTime @default(now())

  @@unique([userId, badgeId])
  @@index([userId])
  @@map("user_badges")
}

// 每日挑战表
model Challenge {
  id          String   @id @default(cuid())
  title       String
  description String
  type        ChallengeType

  // 挑战目标
  targetValue Int
  unit        String   // 例如："steps", "glasses", "minutes"

  // 奖励
  pointsReward Int
  badgeReward  String?  // 徽章ID（可选）

  // 时间范围
  startDate   DateTime
  endDate     DateTime

  // 难度
  difficulty  DifficultyLevel @default(MEDIUM)

  createdAt   DateTime @default(now())

  // 关系
  userChallenges UserChallenge[]

  @@index([startDate, endDate])
  @@map("challenges")
}

enum ChallengeType {
  DAILY_STEPS
  WATER_INTAKE
  EXERCISE_MINUTES
  SLEEP_HOURS
  CALORIE_BURN
  MEDITATION_MINUTES
  MEAL_LOGGING
  MEDICATION_ADHERENCE
}

// 用户挑战关联表
model UserChallenge {
  id          String    @id @default(cuid())
  userId      String
  user        User      @relation(fields: [userId], references: [id])
  challengeId String
  challenge   Challenge @relation(fields: [challengeId], references: [id])

  progress    Int       @default(0)
  completed   Boolean   @default(false)
  completedAt DateTime?

  @@unique([userId, challengeId])
  @@index([userId])
  @@map("user_challenges")
}

// 排行榜表
model Leaderboard {
  id              String   @id @default(cuid())
  userId          String   @unique
  user            User     @relation(fields: [userId], references: [id])

  // 排行榜类型
  type            LeaderboardType @default(OVERALL)

  // 得分
  score           Int      @default(0)
  weeklyScore     Int      @default(0)

  // 排名（缓存）
  rank            Int?
  weeklyRank      Int?

  // 时间范围
  periodStart     DateTime @default(now())
  periodEnd       DateTime

  updatedAt       DateTime @updatedAt

  @@index([type, score])
  @@index([type, weeklyScore])
  @@map("leaderboard")
}

enum LeaderboardType {
  OVERALL
  STEPS
  EXERCISE
  NUTRITION
  SLEEP
  STREAK
  WEEKLY
  MONTHLY
}

// 社交关系表
model SocialConnection {
  id          String   @id @default(cuid())
  userId      String
  friendId    String

  // 关系类型
  connectionType ConnectionType @default(FRIEND)

  createdAt   DateTime @default(now())

  @@unique([userId, friendId])
  @@index([userId])
  @@index([friendId])
  @@map("social_connections")
}

enum ConnectionType {
  FRIEND
  FOLLOWER
  CHALLENGE_PARTNER
}

Code collapsed

数据库迁移

code

# 生成迁移文件
npx prisma migrate dev --name init_gamification_schema

# 应用迁移到生产环境
npx prisma migrate deploy

# 生成Prisma客户端
npx prisma generate

Code collapsed

核心服务实现

用户服务（UserService）

code

// src/services/user.service.ts

import { PrismaClient, User } from '@prisma/client';
import bcrypt from 'bcrypt';
import jwt from 'jsonwebtoken';
import { ConflictError, UnauthorizedError } from '../utils/errors';

export interface CreateUserData {
  email: string;
  username: string;
  password: string;
  displayName: string;
}

export interface LoginData {
  email: string;
  password: string;
}

export class UserService {
  private prisma: PrismaClient;
  private jwtSecret: string;

  constructor() {
    this.prisma = new PrismaClient();
    this.jwtSecret = process.env.JWT_SECRET || 'your-secret-key';
  }

  /**
   * 创建新用户
   */
  async createUser(data: CreateUserData): Promise<{ user: Partial<User>; token: string }> {
    // 检查邮箱是否已存在
    const existingEmail = await this.prisma.user.findUnique({
      where: { email: data.email }
    });

    if (existingEmail) {
      throw new ConflictError('邮箱已被注册');
    }

    // 检查用户名是否已存在
    const existingUsername = await this.prisma.user.findUnique({
      where: { username: data.username }
    });

    if (existingUsername) {
      throw new ConflictError('用户名已被使用');
    }

    // 加密密码
    const passwordHash = await bcrypt.hash(data.password, 10);

    // 创建用户
    const user = await this.prisma.user.create({
      data: {
        email: data.email,
        username: data.username,
        passwordHash,
        displayName: data.displayName,
        level: 1,
        totalPoints: 0,
        currentExp: 0
      }
    });

    // 初始化排行榜记录
    await this.prisma.leaderboard.create({
      data: {
        userId: user.id,
        type: 'OVERALL',
        score: 0,
        weeklyScore: 0,
        periodEnd: this.getEndOfWeek()
      }
    });

    // 生成JWT令牌
    const token = this.generateToken(user.id);

    const { passwordHash: _, ...userWithoutPassword } = user;

    return { user: userWithoutPassword, token };
  }

  /**
   * 用户登录
   */
  async login(data: LoginData): Promise<{ user: Partial<User>; token: string }> {
    const user = await this.prisma.user.findUnique({
      where: { email: data.email }
    });

    if (!user) {
      throw new UnauthorizedError('邮箱或密码错误');
    }

    const isPasswordValid = await bcrypt.compare(
      data.password,
      user.passwordHash
    );

    if (!isPasswordValid) {
      throw new UnauthorizedError('邮箱或密码错误');
    }

    const token = this.generateToken(user.id);
    const { passwordHash: _, ...userWithoutPassword } = user;

    return { user: userWithoutPassword, token };
  }

  /**
   * 获取用户信息
   */
  async getUserById(userId: string): Promise<Partial<User>> {
    const user = await this.prisma.user.findUnique({
      where: { id: userId }
    });

    if (!user) {
      throw new Error('用户不存在');
    }

    const { passwordHash: _, ...userWithoutPassword } = user;
    return userWithoutPassword;
  }

  /**
   * 获取用户游戏化统计信息
   */
  async getUserGamificationStats(userId: string) {
    const [user, achievements, badges, points] = await Promise.all([
      this.prisma.user.findUnique({
        where: { id: userId },
        include: {
          leaderboardEntry: true
        }
      }),
      this.prisma.userAchievement.count({
        where: { userId, unlocked: true }
      }),
      this.prisma.userBadge.count({
        where: { userId }
      }),
      this.prisma.point.groupBy({
        by: ['type'],
        where: { userId },
        _sum: {
          amount: true
        }
      })
    ]);

    return {
      level: user?.level,
      totalPoints: user?.totalPoints,
      currentExp: user?.currentExp,
      expToNextLevel: this.getExpForLevel(user?.level || 1),
      achievementsUnlocked: achievements,
      badgesEarned: badges,
      leaderboardRank: user?.leaderboardEntry?.rank,
      pointsByType: points.map(p => ({
        type: p.type,
        total: p._sum.amount || 0
      }))
    };
  }

  /**
   * 生成JWT令牌
   */
  private generateToken(userId: string): string {
    return jwt.sign(
      { userId },
      this.jwtSecret,
      { expiresIn: '7d' }
    );
  }

  /**
   * 获取本周结束时间
   */
  private getEndOfWeek(): Date {
    const now = new Date();
    const dayOfWeek = now.getDay();
    const daysUntilSunday = 7 - dayOfWeek;
    const endOfWeek = new Date(now);
    endOfWeek.setDate(now.getDate() + daysUntilSunday);
    endOfWeek.setHours(23, 59, 59, 999);
    return endOfWeek;
  }

  /**
   * 计算升级所需经验值
   */
  private getExpForLevel(level: number): number {
    return Math.floor(100 * Math.pow(1.5, level - 1));
  }
}

Code collapsed

游戏化服务（GamificationService）

code

// src/services/gamification.service.ts

import { PrismaClient } from '@prisma/client';
import { UserService } from './user.service';
import { NotificationService } from './notification.service';

export interface AddPointsData {
  userId: string;
  type: string;
  source: string;
  amount: number;
  description?: string;
  metadata?: Record<string, any>;
}

export class GamificationService {
  private prisma: PrismaClient;
  private userService: UserService;
  private notificationService: NotificationService;

  constructor() {
    this.prisma = new PrismaClient();
    this.userService = new UserService();
    this.notificationService = new NotificationService();
  }

  /**
   * 添加积分并处理升级逻辑
   */
  async addPoints(data: AddPointsData) {
    return await this.prisma.$transaction(async (tx) => {
      // 1. 创建积分记录
      const point = await tx.point.create({
        data: {
          userId: data.userId,
          type: data.type as any,
          source: data.source,
          amount: data.amount,
          description: data.description,
          metadata: data.metadata as any
        }
      });

      // 2. 更新用户总积分
      const user = await tx.user.update({
        where: { id: data.userId },
        data: {
          totalPoints: {
            increment: data.amount
          },
          currentExp: {
            increment: data.amount
          }
        }
      });

      // 3. 检查是否升级
      const levelUp = await this.checkLevelUp(tx, user);

      // 4. 更新排行榜分数
      await tx.leaderboard.update({
        where: { userId: data.userId },
        data: {
          score: {
            increment: data.amount
          },
          weeklyScore: {
            increment: data.amount
          }
        }
      });

      // 5. 检查成就解锁
      await this.checkAchievements(tx, data.userId);

      return {
        point,
        newLevel: levelUp ? user.level : null,
        newExp: user.currentExp
      };
    });
  }

  /**
   * 检查并处理用户升级
   */
  private async checkLevelUp(
    tx: any,
    user: any
  ): Promise<boolean> {
    const expForNextLevel = this.getExpForLevel(user.level);

    if (user.currentExp >= expForNextLevel) {
      const newLevel = user.level + 1;
      const remainingExp = user.currentExp - expForNextLevel;

      await tx.user.update({
        where: { id: user.id },
        data: {
          level: newLevel,
          currentExp: remainingExp
        }
      });

      // 发送升级通知
      await this.notificationService.sendNotification({
        userId: user.id,
        type: 'LEVEL_UP',
        title: '恭喜升级！',
        message: `你已达到 ${newLevel} 级！`,
        data: { newLevel }
      });

      return true;
    }

    return false;
  }

  /**
   * 检查并解锁成就
   */
  private async checkAchievements(tx: any, userId: string) {
    // 获取所有未解锁的成就
    const achievements = await tx.achievement.findMany({
      where: {
        userAchievements: {
          none: {
            userId,
            unlocked: true
          }
        }
      }
    });

    for (const achievement of achievements) {
      const unlocked = await this.verifyAchievement(tx, userId, achievement);

      if (unlocked) {
        await this.unlockAchievement(tx, userId, achievement.id);
      }
    }
  }

  /**
   * 验证成就是否满足解锁条件
   */
  private async verifyAchievement(
    tx: any,
    userId: string,
    achievement: any
  ): Promise<boolean> {
    const requirement = achievement.requirement as any;

    switch (requirement.type) {
      case 'TOTAL_POINTS':
        const user = await tx.user.findUnique({
          where: { id: userId }
        });
        return user?.totalPoints >= requirement.value;

      case 'DAILY_STEPS_STREAK':
        // 检查连续打卡天数
        const streak = await this.getStreakDays(tx, userId, 'DAILY_STEPS');
        return streak >= requirement.value;

      case 'POINTS_FROM_SOURCE':
        const sourcePoints = await tx.point.aggregate({
          where: {
            userId,
            source: requirement.source
          },
          _sum: {
            amount: true
          }
        });
        return (sourcePoints._sum.amount || 0) >= requirement.value;

      case 'LEVEL_REACHED':
        const userLevel = await tx.user.findUnique({
          where: { id: userId }
        });
        return userLevel?.level >= requirement.value;

      default:
        return false;
    }
  }

  /**
   * 解锁成就
   */
  private async unlockAchievement(
    tx: any,
    userId: string,
    achievementId: string
  ) {
    const achievement = await tx.achievement.findUnique({
      where: { id: achievementId }
    });

    if (!achievement) return;

    // 更新或创建用户成就记录
    await tx.userAchievement.upsert({
      where: {
        userId_achievementId: {
          userId,
          achievementId
        }
      },
      create: {
        userId,
        achievementId,
        progress: 100,
        target: 100,
        unlocked: true,
        unlockedAt: new Date()
      },
      update: {
        unlocked: true,
        unlockedAt: new Date()
      }
    });

    // 给予积分奖励
    if (achievement.pointsReward > 0) {
      await tx.user.update({
        where: { id: userId },
        data: {
          totalPoints: {
            increment: achievement.pointsReward
          }
        }
      });
    }

    // 发送成就解锁通知
    await this.notificationService.sendNotification({
      userId,
      type: 'ACHIEVEMENT_UNLOCK',
      title: '成就解锁！',
      message: `你解锁了成就：${achievement.name}`,
      data: {
        achievementId,
        pointsReward: achievement.pointsReward
      }
    });

    // 检查徽章解锁
    await this.checkBadges(tx, userId);
  }

  /**
   * 检查并解锁徽章
   */
  private async checkBadges(tx: any, userId: string) {
    const badges = await tx.badge.findMany({
      where: {
        userBadges: {
          none: { userId }
        }
      }
    });

    for (const badge of badges) {
      const unlocked = await this.verifyBadgeRequirements(
        tx,
        userId,
        badge
      );

      if (unlocked) {
        await this.unlockBadge(tx, userId, badge.id);
      }
    }
  }

  /**
   * 验证徽章解锁条件
   */
  private async verifyBadgeRequirements(
    tx: any,
    userId: string,
    badge: any
  ): Promise<boolean> {
    const requirements = badge.requirements as any;

    if (requirements.requiredAchievements) {
      const unlockedAchievements = await tx.userAchievement.count({
        where: {
          userId,
          unlocked: true,
          achievementId: {
            in: requirements.requiredAchievements
          }
        }
      });

      return unlockedAchievements >= requirements.requiredAchievements.length;
    }

    return false;
  }

  /**
   * 解锁徽章
   */
  private async unlockBadge(
    tx: any,
    userId: string,
    badgeId: string
  ) {
    const badge = await tx.badge.findUnique({
      where: { id: badgeId }
    });

    if (!badge) return;

    await tx.userBadge.create({
      data: {
        userId,
        badgeId
      }
    });

    // 发送徽章解锁通知
    await this.notificationService.sendNotification({
      userId,
      type: 'BADGE_EARN',
      title: '获得徽章！',
      message: `你获得了徽章：${badge.name}`,
      data: { badgeId }
    });
  }

  /**
   * 获取用户连续打卡天数
   */
  private async getStreakDays(
    tx: any,
    userId: string,
    source: string
  ): Promise<number> {
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    let streak = 0;
    let currentDate = today;

    while (true) {
      const dayStart = new Date(currentDate);
      const dayEnd = new Date(currentDate);
      dayEnd.setHours(23, 59, 59, 999);

      const point = await tx.point.findFirst({
        where: {
          userId,
          source,
          timestamp: {
            gte: dayStart,
            lte: dayEnd
          }
        }
      });

      if (point) {
        streak++;
        currentDate.setDate(currentDate.getDate() - 1);
      } else {
        break;
      }
    }

    return streak;
  }

  /**
   * 计算升级所需经验值
   */
  private getExpForLevel(level: number): number {
    return Math.floor(100 * Math.pow(1.5, level - 1));
  }
}

Code collapsed

健康追踪服务（HealthTrackingService）

code

// src/services/health-tracking.service.ts

import { PrismaClient } from '@prisma/client';
import { GamificationService } from './gamification.service';

export interface LogStepsData {
  userId: string;
  steps: number;
  date: Date;
}

export interface LogWaterIntakeData {
  userId: string;
  glasses: number;
}

export interface LogExerciseData {
  userId: string;
  minutes: number;
  type: string;
  calories?: number;
}

export class HealthTrackingService {
  private prisma: PrismaClient;
  private gamificationService: GamificationService;

  constructor() {
    this.prisma = new PrismaClient();
    this.gamificationService = new GamificationService();
  }

  /**
   * 记录步数
   */
  async logSteps(data: LogStepsData) {
    // 计算积分（每1000步 = 10积分）
    const pointsEarned = Math.floor(data.steps / 1000) * 10;

    const result = await this.gamificationService.addPoints({
      userId: data.userId,
      type: 'DAILY_STEPS',
      source: 'daily_steps',
      amount: pointsEarned,
      description: `完成 ${data.steps} 步`,
      metadata: {
        steps: data.steps,
        date: data.date
      }
    });

    // 检查每日挑战
    await this.checkDailyChallenge(data.userId, 'DAILY_STEPS', data.steps);

    return result;
  }

  /**
   * 记录饮水量
   */
  async logWaterIntake(data: LogWaterIntakeData) {
    // 每杯水 = 5积分
    const pointsEarned = data.glasses * 5;

    const result = await this.gamificationService.addPoints({
      userId: data.userId,
      type: 'WATER_INTAKE',
      source: 'water_intake',
      amount: pointsEarned,
      description: `饮用 ${data.glasses} 杯水`,
      metadata: {
        glasses: data.glasses
      }
    });

    // 检查每日挑战
    await this.checkDailyChallenge(data.userId, 'WATER_INTAKE', data.glasses);

    return result;
  }

  /**
   * 记录运动
   */
  async logExercise(data: LogExerciseData) {
    // 每分钟运动 = 2积分
    const pointsEarned = data.minutes * 2;

    const result = await this.gamificationService.addPoints({
      userId: data.userId,
      type: 'EXERCISE',
      source: 'exercise',
      amount: pointsEarned,
      description: `${data.type} ${data.minutes} 分钟`,
      metadata: {
        minutes: data.minutes,
        type: data.type,
        calories: data.calories
      }
    });

    // 检查每日挑战
    await this.checkDailyChallenge(
      data.userId,
      'EXERCISE_MINUTES',
      data.minutes
    );

    return result;
  }

  /**
   * 检查并更新每日挑战进度
   */
  private async checkDailyChallenge(
    userId: string,
    challengeType: string,
    value: number
  ) {
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    const tomorrow = new Date(today);
    tomorrow.setDate(tomorrow.getDate() + 1);

    // 查找今日的活跃挑战
    const challenge = await this.prisma.challenge.findFirst({
      where: {
        type: challengeType as any,
        startDate: { lte: today },
        endDate: { gt: today }
      },
      include: {
        userChallenges: {
          where: { userId }
        }
      }
    });

    if (!challenge) return;

    // 获取或创建用户挑战记录
    const userChallenge = await this.prisma.userChallenge.upsert({
      where: {
        userId_challengeId: {
          userId,
          challengeId: challenge.id
        }
      },
      create: {
        userId,
        challengeId: challenge.id,
        progress: value
      },
      update: {
        progress: {
          increment: value
        }
      }
    });

    // 检查是否完成挑战
    if (
      !userChallenge.completed &&
      userChallenge.progress >= challenge.targetValue
    ) {
      await this.prisma.userChallenge.update({
        where: { id: userChallenge.id },
        data: {
          completed: true,
          completedAt: new Date()
        }
      });

      // 给予奖励
      await this.gamificationService.addPoints({
        userId,
        type: 'CHALLENGE_COMPLETION',
        source: 'daily_challenge',
        amount: challenge.pointsReward,
        description: `完成挑战：${challenge.title}`
      });
    }
  }
}

Code collapsed

排行榜服务（LeaderboardService）

code

// src/services/leaderboard.service.ts

import { PrismaClient } from '@prisma/client';
import { Redis } from 'ioredis';

export class LeaderboardService {
  private prisma: PrismaClient;
  private redis: Redis;

  constructor() {
    this.prisma = new PrismaClient();
    this.redis = new Redis({
      host: process.env.REDIS_HOST || 'localhost',
      port: parseInt(process.env.REDIS_PORT || '6379')
    });
  }

  /**
   * 获取排行榜
   */
  async getLeaderboard(
    type: string = 'OVERALL',
    limit: number = 100,
    offset: number = 0
  ) {
    const cacheKey = `leaderboard:${type}:${limit}:${offset}`;

    // 尝试从缓存获取
    const cached = await this.redis.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    // 从数据库查询
    const leaderboard = await this.prisma.leaderboard.findMany({
      where: { type: type as any },
      include: {
        user: {
          select: {
            username: true,
            displayName: true,
            avatarUrl: true,
            level: true
          }
        }
      },
      orderBy: [
        { score: 'desc' },
        { updatedAt: 'asc' }
      ],
      take: limit,
      skip: offset
    });

    // 格式化结果
    const result = leaderboard.map((entry, index) => ({
      rank: offset + index + 1,
      userId: entry.userId,
      username: entry.user.username,
      displayName: entry.user.displayName,
      avatarUrl: entry.user.avatarUrl,
      level: entry.user.level,
      score: entry.score,
      weeklyScore: entry.weeklyScore
    }));

    // 缓存结果（5分钟）
    await this.redis.setex(cacheKey, 300, JSON.stringify(result));

    return result;
  }

  /**
   * 获取用户排名
   */
  async getUserRank(userId: string, type: string = 'OVERALL') {
    const cacheKey = `user_rank:${userId}:${type}`;

    const cached = await this.redis.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    const entry = await this.prisma.leaderboard.findUnique({
      where: { userId_type: { userId, type: type as any } }
    });

    if (!entry) {
      return { rank: null, score: 0 };
    }

    // 计算实际排名
    const rank = await this.prisma.leaderboard.count({
      where: {
        type: type as any,
        score: { gt: entry.score }
      }
    }) + 1;

    const result = {
      rank,
      score: entry.score,
      weeklyScore: entry.weeklyScore
    };

    await this.redis.setex(cacheKey, 60, JSON.stringify(result));

    return result;
  }

  /**
   * 更新排行榜缓存
   */
  async updateLeaderboardCache() {
    const types = ['OVERALL', 'STEPS', 'EXERCISE', 'NUTRITION', 'WEEKLY'];

    for (const type of types) {
      const cacheKey = `leaderboard:${type}:100:0`;

      const leaderboard = await this.prisma.leaderboard.findMany({
        where: { type: type as any },
        include: {
          user: {
            select: {
              username: true,
              displayName: true,
              avatarUrl: true,
              level: true
            }
          }
        },
        orderBy: [
          { score: 'desc' },
          { updatedAt: 'asc' }
        ],
        take: 100
      });

      const result = leaderboard.map((entry, index) => ({
        rank: index + 1,
        userId: entry.userId,
        username: entry.user.username,
        displayName: entry.user.displayName,
        avatarUrl: entry.user.avatarUrl,
        level: entry.user.level,
        score: entry.score
      }));

      await this.redis.setex(cacheKey, 300, JSON.stringify(result));
    }
  }
}

Code collapsed

API端点实现

Express路由设置

code

// src/routes/gamification.routes.ts

import { Router } from 'express';
import { authenticateToken } from '../middleware/auth';
import { GamificationService } from '../services/gamification.service';
import { HealthTrackingService } from '../services/health-tracking.service';
import { LeaderboardService } from '../services/leaderboard.service';
import { z } from 'zod';

const router = Router();
const gamificationService = new GamificationService();
const healthTrackingService = new HealthTrackingService();
const leaderboardService = new LeaderboardService();

// ============================================
// 健康数据追踪端点
// ============================================

/**
 * POST /api/health/steps
 * 记录步数
 */
router.post('/health/steps', authenticateToken, async (req, res, next) => {
  try {
    const schema = z.object({
      steps: z.number().min(0).max(100000),
      date: z.string().datetime().optional()
    });

    const data = schema.parse(req.body);

    const result = await healthTrackingService.logSteps({
      userId: req.user.userId,
      steps: data.steps,
      date: data.date ? new Date(data.date) : new Date()
    });

    res.json({
      success: true,
      data: result
    });
  } catch (error) {
    next(error);
  }
});

/**
 * POST /api/health/water
 * 记录饮水量
 */
router.post('/health/water', authenticateToken, async (req, res, next) => {
  try {
    const schema = z.object({
      glasses: z.number().min(1).max(20)
    });

    const data = schema.parse(req.body);

    const result = await healthTrackingService.logWaterIntake({
      userId: req.user.userId,
      glasses: data.glasses
    });

    res.json({
      success: true,
      data: result
    });
  } catch (error) {
    next(error);
  }
});

/**
 * POST /api/health/exercise
 * 记录运动
 */
router.post('/health/exercise', authenticateToken, async (req, res, next) => {
  try {
    const schema = z.object({
      minutes: z.number().min(1).max(300),
      type: z.string(),
      calories: z.number().optional()
    });

    const data = schema.parse(req.body);

    const result = await healthTrackingService.logExercise({
      userId: req.user.userId,
      minutes: data.minutes,
      type: data.type,
      calories: data.calories
    });

    res.json({
      success: true,
      data: result
    });
  } catch (error) {
    next(error);
  }
});

// ============================================
// 游戏化统计端点
// ============================================

/**
 * GET /api/gamification/stats
 * 获取用户游戏化统计
 */
router.get('/gamification/stats', authenticateToken, async (req, res, next) => {
  try {
    const stats = await gamificationService.getUserGamificationStats(
      req.user.userId
    );

    res.json({
      success: true,
      data: stats
    });
  } catch (error) {
    next(error);
  }
});

/**
 * GET /api/gamification/achievements
 * 获取用户成就列表
 */
router.get('/gamification/achievements', authenticateToken, async (req, res, next) => {
  try {
    const prisma = new PrismaClient();
    const achievements = await prisma.userAchievement.findMany({
      where: { userId: req.user.userId },
      include: {
        achievement: true
      },
      orderBy: { unlockedAt: 'desc' }
    });

    res.json({
      success: true,
      data: achievements
    });
  } catch (error) {
    next(error);
  }
});

/**
 * GET /api/gamification/badges
 * 获取用户徽章列表
 */
router.get('/gamification/badges', authenticateToken, async (req, res, next) => {
  try {
    const prisma = new PrismaClient();
    const badges = await prisma.userBadge.findMany({
      where: { userId: req.user.userId },
      include: {
        badge: true
      },
      orderBy: { earnedAt: 'desc' }
    });

    res.json({
      success: true,
      data: badges
    });
  } catch (error) {
    next(error);
  }
});

// ============================================
// 排行榜端点
// ============================================

/**
 * GET /api/leaderboard
 * 获取排行榜
 */
router.get('/leaderboard', async (req, res, next) => {
  try {
    const schema = z.object({
      type: z.enum(['OVERALL', 'STEPS', 'EXERCISE', 'NUTRITION', 'WEEKLY']).optional(),
      limit: z.coerce.number().min(1).max(100).optional(),
      offset: z.coerce.number().min(0).optional()
    });

    const query = schema.parse(req.query);

    const leaderboard = await leaderboardService.getLeaderboard(
      query.type || 'OVERALL',
      query.limit || 50,
      query.offset || 0
    );

    res.json({
      success: true,
      data: leaderboard
    });
  } catch (error) {
    next(error);
  }
});

/**
 * GET /api/leaderboard/my-rank
 * 获取当前用户排名
 */
router.get('/leaderboard/my-rank', authenticateToken, async (req, res, next) => {
  try {
    const schema = z.object({
      type: z.enum(['OVERALL', 'STEPS', 'EXERCISE', 'NUTRITION', 'WEEKLY']).optional()
    });

    const query = schema.parse(req.query);

    const rank = await leaderboardService.getUserRank(
      req.user.userId,
      query.type || 'OVERALL'
    );

    res.json({
      success: true,
      data: rank
    });
  } catch (error) {
    next(error);
  }
});

export default router;

Code collapsed

测试实现

单元测试示例

code

// tests/services/gamification.service.test.ts

import { GamificationService } from '../../src/services/gamification.service';
import { PrismaClient } from '@prisma/client';

describe('GamificationService', () => {
  let gamificationService: GamificationService;
  let prisma: PrismaClient;

  beforeEach(() => {
    prisma = new PrismaClient();
    gamificationService = new GamificationService();
  });

  afterEach(async () => {
    await prisma.$disconnect();
  });

  describe('addPoints', () => {
    it('应该正确添加积分', async () => {
      // 创建测试用户
      const user = await prisma.user.create({
        data: {
          email: 'test@example.com',
          username: 'testuser',
          passwordHash: 'hash',
          displayName: 'Test User'
        }
      });

      const result = await gamificationService.addPoints({
        userId: user.id,
        type: 'DAILY_STEPS',
        source: 'daily_steps',
        amount: 100,
        description: '完成目标'
      });

      expect(result.point.amount).toBe(100);
      expect(result.newExp).toBeGreaterThan(0);
    });

    it('应该在达到阈值时升级用户', async () => {
      const user = await prisma.user.create({
        data: {
          email: 'test2@example.com',
          username: 'testuser2',
          passwordHash: 'hash',
          displayName: 'Test User 2',
          level: 1,
          currentExp: 90
        }
      });

      // 添加足够经验升级
      const result = await gamificationService.addPoints({
        userId: user.id,
        type: 'DAILY_STEPS',
        source: 'daily_steps',
        amount: 20
      });

      expect(result.newLevel).toBe(2);
    });
  });

  describe('achievements', () => {
    it('应该在达到条件时解锁成就', async () => {
      // 创建测试成就
      const achievement = await prisma.achievement.create({
        data: {
          name: '初次体验',
          description: '获得第一个积分',
          category: 'MILESTONE',
          requirement: {
            type: 'TOTAL_POINTS',
            value: 1
          },
          pointsReward: 50
        }
      });

      const user = await prisma.user.create({
        data: {
          email: 'test3@example.com',
          username: 'testuser3',
          passwordHash: 'hash',
          displayName: 'Test User 3'
        }
      });

      await gamificationService.addPoints({
        userId: user.id,
        type: 'DAILY_STEPS',
        source: 'test',
        amount: 10
      });

      const userAchievement = await prisma.userAchievement.findUnique({
        where: {
          userId_achievementId: {
            userId: user.id,
            achievementId: achievement.id
          }
        }
      });

      expect(userAchievement?.unlocked).toBe(true);
    });
  });
});

Code collapsed

API集成测试

code

// tests/api/gamification.routes.test.ts

import request from 'supertest';
import app from '../../src/app';
import { PrismaClient } from '@prisma/client';
import jwt from 'jsonwebtoken';

describe('Gamification API', () => {
  let prisma: PrismaClient;
  let authToken: string;
  let testUserId: string;

  beforeAll(async () => {
    prisma = new PrismaClient();

    // 创建测试用户
    const user = await prisma.user.create({
      data: {
        email: 'api-test@example.com',
        username: 'apitest',
        passwordHash: 'hash',
        displayName: 'API Test User'
      }
    });

    testUserId = user.id;
    authToken = jwt.sign({ userId: user.id }, 'test-secret');
  });

  afterAll(async () => {
    // 清理测试数据
    await prisma.user.deleteMany({
      where: { email: 'api-test@example.com' }
    });
    await prisma.$disconnect();
  });

  describe('POST /api/health/steps', () => {
    it('应该成功记录步数', async () => {
      const response = await request(app)
        .post('/api/health/steps')
        .set('Authorization', `Bearer ${authToken}`)
        .send({ steps: 10000 })
        .expect(200);

      expect(response.body.success).toBe(true);
      expect(response.body.data.point.amount).toBeGreaterThan(0);
    });

    it('应该验证步数范围', async () => {
      await request(app)
        .post('/api/health/steps')
        .set('Authorization', `Bearer ${authToken}`)
        .send({ steps: -100 })
        .expect(400);
    });
  });

  describe('GET /api/leaderboard', () => {
    it('应该返回排行榜数据', async () => {
      const response = await request(app)
        .get('/api/leaderboard')
        .expect(200);

      expect(response.body.success).toBe(true);
      expect(Array.isArray(response.body.data)).toBe(true);
    });
  });
});

Code collapsed

部署配置

Docker Compose配置

code

# docker-compose.yml

version: '3.8'

services:
  api:
    build: .
    ports:
      - "3000:3000"
    environment:
      - NODE_ENV=production
      - DATABASE_URL=postgresql://user:password@db:5432/health_gamification
      - REDIS_URL=redis://redis:6379
      - JWT_SECRET=${JWT_SECRET}
    depends_on:
      - db
      - redis
    restart: unless-stopped

  db:
    image: postgres:16-alpine
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=health_gamification
    ports:
      - "5432:5432"
    restart: unless-stopped

  redis:
    image: redis:7-alpine
    ports:
      - "6379:6379"
    restart: unless-stopped

  pgadmin:
    image: dpage/pgadmin4:latest
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@wellally.com
      - PGADMIN_DEFAULT_PASSWORD=admin
    ports:
      - "5050:80"
    depends_on:
      - db

volumes:
  postgres_data:

Code collapsed

生产环境最佳实践

连接池配置

code

// 使用pgBouncer进行连接池管理
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  }
});

// 配置连接池
await prisma.$connect();

Code collapsed

错误处理和日志

code

// src/utils/error-handler.ts
import { Request, Response, NextFunction } from 'express';
import { ZodError } from 'zod';

export function errorHandler(
  err: Error,
  req: Request,
  res: Response,
  next: NextFunction
) {
  console.error('Error:', err);

  if (err instanceof ZodError) {
    return res.status(400).json({
      success: false,
      error: '验证错误',
      details: err.errors
    });
  }

  res.status(500).json({
    success: false,
    error: '服务器内部错误'
  });
}

Code collapsed

速率限制

code

// src/middleware/rate-limit.ts
import rateLimit from 'express-rate-limit';

export const apiLimiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15分钟
  max: 100, // 限制每个IP 15分钟内最多100个请求
  message: '请求过于频繁，请稍后再试'
});

Code collapsed

性能优化策略

数据库优化

索引策略

code

-- 为常用查询创建索引
CREATE INDEX idx_points_user_type_timestamp ON points(user_id, type, timestamp DESC);
CREATE INDEX idx_leaderboard_type_score ON leaderboard(type, score DESC);
CREATE INDEX idx_user_achievements_unlocked ON user_achievements(user_id) WHERE unlocked = true;

Code collapsed

查询优化

code

// 使用select字段限制返回数据
const user = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    id: true,
    username: true,
    displayName: true,
    level: true,
    totalPoints: true
    // 不返回passwordHash等敏感字段
  }
});

Code collapsed

批量操作

code

// 批量插入积分记录
await prisma.point.createMany({
  data: pointsData,
  skipDuplicates: true
});

Code collapsed

缓存策略

数据类型	缓存时间	缓存策略
排行榜	5分钟	Redis缓存
用户统计	1分钟	Redis缓存
成就列表	1小时	Redis缓存
用户配置	30分钟	Redis缓存

监控和分析

关键指标监控

code

// src/utils/metrics.ts
import { Counter, Histogram, Registry } from 'prom-client';

export const metrics = {
  // API请求计数
  httpRequestsTotal: new Counter({
    name: 'http_requests_total',
    help: 'Total HTTP requests',
    labelNames: ['method', 'route', 'status_code']
  }),

  // API响应时间
  httpRequestDuration: new Histogram({
    name: 'http_request_duration_seconds',
    help: 'HTTP request duration',
    labelNames: ['method', 'route'],
    buckets: [0.1, 0.5, 1, 2, 5]
  }),

  // 游戏化事件
  gamificationEvents: new Counter({
    name: 'gamification_events_total',
    help: 'Total gamification events',
    labelNames: ['event_type']
  })
};

Code collapsed

安全最佳实践

输入验证
- 使用Zod进行严格的输入验证
- 防止SQL注入
- XSS防护
认证和授权
- JWT令牌验证
- 权限检查
- 速率限制
数据保护
- 密码加密（bcrypt）
- 敏感数据加密
- HTTPS强制

未来扩展方向

机器学习集成
- 个性化积分推荐
- 用户行为预测
- 自适应挑战难度
社交功能增强
- 好友系统
- 群组挑战
- 社交分享
多租户支持
- 企业版功能
- 定制化游戏规则
- 白标解决方案

结论

构建成功的健康游戏化系统需要技术实现与行为设计的完美结合。本指南提供的Node.js + PostgreSQL架构为开发者提供了坚实的基础，同时保持了足够的灵活性来满足不同健康应用的需求。

关键成功因素：

用户参与度：设计有趣且有意义的游戏机制
技术稳定性：确保系统可靠、快速、安全
数据驱动：持续分析和优化游戏化策略
隐私保护：遵守医疗数据保护法规（HIPAA/GDPR）

通过遵循本指南的最佳实践，开发团队可以构建出既技术先进又用户友好的健康游戏化平台，真正帮助用户建立和维持健康的生活习惯。

构建Node.js + Postgres健康游戏化API：完整技术指南

构建Node.js + Postgres健康游戏化API：完整技术指南

引言：游戏化在健康科技中的力量

为什么选择Node.js + PostgreSQL？

核心游戏化机制

系统架构设计

整体架构

技术栈

数据库设计

Prisma Schema定义

数据库迁移

核心服务实现

用户服务（UserService）

游戏化服务（GamificationService）

健康追踪服务（HealthTrackingService）

排行榜服务（LeaderboardService）

API端点实现

Express路由设置

测试实现

单元测试示例

API集成测试

部署配置

Docker Compose配置

生产环境最佳实践

性能优化策略

数据库优化

缓存策略

监控和分析

关键指标监控

安全最佳实践

未来扩展方向

结论

文章标签

相关文章推荐

FHIR互操作性指南：现代医疗健康数据交换的完整指南

AI早期疾病检测：机器学习如何拯救生命

觉得这篇文章有帮助？