Email Delivery Database
Overview
PostgreSQL database tracking all email deliveries, bounces, opens, clicks, and unsubscribes for analytics and compliance.
Database Schema
email_sent- All sent emails with metadataemail_delivered- Confirmed deliveries from SendGridemail_bounced- Bounce events and reasonsemail_complaints- Spam complaints
email_opens- Email open events with timestampsemail_clicks- Link click trackingemail_unsubscribes- Unsubscribe requests
Technical Details
Specifications:
- Version: PostgreSQL 15.x
- Size: 50 GB
- Backup: Daily backups
- Replication: Single instance (non-critical data)
- Encryption: AES-256 at rest
Performance:
- Write Throughput: 1,000 writes/second
- Query Latency: P95 < 100ms
- Connections: Max 100
Data Retention
📊 Retention Policy
Email delivery data retained for 90 days, then archived to S3 for long-term analytics.
Access Patterns
- Email Service: Write delivery events
- Analytics Service: Read delivery metrics
- Compliance API: Query unsubscribe history
Monitoring
- Bounce Rate: Alert if > 5%
- Complaint Rate: Alert if > 0.1%
- Delivery Rate: Alert if < 95%