# Email queue (DB-based)

Emails are queued in the database and sent asynchronously by a cron-driven PHP CLI worker, with retries on failure.

## Setup

### 1. Create the queue table and locking columns

Run the migrations (once per environment). The queue table is in `incremental.sql` (`tbl_email_queue`). Then add locking so overlapping cron runs do not process the same rows:

```bash
mysql -u USER -p DATABASE < incremental_email_queue_locking.sql
```

This adds `locked_at`, `locked_by` and allows status `processing`. It also seeds `email_queue_processing_timeout_minutes` in `tbl_platform_config` (default 15).

### 2. Payload columns (recommended — reduces DB volume)

Run once per environment:

```bash
mysql -u USER -p DATABASE < incremental_email_queue_payload.sql
```

This adds `template_key`, `payload_json`, `reference_type`, `reference_id`, `meta_json` to the queue and history tables, makes `subject`/`body` nullable on the queue, and seeds retention config keys.

If this migration is **not** applied yet, producers fall back to storing pre-rendered `subject` + `body` (legacy behaviour).

### 3. Config (in database)

Email queue settings are stored in `tbl_platform_config`.

| Key | Default | Description |
|-----|---------|-------------|
| `email_queue_max_attempts` | 3 | Max send attempts before marking a job as `dead_letter` |
| `email_queue_retry_backoff_minutes` | 5 | Base minutes to wait before retry (delay = this × attempt number) |
| `email_queue_throttle_seconds` | 5 | Seconds between each send |
| `email_queue_processing_timeout_minutes` | 15 | Stuck `processing` rows reclaimed to `pending` after this many minutes |
| `email_queue_retention_days` | 30 | Purge `sent` / `dead_letter` queue rows older than this |
| `email_history_retention_days` | 90 | Delete history rows older than this |
| `email_history_store_body` | 0 | `1` = store full HTML body in history; `0` = metadata + subject only (smaller DB) |

Change values via SQL (`UPDATE tbl_platform_config SET value = '...' WHERE \`key\` = '...'`) or your platform config API.

### 4. Cron

**Send worker** — every 1–5 minutes:

```cron
*/5 * * * * php /var/www/doi_gov_api/cli/email_queue_worker.php
```

**Purge worker** — daily (recommended):

```cron
0 2 * * * php /var/www/doi_gov_api/cli/email_queue_purge_worker.php
```

Use the same user and PHP binary as your app.

## Flow

```mermaid
flowchart LR
  Producer[API or CLI producer]
  Queue[(tbl_email_queue)]
  Worker[email_queue_worker.php]
  History[(tbl_email_history)]
  SMTP[SMTP sendEmailNow]

  Producer -->|template_key + payload_json OR legacy body| Queue
  Worker -->|claim pending| Queue
  Worker -->|render if payload| Worker
  Worker --> SMTP
  Worker -->|audit row| History
  Worker -->|DELETE sent row| Queue
  Purge[email_queue_purge_worker.php] -->|retention| Queue
  Purge --> History
```

### Enqueue modes

| Mode | When | Queue stores |
|------|------|--------------|
| **Payload (preferred)** | After `incremental_email_queue_payload.sql` | `template_key`, small `payload_json`, recipients, references |
| **Legacy** | Migration not applied or sync fallback | Full `subject` + `body` LONGTEXT |

### Render at send time

When `template_key` + `payload_json` are set, the worker renders the email immediately before SMTP:

| template_key | Renderer |
|--------------|----------|
| `evaluation_reminder` | `EvaluationReminderEmailService` (via `EmailQueueTemplateRegistry`) |
| `evaluation_completed` | Same |
| `calendar_event_reminder` | `CalendarReminderEmailService` |
| Other | `getEmailTemplateFromDb()` |

Template routing is centralized in `services/Emails/EmailQueueTemplateRegistry.php`. Core queue logic lives in `services/Emails/EmailQueueService.php`. `common/mailer.php::mailToQueue()` is a thin facade for backward compatibility.

Legacy rows with a non-empty `body` are sent as-is (no re-render).

### After successful send

In one DB transaction per message: insert into `tbl_email_history` (subject always; body only if `email_history_store_body = 1`), then **delete** the queue row.

Failed rows stay in the queue as `pending` (retry with backoff) or `dead_letter`. Render failures are retried like transient SMTP errors (not immediately dead_lettered).

## Queue producers (current)

| Source | template_key |
|--------|--------------|
| `EvaluationReminderEmailService` | `evaluation_reminder`, `evaluation_completed` |
| `cli/calendar_event_reminder_worker.php` | `calendar_event_reminder` |

Other emails still use synchronous `mailTo()` (stage submit, approvers, registration, etc.).

## Attachments

If an email is queued with attachments, file paths are stored in `attachment_path`. Files must exist when the worker runs.

## History columns

After payload migration, `tbl_email_history` includes:

- `sent_at`, `email_type`, `subject`, `send_to`, `address_cc`, `address_bcc`
- `template_key`, `payload_json`, `reference_type`, `reference_id`, `meta_json`
- `body` — optional (controlled by `email_history_store_body`)

## Common layout (header/footer)

The common email layout uses `email_templates/common_layout.html`. Header and footer are configurable from `tbl_email_templates` (`template_key = 'common_layout'`). Logo uses `ROOT_URL` URL (not inline base64) when available.

## Extending to all emails (future)

1. Add `USE_EMAIL_QUEUE` in `tbl_platform_config` and route `mailTo()` through `mailToQueue()` when enabled.
2. Migrate JSON file templates under `email_templates/` into `tbl_email_templates`.
3. Enqueue with `template_key` + payload from each producer.