Forem: Sangwoo Lee

Integrating AI into a Legacy Broadcasting CMS: The AI Pipeline Internals

Sangwoo Lee — Mon, 04 May 2026 17:24:00 +0000

Whisper hallucinations, a regex-vs-LLM tradeoff, why the "dumber" model structured sermons better, and what 310 Pinecone vectors actually look like.

This is Part 3. Part 1 covers architecture design. Part 2 covers the debugging process of connecting the legacy system to the Python API.

The Pipeline at a Glance

Once the Python API receives a valid POST from the C binary, the sermon goes through six sequential stages:

MP3 (CDN URL)
    │
    ▼  Stage 1: faster-whisper large-v3
STT Transcript (raw, noisy)
    │
    ▼  Stage 2: rule-based dedup + hallucination filter
STT Transcript (cleaned)
    │
    ▼  Stage 3: regex correction (bible_corrections.json)
STT Transcript (proper nouns fixed)
    │
    ▼  Stage 4: gemma4:e4b via Ollama
STT Transcript (context errors fixed)
    │
    ▼  Stage 5: llama3.1:8b via Ollama
Structured Sermon (paragraphed)
    │
    ▼  Stage 6: Pinecone multilingual-e5-large
Vector DB (upserted, queryable)

Each stage writes its output to disk before the next stage reads it. This made debugging dramatically easier — any stage can be re-run independently without re-running the expensive STT step.

Stage 1: STT with faster-whisper

Whisper large-v3 via faster-whisper runs on the RTX 3060 at float16 precision. The configuration that worked best for sermon audio:

segments, info = model.transcribe(
    str(mp3_path),
    language=lang,
    vad_filter=True,
    vad_parameters=dict(min_silence_duration_ms=500),
    condition_on_previous_text=False,
    no_speech_threshold=0.6,
)

Two settings had outsized impact:

condition_on_previous_text=False: By default, Whisper feeds its previous output as context for the next segment. For long sermons, this causes context drift — the model starts hallucinating repetitions of things it said 10 minutes ago. Disabling it treats each segment independently, which loses some cross-segment coherence but eliminates the repetition artifacts.

no_speech_threshold=0.6: Sermon recordings include musical interludes, silent prayers, and congregational responses. A threshold of 0.6 correctly suppresses these without trimming genuine speech pauses.

For a 40-minute sermon at float16, inference time is approximately 8–10 minutes with Ollama unloaded from VRAM. With Ollama active, this balloons to 25+ minutes due to VRAM pressure (see Part 2).

Stage 2: Cleaning Raw STT Output

Raw Whisper output for a 40-minute sermon typically produces 300–400 segments. These need cleaning before any downstream processing.

Problem 1: Consecutive duplicates

Whisper sometimes repeats the same segment on segment boundaries:

"하나님의 은혜가 충만하기를 바랍니다"
"하나님의 은혜가 충만하기를 바랍니다"  ← duplicate

Problem 2: Fuzzy duplicates

Near-identical segments with minor variations appear when Whisper revisits audio it has partially transcribed:

"주님의 사랑은 영원합니다"
"주님의 사랑은 영원합니다 아멘"  ← similar, not identical

Problem 3: Hallucinations

When Whisper encounters non-speech audio (music, silence, congregational noise), it sometimes outputs confident-sounding Korean text that was never spoken. The pattern is consistent: hallucinations have a disproportionately low ratio of Korean characters relative to the segment length.

# Korean hallucination filter
_KOREAN = re.compile(r'[가-힣]')
_LATIN  = re.compile(r'[a-zA-Z]')

final_texts = [
    t for t in sim_filtered
    if len(t) >= 3
    and len(_KOREAN.findall(t)) > len(_LATIN.findall(t)) * 3
    and len(_KOREAN.findall(t)) > len(t) * 0.3
]

Problem 4: Sliding window similarity

Fuzzy duplicates that slip past the consecutive check are caught with a RapidFuzz similarity filter over a sliding window of the last 10 segments:

from rapidfuzz import fuzz
from collections import deque

window: deque[str] = deque(maxlen=10)
for t in global_dedup:
    is_dup = any(
        fuzz.ratio(t, p) >= 85 or (len(t) > 5 and (t in p or p in t))
        for p in window
    )
    if not is_dup:
        sim_filtered.append(t)
        window.append(t)

After these four filters, a typical 338-segment transcript is reduced to approximately 300 clean, unique segments — roughly a 10–15% noise reduction before any LLM processing.

Stage 3: Deterministic Bible Name Correction

This was the most domain-specific part of the pipeline and also the most impactful per unit of engineering effort.

Korean sermon transcripts have a specific failure mode: Whisper consistently mishears Bible proper nouns. The names are uncommon in general Korean text, so the language model defaults to phonetically similar common words:

Spoken	Whisper output	Correct
느헤미야 (Nehemiah)	노에미아	느헤미야
전도서 (Ecclesiastes)	전도 서	전도서
금식 (fasting)	검식	금식
성령 (Holy Spirit)	성냥	성령

My first instinct was to ask an LLM to correct these. This was wrong for three reasons:

LLMs occasionally "correct" the correction — changing a rare Bible name to something more common
Processing a full transcript through an LLM for known patterns adds latency with no benefit
Deterministic regex can be tested exhaustively; LLM behavior cannot The correction layer uses a JSON pattern database:

{
  "books": [
    {
      "pattern": "노에미[아야]|노예미[아야]",
      "replacement": "느헤미야",
      "note": "Nehemiah"
    },
    {
      "pattern": "마태[복봉]음|마태오금",
      "replacement": "마태복음",
      "note": "Matthew"
    }
  ],
  "terms": [
    {
      "pattern": "검[식씩]하[며면]",
      "replacement": "금식하며",
      "note": "fasting"
    },
    {
      "pattern": "성[냥낭]",
      "replacement": "성령",
      "note": "Holy Spirit (context: religious)"
    }
  ]
}

Corrections run via re.subn() sequentially. Each correction logs its count:

[BIBLE] 하나님: 30건 교정
[BIBLE] 성령: 12건 교정
[BIBLE] 예수님: 11건 교정
[성경명사 교정] 완료: 총 86건 교정

The false positive problem: Patterns that are too broad cause silent damage. Early versions matched "누가" (a common Korean question word meaning "who") before ensuring it was part of "누가복음" (Gospel of Luke). The pattern "누가 보면" ("if you look") was being rewritten to "요엘 보면" — meaningless and wrong.

The solution was context anchoring: all patterns now require sufficient surrounding characters or use negative lookahead to prevent matches in common grammatical contexts.

Stage 4: LLM Context Correction (gemma4:e4b)

After deterministic correction, residual STT errors remain — the ones that require semantic context to resolve. "높은 의자" ("high chair") versus "높은 이자" ("high interest rate") can only be disambiguated by reading the surrounding sentences.

system_prompt = """You are a Korean STT post-correction specialist.
Fix ONLY words that are clearly wrong due to STT mishearing, using surrounding context.
Bible proper nouns have already been corrected upstream — do NOT modify them.
Do NOT add, delete, or restructure sentences. When uncertain, preserve the original.
Output ONLY the corrected Korean text, nothing else."""

The transcript is split into ~1,500-character chunks with 1-sentence overlap to preserve cross-boundary context. Each chunk is validated for size before acceptance: if the LLM output is less than 70% of the input length, it's a sign the model hallucinated a summary or omitted content, and the original chunk is used instead.

ratio = len(corrected) / len(original)
if ratio < 0.7:
    print(f"[WARN] 청크 {i} 결과 너무 짧음 → 원문 사용")
    corrected = original  # fallback

At gemma4:e4b (4B effective parameters), each chunk takes 30–40 seconds on the RTX 3060. A full sermon (6 chunks) takes 3–4 minutes.

Stage 5: Paragraph Structuring — Why the "Dumber" Model Won

This stage was the most counterintuitive finding in the entire project.

My first attempt used exaone3.5:7.8b — a Korean-specialized LLM developed by LG AI Research. For RAG answer generation it's excellent (more on this in the QA section). But for paragraph structuring, it was actively harmful: it "corrected" STT-era errors by substituting synonyms and occasionally rewrote entire phrases. The resulting text was cleaner Korean — but it no longer represented what the pastor actually said.

The solution was llama3.1:8b, a model that understands Korean well enough to identify topic transitions but not well enough to paraphrase fluently. It inserts paragraph breaks without rewriting content.

system_prompt = """You are a Korean sermon text formatter, not an editor.
Your tasks, and ONLY your tasks:
1. Add natural Korean sentence-ending punctuation (. , ? !)
2. Insert a blank line at genuine topic transitions (target: 3–8 sentences per paragraph)

Rules:
- NEVER alter, add, or remove any word
- If uncertain whether a transition exists, do NOT insert a break
- Output only the formatted Korean text"""

Word preservation validation: After each chunk, the output is compared against the pre-correction transcript (the Stage 2 output, before any LLM changes) using a Korean morpheme tokenizer (KiwiPy). If fewer than 90% of the original content words are present in the LLM output, the chunk falls back to a rule-based paragraph splitter.

청크 1/6 처리 중... (1,453자)
  단어 보존율 부족 (26.7%) → fallback
청크 3/6 처리 중... (1,428자)
  단어 보존율 부족 (32.3%) → fallback
fallback: 6/6청크 (100%)
완료! 소요: 651.4초

In the test above, 100% of chunks fell back to the rule-based splitter. This is a known issue: llama3.1:8b's structuring quality is inconsistent on Korean-only input. The fallback ensures the output is always acceptable, just without LLM-quality paragraph segmentation. Improving Stage 5 quality — either through prompt refinement or a better Korean structuring model — is the top priority for the next iteration.

Stage 6: Pinecone Upload

The structured sermon is chunked into overlapping segments before embedding:

def chunk_text(text: str, max_chars: int = 1800, min_chars: int = 80) -> list[str]:
    sentences = [s.strip() for s in text.split('\n') if s.strip()]
    chunks = []
    current = []
    current_len = 0

    for sentence in sentences:
        if current_len + len(sentence) > max_chars and current:
            chunks.append(' '.join(current))
            # 1-sentence overlap for cross-chunk context
            current = [current[-1], sentence]
            current_len = len(current[-2]) + len(sentence)
        else:
            current.append(sentence)
            current_len += len(sentence)

    if current:
        chunks.append(' '.join(current))
    return [c for c in chunks if len(c) >= min_chars]

Each chunk is embedded via Pinecone's hosted multilingual-e5-large (1024 dimensions, multilingual support) and upserted with metadata:

vectors = [
    {
        "id": f"{sermon_id}_chunk_{i}",
        "values": embedding,
        "metadata": {
            "sermon_id":    sermon_id,      # e.g., "STHNK_20260427"
            "program_code": program_code,   # e.g., "HNK"
            "date":         date_str,       # e.g., "2026-04-27"
            "text":         chunk,
            "chunk_index":  i,
            "chunk_total":  len(chunks),
        }
    }
    for i, (chunk, embedding) in enumerate(zip(chunks, embeddings))
]
index.upsert(vectors=vectors, namespace="multilingual-e5")

Using the sermon ID as part of the vector ID means re-uploading the same sermon is idempotent — upsert semantics handle the deduplication automatically. No manual cleanup is needed if a sermon is reprocessed.

After processing STHNK_20260427:

[3/4] Pinecone 연결...
  연결 성공 | 기존 벡터 수: 283개

[4/4] 임베딩 생성 및 업로드...
  업서트 완료: 27개
  인덱스 총합: 310개

The QA Layer

With vectors in Pinecone, staff can query the sermon archive:

@app.route("/ask", methods=["POST"])
def ask_endpoint():
    query = request.get_json()["query"]

    # Embed the question
    query_vector = pinecone_embed(query)

    # Retrieve top-5 relevant chunks
    results = index.query(
        vector=query_vector,
        top_k=5,
        namespace="multilingual-e5",
        include_metadata=True
    )

    # Filter by relevance score
    relevant = [r for r in results.matches if r.score >= 0.78]
    if not relevant:
        return jsonify({"answer": "관련 설교를 찾을 수 없습니다."}), 200

    # Build context and ask the LLM
    context = "\n\n".join([r.metadata["text"] for r in relevant])
    answer  = ollama_rag(query, context, model="exaone3.5:7.8b")

    return jsonify({"answer": answer}), 200

exaone3.5:7.8b (Korean-specialized, LG AI Research) is used here because the task is answer generation, not content preservation. The model reads retrieved sermon passages and synthesizes a coherent Korean response — exactly the task where its Korean fluency is an asset rather than a risk.

Current Status

At time of writing, the pipeline is processing sermons from 140 pastors. The Pinecone index contains 310 vectors across multiple sermons. The QA system is in internal testing with pastoral staff.

Known gaps:

Bible verse citations (chapter/verse numbers) are frequently mangled by STT and not yet corrected deterministically
llama3.1:8b structuring quality is inconsistent — fallback rate is high (60–100% of chunks on some sermons)
The Python API runs on a developer workstation; a GPU server is needed for production Planned next steps:
Domain-adapted STT model fine-tuned on corrected sermon transcripts (target: 30–60 transcripts as training data)
Migration of the Python API to a GPU-enabled server (same code, one IP change)

- Surfacing the QA interface inside the CMS for pastoral staff use

Reflections

The most durable lessons from this project weren't about AI — they were about working with systems that predate modern tooling.

Observability is the first feature, not the last. The hardest bugs were the ones where the system silently did nothing. Building GET /status/{job_id}, logging every pipeline stage to stdout, and writing intermediate files to disk turned a black box into a debuggable system.

Understand why constraints exist before working around them. The $cms_rows < 3 queue guard looked like an obstacle. It was actually protecting the server from a real risk. Bypassing it correctly required understanding that MP3 encoding is categorically different from HD video encoding — not just "another type of file."

Deterministic logic beats LLM logic for known patterns. 86 Bible name corrections per sermon, applied in milliseconds, with zero hallucination risk. The LLM does what rules can't — context-dependent disambiguation. The rules do what LLMs shouldn't — deterministic, auditable correction of known failure modes.

The "dumber" model is sometimes the right model. Fluency is a liability when the task requires content preservation. A model that can't write sophisticated Korean can't silently rewrite a pastor's words either.

Full source (api_server.py, structure_sermon.py, fix_bible_stt.py, upload_to_pinecone.py, qa_sermon.py) — contact for details.

Stack: Python · Flask · faster-whisper · Ollama · gemma4:e4b · llama3.1:8b · exaone3.5:7.8b · Pinecone · KiwiPy · RapidFuzz

Integrating AI into a Legacy Broadcasting CMS: Implementation & Debugging

Sangwoo Lee — Mon, 27 Apr 2026 17:57:00 +0000

Getting PHP, a C binary, Python, and Ollama to reliably hand off through a live encoding server — and the non-obvious failures along the way.

This is Part 2. Part 1 covers the architecture design and the constraints that shaped every decision here.

Debugging a Black Box Pipeline

The most frustrating aspect of this project was that the core pipeline — PHP invoking a C binary via exec() — runs silently in the background:

exec("/home/cms/exec/transcoding_ai $list_idx $mp3_url $output_filename > /dev/null &");

> /dev/null & means stdout is discarded and the process is detached. When something goes wrong, there's no error output, no stack trace, no signal. The CMS admin sees a successful submission. The encoding server logs nothing. The Python API receives no request. You're debugging a black box.

This forced me to build observability into every layer before writing any feature code.

Step 1: Establishing Ground Truth on Each Server

Before touching the AI pipeline, I verified the existing pipeline worked end-to-end. This sounds obvious, but it surfaced two bugs that would have wasted days later.

Finding 1: The C binary didn't exist yet.

I had written transcoding_ai.c and assumed it was compiled. It wasn't.

$ ls -la /home/cms/exec/transcoding_ai
ls: cannot access '/home/cms/exec/transcoding_ai': No such file or directory

This was the reason the AI pipeline never fired — PHP called an executable that didn't exist, exec() silently failed, and everything looked fine from the CMS.

# Compile on 227 server
cd /home/cms/exec/
gcc -o transcoding_ai transcoding_ai.c \
    $(mysql_config --cflags --libs) \
    -lm

chmod +x transcoding_ai

Note: I initially tried -lcurl since the C code makes HTTP requests. This failed with /usr/bin/ld: cannot find -lcurl. The fix was realizing that the code used popen() to invoke curl as a shell command — not libcurl as a linked library. -lcurl was never needed.

Finding 2: The wrong server was being targeted.

The ASP layer assigns content to either server T1 (227) or T2 (226) based on load balancing:

If transcoder1_count < transcoder2_count Then
    transcoder_kind = "T1"
    transcoder_url = "http://192.168.1.227/transcoding_ai.php"
Else
    transcoder_kind = "T2"
    transcoder_url = "http://192.168.1.226/transcoding_ai.php"
End If

I had been SSH'd into 226 looking for logs while the actual traffic was hitting 227. The MySQL job table on 226 showed nothing because no jobs had been routed there. Once I checked the transcoder_kind column in MSSQL for the test content, the correct server was immediately obvious.

Lesson: Always verify which physical server is actually handling a request before assuming the code is wrong.

Step 2: The $cms_rows < 3 Deadlock

After compiling the binary and confirming the correct server, the pipeline still didn't fire. Checking the MySQL queue:

mysql -u cms -pcms cms -e \
  "SELECT COUNT(*) as running FROM cms WHERE status='Y';"
# → 3

The existing queue guard was blocking everything:

$cms_sql  = "SELECT idx FROM cms WHERE status = 'Y'";
$cms_rows = mysqli_num_rows(mysqli_query($mysqlCon, $cms_sql));

if ($cms_rows < 3) {  // 3 < 3 = FALSE
    exec($shell_exec);  // Never reached
}

The two Linux servers were actively encoding HD video — a job that takes 30–60 minutes. The status='Y' counter stayed at 3, permanently blocking any new jobs, including the MP3 AI job.

My first fix was wrong: I added a direct curl call from PHP to the Python API, bypassing the C binary entirely. This appeared to work — the Python API received the request — but the MP3 download failed with a 404. The CDN URL didn't exist yet because the C binary hadn't run ffmpeg to produce the MP3 file.

❌ Wrong flow:
PHP → Python API immediately → curl CDN URL → 404 (file doesn't exist yet)

✅ Correct flow:
PHP → C binary → ffmpeg MP3 encoding → FTP to CDN → Python API → CDN URL exists

The fix was to understand what the queue guard was actually protecting. It existed for CPU-intensive HD video encoding. MP3 extraction is audio-only (-vn flag, no video codec) and runs in a fraction of the time. The guard should never have applied to it.

// Final logic: two separate code paths
if (substr($output_filename, -4) === '.mp3' && $ai_learn_YN === 'Y') {
    // MP3 + AI: always run, regardless of queue depth
    $shell_exec = "/home/cms/exec/transcoding_ai $list_idx $mp3_url $output_filename > /dev/null &";
    exec($shell_exec, $out, $err);
} else if ($cms_rows < 3) {
    // Video/other: respect queue limit
    $mp3_url    = "not_mp3";
    $shell_exec = "/home/cms/exec/transcoding_ai $list_idx $mp3_url $output_filename > /dev/null &";
    exec($shell_exec, $out, $err);
}

Step 3: PM2 Port Collision

While debugging, I had started the Python server using PM2 for persistence. This introduced a subtle problem: PM2 was holding port 8001 even after I started the server manually in a separate terminal.

$ pm2 list
┌────┬─────────────┬──────┬──────┬──────────┐
│ 0  │ sermon-api  │ fork │  3   │ stopped  │  ← ghost process
│ 1  │ sermon-api  │ fork │  0   │ online   │  ← memory: 4.1mb (wrong)
└────┴─────────────┴──────┴──────┴──────────┘

The 4.1mb memory figure was the tell — a properly loaded Python server with Whisper imports should use 40–50mb minimum at idle. The PM2 process was a zombie that had claimed the port but wasn't actually running the Flask app.

Health check requests from the monitoring loop were returning 200 (hitting the zombie), masking the real problem. Manual python api_server.py in a terminal would fail silently because the port was already bound.

# Full cleanup required
pm2 delete all
pm2 kill
pm2 cleardump

After this, running python api_server.py directly in a terminal (with venv activated) became the stable operating mode. For this pilot, the simplicity and observability of a foreground process outweigh the convenience of a process manager.

Step 4: The Python API Handoff

With PHP, the C binary, and the port conflict resolved, I ran the first successful end-to-end manual test:

# On 227 server, directly invoking the binary
/home/cms/exec/transcoding_ai \
  917700 \
  "https://online.goodtv.co.kr/mp3/2026/YBD/STYBD_20260426.mp3" \
  "STYBD_20260426.mp3"

Output on the Linux server:

====== Python AI API 호출 ======
[OK] Python API 호출 성공 (HTTP 202)

Simultaneous output on the Python server (my PC):

[QUEUE] [Job:2746c8fe|ko] STYBD_20260426.mp3
  [OK] lang/ko/ 파이프라인 로드 완료
  [Job:2746c8fe|ko] downloading | MP3 다운로드 중
  [OK] 다운로드 완료: STYBD_20260426.mp3 (24.2 MB)
  [Job:2746c8fe|ko] stt | STT 변환 + 텍스트 정제 중 (lang=ko)...

The first automated trigger from a real CMS upload followed the next day after the PHP fix was deployed.

The Python API: Async Job Architecture

The Flask server receives a POST and returns 202 Accepted in milliseconds. All processing happens in a background thread. This is essential — the PHP script has a response timeout, and the AI pipeline takes 20–40 minutes.

@app.route("/process_url", methods=["POST"])
def process_url():
    body         = request.get_json(force=True)
    mp3_url      = body.get("mp3_url",      "").strip()
    mp3_filename = body.get("mp3_filename",  "").strip()
    lang         = body.get("lang",          "ko").strip().lower()

    # Duplicate job prevention
    with JOB_STORE_LOCK:
        running = [
            j for j in JOB_STORE.values()
            if j.get("mp3") == mp3_filename
            and j.get("lang") == lang
            and j["status"] not in ("done", "error")
        ]
    if running:
        return jsonify({"error": "already processing", "job_id": running[0]["job_id"]}), 409

    job_id = str(uuid.uuid4())
    with JOB_STORE_LOCK:
        JOB_STORE[job_id] = {
            "job_id":     job_id,
            "mp3":        mp3_filename,
            "lang":       lang,
            "status":     "queued",
            "started_at": time.time(),
        }

    threading.Thread(
        target=run_pipeline,
        args=(job_id, mp3_url, mp3_filename, lang),
        daemon=True
    ).start()

    return jsonify({"job_id": job_id, "status": "queued"}), 202

Key design choices:

In-memory job store: sufficient for pilot scale; Redis would be the production replacement
TTL-based cleanup: completed jobs are garbage-collected after 1 hour via a recurring timer thread
Whisper singleton: the model (~6GB VRAM) is loaded once and reused across jobs via double-checked locking to prevent race conditions on concurrent requests

- STT semaphore: a `threading.Semaphore(1)` limits concurrent Whisper inference to one job, preventing GPU OOM

The VRAM Contention Problem

During testing, STT for a 40-minute sermon was taking 25+ minutes — far longer than expected for an RTX 3060 at float16 precision.

nvidia-smi revealed the issue:

Memory-Usage: 9167MiB / 12288MiB
GPU-Util: 35%
Processes: ollama.exe (two instances), python.exe

Ollama had loaded gemma4:e4b (≈9.6GB) into VRAM for the LLM stages. Whisper large-v3 (≈6GB) was competing for the same 12GB budget, spilling to shared system memory and degrading throughput significantly.

The fix: unload Ollama before running STT, then allow it to reload for the LLM stages.

def _run_stt(mp3_path, transcript_file, lang="ko"):
    import subprocess
    # Free VRAM before loading Whisper
    subprocess.run(["ollama", "stop", "gemma4:e4b"], capture_output=True)

    with _stt_semaphore:
        # ... Whisper inference ...
        pass

    # Ollama reloads automatically when next called

This reduced STT time for a 40-minute sermon from ~25 minutes to ~8 minutes.

Monitoring Under Production Conditions

The foreground Flask server prints all pipeline stages to stdout. For the pilot, this is the monitoring interface:

192.168.1.227 - - "POST /process_url HTTP/1.1" 202
[QUEUE] [Job:a319d4a9|ko] STHNK_20260427.mp3
  [OK] 다운로드 완료: STHNK_20260427.mp3 (22.6 MB)
  STT: 338구간 [01:28, 3.80구간/s]
  [OK] 정제 완료: 334개 문장
  [BIBLE] 하나님: 30건 교정
  [BIBLE] 성령: 12건 교정
  [OK] 문맥 교정 완료
  완료! 소요: 651.4초
  업서트 완료: 27개 (누계: 310개)
  [OK] 파이프라인 완료: STHNK_20260427.mp3

Status is also queryable via GET /status/{job_id} — useful when monitoring from a second terminal without watching the log stream.

One key lesson about PowerShell and tqdm: tqdm uses carriage return (\r) to overwrite the current line. Windows PowerShell renders \r differently than a Linux terminal, making it look like the STT has stalled when it's actually progressing normally. A quick GPU utilization check (nvidia-smi) confirms actual activity. Adding flush=True to all print calls resolved the visual lag in the application logs.

In Part 3, I'll cover the AI pipeline internals in detail: how Bible-specific STT correction was implemented, why the "dumber" LLM produced better sermon structuring results, and what the Pinecone vector schema looks like for multi-pastor RAG retrieval.

Stack: PHP · C (gcc) · Python · Flask · threading · faster-whisper · Ollama · Pinecone · NVIDIA RTX 3060

Integrating AI into a Legacy Broadcasting CMS(Content-uploading Manager System): Architecture Design

Sangwoo Lee — Thu, 23 Apr 2026 06:56:04 +0000

How I designed a sermon AI pipeline that bridges a 20-year-old ASP Classic system with modern STT, LLM structuring, and vector search — without touching the legacy core.

The Problem

I work at a Korean Christian broadcasting company (GOODTV) serving over 900,000 users. Every week, 140 pastors upload sermon MP3s through an internal Windows-based CMS built on ASP Classic — a system that has been running for over two decades.

The ask from leadership: "Make it searchable by AI."

Specifically, they wanted a RAG (Retrieval-Augmented Generation) system where staff could ask natural language questions like "What did Pastor Kim preach about forgiveness last year?" and get accurate, sourced answers from the sermon archive.

Simple enough in theory. The challenge was the infrastructure I had to work with.

Mapping the Legacy Stack

Before writing a single line of new code, I spent time fully understanding the existing system. This step turned out to be the most important one.

Windows CMS Server (192.168.1.228)
  └── ASP Classic (.asp files, IIS)
  └── MSSQL database
  └── UI for 140 pastors uploading weekly sermons

Linux APM Servers × 2 (192.168.1.226 / 192.168.1.227)
  └── Apache + PHP transcoding pipeline
  └── MySQL (job queue)
  └── C binary: handles ffmpeg encoding + FTP upload

Developer Local PC (192.168.1.138)
  └── Python (Whisper, Ollama, Pinecone client)
  └── NVIDIA RTX 3060 12GB

The transcoding pipeline worked like this: when an admin submitted content in the CMS, the ASP page HTTP-POSTed the content index (idx) to a PHP file on one of the Linux servers. The PHP file queried the MSSQL database, built ffmpeg commands, inserted job records into MySQL, and conditionally invoked a compiled C binary via exec(). The C binary handled the actual media processing.

The key detail: the C binary already had the MP3 URL as a command-line argument.

That was my integration point.

The Core Design Constraint: Don't Break What Works

The most important architectural decision I made was also the most conservative: inject into the existing pipeline at its boundaries without modifying any core logic.

The reasoning was straightforward. The CMS had accumulated years of edge cases, business logic, and undocumented behavior. A 20-year-old ASP Classic system touching 140 active users has a blast radius that's hard to overestimate. Modifying the core meant risking sermon upload failures across the entire organization — not a trade-off worth making for a pilot feature.

So the architecture I designed respected these hard constraints:

No changes to ASP Classic core files — only additive UI changes (one checkbox)
No changes to the original C binary — a new _ai variant handles the AI path
No changes to MySQL job schema — new behavior piggybacked on existing structure
The AI pipeline failure must never affect the encoding pipeline — graceful degradation was non-negotiable The result:

[CMS 228] Admin checks "AI Learn" checkbox → submits content
    │
    ▼  HTTP POST (async, non-blocking)
[PHP 226/227] transcoding_ai.php
    ├── Query MSSQL: ai_learn_YN = 'Y'?
    ├── Is output file .mp3?
    └── If both true → MP3 path bypasses the 3-job queue limit
            │
            ▼  exec() → background
    [C binary] transcoding_ai
        ├── Existing ffmpeg encoding (unchanged logic)
        ├── FTP upload to CDN
        └── NEW: curl POST → Python API (fire-and-forget)
                │
                ▼  HTTP POST /process_url  →  202 Accepted
    [Python 138:8001] api_server.py
        ├── Download MP3 from CDN
        ├── Whisper large-v3 STT
        ├── Rule-based Bible name correction
        ├── LLM context correction (Ollama/gemma4)
        ├── LLM paragraph structuring (Ollama/llama3.1)
        └── Pinecone vector upload

The Gating Mechanism: ai_learn_YN

Not every sermon upload should trigger AI processing. Admins needed explicit control — for cost reasons, for content sensitivity, and because the AI pipeline takes 20–40 minutes per sermon.

I added a single column to the existing MSSQL content table:

ALTER TABLE content_list ADD ai_learn_YN CHAR(1) DEFAULT 'N';
ALTER TABLE program_list ADD ai_learn_YN CHAR(1) DEFAULT 'N';

And surfaced it as a checkbox in the ASP Classic UI. The PHP layer reads this flag before invoking the C binary:

// Queried once, outside the profile loop
$ai_learn_sql    = "SELECT ai_learn_YN FROM content_list WHERE idx = $idx";
$ai_learn_result = mssql_fetch_array(mssql_query($ai_learn_sql, $con));
$ai_learn_YN     = trim($ai_learn_result['ai_learn_YN']);

// Inside the while loop over content_platform_profile_list
if (substr($output_filename, -4) === '.mp3' && $ai_learn_YN === 'Y') {
    // MP3 + AI flag: bypass queue limit, always execute
    $mp3_url    = "https://online.goodtv.co.kr/" . $ftp_path . $output_filename;
    $shell_exec = "/home/cms/exec/transcoding_ai $list_idx $mp3_url $output_filename > /dev/null &";
    exec($shell_exec, $out, $err);
} else if ($cms_rows < 3) {
    // Non-MP3: respect existing 3-job queue limit for server protection
    $mp3_url    = "not_mp3";
    $shell_exec = "/home/cms/exec/transcoding_ai $list_idx $mp3_url $output_filename > /dev/null &";
    exec($shell_exec, $out, $err);
}

Two conditions gate the AI path. This prevents video sermons, thumbnails, and announcements from hitting the pipeline, and gives admins fine-grained control over what gets learned.

A Non-Obvious Decision: MP3 vs. Video Queue Handling

The original PHP code had a queue guard — if ($cms_rows < 3) — that prevented more than 3 concurrent ffmpeg jobs. This existed to protect the Linux servers from CPU overload during HD video encoding.

My initial implementation put the MP3 AI path inside this same guard. This turned out to be wrong: when the servers were busy encoding video (which they almost always are), the MP3 AI pipeline would never execute.

The fix required understanding why the guard existed. ffmpeg video encoding is CPU-intensive, taking 30–60 minutes per job at high bitrate. MP3 extraction is just audio demuxing — it's an order of magnitude cheaper:

# Video encoding (CPU-heavy, 30–60 min)
ffmpeg -i input.mpg -vcodec libx264 -s 1920x1080 -b:v 4000k output.mp4

# MP3 extraction (lightweight, 2–5 min)
ffmpeg -i input.mpg -vn -acodec libmp3lame -b:a 128k output.mp3

Once I understood this, the fix was clean: MP3 + AI jobs bypass the queue limit entirely. Video jobs keep the existing protection. The server load profile is unchanged; the AI pipeline is no longer bottlenecked.

Why a Local PC, Not a Cloud Server?

The Whisper large-v3 model requires a GPU. The Linux APM servers are CPU-only. Our organization wasn't ready to provision a GPU EC2 instance for a pilot project.

Running the Python API on my local workstation with an RTX 3060 was a deliberate trade-off:

Pro: Zero infrastructure cost for the pilot; full control over the environment
Pro: Faster iteration — code changes are instant, no deployment cycle
Con: Single point of failure; PC shutdown kills the pipeline
Con: Not production-ready; dependent on a developer's personal machine This was explicitly scoped as a pilot. The Python API is network-accessible on 192.168.1.138:8001, reachable from both Linux servers. Moving to production means deploying the same Flask + Python code to any GPU-enabled instance and changing one IP constant in the C binary.

What the Final Architecture Achieves

┌─────────────────────────────────────────────────────────────┐
│  PROPERTIES                                                 │
│                                                             │
│  ✅ Zero-downtime deployment: PHP and C binary swapped     │
│     while CMS served 140 active users                      │
│                                                             │
│  ✅ Failure isolation: Python API failure → C binary logs  │
│     the error and exits cleanly; encoding succeeds         │
│                                                             │
│  ✅ Idempotent uploads: Pinecone upsert semantics prevent  │
│     duplicate vectors on retry                             │
│                                                             │
│  ✅ Duplicate job prevention: 409 on second POST for the   │
│     same mp3_filename while job is active                  │
│                                                             │
│  ✅ Observable: job status queryable at GET /status/{id}   │
│                                                             │
│  ✅ Language-extensible: lang/ directory structure allows  │
│     adding English pipeline without touching Korean code   │
└─────────────────────────────────────────────────────────────┘

In Part 2, I'll cover the actual implementation: the debugging process of getting PHP → C binary → Python API to reliably hand off through a live encoding server, the multi-stage AI pipeline internals, and the non-obvious problems that showed up only under real production conditions.

Stack: ASP Classic · MSSQL · PHP · C (gcc) · Python · Flask · faster-whisper · Ollama · Pinecone

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

Forem: Sangwoo Lee

Integrating AI into a Legacy Broadcasting CMS: The AI Pipeline Internals

The Pipeline at a Glance

Stage 1: STT with faster-whisper

Stage 2: Cleaning Raw STT Output

Stage 3: Deterministic Bible Name Correction

Stage 4: LLM Context Correction (gemma4:e4b)

Stage 5: Paragraph Structuring — Why the "Dumber" Model Won

Stage 6: Pinecone Upload

The QA Layer

Current Status

- Surfacing the QA interface inside the CMS for pastoral staff use

Reflections

Integrating AI into a Legacy Broadcasting CMS: Implementation & Debugging

Debugging a Black Box Pipeline

Step 1: Establishing Ground Truth on Each Server

Step 2: The $cms_rows < 3 Deadlock

Step 3: PM2 Port Collision

Step 4: The Python API Handoff

The Python API: Async Job Architecture

- STT semaphore: a threading.Semaphore(1) limits concurrent Whisper inference to one job, preventing GPU OOM

The VRAM Contention Problem

Monitoring Under Production Conditions

Integrating AI into a Legacy Broadcasting CMS(Content-uploading Manager System): Architecture Design

The Problem

Mapping the Legacy Stack

The Core Design Constraint: Don't Break What Works

The Gating Mechanism: ai_learn_YN

A Non-Obvious Decision: MP3 vs. Video Queue Handling

Why a Local PC, Not a Cloud Server?

What the Final Architecture Achieves

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

- STT semaphore: a `threading.Semaphore(1)` limits concurrent Whisper inference to one job, preventing GPU OOM

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'`