Absensi Sampang — Optimization
A mobile-attendance pipeline redesigned to keep check-in responsive while making asynchronous processing, retries, and data recovery safe.
Business context
A mobile attendance submission needs to feel immediate for the user, even when it carries location, device, and photo data that take longer to process and persist. The dashboard also needs to reflect a check-in or check-out promptly, without waiting for every background step to finish.
The attendance pipeline connects the mobile app, a Slim API, RabbitMQ consumers, MySQL attendance records, and Redis as a temporary read model for the current day. MySQL handles high-frequency attendance writes, so its configuration needs to use the available server resources safely.
The challenge
The original flow performed more database work in the request path and relied on finding the latest attendance record after an insert. MySQL also started from its default configuration, leaving available server resources underused for a high-frequency write workload. Under concurrent processing this adds latency and creates race-condition risk. Retries can also duplicate records or leave data partially written, while an interrupted job needs a safe path to recover missing mobile-attendance records.
Solution design
- Moved the v2 write path behind RabbitMQ so the API validates and publishes the payload while the consumer handles photo work and persistence.
- Used the inserted attendance ID directly, and later LAST_INSERT_ID() with prepared direct SQL, instead of searching for the most recent record.
- Made consumer processing idempotent by checking the attendance log and mobile-attendance record before inserting on retry or requeue.
- Added bounded retry, file locking, logging, database nack/requeue, and non-blocking Redis handling for transient failures.
- Stored a temporary attendance timestamp in Redis so the dashboard can merge it with MySQL data and show the day’s status immediately.
- Reduced high-frequency write overhead with prepared direct SQL that selects and writes only the required fields.
- Built a sync CLI with date/time filters, dry-run, single-record mode, progress logs, and parallel/background execution to backfill missed records safely.
- Replaced the default MySQL configuration with a production-ready configuration tuned to the available server resources and the attendance write workload.