Publishing at 20:00 Daily, Yet Having Two "Todays": How Time Zone Boundaries in the Pipeline Create Duplicate Days

This content pipeline publishes one article every day at 20:00 (SGT). The slug prefix is `article-YYYYMMDD-`. Before publishing, it checks the CMS: if a row with today’s prefix already exists, it skips; otherwise, it writes a new one. It sounds like a two-line logic.

Last cycle, it failed: two records were inserted on the same day. No errors, no alerts. Both executions believed they were the first article of the day.

The root cause wasn’t a coding error, but the fact that the word "today" has two answers. From 20:00 to 24:00 SGT, the corresponding UTC time is 12:00 to 16:00 on the same day. However, from 00:00 to 08:00 UTC, SGT has already entered the next day. Our scheduler calculates `YYYYMMDD` based on SGT, while the `published_at` timestamp stored in the database is in UTC, and the frontend list groups items by UTC date. The scheduler says, "Today is 2026-08-27," while the database says, "This record belongs to 2026-08-26." Neither side is wrong individually, but combined, they create a conflict.

There are two specific ways this conflict manifests. First: A re-run at 23:50 SGT occurs while it is still the previous day in UTC. The record inserted into the database carries the old date prefix. However, since the execution checks for "today" based on SGT, the duplicate check looks for the new date prefix. Finding none, it publishes another article with the new date prefix, leaving the re-run article under the old date. Second, a more subtle scenario: In the query window after midnight SGT, the prefix gap for "today" hasn’t yet been filled by the scheduled 20:00 task. Any manually triggered re-run might race to claim this prefix.

After the post-mortem, we implemented four fixes, none of which required database migration:

**1. Calculate the date in only one place.** The scheduler, scripts, and pre-insertion logic all uniformly call a single `day_id(utc_offset)` function, hardcoding `offset=+8`. Raw calls to `date.today()` or `now().date()` are prohibited in any code, as these functions use the machine’s local time zone, yielding different results on containers versus macOS. We added a grep rule in CI to fail the build if such raw calls are detected.

**2. Use the same key for duplicate checking and insertion.** Before the fix, "the prefix used for duplicate checking" and "the prefix used for insertion" were derived from two independent time fetches, separated by a few minutes of drafting time. We changed this to fetch the timestamp once, calculate the `day_id`, and then reference this single value for duplicate checking, slug generation, and writing `published_at`, passing it down via variables without re-fetching.

**3. Use unique constraints as a safety net.** Relying solely on "check-then-insert" always leaves a race condition window. We added a unique index on `(slug, locale)` to the table. Duplicate inserts now result in a 500 error, which the scheduler catches and treats as a HOLD status to skip, rather than silently allowing them. Unique constraints are the cheapest enforcer of business rules.

**4. Monitor "daily aggregates," not individual rows.** We changed monitoring to aggregate by `day_id`: if the number of articles in the same category on the same day exceeds 1, an alert is triggered. Individually, each row appears valid; the issue only becomes visible when aggregated. The characteristic of this type of boundary issue is "locally correct, globally excessive."

One additional insight gained only after stumbling into this problem: The larger the time window, the more likely boundary issues are to be averaged out and masked. Our 20:00 publication involves a mere 200ms jitter in time fetching, which is insufficient to trigger the window. What truly activates it is manual re-runs after network outages—these shift the task execution from "on the hour" to "at any arbitrary moment," completely changing behavior near the boundary. Therefore, we added a rule: Re-runs must explicitly pass `--day-id`; they are not allowed to guess "today" on their own.

When sharing this type of issue, people often ask, "Couldn’t you just use distributed locks or unify everything to UTC?" We ultimately stuck with calculating dates in SGT because our readers, operations team, and on-call staff live by local time. Switching to UTC would merely shift the seam from midnight to 8:00 AM; the seam itself would remain. By consolidating date calculation into a single function, letting unique constraints handle conflicts, and using aggregate alerts to monitor the global state, we degraded the time zone boundary issue from a "randomly failing distributed accident" to a "fixed phenomenon requiring attention on only two days a year."