Pagination and filtering for an email logs API

Sooner or later someone asks, "Did the password reset email go out to this customer?" If the only way to answer is to query the database by hand or open a provider dashboard, your support team will be asking engineers all week.

An email logs endpoint in your own product, backed by the events you already collect, fixes that. The hard part is not storing the rows. It is making a list that stays fast with millions of entries, that people can filter to the one message they need and that does not leak anything it should not. This guide covers the design choices that matter.

If you have not yet decided what an email event looks like, start with an email events data model and come back.

What the list should return#

One row per message, not per event. A row summarises the message's current state, and a detail view shows the events behind it.

A useful list row has:

• id, a stable identifier;
• to (or a count of recipients and the first one);
• from and subject;
• status, such as queued, sent, delivered, deferred, bounced, complained, rejected;
• created_at and last_event_at;
• template or category, so people can tell receipts from resets;
• your own reference, such as a user id or order id, if the sender supplied one.

Keep the list row small. Put bodies, headers, attachment names and raw provider responses in the detail view.

Pagination: use cursors#

Offset pagination (?page=500&per_page=50) is easy and breaks in two ways at scale.

• It gets slower as the offset grows, because the database must skip all earlier rows.
• It shows duplicates or skips rows when new messages arrive between page loads, which is constant on a logs screen.

Cursor pagination avoids both. The response includes an opaque cursor that encodes the position of the last row, and the client sends it back to get the next page.

A good design:

• Order by a stable, indexed key. Use created_at plus id as a tie-breaker, never created_at alone, because many messages share a timestamp.
• Return next_cursor (null on the last page) and optionally prev_cursor.
• Make the cursor opaque, such as a signed or encoded string, so clients do not build their own and you can change the internals.
• Fix the page size with a default (say 50) and a maximum (say 200).
• State clearly that cursors expire if you prune old data.

You can offer a total count as an option, but counting millions of rows on every request is expensive. Return an approximate count or none by default.

Filters people actually use#

Start from the questions support asks, not from every column.

Design rules:

• Combine filters with AND. Keep OR logic out of the first version.
• Require a time range, or default to a recent window such as the last 7 days. An unbounded scan of everything is the most common cause of slow log screens.
• Support exact matching first. Prefix and substring search on addresses need special indexes and are easy to get wrong.
• Normalise addresses by lower-casing the domain part and trimming whitespace before comparing.
• Reject unknown filters with a clear error instead of ignoring them silently. A typo should not return everything.