[BACKEND] - Implement a scheduled job system to send email reminders

[Gerson2102]

📘 Issue Description

Implement a scheduled job system to send email reminders to inactive users who haven't logged in or engaged with the platform for a specified period. This will help re-engage users and improve platform retention through automated reminder campaigns.

🔍 Steps

1. Extend User Model

   • Add lastLoginAt and lastActivityAt fields to User schema
   • Update authentication controller to track login activity

2. Create Notification Service

   • Create src/services/notification.service.ts
   • Implement methods to identify inactive users
   • Create reminder email templates and sending logic

3. Install CRON Package

   • Add node-cron package for scheduled job management
   • Configure job scheduling in application startup

4. Create Scheduled Job

   • Create src/jobs/userReminder.job.ts
   • Implement daily/weekly check for inactive users
   • Send different reminder types based on inactivity period

5. Extend Email Service

   • Add reminder email templates to EmailNotifier
   • Create personalized reminder messages
   • Support different reminder types (7-day, 30-day, etc.)

6. Environment Configuration

   • Add reminder settings to environment variables
   • Configure inactivity thresholds and reminder intervals

✅ Acceptance Criteria

• Activity Tracking: User login and activity timestamps are properly tracked
• Scheduled Jobs: CRON jobs run automatically to check for inactive users
• Email Reminders: Automated emails sent to inactive users with proper templates
• Configurable Thresholds: Inactivity periods and reminder intervals are configurable
• Job Logging: Proper logging for scheduled job execution and email sending
• Error Handling: Failed email attempts are handled gracefully with retry logic

Reminder Schedule

Inactivity Period	Reminder Type	Frequency
7 days	Gentle reminder	Once
14 days	Re-engagement	Once
30 days	Special offer/content	Once
60 days	Final reminder	Once

Database Schema Changes

-- Add to User table
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
ALTER TABLE users ADD COLUMN last_activity_at TIMESTAMP;

🌎 References

• Existing email service in src/utils/service/emailNotifier.ts
• Current user model in prisma/schema.prisma
• Authentication patterns in src/controllers/auth.controller.ts
• node-cron documentation

📜 Additional Notes

Implementation Approach

• Leverage existing ZohoMailer and EmailNotifier infrastructure
• Run CRON job daily at off-peak hours (e.g., 2 AM UTC)
• Track reminder history to avoid duplicate emails
• Support both development and production scheduling

Email Templates

7-day reminder: "We miss you! Come back and practice your English skills"
30-day reminder: "Special learning resources waiting for you"
60-day reminder: "Don't lose your progress - continue your English journey"

Job Configuration

// Daily at 2 AM UTC
cron.schedule('0 2 * * *', async () => {
  await UserReminderJob.checkInactiveUsers();
});

[yahia008]

👋 Hi! I'd love to be assigned to this issue.

I have solid experience working with Node.js, Prisma, and CRON jobs, and I've built similar email automation systems in the past. I'm comfortable with scheduling logic, activity tracking, and integrating email services for user engagement workflows.

I can confidently implement the required schema changes, add activity tracking, and set up a reliable reminder job system with configurable thresholds and proper logging. I’ll also ensure the solution scales well and handles errors gracefully.

Looking forward to contributing

[Cedarich]

Hi, I’m a Fullstack Software Engineer with expertise in backend development and automation. Here’s my approach to implementing the scheduled job system for email reminders:

User Model Extension:

• Add lastLoginAt and lastActivityAt fields to the User schema.
• Update the authentication controller to track user login and activity timestamps.

Notification Service:

• Develop src/services/notification.service.ts to identify inactive users and manage reminder templates and email logic.

CRON Job Setup:

• Install and configure the node-cron package to schedule daily checks for inactive users.
• Implement src/jobs/userReminder.job.ts to send personalized reminders based on inactivity thresholds.

Email Service Integration:

• Extend the existing EmailNotifier to support reminder templates for different inactivity periods.
• Include retry logic for failed email deliveries.

ETA:
24 hours for a complete solution with validations and error handling.

Recommended by OnlyDust for high-quality and dependable contributions.

[laryhills]

Hi, I’d like to apply for this issue!

Plan: I’ll extend the User schema to include lastLoginAt and lastActivityAt, and update the auth controller to track login activity. I’ll set up a NotificationService to identify inactive users, create scheduled CRON jobs with node-cron, and implement personalized reminder emails using the existing EmailNotifier. Reminder logic will be based on configurable inactivity thresholds (7, 14, 30, 60 days) and include graceful error handling with logging and retries.

Why me: With 5+ years of experience in Node.js and email automation, I’ve built similar CRON-based engagement systems and have strong familiarity with NestJS, Prisma, and transactional email patterns. I’m detail-oriented about avoiding duplicate sends and ensuring scalability in scheduling logic.

Excited to help boost retention through this feature! 🚀

[Josue19-08]

🛡 Custom Development Environment with Supabase

@Cedarich
Dear contributor,

As part of our recent updates, the project database has been migrated to Supabase. For security reasons, we do not store real credentials in development. Each developer must create their own Supabase project and configure their local environment accordingly.

Setup Steps

1. Clone or fork this repository.

2. Create a free project on Supabase:
   https://app.supabase.com

3. Locate the Prisma schema in:

   prisma/schema.prisma

4. Apply Prisma migrations to create the required tables in your Supabase database:

   npx prisma db push

5. Add your Supabase credentials to a .env.local file at the project root, following the structure in .env.example:

   DATABASE_URL="postgresql://postgres:your_password@db.your-project.supabase.co:5432/your_database?pgbouncer=true"
   DIRECT_URL="postgresql://postgres:your_password@db.your-project.supabase.co:5432/your_database"

6. Install dependencies and start the development server:

   npm install
   npm run dev

---

💡 Important: Always update your .env.local using the provided .env.example as reference and avoid committing your real credentials to version control.

---

✅ Let us know if you need any help setting up Supabase for your local environment.

[Gerson2102]

Any updates @laryhills ?

[laryhills]

@Gerson2102
So sorry for the delay, expect a draft PR soon