Add missing database indexes to gamification entities

[RUKAYAT-CODER]

Overview

src/gamification/entities/point-transaction.entity.ts, challenge.entity.ts, user-challenge.entity.ts, and tier-reward.entity.ts declare no indexes. PointTransaction is written on every scored user action and read for ledger reconciliation and leaderboards, so it is simultaneously the highest-write and highest-scan table in the module. Leaderboard queries aggregating points per user, and any per-user transaction history, both scan the full table.

Specifications

Features:

• Point transaction queries by user and by time window are index-backed.
• Challenge participation lookups are indexed.

Tasks:

• Add @Index(['user', 'createdAt']) and @Index(['activityType']) to PointTransaction.
• Add @Index(['userId', 'challengeId']) to UserChallenge.
• Add indexes to Challenge and TierReward covering their active/lookup predicates.
• Generate the migration and verify the leaderboard aggregation plan uses the new indexes.

Impacted Files:

• src/gamification/entities/point-transaction.entity.ts
• src/gamification/entities/user-challenge.entity.ts
• src/gamification/entities/challenge.entity.ts
• src/gamification/entities/tier-reward.entity.ts

Acceptance Criteria

• Per-user point history queries use an index.
• Leaderboard aggregation no longer performs a full sequential scan.
• A migration accompanies the entity changes.

[laxjovial]

@laxjovial has applied to work on this issue as part of the Stellar Wave Program's 7th wave.

Please kindly assign to me. I’d love to work on this

ℹ️ Repo Maintainers: To accept this application, review their application or assign @laxjovial to this issue.

[drips-wave]

Congratulations, @laxjovial! 🎉 Your application was accepted by the repo's maintainers, and the issue is due on July 30, 2026.

🧑‍💻 @laxjovial: Please resolve the issue such that the repo's maintainers have enough time to review your contribution before the due date. You'll earn Points for completing the issue on-time, which will make you eligible for a share of the Stellar Wave Program's reward pool.

Warning

When opening a PR, please link it to this issue to ensure it gets tracked accurately. Points are awarded when this issue is marked as completed by the maintainer.

🤠 Repo maintainers: Please keep an eye on the contributor's progress and review their work before the due date. You can manage this issue, including adjusting its complexity and points, here.

🌊 Happy Wave 🌊