sasa-maillist/EMAIL_BOUNCE_HANDLING_SETUP_COMPREHENSIVE.md

# Email-Based Bounce Handling Setup (Comprehensive)

This document explains the complete email-based bounce handling system implemented as an alternative to SNS webhooks for environments without SES production access.

## Overview

The system processes email bounces directly within the Postfix container by:
1. Rewriting return paths to direct bounces to a processing address
2. Processing bounce emails via Python script  
3. Updating member bounce statistics in MySQL database
4. Automatically disabling members with excessive bounces

## Configuration

### Environment Variables (in .env)

```bash
# Bounce handling feature flags
ENABLE_BOUNCE_HANDLING=true           # Master switch for bounce functionality
ENABLE_EMAIL_BOUNCE_PROCESSING=true  # Enable email-based processing
ENABLE_SNS_WEBHOOKS=false            # Disable SNS webhooks (optional)

# Database settings (required for bounce processing)
MYSQL_ROOT_PASSWORD=your_root_password
MYSQL_DATABASE=maillist
MYSQL_USER=maillist
MYSQL_PASSWORD=your_maillist_password
```

### Key Components

#### 1. Return Path Rewriting (`postfix/smtp_generic`)
Routes all bounces to the processing address:
```
@lists.sasalliance.org    bounces@lists.sasalliance.org
@sasalliance.org          bounces@lists.sasalliance.org
```

#### 2. Bounce Processing Script (`postfix/process-bounce.py`)
Python script that:
- Parses bounce emails for recipient addresses and bounce types
- Updates bounce counts in MySQL database
- Automatically disables members after 5 hard bounces
- Logs all bounce events

#### 3. Postfix Integration (`postfix/main.cf.template`)
```
# Return path rewriting for outbound mail
smtp_generic_maps = hash:/etc/postfix/smtp_generic

# Bounce processing
bounce_notice_recipient = bounces@lists.sasalliance.org
```

#### 4. Email Aliases (`postfix/entrypoint.sh`)
```
# Route bounces to processing script
bounces: "|/usr/local/bin/python3 /etc/postfix/process-bounce.py"
```

## How It Works

### Outbound Email Flow
1. User sends email to `community@lists.sasalliance.org`
2. Postfix expands to member list via MySQL
3. Email sent via SES with return path rewritten to `bounces@lists.sasalliance.org`
4. If delivery fails, bounce goes to bounce processing address

### Bounce Processing Flow
1. Bounce email arrives at `bounces@lists.sasalliance.org`
2. Postfix pipes email to `process-bounce.py` script
3. Script parses bounce for recipient and bounce type
4. Database updated with bounce information
5. Member automatically disabled if hard bounce threshold reached

### Bounce Types Detected
- **Hard Bounces**: Permanent failures (5.x.x SMTP codes)
  - Invalid email addresses
  - Domain doesn't exist
  - Mailbox doesn't exist
- **Soft Bounces**: Temporary failures (4.x.x SMTP codes)  
  - Mailbox full
  - Temporary server issues
  - Rate limiting

## Database Schema

### Bounce Logs Table
```sql
CREATE TABLE bounce_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    bounce_type ENUM('hard', 'soft', 'complaint') NOT NULL,
    bounce_reason TEXT,
    bounced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    raw_message TEXT,
    INDEX idx_email (email),
    INDEX idx_bounced_at (bounced_at)
);
```

### Members Table (bounce tracking fields)
```sql
ALTER TABLE members ADD COLUMN bounce_count INT DEFAULT 0;
ALTER TABLE members ADD COLUMN last_bounce_at TIMESTAMP NULL;
ALTER TABLE members ADD COLUMN active BOOLEAN DEFAULT true;
```

## Testing Bounce Handling

### 1. Test Return Path Configuration
```bash
# Check that return path rewriting is working
sudo docker-compose exec postfix postconf smtp_generic_maps
sudo docker-compose exec postfix postmap -q "test@lists.sasalliance.org" hash:/etc/postfix/smtp_generic
```

### 2. Simulate Bounce Email
```bash
# Send test bounce to processing script
echo "Subject: Delivery Status Notification (Failure)
From: MAILER-DAEMON@ses.amazonaws.com
To: bounces@lists.sasalliance.org

The following message could not be delivered:
   Recipient: test@example.com
   Reason: 550 5.1.1 User unknown" | docker-compose exec -T postfix mail -s "Test Bounce" bounces@lists.sasalliance.org
```

### 3. Check Bounce Processing
```bash
# View bounce logs
sudo docker-compose exec mysql mysql -u maillist -pmaillist maillist -e "SELECT * FROM bounce_logs ORDER BY bounced_at DESC LIMIT 5;"

# Check member bounce counts
sudo docker-compose exec mysql mysql -u maillist -pmaillist maillist -e "SELECT email, bounce_count, last_bounce_at, active FROM members WHERE bounce_count > 0;"
```

## Monitoring and Maintenance

### View Processing Logs
```bash
# Monitor bounce processing
sudo docker-compose logs -f postfix | grep -E "(bounce|process-bounce)"

# Check API bounce handling status
curl -H "Authorization: Bearer $API_TOKEN" http://localhost:8000/config
```

### Reset Member Bounce Count
```bash
# Via API
curl -X POST http://localhost:8000/members/{member_id}/reset-bounces \
  -H "Authorization: Bearer $API_TOKEN"

# Via Database
sudo docker-compose exec mysql mysql -u maillist -pmaillist maillist -e "UPDATE members SET bounce_count=0, active=true WHERE email='user@example.com';"
```

## Troubleshooting

### Common Issues

#### Bounces Not Being Processed
1. Check that `bounces` alias exists: `sudo docker-compose exec postfix cat /etc/aliases | grep bounces`
2. Verify Python script permissions: `sudo docker-compose exec postfix ls -la /etc/postfix/process-bounce.py`
3. Test script manually: `sudo docker-compose exec postfix python3 /etc/postfix/process-bounce.py --test`

#### Return Path Not Rewritten
1. Check smtp_generic configuration: `sudo docker-compose exec postfix postconf smtp_generic_maps`
2. Verify map file exists: `sudo docker-compose exec postfix ls -la /etc/postfix/smtp_generic*`
3. Test mapping: `sudo docker-compose exec postfix postmap -q "test@lists.sasalliance.org" hash:/etc/postfix/smtp_generic`

#### Database Connection Issues
1. Check PyMySQL installation: `sudo docker-compose exec postfix python3 -c "import pymysql; print('OK')"`
2. Test database connection: `sudo docker-compose exec postfix python3 -c "import pymysql; pymysql.connect(host='mysql', user='maillist', password='your_password', database='maillist')"`
3. Verify network connectivity: `sudo docker-compose exec postfix ping mysql`

### Log Analysis
```bash
# Postfix logs
sudo docker-compose logs postfix | grep -E "(bounce|MAILER-DAEMON|process-bounce)"

# MySQL connection logs
sudo docker-compose logs postfix | grep -E "(pymysql|mysql)"

# SES relay logs
sudo docker-compose logs postfix | grep -E "(relay|sent|deferred)"
```

## Security Considerations

- Bounce processing script runs with limited privileges
- Database credentials secured in environment variables
- Bounce emails contain sensitive delivery information - logs are rotated
- Return path rewriting prevents bounce loops
- Processing script validates email format before database updates

## Performance Notes

- Bounce processing is asynchronous (doesn't block email delivery)
- Database queries are indexed for bounce lookups
- Bounce logs should be periodically archived for large volumes
- SMTP generic maps are cached by Postfix for performance

## Advantages over SNS Webhooks

- **Works with SES Sandbox**: No production SES access required
- **No External Dependencies**: Doesn't require SNS, webhooks, or HTTPS domains
- **Self-Contained**: All processing happens within existing containers
- **Real-time Processing**: Bounces processed as emails arrive
- **Compatible**: Uses same database schema and UI as SNS method