Database Schema

PostgreSQL database managed via SQLx embedded migrations.

Tables

threads

Column	Type	Description
id	UUID	Primary key
subject	TEXT	Normalized subject line
created_at	TIMESTAMPTZ	First message timestamp
updated_at	TIMESTAMPTZ	Last message timestamp
deleted_at	TIMESTAMPTZ	Soft-delete timestamp (nullable)

messages

Column	Type	Description
message_id	TEXT	RFC 5322 Message-ID (primary key)
thread_id	UUID	FK → threads.id
inbox_id	UUID	FK → inboxes.id
direction	TEXT	inbound or outbound
from_address	TEXT	Sender email
from_name	TEXT	Sender display name (nullable)
subject	TEXT	Original subject
sent_at	TIMESTAMPTZ	Send timestamp
in_reply_to	TEXT	Parent Message-ID (nullable)
body_text	TEXT	Plain text body (nullable)
body_html	TEXT	HTML body (nullable)
raw_storage_key	TEXT	S3 key for raw MIME
ses_message_id	TEXT	SES-assigned ID for delivery tracking (nullable)
created_at	TIMESTAMPTZ	Record creation

message_recipients

Column	Type	Description
id	UUID	Primary key
message_id	TEXT	FK → messages.message_id
recipient_type	TEXT	to, cc, or bcc
address	TEXT	Recipient email
name	TEXT	Display name (nullable)

message_references

Column	Type	Description
id	UUID	Primary key
message_id	TEXT	FK → messages.message_id
referenced_id	TEXT	Referenced Message-ID
position	INTEGER	Order in References header

attachments

Column	Type	Description
id	UUID	Primary key
message_id	TEXT	FK → messages.message_id
filename	TEXT	Original filename
content_type	TEXT	MIME type
size_bytes	BIGINT	File size
storage_key	TEXT	S3 key in attachments bucket

delivery_events

Column	Type	Description
id	UUID	Primary key
message_id	TEXT	FK → messages.message_id
ses_message_id	TEXT	SES message ID (nullable)
status	TEXT	pending, sent, delivered, bounced, complained, failed
details	JSONB	Unstructured event metadata
created_at	TIMESTAMPTZ	Event timestamp

webhook_endpoints

Column	Type	Description
id	UUID	Primary key
url	TEXT	Endpoint URL
secret	TEXT	HMAC signing secret
events	TEXT[]	Subscribed event types
inbox_ids	UUID[]	Scoped inboxes (nullable, all if null)
status	TEXT	active, warning, disabled
failure_count	INTEGER	Consecutive failures
last_success_at	TIMESTAMPTZ	Last successful delivery (nullable)
last_failure_at	TIMESTAMPTZ	Last failed delivery (nullable)
created_at	TIMESTAMPTZ	Registration timestamp

webhook_deliveries

Column	Type	Description
id	UUID	Primary key
endpoint_id	UUID	FK → webhook_endpoints.id
event_type	TEXT	Event type string
payload	JSONB	Full event payload
status	TEXT	pending, delivered, failed
attempts	INTEGER	Delivery attempt count
next_attempt_at	TIMESTAMPTZ	Scheduled retry time
created_at	TIMESTAMPTZ	Event timestamp

suppressed_addresses

Column	Type	Description
address	TEXT	Suppressed email (primary key)
reason	TEXT	bounce or complaint
created_at	TIMESTAMPTZ	Suppression timestamp

domains

Column	Type	Description
id	UUID	Primary key
name	TEXT	Domain name
ses_identity_arn	TEXT	SES identity ARN (nullable)
dkim_tokens	TEXT[]	DKIM CNAME tokens
from_name	TEXT	Default sender name (nullable)
reply_to_address	TEXT	Default reply-to (nullable)
bounce_address	TEXT	Bounce address (nullable)
mx_verified	BOOLEAN	MX record status
spf_verified	BOOLEAN	SPF record status
dkim_verified	BOOLEAN	DKIM record status
dmarc_verified	BOOLEAN	DMARC record status
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Update timestamp
deleted_at	TIMESTAMPTZ	Soft-delete (nullable)

inboxes

Column	Type	Description
id	UUID	Primary key
domain_id	UUID	FK → domains.id
name	TEXT	Display name
local_part	TEXT	Address prefix
is_catchall	BOOLEAN	Catch-all flag
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Update timestamp
deleted_at	TIMESTAMPTZ	Soft-delete (nullable)

auth_keys

Column	Type	Description
id	UUID	Primary key
organization_id	TEXT	Owning organization
name	TEXT	Key display name
public_key_pem	TEXT	PEM-encoded public key
algorithm	TEXT	ES256, ES384, or RS256
created_at	TIMESTAMPTZ	Registration timestamp
revoked_at	TIMESTAMPTZ	Revocation timestamp (nullable)

Indexes

Key indexes for query performance:

• messages.thread_id — thread message listing
• messages.inbox_id — inbox message listing
• messages.ses_message_id — delivery event correlation
• message_references.referenced_id — threading lookups
• delivery_events.message_id — status queries
• delivery_events.ses_message_id — telemetry correlation
• suppressed_addresses.address — suppression checks
• webhook_deliveries.status, next_attempt_at — dispatch polling
• domains.name — domain lookups
• inboxes.domain_id, local_part — address routing
• auth_keys.organization_id, algorithm — key lookups