Background Jobs Infrastructure (Pure Postgres + Quartz.NET)

[radandevist]

Summary

Design and implement a background jobs infrastructure for PublyApp using Quartz.NET (manual lifecycle) and a pure PostgreSQL job queue with lease-based concurrency — no RabbitMQ, no Redis.

Motivation

PublyApp needs reliable background job processing for:

• Scheduled post publishing — publish posts at a user-defined future time
• System maintenance jobs — session cleanup, token refresh, scheduled post validation
• Run-on-demand jobs — staff-triggered system jobs from the dashboard
• Crash-safe execution — lease model with heartbeats, dead-letter queue for terminal failures

Architecture Highlights

• Pure Postgres: Single source of truth, no external message broker
• Leader election: pg_try_advisory_lock for Quartz scheduler leadership across N worker replicas
• Two-phase locking: Dispatcher locks scheduled_posts, workers lock job_queue via FOR UPDATE SKIP LOCKED
• Lease model: locked_until column enables crash recovery without false positives
• Delete-on-success: Job queue stays small; DLQ provides audit trail
• Dashboard-configurable system jobs: system_job_definitions table + SyncSystemJobsJob reconciliation (changes take effect within 60s)

Key Components

Component	Role
SchedulerLeaderService	Advisory lock leader election, manages Quartz lifecycle
JobQueueProcessor	Runs on all instances, SELECT ... FOR UPDATE SKIP LOCKED
DispatchDuePostsJob	Quartz trigger (15s), enqueues due scheduled posts
SyncSystemJobsJob	Quartz trigger (60s), reconciles system jobs from DB
RecoverStaleJobsJob	Quartz trigger (5min), resets expired lease jobs

Database Tables

• job_queue — execution queue with lease, retry, and priority support
• dead_letter_jobs — terminal failure audit trail
• system_job_definitions — dashboard-configurable system job schedules
• scheduled_posts — business intent table for future publishing
• rate_limits — API throttling
• qrtz_* — Quartz internal persistence tables

Status

• Architecture design & planning (v4 complete)
• Specification refinement (in progress)
• Implementation

References

• Plan document: docs/PUBLYAPP_JOBS_INFRA_PLAN_V4_COMPLETE.md
• Supporting docs: docs/jobs-infra-planning/