# Graduated Transient Bounce Handling with DNC + Auto-Remove Cron

**Implemented:** 2026-03-19

## Overview

Previously, transient bounces immediately added permanent DNC — too aggressive. A single "mailbox full" bounce would permanently block the contact. Now: DNC all transient bounces immediately (to stop sending), but auto-remove DNC after a cooling period based on bounce count. At 5 bounces, DNC becomes permanent.

## Graduated Logic

| bounce_count | On bounce | next_retry_at | Cron action when next_retry_at passes |
|---|---|---|---|
| 1 | DNC (temporary) | +24 hours | Remove DNC, contact can receive again |
| 2 | DNC (temporary) | +72 hours | Remove DNC, contact can receive again |
| 3 | DNC (temporary), `is_suppressed=1` | +7 days | Remove DNC, reset is_suppressed |
| 4 | DNC (temporary), `is_suppressed=1` | +7 days | Remove DNC, reset is_suppressed |
| 5+ | DNC (permanent), `is_dnc=1` | NULL | Never touched by cron |

## Files Modified

| File | Change |
|------|--------|
| `docroot/scripts/process_sqs_bounces.php` | Modified upsertBounceTracking (returns bounce_count), added markBounceAsDnc helper, rewrote transient block with graduated comments, updated summary |
| `docroot/scripts/process_sqs_bounces_v2.php` | Identical changes as V1 |
| `docroot/scripts/process_bounce_retries.php` | **NEW** — Cron script to remove DNC for contacts whose cooling period has passed |

## Implementation Details

### upsertBounceTracking() — returns int bounce_count

- Uses MySQL `LAST_INSERT_ID()` trick: `bounce_count = LAST_INSERT_ID(bounce_count + 1)`
- Fresh inserts: `LAST_INSERT_ID(1)`
- After execute, `$pdo->lastInsertId()` returns the new bounce_count
- Computes `next_retry_at` and `is_suppressed` in SQL via CASE on `LAST_INSERT_ID()`:
  - `next_retry_at`: 1 -> +24h, 2 -> +72h, 3-4 -> +7d, 5+ -> NULL
  - `is_suppressed`: `IF(LAST_INSERT_ID() >= 3 AND LAST_INSERT_ID() < 5, 1, is_suppressed)`
- Removed `$isDnc` parameter — caller handles DNC marking separately

### markBounceAsDnc() helper

- `UPDATE email_bounce_tracking SET is_dnc = 1, next_retry_at = NULL WHERE email = :email`
- Called after successful Mautic DNC API call at count >= 5 (transient) or any permanent bounce

### Transient bounce DNC comments

- **Count 1-2:** `"SES Transient Bounce (temporary, count=N, retry in Xh) ..."`
- **Count 3-4:** `"SES Transient Bounce (suppressed, count=N, retry in 7d) ..."`
- **Count 5+:** `"SES Transient Bounce (permanent, count=N) ..."`

### Stats added

- `transient_suppressed` — count 3-4 bounces
- `transient_perm_dnc` — count 5+ bounces
- Summary format: `Transient bounces: N (suppressed: M, perm DNC: K)`

## process_bounce_retries.php — Cron Script

Run every hour:
```
/opt/cpanel/ea-php82/root/usr/bin/php /home/whizzmailer/public_html/swiss-belhotel-v2/docroot/scripts/process_bounce_retries.php
```

### What it does

1. CLI-only guard + file lock (`.process_bounce_retries.lock`)
2. Loads DB creds from `config/local.php`, Mautic API config from `sqs_config.php`
3. Queries: `SELECT email FROM email_bounce_tracking WHERE is_dnc = 0 AND next_retry_at IS NOT NULL AND next_retry_at <= NOW()`
4. For each eligible email:
   - Looks up contact via Mautic API
   - Removes DNC via `POST /api/contacts/{id}/dnc/email/remove`
   - Resets DB: `SET is_suppressed = 0, next_retry_at = NULL`
5. Logs to `var/logs/bounce_retries.log`
6. Summary: `Retries processed: N | DNC removed: M | Failed: K`

### Safety

- Only touches rows where `is_dnc = 0` — existing 47 rows with `is_dnc=1` (old permanent DNC) are never modified
- If Mautic contact not found, still clears retry fields to prevent infinite re-processing
- If DNC removal partially fails (multiple contacts per email), does NOT clear retry fields — will retry next hour

## Verification Commands

```bash
# Check bounce tracking state
SELECT bounce_count, is_dnc, is_suppressed, next_retry_at FROM email_bounce_tracking WHERE bounce_type = 'Transient' LIMIT 10;

# Count temporary DNC entries
SELECT COUNT(*) FROM lead_donotcontact WHERE comments LIKE '%temporary%';

# Count suppressed entries
SELECT COUNT(*) FROM lead_donotcontact WHERE comments LIKE '%suppressed%';

# Count permanent transient DNC
SELECT COUNT(*) FROM lead_donotcontact WHERE comments LIKE '%permanent, count%';

# Run retry cron manually
/opt/cpanel/ea-php82/root/usr/bin/php /home/whizzmailer/public_html/swiss-belhotel-v2/docroot/scripts/process_bounce_retries.php

# Check retry log
tail -50 /home/whizzmailer/public_html/swiss-belhotel-v2/docroot/var/logs/bounce_retries.log
```