Counters That Lie: Date Filters and Two Clocks
The counter said 12, the table said 15: the date filter was reading the wrong clock. The fix counts from transition history, not the status column.
TL;DR
The dashboard showed mismatched ticket counts because one query filtered by creation date while the other reflected actual status changes today. Tickets created yesterday but handled today were missed due to timezone and date-boundary quirks in PostgreSQL. Switching all counters to aggregate the transition history by changed_at made the numbers consistent and trustworthy.
1. Judul (H1) # When the Numbers on the Dashboard Never Matched
I opened the KotaPortal admin dashboard that morning, selected today's time range, and was immediately confused. The number in the header said there were 12 tickets that had been processed. But when I scrolled down to the status table below, the total was actually 15.
My first guess was that the frontend cache hadn't refreshed. Or maybe there was a race condition in the API that made the counts on the server and in the database out of sync. I even checked the request logs, looking for query duplication or whether a background job process was running late. But all the logs looked clean and normal.
Turns out the problem wasn't in the cache or a race condition. The trap was in the semantics of the filter tanggal counter admin itself. There were two clocks clashing here: created_at from the main ticket, and changed_at from the transition history table.
I almost closed this case as a cosmetic bug. Good thing I did not. Numbers that disagree on an admin dashboard are not a display problem; they make people take decisions from two different criteria without noticing. Daily reports, SLA targets, even team performance reviews all flow from the same query.
Two Clashing Clocks
Our old query just took the count from the ticket table where the status had changed and created_at was within today's range. This is a huge mistake if a ticket was created last night, but only responded to by the admin this morning. Its status changed today, but its created_at remained yesterday. The result? That ticket wasn't counted in today's filter, even though the admin's work truly happened today.
In PostgreSQL, comparing a date with a timestamp has a hidden trap. The database assumes the date value is midnight in the time zone set in the TimeZone parameter [4]. This is what makes the "today" interval boundary often miss the mark if we aren't careful with the data types and time zone assumptions.
People often mistakenly think that counting the live status column based on creation time is enough. In reality, what we actually want to know is "how many tickets changed their status in this period".
The Solution: Relying on Transition History
The Solution: Relying on Transition History
A correct implementation of the filter tanggal counter admin can no longer rely solely on the live status column in the main table. We must calculate the per-status counters by JOINing the history table based on the selected time period.
SELECT h.new_status, COUNT(*) AS total
FROM ticket_history h
WHERE h.changed_at >= :period_start
AND h.changed_at < :period_end
GROUP BY h.new_status;Why is this approach **more honest**? Because in PostgreSQL, a transaction wraps all steps into a single **all-or-nothing** operation. History rows are written within the same transition transaction. Therefore, counters derived from the history table are just as trustworthy as the transition engine itself [3].
This approach also aligns with the outbox pattern we built earlier to guarantee event consistency. We no longer need to guess whether the number in the header is correct or not.
I ultimately decided to replace all aggregation queries on the dashboard. Not just to make the numbers match, but because honest data is far more important than a query that is short and easy to write.
###
One honest note about the range boundary. The PostgreSQL documentation states that a date value compared against a timestamp is assumed to represent midnight in the TimeZone parameter zone, and comparing a timestamp without a time zone to one with a time zone rotates the naive value using that TimeZone configuration [4]. That is why I make the upper bound exclusive. A ticket whose status changed exactly at midnight lands in its new period, not double-counted in both. A detail this small is what finally makes the header number and the status table sit on the same figure.
A side effect I like: once every counter derives from the history table, adding a new status to the transition engine automatically adds a counter category. No dashboard query needs editing whenever the vocabulary changes.
Sources