KakaoTalk sending and template statistics collection server

Period  — 

The server covers AlimTalk, FriendTalk and brand messages. The statistics table was denormalized around user IDs to speed up queries.

Sending statistics for over 8 million messages a month

Team
Solo
Role
DB and flow designBatch schedulerAggregation APIRe‑collection APIData reprocessing scheduler

Java 17Spring Boot 3.5Spring BatchMyBatisMySQL

Statistics that differ by elapsed day

Problem
Read and click statistics from the Kakao API arrive aggregated separately per elapsed day (0, 1, 2). Combining them in one pass produced wrong totals and broke data consistency.
Solution
Every day, the batch pulls all aggregated statistics from the Kakao API. Read and click values are accumulated in three tiers by elapsed days. The batch writes 500 rows at a time to speed up DB writes.

Duplicate rows on re‑collection

Problem
A manual re‑collection API, added for cases where the scheduled batch missed data, made it possible to accumulate the same data several times.
Solution
In the denormalized final table, I built a composite key from the main columns, leaving out those that change. An elapsed_mask column marks elapsed days 0, 1 and 2 as bits 1, 2 and 4. Accumulation checks these bits, so the same elapsed day is never added twice. Collecting the same data again creates no duplicate rows, and the statistics stay consistent.