24. Automated jobs (cron)

Date: 2026-06-06

Status

Accepted

Context

A few maintenance tasks should run on a schedule: lock the hours of contractors/assignments whose end date has passed, and recompute late-payment interest on overdue invoices. The build plan picks pg_cron → pg_net POST to Next.js route handlers (rather than a separate scheduler), authenticated by a shared secret, with idempotent jobs.

Decision

• Route handlers under /api/cron/<job> (Node runtime), each authenticated by CRON_SHARED_SECRET via cronAuthorized — ?token= or Authorization: Bearer, constant-time compared, fails closed when the secret is unset. Jobs return JSON counts.
  • POST /api/cron/lock-ended → lockEndedAssignments: locks draft/submitted/approved entries of assignments whose end_date has passed (leaves pending_approval; already-locked skipped).
  • POST /api/cron/accrue-interest → accrueOverdueInterest: recomputes interest for overdue invoices using each facility's config and the corrected computeInterest (ADR-0022).
• Idempotent by construction: locking filters on status; interest is set-not-increment. Re-runs are safe, so the schedule cadence doesn't risk double-effects.
• Logic lives in a service (src/server/services/cron.ts), so the same functions can be invoked from a test or a one-off without the HTTP layer.

Consequences

• You wire the schedule at deploy (it needs the deployed URL + the secret) — see docs/cron-setup.md for the pg_cron + pg_net template. The handlers + secret are ready now.
• Interest accrual scans up to 1000 overdue invoices per run (re-runs cover the tail); raise/paginate if the overdue set ever grows beyond that.
• Deferred (need Gmail, Step 12): emailed renewal reminders (90/60/30) and overdue-invoice reminders — the data/alerts exist (contract_renewals, contractAlerts), only delivery is missing.
• Operating timezone is America/Denver; the jobs use a UTC "today" for date comparisons, which is adequate for once-a-day maintenance (revisit if a job becomes time-of-day sensitive).