ZapBounce

ZapBounce and PostgreSQL

PostgreSQL is where a lot of application contact data lives, and verification here is a schema question before it is an API one.

The schema decision that matters: verdicts belong in their own table with a timestamp, not as a column on the users table.

A status column on the users table has no expiry

Adding email_status to the users table looks simplest and creates a value with no age. Six months later it is quoted in a query as current, and it describes a check nobody remembers running.

A separate table with a checked_at timestamp makes the age explicit, and a view can apply the staleness rule so every consumer inherits it rather than remembering it.

The second detail is the index. A partial index on rows needing a check keeps the candidate query fast as the table grows, which a full index on a low-cardinality status column does not.

How the data moves

  1. A verdicts table keyed on the address

    Address, result, reason, smtp_code, checked_at. One row per address, not per user.

  2. A partial index for the candidate query

    Indexing only rows where checked_at is old or null keeps the scan small however large the table gets.

  3. Batch through a scheduled job

    A worker selects candidates, submits a batch, and upserts results on the webhook.

  4. Expose a view with the staleness rule built in

    So a consumer querying deliverable addresses cannot accidentally read a two-year-old verdict.

Setting it up

  1. Create an email_verdicts table keyed on the address with a checked_at timestamp.
  2. Add a partial index covering rows where checked_at is null or older than your threshold.
  3. Write a worker that selects candidates, submits them as a batch and stores the batch id.
  4. Handle the completion webhook by upserting results into the verdicts table.
  5. Create a view joining users to verdicts with the staleness condition applied.
  6. Point application queries at the view rather than the table.

PostgreSQL: common questions

Column on users or a separate table?

Separate table. A column on users has no natural place for the check timestamp, and a verdict without one silently expires.

Why a partial index?

The candidate query only ever wants stale or unchecked rows. A partial index keeps that scan small as the table grows.

Where should the staleness rule live?

In a view, so every consumer inherits it. A convention in documentation is a rule nobody runs inside.

Reference

Check a PostgreSQL export today

100 free checks a month, no card. Unknown results and duplicates are never billed.

Get an API keySee pricing