# Job Queue Forensics — Phase 4

**Commit:** (pending Phase 4 commit)
**Date:** 2026-08-28
**Phase:** 4 — Real Durable Job Queue (Playbook Phase 8.2)

---

## 1. Existing Queue / Async Audit

**Pre-existing async mechanism:** `image_processing_jobs` table (migration `44_image_processing_jobs.sql`) — a simple status-column table with `pending/processing/completed/failed` enum. No generic worker, no retry, no dead-letter, no idempotency.

**Queue-like usage found in `UploadControllerHandler.php`:**
- Raw `$this->app->db()->insert('image_processing_jobs', [...])` calls
- No `JobQueueInterface` abstraction
- No worker to consume these rows

**Verdict:** The existing mechanism is domain-specific and not a general job queue. Phase 4 adds a canonical durable queue alongside it without modifying existing behavior.

---

## 2. Chosen Architecture

```
JobQueueInterface
    ├── DatabaseJobQueue    [default in local/testing when Redis is unavailable]
    └── RedisJobQueue       [production: Redis-backed, dead-letters to DB via DatabaseJobQueue]
    └── InMemoryJobQueue    [unit testing only — no infrastructure]

Worker                      [bin/worker.php CLI entry point]
    └── JobHandlerInterface [per-job-type business handlers]
```

---

## 3. Job Data Model

**Table: `jobs`**

| Column          | Type                                          | Purpose                                          |
| --------------- | --------------------------------------------- | ------------------------------------------------ |
| `id`            | BIGINT UNSIGNED AUTO_INCREMENT PK             | Unique job ID                                    |
| `queue`         | VARCHAR(64) DEFAULT 'default'                 | Logical queue partition                          |
| `type`          | VARCHAR(128)                                  | Job type key used to dispatch to handler         |
| `payload`       | LONGTEXT (JSON)                               | Serialised job parameters                        |
| `status`        | ENUM(pending, reserved, completed, failed)    | Job lifecycle state                              |
| `attempts`      | INT UNSIGNED                                  | Number of execution attempts performed so far    |
| `max_attempts`  | INT UNSIGNED                                  | Maximum attempts before dead-lettering           |
| `idempotency_key` | VARCHAR(128) NULL                           | Deduplication key per queue                      |
| `reserved_at`   | INT UNSIGNED NULL                             | Unix timestamp when reserved (lease start)       |
| `reserved_by`   | VARCHAR(128) NULL                             | Worker identity that holds lease                 |
| `available_at`  | INT UNSIGNED                                  | Earliest Unix timestamp job is eligible          |
| `created_at`    | INT UNSIGNED                                  | Creation time                                    |
| `updated_at`    | INT UNSIGNED                                  | Last modification time                           |

**Table: `dead_letter_jobs`**

| Column              | Type                 | Purpose                          |
| ------------------- | -------------------- | -------------------------------- |
| `id`                | BIGINT UNSIGNED PK   | Auto-increment                   |
| `original_job_id`   | VARCHAR(64) UNIQUE   | Prevents duplicate dead-letter   |
| `queue`             | VARCHAR(64)          | Queue that originated the job    |
| `type`              | VARCHAR(128)         | Job type                         |
| `payload`           | LONGTEXT             | Original payload for forensics   |
| `attempts`          | INT UNSIGNED         | Total attempts made              |
| `error_message`     | TEXT NULL            | Final failure reason             |
| `failed_at`         | INT UNSIGNED         | Unix timestamp of failure        |

---

## 4. Job Lifecycle

```
enqueue()
    └── job row created with status='pending', available_at=now+delay

reserve()
    └── BEGIN TRANSACTION
    └── SELECT ... FOR UPDATE (atomic lease acquisition)
    └── UPDATE status='reserved', reserved_at=now, reserved_by=worker_id, attempts++
    └── COMMIT
    └── returns Job object

Worker::processNext()
    ├── Handler success   → acknowledge() → status='completed'
    └── Handler exception → release()
                              ├── attempts < max_attempts → status='pending', available_at=now+backoff
                              └── attempts >= max_attempts → deadLetter()
                                                              ├── status='failed'
                                                              └── INSERT dead_letter_jobs (ON DUPLICATE KEY UPDATE)

Visibility timeout recovery:
    └── reserved jobs with reserved_at < now-visibility_timeout
        are re-queued by the next reserve() call (no external daemon needed)
```

---

## 5. Delivery Guarantee

**Guarantee:** **At-least-once delivery with idempotent handler pattern.**

- "Exactly-once" is NOT claimed.
- A worker crash after side effect but before `acknowledge()` will result in the job being re-reserved after lease expiration.
- The calling handler MUST be idempotent.

---

## 6. Idempotency Model

- **Queue-level:** `UNIQUE KEY uniq_queue_idempotency (queue, idempotency_key)` — prevents duplicate rows.
- **Race condition at enqueue:** PDO duplicate-key exception is caught; existing job ID is returned.
- **Redis-level:** Redis TTL key `{prefix}:idemp:{key}` guards duplicate enqueue before `LPUSH`.
- **Handler-level:** Business code must implement idempotent side effects (e.g., check-then-act patterns).

---

## 7. Retry / Backoff

- **Maximum attempts:** Configurable per job via `$maxAttempts` (default: 3)
- **Backoff formula:** `2^(attempts-1) * 10 seconds`
  - Attempt 1 failure → 10s delay
  - Attempt 2 failure → 20s delay
  - Attempt 3 failure → dead-letter (no backoff)
- **Non-retryable failures:** Currently all exceptions are retryable up to max_attempts. Future: handlers may throw a specific `NonRetryableException` for immediate dead-lettering.
- **Tight polling prevention:** Worker sleeps 500ms between idle polls (`usleep(500000)`).

---

## 8. Dead-Letter Behavior

- A job reaching `attempts >= max_attempts` during `release()` automatically calls `deadLetter()`.
- `deadLetter()` runs in a transaction:
  1. `UPDATE jobs SET status='failed'`
  2. `INSERT INTO dead_letter_jobs ... ON DUPLICATE KEY UPDATE` (prevents duplicate dead-letter rows on race)
- All forensic data is preserved: original job ID, type, payload, attempt count, error message, timestamp.

---

## 9. Concurrency Model

**Database Queue (`DatabaseJobQueue`):**
- `SELECT ... FOR UPDATE` wraps the reserve loop in a transaction
- Two independent PDO connections cannot both claim the same `pending` job simultaneously (one blocks, the other commits first; the second gets the committed state with no eligible row)
- Visibility timeout leakage recovery: expired `reserved` jobs (`reserved_at < now-timeout`) are re-queued atomically in the next reserve call

**`SKIP LOCKED` compatibility:** MariaDB 10.4.32 does NOT support `SKIP LOCKED`. Verified by runtime probe. Implementation uses `SELECT ... FOR UPDATE` with blocking semantics instead — correct for correctness guarantees.

**Redis Queue (`RedisJobQueue`):**
- Lua script `LUA_RESERVE` atomically:
  1. Moves expired leases from processing zset back to pending list
  2. `RPOP` + `ZADD` (claim job from pending + add to processing with lease expiry timestamp)
- Acknowledge: atomic `ZREM` + `HDEL` via `LUA_ACK`

---

## 10. Worker Shutdown

- `SIGTERM` / `SIGINT` handlers set `$shouldStop = true` (using `pcntl_async_signals`)
- Worker completes the current `processNext()` unit of work before exiting
- No transaction is abandoned mid-flight during shutdown

---

## 11. Crash Recovery

| Scenario                               | Outcome                                                          |
| -------------------------------------- | ---------------------------------------------------------------- |
| Worker crash before reserve            | Job remains pending — no impact                                  |
| Worker crash after reserve, before ACK | Lease expires → another worker recovers job on next poll         |
| Worker crash after side effect, no ACK | At-least-once: job re-executed after visibility timeout          |
| Worker crash after ACK                 | Job completed — no duplicate execution                           |
| Double dead-letter race                | ON DUPLICATE KEY UPDATE prevents duplicate dead_letter_jobs row  |

---

## 12. Configuration

All queue infrastructure is wired via `ContainerFactory::buildJobQueue()` with no hard-coded values:

| Config                 | Source           | Default   |
| ---------------------- | ---------------- | --------- |
| Queue driver           | APP_ENV          | auto      |
| Redis host             | REDIS_HOST       | (empty)   |
| Redis port             | REDIS_PORT       | 6379      |
| Retry limit            | per enqueue call | 3         |
| Backoff formula        | Worker class     | 2^n * 10s |
| Visibility timeout     | per reserve call | 60s       |
| Worker sleep           | bin/worker.php   | 500000µs  |

**No secrets committed.**

---

## 13. Tests

| Test class                              | Coverage                                               |
| --------------------------------------- | ------------------------------------------------------ |
| `WorkerTest`                            | Unit: handle→ack, retry→dead-letter, graceful stop     |
| `DatabaseJobQueueConcurrencyTest`       | Integration: 2 independent connections                 |
| - Scenario 1: Single owner             | Worker A claims; Worker B gets null                    |
| - Scenario 2: Different jobs           | Both workers progress without global lock              |
| - Scenario 3: Retry race               | Only one worker can re-claim a released job            |
| - Scenario 4: Duplicate enqueue        | Idempotency key returns same job ID                    |
| - Scenario 5: Dead-letter race         | `ON DUPLICATE KEY UPDATE` prevents duplication         |
| - Transaction rollback recovery        | Rolled-back claim leaves job claimable                 |
| `JobQueueArchitectureTest`             | Architecture: interface exists, DI binding correct     |
| - Production without Redis             | RuntimeException thrown                                |
| - Testing without Redis                | DatabaseJobQueue resolved                              |
| - Production with Redis                | RedisJobQueue resolved                                 |

---

## 14. Manual Smoke Test (Executed)

```text
1. Enqueue test job...
Enqueued job ID: 9
2. Running worker to process 1 job...
Worker executed job ID: 9, Type: smoke.test
Worker processed 1 job(s).
3. Testing failing job -> retry -> dead letter...
Enqueued failing job ID: 10
Attempt 1 reserved (attempts=1, max=2)
Attempt 2 reserved (attempts=2, max=2)
Dead-letter confirmed in DB: original_job_id=10, attempts=2, error=Second failure - should dead letter
```

**Result: PASS**

---

## 15. Known Limitations

- `SKIP LOCKED` not available in MariaDB 10.4.32 → `FOR UPDATE` with serialised reservation is used instead (correct but slightly lower throughput under high contention vs. `SKIP LOCKED`).
- `RedisJobQueue` dead-letter always attempts to also persist to `dead_letter_jobs` table via `DatabaseJobQueue`. If DB is unavailable, Redis dead-letter still succeeds but DB persistence may silently fail.
- Redis Lua `eval` parameter ordering: Predis `eval($script, numKeys, keys..., args...)` — documented in implementation comments.
- Worker `pcntl_*` functions require PHP compiled with `--enable-pcntl` (not available on Windows; signal handling degrades gracefully).
