architecture-decision-records

Load today is ~400 jobs/min at the 1st-of-month peak. The sales forecast projects ~1,200 jobs/min by end of 2027. A key requirement is **transactional enqueue**: a job must be enqueued in the same transaction as the row write that triggers it (e.g. a statement row plus its "render PDF" job), so neither can exist without the other. ## Decision Drivers - Reliability: retries and visibility into failed or missed jobs - Transactional enqueue with Postgres writes - Operational simplicity; the team is small - Headroom for the ~3x growth forecast through 2027 - Cost ## Considered Options ### Option 1: Kafka - **Pros**: Very high throughput ceiling. - **Cons**: Nobody on the team has run it in production. A managed cluster was quoted at ~$1,800/month. Far beyond the needed scale (400 jobs/min today, ~1,200 forecast). It also cannot join a Postgres transaction. ### Option 2: Amazon SQS - **Pros**: Cheap and fully managed. - **Cons**: Cannot participate in a Postgres transaction, so we would need an outbox table and a relay process anyway, which adds a moving part and recreates part of what a Postgres-backed queue gives us directly. ### Option 3: Queue in existing PostgreSQL 16 via pg-boss - **Pros**: Transactional enqueue comes for free. No new system to operate. Retries and scheduling are built in. pg-boss uses `SKIP LOCKED` for job claiming. - **Cons**: Adds load to the primary database (see Risks). ## Decision We will use **pg-boss on the existing PostgreSQL 16 database** for background jobs. Once all jobs are migrated, the cron scripts will be retired. ## Consequences ### Positive - Job enqueue is atomic with the originating write, with no outbox needed - One less system to run, monitor, and pay for - Retries and scheduling replace the current no-retry cron behavior - Job state lives in the database, which gives the visibility we lack today ### Negative - Job traffic shares capacity with the primary database - Tied to Postgres; moving to a dedicated broker later means migrating the job layer - Marco preferred SQS. The disagreement was noted and he agreed to commit to this decision. ### Risks - **Primary DB load.** Mitigation: revisit this decision if sustained throughput exceeds **3,000 jobs/min**, or if **DB CPU stays above 60% at peak**. (Forecast peak of ~1,200 jobs/min is well under the throughput trigger.) ## Implementation Notes - Lena Hoffmann to spike pg-boss by **2026-11-13** - Migrate jobs from cron to pg-boss: rent reminders, statement PDFs, bank-feed imports - Retire cron scripts and the VM's job duties after all jobs are migrated - Monitor job throughput and DB CPU at peak against the revisit thresholds ## Related Decisions - None yet ## References - Tidewell platform sync meeting notes, 2026-10-21 - [pg-boss](https://github.com/timgit/pg-boss) ``` Some points to check before merging: 1. **Spike outcome:** The notes say Lena spikes by Nov 13 but don't say whether the decision is contingent on it. I marked it Accepted. If the spike could reverse the decision, use Proposed until it lands. 2. **Migration timeline:** The notes give no dates or order for migrating jobs, so I left them out. You may want to add them. 3. **Monitoring owner:** The notes don't say who watches the 3,000 jobs/min and 60% CPU triggers or where those metrics live. Adding that would make the revisit criteria enforceable. 4. **SQS cost:** The notes only quote Kafka. I described SQS as "cheap" as stated, without a number. 5. **Marco's dissent:** I recorded it neutrally under Consequences. Check that he's comfortable with the wording.