background_job_runs
This table stores application-visible execution records for asynchronous jobs such as OCR processing, notifications, PDF generation, and thumbnail creation.
Purpose
The purpose of this table is to expose operational visibility into background processing without depending only on internal queue tables such as those used by pg-boss or other queue infrastructure.
What this table does
- Tracks the lifecycle of background jobs
- Provides status visibility for support and admin views
- Connects jobs to the business resources they are working on
- Stores retry attempts and failure details for troubleshooting
Why this table is defined
Queue infrastructure tables are often too low-level or operationally noisy for application-facing visibility. DirtView benefits from a cleaner job-history table tied to business objects like files, drawings, and notifications.
Columns
| Column | Type | Required | Description | Example |
|---|---|---|---|---|
id | uuid | Yes | Primary key for the job run | job_001 |
tenant_id | uuid | No | Tenant scope if relevant | tenant_001 |
job_type | text | Yes | Type of async job | ocr.process_drawing |
resource_type | text | No | Business resource type | drawing_version |
resource_id | uuid | No | Business resource ID | dver_003 |
status | text | Yes | Queued/running/succeeded/failed/dead_letter | running |
attempt_count | int | Yes | Retry attempt count | 2 |
error_message | text | No | Failure summary if the job failed | OCR provider timeout |
payload | jsonb | Yes | Structured job payload metadata | {"file_id":"file_001"} |
queued_at | timestamptz | Yes | When job was queued | 2026-04-11 14:10:00+00 |
started_at | timestamptz | No | When job execution started | 2026-04-11 14:10:05+00 |
completed_at | timestamptz | No | When execution completed | 2026-04-11 14:11:30+00 |
created_at | timestamptz | Yes | Record creation timestamp | 2026-04-11 14:10:00+00 |
Relationships
- tenant_id → tenants.id when tenant-scoped
- resource_id references the resource named by
resource_type
How it is used
- Written by background workers and orchestration services
- Used by internal dashboards and support tools to inspect processing state
- Useful for upload progress, OCR debugging, and notification delivery investigation
- Can surface meaningful status to users for long-running tasks
Access and security
- Not all job details should be exposed to tenant users
- Error payloads may contain sensitive operational details
- Write access should be backend-only
Example scenarios
Scenario 1: OCR pipeline
Uploading a drawing PDF queues an ocr.process_drawing job whose progress is tracked here.
Scenario 2: PDF generation
A form submission triggers pdf.generate_submission, recorded as a job run.
Scenario 3: Failed notification job
A notification delivery job fails three times and the last error is stored for support review.
Notes and assumptions
- This table complements, not replaces, the underlying queue system tables
- Use indexes on
(job_type, status, queued_at desc) - Keep payloads structured but not excessively large