Building a Production-Ready Scheduled Push Notification System with NestJS Cron and Firebase

Source: Dev.to

From immediate delivery to precise scheduling

Building a reliable cron‑based notification scheduler that handles timezone complexities, database race conditions, and mobile app data contracts.

When your Firebase push notification server already handles mass delivery to hundreds of thousands of users via BullMQ queues, the next logical step is scheduled delivery. Sounds simple, right? Just add a cron job and a scheduling table.

What I discovered instead was a fascinating journey through timezone hell, TypeORM’s NULL comparison quirks, cursor pagination bugs, and the subtle differences between database schemas and mobile app data contracts. Here’s how I built a production‑ready scheduled notification system that now processes thousands of time‑based notifications daily with zero missed deliveries.

The Starting Point: A Working Mass Notification System

Before diving into scheduling, let me set the context. I already had a robust mass notification infrastructure:

// firebase.controller.ts - Existing mass notification endpoint
@Post('send-to-conditional-users')
async sendConditionalNotifications(
  @Query() query: SendMultiNotificationDto,
  @Body() body?: SendDataDto,
): Promise {
  const jobId = `conditional-${uuidv4()}`;

  const jobData: ConditionalNotificationParams = {
    ...query,
    jobId,
    chunkSize: 500,
    chunkDelay: 2000,
    data: body || {},
  };

  // Add to BullMQ queue - processed asynchronously
  await this.pushQueue.add('send-conditional-notification', jobData, {
    jobId,
    removeOnComplete: true,
    removeOnFail: false,
  });

  return CommonResponseDto.messageSendSuccess();
}

Key components

• BullMQ for asynchronous job processing
• Cursor‑based pagination for efficient database queries
• Chunked delivery (500 tokens per chunk, 2‑second delays)
• Automatic retry logic for failed messages
• Redis for job queue management

The challenge: How do I schedule these notifications to run at specific future times?

The Goal: Schedule Notifications Like a Pro

Requirements

• Store scheduled notifications in MySQL
• Use NestJS cron to check for pending notifications every minute
• Trigger the existing notification pipeline at the scheduled time
• Support all the same filtering options (gender, age, platform, etc.)
• Track execution status (pending, processing, completed)
• Handle timezone correctly (Korean Standard Time)

Envisioned Architecture

┌─────────────────┐
│  Schedule API   │ ──► INSERT into MySQL
│ (Create/Update) │
└─────────────────┘
        ↓
┌──────────────────────┐
│  MySQL Schedule DB   │
│  scheduled_send_date │
└──────────────────────┘
        ↓ Every minute
┌──────────────────────┐
│   NestJS Cron Job    │
│ @Cron(EVERY_MINUTE) │
└──────────────────────┘
        ↓ Found match?
┌──────────────────────┐
│    BullMQ Queue      │
│ (Existing Pipeline)  │
└──────────────────────┘
        ↓
┌──────────────────────┐
│  Firebase Worker     │
│  (Send to users)     │
└──────────────────────┘

Implementation: The Journey Through Edge Cases

Step 1: Create the Schedule Table

// push-notification-schedule.entity.ts
@Entity({ name: 'push_notification_schedule' })
@Index(['sent_yn', 'scheduled_send_date'])  // Critical for cron queries
@Index(['job_id'])
export class PushNotificationSchedule {
  @PrimaryGeneratedColumn({ type: 'int' })
  seq: number;

  @Column({ type: 'varchar', length: 200, nullable: true })
  job_id: string; // BullMQ job ID once queued

  @Column({ type: 'varchar', length: 200 })
  title: string;

  @Column({ type: 'text' })
  content: string;

  @Column({ type: 'datetime' })
  scheduled_send_date: Date; // When to send

  @Column({ type: 'datetime', precision: 6, nullable: true })
  actual_send_start_date: Date | null; // When actually sent

  @Column({ type: 'datetime', precision: 6, nullable: true })
  actual_send_end_date: Date | null;

  @Column({ type: 'int', default: 0 })
  total_send_count: number; // How many sent

  @Column({ type: 'tinyint', width: 1, default: 0 })
  sent_yn: number; // 0: pending, 1: completed

  // Filter fields (same as immediate API)
  @Column({ type: 'varchar', length: 1, nullable: true })
  push_onoff: string; // 'Y' = only subscribers

  @Column({ type: 'varchar', length: 1, nullable: true })
  marketing_onoff: string;

  @Column({ type: 'varchar', length: 20, nullable: true })
  topic: string; // FCM topic

  // Mobile app deep‑link data
  @Column({ type: 'varchar', length: 50, nullable: true })
  division: string; // e.g., 'bible'

  @Column({ type: 'int', nullable: true })
  version: number;

  @Column({ type: 'int', nullable: true })
  bible_code: number; // Database uses snake_case

  @Column({ type: 'int', nullable: true })
  chapter: number;

  @Column({ type: 'int', nullable: true })
  section: number;

  @Column({ type: 'varchar', length: 500, nullable: true })
  landing_url: string;

  @CreateDateColumn({ type: 'datetime' })
  regdate: Date;
}

Key design decisions

• sent_yn flag prevents duplicate execution.
• job_id tracks the BullMQ job once queued.
• actual_send_start_date / actual_send_end_date use microsecond precision for analytics.
• Fields such as bible_code support deep‑linking for the Bible app.

Step 2: The Naïve Cron Implementation (That Didn’t Work)

My first attempt seemed logical:

// scheduler.service.ts - First attempt (broken!)
@Cron(CronExpression.EVERY_MINUTE)
async handleScheduledPushNotifications() {
  const nowKST = moment.tz('Asia/Seoul').startOf('minute').toDate();

  // Find schedules within ±1 minute window
  const startWindow = moment(nowKST).subtract(1, 'minutes').toDate();
  const endWindow = moment(nowKST).add(1, 'minutes').toDate();

  const pendingSchedules = await this.scheduleRepository
    .createQueryBuilder('schedule')
    .where('schedule.sent_yn = :sentYn', { sentYn: 0 })
    .andWhere('schedule.job_id = :jobId', { jobId: null }) // ❌ BUG!
    .andWhere('schedule.scheduled_send_date BETWEEN :start AND :end', {
      start: startWindow,
      end: endWindow,
    })
    .getMany();

  for (const schedule of pendingSchedules) {
    await this.processSchedule(schedule);
  }
}

private async processSchedule(schedule: PushNotificationSchedule) {
  const jobId = `scheduled-${schedule.seq}-${uuidv4()}`;

  // Mark as processing
  await this.scheduleRepository.update(
    {
      seq: schedule.seq,
      sent_yn: 0,
      job_id: null, // ❌ BUG!
    },
    {
      job_id: jobId,
      sent_yn: 1,
    },
  );

  // Queue the job
  const jobData = { /* ...build payload from schedule... */ };
  await this.pushQueue.add('send-scheduled-notification', jobData, { jobId });
}

What went wrong

1. TypeORM’s NULL comparison trap
   The clause schedule.job_id = :jobId with jobId: null generates WHERE job_id = NULL, which is always false. The correct SQL is WHERE job_id IS NULL, and TypeORM requires .isNull() or a raw condition.

2. Same issue in the update call
   Providing { job_id: null } in the WHERE part translates to job_id = NULL, preventing the row from being matched and leaving the schedule untouched.

These bugs caused the cron job to never pick up pending schedules, leading to missed deliveries.