Modeling email events in your database

"Did the customer get the email?" is one of the most common support questions in any SaaS company, and one of the hardest to answer if your database only stores a sent_at column. A small, well-designed event model turns that question into a single query.

Why a status column is not enough#

The first version of email tracking in most applications looks like this:

ALTER TABLE invoices ADD COLUMN email_sent_at timestamptz;

It answers whether your code attempted to send. It cannot answer whether the receiving server accepted the message, whether it bounced two hours later, whether the recipient complained, or whether a retry sent it twice. Each new question adds another column, and soon a dozen tables each have their own partial, inconsistent email state.

Email delivery is a sequence of events that arrive over time, often out of order, from systems you do not control. Model it that way.

Two tables: messages and events#

Messages#

One row per logical message your application sends:

CREATE TABLE email_messages (
  id               uuid PRIMARY KEY,
  provider_id      text UNIQUE,          -- ID returned by your email provider
  idempotency_key  text UNIQUE,
  template         text NOT NULL,
  template_version text,
  recipient        text NOT NULL,
  subject          text NOT NULL,
  related_type     text,                 -- e.g. 'invoice'
  related_id       uuid,                 -- e.g. the invoice ID
  status           text NOT NULL,        -- derived; see below
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

If a single send has several recipients, either create one row per recipient or add a recipients table. Per-recipient rows are usually simpler, because bounces, complaints and opens are per recipient.

Events#

One row per thing that happened to a message:

CREATE TABLE email_events (
  id           bigserial PRIMARY KEY,
  message_id   uuid NOT NULL REFERENCES email_messages(id),
  event_type   text NOT NULL,       -- queued, sent, delivered, bounced, complained, opened, clicked...
  occurred_at  timestamptz NOT NULL, -- when the provider says it happened
  received_at  timestamptz NOT NULL DEFAULT now(),
  provider_event_id text UNIQUE,     -- for deduplication
  detail       jsonb                 -- bounce reason, SMTP response, clicked URL...
);
CREATE INDEX ON email_events (message_id, occurred_at);

The events table is append-only. You never update or delete rows in normal operation. That gives you a complete history for support, debugging and audits.

Deriving status from events#

The status column on messages is a cache of the most meaningful event so far, recalculated whenever a new event arrives. Define a precedence order, because events arrive out of order:

When an event arrives, update the status only if the new event ranks higher than the current one:

UPDATE email_messages
SET status = $2, updated_at = now()
WHERE id = $1
  AND array_position(ARRAY['queued','sent','delivered','opened','clicked','bounced','complained'], status)
    < array_position(ARRAY['queued','sent','delivered','opened','clicked','bounced','complained'], $2);

A late "delivered" event can never overwrite "bounced," and a duplicate "opened" event changes nothing. Adjust the order to your product's needs; some teams prefer to keep engagement events out of status entirely and track them in separate counters.

Ingesting events safely#

Events come from your own code (queued, sent) and from provider webhooks (delivered, bounced, complained, opened, clicked). Webhooks need care:

• Verify signatures before trusting the payload.
• Deduplicate on the provider's event ID; webhook systems retry, so duplicates are normal.
• Respond quickly and process asynchronously, so slow database work does not cause provider timeouts and more retries.
• Store the raw payload in detail, so you can reprocess if your mapping logic changes.
• Handle unknown message IDs gracefully. An event can arrive before your own transaction that created the message commits. Retry such events later rather than dropping them.

-- Everything that happened to emails about one invoice
SELECT m.template, m.recipient, e.event_type, e.occurred_at, e.detail->>'reason' AS reason
FROM email_messages m
JOIN email_events e ON e.message_id = m.id
WHERE m.related_type = 'invoice' AND m.related_id = $1
ORDER BY e.occurred_at;