architecture-decision-records

- Atomic enqueue alongside application writes in PostgreSQL. - Retries, scheduling, and visibility into job execution. - Capacity for current and forecast job volumes. - Low operational complexity and cost. - Protection of primary database performance. ## Considered Options | Option | Benefits | Drawbacks | | --- | --- | --- | | Kafka | Separates job infrastructure from the primary database. | No team production experience; managed cluster quoted at approximately $1,800/month; considered excessive for the current workload. Atomic enqueue with PostgreSQL writes would require an additional coordination pattern. | | Amazon SQS | Low-cost, managed service; separates queue storage from the primary database. | Cannot participate in a PostgreSQL transaction. Would require an outbox table and a mechanism to publish its entries to SQS. | | pg-boss on existing PostgreSQL 16 | Supports transactional enqueue, built-in retries and scheduling, and avoids introducing another infrastructure service. Uses PostgreSQL `SKIP LOCKED` for job claiming. | Adds queue-related load to the primary database and couples job processing capacity to database capacity. | ## Decision Use **pg-boss on the existing PostgreSQL 16 database** for background jobs. Where a job depends on an application write, enqueue it within the same database transaction so that the write and enqueue commit or roll back together. Migrate the existing background jobs to pg-boss and retire the cron scripts after all jobs have been migrated. ## Rationale pg-boss meets the transactional enqueue requirement without introducing an outbox publishing mechanism or another infrastructure service. Its built-in retries and scheduling address key gaps in the current cron-based approach. The team judged Kafka disproportionate to the current workload, cost, and available operational experience. SQS remains a viable alternative, but its operational benefits did not outweigh the additional outbox and publishing mechanism required for transactional consistency. Marco Bianchi preferred SQS but agreed to commit to the pg-boss decision. ## Consequences ### Positive - Application writes and dependent job enqueue operations can be atomic. - Built-in retries and scheduling replace capabilities missing from the current setup. - No additional queue infrastructure service is required. - The team avoids the quoted Kafka cluster cost and the need to develop Kafka operational expertise. ### Negative and Risks - Queue operations add load to the primary PostgreSQL database. - Background processing and application database workloads share capacity and a failure domain. - Migrating the existing scripts requires implementation and validation work. - Job execution visibility must be implemented and verified during rollout; adopting the library alone does not establish operational monitoring. ## Implementation and Follow-up - **Lena Hoffmann:** Complete a pg-boss spike by **2026-11-13**. - Migrate rent reminders, statement PDF generation, and bank-feed imports. - Retire the cron scripts after all jobs have been migrated. - Monitor job throughput and primary database CPU to support the review criteria below. ## Review Triggers Revisit this decision if either condition occurs: - Sustained throughput exceeds **3,000 jobs per minute**. - Primary database CPU remains above **60% at peak**. The measurement windows for “sustained” throughput and persistent peak CPU were not specified in the meeting and need to be defined for monitoring.