sql

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.

One ticket skipped two statuses, a citizen got a duplicate email: the root was a dual-write. The fix is a transition map, 409, history, and an outbox.

TL;DR: Duplicate emails weren't a frontend race condition but a dual-write bug where database updates and SMTP sends weren't atomic. The fix uses a transactional outbox, saving the email to an outbox table in the same Postgres transaction for a background worker to deliver. Illegal status jumps are now blocked early with 409 Conflict using a central transition map.

Swapping a status vocabulary on live MySQL 5.7 tables: expand-migrate-contract, an honestly lossy down file, and an up-down-up proof before production.

TL;DR: Changing submission statuses from four to six values forces a costly MySQL table copy unless new ENUM values are appended. Instead the migration appends all values, remaps the data, then trims to the final list to stay in-place. Rollback is lossy for newer statuses, so history is tracked separately and the flow was verified with an up-down-up test.

Adding a bidang scope filter to a public endpoint sounds like one WHERE clause. Then the global rows disappear.

TL;DR: The new scope filter hid global announcements because WHERE bidang equals value never matches NULL. The fix validates bidang with a 400 on invalid values and changes the query to keep NULL and ALL rows. It also adds bidang to the cache key and moves pillar intros into the settings table with a fallback.

Renaming a public route is three moves at once: relocate the route tree, install a permanent 308, and migrate menu rows without stomping admin edits.

TL;DR: Renaming the public /wisata route to /pariwisata took more than a file move. The team added permanent 308 redirects for the old path and wildcard subpaths to preserve bookmarks and POST requests. A guarded MySQL migration renamed only untouched menu rows, shifted ordering, and left CMS-edited content intact.

A test-only commit: proving the global guard via httptest, and the migration down path on MySQL 5.7 really goes backward, then comes home.

TL;DR: We finally tested two long-ignored paths: the RequireGlobalBidang guard that should block bidang access and the down migrations that had never run. A table-driven unit test now locks in the guard's behavior, including its intentional fail-open for empty contexts. An integration test migrates down to version 56 and back up, verifying columns disappear and reappear correctly.

Adding a ?module= filter is not just a WHERE clause: 400 validation at the handler, a parameterized subquery, and a cache key per filter combination.

TL;DR: A naive SQL-only module filter would poison the shared mappoints:all cache, leaking filtered results to unfiltered requests and vice versa. The fix layers validation against six allowed modules with a 400 error, a parameterized subquery, and branched cache keys by module and category. This isolates each response variant and prevents cross-contamination.

A no-op form save got a 404 because MySQL counts changed rows, not matched rows. The fix: an existence check only on the zero path.

TL;DR: Hitting Save without changes returned 404 because MySQL reports zero affected rows when nothing actually changes. The default driver setting means zero is ambiguous and doesn't reliably indicate a missing record. The fix adds a quick existence check on zero instead of changing the global DSN setting.

A nullable JSON-TEXT column is not covered by omitempty alone: omitempty only knows Go emptiness, not SQL NULL. The story of a four-layer exposure.

TL;DR: A public detail page wasn't showing saved additional info even though the data was in the database. The fix required passing a nullable JSON column through four layers, and realizing Go's omitempty only drops nil, not empty strings. Normalizing null values to nil in the mapper and adding a fallback on the frontend finally hid the empty field correctly.