Forem: Sangwoo Lee

Solving the 100M-Row Problem: A Summary Table Pattern for High-Volume Push Notification Logs

Sangwoo Lee — Thu, 16 Apr 2026 06:38:38 +0000

How I redesigned a push notification logging system that had accumulated 100 million rows — without a single minute of downtime — using a summary table pattern, idempotent aggregation, and safe batch deletion.

When your push notification service sends 500,000 messages per job and those logs never get cleaned up, you end up with 100 million rows in a single MSSQL table. Every statistics query becomes a full-scan nightmare. Storage costs spiral. And your dashboards get slower every week.

Here's the complete story of how I solved it — including the architecture decisions, the idempotency design, the MSSQL deadlock pitfalls, and the BullMQ integration that tied it all together.

The Problem: 100 Million Rows and Growing

What Was Happening

Each time the push notification API was called, up to 500,000 FCM messages were sent over ~1.5 hours via a BullMQ worker. Every single message was logged individually to [c3tv].[c3tv].[push_notification_log] in MSSQL:

push_notification_log (MSSQL)
├── id           BIGINT (PK)
├── job_id       VARCHAR(200)   ← Same value for all 500k rows in one send
├── member_seq   INT
├── push_token   VARCHAR(500)
├── is_success   BIT
├── sent_at      DATETIME2
└── ...          (20+ more columns)

After multiple sends per day, the table grew to 100 million rows.

The Real Cost

Every statistics API call — "how many succeeded in this send?" — did this:

-- This was running against 100M rows
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) AS success,
  SUM(CASE WHEN is_success = 0 THEN 1 ELSE 0 END) AS failure
FROM push_notification_log
WHERE job_id = 'conditional-abc-123'

Even with an index on job_id, scanning 500,000 rows per query on a 100M-row table created significant I/O pressure, especially when multiple admins were checking stats simultaneously.

The numbers:

Storage: ~80 GB for raw logs (growing 500K rows x 20+ columns per send)
Stats query: 1.2 to 3.8 seconds average response time
Cost: Disproportionate MSSQL DTU consumption ### What the API Consumers Expected

The admin dashboard called GET /message/conditional-sends/:jobId/stats and expected:

{
  "total": 498234,
  "success": 489102,
  "failure": 9132,
  "successRate": 98.17,
  "deliveryRate": 98.17
}

This had to work even after the raw logs were deleted.

The Solution: Summary Table Pattern + Batch Deletion

Architecture Overview

Before (problematic):

FCM Send (500K msgs)
  --> push_notification_log (MSSQL)
        500,000 rows per send x N sends = 100M+
        COUNT(*) on every stats call (expensive, forever)
  --> Stats API Response

After (optimized):

FCM Send (500K msgs)
  --> push_notification_log (MSSQL)
        500,000 rows (temporary)
        Aggregated immediately on job complete
  --> push_notification_log_summary (MySQL)
        1 row per send job
        job_id        = 'conditional-abc-123'
        total_count   = 498234
        success_count = 489102
        failure_count = 9132
        first_sent_at = 2026-04-16 09:00:00
        last_sent_at  = 2026-04-16 10:31:22
        logs_deleted_at   = null  (becomes datetime after deletion)
        logs_deleted_count = 0    (becomes 498234 after deletion)
        Batch delete raw logs (runs immediately after aggregation)
  --> push_notification_log (MSSQL)
        0 rows remaining for this job_id
        Stats API reads from summary (1-row lookup, instant)
  --> Stats API Response

Key Design Constraints

Constraint	Requirement
Data integrity	`success_count + failure_count = total_count` always
Idempotency	Re-running aggregation must be safe (no duplicate rows)
Batch deletion	No table locks, no deadlocks, no CPU spikes
API continuity	Stats APIs work before AND after log deletion
Failure recovery	Resume from where it left off if server crashes mid-delete

Implementation Deep Dive

Step 1: The Summary Table Entity

// src/entities/push-notification-log-summary.entity.ts
@Entity({ name: 'push_notification_log_summary' })
@Index(['created_at'])
@Index(['first_sent_at'])
export class PushNotificationLogSummary {
  @PrimaryColumn({ type: 'varchar', length: 200, name: 'job_id' })
  job_id: string;

  // Aggregated send counts
  @Column({ type: 'int', name: 'total_count', default: 0 })
  total_count: number;          // COUNT(*) WHERE job_id = ?

  @Column({ type: 'int', name: 'success_count', default: 0 })
  success_count: number;        // COUNT(*) WHERE is_success = 1

  @Column({ type: 'int', name: 'failure_count', default: 0 })
  failure_count: number;        // COUNT(*) WHERE is_success = 0
  // INVARIANT: success_count + failure_count = total_count (always)

  // Timestamps (preserved after log deletion)
  @Column({ type: 'datetime', precision: 6, nullable: true })
  scheduled_send_date: Date | null;  // For scheduled-* jobs only

  @Column({ type: 'datetime', precision: 6, nullable: true })
  first_sent_at: Date | null;   // MIN(sent_at) from raw logs

  @Column({ type: 'datetime', precision: 6, nullable: true })
  last_sent_at: Date | null;    // MAX(sent_at) from raw logs

  // Deletion tracking
  @Column({ type: 'datetime', nullable: true })
  logs_deleted_at: Date | null; // null = not yet deleted

  @Column({ type: 'int', default: 0 })
  logs_deleted_count: number;   // Must equal total_count when done

  @CreateDateColumn({ type: 'datetime', name: 'created_at' })
  created_at: Date;
}

Why logs_deleted_at = null is a critical sentinel:

null means raw logs still exist in MSSQL (cleanup in progress or pending)
A datetime value means logs are confirmed deleted, and logs_deleted_count matches total_count This single column drives the idempotency logic across the entire system.

Step 2: Triggering Aggregation on Job Completion

I used BullMQ's Worker.on('completed') event with a fire-and-forget pattern:

// src/routes/firebase/firebase.process.ts
private registerCompletedHandler() {
  this.worker.on('completed', async (job: Job) => {
    const { jobId, scheduleSeq } = job.data;

    // STEP 1: Fire-and-forget aggregation + deletion
    // Runs independently of Redis/DB progress updates.
    // If this fails, the job is already 'completed' in BullMQ.
    // Re-runnable via admin API without re-sending notifications.
    if (jobId && !String(jobId).startsWith('backfill-')) {
      this.firebaseService
        .aggregateAndCleanupPushLogs(jobId)
        .then(() => this.logger.log(`[Completed] Cleanup done: ${jobId}`))
        .catch(err => this.logger.error(`[Completed] Cleanup failed: ${jobId}`, err));
    }

    // STEP 2: Update Redis progress and DB timestamps
    // Independent of STEP 1 — failure here does not block cleanup.
    try {
      // ... Redis progress update, actual_send_end_date recording
    } catch (error) {
      this.logger.error(`[Completed] Progress update failed:`, error);
    }
  });
}

Why fire-and-forget?

The completed event fires after BullMQ marks the job as done. If aggregation blocks this handler and throws, BullMQ might retry the job — causing duplicate push notifications. Fire-and-forget ensures:

Job completion is recorded immediately
Aggregation runs independently
Aggregation failure is logged but does not re-trigger notification sending ### Step 3: The Core Aggregation Function

This is the heart of the solution. It must be idempotent — safe to call multiple times:

// src/routes/firebase/firebase.service.ts
async aggregateAndCleanupPushLogs(jobId: string): Promise<void> {
  const logger = new Logger('AggregateAndCleanup');

  // Batch deletion constants
  const BATCH_SIZE       = 4_500;  // Below MSSQL lock escalation threshold (5,000)
  const BATCH_DELAY_MS   = 200;    // Yield to concurrent INSERT operations
  const MAX_ITERATIONS   = 500;    // Safety cap: 4,500 x 500 = 2.25M rows max
  const DEADLOCK_RETRIES = 3;

  // ============================================================
  // STAGE 1: Idempotency check
  // ============================================================
  const existing = await this.pushNotificationLogSummaryRepository.findOne({
    where: { job_id: jobId },
  });

  if (existing?.logs_deleted_at) {
    // Both aggregation AND deletion already completed -> skip entirely
    logger.log(`Already fully processed: ${jobId}`);
    return;
  }

  // ============================================================
  // STAGE 2: Aggregation (only if summary does not exist yet)
  // ============================================================
  if (!existing) {
    const aggregateSQL = `
      SELECT
        COUNT(*)                                          AS total_count,
        SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END)  AS success_count,
        SUM(CASE WHEN is_success = 0 THEN 1 ELSE 0 END)  AS failure_count,
        MIN(sent_at)                                      AS first_sent_at,
        MAX(sent_at)                                      AS last_sent_at,
        MIN(created_at)                                   AS log_created_at
      FROM [c3tv].[c3tv].[push_notification_log]
        WITH (INDEX(idx_push_log_job_id))
      WHERE job_id = @0
    `;

    const rows = await this.pushNotificationLog.query(aggregateSQL, [jobId]);
    const row = rows?.[0];

    if (!row || Number(row.total_count) === 0) {
      logger.warn(`No logs found for aggregation: ${jobId}`);
      return;
    }

    try {
      // Use INSERT (not save/upsert) to detect concurrent duplicates
      await this.pushNotificationLogSummaryRepository.insert({
        job_id:             jobId,
        total_count:        Number(row.total_count),
        success_count:      Number(row.success_count),
        failure_count:      Number(row.failure_count),
        first_sent_at:      row.first_sent_at ? new Date(row.first_sent_at) : null,
        last_sent_at:       row.last_sent_at  ? new Date(row.last_sent_at)  : null,
        logs_deleted_at:    null,   // Sentinel: deletion not yet done
        logs_deleted_count: 0,
        created_at:         row.log_created_at
                              ? new Date(row.log_created_at)
                              : new Date(),
      });
      logger.log(`Summary saved: ${jobId} -> total: ${row.total_count}`);

    } catch (insertError: any) {
      if (insertError.code === 'ER_DUP_ENTRY' || insertError.errno === 1062) {
        // Blue/Green concurrent execution hit the same job.
        // The other container already inserted — safe to continue to deletion.
        logger.warn(`Concurrent insert detected, proceeding to deletion: ${jobId}`);
      } else {
        throw insertError;
      }
    }
  } else {
    // Summary exists but logs_deleted_at is null.
    // Previous run crashed during deletion -> resume from here.
    logger.log(`Resuming deletion (aggregation already done): ${jobId}`);
  }

  // ============================================================
  // STAGE 3: Batch deletion with deadlock handling
  // ============================================================
  const deleteSQL = `
    SET NOCOUNT ON;
    DELETE TOP(${BATCH_SIZE})
    FROM [c3tv].[c3tv].[push_notification_log] WITH (READPAST)
    WHERE job_id = @0;
    SELECT @@ROWCOUNT AS affected;
  `;

  let totalDeleted = 0;
  let iteration = 0;

  while (iteration < MAX_ITERATIONS) {
    iteration++;

    const affected = await this.executeDeleteBatchWithRetry(
      deleteSQL,
      jobId,
      DEADLOCK_RETRIES,
      logger,
    );

    totalDeleted += affected;
    if (affected === 0) break; // All rows deleted

    if (iteration % 10 === 0) {
      logger.log(`Deletion progress: ${totalDeleted.toLocaleString()} rows`);
    }

    await delay(BATCH_DELAY_MS); // Yield to concurrent writes
  }

  // ============================================================
  // STAGE 4: Record deletion completion
  // Use SQL expression for atomic accumulation (handles retries)
  // ============================================================
  await this.pushNotificationLogSummaryRepository
    .createQueryBuilder()
    .update(PushNotificationLogSummary)
    .set({
      logs_deleted_at:    getKSTDate(),
      // If previous attempt deleted N rows, accumulate correctly
      logs_deleted_count: () => `logs_deleted_count + ${totalDeleted}`,
    })
    .where('job_id = :jobId', { jobId })
    .execute();

  logger.log(`Cleanup complete: ${jobId}, deleted: ${totalDeleted.toLocaleString()} rows`);
}

Step 4: Deadlock-Safe Batch Deletion

MSSQL deadlocks are a real concern when deleting large volumes of rows while other transactions are writing. Here's the retry logic:

private async executeDeleteBatchWithRetry(
  sql: string,
  jobId: string,
  maxRetries: number,
  logger: Logger,
): Promise<number> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const result = await this.pushNotificationLog.query(sql, [jobId]);
      return Number(result?.[0]?.affected ?? 0);
    } catch (err: any) {
      const isDeadlock =
        err.number === 1205 ||
        err.code === 'EDEADLOCK' ||
        /deadlock/i.test(err.message ?? '');

      if (isDeadlock && attempt < maxRetries) {
        // Exponential backoff: 500ms, 1000ms, 2000ms
        const backoffMs = 500 * Math.pow(2, attempt - 1);
        logger.warn(
          `Deadlock on attempt ${attempt}/${maxRetries}, retrying in ${backoffMs}ms`
        );
        await delay(backoffMs);
        continue;
      }
      throw err;
    }
  }
  return 0;
}

Why BATCH_SIZE = 4,500?

MSSQL has a lock escalation threshold: when a single transaction holds 5,000 or more row locks, it automatically escalates to a table lock. This blocks all concurrent reads and writes.

Lock escalation threshold : 5,000 row locks
Our batch size            : 4,500 rows
Safety margin             : 10% below threshold

Result: Each DELETE batch holds at most 4,500 row locks
        -> No table lock escalation
        -> Concurrent selects/inserts continue unblocked

Why WITH (READPAST) instead of WITH (NOLOCK)?

Hint	Behavior	Risk
`NOLOCK`	Reads uncommitted data	Dirty reads (wrong counts)
`READPAST`	Skips locked rows, processes them next batch	Slightly slower, zero dirty reads
(none)	Waits for locks to release	Deadlock risk, blocking

READPAST is the right choice: it does not wait for locked rows (avoiding deadlocks) and does not read dirty data (avoiding incorrect deletion).

Step 5: All Statistics APIs Now Read from Summary

Before this change, every stats API queried the 100M-row table. Now they all hit the summary table first:

// getDeliveryStats() — O(1) lookup instead of O(N) scan
async getDeliveryStats(jobId: string): Promise<FcmErrorStats> {
  // Primary: MySQL summary table (1-row lookup, instant)
  const summary = await this.pushNotificationLogSummaryRepository.findOne({
    where: { job_id: jobId },
  });

  if (summary) {
    const { total_count: total, success_count: success, failure_count: failure } = summary;
    const successRate = total > 0
      ? parseFloat(((success / total) * 100).toFixed(2))
      : 0;

    return {
      total,
      success,
      invalidToken: failure,
      temporary: 0,
      quota: 0,
      other: 0,
      deliveryRate: successRate,
      successRate,
    };
  }

  // Fallback: MSSQL query (only during the brief ~5s aggregation window)
  const sendStats = await this.pushNotificationLog
    .createQueryBuilder('log')
    .select([
      'COUNT(*) AS total',
      'SUM(CASE WHEN log.is_success = 1 THEN 1 ELSE 0 END) AS success',
    ])
    .where('log.job_id = :jobId', { jobId })
    .getRawOne();

  // ... return computed stats
}

The same dual-source pattern applies to getClickStats(), getInvalidTokens(), and getScheduleDetail().

The Idempotency State Machine

This is the most important design element. The system can be interrupted at any point and safely resumed:

State 1: No summary row
  -> Aggregate from MSSQL
  -> INSERT summary (logs_deleted_at = null)
  -> Proceed to deletion

State 2: Summary exists, logs_deleted_at = null
  -> Skip aggregation (already done)
  -> Resume deletion from current state
  -> logs_deleted_count accumulates via SQL expression

State 3: Summary exists, logs_deleted_at = datetime
  -> Fully complete -> return immediately

Crash scenario:
  Server crashes during deletion at iteration 47 (211,500 rows deleted)
    |
    v
  Restart -> aggregateAndCleanupPushLogs() called again
    |
    v
  State 2 detected (summary exists, logs_deleted_at = null)
    |
    v
  Deletion resumes — remaining rows still exist in MSSQL
    |
    v
  logs_deleted_count: SQL expression `logs_deleted_count + 288500`
    |
    v
  Final: logs_deleted_count = 211500 + 288500 = 500000 (correct)

The SQL expression for accumulation is key:

// Correct: accumulates safely on retry
logs_deleted_count: () => `logs_deleted_count + ${totalDeleted}`

// Wrong: would reset the count on every retry
// logs_deleted_count: totalDeleted

Backfilling Historical Data

With 100 million existing rows accumulated before this feature shipped, I needed to retroactively build summaries for all past jobs. I built a dedicated backfill system:

POST /message/admin/backfill-log-summary
  ?batchSize=100
  &batchDelayMs=1000
  &jobIdType=all
  &dryRun=false

Response:
{
  "backfillJobId": "backfill-abc-123",
  "progressUrl": "/message/admin/backfill-log-summary/backfill-abc-123/progress"
}

Backfill flow:

MSSQL: 100M rows, ~200 distinct job_ids
  |
  | SELECT DISTINCT job_id (paginated, cursor-based)
  | TOP 100 WHERE job_id > @cursor ORDER BY job_id ASC
  v
For each job_id:
  1. Aggregate: COUNT(*), SUM(is_success=1), MIN/MAX(sent_at)
  2. Get time info from push_notification_schedule or push_notification_conditional
  3. INSERT into push_notification_log_summary (logs_deleted_at = null)
     -> Skip if already exists (idempotent)
  4. Original logs NOT deleted (backfill is read-only)

Progress tracked in Redis (TTL 24h):
{
  "status": "in_progress",
  "totalJobIds": 200,
  "processedJobIds": 87,
  "insertedSummaries": 84,
  "skippedJobIds": 3,
  "progressPercentage": 43,
  "estimatedEndAt": "2026-04-16T11:30:00Z"
}

Cursor-based pagination instead of OFFSET:

-- OFFSET gets slower as offset increases
SELECT DISTINCT job_id FROM push_notification_log
ORDER BY job_id
OFFSET 5000 ROWS FETCH NEXT 100 ROWS ONLY

-- Cursor: constant performance regardless of position
SELECT TOP 100 job_id, COUNT(*) AS cnt
FROM push_notification_log WITH (INDEX(idx_push_log_job_id))
WHERE job_id > 'conditional-abc-123'  -- last processed cursor
GROUP BY job_id
ORDER BY job_id ASC

Timeline: What Happens After a Send

T+0:00    500,000 messages sent via FCM
          All 500,000 rows written to push_notification_log (MSSQL)

T+0:00    Promise.all(logSavePromises) resolves
          sendConditionalNotifications() returns
          BullMQ marks job as 'completed'

T+0:01    Worker.on('completed') fires
          aggregateAndCleanupPushLogs() called (fire-and-forget)

T+0:05    Aggregation query completes (~4 seconds for 500K rows with index)
          push_notification_log_summary row inserted:
            total_count   = 498234
            success_count = 489102
            failure_count = 9132
            logs_deleted_at = null

T+0:10    Batch deletion begins
          DELETE TOP(4500) ... WITH (READPAST)

T+4:10    ~56 batches x 4,500 rows x 200ms delay = ~4 minutes
          All 498,234 rows deleted

T+4:10    Summary updated:
            logs_deleted_at    = 2026-04-16 09:04:10
            logs_deleted_count = 498234

Stats API at any point after T+0:05:
          -> 1-row lookup from push_notification_log_summary (MySQL)
          -> Response in under 5ms

Performance Results

Before vs After

Metric	Before	After	Improvement
`getDeliveryStats` response time	1.2 to 3.8s	under 5ms	760x faster
MSSQL table size	100M rows (~80 GB)	under 1M rows (~0.8 GB)	99% reduction
Storage cost	High (MSSQL DTU intensive)	Minimal (MySQL summary)	~90% reduction
Stats query complexity	O(N) per call	O(1) always	Constant time
Deletion impact on production	N/A	Near-zero (READPAST)	No blocking

Resource Usage During Cleanup

Batch deletion run (500K rows, 4,500 batch size):

CPU (MSSQL):
  Aggregation query : peak 45% for ~4 seconds
  Batch deletion    : sustained 15-20% for ~4 minutes
  (No CPU spike — smooth, predictable load)

MSSQL Locks:
  Max concurrent row locks : 4,500 (below 5,000 escalation threshold)
  Table locks              : ZERO (READPAST + batch size design)

Concurrent write impact:
  Ongoing push sends : ZERO slowdown (READPAST skips their rows)

Gotchas and Lessons Learned

1. MSSQL Lock Escalation Is a Silent Killer

Initial attempt: DELETE TOP(10,000) seemed reasonable.

What happened: At 5,000+ row locks, MSSQL escalated to a table lock. All concurrent INSERT operations from ongoing push sends were blocked. The notification worker stalled.

Fix: BATCH_SIZE = 4,500 keeps every delete transaction below the escalation threshold.

-- Lock escalation behavior:
-- Row locks 1 to 4,999 : table is fine
-- Row locks 5,000+     : table lock acquired, everything blocks

-- Our fix:
DELETE TOP(4500) FROM push_notification_log WITH (READPAST)
WHERE job_id = @0
-- Maximum 4,500 row locks per transaction -> no escalation

2. `@@ROWCOUNT` Is the Only Reliable Termination Signal

Naive approach: Count rows before deletion and loop until expected count is deleted.

Problem: Concurrent INSERTs from ongoing send jobs could shift the count. The loop might run forever.

Fix: Trust @@ROWCOUNT — the actual rows affected by the last DELETE:

DELETE TOP(4500) FROM push_notification_log WITH (READPAST) WHERE job_id = @0;
SELECT @@ROWCOUNT AS affected;
-- When affected = 0, we are done. No counting, no guessing.

3. `insert()` Over `save()` for Idempotency

Problem with save(): TypeORM's save() behaves like an upsert and may silently overwrite correctly aggregated values on retry.

Fix: Use insert() which throws on duplicate PK:

// Throws ER_DUP_ENTRY if already exists -> catch and continue to deletion
await this.pushNotificationLogSummaryRepository.insert({ job_id: jobId, ... });

Combined with catching ER_DUP_ENTRY, this gives safe concurrent execution between Blue and Green containers.

4. SQL Expression for Retry-Safe Accumulation

// Wrong: resets the count on retry
logs_deleted_count: 300000

// Correct: accumulates across retries
// Previous run: 200,000 + this run: 300,000 = correct total: 500,000
logs_deleted_count: () => `logs_deleted_count + ${totalDeleted}`

5. Keep `first_sent_at` in Summary for Click Rate Calculations

Click-through rate calculations need to know when the first notification was sent. After raw logs are deleted, this information was gone.

Fix: Include MIN(sent_at) in the aggregation and store it in the summary:

SELECT
  MIN(sent_at) AS first_sent_at,  -- Preserved even after log deletion
  MAX(sent_at) AS last_sent_at,
  ...
FROM push_notification_log WHERE job_id = @0

Now getClickStats() can compute "average time from send to click" even months later.

6. The `backfill-` Job Prefix Guard

With fire-and-forget in Worker.on('completed'), every completing job triggers aggregateAndCleanupPushLogs(). Backfill jobs themselves complete and have a jobId field — but they are not push notification jobs.

// Without the guard: aggregateAndCleanupPushLogs('backfill-xyz') queries
// MSSQL for a job_id that doesn't exist there. Wasteful and noisy.

// With the guard:
if (jobId && !String(jobId).startsWith('backfill-')) {
  this.firebaseService.aggregateAndCleanupPushLogs(jobId)...
}

API Design After the Change

Statistics APIs — Now Dual-Source

All stats APIs follow the same pattern:

GET /message/conditional-sends/:jobId/stats

Decision tree:
  1. Does push_notification_log_summary have this job_id?
     YES: Return summary data (O(1), instant)
     NO:  Fall back to MSSQL query (O(N), only during brief aggregation window)

Window where fallback is needed:
  T+0:00  Job completes, logs exist in MSSQL
  T+0:05  Aggregation done, summary row exists  <- Fallback no longer needed
  T+4:10  Raw logs deleted
  T+inf   Summary row always available

New Monitoring Endpoint

// GET /message/conditional-sends/:jobId/log-summary
// Lets admins see exactly where each job's cleanup stands

// Response:
{
  "job_id": "conditional-abc-123",
  "total_count": 498234,
  "success_count": 489102,
  "failure_count": 9132,
  "first_sent_at": "2026-04-16T09:00:00.000Z",
  "last_sent_at": "2026-04-16T10:31:22.000Z",
  "logs_deleted_at": "2026-04-16T10:35:40.000Z",
  "logs_deleted_count": 498234,
  "created_at": "2026-04-16T09:00:03.500Z"
}

When to Use This Pattern

This Summary Table Pattern with Batch Deletion is appropriate when:

Use it when:

You have append-only logs where historical granularity matters at write time but not read time
Log volume is order-of-magnitude larger than the number of logical events (1 job = 500K rows)
Statistics queries are read-heavy (admins checking stats repeatedly)
You can tolerate a brief aggregation window (~5 seconds) between log creation and summary availability
You need provable data integrity (success + failure = total)
Avoid it when:
You need to query individual log rows post-cleanup (e.g., "which specific users failed?")
Logs have regulatory retention requirements

- The ratio of unique events to total rows is close to 1:1

Conclusion

Solving the 100-million-row problem required rethinking the data lifecycle from the ground up:

Write raw logs during the send (necessary for real-time diagnostics)
Aggregate immediately on job completion (fire-and-forget, idempotent)
Delete in safe batches respecting MSSQL's lock escalation limits
Serve all stats from the 1-row summary (forever, even after deletion) The key engineering decisions:

insert() over save() for race-condition-safe idempotency
BATCH_SIZE = 4,500 to stay below MSSQL's 5,000-lock escalation threshold
WITH (READPAST) to avoid deadlocks against concurrent write transactions
@@ROWCOUNT as termination signal for reliable loop exit
SQL expression accumulation (logs_deleted_count + N) for retry-safe bookkeeping
logs_deleted_at sentinel to drive the three-state idempotency machine The result: stats queries went from 1 to 4 seconds down to sub-5ms, MSSQL storage dropped by over 90%, and the system now handles 100K+ notifications daily without accumulating unbounded data.

Key Takeaways

Summary table pattern converts O(N) aggregation queries to O(1) lookups permanently
Lock escalation thresholds are MSSQL-specific — know your database's limits before batch-deleting
READPAST is the right hint for high-concurrency deletion (not NOLOCK)
Design for idempotency first — retry-safe systems are operationally resilient
Store timestamps in summary rows — you will regret losing them after deletion
Backfill is a feature, not an afterthought — plan for historical data migration from day one
Fire-and-forget with proper error handling gives you cleanup guarantees without coupling
Choose simplicity over features until you outgrow it

Solving MSSQL's 2100 Parameter Limit: From IN Clause to Cursor-Based Pagination

Sangwoo Lee — Mon, 16 Feb 2026 17:22:00 +0000

How we fixed 'The incoming request has too many parameters' error in a NestJS push notification service by switching from IN clause batch queries to cursor-based pagination

The Problem: A Mysterious MSSQL Error

While working on a Bible reading reminder feature in our NestJS application, we encountered a cryptic error:

Error: The incoming request has too many parameters. 
The server supports a maximum of 2100 parameters.
Error Code: EREQUEST

This error occurred when trying to fetch push tokens for inactive users. Interestingly, the exact same pagination constants worked perfectly in another service (firebase.service.ts) but failed in our new service (bible-read.service.ts).

// These constants were identical in both services
private readonly DB_FETCH_PAGE_SIZE = 10000;
private readonly PAGE_FETCH_DELAY_MS = 100;

So why did one service work while the other crashed? 🤔

Root Cause Analysis

The culprit was different pagination strategies:

❌ bible-read.service.ts (IN Clause Batch Query)

// Step 1: Get inactive user IDs from mobile DB
const inactiveMembers = await this.mobileDataSource.query(query);
const memberSeqs = inactiveMembers.map(m => m.member_seq);

// Step 2: Fetch push tokens in batches
let page = 0;
while (page * pageSize < memberSeqs.length) {
  // Slice array in JavaScript memory
  const chunk = memberSeqs.slice(page * pageSize, (page + 1) * pageSize);

  // ❌ IN clause with 10,000 parameters!
  const members = await MemberRepository
    .where('member.seq IN (:...seqs)', { seqs: chunk })
    .getMany();

  page++;
}

Problem: The IN clause creates one parameter per value. With a chunk size of 10,000, we're sending 10,000 parameters to MSSQL, far exceeding its 2,100 parameter limit.

✅ firebase.service.ts (Cursor-Based Pagination)

let lastSeq = 0;

while (true) {
  // ✅ Only 3 parameters: lastSeq, pushOnoff, limit
  const members = await Repository
    .where('member.seq > :lastSeq', { lastSeq })
    .andWhere('member.push_onoff = :pushOnoff', { pushOnoff: 'Y' })
    .orderBy('member.seq', 'ASC')
    .take(10000)
    .getMany();

  if (members.length === 0) break;

  lastSeq = members[members.length - 1].seq; // Next cursor
}

Why it works: Cursor-based pagination uses a single cursor parameter (lastSeq) instead of listing all IDs. This keeps the parameter count minimal regardless of page size.

Pagination Strategies Compared

Strategy	Parameters	MSSQL Safe?	Performance
IN Clause Batch	10,000	❌ Exceeds limit	Heavy IN parsing
Cursor-Based	3	✅ Safe	Efficient index scan
OFFSET/LIMIT	3	✅ Safe	Skips earlier rows

The Solution: Migrating to Cursor-Based Pagination

Here's the refactored code for bible-read.service.ts:

private async getInactiveUsersWithTokens(
  inactiveDays: number,
): Promise<Array> {

  // Step 1: Get inactive users from mobile DB
  const inactiveMembers = await this.mobileDataSource.query(query, [inactiveDays]);

  if (inactiveMembers.length === 0) return [];

  // Step 2: Build a lookup map for fast access
  const inactiveMemberMap = new Map();
  let minSeq = Infinity;
  let maxSeq = -Infinity;

  for (const member of inactiveMembers) {
    inactiveMemberMap.set(member.member_seq, member);
    if (member.member_seq < minSeq) minSeq = member.member_seq;
    if (member.member_seq > maxSeq) maxSeq = member.member_seq;
  }

  this.logger.log(`Member seq range: ${minSeq} ~ ${maxSeq}`);

  // Step 3: Cursor-based pagination to fetch push tokens
  const results = new Map();
  let lastSeq = minSeq - 1;
  let pageCount = 0;

  while (true) {
    pageCount++;

    // ✅ Cursor-based query with only 3 parameters
    const members = await this.memberRepository
      .createQueryBuilder('member')
      .select(['member.seq', 'member.push_token'])
      .where('member.seq > :lastSeq', { lastSeq })
      .andWhere('member.seq <= :maxSeq', { maxSeq })
      .andWhere('member.push_onoff = :pushOnoff', { pushOnoff: 'Y' })
      .andWhere('member.push_token IS NOT NULL')
      .andWhere("member.push_token != ''")
      .orderBy('member.seq', 'ASC')
      .take(this.DB_FETCH_PAGE_SIZE)
      .getMany();

    if (members.length === 0) break;

    this.logger.log(
      `Page ${pageCount}: ${members.length} members fetched ` +
      `(cursor: ${lastSeq} → ${members[members.length - 1].seq})`
    );

    // Filter and add only inactive users
    let addedCount = 0;
    for (const member of members) {
      const inactiveMember = inactiveMemberMap.get(member.seq);

      if (inactiveMember && member.push_token?.trim()) {
        results.set(member.push_token, {
          member_seq: member.seq,
          last_read_date: inactiveMember.last_read_date,
          // ... other fields
        });
        addedCount++;
      }
    }

    this.logger.log(
      `Page ${pageCount}: ${addedCount} inactive users added ` +
      `(total: ${results.size})`
    );

    // Update cursor
    lastSeq = members[members.length - 1].seq;

    // Stop if we've reached the end of the range
    if (lastSeq >= maxSeq) break;

    // Small delay to reduce DB load
    await delay(this.PAGE_FETCH_DELAY_MS);
  }

  return Array.from(results.entries()).map(([push_token, data]) => ({
    push_token,
    ...data,
  }));
}

Key Improvements

1. Parameter Count: 10,000 → 3

// Before: 10,000 parameters
WHERE member.seq IN (1, 2, 3, ..., 10000)

// After: 3 parameters
WHERE member.seq > :lastSeq 
  AND member.seq <= :maxSeq 
  AND member.push_onoff = :pushOnoff

2. Consistent Pattern Across Services

Both firebase.service.ts and bible-read.service.ts now use the same cursor-based approach, improving maintainability.

3. Index-Friendly Queries

Cursor-based pagination leverages indexes more efficiently than IN clauses with thousands of values.

Performance Considerations

When Cursor-Based Shines ✅

Users are densely distributed (e.g., 4,000 inactive users in seq range 12,000-15,000)
Indexes on seq column exist
Consistent query pattern across the codebase

Potential Drawback ⚠️

If inactive users are sparsely distributed (e.g., 100 users scattered across seq 1-1,000,000), the cursor might scan many unnecessary rows.

Optimization:

-- Add a compound index
CREATE INDEX IX_member_seq_push 
ON member(seq, push_onoff, push_token);

Results

[getInactiveUsersWithTokens] Inactive members: 5,234 found
[getInactiveUsersWithTokens] Member seq range: 10001 ~ 950123
[getInactiveUsersWithTokens] Page 1: 10000 members fetched (cursor: 10000 → 20500)
[getInactiveUsersWithTokens] Page 1: 423 inactive users added (total: 423)
[getInactiveUsersWithTokens] Page 2: 10000 members fetched (cursor: 20500 → 31200)
[getInactiveUsersWithTokens] Page 2: 389 inactive users added (total: 812)
...
[getInactiveUsersWithTokens] ✅ Final targets: 4,892 users

No more parameter limit errors! 🎉

Lessons Learned

Different pagination strategies have different parameter requirements - What works for one query pattern might not work for another
Cursor-based pagination is more scalable when dealing with large datasets
Understanding database limitations (like MSSQL's 2,100 parameter cap) is crucial for production systems
Code consistency matters - Using the same pattern across services reduces debugging time

Alternative Solutions

If cursor-based pagination isn't suitable for your use case, consider:

1. Chunked IN Clauses

const CHUNK_SIZE = 1500; // Safe margin below 2,100

for (let i = 0; i < memberSeqs.length; i += CHUNK_SIZE) {
  const chunk = memberSeqs.slice(i, i + CHUNK_SIZE);
  // Process chunk...
}

2. Temporary Table + JOIN

CREATE TABLE #TempSeqs (member_seq INT PRIMARY KEY);

INSERT INTO #TempSeqs VALUES (1), (2), (3), ...;

SELECT m.* 
FROM member m
INNER JOIN #TempSeqs t ON m.seq = t.member_seq
WHERE m.push_onoff = 'Y';

DROP TABLE #TempSeqs;

Conclusion

By switching from IN clause batch queries to cursor-based pagination, we:

✅ Eliminated the MSSQL 2,100 parameter limit error
✅ Improved code consistency across services
✅ Reduced database query parsing overhead
✅ Maintained the same 10,000 page size

Building a Smart Bible Reading Reminder System - Personalized Push Notifications at Scale

Sangwoo Lee — Mon, 09 Feb 2026 17:06:00 +0000

When users can create multiple Bible reading plans, how do you send exactly one reminder per person—with personalized deep-link data—based on their most recent activity? Here's how I built an automated system that handles this complexity at scale.

When building a Bible reading app where users can create multiple concurrent reading plans, a simple question emerged: How do we remind inactive readers without spamming them?

The challenge wasn't just sending push notifications—that's the easy part. The real complexity came from:

One notification per user, not one per reading plan (users have multiple plans)
Personalized deep-links in each notification (click should open their specific plan)
Most recent activity tracking across all plans (don't send if they read any plan recently)
Automated scheduling via cron (zero manual intervention)
Configurable inactivity threshold (default: 7 days, but adjustable)

What started as "send a push to inactive users" turned into a fascinating journey through SQL window functions, personalized FCM payloads, and the critical importance of the one-person-one-push rule. Here's how I built it.

The Context: Multi-Plan Bible Reading System

Our app allows users to create multiple Bible reading plans simultaneously:

User A:
- Plan 1: One Year Bible (Started 6 months ago, last read: 10 days ago)
- Plan 2: New Testament (Started last week, last read: 2 days ago)  
- Plan 3: Psalms (Started yesterday, last read: 1 day ago)

User B:
- Plan 1: Chronological Bible (Started 3 months ago, last read: 8 days ago)

The Database Schema:

// bible_schedule_plan_list - The reading plan
// (One plan can have multiple participants)
{
  idx: number;              // Plan ID
  leader_seq: number;       // Creator's member_seq
  title: string;            // Plan name
  schedule_type_name: string;
  start_date: string;
  state: number;            // 1=pending, 2=active, 3=completed
  delete_yn: string;
}

// bible_schedule_plan_member_list - User's participation in plan
// (One user can participate in multiple plans)
{
  idx: number;              // plan_member_idx (unique per participation)
  plan_list_idx: number;    // FK to plan
  member_seq: number;       // User ID
  state: number;            // 1=pending, 2=active, 3=completed
  push_time: string;        // User's preferred notification time
  delete_yn: string;
}

// bible_schedule_sync_data_list - Reading progress records
// (Every chapter read = one record)
{
  idx: number;
  member_seq: number;
  plan_list_idx: number;
  plan_member_idx: number;
  bible_code: number;       // Book number (1-66)
  jang: number;             // Chapter
  regdate: Date;            // When they read it
  delete_yn: string;
}

The Business Rules:

A user can have multiple active plans (state=2)
Each plan tracks reading progress independently
Users read different plans at different paces
Critical: Send ONE reminder per user, not one per plan

The Challenge: Defining "Inactive"

Naive Approach (Wrong!)

My first instinct was simple:

-- ❌ Wrong: This gives users with multiple plans
SELECT DISTINCT member_seq
FROM bible_schedule_sync_data_list
WHERE DATEDIFF(day, regdate, GETDATE()) >= 7
  AND delete_yn = 'N';

Problem: User A has Plan 1 (10 days inactive) and Plan 3 (1 day active). This query would:

Find Plan 1's inactivity (10 days) ✓
Find Plan 3's activity (1 day) ✓
Still include User A (because Plan 1 is inactive) ❌

Result: False positive. User A gets a reminder even though they actively read Plan 3 yesterday!

The Correct Definition

A user is inactive if their MOST RECENT reading activity (across all plans) was more than N days ago.

User A's activity timeline:
- Plan 1: last read 10 days ago
- Plan 2: last read 2 days ago  ← Most recent
- Plan 3: last read 1 day ago

Most recent activity: 1 day ago → NOT INACTIVE ✓

User B's activity timeline:
- Plan 1: last read 8 days ago  ← Most recent (only plan)

Most recent activity: 8 days ago → INACTIVE ✓

This requires SQL window functions to find the most recent activity across all plans per user.

Implementation: The SQL Query Journey

Attempt 1: HAVING Clause (Wrong!)

-- ❌ This has a fatal logic error
WITH LastReadPerMember AS (
  SELECT 
    sd.member_seq,
    sd.plan_list_idx,
    pm.idx AS plan_member_idx,
    MAX(sd.regdate) AS last_read_date,
    ROW_NUMBER() OVER (
      PARTITION BY sd.member_seq 
      ORDER BY MAX(sd.regdate) DESC
    ) AS rn
  FROM bible_schedule_sync_data_list sd
  INNER JOIN bible_schedule_plan_member_list pm
    ON sd.member_seq = pm.member_seq
    AND sd.plan_list_idx = pm.plan_list_idx
  WHERE sd.delete_yn = 'N'
    AND pm.delete_yn = 'N'
    AND pm.state = 2  -- Active plans only
  GROUP BY sd.member_seq, sd.plan_list_idx, pm.idx
  HAVING DATEDIFF(day, MAX(sd.regdate), GETDATE()) >= 7  -- ❌ BUG!
)
SELECT * FROM LastReadPerMember WHERE rn = 1;

The Bug:

The HAVING clause filters OUT plans with recent activity BEFORE applying ROW_NUMBER().

Example:

User A's plans:
- Plan 1: 10 days inactive → HAVING passes → included
- Plan 2: 5 days inactive  → HAVING passes → included
- Plan 3: 1 day active     → HAVING fails  → EXCLUDED

ROW_NUMBER() now sees only Plan 1 and Plan 2:
- Plan 2 gets rn=1 (more recent)

Result: User A is marked inactive with Plan 2 (5 days)
Reality: User A is ACTIVE (read Plan 3 yesterday)

Actual production data:

Wrong query: 71,682 users flagged as inactive
Correct query: 56,671 users actually inactive
Difference: 15,011 false positives!

Attempt 2: Correct Logic (✅)

-- ✅ Correct: Check inactivity AFTER finding most recent activity
WITH LastReadPerMember AS (
  SELECT 
    sd.member_seq,
    sd.plan_list_idx,
    pm.idx AS plan_member_idx,
    MAX(sd.regdate) AS last_read_date,
    ROW_NUMBER() OVER (
      PARTITION BY sd.member_seq 
      ORDER BY MAX(sd.regdate) DESC  -- Most recent first
    ) AS rn
  FROM bible_schedule_sync_data_list sd
  INNER JOIN bible_schedule_plan_member_list pm
    ON sd.member_seq = pm.member_seq
    AND sd.plan_list_idx = pm.plan_list_idx
  WHERE sd.delete_yn = 'N'
    AND pm.delete_yn = 'N'
    AND pm.state = 2
  GROUP BY sd.member_seq, sd.plan_list_idx, pm.idx
  -- No HAVING clause here!
)
SELECT 
  member_seq,
  plan_list_idx,
  plan_member_idx,
  last_read_date
FROM LastReadPerMember
WHERE rn = 1  -- Get most recent plan per user
  AND DATEDIFF(day, last_read_date, GETDATE()) >= @0  -- Check inactivity here!
ORDER BY member_seq;

How It Works:

Find all active plans for all users (no date filtering yet)
Get last read date for each plan
Rank by recency per user (PARTITION BY member_seq ORDER BY MAX(regdate) DESC)
Select most recent plan per user (WHERE rn = 1)
Check inactivity on that most recent plan only

Result:

User A:
- Plan 1: 10 days, rn=3
- Plan 2: 5 days, rn=2  
- Plan 3: 1 day, rn=1   ← Selected
- Check: 1 day < 7 days → NOT included ✓

User B:
- Plan 1: 8 days, rn=1  ← Selected  
- Check: 8 days >= 7 days → INCLUDED ✓

Verified: Matches the independent query that counts DISTINCT member_seq (56,671 users).

The Critical Piece: Personalized Deep-Links

The Problem: Generic vs Personalized

Generic approach (wrong for multi-plan users):

// ❌ All users get the same FCM data
const message = {
  token: user.push_token,
  notification: {
    title: "Come back to Bible reading!",
    body: "It's been a while since your last reading."
  },
  data: {
    division: "screen",
    screen_name: "ReadPlanHome"  // Opens generic screen
  }
};

Problem: User clicks notification → Opens generic plan list → User confused ("Which plan was I reading?")

The Solution: Individual Plan Data

Each user should get their specific plan's deep-link:

// ✅ Each user gets personalized FCM data
const messages = chunk.map((token, idx) => {
  const memberSeqIndex = chunkIndex * params.chunkSize + idx;
  const memberSeq = params.memberSeqs[memberSeqIndex];
  const planListIdx = params.planListIdxs[memberSeqIndex];      // ← User's plan
  const planMemberIdx = params.planMemberIdxs[memberSeqIndex];  // ← User's participation

  return {
    token,
    notification: {
      title: params.title,
      body: params.content,
    },
    data: {
      division: "screen",
      screen_name: "ReadPlanHome",
      plan_idx: String(planListIdx),        // ✅ Opens THIS user's plan
      plan_member_idx: String(planMemberIdx),
      member_seq: String(memberSeq),
    },
  };
});

Result:

User A clicks notification → Opens Plan 3 directly (their most recent)
User B clicks notification → Opens Plan 1 directly (their only plan)

This is crucial for user experience in a multi-plan system!

NestJS Implementation: The Complete Service

Step 1: Module Setup

// bible-read.module.ts
@Module({
  imports: [
    ScheduleModule.forRoot(),  // Enable cron

    TypeOrmModule.forFeature([
      Member, 
      MemberNewmediaDigital,
      PushNotificationLog,
    ], 'mssqlConnection'),

    TypeOrmModule.forFeature([
      BibleScheduleSyncDataList, 
      BibleSchedulePlanMemberList
    ], 'mobileConnection'),

    BullModule.registerQueue({ name: 'push-message-queue' }),
  ],
  controllers: [BibleReadController],
  providers: [BibleReadService],
})
export class BibleReadModule {}

Step 2: Configurable Constants

// bible-read.service.ts
@Injectable()
export class BibleReadService {
  private readonly logger = new Logger(BibleReadService.name);

  // ===== Configuration =====
  private readonly CRON_ENABLED = true;
  private readonly DEFAULT_INACTIVE_DAYS = 7;  // ← Adjustable!
  private readonly DEFAULT_TITLE = 'Come back to Bible reading!';
  private readonly DEFAULT_CONTENT = 'Continue your spiritual journey today.';

  constructor(
    @InjectRepository(Member, 'mssqlConnection')
    private readonly memberRepository: Repository,

    @InjectRepository(PushNotificationLog, 'mssqlConnection')
    private readonly pushLogRepository: Repository,

    @InjectDataSource('mobileConnection')
    private readonly mobileDataSource: DataSource,

    @InjectQueue('push-message-queue')
    private readonly pushQueue: Queue,

    @Inject('FIREBASE_ADMIN')
    private readonly firebaseApp: admin.app.App,
  ) {}
}

Step 3: Cron Job Scheduler

/**
 * Cron Job: Daily Inactive User Reminder
 * Schedule: Every day at 9:00 AM KST
 * 
 * To change schedule, modify the cron expression:
 * '0 9 * * *'  = 9:00 AM daily
 * '30 14 * * *' = 2:30 PM daily
 * '0 6 * * 1'   = 6:00 AM every Monday
 */
@Cron('0 9 * * *', {
  name: 'daily-bible-read-reminder',
  timeZone: 'Asia/Seoul',
})
async handleDailyInactiveReminder() {
  if (!this.CRON_ENABLED) {
    this.logger.log('[Cron] Reminder disabled');
    return;
  }

  try {
    this.logger.log('[Cron] ===== Daily Reminder Started =====');
    this.logger.log(`[Cron] Execution time: ${new Date().toLocaleString('ko-KR')}`);

    const dto: SendInactiveReminderDto = {
      title: this.DEFAULT_TITLE,
      content: this.DEFAULT_CONTENT,
      inactive_days: this.DEFAULT_INACTIVE_DAYS,
      chunkSize: 500,
      chunkDelay: 2000,
      data: {
        division: 'screen',
        screen_name: 'ReadPlanHome',
      },
    };

    await this.sendInactiveReminder(dto, 'CRON_JOB');

    this.logger.log('[Cron] ===== Daily Reminder Completed =====');
  } catch (error) {
    this.logger.error('[Cron] Execution failed:', error);
  }
}

Step 4: Database Query (The Core Logic)

private async getInactiveUsersWithTokens(
  inactiveDays: number,
): Promise<Array> {
  this.logger.log(`[getInactiveUsers] Querying ${inactiveDays}-day inactive users`);

  // Step 1: Find inactive users with their most recent plan
  const query = `
    WITH LastReadPerMember AS (
      SELECT 
        sd.member_seq,
        sd.plan_list_idx,
        pm.idx AS plan_member_idx,
        MAX(sd.regdate) AS last_read_date,
        ROW_NUMBER() OVER (
          PARTITION BY sd.member_seq 
          ORDER BY MAX(sd.regdate) DESC
        ) AS rn
      FROM bible_schedule_sync_data_list sd
      INNER JOIN bible_schedule_plan_member_list pm
        ON sd.member_seq = pm.member_seq
        AND sd.plan_list_idx = pm.plan_list_idx
      WHERE sd.delete_yn = 'N'
        AND pm.delete_yn = 'N'
        AND pm.state = 2
      GROUP BY sd.member_seq, sd.plan_list_idx, pm.idx
    )
    SELECT 
      member_seq,
      plan_list_idx,
      plan_member_idx,
      last_read_date
    FROM LastReadPerMember
    WHERE rn = 1
      AND DATEDIFF(day, last_read_date, GETDATE()) >= @0
    ORDER BY member_seq
  `;

  const inactiveMembers = await this.mobileDataSource.query(query, [inactiveDays]);

  this.logger.log(`[getInactiveUsers] Found ${inactiveMembers.length} inactive users`);

  if (inactiveMembers.length === 0) return [];

  // Step 2: Get push tokens (paginated for large datasets)
  const memberSeqs = inactiveMembers.map(m => m.member_seq);
  const tokenToDataMap = new Map();

  let page = 0;
  const PAGE_SIZE = 10000;

  while (page * PAGE_SIZE < memberSeqs.length) {
    const seqChunk = memberSeqs.slice(page * PAGE_SIZE, (page + 1) * PAGE_SIZE);

    const members = await this.memberRepository
      .createQueryBuilder('member')
      .select(['member.seq', 'member.push_token'])
      .where('member.seq IN (:...seqs)', { seqs: seqChunk })
      .andWhere('member.push_onoff = :onoff', { onoff: 'Y' })
      .andWhere('member.push_token IS NOT NULL')
      .andWhere("member.push_token != ''")
      .getMany();

    for (const member of members) {
      const inactiveMember = inactiveMembers.find(m => m.member_seq === member.seq);
      if (inactiveMember && member.push_token) {
        tokenToDataMap.set(member.push_token, {
          member_seq: member.seq,
          last_read_date: inactiveMember.last_read_date,
          plan_list_idx: inactiveMember.plan_list_idx,
          plan_member_idx: inactiveMember.plan_member_idx,
        });
      }
    }

    page++;
    await delay(100);  // Prevent DB overload
  }

  const result = Array.from(tokenToDataMap.entries()).map(([token, data]) => ({
    push_token: token,
    ...data,
  }));

  this.logger.log(`[getInactiveUsers] Final: ${result.length} users with valid tokens`);

  return result;
}

Step 5: Personalized FCM Payload

async sendInactiveReminderNotifications(params: {
  jobId: string;
  title: string;
  content: string;
  chunkSize: number;
  chunkDelay: number;
  pushTokens: string[];
  memberSeqs: number[];
  planListIdxs: number[];      // ← Individual plan IDs
  planMemberIdxs: number[];    // ← Individual participation IDs
  inactiveDays: number;
}): Promise {
  const uniqueTokens = Array.from(new Set(params.pushTokens));
  const chunks = chunkArray(uniqueTokens, params.chunkSize);

  this.logger.log(`[FCM] Sending to ${uniqueTokens.length} users in ${chunks.length} chunks`);

  let totalSent = 0;
  let totalFailed = 0;

  for (let chunkIndex = 0; chunkIndex < chunks.length; chunkIndex++) {
    const chunk = chunks[chunkIndex];

    if (chunkIndex > 0) {
      await delay(params.chunkDelay);
    }

    const messaging = this.firebaseApp.messaging();

    // ✅ Build personalized messages
    const messages = chunk.map((token, idx) => {
      const globalIdx = chunkIndex * params.chunkSize + idx;
      const memberSeq = params.memberSeqs[globalIdx];
      const planIdx = params.planListIdxs[globalIdx];        // ← This user's plan
      const planMemberIdx = params.planMemberIdxs[globalIdx]; // ← This user's participation

      return {
        token,
        notification: {
          title: params.title,
          body: params.content,
        },
        data: {
          division: 'screen',
          screen_name: 'ReadPlanHome',
          member_seq: String(memberSeq),
          plan_idx: String(planIdx),              // ✅ Personalized!
          plan_member_idx: String(planMemberIdx), // ✅ Personalized!
        },
      };
    });

    // Send via FCM with retry logic
    const response = await sendEachWithRetry(messaging, messages, false, {
      maxRetries: 3,
      initialDelayMs: 1000,
      maxDelayMs: 5000,
    });

    totalSent += response.successCount;
    totalFailed += response.failureCount;

    // ✅ Save logs asynchronously
    await saveBibleReadInactiveReminderLogs(
      this.pushLogRepository,
      params.jobId,
      params.inactiveDays,
      params.title,
      params.content,
      messages,
      response,
      chunkIndex,
      params.chunkSize,
      params.chunkDelay,
      false  // isDryRun
    );

    this.logger.log(
      `[FCM] Chunk ${chunkIndex + 1}/${chunks.length}: ${response.successCount}/${chunk.length} sent`
    );
  }

  this.logger.log(`[FCM] Total: ${totalSent} sent, ${totalFailed} failed`);

  return totalFailed === 0;
}

Testing and Verification

Manual Trigger API (For Testing)

// bible-read.controller.ts
@Post('trigger-daily-reminder')
@Public()
async triggerDailyReminder() {
  this.logger.log('[Manual] Triggering daily reminder');

  await this.bibleReadService.handleDailyInactiveReminder();

  return {
    statusCode: 200,
    message: 'Daily reminder executed manually',
  };
}

Test in development:

# Don't wait for cron - trigger immediately
curl -X POST http://localhost:3002/bible-read/trigger-daily-reminder

Count API (Preview Recipients)

@Get('inactive-users/count')
async getInactiveUsersCount(
  @Query('inactive_days') inactiveDays: number = 7,
) {
  const result = await this.bibleReadService.getInactiveUsersCount(inactiveDays);

  return {
    statusCode: 200,
    data: {
      inactive_users_count: result.inactive_users_count,
      inactive_days: inactiveDays,
    },
  };
}

Test before production:

# Check how many users would receive notifications
curl "http://localhost:3002/bible-read/inactive-users/count?inactive_days=7"

# Response:
{
  "statusCode": 200,
  "data": {
    "inactive_users_count": 56671,
    "inactive_days": 7
  }
}

Production Results

After deploying to production with 900,000+ registered users:

Metric	Result	Notes
Query Performance	< 3 seconds	Cursor pagination + indexed queries
Daily Reminders Sent	~56,000	7-day inactive users
One-User-One-Push Rate	100%	Verified via logs
Personalized Deep-Link Rate	100%	All users get their plan_idx
False Positive Rate	0%	Corrected SQL logic
Notification Delivery Rate	98.5%	FCM standard delivery rate
User Engagement Lift	+23%	Compared to generic reminders

Database Query Verification:

-- Verification query: Should match service count
WITH LastReadPerMember AS (
  SELECT 
    sd.member_seq,
    MAX(sd.regdate) AS last_read_date,
    ROW_NUMBER() OVER (
      PARTITION BY sd.member_seq 
      ORDER BY MAX(sd.regdate) DESC
    ) AS rn
  FROM bible_schedule_sync_data_list sd
  INNER JOIN bible_schedule_plan_member_list pm
    ON sd.member_seq = pm.member_seq
    AND sd.plan_list_idx = pm.plan_list_idx
  WHERE sd.delete_yn = 'N'
    AND pm.delete_yn = 'N'
    AND pm.state = 2
  GROUP BY sd.member_seq, sd.plan_list_idx
)
SELECT COUNT(*) AS inactive_count
FROM LastReadPerMember
WHERE rn = 1
  AND DATEDIFF(day, last_read_date, GETDATE()) >= 7;

-- Result: 56,671 ✓ (matches service count)

Key Implementation Patterns

Pattern 1: SQL Window Functions for Multi-Plan Users

-- Use ROW_NUMBER() to rank activities per user
ROW_NUMBER() OVER (
  PARTITION BY sd.member_seq      -- Group by user
  ORDER BY MAX(sd.regdate) DESC   -- Most recent first
) AS rn

-- Then filter: WHERE rn = 1 (most recent only)

Why This Matters:

Handles users with 1 plan (simple case)
Handles users with 10+ plans (complex case)
Single query for all scenarios

Pattern 2: Parameterized Inactivity Threshold

// Configurable constant
private readonly DEFAULT_INACTIVE_DAYS = 7;

// Parameterized SQL
const query = `... WHERE DATEDIFF(day, last_read_date, GETDATE()) >= @0`;

// Pass value at runtime
await this.mobileDataSource.query(query, [inactiveDays]);

Benefits:

Easy A/B testing (5 days vs 7 days vs 10 days)
No code changes needed
SQL injection safe

Pattern 3: Personalized FCM Data Arrays

// Parallel arrays for individual data
{
  pushTokens: ['token1', 'token2', 'token3'],
  memberSeqs: [123, 456, 789],
  planListIdxs: [10, 20, 30],        // ← User 123's plan is 10
  planMemberIdxs: [100, 200, 300],   // ← User 123's participation is 100
}

// Map by index
const messages = chunk.map((token, idx) => {
  const globalIdx = chunkIndex * chunkSize + idx;
  return {
    token,
    data: {
      plan_idx: String(planListIdxs[globalIdx]),  // ← Individual!
    },
  };
});

Alternative (rejected): Map of maps

// ❌ More memory, harder to chunk
{
  'token1': { member_seq: 123, plan_idx: 10, ... },
  'token2': { member_seq: 456, plan_idx: 20, ... },
}

Pattern 4: Dry-Run Mode for Testing

const isDryRun = true;  // ← Toggle for testing

const response = await sendEachWithRetry(
  messaging,
  messages,
  isDryRun,  // ← FCM validates but doesn't send
);

// Test the full pipeline without spamming users

Lessons Learned

1. SQL Window Functions Are Your Friend

When you need "per-group rankings" (e.g., most recent activity per user), ROW_NUMBER() OVER (PARTITION BY ...) is the elegant solution. Alternative approaches (subqueries, multiple joins) are harder to read and slower.

2. HAVING vs WHERE Placement Matters

-- ❌ Filters BEFORE ranking (wrong)
GROUP BY user
HAVING last_date >= 7
THEN rank by date

-- ✅ Ranks FIRST, filters AFTER (correct)
GROUP BY user
THEN rank by date
WHERE rn = 1 AND last_date >= 7

Rule of thumb: Apply filters on derived columns (like rn) in the outer query, not in HAVING.

3. Personalization At Scale Requires Planning

Sending 50,000 personalized notifications means:

Storing 50,000 plan_idx values in memory
Mapping them correctly during chunking
Verifying index alignment (easy to get off-by-one errors)

Test with small datasets first (10-100 users) before scaling.

4. Cron Expressions Are Powerful But Subtle

'0 9 * * *'   // 9:00 AM daily
'30 14 * * *' // 2:30 PM daily  
'0 6 * * 1'   // 6:00 AM every Monday
'0 */2 * * *' // Every 2 hours

Pro tip: Use crontab.guru to validate expressions.

5. Database Logging Is Non-Negotiable

Every sent notification should be logged to the database with:

member_seq (who received it)
job_id (which batch)
is_success (delivered or failed)
sent_at (when)
error_message (if failed)

This enables:

Analytics (engagement rates)
Debugging (why did User X not receive it?)
Compliance (proof of delivery)

6. Dry-Run Mode Saves Production Incidents

Before enabling cron in production:

Set isDryRun = true
Trigger manually via API
Verify logs show correct count
Verify FCM returns dryRun: true in response
Only then enable isDryRun = false

Real story: My dry-run test showed 71,682 users instead of expected 56,671. Caught the SQL bug before spamming 15,000 users!

Scalability Considerations

Current Architecture (Synchronous Cron)

Cron (9 AM) → Query DB → Send via BullMQ → FCM
              ↓ 3s      ↓ Queue        ↓ 2-3 min

Pros:

Simple architecture
Easy to debug
Sufficient for 50K-100K users

Cons:

Cron blocks until query completes
Single point of failure

Future Architecture (Async Queue)

For 500K+ users, consider decoupling query from cron:

Cron (9 AM) → Add "query job" to queue
               ↓
            Worker queries DB → Adds "send jobs" to queue
                                 ↓
                              Workers send via FCM

Benefits:

Cron completes in <1 second
Query can take minutes without blocking
Horizontal scaling (more workers)

Implementation sketch:

@Cron('0 9 * * *')
async handleDailyReminder() {
  // Just schedule the query job
  await this.pushQueue.add('query-inactive-users', {
    inactiveDays: 7,
  });

  this.logger.log('[Cron] Query job scheduled');
}

// Separate worker processes the query
case 'query-inactive-users': {
  const users = await this.bibleReadService.getInactiveUsers(data.inactiveDays);

  // Add individual send jobs
  for (const userChunk of chunkArray(users, 500)) {
    await this.pushQueue.add('send-bible-reminder', {
      users: userChunk,
    });
  }
  break;
}

Conclusion

Building an automated Bible reading reminder system taught me that the devil is in the business logic. What seemed like a straightforward "send push to inactive users" requirement turned into a fascinating journey through:

SQL window functions for complex multi-plan logic
Personalized FCM payloads for better user experience
The critical importance of "one person, one push"
Parameterized queries for flexibility
Dry-run testing for confidence

The final system now runs daily at 9 AM KST, automatically identifying ~56,000 inactive users, sending each exactly one notification with their personalized deep-link, and logging every delivery—all with zero manual intervention.

The key insight: In a multi-plan system, "inactive" doesn't mean "hasn't used Plan A"—it means "hasn't used ANY plan." SQL window functions (ROW_NUMBER() OVER (PARTITION BY ...)) elegantly solve this by finding the most recent activity across all plans per user, then checking inactivity on that single timestamp.

By carefully structuring the SQL logic (rank first, filter second), leveraging TypeScript's type safety, and building in observability (logs, dry-run mode), I built a reminder system that's both robust and maintainable.

In the next part of this series, I'll explore how I optimized FCM delivery for 900,000+ tokens using cursor-based pagination, deduplicated tokens with Redis, and handled rate limits gracefully.

Key Takeaways

Use SQL window functions (ROW_NUMBER() OVER (PARTITION BY ...)) for per-group rankings
In multi-entity systems, define "inactive" carefully (most recent across ALL entities)
Always filter on derived columns (like rn) in the outer query, not in HAVING
Personalized data (plan_idx) significantly improves user engagement (+23% in our case)
Parameterize everything (inactive days, cron schedule, chunk size)
Implement dry-run mode and test thoroughly before production
Log every notification for debugging and analytics
Database query optimization matters at scale (cursor pagination, indexes)

When 502 Bad Gateway Revealed My Firebase Connection Pool Mistake (and How I Fixed It)

Sangwoo Lee — Mon, 02 Feb 2026 17:27:00 +0000

A production incident at 5PM - how a 502 error led me to discover we were creating new HTTP connections for every Firebase request, and the simple fix that improved performance 3-5x

5:15 PM. Friday afternoon. My Slack explodes with messages:

"Push notification admin page down - 502 Bad Gateway"
"Can't send any notifications"

I opened my AWS CloudWatch dashboard. Every metric had spiked simultaneously:

CPU usage: 15% → 95%
Network packets out: 50/sec → 12,000/sec
Network bytes out: 1 MB/min → 800 MB/min

My first thought: "Did we get DDoS'd?"

Turns out, I had been creating a new HTTP connection for every single Firebase push notification for 6 months without realizing it. Here's how I discovered the issue and how a 3-line code change fixed everything.

The smoking gun

I checked the system logs:

sudo tail -f /var/log/syslog

Jan 15 17:40:02 kernel: nf_conntrack: nf_conntrack: table full, dropping packet
Jan 15 17:40:02 kernel: nf_conntrack: nf_conntrack: table full, dropping packet
(repeating thousands of times)

That one line explained everything. Linux kernel's connection tracking table had filled up, causing it to drop all packets - including health checks, SSH attempts, and API requests.

What actually happened

Without Keep-Alive, each Firebase API call creates a brand new TCP connection:

Request 1: New connection → 3-way handshake → Send → Close → TIME_WAIT (60s)
Request 2: New connection → 3-way handshake → Send → Close → TIME_WAIT (60s)
Request 3: New connection → 3-way handshake → Send → Close → TIME_WAIT (60s)
...

With retry logic (up to 3 retries per chunk):

50,000 users = 100 chunks of 500
Worst case: 100 chunks × 4 attempts = 400 connections
Plus retries and other traffic = 130+ simultaneous TIME_WAIT connections

When this exceeded the nf_conntrack_max limit (16,384 on my EC2), the kernel started dropping ALL packets → 502 Bad Gateway.

Why it worked fine for 6 months

Three factors kept me safe:

1. Chunk delays

const chunkDelay = 2000; // 2 second delay between chunks

This gave connections time to clear from TIME_WAIT.

2. Small campaigns

Daily devotionals: 10,000 users (20 chunks)
Event announcements: 5,000 users (10 chunks)
All well below threshold

3. Peak hour spacing
Notifications sent at 9 AM, 12 PM, 9 PM - hours apart.

What changed on Jan 15th?

50,000 users (100 chunks)
2 campaigns sent simultaneously
FCM API errors triggered retry storm
Result: 400+ connections in 5 minutes → crash

The immediate fix: Increase conntrack limit

# Check current limit
sudo sysctl net.netfilter.nf_conntrack_max
# Output: 16384

# Increase to 262,144
sudo sysctl -w net.netfilter.nf_conntrack_max=262144

# Make permanent
echo "net.netfilter.nf_conntrack_max = 262144" | sudo tee /etc/sysctl.d/99-custom.conf
sudo sysctl -p

This fixed the immediate crisis but didn't address the root cause.

The root cause: No HTTP Keep-Alive

My Firebase initialization code:

// firebase.module.ts - BEFORE (❌ Wrong)
{
  provide: 'FIREBASE_ADMIN',
  useFactory: (configService: ConfigService) => {
    const firebaseEnv = configService.get('firebase');

    const app = admin.initializeApp({
      credential: admin.credential.cert({
        projectId: firebaseEnv.projectId,
        clientEmail: firebaseEnv.clientEmail,
        privateKey: firebaseEnv.privateKey,
      }),
    });

    // ❌ No HTTP agent configuration!
    return app;
  },
}

Overhead per request: TCP handshake (20-50ms) + TLS handshake (50-150ms) = 100-200ms wasted per connection.

The fix: Enable HTTP Keep-Alive

// firebase.module.ts - AFTER (✅ Correct)
import { Agent as HttpsAgent } from 'https';

{
  provide: 'FIREBASE_ADMIN',
  useFactory: (configService: ConfigService) => {
    const firebaseEnv = configService.get('firebase');

    // ✅ Create Keep-Alive HTTPS Agent
    const httpsAgent = new HttpsAgent({
      keepAlive: true,              // Enable connection reuse
      keepAliveMsecs: 30000,        // Keep idle connections for 30s
      maxSockets: 100,              // Max 100 concurrent connections
      maxFreeSockets: 10,           // Keep 10 idle sockets ready
      timeout: 60000,               // 60s socket timeout
      scheduling: 'lifo',           // Use most recent connection first
    });

    console.log('🔧 [Firebase] Keep-Alive HTTPS Agent created');

    // ✅ Initialize Firebase
    const app = admin.initializeApp({
      credential: admin.credential.cert({
        projectId: firebaseEnv.projectId,
        clientEmail: firebaseEnv.clientEmail,
        privateKey: firebaseEnv.privateKey,
      }),
    });

    // ✅ Inject Keep-Alive Agent globally
    process.env.GOOGLE_APPLICATION_TIMEOUT = '30000';
    const https = require('https');
    https.globalAgent = httpsAgent;

    console.log('✅ [Firebase] Keep-Alive enabled');
    return app;
  },
  inject: [ConfigService],
}

What changed:

Before: 100 requests = 100 new connections
After: 100 requests = 5-10 reused connections

Configuration explained

`keepAlive: true`

Enables TCP connection reuse. Without this, every request creates a new connection.

`keepAliveMsecs: 30000`

Sends TCP keep-alive probes every 30s to prevent connection timeout (Firebase timeout is ~60s).

`maxSockets: 100`

Limits concurrent active connections to 100. Requests queue if exceeded.

`maxFreeSockets: 10`

Keeps 10 idle connections ready for immediate reuse.

`timeout: 60000`

Closes socket if no data received for 60s (Firebase SLA is 30s max).

`scheduling: 'lifo'`

Last In, First Out - reuses the most recently used connection (keeps connections "warm").

Measuring the impact

Test: Send 100,000 notifications (200 chunks of 500)

Metric	Before	After	Improvement
Total time	12m 30s	4m 15s	3x faster
TCP connections	200	12	16x fewer
CPU usage	45%	28%	38% lower
nf_conntrack entries	180 peak	25 peak	7x fewer

Real production (1 week after fix):

Send time (100K): 12.5 min → 4.2 min
502 errors: 3 incidents → 0 incidents
CPU credit exhaustion: 2 times → 0
Cost savings: ~$50/month (can downgrade EC2)

Key lessons

1. Default configurations are rarely optimal

Node.js defaults to keepAlive: false. Always enable it:

const httpsAgent = new HttpsAgent({ keepAlive: true });

2. Check kernel limits for high-connection workloads

# Default is often too low (16,384)
sysctl net.netfilter.nf_conntrack_max

# Increase for API-heavy apps
echo "net.netfilter.nf_conntrack_max = 262144" | sudo tee /etc/sysctl.d/99-custom.conf

3. Always check system logs

sudo tail -f /var/log/syslog

Kernel messages tell the real story. High CPU/network can have many causes - logs reveal the truth.

4. Small-scale problems hide at scale

My 2-second chunk delays accidentally saved me at small scale (10K users), but broke at large scale (50K users).

How to check if you have this issue

Monitor while sending notifications:

# Check TIME_WAIT connections
ss -s | grep timew
# If you see 200+, you have a problem

# Check conntrack usage
cat /proc/sys/net/netfilter/nf_conntrack_count
cat /proc/sys/net/netfilter/nf_conntrack_max
# If count is >80% of max, you're at risk

Quick fix for other Firebase users

Add this to your Firebase initialization:

import { Agent as HttpsAgent } from 'https';

const httpsAgent = new HttpsAgent({
  keepAlive: true,
  keepAliveMsecs: 30000,
  maxSockets: 100,
  maxFreeSockets: 10,
  timeout: 60000,
  scheduling: 'lifo',
});

const app = admin.initializeApp({ /* ... */ });

const https = require('https');
https.globalAgent = httpsAgent;

This works for all Node.js HTTP clients (axios, node-fetch, etc.) and Google Cloud libraries.

Conclusion

A 502 error on Friday evening led me to discover I'd been creating 200+ unnecessary TCP connections every time I sent push notifications.

The fix? Three lines of code:

import { Agent as HttpsAgent } from 'https';
const httpsAgent = new HttpsAgent({ keepAlive: true });
https.globalAgent = httpsAgent;

The result:

3x faster sends
16x fewer connections
38% lower CPU usage
Zero 502 errors since

If you're making high-volume HTTP requests in Node.js, check your connection pooling settings. You might be surprised what you find.

From Slow Aggregations to Instant Queries: Optimizing Push Notification History with Summary Tables

Sangwoo Lee — Mon, 26 Jan 2026 17:13:00 +0000

How I transformed a 50-second GROUP BY query into a 50-millisecond lookup by implementing a summary table pattern in NestJS + MySQL

When your push notification dashboard takes nearly a minute to load because it's aggregating half a million database records in real-time, it's time to rethink your data access patterns. Here's how I solved this problem by implementing a summary table pattern that improved query performance by 1,000x while maintaining data consistency.

The Problem: Real-Time Aggregation at Scale

My push notification service sends conditional notifications to 100,000+ users daily. Each notification generates individual logs in push_notification_log table. The admin dashboard needs to display a paginated list of these campaigns with statistics—but there was a catch.

Before Optimization: The Slow GROUP BY Approach

// firebase.service.ts - Original implementation
async getConditionalSendList(dto: ConditionalSendListReqDto) {
  // ❌ Problem: GROUP BY on 500,000+ rows every request
  const query = this.pushNotificationLog
    .createQueryBuilder('log')
    .select([
      'log.job_id AS job_id',
      'MAX(log.title) AS title',
      'MAX(log.content) AS content',
      'MIN(log.sent_at) AS sent_start_at',
      'MAX(log.sent_at) AS sent_end_at',
      'COUNT(*) AS total_count',
      'SUM(CASE WHEN log.is_success = 1 THEN 1 ELSE 0 END) AS success_count',
      'SUM(CASE WHEN log.is_success = 0 THEN 1 ELSE 0 END) AS failure_count',
    ])
    .where("log.job_id LIKE 'conditional-%'")  // Filter conditional sends
    .groupBy('log.job_id')  // ← Bottleneck: Groups millions of rows
    .orderBy('sent_start_at', 'DESC')
    .skip((page - 1) * size)
    .take(size);

  const results = await query.getRawMany();

  // Calculate success rate in application
  return results.map(row => ({
    ...row,
    success_rate: (row.success_count / row.total_count) * 100
  }));
}

Database structure:

-- push_notification_log: Individual notification records
-- Sample data: 500,000+ rows for 100 campaigns
┌─────┬──────────────────┬────────────┬─────────────┬────────────┐
│ id  │ job_id           │ member_seq │ is_success  │ sent_at    │
├─────┼──────────────────┼────────────┼─────────────┼────────────┤
│ 1   │ conditional-abc  │ 12345      │ 1           │ 2025-01-08 │
│ 2   │ conditional-abc  │ 12346      │ 1           │ 2025-01-08 │
│ ... │ ...              │ ...        │ ...         │ ...        │
│ 5k  │ conditional-abc  │ 17345      │ 0           │ 2025-01-08 │
│ 5k+ │ conditional-xyz  │ 20001      │ 1           │ 2025-01-07 │
└─────┴──────────────────┴────────────┴─────────────┴────────────┘

Performance Impact

Query execution time analysis:

EXPLAIN SELECT 
  job_id, 
  COUNT(*) as total_count,
  SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) as success_count
FROM push_notification_log
WHERE job_id LIKE 'conditional-%'
GROUP BY job_id;

+----+-------------+------+-------+----------+------+---------+
| id | select_type | type | rows  | filtered | cost | Extra   |
+----+-------------+------+-------+----------+------+---------+
| 1  | SIMPLE      | ALL  | 524288| 100.00   | 8947 | Using   |
|    |             |      |       |          |      | filesort|
+----+-------------+------+-------+----------+------+---------+

Execution time: 48-52 seconds (with proper indexes)
Memory usage: Temporary table on disk (GROUP BY too large for RAM)

Real-world metrics:

Average page load time: 50-55 seconds
Database CPU during query: 85-95%
Frontend timeout rate: 15% (users refresh before completion)
User complaints: Daily

The problem compounds with scale:

100 campaigns × 5,000 recipients = 500,000 rows
Every pagination request triggers full table scan
No indexes can help GROUP BY on this scale
Temporary tables spill to disk

The Solution: Pre-Aggregated Summary Table

The key insight: compute once at write time, read many times at query time. Instead of aggregating 500,000 rows on every page load, store pre-computed summaries when the campaign completes.

Architecture Comparison

Before (Read-Time Aggregation):

┌─────────────┐      ┌──────────────────────────────┐
│  Dashboard  │─────▶│  GROUP BY Query              │
│  Request    │      │  (500k rows → 100 groups)    │
└─────────────┘      └──────────────────────────────┘
                              │ 50 seconds
                              ▼
                     ┌─────────────────┐
                     │  Aggregated     │
                     │  Results        │
                     └─────────────────┘

After (Write-Time Aggregation):

┌──────────────┐      ┌────────────────────┐
│ Campaign     │─────▶│  Save Summary      │
│ Completes    │      │  (1 insert)        │
└──────────────┘      └────────────────────┘
                              │ 10ms
                              ▼
                     ┌─────────────────────┐
                     │ Summary Table       │
                     │ (100 pre-computed   │
                     │  rows)              │
                     └─────────────────────┘
                              ▲
                              │ 50ms
┌─────────────┐      ┌────────────────────┐
│  Dashboard  │─────▶│  Simple SELECT     │
│  Request    │      │  (no aggregation)  │
└─────────────┘      └────────────────────┘

Implementation: Step by Step

Step 1: Create Summary Table Entity

Key decision: What fields to store in the summary?

// src/entities/push-notification-conditional.entity.ts
import { Entity, Column, PrimaryColumn, CreateDateColumn, Index } from 'typeorm';

@Entity({ name: 'push_notification_conditional', schema: 'c3tv' })
@Index(['sent_at'])  // For date-based sorting
@Index(['title'])    // For search
@Index(['topic'])    // For filtering
@Index(['push_onoff', 'marketing_onoff'])  // For consent filtering
export class PushNotificationConditional {
  @PrimaryColumn({ type: 'varchar', length: 200 })
  job_id: string;  // e.g., "conditional-7b721b91-2733-4d01-92c5-6882b45e833e"

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

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

  // ===== Timing Information =====
  @Column({ type: 'datetime', precision: 6 })
  sent_start_at: Date;  // First notification sent

  @Column({ type: 'datetime', precision: 6 })
  sent_end_at: Date;    // Last notification sent

  @Column({ type: 'datetime' })
  sent_at: Date;        // Representative timestamp for sorting

  // ===== Pre-Computed Statistics =====
  @Column({ type: 'int', default: 0 })
  total_count: number;    // Total notifications attempted

  @Column({ type: 'int', default: 0 })
  success_count: number;  // Successfully sent

  @Column({ type: 'int', default: 0 })
  failure_count: number;  // Failed to send

  // ===== Campaign Filters (for audit trail) =====
  @Column({ type: 'varchar', length: 1, nullable: true })
  push_onoff: string | null;  // 'Y' | 'N' | null

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

  @Column({ type: 'varchar', length: 20, nullable: true })
  topic: string | null;  // 'app_info' | 'ads_info'

  @Column({ type: 'varchar', length: 10, nullable: true })
  gender: string | null;

  @Column({ type: 'int', nullable: true })
  age_min: number | null;

  @Column({ type: 'int', nullable: true })
  age_max: number | null;

  @Column({ type: 'varchar', length: 50, nullable: true })
  auth_kind: string | null;

  @Column({ type: 'varchar', length: 100, nullable: true })
  platform_type: string | null;

  @Column({ type: 'int', nullable: true })
  days_since_login_min: number | null;

  @Column({ type: 'int', nullable: true })
  days_since_login_max: number | null;

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

  @Column({ type: 'tinyint', width: 1, nullable: true })
  bible_missionary_applications: number | null;

  @Column({ type: 'int', nullable: true })
  limit: number | null;  // Campaign send limit

  // ===== Error Tracking =====
  @Column({ type: 'varchar', length: 500, nullable: true })
  error_message: string | null;

  @Column({ type: 'varchar', length: 50, nullable: true })
  error_code: string | null;

  @Column({ type: 'varchar', length: 50, nullable: true })
  campaign_id: string | null;

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

  constructor(data?: Partial) {
    if (data) {
      Object.assign(this, data);
    }
  }
}

Design principles:

Denormalization: Store campaign filters alongside stats (audit trail)
Timing precision: DATETIME(6) for millisecond-accurate duration tracking
Strategic indexes: On commonly filtered/sorted columns
PrimaryKey = job_id: Natural key, ensures uniqueness

Step 2: Register Entity in Module

// src/routes/firebase/firebase.module.ts
import { PushNotificationConditional } from 'src/entities/push-notification-conditional.entity';

@Module({
  imports: [
    TypeOrmModule.forFeature(
      [
        PushNotificationConditional,  // ✅ Add new entity
        PushNotificationLog,           // Existing detail logs
        // ... other entities
      ],
      'mysqlConnection'
    ),
  ],
  // ...
})
export class FirebaseModule {}

Step 3: Inject Repository in Service

// src/routes/firebase/firebase.service.ts
@Injectable()
export class FirebaseService {
  constructor(
    // ... other repositories

    @InjectRepository(PushNotificationConditional, 'mysqlConnection')
    private readonly conditionalRepository: Repository,
  ) {}
}

Step 4: Save Summary on Campaign Completion

Critical placement: Save summary after all notifications are sent, before marking job complete.

// firebase.service.ts - sendConditionalNotifications method
async sendConditionalNotifications(
  jobData: ConditionalNotificationParams
): Promise {

  // ... existing campaign execution logic ...
  // (Database query, token collection, FCM sending, etc.)

  let totalSent = 0;
  let totalFailed = 0;
  let firstChunkStartTime: Date | null = null;
  let lastChunkEndTime: Date | null = null;

  // Process chunks and send notifications...
  for (let chunkIndex = 0; chunkIndex < chunks.length; chunkIndex++) {
    // Send notifications, track stats...
    totalSent += response.successCount;
    totalFailed += response.failureCount;
  }

  // ===== Final logging =====
  console.log(`\n========== [${jobData.jobId}] Campaign Completed ==========`);
  console.log(`Total sent: ${totalSent.toLocaleString()}`);
  console.log(`Total failed: ${totalFailed.toLocaleString()}`);
  console.log(`Success rate: ${((totalSent / (totalSent + totalFailed)) * 100).toFixed(2)}%`);
  console.log(`Duration: ${firstChunkStartTime} → ${lastChunkEndTime}`);
  console.log(`====================================================\n`);

  // ✅ NEW: Save pre-computed summary to database
  if (jobData.jobId) {
    try {
      const summary = new PushNotificationConditional({
        job_id: jobData.jobId,
        title: jobData.title,
        content: jobData.content,

        // Timing
        sent_start_at: firstChunkStartTime || new Date(),
        sent_end_at: lastChunkEndTime || new Date(),
        sent_at: lastChunkEndTime || new Date(),  // For sorting

        // Statistics (the key value!)
        total_count: totalSent + totalFailed,
        success_count: totalSent,
        failure_count: totalFailed,

        // Campaign filters (for audit trail)
        push_onoff: jobData.push_onoff || null,
        marketing_onoff: jobData.marketing_onoff || null,
        topic: jobData.topic || null,
        gender: jobData.gender || null,
        age_min: jobData.ageMin || null,
        age_max: jobData.ageMax || null,
        auth_kind: jobData.auth_kind || null,
        platform_type: jobData.platform_type || null,
        days_since_login_min: jobData.daysSinceLastLoginMin || null,
        days_since_login_max: jobData.daysSinceLastLoginMax || null,
        bible_missionary_yn: jobData.bible_missionary_yn || null,

        // ⚠️  Type conversion: boolean → number (MySQL TINYINT)
        bible_missionary_applications: 
          jobData.bible_missionary_applications !== null && 
          jobData.bible_missionary_applications !== undefined
            ? (jobData.bible_missionary_applications ? 1 : 0)
            : null,

        limit: jobData.limit || null,

        // Error information
        error_message: totalFailed > 0 ? 'Partial failure' : null,
        error_code: null,
        campaign_id: jobData.data?.campaignId || null,
      });

      await this.conditionalRepository.save(summary);
      console.log(`[${jobData.jobId}] ✅ Summary saved to database`);

    } catch (dbError) {
      console.error(`[${jobData.jobId}] ❌ Failed to save summary:`, dbError);
      // Don't throw - summary save failure shouldn't fail campaign
    }
  }

  return {
    success: true,
    jobId: jobData.jobId,
    sendStats: { totalSent, totalFailed },
    // ... other result data
  };
}

Why save summary here?

✅ Have complete statistics (all chunks processed)
✅ Campaign marked complete
✅ Safe to display in dashboard
✅ Failure to save summary doesn't affect campaign success

Step 5: Rewrite Query to Use Summary Table

The transformation: From aggregation query to simple lookup.

// firebase.service.ts - getConditionalSendList method
async getConditionalSendList(
  dto: ConditionalSendListReqDto,
): Promise {
  try {
    const { 
      page = 1, 
      size = 10, 
      search, 
      is_success, 
      start_date, 
      end_date, 
      sort_by = 'sent_at', 
      sort_order = 'DESC' 
    } = dto;

    const startTime = Date.now();
    console.log(`[getConditionalSendList] Request started - Page: ${page}, Size: ${size}`);

    // ===== Build Query (No GROUP BY!) =====
    let query = this.conditionalRepository
      .createQueryBuilder('cond')
      .select([
        'cond.job_id AS job_id',
        'cond.title AS title',
        'cond.content AS content',
        'cond.sent_start_at AS sent_start_at',
        'cond.sent_end_at AS sent_end_at',
        'cond.total_count AS total_count',        // ← Pre-computed!
        'cond.success_count AS success_count',    // ← Pre-computed!
        'cond.failure_count AS failure_count',    // ← Pre-computed!
        'cond.sent_at AS sent_at',
        'cond.push_onoff AS push_onoff',
        'cond.marketing_onoff AS marketing_onoff',
        'cond.topic AS topic',
        'cond.error_message AS error_message',
        'cond.error_code AS error_code',
        'cond.campaign_id AS campaign_id',
        'cond.`limit` AS `limit`',  // ⚠️  Backticks: 'limit' is MySQL reserved word
      ]);

    // ===== Apply Filters (Simple WHERE clauses) =====
    if (search?.trim()) {
      query.andWhere(
        '(cond.title LIKE :search OR cond.content LIKE :search)',
        { search: `%${search}%` }
      );
    }

    if (start_date?.trim()) {
      query.andWhere('cond.sent_at >= :start_date', { 
        start_date: `${start_date} 00:00:00` 
      });
    }

    if (end_date?.trim()) {
      query.andWhere('cond.sent_at <= :end_date', { 
        end_date: `${end_date} 23:59:59` 
      });
    }

    if (is_success && is_success !== 'all') {
      if (is_success === 'true') {
        query.andWhere('cond.success_count > 0');
      } else {
        query.andWhere('cond.failure_count > 0');
      }
    }

    // ===== Sorting =====
    const sortColumn = {
      'title': 'cond.title',
      'created_at': 'cond.regdate',
      'sent_at': 'cond.sent_at'
    }[sort_by] || 'cond.sent_at';

    query.orderBy(sortColumn, sort_order);

    // ===== Pagination (SQL-level, no memory buffering) =====
    const skip = (page - 1) * size;
    query.offset(skip).limit(size);

    console.log('[getConditionalSendList] SQL:', query.getSql());

    // Execute query
    const rawResults = await query.getRawMany();
    const queryTime = Date.now() - startTime;
    console.log(`[getConditionalSendList] Query completed - ${queryTime}ms`);

    // ===== Transform to DTO =====
    const items = rawResults.map(row => {
      const totalCount = Number(row.total_count) || 0;
      const successCount = Number(row.success_count) || 0;

      return {
        job_id: row.job_id,
        title: row.title,
        content: row.content,
        sent_start_at: row.sent_start_at,
        sent_end_at: row.sent_end_at,
        total_count: totalCount,
        success_count: successCount,
        failure_count: Number(row.failure_count) || 0,
        success_rate: totalCount > 0 
          ? parseFloat(((successCount / totalCount) * 100).toFixed(2))
          : 0,  // ← Computed in application (cheap)
        sent_at: row.sent_at,
        push_onoff: row.push_onoff,
        marketing_onoff: row.marketing_onoff,
        topic: row.topic,
        error_message: row.error_message,
        error_code: row.error_code,
        campaign_id: row.campaign_id,
        limit: row.limit || undefined,
      };
    });

    // ===== Total Count (Still needs separate query, but fast) =====
    let countQuery = this.conditionalRepository
      .createQueryBuilder('cond')
      .select('COUNT(*)', 'count');

    // Apply same filters...
    if (search?.trim()) {
      countQuery.andWhere(
        '(cond.title LIKE :search OR cond.content LIKE :search)',
        { search: `%${search}%` }
      );
    }
    // ... (same filter logic as main query)

    const countResult = await countQuery.getRawOne();
    const totalCount = parseInt(countResult?.count || '0');

    // ===== Summary Stats (Optional, for dashboard) =====
    const summary = await this.getConditionalSendSummary();

    const totalTime = Date.now() - startTime;
    console.log(`[getConditionalSendList] Total time: ${totalTime}ms (${items.length} items, ${totalCount} total)`);

    return {
      page,
      size,
      totalCount,
      totalPages: Math.ceil(totalCount / size),
      items,
      summary,  // { total: 150, success: 145, failed: 5, today: 12 }
    };

  } catch (error) {
    console.error(`[getConditionalSendList] Error:`, error);
    throw new HttpException(
      {
        statusCode: 500,
        message: 'Failed to fetch campaign list',
        error: error?.message || 'Unknown error',
      },
      HttpStatus.INTERNAL_SERVER_ERROR,
    );
  }
}

Key improvements:

No GROUP BY: Direct SELECT from pre-computed data
Index-friendly filters: All WHERE clauses use indexed columns
SQL-level pagination: OFFSET/LIMIT avoids loading all rows
Simple aggregation: Success rate computed in application (cheap)

Step 6: Add Summary Stats Helper Method

For dashboard metrics (total campaigns, today's campaigns, etc.):

// firebase.service.ts
private async getConditionalSendSummary(): Promise {
  try {
    console.log('[getConditionalSendSummary] Fetching summary stats');

    // Total campaigns
    const total = await this.conditionalRepository.count();

    // Successful campaigns
    const success = await this.conditionalRepository
      .createQueryBuilder('cond')
      .where('cond.success_count > 0')
      .getCount();

    // Failed campaigns
    const failed = await this.conditionalRepository
      .createQueryBuilder('cond')
      .where('cond.failure_count > 0')
      .getCount();

    // Today's campaigns
    const now = new Date();
    const todayStart = new Date(now.getFullYear(), now.getMonth(), now.getDate(), 0, 0, 0);
    const todayEnd = new Date(now.getFullYear(), now.getMonth(), now.getDate(), 23, 59, 59);

    const today = await this.conditionalRepository
      .createQueryBuilder('cond')
      .where('cond.sent_at >= :todayStart', { todayStart })
      .andWhere('cond.sent_at <= :todayEnd', { todayEnd })
      .getCount();

    console.log(`[getConditionalSendSummary] Stats: total=${total}, success=${success}, failed=${failed}, today=${today}`);

    return { total, success, failed, today };

  } catch (error) {
    console.error('[getConditionalSendSummary] Error:', error);
    // Return zeros on error (don't break dashboard)
    return { total: 0, success: 0, failed: 0, today: 0 };
  }
}

Error Handling: The Debugging Journey

Issue 1: Type Mismatch (boolean → number)

Error encountered:

Type 'boolean' is not assignable to type 'number'.
  bible_missionary_applications: boolean | null  // MSSQL entity
  bible_missionary_applications: number | null   // MySQL entity

Root cause: Different database type mappings

MSSQL BIT → TypeScript boolean
MySQL TINYINT(1) → TypeScript number

Solution: Explicit type conversion

bible_missionary_applications: 
  jobData.bible_missionary_applications !== null && 
  jobData.bible_missionary_applications !== undefined
    ? (jobData.bible_missionary_applications ? 1 : 0)  // boolean → 0 or 1
    : null,

Lesson: Always check database type mappings across different RDBMS.

Issue 2: Missing `summary` Field

Error from frontend:

{
  "statusCode": 500,
  "message": "Failed to fetch campaign list"
}

Root cause: Frontend expected summary field that didn't exist in response.

Solution: Add getConditionalSendSummary() method and include in response:

const summary = await this.getConditionalSendSummary();

return {
  page,
  size,
  totalCount,
  totalPages,
  items,
  summary,  // ← Added
};

Lesson: Test with actual frontend integration, not just Postman.

Issue 3: MySQL Reserved Word ('limit')

Error encountered:

ER_PARSE_ERROR: You have an error in your SQL syntax near 
'limit FROM `push_notification_conditional`'

Root cause: Column named limit conflicts with SQL LIMIT keyword.

Failed attempt: Rename column (breaking change for existing data)

Correct solution: Escape with backticks

// ❌ Wrong
'cond.limit AS limit'

// ✅ Correct
'cond.`limit` AS `limit`'

Generated SQL:

SELECT 
  cond.`limit` AS `limit`  -- Backticks tell MySQL this is column name
FROM push_notification_conditional cond
LIMIT 10;  -- This is the SQL keyword

Lesson: Always check for reserved words when naming columns. Better yet, avoid them entirely (e.g., send_limit).

Performance Results

Query Execution Time

Operation	Before (GROUP BY)	After (Summary Table)	Improvement
Dashboard load (page 1)	48-52s	45-55ms	1,000x faster
Dashboard load (page 5)	50-55s	48-52ms	1,000x faster
Search query	52-58s	50-60ms	1,000x faster
Filter by date	45-50s	40-50ms	1,000x faster

Database Metrics

Before:

EXPLAIN SELECT job_id, COUNT(*), SUM(CASE...) 
FROM push_notification_log 
WHERE job_id LIKE 'conditional-%'
GROUP BY job_id;

+------+-------+--------+----------+-------------+
| type | rows  | Extra  | CPU cost | Disk reads  |
+------+-------+--------+----------+-------------+
| ALL  | 524K  | Using  | 8,947    | 15,234 pages|
|      |       | filesort|          |             |
+------+-------+--------+----------+-------------+

After:

EXPLAIN SELECT * 
FROM push_notification_conditional
ORDER BY sent_at DESC
LIMIT 10;

+------+-------+---------+-----------+------------+
| type | rows  | Extra   | CPU cost  | Disk reads |
+------+-------+---------+-----------+------------+
| ref  | 10    | Using   | 12        | 1 page     |
|      |       | index   |           |            |
+------+-------+---------+-----------+------------+

System Resource Impact

During dashboard usage (5 concurrent users):

Metric	Before	After	Improvement
DB CPU usage	85-95%	5-12%	8x reduction
Query wait time	45-50s	< 0.1s	500x reduction
Memory (temp tables)	2.4 GB	0 MB	Eliminated
Disk I/O	15K page reads	~10 page reads	1,500x reduction

Frontend user experience:

Metric	Before	After
Time to first render	52s	0.5s
Timeout rate	15%	0%
User complaints	Daily	None (past 3 months)

Production Deployment Strategy

Database Migration

Created MySQL table:

CREATE TABLE `push_notification_conditional` (
  `job_id` VARCHAR(200) NOT NULL COMMENT 'BullMQ Job ID (Primary Key)',
  `title` VARCHAR(200) NOT NULL,
  `content` TEXT NOT NULL,
  `sent_start_at` DATETIME(6) NOT NULL,
  `sent_end_at` DATETIME(6) NOT NULL,
  `sent_at` DATETIME NOT NULL,
  `total_count` INT NOT NULL DEFAULT 0,
  `success_count` INT NOT NULL DEFAULT 0,
  `failure_count` INT NOT NULL DEFAULT 0,
  `push_onoff` VARCHAR(1) NULL,
  `marketing_onoff` VARCHAR(1) NULL,
  `topic` VARCHAR(20) NULL,
  `gender` VARCHAR(10) NULL,
  `age_min` INT NULL,
  `age_max` INT NULL,
  `auth_kind` VARCHAR(50) NULL,
  `platform_type` VARCHAR(100) NULL,
  `days_since_login_min` INT NULL,
  `days_since_login_max` INT NULL,
  `bible_missionary_yn` VARCHAR(1) NULL,
  `bible_missionary_applications` TINYINT(1) NULL,
  `limit` INT NULL,
  `error_message` VARCHAR(500) NULL,
  `error_code` VARCHAR(50) NULL,
  `campaign_id` VARCHAR(50) NULL,
  `regdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`job_id`),
  INDEX `idx_sent_at` (`sent_at`),
  INDEX `idx_title` (`title`),
  INDEX `idx_topic` (`topic`),
  INDEX `idx_consent` (`push_onoff`, `marketing_onoff`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Backfilling Historical Data

One-time migration script:

-- Backfill summary table with historical campaigns
INSERT INTO push_notification_conditional (
  job_id, title, content,
  sent_start_at, sent_end_at, sent_at,
  total_count, success_count, failure_count,
  push_onoff, marketing_onoff, topic,
  gender, age_min, age_max, auth_kind, platform_type,
  days_since_login_min, days_since_login_max,
  bible_missionary_yn, bible_missionary_applications,
  `limit`, error_message, error_code, campaign_id, regdate
)
SELECT 
  log.job_id,
  MAX(log.title) AS title,
  MAX(log.content) AS content,
  MIN(log.sent_at) AS sent_start_at,
  MAX(log.sent_at) AS sent_end_at,
  MAX(log.sent_at) AS sent_at,
  COUNT(*) AS total_count,
  SUM(CASE WHEN log.is_success = 1 THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN log.is_success = 0 THEN 1 ELSE 0 END) AS failure_count,
  MAX(log.push_onoff) AS push_onoff,
  MAX(log.marketing_onoff) AS marketing_onoff,
  MAX(log.topic) AS topic,
  MAX(log.gender) AS gender,
  MAX(log.age_min) AS age_min,
  MAX(log.age_max) AS age_max,
  MAX(log.auth_kind) AS auth_kind,
  MAX(log.platform_type) AS platform_type,
  MAX(log.days_since_login_min) AS days_since_login_min,
  MAX(log.days_since_login_max) AS days_since_login_max,
  MAX(log.bible_missionary_yn) AS bible_missionary_yn,
  MAX(CASE 
    WHEN log.bible_missionary_applications = 1 THEN 1 
    WHEN log.bible_missionary_applications = 0 THEN 0 
    ELSE NULL 
  END) AS bible_missionary_applications,
  MAX(log.limit) AS `limit`,
  MAX(CASE WHEN log.is_success = 0 THEN log.error_message ELSE NULL END) AS error_message,
  MAX(CASE WHEN log.is_success = 0 THEN log.error_code ELSE NULL END) AS error_code,
  MAX(log.campaign_id) AS campaign_id,
  MAX(log.created_at) AS regdate
FROM c3tv.push_notification_log AS log
WHERE log.job_id LIKE 'conditional-%'
GROUP BY log.job_id
ORDER BY sent_at DESC;

-- Execution time: ~3 minutes for 500K rows → 150 summary rows
-- Rows inserted: 150 campaigns

Result:

mysql> SELECT COUNT(*) FROM push_notification_conditional;
+----------+
| COUNT(*) |
+----------+
|      150 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM push_notification_log WHERE job_id LIKE 'conditional-%';
+----------+
| COUNT(*) |
+----------+
|   524,831|
+----------+
1 row in set (2.47 sec)

-- 524K rows → 150 rows = 3,498:1 compression ratio

Deployment Steps

Create new table (no downtime)

mysql> SOURCE create_summary_table.sql;

Deploy code with dual-write logic (writes to both tables)

git push origin main
# CI/CD deploys via Blue-Green (zero downtime)

Backfill historical data (background job)

mysql> SOURCE backfill_summary.sql;
# Runs in background, doesn't block queries

Switch read path to summary table (instant)

// Already deployed in step 2
// getConditionalSendList now uses summary table

Monitor for 1 week (verify correctness)

# Compare counts
SELECT job_id, COUNT(*) FROM push_notification_log GROUP BY job_id;
SELECT job_id, total_count FROM push_notification_conditional;
# Should match perfectly

Archive old implementation (cleanup)

// Remove GROUP BY logic (keep for rollback reference)

Trade-offs and Considerations

Storage Cost

Additional storage:

Summary table size:
  150 rows × ~500 bytes/row = 75 KB

Detail log table size:
  524,831 rows × ~300 bytes/row = 157 MB

Storage overhead: 0.05% (negligible)

Cost: < $0.01/month (AWS RDS pricing)

Data Consistency

Challenge: Summary table could become stale if:

Campaign update logic bypasses summary save
Database transaction fails partially
Concurrent updates to same campaign

Mitigation strategies:

Atomic writes: Save summary in same transaction as campaign completion

await this.connection.transaction(async manager => {
  // Mark campaign complete
  await manager.save(campaign);
  // Save summary
  await manager.save(summary);
});

Idempotency: Use INSERT ... ON DUPLICATE KEY UPDATE

await this.conditionalRepository
  .createQueryBuilder()
  .insert()
  .into(PushNotificationConditional)
  .values(summary)
  .orUpdate(['total_count', 'success_count', 'failure_count'], ['job_id'])
  .execute();

Reconciliation job: Daily check for discrepancies

// Cron job: Compare summary vs. actual aggregation
const mismatches = await this.checkSummaryConsistency();
if (mismatches.length > 0) {
  // Alert ops team
  await this.notifySlack('Summary table inconsistency detected');
}

Write Performance Impact

Additional write per campaign:

Before: 0 writes to summary table
After: 1 INSERT per campaign

Cost: ~10ms per campaign (negligible compared to 2-5 minute send duration)

Trade-off analysis:

Write cost: +10ms per campaign
Read benefit: -50,000ms per page load
ROI: 5,000x

When NOT to Use Summary Tables

❌ Don't use summary tables when:

Data changes frequently (summary becomes stale fast)
Write-heavy workload (summary updates become bottleneck)
Complex aggregations that vary per query (can't pre-compute)
Real-time analytics required (summary has lag)

✅ Perfect for summary tables when:

Read-heavy workload (like admin dashboards)
Expensive aggregations (GROUP BY on millions of rows)
Stable dimensions (campaign stats don't change post-completion)
Acceptable eventual consistency (10ms lag is fine)

Lessons Learned

1. Measure Before Optimizing

I initially tried optimizing indexes and query structure. Waste of time. The problem was fundamental: GROUP BY on 500K rows will always be slow.

Key insight: If your query scans > 100K rows, consider materialized views or summary tables.

2. Pre-Compute at Write Time

The pattern:

Expensive to compute, cheap to store → Pre-compute and store
Cheap to compute, expensive to store → Compute on-the-fly

Examples:

✅ Summary stats (expensive aggregation) → Pre-compute
❌ User's age (cheap YEAR(NOW()) - birth_year) → Compute on-the-fly

3. Database Reserved Words Are Sneaky

Columns to avoid: limit, order, group, select, where, table, index, etc.

Better alternatives: send_limit, sort_order, user_group

When stuck: Use backticks (MySQL), double quotes (PostgreSQL), square brackets (SQL Server)

4. Type Conversions Matter Across Databases

When working with multi-database systems:

Document type mappings
Use explicit conversions
Test with actual data from both databases

Example mapping:

MSSQL BIT        → boolean
MySQL TINYINT(1) → number (not boolean!)
MSSQL DATETIME2  → Date (precision 7)
MySQL DATETIME   → Date (precision 0)

5. Don't Forget Backfilling

New summary table is empty initially. Two options:

Backfill immediately: Users see all historical data (my choice)
Progressive fill: Only new campaigns appear

For admin dashboards, backfilling is usually necessary.

Conclusion

By implementing a summary table pattern, I transformed a frustratingly slow admin dashboard into a snappy, responsive interface. The 1,000x performance improvement came from a fundamental shift in approach: compute once at write time, read many times.

The key decisions:

Pre-aggregate at campaign completion (write-time computation)
Store comprehensive statistics (total, success, failure, timing)
Maintain audit trail (store campaign filters for compliance)
Strategic indexing (optimize for common query patterns)
Handle reserved words (backtick escaping for 'limit' column)

For high-traffic dashboards displaying aggregated data, summary tables aren't just an optimization—they're a necessity for acceptable user experience.

Key Takeaways

GROUP BY on 500K+ rows will always be slow (no index can save you)
Summary tables trade storage space for query speed (worth it for read-heavy workloads)
Pre-compute expensive aggregations at write time, not read time
Always backfill historical data when introducing summary tables
Test with actual production data (Postman tests aren't enough)
Handle database reserved words with proper escaping
Document type mappings when working across multiple RDBMS
Monitor summary table consistency with periodic reconciliation jobs

Measuring Push Notification Engagement: Tracking Open Rates for 500K+ Campaigns

Sangwoo Lee — Mon, 19 Jan 2026 17:18:00 +0000

How I built a click tracking system to measure real engagement rates, analyze user behavior by hour, and optimize notification timing for 500,000+ push campaigns?

"I sent 500,000 notifications. How many people actually opened them?"

After launching my push notification system, my product manager asked the most important question. I could track delivery rates (80% success), but I had no idea about engagement. Did users just dismiss the notification? Or did they actually open the app?

Turns out, measuring this is harder than it sounds. Push notifications don't automatically report "opens" like emails do. You have to build your own tracking system. In this post, I'll show you how I implemented click tracking for 500,000+ user campaigns and discovered surprising insights about user behavior.

The challenge: push notifications are fire-and-forget

Unlike emails (which can embed tracking pixels), push notifications offer no built-in open tracking. Firebase Cloud Messaging only tells you:

✅ Was the notification delivered to the device?
❌ Did the user see it?
❌ Did the user tap it?
❌ When did they tap it?

Why this matters:

Without engagement data, you're flying blind:

Can't measure ROI of campaigns
Can't optimize send times
Can't A/B test notification copy
Can't identify disengaged users

Solution: track clicks when the app opens

My approach: When a user taps a notification and the app opens, send a tracking event to my backend.

High-level flow:

1. Backend sends notification → (includes job_id + member_seq in data payload)
2. User taps notification → App launches
3. App extracts job_id + member_seq from notification data
4. App calls POST /message/click → Backend records click
5. Later: Backend calculates open_rate = clicks / successful_sends

Key insight: Firebase includes the data payload when opening from a notification. I use this to pass tracking identifiers.

Implementation Part 1: Adding tracking data to notifications

First, I modified my send logic to include tracking identifiers:

// firebase.service.ts - sendConditionalNotifications
async sendConditionalNotifications(jobData: ConditionalNotificationParams) {
  // ... DB query logic ...

  for (let chunkIndex = 0; chunkIndex < chunks.length; chunkIndex++) {
    const chunk = chunks[chunkIndex];

    const messages = chunk.map((token) => {
      // ✅ Get member_seq for this token
      const memberSeq = tokenToSeqMap.get(token);

      return {
        token,
        notification: { 
          title: jobData.title, 
          body: jobData.content 
        },
        data: {
          // ✅ Tracking identifiers
          job_id: jobData.jobId,           // Campaign ID
          member_seq: String(memberSeq),   // User ID

          // Other app routing data
          screen_name: jobData.data.screen_name,
          landing_url: jobData.data.landing_url,
          // ...
        },
      };
    });

    // Send batch
    const response = await sendEachWithRetry(messaging, messages, false);

    // ... logging and progress tracking ...
  }
}

Why include member_seq?

I need to know WHICH user clicked, not just that SOMEONE clicked. This allows:

Deduplication (same user clicking multiple times)
Per-user engagement analysis
Segmentation (e.g., "iOS users click 2x more than Android")

Implementation Part 2: Click tracking API endpoint

I built a simple REST API that apps call when opening from a notification:

// firebase.controller.ts
@Post('click')
@Public() // ✅ No authentication required (called by mobile app)
@ApiOperation({
  summary: '푸시 알림 클릭 기록',
  description: `모바일 앱에서 푸시 알림 클릭 시 호출하는 API입니다.

  주요 기능:
  - 클릭 이벤트 기록 (job_id + member_seq)
  - 중복 클릭 방지 (동일 회원의 동일 푸시는 1회만 기록)
  - 클릭률(오픈율) 계산을 위한 데이터 수집`,
})
async recordClick(@Body() dto: RecordClickDto) {
  try {
    console.log(`[recordClick] 클릭 기록 요청 - Job: ${dto.job_id}, Member: ${dto.member_seq}`);

    const recorded = await this.firebaseService.recordClick(dto);

    return {
      statusCode: 200,
      message: recorded ? '클릭 기록 완료' : '이미 기록된 클릭',
      data: { recorded },
    };

  } catch (error) {
    console.error('[recordClick] 클릭 기록 실패:', error);
    throw new HttpException(
      {
        statusCode: 500,
        message: '클릭 기록에 실패했습니다.',
        data: null,
      },
      HttpStatus.INTERNAL_SERVER_ERROR,
    );
  }
}

Request DTO:

// record-click.dto.ts
export class RecordClickDto {
  @IsString()
  job_id: string; // e.g., "conditional-a1b2c3d4-e5f6-7890-abcd-ef1234567890"

  @IsInt()
  member_seq: number; // User ID

  @IsOptional()
  @IsDateString()
  clicked_at?: string; // Client can provide timestamp (or server uses current time)
}

Implementation Part 3: Database table with deduplication

I created a dedicated table for click events:

// push-notification-click-log.entity.ts
@Entity({ name: 'push_notification_click_log' })
@Index(['job_id', 'member_seq'], { unique: true }) // ✅ Prevent duplicate clicks
@Index(['job_id']) // For aggregation queries
@Index(['regdate']) // For hourly analysis
export class PushNotificationClickLog {
  @PrimaryGeneratedColumn({ type: 'int' })
  seq: number;

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

  @Column({ type: 'int' })
  member_seq: number;

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

Key design: UNIQUE constraint on (job_id, member_seq)

This prevents double-counting if:

User taps notification multiple times
App crashes and retries the API call
Race condition between multiple app instances

Service logic: deduplication and error handling

// firebase.service.ts
async recordClick(dto: RecordClickDto): Promise {
  try {
    const clickedAt = dto.clicked_at ? new Date(dto.clicked_at) : getKSTDate();

    // ✅ Check if already recorded (application-level check)
    const existing = await this.clickLogRepository.findOne({
      where: {
        job_id: dto.job_id,
        member_seq: dto.member_seq,
      },
    });

    if (existing) {
      console.log(`[recordClick] 중복 클릭 무시 - Job: ${dto.job_id}, Member: ${dto.member_seq}`);
      return false; // Already recorded
    }

    // ✅ Save new click
    const clickLog = new PushNotificationClickLog({
      job_id: dto.job_id,
      member_seq: dto.member_seq,
      regdate: clickedAt,
    });

    await this.clickLogRepository.save(clickLog);

    console.log(`[recordClick] ✅ 클릭 기록 완료 - Job: ${dto.job_id}, Member: ${dto.member_seq}`);
    return true; // Newly recorded

  } catch (error) {
    // ✅ Database-level duplicate (race condition)
    if (error.code === 'ER_DUP_ENTRY' || error.number === 2627) {
      console.log(`[recordClick] 중복 클릭 감지 (DB 제약) - Job: ${dto.job_id}`);
      return false;
    }

    console.error(`[recordClick] 클릭 기록 실패:`, error);
    throw error;
  }
}

Two-level deduplication:

Application-level: Check findOne before insert (fast path)
Database-level: UNIQUE constraint catches race conditions (safety net)

This is important because mobile apps can have:

Slow networks (retry logic)
Background/foreground transitions (duplicate events)
Multiple processes (race conditions)

Calculating engagement metrics

Now the fun part: analyzing the data. I built a comprehensive stats API:

// firebase.service.ts
async getClickStats(jobId: string): Promise {
  // ===== 1. Send statistics =====
  const sendStats = await this.pushNotificationLog
    .createQueryBuilder('log')
    .select([
      'COUNT(*) AS total_sent',
      'SUM(CASE WHEN log.is_success = 1 THEN 1 ELSE 0 END) AS success_count',
      'SUM(CASE WHEN log.is_success = 0 THEN 1 ELSE 0 END) AS failure_count',
      "SUM(CASE WHEN log.error_type = 'invalid_token' THEN 1 ELSE 0 END) AS invalid_token_count",
      "SUM(CASE WHEN log.error_type = 'temporary' THEN 1 ELSE 0 END) AS temporary_error_count",
      'MIN(log.sent_at) AS first_sent_at', // For time-to-click calculation
    ])
    .where('log.job_id = :jobId', { jobId })
    .getRawOne();

  const totalSent = parseInt(sendStats?.total_sent || '0');
  const successCount = parseInt(sendStats?.success_count || '0');
  const invalidTokenCount = parseInt(sendStats?.invalid_token_count || '0');
  const temporaryErrorCount = parseInt(sendStats?.temporary_error_count || '0');
  const firstSentAt = sendStats?.first_sent_at;

  // ===== 2. Click statistics =====
  const clickStats = await this.clickLogRepository
    .createQueryBuilder('click')
    .select([
      'COUNT(DISTINCT click.member_seq) AS total_clicks', // ✅ DISTINCT for deduplication
      'MIN(click.regdate) AS first_click_at',
      'MAX(click.regdate) AS last_click_at',
    ])
    .where('click.job_id = :jobId', { jobId })
    .getRawOne();

  const totalClicks = parseInt(clickStats?.total_clicks || '0');

  // ===== 3. Key metrics =====

  // ✅ Main metric: Open rate (clicks / successful sends)
  const openRate = successCount > 0
    ? parseFloat(((totalClicks / successCount) * 100).toFixed(2))
    : 0;

  // ✅ Potential open rate (clicks / (success + temporary errors))
  const potentialReachable = successCount + temporaryErrorCount;
  const potentialOpenRate = potentialReachable > 0
    ? parseFloat(((totalClicks / potentialReachable) * 100).toFixed(2))
    : 0;

  // Overall click rate (reference only)
  const overallClickRate = totalSent > 0
    ? parseFloat(((totalClicks / totalSent) * 100).toFixed(2))
    : 0;

  // Delivery rate (from Part 5)
  const deliveryRate = totalSent > 0
    ? parseFloat(((potentialReachable / totalSent) * 100).toFixed(2))
    : 0;

  // ===== 4. Time analysis =====

  let firstClickDelayMinutes: number | undefined;
  if (firstSentAt && clickStats.first_click_at) {
    const delayMs = new Date(clickStats.first_click_at).getTime() - new Date(firstSentAt).getTime();
    if (delayMs > 0) {
      firstClickDelayMinutes = parseFloat((delayMs / 60000).toFixed(2));
    }
  }

  // Average time-to-click
  let avgTimeToClickMinutes: number | undefined;
  if (firstSentAt && totalClicks > 0) {
    const avgClickTime = await this.clickLogRepository
      .createQueryBuilder('click')
      .select('AVG(TIMESTAMPDIFF(MINUTE, :sentAt, click.regdate)) AS avg_minutes')
      .where('click.job_id = :jobId', { jobId })
      .setParameter('sentAt', firstSentAt)
      .getRawOne();

    avgTimeToClickMinutes = avgClickTime?.avg_minutes 
      ? parseFloat(parseFloat(avgClickTime.avg_minutes).toFixed(2))
      : undefined;
  }

  // ===== 5. Hourly distribution =====
  const hourlyDistribution = await this.clickLogRepository
    .createQueryBuilder('click')
    .select([
      'HOUR(click.regdate) AS hour',
      'COUNT(*) AS clicks',
    ])
    .where('click.job_id = :jobId', { jobId })
    .groupBy('HOUR(click.regdate)')
    .orderBy('hour', 'ASC')
    .getRawMany();

  return {
    job_id: jobId,

    // Send statistics
    total_sent: totalSent,
    success_count: successCount,
    failure_count: totalSent - successCount,
    invalid_token_count: invalidTokenCount,
    temporary_error_count: temporaryErrorCount,

    // Click statistics
    total_clicks: totalClicks,

    // ✅ Key engagement metrics
    open_rate: openRate,                    // Main metric
    potential_open_rate: potentialOpenRate,
    overall_click_rate: overallClickRate,

    // Delivery metrics
    delivery_rate: deliveryRate,
    success_rate: parseFloat(((successCount / totalSent) * 100).toFixed(2)),

    // Time analysis
    first_click_delay_minutes: firstClickDelayMinutes,
    avg_time_to_click_minutes: avgTimeToClickMinutes,

    // Hourly distribution
    hourly_distribution: hourlyDistribution.map(row => ({
      hour: parseInt(row.hour),
      clicks: parseInt(row.clicks),
    })),
  };
}

Example API response

GET /message/conditional-sends/conditional-abc-123/click-stats

{
  "statusCode": 200,
  "message": "클릭률 통계 조회 성공",
  "data": {
    "job_id": "conditional-abc-123",

    // Send statistics
    "total_sent": 500000,
    "success_count": 410000,
    "failure_count": 90000,
    "invalid_token_count": 75000,
    "temporary_error_count": 15000,

    // Click statistics
    "total_clicks": 61500,

    // ✅ Key metrics
    "open_rate": 15.0,              // 61,500 / 410,000 = 15%
    "potential_open_rate": 14.5,    // 61,500 / 425,000 = 14.5%
    "overall_click_rate": 12.3,     // 61,500 / 500,000 = 12.3%

    // Delivery metrics
    "delivery_rate": 85.0,           // 425,000 / 500,000 = 85%
    "success_rate": 82.0,            // 410,000 / 500,000 = 82%

    // Time analysis
    "first_click_delay_minutes": 0.5,  // First click 30 seconds after send
    "avg_time_to_click_minutes": 12.3, // Average: 12 minutes

    // Hourly distribution
    "hourly_distribution": [
      { "hour": 9, "clicks": 8200 },   // 9 AM: Peak engagement
      { "hour": 10, "clicks": 7100 },
      { "hour": 11, "clicks": 5900 },
      { "hour": 12, "clicks": 4200 },  // Noon: Lunch break
      { "hour": 13, "clicks": 3100 },
      { "hour": 14, "clicks": 2800 },
      // ... more hours
    ]
  }
}

Understanding the metrics

1. Open Rate (Main Metric)

open_rate = (total_clicks / success_count) * 100
          = (61,500 / 410,000) * 100
          = 15.0%

This is my primary engagement metric. It tells me: "Of all notifications that successfully reached devices, what percentage did users actually click?"

Why not include failures? Invalid tokens never reached a device, so they can't be clicked. Including them would artificially deflate the open rate.

2. Potential Open Rate

potential_open_rate = (total_clicks / (success + temporary)) * 100
                    = (61,500 / 425,000) * 100
                    = 14.5%

This includes temporary errors that might have succeeded on retry. Useful for predicting what the open rate COULD be if I fixed transient issues.

3. Overall Click Rate (Reference)

overall_click_rate = (total_clicks / total_sent) * 100
                   = (61,500 / 500,000) * 100
                   = 12.3%

This includes all sends (even invalid tokens). Useful for reporting to non-technical stakeholders who just care about the total campaign size.

Mobile app integration

iOS (Swift):

// AppDelegate.swift
func application(
    _ application: UIApplication,
    didReceiveRemoteNotification userInfo: [AnyHashable: Any],
    fetchCompletionHandler completionHandler: @escaping (UIBackgroundFetchResult) -> Void
) {
    // Extract tracking data
    guard let jobId = userInfo["job_id"] as? String,
          let memberSeqStr = userInfo["member_seq"] as? String,
          let memberSeq = Int(memberSeqStr) else {
        completionHandler(.noData)
        return
    }

    // Record click
    let clickData: [String: Any] = [
        "job_id": jobId,
        "member_seq": memberSeq,
        "clicked_at": ISO8601DateFormatter().string(from: Date())
    ]

    // Send to backend (async)
    APIClient.shared.post(
        url: "https://messaging.goodtv.co.kr/message/click",
        body: clickData
    ) { result in
        switch result {
        case .success:
            print("✅ Click recorded: \(jobId)")
        case .failure(let error):
            print("⚠️ Click recording failed: \(error)")
        }
        completionHandler(.newData)
    }
}

Android (Kotlin):

// MyFirebaseMessagingService.kt
override fun onMessageReceived(remoteMessage: RemoteMessage) {
    val jobId = remoteMessage.data["job_id"]
    val memberSeq = remoteMessage.data["member_seq"]?.toIntOrNull()

    if (jobId != null && memberSeq != null) {
        // Record click when notification is opened
        recordClick(jobId, memberSeq)
    }

    // Show notification
    showNotification(remoteMessage)
}

private fun recordClick(jobId: String, memberSeq: Int) {
    val clickData = mapOf(
        "job_id" to jobId,
        "member_seq" to memberSeq,
        "clicked_at" to SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", Locale.US)
            .apply { timeZone = TimeZone.getTimeZone("UTC") }
            .format(Date())
    )

    CoroutineScope(Dispatchers.IO).launch {
        try {
            apiClient.post("https://messaging.goodtv.co.kr/message/click", clickData)
            Log.d(TAG, "✅ Click recorded: $jobId")
        } catch (e: Exception) {
            Log.w(TAG, "⚠️ Click recording failed: ${e.message}")
        }
    }
}

Real production insights (6 months of data)

After implementing click tracking, I analyzed 200+ campaigns:

Overall averages:

Open rate: 12-18% (industry standard: 10-15%)
First click: 30 seconds after send
Average time-to-click: 15 minutes
Peak engagement: 9-10 AM (morning commute)

Surprising findings:

1. Time-of-day matters more than day-of-week

I tested sending at different times:

Send Time	Open Rate	Avg Time-to-Click
6 AM	8.2%	45 min
9 AM	18.3%	12 min
12 PM	11.5%	28 min
3 PM	9.7%	35 min
6 PM	14.2%	18 min
9 PM	10.1%	52 min

Winner: 9 AM (commute time) - 18.3% open rate, 12-minute average click time.

2. Push notification copy length matters

Title Length	Body Length	Open Rate
<20 chars	<50 chars	16.2%
20-40 chars	50-100 chars	14.8%
>40 chars	>100 chars	9.5%

Takeaway: Keep it short. Titles under 20 characters perform 70% better than long titles.

3. iOS vs Android engagement

// Query by platform
const iosOpenRate = await this.getOpenRateByPlatform(jobId, 'ios');
const androidOpenRate = await this.getOpenRateByPlatform(jobId, 'android');

// Results (average across 200 campaigns):
// iOS: 17.2% open rate
// Android: 13.1% open rate

iOS users click notifications 31% more than Android users. Why?

Notification prominence (iOS notifications are larger)
App badge culture (iOS users check apps more frequently)
Demographic differences (iOS users in my app are older, more engaged)

4. Follow-up notifications have diminishing returns

I tested sending follow-up notifications to non-clickers:

Attempt	Open Rate	Cumulative Open Rate
1st	15.2%	15.2%
2nd	4.8%	19.1%
3rd	1.2%	20.0%

Takeaway: Second attempts add 25% more opens. Third attempts are barely worth it.

5. Content type affects engagement

Notification Type	Open Rate	Avg Time-to-Click
Daily Bible verse	22.1%	8 min
Event announcement	18.3%	15 min
App feature update	9.2%	45 min
Generic reminder	6.5%	120 min

Takeaway: Content users explicitly signed up for (daily verses) performs 3x better than marketing messages.

Edge cases and gotchas

Gotcha 1: Background vs foreground clicks

Problem: iOS and Android handle notifications differently based on app state.

iOS:

App in foreground: userNotificationCenter(_:willPresent:) fires
App in background: userNotificationCenter(_:didReceive:) fires
App terminated: application(_:didFinishLaunchingWithOptions:) fires

Android:

App in any state: onMessageReceived() fires consistently

Solution: Track clicks in all handlers, rely on database UNIQUE constraint to prevent duplicates.

Gotcha 2: Delayed clicks (user opens app later)

Problem: User sees notification, doesn't tap it, opens app 2 hours later through home screen. Should I count this as a "click"?

Answer: No. I only count direct taps on the notification. This is intentional:

Reflects true notification effectiveness
Matches industry standards
Prevents false positives (user might have forgotten about the notification)

Gotcha 3: Network failures during click recording

Problem: User taps notification, app opens, but network is offline. Click never recorded.

Solution: Implement client-side queue with retry:

// Android example
class ClickQueue {
    private val pendingClicks = mutableListOf()

    fun enqueue(jobId: String, memberSeq: Int) {
        pendingClicks.add(ClickEvent(jobId, memberSeq, System.currentTimeMillis()))
        tryFlush()
    }

    private fun tryFlush() {
        if (!isNetworkAvailable()) return

        pendingClicks.toList().forEach { event ->
            try {
                apiClient.post("/message/click", event)
                pendingClicks.remove(event)
            } catch (e: Exception) {
                // Keep in queue for next attempt
            }
        }
    }
}

In production, this catches ~2% of clicks that would otherwise be lost to network issues.

Gotcha 4: Multiple devices per user

Problem: User has app installed on phone and tablet. Taps notification on phone, then later on tablet. Counted twice?

Answer: Yes, and that's intentional. Each device represents a separate engagement opportunity. However, I can adjust the query:

// Option A: Count unique users (current implementation)
SELECT COUNT(DISTINCT member_seq) AS total_clicks FROM push_notification_click_log;

// Option B: Count all clicks (including multi-device)
SELECT COUNT(*) AS total_clicks FROM push_notification_click_log;

I chose Option A (unique users) because it reflects unique people who engaged, not total taps.

Cost and performance considerations

Database growth:

My push_notification_click_log table grows at:

~15% open rate × 500K sends = 75K rows per campaign
50 campaigns/month = 3.75M rows/month
45M rows/year

Storage: ~100 bytes/row × 45M = 4.5 GB/year (negligible)

Index overhead: UNIQUE constraint adds ~5% write overhead (acceptable)

Query performance:

-- Aggregation query (used in getClickStats)
SELECT 
    COUNT(DISTINCT member_seq) AS total_clicks,
    HOUR(regdate) AS hour
FROM push_notification_click_log
WHERE job_id = 'conditional-abc-123'
GROUP BY HOUR(regdate);

With proper indexes: <50ms for 100K clicks
Without indexes: >5 seconds (unacceptable)

Optimization tip: If click tracking slows down sends, make the API call async:

// ✅ Don't block notification display
override fun onMessageReceived(remoteMessage: RemoteMessage) {
    // Show notification immediately
    showNotification(remoteMessage)

    // Record click asynchronously (don't block)
    CoroutineScope(Dispatchers.IO).launch {
        recordClick(jobId, memberSeq)
    }
}

Combining with dry-run validation (Part 5)

One powerful use case: predict open rates BEFORE sending.

// Step 1: Dry-run validation on 10K sample
const dryRunJobId = 'dryrun-campaign-123';
await sendConditionalNotifications({ ...params, isDryRun: true, limit: 10000 });

const deliveryStats = await getDeliveryStats(dryRunJobId);
// deliveryRate: 85% (425K reachable out of 500K)

// Step 2: Use historical open rates to predict engagement
const historicalOpenRate = 15.2; // From past campaigns
const predictedClicks = 500000 * (deliveryStats.deliveryRate / 100) * (historicalOpenRate / 100);

console.log(`
Prediction for 500K campaign:
- Estimated reachable: ${500000 * 0.85} = 425,000 devices
- Expected open rate: 15.2%
- Predicted clicks: ${Math.round(predictedClicks)} = 64,600 clicks
`);

// Step 3: Real send
const productionJobId = 'production-campaign-123';
await sendConditionalNotifications({ ...params, isDryRun: false, limit: 500000 });

// Step 4: Compare prediction vs reality
const actualStats = await getClickStats(productionJobId);
console.log(`
Actual results:
- Delivered: ${actualStats.success_count}
- Clicks: ${actualStats.total_clicks}
- Open rate: ${actualStats.open_rate}%

Prediction accuracy:
- Delivery: ${Math.abs(425000 - actualStats.success_count)} difference
- Clicks: ${Math.abs(64600 - actualStats.total_clicks)} difference
`);

In practice, my predictions are accurate within ±10% for delivery and ±15% for open rate.

Visualizing the data (bonus: dashboard)

I built a simple dashboard using this data:

// Dashboard API endpoint
@Get('dashboard/engagement-overview')
async getEngagementOverview(@Query('days') days: number = 30) {
  const campaigns = await this.getRecentCampaigns(days);

  const summary = {
    totalCampaigns: campaigns.length,
    totalSent: 0,
    totalClicks: 0,
    avgOpenRate: 0,
    bestPerforming: null,
    worstPerforming: null,
    hourlyTrends: [],
  };

  for (const campaign of campaigns) {
    const stats = await this.getClickStats(campaign.job_id);

    summary.totalSent += stats.total_sent;
    summary.totalClicks += stats.total_clicks;

    // Track best/worst
    if (!summary.bestPerforming || stats.open_rate > summary.bestPerforming.open_rate) {
      summary.bestPerforming = { job_id: campaign.job_id, open_rate: stats.open_rate };
    }
    if (!summary.worstPerforming || stats.open_rate < summary.worstPerforming.open_rate) {
      summary.worstPerforming = { job_id: campaign.job_id, open_rate: stats.open_rate };
    }
  }

  summary.avgOpenRate = (summary.totalClicks / summary.totalSent) * 100;

  return summary;
}

Example dashboard output:

📊 Last 30 Days Engagement Overview
====================================
Total Campaigns: 42
Total Sent: 18.5M notifications
Total Clicks: 2.8M
Average Open Rate: 15.1%

Best Performing:
  Job: conditional-dec-holiday-promo
  Open Rate: 24.3%

Worst Performing:
  Job: conditional-app-update-reminder
  Open Rate: 6.2%

Peak Engagement Hours:
  9 AM: 312K clicks
  10 AM: 287K clicks
  8 AM: 245K clicks

Key takeaways

1. Click tracking is simpler than you think

Add tracking IDs to notification data
Record clicks when app opens
Use UNIQUE constraint for deduplication

2. Open rate is the metric that matters

Not delivery rate (that's Part 5)
Not total clicks (inflated by duplicates)
Clicks / Successful Sends = true engagement

3. Timing is everything

9 AM: 18.3% open rate (best)
6 AM: 8.2% open rate (worst)
That's a 2.2x difference just from timing

4. Short and sweet wins

Titles <20 chars: 16.2% open rate
Titles >40 chars: 9.5% open rate
70% improvement from brevity

5. iOS > Android (for engagement)

iOS: 17.2% open rate
Android: 13.1% open rate
Consider separate strategies per platform

If you're sending push notifications at scale, click tracking is non-negotiable. It transforms notifications from "spray and pray" to data-driven engagement. My open rates improved 40% over 6 months just by optimizing send times and notification copy based on this data.

In my next post, I'll show you how I built a real-time analytics dashboard that combines all these metrics (delivery rate, open rate, hourly trends) into a single view that updates every 2 seconds.

[AWS] 7. AWS Route 53, DNS (Domain Name System), Routing Policies

Sangwoo Lee — Thu, 15 Jan 2026 03:21:04 +0000

What is DNS?

Domain Name System which translates the human friendly hostnames into the machine IP addresses
www.google.com → 172.217.18.36
DNS is the backbone of the Internet
DNS uses hierarchical naming structure (ex. .com, example.com, www.example.com, api.example.com)

DNS Terminologies

Domain Registrar: Amazon Route 53, GoDaddy, ...
DNS Records: A, AAAA, CNAME, NS, ...
Zone File: contains DNS records
Name Server: resolves DNS queries (Authoritative or Non-Authoritative)
Top Level Domain (TLD): .com, .us, .in, .gov, .org, ...
Second Level Domain (SLD): amazon.com, google.com, ...

How DNS Works

Amazon Route 53

A high available, scalable, fully managed and Authoritative DNS
- Authoritative = the customer (you) can update the DNS records
Route 53 is also a Domain Registrar
Ability to check the health of your resources
The only AWS service which provides 100% Availability SLA
Why Route 53? 53 is a reference to the traditional DNS port

Route 53 - Records

How you want to route traffic for a domain
Each record contains:
- Domain/subdomain Name - e.g., example.com
- Record Type - e.g., A or AAAA
- Value - e.g., 12.34.56.78
- Routing Policy - how Route 53 responds to queries
- TTL - amount of time the record cached at DNS Resolvers
Route 53 supports the following DNS record types:
- (must know) A / AAAA / CNAME / NS
- (advanced) CAA / DS / MX / NAPTR / PTR / SOA / TXT / SPF / SRV

Route 53 - Record Types

A - maps a hostname to IPv4
AAAA - maps a hostname to IPv6
CNAME - maps a hostname to another hostname
- The target is a domain name which must have an A or AAAA record
- Can't create a CNAME record for the top node of a DNS namespace (Zone Apex)
- Example: you can't create for example.com, but you can create for www.example.com
NS - Name Servers for the Hosted Zone
- Control how traffic is routed for a domain

Route 53 - Hosted Zones

A container for records that define how to route traffic to a domain and its subdomains
Public Hosted Zones - contains records that specify how to route traffic on the Internet (public domain names) ex) application1.mypublicdomain.com
Private Hosted Zones - contain records that specify how you route traffic within one or more VPCs (private domain names) ex) application1.company.internal
You pay $0.50 per month per hosted zone

Route 53 - Public vs Private Hosted Zones

Route 53 - Records TTL (Time To Live)

High TTL - e.g., 24hr
- Less traffic on Route 53
- Possibly outdated records
Low TTL - e.g., 60sec
- More traffic on Route 53 ($$)
- Records are outdated for less time
- Easy to change records
Except for Alias records, TTL is mandatory for each DNS record

CNAME vs Alias

AWS Resources (Load Balancer, CloudFront ..) expose an AWS hostname:
- lb-1234.us-east-2.elb.amazonaws.com and you want myapp.mydomain.com
CNAME:
- Points a hostname to any other hostname. (app.mydomain.com → blabla.anything.com)
- ONLY FOR NON ROOT DOMAIN (aka.something.mydomain.com)
Alisa:
- Points a hostname to an AWS Resource (app.mydomain.com → blabla.amazonaws.com)
- Works for ROOT DOMAIN and NON ROOT DOMAIN (aka mydomain.com)
- Free of charge
- Native health check

Route 53 - Alias Records

Maps a hostname to an AWS resource
An extension to DNS functionally
Automatically recognizes changes in the resource's IP addresses
Unlike CNAME, it can be used for the top node of a DNS namespace (Zone Apex), e.g.: example.com
Alias Record is always of type A/AAAA for AWS resources (IPv4 / IPv6)
You can't set the TTL

Route 53 - Alias Records Targets

Elastic Load Balancers
CloudFront Distributions
API Gateway
Elastic Beanstalk environments
S3 Websites
VPC Interface Endpoints
Global Accelerator accelerator
Route 53 record in the same hosted zone
You cannot set an ALIAS record for an EC2 DNS name

Route 53 - Routing Policies

Define how Route 53 responds to DNS queries
Don't get confused by the word "Routing"
- It's not the same as Load balancer routing which routes the traffic
- DNS does not route any traffic, it only responds to the DNS queries
Route 53 Supports the following Routing Policies
- (1) Simple
- (2) Weighted
- (3) Latency-based
- (4) Failover (Active-Passive)
- (5) Geolocation
- (6) Geoproximity (using Route 53 Traffic Flow feature)
- (7) Multi-Value Answer

(1) Routing Policies - Simple

Typically, route traffic to a single resource
Can specify multiple values in the same record
If multiple values are returned, a random one is chosen by the client
When Alias enabled, specify only one AWS resource
Can't be associated with Health Checks

(2) Routing Policies - Weighted

Control the % of the requests that go to each specific resource
Assign each record a relative weight:

DNS records must have the same name and type
Can be associated with Health Checks
Use cases: load balancing between regions, testing new application versions...
Assign a weight of 0 to a record to stop sending traffic to a resource
If all records have weight of 0, then all records will be returned equally

(3) Routing Policies - Latency-based

Redirect to the resource that has the least latency close to us
Super helpful when latency for users is a priority
Latency is based on traffic between users and AWS Regions
Germany users may be directed to the US (if that's the lowest latency)
Can be associated with Health Checks (has a failover capability)

Route 53 - Health Checks

HTTP Health Checks are only for public resources
Health Check → Automated DNS Failover:
- Health checks that monitor an endpoint (application, server, other AWS resource)
- Health checks that monitor other health checks (Calculated Health Checks)
- Health checks that monitor CloudWatch Alarms (full control !) - e.g., throttles of DynamoDB, alarms on RDS, custom metrics,... (helpful for private resources)
Health Checks are integrated with CW metrics

Health Checks - Monitor an Endpoint

About 15 global health checkers will check the endpoint health
- Healthy/Unhealthy Threshold - 3 (default)
- Interval - 30sec (can set to 10sec - higher cost)
- Supported protocol: HTTP, HTTPS and TCP
- If > 18% of health checkers report the endpoint is healthy, Route 53 considers it Healthy. Otherwise, it's Unhealthy
- Ability to choose which locations you want Route 53 to use
Health Checks pass only when the endpoint responds with the 2xx and 3xx status codes
Health Checks can be setup to pass / fail based on the text in the first 5120 bytes of the response
Configure you router / firewall to allow incoming requests from Route 53 Health Checkers

Route 53 - Calculated Health Checks

Combine the results of multiple Health Checks into a single Health Check
You can use OR, AND or NOT
Can monitor up to 256 Child Health Checks
Specify how many of the health checks need to pass to make the parent pass
Usage: perform maintenance to your website without causing all health checks to fail

Health Checks - Private Hosted Zones

Route 53 health checkers are outside the VPC
They can't access private endpoints (private VPC or on-premises resource)
You can create a CloudWatch Metric and associate a CloudWatch Alarm, then create a Health Check that checks the alarm itself

(4) Routing Policies - Failover (Active-Passive)

(5) Routing Policies - Geolocation

Different from Latency-based!
This routing is based on user location
Specify location by Continent, Country or by US State (if there's overlapping, most precise location selected)
Should create a "Default" record (in case there's no match on location)
Use cases: website localization, restrict content distribution, load balancing, ..
Can be associated with Health Checks

(6) Routing Policies - Geoproximity

Route traffic to your resources based on the geographic location of users and resources
Ability to shift more traffic to resources based on the defined bias
To change the size of the geographic region, specify bias values:
- To expand (1 to 99) - more traffic to the resource
- To shrink (-1 to -99) - less traffic to the resource
Resources can be:
- AWS resources (specify AWS region)
- Non-AWS resources (specify Latitude and Longitude)
You must use Route 53 Traffic Flow (advanced) to use this feature

Geoproximity Routing Policy

Geoproximity Routing Policy Higher bias in us-east-1

Routing Policies - IP-based Routing

Routing is based on clients' IP addresses
You provide a list of CIDRs for your clients and the corresponding endpoints / locations (user-IP-to-endpoint mappings)
Use cases: Optimize performance, reduce network costs..
Example: route end users from a particular ISP to a specific endpoint

Routing Policies - Multi-Value

Use when routing traffic to multiple resources
Route 53 return multiple values/resources
Can be associated with Health Checks (return only values for healthy resources)
Up to 8 healthy records are returned for each Multi-Value query
Multi-Value is not a substitute for having an ELB (Elastic Load Balancer)

Domain Registrar vs DNS Service

You buy or register your domain name with a Domain Registrar typically by paying annual charges (e.g., GoDaddy, Amazon Registrar Inc., ..)
The Domain Registrar usually provides you with a DNS service to manage your DNS records
But you can use another DNS service to manage your DNS records
Example: purchase the domain from GoDaddy and use Route 53 to manage your DNS records

GoDaddy as Registrar & Route 53 as DNS Service

3rd Party Registrar with Amazon Route 53

If you buy your domain on a 3rd party registrar, you can still use Route 53 as the DNS Service provider

Create a Hosted Zone in Route 53
Update NS Records on 3rd party website to use Route 53 Name Servers

Domain Registrar != DNS Service
But every Domain Registrar usually comes with some DNS features

Route 53 - Hybrid DNS

By Default, Route 53 Resolver automatically answers DNS queries for:
- Local domain names for EC2 instances
- Records in Private Hosted Zones
- Records in public Name Servers
Hybrid DNS - resolving DNS queries between VPC (Route 53 Resolver) and your networks (other DNS Resolvers)
Networks can be:
- VPC itself / Peered VPC
- On-premises Network (connected through Direct Connect or AWS VPN)

Route 53 - Resolver Endpoints

Inbound Endpoint
- allows your DNS Resolvers to resolve domain names for AWS resources (e.g., EC2 instances) and records in Private Hosted Zones

Outbound Endpoint
- Route 53 Resolver forwards DNS queries to your DNS Resolvers

[AWS] 6. AWS Fundamentals: RDS (Relational Database Service) + Amazon Aurora + ElastiCache

Sangwoo Lee — Thu, 15 Jan 2026 03:20:39 +0000

Amazon RDS Overview

RDS stands for Relational Database Service
It's a managed DB service for DB use SQL as a query language
It allows you to create databases in the cloud that are managed by AWS
- Postgres
- MySQL
- MariaDB
- Oracle
- Microsoft SQL Server
- IBM DB2
- Aurora (AWS Proprietary database)

Advantage over using RDS vs deploying DB on EC2

RDS is a managed service:
- Automated provisioning, OS patching
- Continuous backups and restore to specific timestamp (Point in Time Restore)!
- Monitoring dashboards
- Read replicas for improved read performance
- Multi AZ setup for DR (Disaster Recovery)
- Maintenance windows for upgrades
- Scaling capability (vertical and horizontal)
- Storage backed by EBS
But you can't SSH into your instance

RDS - Storage Auto Scaling

Helps you increase storage on your RDS DB instance dynamically
When RDS detects you are running out of free database storage, it scales automatically
Avoid manually scaling your database storage
You have to set Maximum Storage Threshold (maximum limit for DB storage)
Automatically modify storage if:
- Free storage is less than 10% of allocated storage
- Low-storage lasts at least 5 minutes
- 6 hours have passed since last modification
Useful for applications with unpredictable workloads

RDS Read Replicas for read scalability

Up to 15 Read Replicas
Within AZ, Cross AZ or Cross Region
Replicas is ASYNC, so reads are eventually consistent
Replicas can be promoted to their own DB
Applications must update the connection string to leverage read replicas

RDS Read Replicas - Use Cases

You have a production database that is taking on normal load
You want to run a reporting application to run some analytics
You create a Read Replica to run the new workload there
The production application is unaffected
Read replicas are used for SELECT(=read) only kind of statements (not INSERT, UPDATE, DELETE)

RDS Read Replicas - Network Cost

In AWS there's a network cost when data goes from one AZ to another
For RDS Read Replicas within the same region, you don't pay that fee

RDS Multi AZ (Disaster Recovery)

SYNC replication
One DNS name - automatic app failover to standby
Increase availability
Failover in case of loss of AZ, loss of network, instance or storage failure
No manual intervention in apps
Not used for scaling
Note: The Read Replicas be setup as Multi AZ for Disaster Recovery (DR)

RDS - From Single-AZ to Multi-AZ

Zero downtime operation (no need to stop the DB)
Just click on "modify" for the database
The following happens internally:
- A snapshot is taken
- A new DB is restored from the snapshot in a new AZ
- Synchronization is established between the two databases

RDS Custom

Managed Oracle and Microsoft SQL Server Database with OS and database customization
RDS: Automates setup, operation, and scaling of database in AWS
Custom: access to the underlying database and OS so you can
- Configure settings
- Install patches
- Enable native features
- Access the underlying EC2 Instance using SSH or SSM Session Manager
De-active Automation Mode to perform your customization, better to take a DB snapshot before
RDS vs RDS Custom
- RDS: entire database and the OS to be managed by AWS
- RDS Custom: full admin access to the underlying OS and the database

Amazon Aurora

Aurora is a proprietary technology from AWS (not open sourced)
Postgres and MySQL are both supported as Aurora DB (that means you drivers will work as if Aurora was a Postgres or MySQL database)
Aurora is "AWS cloud optimized" and claims 5x performance improvement over MySQL on RDS, over 3x the performance of Postgres on RDS
Aurora storage automatically grows in increments of 10GB, up to 256TB
Aurora can have up to 15 replicas and replication process is faster than MySQL (sub 10ms replica lag)
Failover in Aurora is instantaneous. It's HA native.
Aurora costs more than RDS (20% more) - but is more efficient

Aurora High Availability and Read Scaling

6 copies of your data across 3 AZ:
- 4 copies out of 6 needed for writes
- 3 copies out of 6 need for reads
- Self healing with peer-to-peer replication
- Storage is striped across 100s of volumes
One Aurora Instance takes writes (master)
Automated failover for master in less than 30 seconds
Master+ up to 15 Aurora Read Replicas serve reads
Support for Cross Region Replication

Aurora DB Cluster

Features of Aurora

Automatic fail-over
Backup and Recovery
Isolation and security
Industry compliance
Push-button scaling
Automated Patching with Zero Downtime
Advanced Monitoring
Routine Maintenance
Backtrack: restore data at any point of time without using backups

Aurora Replicas - Auto Scaling

Aurora - Custom Endpoints

Define a subset of Aurora Instances as a Custom Endpoint
Example: Run analytical queries on specific replicas

The Reader Endpoint is generally not used after defining Custom Endpoints

Aurora Serverless

Automated database instantiation and auto-scaling based on actual usage
Good for infrequent, intermittent of unpredictable workloads
No capacity planning needed
Pay per second, can be more cost-effective

Global Aurora

Aurora Cross Region Read Replicas:
- Useful for disaster recovery
- Simple to put in place
Aurora Global Database (recommended):
- I Primary Region (read/write)
- Up to 10 secondary (read-only) regions, replication lag is less than 1 second
- Up to 16 Read Replicas per secondary region
- Helps for decreasing latency
- Promoting another region (for disaster recovery) has an RTO of < 1minute
- Typical cross-region replication takes less than 1 second

Aurora Machine Learning

Enables you to add ML-based predictions to your applications via SQL
Simple, optimized, and secure integration between Aurora and AWS ML services
Supported services
- Amazon SageMaker (use with any ML model)
- Amazon Comprehend (for sentiment analysis)
You don't need to have ML experience
Use cases: fraud detection, ads targeting, sentiment analysis, product recommendations

Babelfish for Aurora PostgreSQL

Allow Aurora PostgreSQL to understand commands targeted for MS SQL Server (e.g., T-SQL)
Therefore Microsoft SQL Server based applications can work on Aurora PostgreSQL
Requires no to little code changes (using the same MS SQL Server client driver)
The same applications can be used after a migration of your database (using AWS SCT and DMS)

RDS Backups

Automated backups:
- Daily full backup of the database (during the backup window)
- Transaction logs are backed-up by RDS every 5minutes
- => ability to restore to any point in time (from oldest backup to 5minutes ago)
- 1 to 35 days of retention, set 0 to disable automated backups)
Manual DB Snapshots
- Manually triggered by the user
- Retention of backup for as long as you want
Trick: in a stopped RDS database, you will still pay for storage. If you plan on stopping it for a long time, you should snapshot & restore instead
Automated backups
- 1 to 35 days (cannot be disabled)
- point-in-time recovery in that timeframe
Manual DB Snapshots
- Manually triggered by the user
- Retention of backup for as long as you want

RDS & Aurora Restore options

Restoring a RDS / Aurora backup or a snapshot creates a new database
Restoring MySQL RDS database from S3
- Create a backup of your on-premises database
- Store it on Amazon S3 (object storage)
- Restore the backup file onto a new RDS instance running MySQL
Restoring MySQL Aurora cluster from S3
- Create a backup of your on-premises database using Percona XtraBackup
- Store the backup file on Amazon S3
- Restore the backup file onto a new Aurora cluster running MySQL

Aurora Database Cloning

Create a new Aurora DB Cluster from an existing one
Faster than snapshot & restore
Uses copy-on-write protocol
- Initially, the new DB cluster uses the same data volume as the original DB cluster (fast and efficient - no copying is needed)
- When updates are made to the new DB cluster data, then additional storage is allocated and data is copied to be separated
Very fast & cost-effective
Useful to create a "staging" database from a "production" database without impacting the production database

RDS & Aurora Security

At-rest encryption:
- Database master & replicas encryption using AWS KMS - must be defined as launch time
- If the master is not encrypted,, the read replicas cannot be encrypted
- To encrypt an un-encrypted database, go through a DB snapshot & restore as encrypted
In-flight encryption: TLS-ready by default, use the AWSTLS root certificates client-side
IAM Authentication: IAM roles to connect to your database (instead of username/pw)
Security Groups: Control Network access to your RDS / Aurora DB
No SSH available except on RDS Custom
Audit Logs can be enabled and sent to CloudWatch Logs for longer retention

Amazon RDS Proxy

Fully managed database proxy for RDS
Allows apps to pool and share DB connections established with the database
Improving database efficiency by reducing the stress on database resources (e.g., CPU, RAM) and minimize open connections (and timeouts)
Serverless, autoscaling, highly available (multi-AZ)
Reduced RDS & Aurora failover time by up to 66%
Supports RDS (MySQL, PostgreSQL, MariaDB, MSSQL Server) and Aurora (MySQL, PostgreSQL)
No cod changes required for most apps
Enforce IAM Authentication for DB, and securely store credentials in AWS Secrets Manager
RDS Proxy is never publicly accessible (must be accessed from VPC)

Amazon ElastiCache Overview

The same way RDS is to get managed Relational Databases
ElastiCache is to get managed Redis or Memcached
Caches are in-memory databases with really high performance, low latency
Helps reduce load off of databases for read intensive workloads
Helps make your application stateless
AWS takes care of OS maintenance / patching, optimizations, setup, configuration, monitoring failure recovery and backups
Using ElastiCache involves heavy application code changes

ElastiCache - Cache Security

ElastiCache supports IAM Authentication for Redis
IAM policies on ElastiCache are only used for AWS API-level security
Redis AUTH
- You can set a "password/token" when you create a Redis cluster
- This is an extra level of security for your cache (on top of security groups)
- Support SSL in flight encryption
Memcached
- Supports SASL-based authentication (advanced)

Patterns for ElastiCache

Lazy Loading: all the read data is cached, data can become stale in cache
Write Through: Adds or update data in the cache when written to a DB (no stale data)
Session Store: store temporary session data in a cache (using TTL features)
Quote: There are only two hard things in Computer Science: cache invalidation and naming things

ElastiCache - Redis Use Case

Gaming Leaderboards are computationally complex
Redis Sorted sets guarantee both uniqueness and element ordering
Each time a new element added, it's ranked in real time, then added in correct order

List of Ports to be familiar with

Here's a list of standard ports you should see at least once. You shouldn't remember them (the exam will not test you on that), but you should be able to differentiate between an important (HTTPS - port 443) and a database port (PostgreSQL - port 5432)
Important ports:
- FTP: 21
- SSH: 22
- SFTP: 22(same as SSH)
- HTTP: 80
- HTTPS: 443
vs RDS Database ports:
- PostgreSQL: 5432
- MySQL: 3306
- Oracle RDS: 1521
- MSSQL Server: 1433
- MariaDB: 3306 (same as MySQL)
- Aurora: 5432 (if PostgreSQL compatible) or 3306(if MySQL compatible)

Don't stress out on remember those, just read that list once today and once before going into the exam and you should be all set :)

Remember, you should just be able to differentiate an "Important Port" vs an "RDS database Port".

[AWS] 5. High Availability and Scalability, Elastic Load Balancer (ELB), Auto Scaling Groups (ASG)

Sangwoo Lee — Thu, 15 Jan 2026 03:20:25 +0000

Scalability & High Availability

Scalability means that an application / system can handle greater loads by adapting
There are two kinds of scalability:
- Vertical Scalability
- Horizontal Scalability (=elasticity)
Scalability is linked but different to High Availability
Let's deep dive into the distinction, using a call center as an example

(1) Vertical Scalability

Vertically scalability means increasing the size of the instance
For example, your application runs on a t2.micro
Scaling that application vertically means running it on a t2.large
Vertical scalability is very common for non distributed systems, such as a database
RDS, ElastiCache are services that can scale vertically
There's usually a limit to how much you can vertically scale (hardware limit)

(2) Horizontal Scalability

Horizontal Scalability means increasing the number of instances / system for your application
Horizontal scaling implies distributed systems
This is very common for web applications / modern applications
It's easy to horizontally scale thanks the cloud offerings such as Amazon EC2

(3) High Availability

High Availability usually goes hand in hand with horizontal scaling
High Availability means running your application / system in at least 2 data center (== Availability Zones)
The goal of high availability is to survive a data center loss
The high availability can be passive (for RDS Multi AZ for example)
The high availability can be active (for horizontal scaling)

High Availability & Scalability For EC2

Vertical Scaling: Increase instance size (=scale up/down)
- From: t2.nano - 0.5G of RAM, 1 vCPU
- To: u-12tb1.metal - 12.3TB of RAM, 448 vCPUs
Horizontal Scaling: Increase number of instances (=scale out/in)
- Auto Scaling Group
- Load Balancer
High Availability: Run instances for the same application across multi AZ
- Auto Scaling Group multi AZ
- Load Balancer multi AZ

What is load balancing?

Load Balances are servers that forward traffic to multiple servers (e.g., EC2 instances) downstream

Why use a load balancer?

Spread load across multiple downstream instances
Expose a single point of access (DNS) to your application
Seamlessly handle failures of downstream instances
Do regular health checks to your instances
Provide SSL termination (HTTPS) for your websites
Enforce stickiness with cookies
High availability across zones
Separate public traffic from private traffic

Why use an Elastic Load Balancer?

An Elastic Load Balancer is a managed load balancer
- AWS guarantees that it will be working
- AWS takes care of upgrades, maintenance, high availability
- AWS provides only a few configuration knobs
It costs less to setup your own load balancer but it will be a lot more effort on your end
It is integrated with many AWS offerings / services
- EC2, EC2 Auto Scaling Groups, Amazon ECS
- AWS Certificate Manager (ACM), CloudWatch
- Route 53, AWS WAF, AWS Global Accelerator

Health Checks

Health Checks are crucial for Load Balancers
They enable the load balancer to know if instances it forwards traffic to are available to reply to requests
The health check is done on a port and a route (/health is common)
If the response is not 200(OK), then the instance is unhealthy

Types of load balancer of AWS

AWS has 4kinds of managed Load Balancers
(1) Classic Load Balancer (v1 - old generation) - 2009 - CLB
- HTTP, HTTPS, TCP, SSL (secure TCP)
(2) Application Load Balancer (v2 - new generation) - 2016 - ALB
- HTTP, HTTPS, WebSocket
(3) Network Load Balancer (v2 - new generation) - 2017 - NLB
- TCP, TLS(secure TCP), UDP
(4) Gataway Load Balancer - 2020 - GWLB
- Operates at layer 3 (Network layer) - IP Protocol

Overall, it is recommended to use the newer generation load balancers as they provide more features
Some load balancers can be setup as internal (private) or external (public) ELBs (Elastic Load Balancers)

Load Balancer Security Groups

(1) Application Load Balancer (ALB) (v2)

Application load balancer is Layer 7 (HTTP)
Load balancing to multiple HTTP applications across machines (target groups)
Load balancing to multiple applications on the same machine (ex: containers)
Support for HTTP/2 and WebSocket
Support redirects (from HTTP to HTTPS for example)
Routing tables to different target groups:
- Routing based on path in URL (example.com/users & example.com/posts)
- Routing based on hostname in URL (one.example.com & other.example.com)
- Routing based on Query String, Headers (example.com/users?id=123&order=false)
ALB are a great fit for micro services & container-based application (example: Docker&Amazon ECS)
Has a port mapping feature to redirect to a dynamic port in ECS
In comparison, we'd need multiple Classic Load Balancer per application

Application Load Balancer (v2) HTTP Based Traffic

Application Load Balancer (v2) Target Groups

EC2 instances (can be managed by an Auto Scaling Group) - HTTP
ECS tasks (managed by ECS itself) - HTTP
Lambda functions - HTTP request is translated into a JSON event
IP Addresses - must be private IPs
ALB can route to multiple target groups
Health checks are at the target group level

Application Load Balancer (v2) Query Strings / Parameters Routing

Fixed hostname (XXX.region.elb.amazonaws.com)
The application servers don't see the IP of the client directly
- The true IP of the client is inserted in the header X-Forwarded-For
- We can also get Port (X-Forwarded-Port) and photo (X-Forwarded-Proto)

(2) Network Load Balancer (NLB) (v2)

Network load balancers (Layer 4) allow to:
- Forward TCP & UDP traffic to your instances
- Handle million of request per seconds
- Ultra-low latency
NLB has one static IP per AZ, and supports assigning Elastic IP (helpful for whitelisting specific IP)
NLB are used for extreme performance, TCP or UDP traffic

Network Load Balancer (v2) TCP (Layer 4) Based Traffic

Network Load Balancer - Target Groups

EC2 instances
IP addresses - must be private IPs
Application Load Balancer
Health Checks support the TCP, HTTP and HTTPS Protocols

(3) Gateway Load Balancer (GWLB)

Deploy, scale, and manage a fleet of 3rd party network virtual appliances in AWS
Example: Firewalls, Intrusion Detection and Prevention Systems, Deep Packet Inspection System, payload manipulation,...
Operates at Layer 3 (Network Layer) - IP Packets
Combines the following functions:
- Transparent Network Gateway - single entry/exit for all traffic
- Load Balancer - distributes traffic to your virtual appliances
Uses the GENEVE protocol on port 6081

Gataway Load Balancer - Target Groups

EC2 instances
IP addresses - must be private IPs

Sticky Session (Session Affinity)

It is possible to implement stickiness so that the same client is always redirected to the same instance behind a load balancer
This works for Classic Load Balancer, Application Load Balancer, and Network Load Balancer
The "cookie" used for stickiness has an expiration date you control
Use cases: make sure the user doesn't lose his session data
Enabling stickiness may bring imbalance to the load over the backend EC2 instances

Sticky Session - Cookie Names

Application-based Cookies
- Custom cookie ① Generated by the target ② Can include any custom attributes required by the application ③ Cookie name must be specified individually for each target group ④ Don't use AWSALB, AWSALBAPP, or AWSALBTG (reserved for use by the ELB)
- Application cookie ① Generated by the load balancer ② Cookie name is AWSALBAPP
Duration-based Cookies
- Cookie generated by the load balancer
- Cookie name is AWSALB for ALB, AWSLEB for CLB

Cross-Zone Load Balancing

Application Load Balancer
- Enabled by default (can be disabled at the Target Group level)
- No charges for inter AZ data
Network Load Balancer & Gateway Load Balancer
- Disabled by default
- You pay charge ($) for inter AZ data if enabled
Classic Load Balancer
- Disabled by default
- No charges for inter AZ data if enabled

SSL/TLS - Basic

An SSL Certificate allows traffic between your clients and your load balancer to be encrypted in transit (in-flight encryption)
SSL = Secure Sockets Layer, used to encrypt connections
TLS = Transport Layer Security, which is a newer version
Nowadays, TLS certificates are mainly used, but people still refer as SSL
Public SSL certificates are issued by Certificate Authorities (CA)
Comodo, Symantec, GoDaddy, GlobalSign, Digicert, Letsencrypt, etc..
SSL certificates have an expiration date (you set) and must be renewed

Load Balancer - SSL (Secure Sockets Layer) Certificates

The load balancer uses an X.509 certificate (SSL/TLS server certificate)
You can manage certificates using ACM (AWS Certificate Manager)
You can create upload your own certificates alternatively
HTTPS listener:
- You must specify a default certificate
- You can add an optional list of certs to support multiple domains
- Clients can use SNI(Server Name Indication) to specify the hostname they reach
- Ability to specify a security policy to support older versions of SSL/TLS (legacy clients)

SSL (Secure Sockets Layer) - SNI (Server Name Indication)

SNI solves the problem of loading multiple SSL certificates onto one web server (to serve multiple websites)
It's a "newer" protocol, and requires the client to indicate the hostname of the target server in the initial SSL handshake
The server will then find the correct certificate, or return the default one
Only works for ALB & NLB (ALB : Application Load Balancer, NLB : Network Load Balancer. Both newer generation), CloudFront
Does not work for CLB (Classic Load Balancer, older genaration)

Elastic Load Balancer - SSL (Secure Sockets Layer) Certificates

Classic Load Balancer (v1)
- Support only one SSL certificate
- Must use multiple CLB for multiple hostname with multiple SSL certificates
Application Load Balancer (v2)
- Supports multiple listeners with multiple SSL certificates
- Uses Server Name Indications (SNI) to make it work
Network Load Balancer (v2)
- Supports multiple listeners with multiple SSL certificates
- Uses Server Name Indication (SNI) to make it work

Elastic Load Balancer - Connection Draining

Feature naming
- Connection Draining - for CLB
- Deregisteration Delay - for ALB & NLB
Time to complete "in-flight requests" while the instance is de-registering or unhealthy
Stops sending new requests to the EC2 instance which is de-registering
Between 1 to 3600 seconds (default: 300seconds)
Can be disabled (set value to 0)
Set to a low value if your requests are shot

What's an Auto Scaling Group?

In real-life, the load on your websites and application can change
In the cloud, you can create and get rid of servers very quickly
The goal of an Auto Scaling Group (ASG) is to:
- Scale out (add EC2 instances) to match an increased load
- Scale in (remove EC2 instances) to match a decreased load
- Ensure we have a minimum and a maximum number of EC2 instances running
- Automatically register new instances to a load balancer
- Re-create an EC2 instance in case a previous one is terminated (ex: if unhealthy)
ASG are fee (you only pay for the underlying EC2 instances)

Auto Scaling Group in AWS

Auto Scaling Group in AWS with Load Balancer

Auto Scaling Group Attributes

A Launch Template (older "Launch Configurations" are deprecated)
- AMI + Instance Type
- EC2 User Data
- EBS Volume
- Security Groups
- SSH Key Pair
- IAM Roles for your EC2 Instances
- Network + Subnets Information
- Load Balancer Information
Min Size / Max Size / Initial Capacity
Scaling Policies

Auto Scaling - CloudWatch Alarms & Scaling

It is possible to scale an ASG based on CloudWatch alarms
An alarm monitors a metric (such as Average CPU, or a custom metric)
Metrics such as Average CPU are computed for the overall ASG instances
Based on the alarm:
- We can create scale-out policies (increase the number of instances)
- We can create scale-in policies (decrease the number of instances)

Auto Scaling Group - Scaling Policies

Dynamic Scaling
- Target Tracking Scaling ① Simple to set-up ② Example: 1 want to average ASG CPU to stay at around 40%
- Simple / Step Scaling ① When a CloudWatch alarm is triggered (example CPU > 70%), then add 2 units ② When a CloudWatch alarm is triggered (example CPU < 30%), then remove 1
Scheduled Scaling
- Anticipate a scaling based on known usage patterns
- Example: increase the min capacity to 10 to 5pm on Friday
Predictive scaling: continuously forecast load and schedule scaling ahead

Good metrics to scale on

CPUUtilization: Average CPU utilization across your instances
RequestCountPerTarget: to make sure the number of requests per EC2 instances is stable
Average Network In/Out (if you're application is network bound)
Any custom metric (that you push using CloudWatch)

Auto Scaling Groups - Scaling Cooldowns

After a scaling activity happens, you are in the cooldown period (default 300seconds)
During the cooldown period, the ASG will not launch or terminate additional instances (to allow for metrics to stabilize)
Advice: Use a ready-to-use AMI to reduce configuration time in order to be serving request fasters and reduce the cooldown period

Beyond Token Validation: Measuring Real Device Delivery Rates with Firebase FCM

Sangwoo Lee — Tue, 13 Jan 2026 17:23:00 +0000

How we measure actual device reach rates by analyzing production send results, classify error types, and maintain 85%+ delivery rates across 50M monthly notifications

"Our dry-run predicted 79% delivery rate. Production hit 80.2%."

My engineering lead looked at me skeptically. "That's... suspiciously accurate. How do we know the tokens we validated are still valid when we actually send?"

He had a point. Token validation (dry-run) tells you if a token was valid at validation time. Actual delivery tells you if the token was valid at send time. In high-volume push systems with millions of users, those two moments can be minutes, hours, or days apart.

In this post, I'll show you how we measure real device delivery rates by analyzing actual FCM send results, classify errors to understand why messages fail, and use that data to maintain 85%+ delivery rates across 50 million monthly notifications.

The gap between validation and reality

Here's what most tutorials don't tell you: Firebase dry-run and actual sends can produce different results.

Dry-run validation (covered in previous post):

const response = await admin.messaging().send(message, true); // dry-run = true
// Returns: { success: true } - token is valid RIGHT NOW

Actual send (this post):

const response = await admin.messaging().send(message); // dry-run = false  
// Returns: { success: true } - message DELIVERED to device

Why the difference matters:

Scenario	Dry-run Result	Actual Send Result
User uninstalled app 5 minutes ago	✅ Success	❌ `invalid-registration-token`
User's device is offline	✅ Success	✅ Success (queued for delivery)
FCM quota exceeded	✅ Success	❌ `quota-exceeded`
Network timeout during send	✅ Success	❌ `unavailable`

The reality: You need BOTH validation and delivery measurement.

Validation (dry-run): Pre-send health check
Delivery measurement (actual send): Ground truth

This post focuses on the latter: measuring what actually happened.

Why measure delivery rates at all?

Business impact:

When we started measuring delivery rates in January 2025, we discovered:

Reported: "Sent to 500,000 users"
Reality: Delivered to 350,000 devices (70% delivery rate)
Gap: 150,000 users never had a chance to see the notification

Stakeholder impact:

Product Manager: "We sent to 500K users, why did only 80K click?"
Dev Team: "That's a 16% click rate!" 
Product Manager: "No, it's actually 23% click rate (80K / 350K actual reach)"

Without delivery rate tracking, we were misunderstanding our engagement metrics.

Cost impact:

Processing 150K invalid tokens = 24 minutes of server time per campaign
50 campaigns/month × 24 minutes = 20 hours/month wasted
Database writes for failed sends = 7.5M unnecessary operations/month

How Firebase FCM responses reveal delivery truth

Every FCM send returns detailed response data:

const response = await admin.messaging().sendEachForMulticast({
  tokens: ['token1', 'token2', ...],
  notification: { title: 'Hello', body: 'World' },
  data: { campaignId: '12345' },
});

console.log(response);

Response structure:

{
  successCount: 7243,
  failureCount: 2757,
  responses: [
    // Success example
    {
      success: true,
      messageId: 'projects/my-app/messages/0:1234567890'
    },

    // Failure examples
    {
      success: false,
      error: {
        code: 'messaging/invalid-registration-token',
        message: 'The registration token is not valid anymore'
      }
    },
    {
      success: false,
      error: {
        code: 'messaging/server-unavailable',
        message: 'The server is temporarily unavailable'
      }
    },
    // ... 10,000 total responses (one per token)
  ]
}

The goldmine: The error.code field tells you exactly WHY a send failed.

Error classification: the key to actionable metrics

Not all failures are equal. Some are permanent (bad token), others are temporary (retry might work).

// fcm-error-classifier.ts
export type FcmErrorType = 'invalid_token' | 'temporary' | 'quota' | 'other';

export function classifyFcmError(errorCode?: string): FcmErrorType {
  if (!errorCode) return 'other';

  // ❌ PERMANENT FAILURES - Token is completely dead
  const INVALID_TOKEN_ERRORS = [
    'messaging/invalid-registration-token',
    'messaging/registration-token-not-registered',
    'messaging/invalid-argument',
  ];

  if (INVALID_TOKEN_ERRORS.includes(errorCode)) {
    return 'invalid_token';
  }

  // ⏳ TEMPORARY FAILURES - Retry might succeed
  const TEMPORARY_ERRORS = [
    'messaging/unavailable',
    'messaging/internal-error',
    'messaging/server-unavailable',
    'messaging/timeout',
    'messaging/unknown-error',
  ];

  if (TEMPORARY_ERRORS.includes(errorCode)) {
    return 'temporary';
  }

  // 🚫 QUOTA EXCEEDED - Rate limiting
  if (errorCode === 'messaging/quota-exceeded') {
    return 'quota';
  }

  // ❓ UNKNOWN ERRORS
  return 'other';
}

Why this classification matters:

Invalid Token (❌ Permanent):

Action: Remove from database immediately
Retry: Pointless - will always fail
Cause: User uninstalled app, token expired, device was factory reset

Temporary (⏳ Retry-able):

Action: Retry after backoff delay
Retry: 70-80% success rate on retry
Cause: Network hiccup, FCM infrastructure maintenance, device temporarily offline

Quota (🚫 Rate Limit):

Action: Wait and retry later
Retry: 100% success after rate limit window
Cause: Too many requests too fast

Other (❓ Unknown):

Action: Log for investigation
Retry: Case-by-case decision
Cause: New error types, unexpected scenarios

Storing delivery results: the audit trail

We store every single send result in the database for analysis:

// push-notification-log.entity.ts
@Entity({ name: 'push_notification_log' })
export class PushNotificationLog {
  @PrimaryGeneratedColumn({ type: 'bigint' })
  id: number;

  @Column({ type: 'varchar', length: 200 })
  job_id: string; // e.g., "production-blackfriday-2025"

  @Column({ type: 'int' })
  member_seq: number; // User identifier

  @Column({ type: 'varchar', length: 500 })
  push_token: string;

  // ⭐ Success/failure tracking
  @Column({ type: 'bit', default: false })
  is_success: boolean;

  @Column({ type: 'datetime2' })
  sent_at: Date;

  // ⭐ Error details (null if success)
  @Column({ type: 'varchar', length: 50, nullable: true })
  error_code: string; // FCM error code

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

  // ⭐ Error classification for analytics
  @Column({ type: 'varchar', length: 30, nullable: true })
  error_type: 'invalid_token' | 'temporary' | 'quota' | 'other';

  // Campaign details
  @Column({ type: 'nvarchar', length: 200 })
  title: string;

  @Column({ type: 'nvarchar', length: 1000 })
  content: string;

  // Metadata
  @Column({ type: 'int', nullable: true })
  chunk_index: number; // Which batch was this part of

  @Column({ type: 'bit', nullable: true, default: false })
  is_dry_run: boolean; // false = production send

  // Retry tracking
  @Column({ type: 'int', default: 0 })
  retry_count: number; // How many times retried

  @Column({ type: 'bit', default: false })
  retry_success: boolean; // Did retry succeed?
}

Indexes for fast queries:

@Index(['job_id', 'is_success']) // Fast delivery rate queries
@Index(['job_id', 'error_type']) // Fast error breakdown
@Index(['sent_at']) // Time-series analysis
@Index(['member_seq', 'sent_at']) // User-level tracking
export class PushNotificationLog { /* ... */ }

Implementation: capturing delivery results

Here's how we save every send result:

// firebase.service.ts
async sendConditionalNotifications(
  jobData: ConditionalNotificationParams
): Promise {

  // ... Get target tokens from database ...

  const tokens = await this.getTargetTokens(jobData);
  const chunks = chunkArray(tokens, 500); // 500 tokens per chunk

  let totalSuccess = 0;
  let totalFailed = 0;
  const errorStats = {
    invalid_token: 0,
    temporary: 0,
    quota: 0,
    other: 0,
  };

  for (let chunkIndex = 0; chunkIndex < chunks.length; chunkIndex++) {
    const chunk = chunks[chunkIndex];

    // Build FCM messages
    const messages = chunk.map(token => ({
      token,
      notification: { 
        title: jobData.title, 
        body: jobData.content 
      },
      data: {
        job_id: jobData.jobId,
        campaign_id: jobData.campaignId,
      },
    }));

    try {
      // ★ ACTUAL SEND (not dry-run)
      const response = await this.firebaseApp
        .messaging()
        .sendEachForMulticast({
          tokens: chunk,
          notification: messages[0].notification,
          data: messages[0].data,
        });

      console.log(`
        Chunk ${chunkIndex + 1}/${chunks.length}:
        ✅ Success: ${response.successCount}
        ❌ Failed: ${response.failureCount}
      `);

      // ⭐ ANALYZE EACH RESPONSE
      for (let i = 0; i < response.responses.length; i++) {
        const resp = response.responses[i];
        const message = messages[i];

        // Create log entry
        const log = new PushNotificationLog({
          job_id: jobData.jobId,
          member_seq: await this.getMemberSeq(message.token),
          push_token: message.token,
          title: jobData.title,
          content: jobData.content,
          sent_at: new Date(),
          chunk_index: chunkIndex,
          is_dry_run: false, // ✅ Production send
        });

        if (resp.success) {
          // ✅ Success - message delivered
          log.is_success = true;
          totalSuccess++;

        } else {
          // ❌ Failure - classify error
          log.is_success = false;
          log.error_code = resp.error?.code;
          log.error_message = resp.error?.message;
          log.error_type = classifyFcmError(resp.error?.code);

          totalFailed++;
          errorStats[log.error_type]++;

          // Log detailed error for debugging
          console.error(`
            Token: ${message.token.substring(0, 30)}...
            Error: ${resp.error?.code}
            Message: ${resp.error?.message}
          `);
        }

        // ⭐ SAVE TO DATABASE
        await this.pushNotificationLog.save(log);
      }

      // Rate limiting (prevent quota-exceeded)
      if (chunkIndex < chunks.length - 1) {
        await delay(2000); // 2 seconds between chunks
      }

    } catch (error) {
      console.error(`Chunk ${chunkIndex + 1} failed completely:`, error);

      // Save error logs for entire chunk
      for (const message of messages) {
        const log = new PushNotificationLog({
          job_id: jobData.jobId,
          member_seq: await this.getMemberSeq(message.token),
          push_token: message.token,
          title: jobData.title,
          content: jobData.content,
          sent_at: new Date(),
          chunk_index: chunkIndex,
          is_success: false,
          error_code: 'CHUNK_FAILURE',
          error_message: error.message,
          error_type: 'other',
          is_dry_run: false,
        });

        await this.pushNotificationLog.save(log);
      }

      totalFailed += chunk.length;
    }
  }

  // Calculate final delivery rate
  const deliveryRate = totalSuccess > 0
    ? parseFloat(((totalSuccess / (totalSuccess + totalFailed)) * 100).toFixed(2))
    : 0;

  console.log(`
    ========== SEND COMPLETE ==========
    Total tokens: ${(totalSuccess + totalFailed).toLocaleString()}
    ✅ Delivered: ${totalSuccess.toLocaleString()} (${deliveryRate}%)
    ❌ Failed: ${totalFailed.toLocaleString()}

    Error breakdown:
    - Invalid tokens: ${errorStats.invalid_token.toLocaleString()}
    - Temporary errors: ${errorStats.temporary.toLocaleString()}
    - Quota errors: ${errorStats.quota.toLocaleString()}
    - Other errors: ${errorStats.other.toLocaleString()}
    ===================================
  `);

  return {
    success: true,
    totalTokens: totalSuccess + totalFailed,
    deliveredCount: totalSuccess,
    failedCount: totalFailed,
    deliveryRate,
    errorStats,
  };
}

Calculating delivery metrics: beyond simple success rates

With detailed error classification, we calculate multiple metrics:

// fcm-error-classifier.ts
export interface FcmErrorStats {
  total: number;
  success: number;
  invalidToken: number;
  temporary: number;
  quota: number;
  other: number;
  deliveryRate: number;
  successRate: number;
  retryableRate: number;
}

export function calculateErrorStats(
  logs: PushNotificationLog[]
): FcmErrorStats {
  const total = logs.length;
  let success = 0;
  let invalidToken = 0;
  let temporary = 0;
  let quota = 0;
  let other = 0;

  for (const log of logs) {
    if (log.is_success) {
      success++;
    } else {
      switch (log.error_type) {
        case 'invalid_token': invalidToken++; break;
        case 'temporary': temporary++; break;
        case 'quota': quota++; break;
        default: other++; break;
      }
    }
  }

  // ⭐ DELIVERY RATE
  // = Successfully delivered / Total attempted
  const deliveryRate = total > 0
    ? parseFloat(((success / total) * 100).toFixed(2))
    : 0;

  // ⭐ SUCCESS RATE (same as delivery rate for actual sends)
  const successRate = deliveryRate;

  // ⭐ RETRYABLE RATE
  // = Tokens that could succeed on retry / Total attempted
  const retryableRate = total > 0
    ? parseFloat((((temporary + quota) / total) * 100).toFixed(2))
    : 0;

  return {
    total,
    success,
    invalidToken,
    temporary,
    quota,
    other,
    deliveryRate,
    successRate,
    retryableRate,
  };
}

Example output:

const stats = calculateErrorStats(productionLogs);

console.log(`
📊 Delivery Analysis:
======================
Total Attempted: ${stats.total.toLocaleString()}

✅ Delivered: ${stats.success.toLocaleString()}
❌ Invalid Tokens: ${stats.invalidToken.toLocaleString()}
⏳ Temporary Errors: ${stats.temporary.toLocaleString()}
🚫 Quota Errors: ${stats.quota.toLocaleString()}
❓ Other Errors: ${stats.other.toLocaleString()}

📈 Key Metrics:
- Delivery Rate: ${stats.deliveryRate}%
- Retryable Rate: ${stats.retryableRate}%
- Permanent Failure Rate: ${((stats.invalidToken / stats.total) * 100).toFixed(2)}%
`);

Sample output:

📊 Delivery Analysis:
======================
Total Attempted: 500,000

✅ Delivered: 401,000
❌ Invalid Tokens: 87,000
⏳ Temporary Errors: 11,000
🚫 Quota Errors: 800
❓ Other Errors: 200

📈 Key Metrics:
- Delivery Rate: 80.2%
- Retryable Rate: 2.4%
- Permanent Failure Rate: 17.4%

Retry logic: recovering from temporary failures

Temporary errors (unavailable, timeout) often succeed on retry. Here's our retry strategy:

// fcm-retry.utils.ts
export async function sendEachWithRetry(
  messaging: admin.messaging.Messaging,
  messages: admin.messaging.Message[],
  isDryRun: boolean,
  retryConfig: {
    maxRetries: number;
    initialDelayMs: number;
    maxDelayMs: number;
  }
): Promise {

  const { maxRetries, initialDelayMs, maxDelayMs } = retryConfig;

  let attempt = 0;
  let lastError: Error | null = null;

  while (attempt <= maxRetries) {
    try {
      // Attempt send
      const response = await messaging.sendEach(messages, isDryRun);

      // If no failures, return immediately
      if (response.failureCount === 0) {
        return response;
      }

      // Check if failures are retryable
      const retryableIndices: number[] = [];

      response.responses.forEach((resp, idx) => {
        if (!resp.success) {
          const errorType = classifyFcmError(resp.error?.code);

          if (errorType === 'temporary' || errorType === 'quota') {
            retryableIndices.push(idx);
          }
        }
      });

      // If no retryable failures, return current response
      if (retryableIndices.length === 0) {
        console.log(`No retryable errors. Returning after attempt ${attempt + 1}`);
        return response;
      }

      // Last attempt - return as-is
      if (attempt === maxRetries) {
        console.log(`Max retries (${maxRetries}) reached. Returning with ${retryableIndices.length} remaining failures.`);
        return response;
      }

      // Prepare retry batch
      const retryMessages = retryableIndices.map(idx => messages[idx]);

      // Exponential backoff
      const delayMs = Math.min(
        initialDelayMs * Math.pow(2, attempt),
        maxDelayMs
      );

      console.log(`
        Attempt ${attempt + 1}/${maxRetries + 1}:
        - Retryable failures: ${retryableIndices.length}
        - Waiting ${delayMs}ms before retry
      `);

      await delay(delayMs);

      // Retry just the failed messages
      const retryResponse = await messaging.sendEach(retryMessages, isDryRun);

      // Merge retry results back into original response
      retryResponse.responses.forEach((retryResp, idx) => {
        const originalIdx = retryableIndices[idx];
        response.responses[originalIdx] = retryResp;
      });

      // Recalculate success/failure counts
      response.successCount = response.responses.filter(r => r.success).length;
      response.failureCount = response.responses.filter(r => !r.success).length;

      console.log(`
        After retry:
        - Success: ${response.successCount}
        - Failed: ${response.failureCount}
      `);

      // If all succeeded after retry, return
      if (response.failureCount === 0) {
        return response;
      }

      // Otherwise, continue to next retry attempt
      attempt++;

    } catch (error) {
      console.error(`Attempt ${attempt + 1} threw exception:`, error);
      lastError = error;
      attempt++;

      if (attempt <= maxRetries) {
        const delayMs = Math.min(
          initialDelayMs * Math.pow(2, attempt - 1),
          maxDelayMs
        );
        await delay(delayMs);
      }
    }
  }

  // All retries failed
  throw lastError || new Error('All retry attempts failed');
}

Retry configuration:

// Production sends
const response = await sendEachWithRetry(
  messaging,
  messages,
  false, // isDryRun = false (actual send)
  {
    maxRetries: 3,        // Try up to 3 times
    initialDelayMs: 1000, // Start with 1 second
    maxDelayMs: 5000,     // Cap at 5 seconds
  }
);

// Results:
// - Attempt 1: Instant
// - Attempt 2: After 1 second (2^0 * 1000ms)
// - Attempt 3: After 2 seconds (2^1 * 1000ms)
// - Attempt 4: After 4 seconds (2^2 * 1000ms, but capped at 5s)

Retry success rates (our production data):

Error Type          | 1st Retry | 2nd Retry | 3rd Retry | Overall
--------------------|-----------|-----------|-----------|--------
temporary           |    78%    |    15%    |     4%    |   97%
quota               |    95%    |     4%    |     1%    |  100%
invalid_token       |     0%    |     0%    |     0%    |    0%
other               |    45%    |    20%    |    10%    |   75%

Key insight: 97% of temporary errors eventually succeed with retry!

Tracking retry results in the database

We track both initial send and retry outcomes:

// After initial send
const initialLog = new PushNotificationLog({
  // ... basic fields ...
  is_success: false,
  error_type: 'temporary',
  retry_count: 0,
  retry_success: false,
});

await repository.save(initialLog);

// After successful retry
if (retryResponse.success) {
  await repository.update(
    { id: initialLog.id },
    {
      retry_count: attemptNumber,
      retry_success: true,
      // Note: is_success stays false - original send failed
    }
  );

  console.log(`Token ${token} succeeded on retry ${attemptNumber}`);
}

Query retry effectiveness:

-- Retry success rate by error type
SELECT 
  error_type,
  COUNT(*) as total_failures,
  SUM(CASE WHEN retry_success = 1 THEN 1 ELSE 0 END) as retry_successes,
  CAST(SUM(CASE WHEN retry_success = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) as retry_success_rate
FROM push_notification_log
WHERE is_success = 0
  AND retry_count > 0
  AND sent_at >= DATEADD(month, -1, GETDATE())
GROUP BY error_type
ORDER BY total_failures DESC;

Example result:

error_type     | total_failures | retry_successes | retry_success_rate
---------------|----------------|-----------------|-------------------
temporary      |      45,230    |      43,875     |      97.0%
quota          |       2,180    |       2,180     |     100.0%
other          |       1,520    |       1,140     |      75.0%
invalid_token  |      87,430    |           0     |       0.0%

API endpoint: real-time delivery statistics

We expose delivery metrics via REST API:

// GET /api/campaigns/:jobId/delivery-stats
async getDeliveryStats(jobId: string): Promise {
  try {
    const logs = await this.pushNotificationLog.find({
      where: { job_id: jobId },
      select: ['is_success', 'error_code', 'error_type', 'retry_success'],
    });

    if (logs.length === 0) {
      throw new NotFoundException(`No logs found for campaign: ${jobId}`);
    }

    const stats = calculateErrorStats(logs);

    // Additional retry analysis
    const retriedLogs = logs.filter(log => log.retry_count > 0);
    const retrySuccessCount = retriedLogs.filter(log => log.retry_success).length;
    const retrySuccessRate = retriedLogs.length > 0
      ? parseFloat(((retrySuccessCount / retriedLogs.length) * 100).toFixed(2))
      : 0;

    console.log(`[getDeliveryStats] Campaign: ${jobId}`);
    console.log(`  Delivery Rate: ${stats.deliveryRate}%`);
    console.log(`  Retry Success Rate: ${retrySuccessRate}%`);

    return {
      ...stats,
      retryAttempts: retriedLogs.length,
      retrySuccesses: retrySuccessCount,
      retrySuccessRate,
    };

  } catch (error) {
    console.error(`[getDeliveryStats] Error:`, error);
    throw error;
  }
}

Response example:

GET /api/campaigns/production-blackfriday-2025/delivery-stats

{
  "total": 500000,
  "success": 401000,
  "invalidToken": 87000,
  "temporary": 11000,
  "quota": 800,
  "other": 200,
  "deliveryRate": 80.2,
  "successRate": 80.2,
  "retryableRate": 2.4,
  "retryAttempts": 11800,
  "retrySuccesses": 11450,
  "retrySuccessRate": 97.0
}

Automated token cleanup: removing dead tokens

After each campaign, we automatically clean up invalid tokens:

// Runs after campaign completes
async function cleanupInvalidTokens(jobId: string) {
  console.log(`[Cleanup] Starting for campaign: ${jobId}`);

  // Get all permanently invalid tokens
  const invalidLogs = await pushNotificationLog.find({
    where: {
      job_id: jobId,
      error_type: 'invalid_token', // Permanent failures only
    },
    select: ['member_seq', 'push_token', 'error_code'],
  });

  console.log(`[Cleanup] Found ${invalidLogs.length} invalid tokens`);

  if (invalidLogs.length === 0) {
    console.log(`[Cleanup] Nothing to clean up`);
    return;
  }

  // Batch update member table
  const batchSize = 1000;
  let updated = 0;

  for (let i = 0; i < invalidLogs.length; i += batchSize) {
    const batch = invalidLogs.slice(i, i + batchSize);
    const memberSeqs = batch.map(log => log.member_seq);

    await memberRepository
      .createQueryBuilder()
      .update(Member)
      .set({
        push_token_valid: false,
        push_token_invalidated_at: () => 'GETDATE()',
        push_token_invalid_reason: 'fcm_invalid_token',
      })
      .whereInIds(memberSeqs)
      .execute();

    updated += batch.length;
    console.log(`[Cleanup] Updated ${updated}/${invalidLogs.length}`);
  }

  // Log cleanup event
  await cleanupEventLog.save({
    campaign_job_id: jobId,
    tokens_invalidated: invalidLogs.length,
    executed_at: new Date(),
  });

  console.log(`[Cleanup] ✅ Complete - ${invalidLogs.length} tokens marked invalid`);
}

Database schema for token validity:

ALTER TABLE member
ADD push_token_valid BIT DEFAULT 1,
ADD push_token_invalidated_at DATETIME2 NULL,
ADD push_token_invalid_reason VARCHAR(50) NULL;

-- Index for fast filtering
CREATE INDEX idx_member_valid_tokens 
ON member(push_token_valid, push_token)
WHERE push_token IS NOT NULL;

Future queries automatically exclude invalid tokens:

// ✅ Only query valid tokens
const tokens = await memberRepository
  .createQueryBuilder('m')
  .where('m.push_token_valid = 1') // Valid tokens only
  .andWhere('m.push_token IS NOT NULL')
  .select(['m.push_token', 'm.seq'])
  .getMany();

console.log(`Found ${tokens.length} valid tokens (invalid tokens excluded)`);

Measuring improvement over time

With consistent delivery tracking, we can measure token health trends:

-- Monthly delivery rate trends
SELECT 
  FORMAT(sent_at, 'yyyy-MM') as month,
  COUNT(*) as total_sends,
  SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) as successful_sends,
  CAST(SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) as delivery_rate
FROM push_notification_log
WHERE is_dry_run = 0 -- Production sends only
  AND sent_at >= DATEADD(month, -6, GETDATE())
GROUP BY FORMAT(sent_at, 'yyyy-MM')
ORDER BY month;

Our 6-month trend (with automated cleanup):

Month    | Total Sends | Successful | Delivery Rate
---------|-------------|------------|---------------
2025-01  |  25,000,000 | 17,500,000 |     70.0%
2025-02  |  28,000,000 | 21,280,000 |     76.0%
2025-03  |  30,000,000 | 24,000,000 |     80.0%
2025-04  |  32,000,000 | 26,560,000 |     83.0%
2025-05  |  35,000,000 | 29,750,000 |     85.0%
2025-06  |  38,000,000 | 32,680,000 |     86.0%

Improvement: 70% → 86% delivery rate (+16 percentage points)
Reason: Automated cleanup after each campaign

Production metrics: 50M monthly notifications

Current performance (June 2025):

Monthly Volume: 50,000,000 notifications
Delivery Rate: 86.0%
Retry Success Rate: 97.0%

Error Breakdown:
- Invalid tokens: 12.0% (down from 30% in Jan)
- Temporary errors: 1.8%
- Quota errors: 0.1%
- Other errors: 0.1%

Time Saved (vs. no cleanup):
- 14% fewer invalid tokens per campaign
- ~12 minutes saved per campaign
- 50 campaigns/month × 12 min = 600 min/month = 10 hours/month

Cost savings (compared to January baseline):

Before cleanup automation (Jan 2025):
- Invalid token rate: 30%
- Monthly invalid sends: 15,000,000
- Server time wasted: 40 hours/month
- DB operations wasted: 15M writes/month

After cleanup automation (Jun 2025):
- Invalid token rate: 12%
- Monthly invalid sends: 6,000,000
- Server time wasted: 16 hours/month
- DB operations wasted: 6M writes/month

Savings:
- Server time: 24 hours/month × $0.10/min × 60 = $144/month = $1,728/year
- DB operations: 9M writes/month × $0.0001 = $900/month = $10,800/year
- Total: $12,528/year

Key takeaways

1. Measure actual delivery, not just attempts

// ❌ Misleading metric
console.log(`Sent to ${totalAttempts} users!`);

// ✅ Accurate metric  
console.log(`Delivered to ${actualSuccess} devices (${deliveryRate}%)`);

2. Classify errors for actionable insights

Invalid tokens: Remove immediately
Temporary errors: Retry with backoff
Quota errors: Wait and retry
Other errors: Investigate and classify

3. Retry temporary failures (97% eventually succeed)

const response = await sendEachWithRetry(messaging, messages, false, {
  maxRetries: 3,
  initialDelayMs: 1000,
  maxDelayMs: 5000,
});

4. Automate token cleanup after every campaign

Improves delivery rate over time (70% → 86% in 6 months)
Reduces wasted processing (24 hours/month saved)
Maintains database health automatically

5. Track trends to measure improvement

SELECT FORMAT(sent_at, 'yyyy-MM'), 
       AVG(delivery_rate)
FROM monthly_delivery_stats
GROUP BY FORMAT(sent_at, 'yyyy-MM');

6. Store everything for forensic analysis

Success/failure for every token
Error codes and classifications
Retry attempts and results
Timestamp for time-series analysis

When to use actual delivery measurement vs dry-run validation

Use both in sequence:

// Phase 1: Dry-run validation (pre-send health check)
const validation = await sendConditionalNotifications({
  ...campaignData,
  jobId: 'dryrun-campaign-123',
  limit: 10000, // Sample
  isDryRun: true,
});

console.log(`Predicted delivery rate: ${validation.deliveryRate}%`);

// Phase 2: Actual send (ground truth measurement)
const production = await sendConditionalNotifications({
  ...campaignData,
  jobId: 'production-campaign-123',
  limit: undefined, // Full send
  isDryRun: false,
});

console.log(`Actual delivery rate: ${production.deliveryRate}%`);
console.log(`Prediction accuracy: ${Math.abs(validation.deliveryRate - production.deliveryRate).toFixed(1)}%`);

Dry-run (validation):

✅ Fast (2 minutes for 10K sample)
✅ Zero user impact
✅ Predicts delivery rate
❌ Not 100% accurate (time gap between test and send)

Actual send (delivery measurement):

✅ 100% accurate (ground truth)
✅ Reveals real-world issues (network, devices, timing)
✅ Enables retry logic
❌ Slower (60+ minutes for 500K)
❌ User impact (notifications sent)

Best practice: Use both

Dry-run validation before large campaigns (risk mitigation)
Actual delivery measurement for all sends (ground truth)
Compare validation vs delivery for continuous improvement

Validating 500K Push Tokens with Firebase Dry-Run

Sangwoo Lee — Wed, 07 Jan 2026 17:03:00 +0000

How we predict delivery rates in 2 minutes instead of 80 minutes using Firebase's hidden validation mode—without sending a single notification

"We just sent to 150,000 dead tokens."

My product manager's face turned pale as she refreshed the dashboard. Out of our 500,000-user Black Friday campaign, 150,000 failed with invalid-registration-token. We'd spent 80 minutes of server time and consumed precious FCM quota—all to discover what we could have known in 2 minutes.

The wake-up call: We were discovering invalid tokens AFTER sending. By then:

80 minutes of server resources consumed
FCM quota unnecessarily depleted
Misleading "500K sent!" reports to stakeholders
30% of our users never had a chance to receive the notification

The solution: Firebase's dry-run mode. A little-known parameter that validates tokens WITHOUT sending notifications. In this post, I'll show you how we built a pre-send validation system that predicts delivery rates with 95%+ accuracy in under 2 minutes.

The $2,000/year parameter you've never used

Firebase Cloud Messaging has a superpower that most developers don't know about: the dryRun parameter.

// Normal send - delivers notification to device
await admin.messaging().send(message); 
// ❌ Consumes quota, takes time, delivers to user

// Dry-run - validates token WITHOUT sending
await admin.messaging().send(message, true); 
// ✅ Fast validation, minimal quota, zero delivery

What dry-run actually does:

✅ DOES validate:

Token format correctness
Device registration status
Token-to-device binding validity
Returns same response structure as real sends

❌ Does NOT do:

Send notification to device
Trigger user's notification sound/vibration
Increment delivery metrics significantly
Consume standard FCM quota

The magic part: Dry-run responses predict real send results with 95-98% accuracy. You get the validation results without any user impact.

Why validation matters at scale

When you're sending to 100 users, a 30% failure rate is annoying. At 500,000 users, it's a business problem.

Time impact:

Without validation:
- Send 500K notifications: 80 minutes
- Discover 150K failures: After the fact
- Wasted time on invalid tokens: 24 minutes

With dry-run validation:
- Validate 10K sample: 2 minutes  
- Predict: "You'll reach ~350K devices (70%)"
- Send to 350K valid tokens only: 56 minutes
- Total: 58 minutes (28% faster)

Cost impact (per campaign):

Server CPU time saved: 24 minutes × $0.10/min = $2.40
Database operations saved: 150K failed writes = $0.45
FCM quota preservation: Minimal but measurable

We run 50+ campaigns per month: $2.40 × 50 = $120/month = $1,440/year

Trust impact:

Before: "We sent to 500,000 users!" → Actual reach: 350,000 → Stakeholders lose trust
After: "We'll reach ~349,000 users (69.8%)" → Actual reach: 351,000 → Stakeholders trust predictions

Implementation: one flag to rule them all

The key insight: use the same code path for validation and production, controlled by a single flag.

// firebase.service.ts
async sendConditionalNotifications(
  jobData: ConditionalNotificationParams
): Promise {

  // ... Query database for target users ...

  const tokens = await this.getTargetTokens(jobData);
  // Returns 500,000 tokens based on filters

  const chunks = chunkArray(tokens, 500);
  // Creates 1,000 chunks of 500 tokens each

  // ★ The magic switch
  const isDryRun = true; // Set to false for production

  console.log(`
    ========== SEND MODE ==========
    Mode: ${isDryRun ? 'DRY RUN (validation only)' : 'PRODUCTION (actual delivery)'}
    Total tokens: ${tokens.length.toLocaleString()}
    Chunks: ${chunks.length}
    ==============================
  `);

  let totalSuccess = 0;
  let totalFailed = 0;

  for (let i = 0; i < chunks.length; i++) {
    const chunk = chunks[i];

    // Build FCM messages
    const messages = chunk.map(token => ({
      token,
      notification: { 
        title: jobData.title, 
        body: jobData.content 
      },
      data: {
        campaignId: jobData.campaignId,
        jobId: jobData.jobId,
      },
    }));

    try {
      // ★ Pass dry-run flag to FCM
      const response = await this.firebaseApp
        .messaging()
        .sendEach(messages, isDryRun); // 👈 Critical parameter

      console.log(`
        Chunk ${i + 1}/${chunks.length}:
        Valid: ${response.successCount}/${chunk.length}
        Invalid: ${response.failureCount}/${chunk.length}
        ${isDryRun ? '(No notifications sent)' : '(Delivered to devices)'}
      `);

      totalSuccess += response.successCount;
      totalFailed += response.failureCount;

      // ✅ Save results to database (same logic for both modes)
      await this.savePushNotificationLogs(
        jobData,
        messages,
        response,
        isDryRun // Flag stored in database
      );

      // Rate limiting (even for dry-run)
      if (i < chunks.length - 1) {
        await delay(2000); // 2 seconds between chunks
      }

    } catch (error) {
      console.error(`Chunk ${i + 1} failed:`, error);
      totalFailed += chunk.length;
    }
  }

  const deliveryRate = ((totalSuccess / tokens.length) * 100).toFixed(2);

  console.log(`
    ========== VALIDATION COMPLETE ==========
    Total tested: ${tokens.length.toLocaleString()}
    Valid tokens: ${totalSuccess.toLocaleString()} (${deliveryRate}%)
    Invalid tokens: ${totalFailed.toLocaleString()}
    ${isDryRun ? '✅ Zero notifications sent to users' : '📨 Notifications delivered'}
    ========================================
  `);

  return {
    success: true,
    totalTokens: tokens.length,
    validTokens: totalSuccess,
    invalidTokens: totalFailed,
    deliveryRate: parseFloat(deliveryRate),
    isDryRun,
  };
}

Design decisions:

1. Same code path

No separate validation/production functions
Reduces code duplication bugs
Easy to maintain and test

2. Single flag at top level

Change one variable: isDryRun
Clear console output showing mode
Zero risk of mixing modes mid-send

3. Rate limiting applies to both modes

// Even dry-run counts toward rate limits!
if (i > 0) {
  await delay(2000); // Respect FCM quotas
}

Storing dry-run results: the audit trail

We store validation results identically to real sends, with one extra field:

// push-notification-log.entity.ts
@Entity({ name: 'push_notification_log' })
export class PushNotificationLog {
  @PrimaryGeneratedColumn({ type: 'bigint' })
  id: number;

  @Column({ type: 'varchar', length: 200 })
  job_id: string; // e.g., "dryrun-blackfriday-2025"

  @Column({ type: 'int' })
  member_seq: number;

  @Column({ type: 'varchar', length: 500 })
  push_token: string;

  @Column({ type: 'bit', default: false })
  is_success: boolean; // Did token validate?

  @Column({ type: 'datetime2' })
  sent_at: Date;

  @Column({ type: 'varchar', length: 50, nullable: true })
  error_code: string; // FCM error code

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

  // ★ The key differentiator
  @Column({ type: 'bit', nullable: true, default: false })
  is_dry_run: boolean; // true = validation, false = production

  // Error classification for analytics
  @Column({ type: 'varchar', length: 30, nullable: true })
  error_type: 'invalid_token' | 'temporary' | 'quota' | 'other';
}

Why store dry-run results?

Historical analysis: "Token quality improved from 70% → 85% over 3 months"
A/B testing: Compare dry-run predictions vs actual results
Debugging: "Did we validate before this failed campaign?"
Compliance: Audit trail for regulatory requirements

Query examples:

-- Get all dry-run validation jobs
SELECT job_id, 
       COUNT(*) as total,
       SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) as valid,
       SUM(CASE WHEN is_success = 0 THEN 1 ELSE 0 END) as invalid
FROM push_notification_log
WHERE is_dry_run = 1
GROUP BY job_id
ORDER BY sent_at DESC;

-- Compare dry-run vs production for same campaign
SELECT 
  CASE WHEN is_dry_run = 1 THEN 'Validation' ELSE 'Production' END as mode,
  COUNT(*) as total,
  SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) as success,
  CAST(SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) as rate
FROM push_notification_log
WHERE job_id LIKE 'blackfriday-2025%'
GROUP BY is_dry_run;

Error classification: not all failures are equal

FCM returns different error codes. Some are permanent (dead token), others are temporary (network hiccup).

// fcm-error-classifier.ts
export function classifyFcmError(errorCode?: string): string {
  if (!errorCode) return 'other';

  // ❌ Permanent failures - token is completely dead
  const INVALID_TOKEN_ERRORS = [
    'messaging/invalid-registration-token',
    'messaging/registration-token-not-registered',
    'messaging/invalid-argument',
  ];

  if (INVALID_TOKEN_ERRORS.includes(errorCode)) {
    return 'invalid_token';
  }

  // ⏳ Temporary failures - might succeed on retry
  const TEMPORARY_ERRORS = [
    'messaging/unavailable',
    'messaging/internal-error',
    'messaging/server-unavailable',
    'messaging/timeout',
  ];

  if (TEMPORARY_ERRORS.includes(errorCode)) {
    return 'temporary';
  }

  // 🚫 Quota exceeded - rate limiting
  if (errorCode === 'messaging/quota-exceeded') {
    return 'quota';
  }

  // ❓ Unknown/other errors
  return 'other';
}

Critical insight: In dry-run mode, "temporary" errors often become successes in production. Why?

Network conditions differ between validation and actual send
Time gap allows transient issues to resolve
FCM infrastructure load balancing

In our testing: ~80% of "temporary" dry-run errors succeed in production.

Calculating delivery rate: the metric that matters

With classified errors, we calculate delivery rate - tokens that CAN receive notifications.

// fcm-error-classifier.ts
export function calculateDeliveryRate(logs: PushNotificationLog[]): {
  total: number;
  success: number;
  invalidToken: number;
  temporary: number;
  deliveryRate: number;
  successRate: number;
} {
  const total = logs.length;
  let success = 0;
  let invalidToken = 0;
  let temporary = 0;
  let quota = 0;
  let other = 0;

  for (const log of logs) {
    if (log.is_success) {
      success++;
    } else {
      switch (log.error_type) {
        case 'invalid_token': invalidToken++; break;
        case 'temporary': temporary++; break;
        case 'quota': quota++; break;
        default: other++; break;
      }
    }
  }

  // ✅ Delivery rate = tokens that CAN receive notifications
  // Includes temporary errors (likely to succeed in production)
  const deliveryRate = total > 0
    ? parseFloat((((success + temporary) / total) * 100).toFixed(2))
    : 0;

  // Success rate = immediate validation success only
  const successRate = total > 0
    ? parseFloat(((success / total) * 100).toFixed(2))
    : 0;

  return {
    total,
    success,
    invalidToken,
    temporary,
    quota,
    other,
    deliveryRate,  // ⭐ The key metric
    successRate,
  };
}

Example output:

const stats = calculateDeliveryRate(dryRunLogs);

console.log(`
📊 Validation Results:
======================
Total tested: ${stats.total.toLocaleString()}
✅ Valid tokens: ${stats.success.toLocaleString()}
❌ Invalid tokens: ${stats.invalidToken.toLocaleString()}
⏳ Temporary errors: ${stats.temporary.toLocaleString()}

🎯 Delivery Rate: ${stats.deliveryRate}%
   (${stats.success + stats.temporary} tokens can receive notifications)

📈 Success Rate: ${stats.successRate}%
   (immediate validation success)
`);

Sample output:

📊 Validation Results:
======================
Total tested: 10,000
✅ Valid tokens: 7,243
❌ Invalid tokens: 2,103
⏳ Temporary errors: 654

🎯 Delivery Rate: 79.0%
   (7,897 tokens can receive notifications)

📈 Success Rate: 72.4%
   (immediate validation success)

Production workflow: validate, analyze, send

Here's our real-world process for a 500K-user campaign:

Phase 1: Dry-run validation (2 minutes)

// Step 1: Create validation job
const validationJob = {
  jobId: 'dryrun-blackfriday-2025',
  title: 'Black Friday Sale - 50% Off Everything!',
  content: 'Limited time offer. Shop now!',

  // Target filters
  gender: undefined, // All genders
  ageMin: 20,
  ageMax: 65,
  platform_type: undefined, // iOS + Android

  // ★ Sample size: 1-2% of target
  limit: 10000, // Out of 500K total
};

// Step 2: Run validation (isDryRun = true)
console.log('========== PHASE 1: VALIDATION ==========');
const validationResult = await firebaseService.sendConditionalNotifications({
  ...validationJob,
  // Service internally uses isDryRun: true flag
});

console.log('Validation complete:', validationResult);
// Output:
// {
//   success: true,
//   totalTokens: 10000,
//   validTokens: 7243,
//   invalidTokens: 2757,
//   deliveryRate: 79.0,
//   isDryRun: true
// }

Phase 2: Analyze results and decide (30 seconds)

// Get detailed error breakdown
const stats = await firebaseService.getDeliveryStats('dryrun-blackfriday-2025');

console.log(`
📊 Detailed Validation Analysis:
=================================
Total Tested: ${stats.total.toLocaleString()}
✅ Valid: ${stats.success.toLocaleString()} (${stats.successRate}%)
❌ Invalid: ${stats.invalidToken.toLocaleString()}
⏳ Temporary: ${stats.temporary.toLocaleString()}
🚫 Quota: ${stats.quota}
❓ Other: ${stats.other}

🎯 Delivery Rate: ${stats.deliveryRate}%
`);

// Decision logic
if (stats.deliveryRate < 70) {
  console.error('❌ ABORT: Delivery rate too low!');
  console.log('Action required: Database cleanup before sending');

  // Get list of invalid tokens for cleanup
  const invalidTokens = await firebaseService.getInvalidTokens(
    'dryrun-blackfriday-2025'
  );

  console.log(`Found ${invalidTokens.length} invalid tokens to remove`);
  // TODO: Implement automated cleanup

} else if (stats.deliveryRate < 80) {
  console.warn('⚠️  CAUTION: Delivery rate acceptable but improvable');
  console.log('Recommendation: Proceed, but schedule cleanup for next week');

} else {
  console.log('✅ EXCELLENT: Delivery rate is healthy. Safe to proceed!');
}

// Estimate actual campaign reach
const totalTarget = 500000;
const estimatedReach = Math.floor(totalTarget * (stats.deliveryRate / 100));
const estimatedFailed = totalTarget - estimatedReach;

console.log(`
📈 Campaign Projection:
========================
Target Audience: ${totalTarget.toLocaleString()} users
Estimated Reach: ${estimatedReach.toLocaleString()} devices (${stats.deliveryRate}%)
Expected Failures: ${estimatedFailed.toLocaleString()} invalid tokens

Time Estimate: ~${Math.ceil(estimatedReach / 10000)}  minutes
(Processing ${Math.ceil(estimatedReach / 500)} chunks at 2 sec/chunk)
`);

Example decision output:

📊 Detailed Validation Analysis:
=================================
Total Tested: 10,000
✅ Valid: 7,243 (72.4%)
❌ Invalid: 2,103
⏳ Temporary: 654
🚫 Quota: 0
❓ Other: 0

🎯 Delivery Rate: 79.0%

✅ EXCELLENT: Delivery rate is healthy. Safe to proceed!

📈 Campaign Projection:
========================
Target Audience: 500,000 users
Estimated Reach: 395,000 devices (79.0%)
Expected Failures: 105,000 invalid tokens

Time Estimate: ~80 minutes
(Processing 790 chunks at 2 sec/chunk)

Phase 3: Production send (63 minutes)

console.log('========== PHASE 2: PRODUCTION SEND ==========');

// Create production job (isDryRun = false)
const productionJob = {
  jobId: 'production-blackfriday-2025',
  title: 'Black Friday Sale - 50% Off Everything!',
  content: 'Limited time offer. Shop now!',

  // Same filters as validation
  gender: undefined,
  ageMin: 20,
  ageMax: 65,
  platform_type: undefined,

  // ★ Full campaign (no limit)
  limit: undefined, // Send to all matching users
};

const productionResult = await firebaseService.sendConditionalNotifications(productionJob);

console.log(`
✅ Campaign Complete:
- Total sent: ${productionResult.sendStats.totalSent.toLocaleString()}
- Successful: ${productionResult.sendStats.successCount.toLocaleString()}
- Failed: ${productionResult.sendStats.totalFailed.toLocaleString()}
- Duration: ${productionResult.timing.durationMinutes} minutes
`);

Phase 4: Validation accuracy check (10 seconds)

// Compare dry-run prediction to actual results
const productionStats = await firebaseService.getDeliveryStats(
  'production-blackfriday-2025'
);

const predictionError = Math.abs(stats.deliveryRate - productionStats.deliveryRate);
const reachError = Math.abs(estimatedReach - productionStats.success);
const accuracy = 100 - (predictionError);

console.log(`
🎯 Prediction vs Reality:
==========================
DRY-RUN PREDICTION:
- Delivery rate: ${stats.deliveryRate}%
- Estimated reach: ${estimatedReach.toLocaleString()}

PRODUCTION ACTUAL:
- Delivery rate: ${productionStats.deliveryRate}%
- Actual reach: ${productionStats.success.toLocaleString()}

ACCURACY:
- Delivery rate error: ${predictionError.toFixed(1)}%
- Reach error: ${reachError.toLocaleString()} users (${((reachError/estimatedReach)*100).toFixed(1)}%)
- Overall accuracy: ${accuracy.toFixed(1)}%

${predictionError < 2 ? '✅ Excellent prediction!' : 
  predictionError < 5 ? '⚠️  Acceptable prediction' : 
  '❌ Poor prediction - review sampling strategy'}
`);

Typical accuracy (our production data):

🎯 Prediction vs Reality:
==========================
DRY-RUN PREDICTION:
- Delivery rate: 79.0%
- Estimated reach: 395,000

PRODUCTION ACTUAL:
- Delivery rate: 80.2%
- Actual reach: 401,000

ACCURACY:
- Delivery rate error: 1.2%
- Reach error: 6,000 users (1.5%)
- Overall accuracy: 98.8%

✅ Excellent prediction!

Why production beats prediction:

Temporary errors in dry-run often succeed in production (~80% success rate)
Time gap allows network conditions to stabilize
FCM infrastructure routing differences

Sampling strategies: the 1-2% rule

Question: Should you validate all 500K tokens or sample?

Answer: Sample. Here's why:

Statistical validity:

10K sample from 500K population = 95% confidence, ±1% margin of error
20K sample = 98% confidence, ±0.7% margin
50K sample = 99% confidence, ±0.5% margin

Diminishing returns above 2%:

Sample Size | Confidence | Margin of Error | Validation Time
-----------:|:----------:|:---------------:|----------------
     5,000  |    90%     |      ±1.4%      |     1 min
    10,000  |    95%     |      ±1.0%      |     2 min
    20,000  |    98%     |      ±0.7%      |     4 min
    50,000  |    99%     |      ±0.5%      |    10 min
   100,000  |   99.5%    |      ±0.3%      |    20 min
   500,000  |   100%     |       0.0%      |    100 min

Our rule: 1-2% sample size

100K campaign → 1-2K sample
500K campaign → 5-10K sample
1M campaign → 10-20K sample

Critical: Random sampling to avoid bias

// ❌ BAD: Samples most active users only
const sample = await memberRepository
  .createQueryBuilder('m')
  .where(/* filters */)
  .orderBy('m.last_login', 'DESC') // Sorted by activity
  .take(10000)
  .getMany();

// Result: 85% delivery rate (biased upward)
// Reality: 72% delivery rate (includes dormant users)

// ✅ GOOD: Random sample across entire dataset
const sample = await memberRepository
  .createQueryBuilder('m')
  .where(/* filters */)
  .orderBy('NEWID()') // Random order (MSSQL)
  // .orderBy('RAND()') // Random order (MySQL)
  // .orderBy('RANDOM()') // Random order (PostgreSQL)
  .take(10000)
  .getMany();

// Result: 79% delivery rate (accurate)
// Reality: 80.2% delivery rate (within 1.2% margin)

Pro tip: Stratified sampling for heterogeneous audiences

// If audience has distinct segments, sample each proportionally
const iosSample = await getSample({ platform: 'ios' }, 0.02); // 2%
const androidSample = await getSample({ platform: 'android' }, 0.02);

const combinedStats = calculateWeightedDeliveryRate([
  { platform: 'ios', stats: iosSample.stats, weight: iosSample.totalPopulation },
  { platform: 'android', stats: androidSample.stats, weight: androidSample.totalPopulation },
]);

Edge cases and lessons learned

Gotcha 1: Dry-run IS NOT quota-free

Misconception: "Dry-run doesn't count toward rate limits."

Reality: Dry-run DOES count toward FCM rate limits (though at a lower rate).

What happened:

// Our first attempt (disaster)
const isDryRun = true;

for (let i = 0; i < 1000; i++) {
  // No delay - sent 500K tokens in 5 minutes
  const response = await messaging.sendEach(messages, isDryRun);
}

// Result: messaging/quota-exceeded after chunk 200

Solution: Apply same rate limiting as production

for (let i = 0; i < chunks.length; i++) {
  const response = await messaging.sendEach(messages[i], isDryRun);

  // ✅ Rate limiting (even in dry-run)
  if (i < chunks.length - 1) {
    await delay(2000); // 2 seconds between chunks
  }
}

Gotcha 2: Database performance with large validations

Problem: Storing 500K dry-run logs slowed down queries.

-- This query became slow
SELECT * FROM push_notification_log
WHERE job_id = 'production-campaign-123'
AND is_dry_run = 0;

-- Execution time: 8 seconds (unacceptable)

Solution: Compound index

@Entity({ name: 'push_notification_log' })
@Index(['job_id', 'is_dry_run', 'sent_at']) // ✅ Compound index
export class PushNotificationLog {
  // ...
}

Result: Query time: 8s → 0.3s

Gotcha 3: Dry-run vs production discrepancies

Observed pattern:

Dry-run: 79.0% delivery rate
Production: 80.2% delivery rate
Difference: +1.2%

Why production performs better:

Temporary errors resolve: ~80% of unavailable errors in dry-run succeed in production
Time gap: 2-5 minutes between validation and send allows network recovery
Infrastructure differences: Dry-run may use stricter validation paths

Our adjustment formula:

// Apply 1-2% buffer to dry-run predictions
const adjustedDeliveryRate = dryRunRate * 1.015;
const estimatedReach = Math.floor(totalTarget * (adjustedDeliveryRate / 100));

console.log(`
Conservative estimate (dry-run): ${Math.floor(totalTarget * (dryRunRate / 100))}
Adjusted estimate (+1.5%): ${estimatedReach}
`);

Gotcha 4: Sampling bias from user behavior patterns

Problem: Validation at 9 AM showed 85% delivery rate. Production send at 9 PM showed 76% delivery rate.

Root cause: User activity patterns.

9 AM sample: Captured active morning users (fresh tokens)
9 PM send: Included evening users who hadn't opened app in weeks (stale tokens)

Solution: Time-aware sampling

// If sending at 9 PM, validate at similar time
const now = new Date();
const hour = now.getHours();

if (hour >= 20 || hour <= 6) {
  console.log('Sending during off-peak hours');
  console.log('Recommendation: Validate during same time window for accuracy');
}

// Or: Account for temporal variance in estimates
const timeVarianceFactor = hour >= 20 ? 0.95 : 1.0;
const adjustedReach = estimatedReach * timeVarianceFactor;

Real production metrics: 6 months of dry-run usage

After implementing dry-run validation (January 2025 - July 2025):

Validation accuracy (10,000+ campaigns):

Metric                    | Value
--------------------------|--------
Average prediction error  |  1.8%
95th percentile error     |  3.2%
Best case                 |  0.3%
Worst case                |  5.7%

Time savings:

Before dry-run:
- Average campaign duration: 80 minutes
- Wasted on invalid tokens: 24 minutes/campaign

After dry-run:
- Validation time: 2 minutes
- Actual send time: 56 minutes (valid tokens only)
- Total: 58 minutes

Savings: 22 minutes/campaign × 50 campaigns/month = 1,100 min/month = 18 hours/month

Database quality improvement:

Month    | Invalid Token Rate
---------|-------------------
Jan 2025 | 30%
Feb 2025 | 27%
Mar 2025 | 23%
Apr 2025 | 18%
May 2025 | 15%
Jun 2025 | 12%

Reason: Automated cleanup after each validation

Cost impact:

Server time saved: 18 hours/month × 12 months = 216 hours/year
Value: 216 hours × $0.10/minute × 60 minutes = $1,296/year

Database operations saved: 105K failed writes/campaign × 50 campaigns/month = 5.25M writes/month
Value: 5.25M writes × $0.0001 = $525/month = $6,300/year

Total estimated savings: $7,596/year

Developer confidence:

Before: "I hope this works 🤞"
Team questions:
- "How many users will we actually reach?"
- "Should we send to everyone or clean database first?"
- "What if 50% fail?"

After: "We'll reach 395,000 ± 1.5%"
Team confidence:
- "Delivery rate: 79.0% (validated 10 minutes ago)"
- "Estimated reach: 395,000 devices"
- "Send time: ~63 minutes"
- "Recommendation: Proceed with send"

When to use dry-run validation

Always validate for:
✅ Large campaigns (>100K users)
✅ High-value campaigns (product launches, Black Friday)
✅ New audience segments (first-time targeting)
✅ After database migrations
✅ Monthly/quarterly database health checks

Skip validation for:
❌ Small campaigns (<1K users) - overhead not worth it
❌ Time-critical alerts (breaking news, emergencies)
❌ Daily recurring sends to proven audiences
❌ Transactional notifications (order confirmations)

Sample size guidelines:

Campaign Size | Sample Size | Confidence Level
--------------:|------------:|------------------
       10,000  |     1,000   |      95%
      100,000  |     2,000   |      95%
      500,000  |    10,000   |      95%
    1,000,000  |    20,000   |      98%
    5,000,000  |    50,000   |      99%

API endpoint for delivery statistics

We exposed dry-run results via REST API for dashboard integration:

// GET /api/campaigns/:jobId/delivery-stats
async getDeliveryStats(jobId: string): Promise {
  const logs = await this.pushNotificationLog.find({
    where: { job_id: jobId },
    select: ['is_success', 'error_code', 'error_type']
  });

  if (logs.length === 0) {
    throw new NotFoundException(`No logs found for job: ${jobId}`);
  }

  const stats = calculateDeliveryRate(logs);

  // Check if this was a dry-run job
  const isDryRun = logs[0]?.is_dry_run === true;

  return {
    jobId,
    isDryRun,
    total: stats.total,
    success: stats.success,
    invalidToken: stats.invalidToken,
    temporary: stats.temporary,
    quota: stats.quota,
    other: stats.other,
    deliveryRate: stats.deliveryRate,
    successRate: stats.successRate,

    // Prediction for production (if dry-run)
    estimatedProductionRate: isDryRun 
      ? parseFloat((stats.deliveryRate * 1.015).toFixed(2))
      : null,
  };
}

Response example:

GET /api/campaigns/dryrun-blackfriday-2025/delivery-stats

{
  "jobId": "dryrun-blackfriday-2025",
  "isDryRun": true,
  "total": 10000,
  "success": 7243,
  "invalidToken": 2103,
  "temporary": 654,
  "quota": 0,
  "other": 0,
  "deliveryRate": 79.0,
  "successRate": 72.4,
  "estimatedProductionRate": 80.2
}

Automated token cleanup workflow

After validation, we automatically mark invalid tokens:

// Runs after each dry-run validation
async function cleanupInvalidTokens(dryRunJobId: string) {
  console.log(`Starting cleanup for: ${dryRunJobId}`);

  // Get all permanently invalid tokens
  const invalidTokens = await pushNotificationLog.find({
    where: {
      job_id: dryRunJobId,
      error_type: 'invalid_token', // Permanent failures only
    },
    select: ['member_seq', 'push_token', 'error_code'],
  });

  console.log(`Found ${invalidTokens.length} invalid tokens to mark`);

  // Update member table (don't delete - mark as invalid)
  for (const token of invalidTokens) {
    await memberRepository.update(
      { seq: token.member_seq },
      { 
        push_token_valid: false,
        push_token_invalidated_at: new Date(),
        push_token_invalid_reason: token.error_code,
      }
    );
  }

  console.log(`✅ Marked ${invalidTokens.length} tokens as invalid`);

  // Log cleanup event
  await cleanupLogRepository.save({
    dry_run_job_id: dryRunJobId,
    tokens_marked_invalid: invalidTokens.length,
    executed_at: new Date(),
  });
}

Database schema update:

ALTER TABLE member
ADD push_token_valid BIT DEFAULT 1,
ADD push_token_invalidated_at DATETIME2 NULL,
ADD push_token_invalid_reason VARCHAR(50) NULL;

CREATE INDEX idx_member_valid_token 
ON member(push_token_valid, push_token);

Future queries automatically exclude invalid tokens:

// Before cleanup automation
const tokens = await memberRepository.find({
  where: { /* filters */ },
  select: ['push_token'],
});
// Returns 500K tokens (30% invalid = wasted processing)

// After cleanup automation  
const tokens = await memberRepository.find({
  where: { 
    /* filters */,
    push_token_valid: true, // ✅ Only valid tokens
  },
  select: ['push_token'],
});
// Returns 350K tokens (0% invalid = efficient processing)

Key takeaways

1. Dry-run is a 2-minute investment that saves hours

Validates 500K tokens in 100 minutes → 10K sample in 2 minutes
Predicts delivery rate with 95-98% accuracy
Zero user impact (no notifications sent)

2. One flag, same code path

const isDryRun = true;  // Validation mode
const isDryRun = false; // Production mode

Eliminates code duplication
Reduces maintenance burden
Prevents logic drift between validation and production

3. Sample intelligently (1-2% rule)

10K sample from 500K = 95% confidence, ±1% margin
Random sampling prevents bias
Diminishing returns above 2%

4. Store everything for analysis

is_dry_run flag in database
Error classification (invalid_token, temporary, etc.)
Historical trends ("Token quality improved 70% → 85%")

5. Automate cleanup after validation

Mark invalid tokens immediately
Future campaigns skip them automatically
Database quality improves over time

6. Validate before high-stakes sends

Product launches
Black Friday campaigns
New audience segments
After database migrations

ROI calculation:

Time investment: 2 minutes/campaign
Time saved: 22 minutes/campaign
Net savings: 20 minutes × 50 campaigns/month = 1,000 minutes/month

Cost savings: ~$120/month server time + $40/month DB operations = $160/month
Annual ROI: $1,920

Confidence gained: Priceless

If you're sending push notifications at scale, dry-run validation is the highest-ROI 10 lines of code you can write.

Building Real-time Progress Tracking for Mass Push Notifications with Redis and Polling

Sangwoo Lee — Wed, 31 Dec 2025 06:02:26 +0000

"Can we see the progress bar while notifications are being sent?"

My product manager asked this during a sprint review. We were sending push notifications to 500,000+ users, and the process took over 80 minutes. During that time, the admin dashboard just showed "Sending..." with no visibility into:

How many notifications were actually sent
Current success/failure rates
Estimated time remaining
Whether the job was stuck or progressing normally

The challenge: My workers run in separate Docker containers on different EC2 instances. The frontend can only talk to API servers—it has no direct connection to workers. How do I track progress across this distributed system in real-time?

In this post, I'll show you how I built a polling-based progress tracking system using Redis as a real-time cache, with database fallbacks for reliability.

The problem: distributed workers, centralized monitoring

My push notification architecture:

NestJS API servers (3 instances behind load balancer)
BullMQ workers (2 instances for blue-green deployment)
Redis for job queue and coordination
MSSQL for notification logs (one row per recipient)
MySQL for schedule metadata

A typical campaign flow:

Admin creates a campaign targeting 500,000 users
API server queues a BullMQ job with unique jobId
Worker picks up the job and starts sending in chunks of 500
Worker adds 2-second delays between chunks (FCM rate limits: 600K/minute)
Total time: ~80 minutes for 500,000 recipients

The math:

500,000 users ÷ 500 per chunk = 1,000 chunks
1,000 chunks × 2 seconds delay = 2,000 seconds (~33 minutes of pure waiting)
+ Actual FCM API calls (~47 minutes)
= ~80 minutes total

The problem? The frontend has no idea what's happening between step 3 and step 5.

Solution: Redis as a real-time progress cache

I needed a centralized progress store that:

✅ Writes fast - Workers update every 2 seconds (per chunk)
✅ Reads fast - Frontend polls every 2-5 seconds
✅ Auto-expires - Completed jobs clean up automatically
✅ Survives crashes - Progress persists if workers restart
✅ Low overhead - Minimal impact on worker performance

Why not just query the database?

I could count rows in the notification logs table, but this has serious problems:

Write amplification - 500,000 INSERT operations during sending
Query load - Frontend polling creates constant SELECT COUNT(*) queries
Lock contention - COUNT(*) locks table during active writes
No estimated time - Can't calculate completion time from static logs

Redis solves all of these:

// redis.service.ts
private readonly PROGRESS_KEY_PREFIX = 'job:progress:';
private readonly PROGRESS_TTL_SECONDS = 3600; // 1 hour

async setProgressByJobId(params: JobProgressUpdateParams): Promise {
  const key = `${this.PROGRESS_KEY_PREFIX}${params.jobId}`;

  const progressPercentage = params.totalTargetCount > 0
    ? Math.round((params.currentSentCount / params.totalTargetCount) * 100)
    : 0;

  const progress: ScheduleProgress = {
    jobId: params.jobId,
    status: params.status, // 'preparing', 'in_progress', 'completed', 'failed'
    currentSentCount: params.currentSentCount,
    totalTargetCount: params.totalTargetCount,
    progressPercentage,
    estimatedEndAt: this.calculateEstimatedEndTime(params),
    // ... more fields
  };

  // TTL: 1 hour for completed, 24 hours for in-progress
  const ttl = params.status === 'completed' ? 3600 : 86400;
  await this.client.setex(key, ttl, JSON.stringify(progress));
}

Key design decisions:

1. TTL for automatic cleanup

Completed jobs: 1-hour TTL (users rarely check after completion)
In-progress jobs: 24-hour TTL (safety net for very slow campaigns)
No cron jobs needed—Redis handles cleanup

2. Separate keys per job

Pattern: job:progress:{jobId}
Individual queries: GET job:progress:conditional-abc-123
Batch queries: KEYS job:progress:* (for dashboard)

The worker: updating progress during execution

Workers update Redis after completing each chunk:

// firebase.service.ts - Simplified core loop
async sendConditionalNotifications(jobData): Promise {
  const chunks = chunkArray(tokens, 500); // 1,000 chunks for 500K users

  // Initialize progress
  await this.redisService.setProgressByJobId({
    jobId,
    status: 'in_progress',
    totalTargetCount: tokens.length,
    currentSentCount: 0,
    totalChunks: chunks.length,
    currentChunk: 0,
    startedAt: new Date(),
  });

  // Process each chunk
  for (let i = 0; i < chunks.length; i++) {
    const response = await this.fcmService.sendBatch(chunks[i], title, content);

    totalSent += response.successCount;
    totalFailed += response.failureCount;

    // Update Redis every chunk (every 2 seconds)
    await this.redisService.setProgressByJobId({
      jobId,
      status: 'in_progress',
      currentSentCount: totalSent + totalFailed,
      totalChunks: chunks.length,
      currentChunk: i + 1,
      // ... other fields
    });

    await delay(2000); // FCM rate limiting
  }

  // Mark completed
  await this.redisService.setProgressByJobId({
    jobId,
    status: 'completed',
    completedAt: new Date(),
  });

  return true;
}

Why update every chunk?

Users expect near-instant feedback
Redis writes are cheap (<1ms)
For 500K users (1,000 chunks), total overhead is ~1 second over 80 minutes

Three polling APIs for different use cases

1. Single job progress (campaign detail page)

// firebase.controller.ts
@Get('conditional-sends/:jobId/progress')
async getConditionalSendProgress(@Param('jobId') jobId: string) {
  const progress = await this.firebaseService.getConditionalSendProgress(jobId);

  if (!progress) {
    return {
      statusCode: 404,
      message: 'Progress not found. Job may have completed over 1 hour ago.',
      data: null,
    };
  }

  return {
    statusCode: 200,
    message: 'Progress retrieved successfully',
    data: progress,
  };
}

Frontend polling:

// React example
useEffect(() => {
  const interval = setInterval(async () => {
    const response = await fetch(`/api/conditional-sends/${jobId}/progress`);
    const { data } = await response.json();

    if (data) {
      setProgress(data);
      if (data.status === 'completed') clearInterval(interval);
    }
  }, 3000); // Poll every 3 seconds

  return () => clearInterval(interval);
}, [jobId]);

2. All active jobs (dashboard overview)

@Get('conditional-sends/active-progress')
async getAllActiveConditionalSendProgress() {
  const progressList = await this.firebaseService.getAllActiveConditionalSendProgress();

  return {
    statusCode: 200,
    message: progressList.length > 0 
      ? `${progressList.length} active campaigns found`
      : 'No active campaigns',
    data: progressList,
  };
}

// redis.service.ts
async getAllActiveJobProgress(): Promise {
  const keys = await this.client.keys('job:progress:*');
  const results: ScheduleProgress[] = [];

  for (const key of keys) {
    const data = await this.client.get(key);
    if (data) {
      const progress = JSON.parse(data);
      if (progress.status === 'in_progress') {
        results.push(progress);
      }
    }
  }

  return results;
}

3. Database fallback (scheduled sends)

For scheduled campaigns, I added a fallback for when Redis is down:

// scheduler.service.ts
async getAllActiveScheduleProgress(): Promise {
  // Try Redis first
  const progressList = await this.redisService.getAllActiveScheduleProgress();
  if (progressList.length > 0) return progressList;

  // Fallback to database
  const sendingSchedules = await this.scheduleRepository
    .createQueryBuilder('schedule')
    .where('schedule.sent_yn = 1') // Sending started
    .andWhere('schedule.actual_send_start_date IS NOT NULL')
    .andWhere('schedule.actual_send_end_date IS NULL') // Not finished
    .getMany();

  // Convert DB records to progress format (without real-time counts)
  return sendingSchedules.map(schedule => ({
    scheduleSeq: schedule.seq,
    status: 'in_progress',
    totalTargetCount: schedule.total_send_count || 0,
    currentSentCount: 0, // Real-time unavailable
    startedAt: schedule.actual_send_start_date?.toISOString(),
    // ...
  }));
}

Estimating completion time accurately

The most requested feature: "When will this finish?"

For a 500K-user campaign taking 80 minutes, accurate estimates are critical:

// redis.service.ts
private calculateJobEstimatedEndTime(params): string | null {
  if (!params.startedAt || params.currentSentCount >= params.totalTargetCount) {
    return null;
  }

  const startTime = new Date(params.startedAt).getTime();
  const elapsedMs = Date.now() - startTime;

  // Current sending rate
  const currentRate = params.currentSentCount / elapsedMs;

  // Remaining work
  const remainingCount = params.totalTargetCount - params.currentSentCount;
  const remainingChunks = params.totalChunks - params.currentChunk;

  // Chunk delay overhead (2 seconds per chunk)
  const chunkDelayMs = remainingChunks > 0 ? (remainingChunks - 1) * 2000 : 0;

  // Pure sending time
  const pureSendingTimeMs = remainingCount / currentRate;

  // Network overhead (5% buffer)
  const networkOverheadMs = pureSendingTimeMs * 0.05;

  // Total estimate
  const totalRemainingMs = pureSendingTimeMs + chunkDelayMs + networkOverheadMs;
  const estimatedEndAt = new Date(Date.now() + totalRemainingMs);

  return estimatedEndAt.toISOString();
}

Why this works for 80-minute jobs:

Example at 40% completion (chunk 400/1,000):

Elapsed: 32 minutes
Current rate: 200,000 sent / 32 min = 6,250 msg/min
Remaining: 300,000 messages
Pure sending: 300,000 / 6,250 = 48 minutes
Chunk delays: 600 chunks × 2 sec = 20 minutes
Network overhead: 48 min × 5% = 2.4 minutes
Total remaining: ~70 minutes
ETA: 32 + 70 = 102 minutes (accurate within ±10 min)

The estimate adjusts in real-time—if the sending rate slows down due to network issues, the ETA shifts accordingly.

Handling edge cases

Worker crashes mid-send

BullMQ's event handlers update Redis on failure:

// firebase.processor.ts
this.worker.on('failed', async (job, error) => {
  const progress = await this.redisService.getProgressByJobId(job.data.jobId);

  await this.redisService.setProgressByJobId({
    ...progress,
    status: 'failed',
    completedAt: new Date().toISOString(),
    errorMessage: error.message,
  });
});

Frontend sees "Failed" status with partial progress, not phantom "in progress" forever.

Redis restart during send

Workers re-initialize progress on restart:

Worker continues from last BullMQ checkpoint
Re-creates Redis progress entry
Frontend sees brief gap (5-10 seconds), then progress resumes

Polling storm (100+ admins watching)

100 clients polling every 3 seconds = 33 req/sec to Redis.

Redis handles this easily (can do 100K+ ops/sec), but I can add API-level caching:

// Future optimization: 1-second in-memory cache
const cache = new Map();

async getProgress(jobId: string) {
  const cached = cache.get(jobId);
  if (cached && Date.now() < cached.expiresAt) return cached.data;

  const progress = await this.redisService.getProgressByJobId(jobId);
  cache.set(jobId, { data: progress, expiresAt: Date.now() + 1000 });

  return progress;
}

Reduces Redis load by 66% (3-sec poll / 1-sec cache = 3x reduction).

Production metrics and observations

After running this system in production for 3 months:

Performance:

Redis latency: <2ms (p99)
API endpoint latency: 15ms (p50), 45ms (p99)
Memory per job: ~500KB (~50MB for 100 concurrent jobs)

Reliability:

Uptime: 99.97% (1 Redis restart in 3 months)
DB fallback usage: 0.03% of requests
Stale data incidents: 0 (TTL works perfectly)

Real campaign example (500K users):

Start: 2025-01-10 09:00:00
Chunks: 1,000 (500 users each)
Progress updates: 1,000 Redis writes over 80 minutes
Frontend polls: ~1,600 requests (80 min ÷ 3 sec)
Total Redis ops: ~2,600 (writes + reads)
Redis CPU usage: <2%

Cost:

ElastiCache t3.micro ($15/month) handles 50 concurrent large campaigns easily
No database load increase—polling doesn't touch MSSQL

Why not Server-Sent Events (SSE)?

I implemented SSE (it's in the codebase) but didn't deploy it because:

1. Proxy/firewall issues

Corporate networks block long-lived HTTP connections
Users complained: "Progress doesn't update on office WiFi"

2. Load balancer complexity

AWS ALB has 30-second timeout by default
Requires sticky sessions or custom routing

3. Debugging is harder

Polling: Check Network tab, see every request/response
SSE: One opaque stream, harder to inspect

4. Reconnection logic

SSE connections drop on network blips
Frontend needs complex retry handling
Polling auto-recovers (next request succeeds)

The trade-off:

Polling overhead: ~3 requests/minute/user (negligible for Redis)
SSE benefit: ~1 connection/user (not worth the complexity)

For admin tools with <100 concurrent users watching 80-minute jobs, polling is perfect. If I had millions of users watching real-time sports scores, SSE would make sense.

Key takeaways

1. Redis TTL is a superpower

Set it and forget it for ephemeral data
No cron jobs, no manual cleanup
Perfect for progress tracking

2. Polling isn't always bad

For admin dashboards: simpler than WebSockets/SSE
Easy to debug (just curl the endpoint)
Browsers handle it fine (no memory leaks)

3. Database fallbacks provide peace of mind

Redis downtime is rare but real
Users prefer "slightly stale data" over "no data"

4. Estimate completion times carefully

Account for chunk delays (2 sec × 1,000 = 33 minutes)
Add network overhead buffers
Re-calculate on every update for accuracy

5. Scale considerations for 500K users

Total Redis memory: ~500KB per job (50MB for 100 jobs)
1,000 updates over 80 minutes = low write pressure
Polling every 3 seconds = ~1,600 reads per job (easily handled)

If I had to redesign this for 1M+ users, the only change would be reducing update frequency (every 5-10 chunks instead of every chunk) to cut Redis writes by 80-90%.

Have you built similar real-time monitoring for long-running jobs? What approach did you take? I'd love to hear your experiences in the comments.

Forem: Sangwoo Lee

Solving the 100M-Row Problem: A Summary Table Pattern for High-Volume Push Notification Logs

The Problem: 100 Million Rows and Growing

What Was Happening

The Real Cost

The Solution: Summary Table Pattern + Batch Deletion

Architecture Overview

Key Design Constraints

Implementation Deep Dive

Step 1: The Summary Table Entity

Step 2: Triggering Aggregation on Job Completion

Step 4: Deadlock-Safe Batch Deletion

Step 5: All Statistics APIs Now Read from Summary

The Idempotency State Machine

Backfilling Historical Data

Timeline: What Happens After a Send

Performance Results

Before vs After

Resource Usage During Cleanup

Gotchas and Lessons Learned

1. MSSQL Lock Escalation Is a Silent Killer

2. @@ROWCOUNT Is the Only Reliable Termination Signal

3. insert() Over save() for Idempotency

4. SQL Expression for Retry-Safe Accumulation

5. Keep first_sent_at in Summary for Click Rate Calculations

6. The backfill- Job Prefix Guard

API Design After the Change

Statistics APIs — Now Dual-Source

New Monitoring Endpoint

When to Use This Pattern

- The ratio of unique events to total rows is close to 1:1

Conclusion

Key Takeaways

Solving MSSQL's 2100 Parameter Limit: From IN Clause to Cursor-Based Pagination

The Problem: A Mysterious MSSQL Error

Root Cause Analysis

❌ bible-read.service.ts (IN Clause Batch Query)

✅ firebase.service.ts (Cursor-Based Pagination)

Pagination Strategies Compared

The Solution: Migrating to Cursor-Based Pagination

Key Improvements

1. Parameter Count: 10,000 → 3

2. Consistent Pattern Across Services

3. Index-Friendly Queries

Performance Considerations

When Cursor-Based Shines ✅

Potential Drawback ⚠️

Results

Lessons Learned

Alternative Solutions

1. Chunked IN Clauses

2. Temporary Table + JOIN

Conclusion

Building a Smart Bible Reading Reminder System - Personalized Push Notifications at Scale

The Context: Multi-Plan Bible Reading System

The Challenge: Defining "Inactive"

Naive Approach (Wrong!)

The Correct Definition

Implementation: The SQL Query Journey

Attempt 1: HAVING Clause (Wrong!)

Attempt 2: Correct Logic (✅)

The Critical Piece: Personalized Deep-Links

The Problem: Generic vs Personalized

The Solution: Individual Plan Data

NestJS Implementation: The Complete Service

Step 1: Module Setup

Step 2: Configurable Constants

Step 3: Cron Job Scheduler

Step 4: Database Query (The Core Logic)

Step 5: Personalized FCM Payload

Testing and Verification

Manual Trigger API (For Testing)

Count API (Preview Recipients)

Production Results

Key Implementation Patterns

Pattern 1: SQL Window Functions for Multi-Plan Users

Pattern 2: Parameterized Inactivity Threshold

Pattern 3: Personalized FCM Data Arrays

Pattern 4: Dry-Run Mode for Testing

Lessons Learned

2. `@@ROWCOUNT` Is the Only Reliable Termination Signal

3. `insert()` Over `save()` for Idempotency

5. Keep `first_sent_at` in Summary for Click Rate Calculations

6. The `backfill-` Job Prefix Guard

`keepAlive: true`

`keepAliveMsecs: 30000`

`maxSockets: 100`

`maxFreeSockets: 10`

`timeout: 60000`

`scheduling: 'lifo'`

Issue 2: Missing `summary` Field