Designing a D1 email database schema for an inbox

You decided to store mail in Cloudflare D1 instead of a SaaS inbox API. Now you have a blank schema file and a pile of questions about threads, folders, and where the raw bytes go. Here is a reference design that holds up.

D1 is SQLite at the edge, which is a genuinely good fit for an email inbox: mail is mostly read-heavy, append-mostly, and you want it co-located with the Worker that serves it. The hard part is not the database, it is the data model. Email has been accreting headers since 1982, threading is a graph not a tree, and bodies range from a one-line reply to a 25 MB newsletter with inline images. Get the model wrong and every list query gets slow or every thread renders out of order.

This walks a normalized threads / messages / attachments design, the columns each table actually needs, and the indexes that keep the common queries cheap. It is the same shape Tinbox uses in production, so the snippets are close to real.

What tables does an email inbox need in D1?

An inbox needs three core tables: threads (the conversation grouping the UI lists), messages (one immutable row per sent or received email), and attachments (file metadata plus an R2 key). Everything else is an extension of those three.

The mental model that keeps the schema clean: a message is an immutable fact (this email arrived, with these headers, at this time). A thread is mutable derived state (this conversation has 4 messages, the newest at 9:02, and it is unread). You never edit a message after it lands; you update the thread's pointers as new messages arrive. Keeping the immutable facts separate from the mutable rollup is what makes the schema easy to reason about, easy to back up, and easy to reproject later.

Here is the table-level picture before we get into columns:

inboxes      one row per mailbox (hello@, support@, you@)
  └─ threads     one row per conversation, belongs to an inbox
       └─ messages   one row per email, belongs to a thread
            └─ attachments   metadata only; bytes live in R2

We will skip inboxes (it is just id, address, created_at) and focus on the three that carry the weight.

How do you model an email message in SQLite?

Model a message as one row holding its RFC 5322 headers, its addressing, its bodies, and pointers to the thread it belongs to and the raw blob in R2. The threading headers (message_id, in_reply_to, references) are the primitives every threading decision reads, so they are columns, not buried in a JSON blob.

CREATE TABLE messages (
  id            TEXT PRIMARY KEY,
  thread_id     TEXT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  inbox_id      TEXT NOT NULL REFERENCES inboxes(id) ON DELETE CASCADE,

  -- RFC 5322 threading primitives
  message_id    TEXT UNIQUE,        -- angle-bracketed, normalized; we own it for sent mail
  in_reply_to   TEXT,               -- single parent Message-ID
  "references"  TEXT,               -- JSON array of Message-IDs, oldest -> newest

  direction     TEXT NOT NULL,      -- 'inbound' | 'outbound'
  folder        TEXT NOT NULL DEFAULT 'inbox',  -- inbox|sent|drafts|archive|spam|trash
  status        TEXT NOT NULL DEFAULT 'unread', -- unread|read|replied|archived|spam

  -- addressing (as received/sent)
  "from"        TEXT NOT NULL,
  from_name     TEXT,
  "to"          TEXT NOT NULL DEFAULT '[]',  -- JSON array
  cc            TEXT,                          -- JSON array
  bcc           TEXT,                          -- JSON array
  reply_to      TEXT,

  subject       TEXT NOT NULL DEFAULT '',

  -- bodies: small text in D1, raw blob in R2
  body_text     TEXT,
  body_html     TEXT,
  body_preview  TEXT,               -- first ~200 chars for list rows + search
  raw_r2_key    TEXT,               -- R2 object key for the full .eml

  has_attachments  INTEGER NOT NULL DEFAULT 0,
  attachment_count INTEGER NOT NULL DEFAULT 0,

  date          INTEGER NOT NULL,   -- header Date, epoch millis
  received_at   INTEGER,            -- when we durably accepted it, epoch millis

  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);

A few decisions worth calling out, because they are the ones people get wrong:

• message_id is UNIQUE and is your natural key. The primary id can be whatever ID scheme you like (a KSUID, a nanoid), but the RFC Message-ID is the thing that correlates an outbound send with its later bounce notification and prevents you from ingesting the same email twice. Make it unique and you get idempotent ingestion for free.
• to / cc / bcc / references are JSON in TEXT columns. SQLite has no array type. Store the JSON, parse it in app code. D1 even lets you query inside it with json_extract if you need to, though for recipients you rarely do.
• direction, folder, and status are string enums, not separate tables. A lookup table for six folder values is ceremony with no payoff. Constrain them in the application layer (or a CHECK constraint) and move on.
• date vs received_at are different timestamps and you want both. date is the header the sender claims; received_at is when your deployment actually accepted the message. Senders lie or have skewed clocks, so list by date for display but trust received_at for "what is new since I last synced."

If you are wiring the inbound side that fills this table, the MIME parsing that produces these columns is its own rabbit hole. See parsing raw MIME without a heavy library for the encoding and multipart edge cases.

Should email bodies and attachments live in D1 or R2?

Keep the text and HTML bodies in D1; push the raw .eml and every attachment's bytes to R2 and store only the object key. This split is the single most consequential choice in the schema.

The reasoning is concrete. D1 rows have a practical size limit and, more importantly, D1 reads pull whole rows. If a 25 MB newsletter body sits inline in a row, then every SELECT id, subject, from FROM messages that scans near it pays for blob bytes it never returns. You want list queries to touch small rows. So:

• body_text and body_html: keep in D1. You render and full-text-search these constantly, and they are usually kilobytes.
• body_preview: a denormalized first ~200 characters. The inbox list shows it on every row, and computing it once at write time beats substringing a body column on every read.
• raw_r2_key: the full original .eml goes to R2. You need it for "show original," for re-parsing if your parser improves, and for exporting. It does not belong in the hot path.

Attachments get their own table, holding metadata only:

CREATE TABLE attachments (
  id           TEXT PRIMARY KEY,
  message_id   TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,

  r2_key       TEXT,               -- where the bytes live in R2 (content-addressed)
  filename     TEXT NOT NULL,
  mime_type    TEXT NOT NULL DEFAULT 'application/octet-stream',
  size         INTEGER NOT NULL DEFAULT 0,
  sha256       TEXT,               -- content hash: dedup + integrity
  content_id   TEXT,               -- cid: for inline images referenced in body_html
  disposition  TEXT NOT NULL DEFAULT 'attachment',  -- 'attachment' | 'inline'

  created_at   INTEGER NOT NULL
);

CREATE INDEX attachments_message_idx ON attachments(message_id);
CREATE INDEX attachments_sha256_idx  ON attachments(sha256);

The sha256 column earns its keep two ways. It dedups: a logo attached to a thousand newsletter sends is one R2 object, referenced a thousand times. And it is your integrity check on download. The content_id maps cid: references in body_html back to the inline image so you can rewrite them to signed R2 URLs at render time. For the full storage and signed-retrieval pattern, see storing email attachments in R2.

How do you thread emails correctly in a SQLite schema?

Thread on the RFC 5322 headers, never on the subject. When an inbound message arrives, look for an existing thread whose messages match any Message-ID in the new message's In-Reply-To or References; if you find one, attach to it; if not, open a new thread. Subject matching is a loose fallback for clients that strip headers, not your primary signal.

This is why the threads table exists as a separate, mutable rollup:

CREATE TABLE threads (
  id              TEXT PRIMARY KEY,
  inbox_id        TEXT NOT NULL REFERENCES inboxes(id) ON DELETE CASCADE,

  subject         TEXT NOT NULL,           -- normalized: Re:/Fwd: stripped
  participants    TEXT NOT NULL DEFAULT '[]', -- JSON array of distinct addresses

  message_count   INTEGER NOT NULL DEFAULT 0,
  last_message_at INTEGER,                  -- epoch millis of newest message
  last_message_id TEXT,                     -- row id of newest message

  has_unread      INTEGER NOT NULL DEFAULT 0, -- 0/1; D1 has no boolean
  needs_reply     INTEGER NOT NULL DEFAULT 0,

  status          TEXT NOT NULL DEFAULT 'open', -- open|archived|spam

  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL
);

The resolution logic, in plain terms:

// Find or create the thread for an inbound message.
async function resolveThread(db: D1Database, msg: ParsedMessage): Promise<string> {
  // 1. Collect every Message-ID this email references.
  const refIds = [msg.inReplyTo, ...(msg.references ?? [])].filter(Boolean);

  if (refIds.length > 0) {
    // 2. Is any referenced message already in a thread? Use IN, not a loop.
    const placeholders = refIds.map(() => "?").join(",");
    const hit = await db
      .prepare(
        `SELECT thread_id FROM messages
         WHERE message_id IN (${placeholders})
         LIMIT 1`,
      )
      .bind(...refIds)
      .first<{ thread_id: string }>();

    if (hit) return hit.thread_id;
  }

  // 3. Nothing matched: this is a new conversation.
  return createThread(db, msg);
}

Three things to internalize:

• Use IN, not a loop. It is tempting to query each referenced ID separately. Do not; one IN (?, ?, ?) is a single round trip. Looping turns a 3-reference email into 3 queries, and D1 charges per query.
• participants and subject on the thread are denormalized on purpose. The inbox list shows "Jane, Bob (4)" without joining to messages. You recompute these when a message is added, not on every read.
• The flags are integers. D1 (SQLite) has no boolean type. has_unread holds 0 or 1. Filtering is WHERE has_unread = 1.

Because the thread is just a rollup of immutable messages, you can always rebuild it. If your threading logic improves, reproject every thread from the messages table. That is only safe because you never destructively edited a message.

What indexes does a D1 email schema need?

Index the columns your hot queries filter and sort on, and build two composite indexes that match your two most frequent access patterns: listing a folder newest-first, and reading a thread's timeline. A unique index on message_id is mandatory; the rest follow your query shapes.

Start by naming the queries the inbox actually runs, then index to them:

-- Query A: "show me this inbox's inbox folder, newest first" (the main list view)
SELECT id, subject, from_name, body_preview, date
FROM messages
WHERE inbox_id = ?1 AND folder = 'inbox'
ORDER BY date DESC
LIMIT 50;

-- Query B: "show the full timeline of one thread, oldest first" (opening a thread)
SELECT * FROM messages
WHERE thread_id = ?1
ORDER BY date ASC;

-- Query C: "list this inbox's threads, most recently active first" (conversation list)
SELECT * FROM threads
WHERE inbox_id = ?1
ORDER BY last_message_at DESC
LIMIT 50;

The indexes that make each of those a single index range scan:

-- Mandatory: idempotent ingestion + bounce correlation.
CREATE UNIQUE INDEX messages_message_id_idx ON messages(message_id);

-- Query A: composite matching the WHERE + ORDER BY exactly.
CREATE INDEX messages_inbox_folder_date_idx ON messages(inbox_id, folder, date);

-- Query B: thread timeline.
CREATE INDEX messages_thread_date_idx ON messages(thread_id, date);

-- Query C: the conversation list.
CREATE INDEX threads_inbox_last_message_idx ON threads(inbox_id, last_message_at);

-- Useful singles for filters and lookups.
CREATE INDEX messages_in_reply_to_idx ON messages(in_reply_to);
CREATE INDEX messages_from_idx        ON messages("from");

The composite order matters. (inbox_id, folder, date) works for Query A because SQLite can seek to the inbox_id + folder prefix and then walk the date portion already sorted, so ORDER BY date DESC needs no separate sort step. The same three columns in a different order would not serve that query as cleanly. This is the general rule: put equality-filter columns first, the range/sort column last.

One thing indexes do not give you: full-text search. WHERE body_text LIKE '%invoice%' is a full table scan and will get slow. For real search, build an FTS5 virtual table over subject, body_text, and from, and keep it in sync on insert. That is its own topic, but the schema above is the substrate it sits on.

Putting it together and applying it to D1

With the three tables and their indexes defined, generating and applying the migration is a couple of commands. If you are using Drizzle (which is what the snippets above mirror), the columns map one-to-one to sqliteTable definitions and you generate SQL from them:

# Generate the migration SQL from your schema definitions
npx drizzle-kit generate

# Apply it to your D1 database
npx wrangler d1 execute my-inbox-db --file=./drizzle/0000_initial.sql --remote

Then sanity-check that the tables and indexes landed:

npx wrangler d1 execute my-inbox-db --remote \
  --command="SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;"

┌─────────────────────────────────────┐
│ name                                │
├─────────────────────────────────────┤
│ attachments_message_idx             │
│ messages_inbox_folder_date_idx      │
│ messages_message_id_idx             │
│ messages_thread_date_idx            │
│ threads_inbox_last_message_idx      │
└─────────────────────────────────────┘

A common failure here, worth knowing before you hit it: D1 has subtle SQL behavior differences from server SQLite, and correlated subqueries in particular have bitten people (a subquery that should see the outer row's value can silently get the wrong one). If you reach for a correlated subquery to compute, say, a per-thread unread count inline, test the result against known data before trusting it. The safer pattern is to maintain the rollup columns (message_count, has_unread) at write time, which the design above already does.

Skip the schema design: deploy the reference

The design above is deliberately close to what Tinbox runs, because it is the result of actually shipping an inbox on D1 and hitting these walls. If you would rather not hand-build and migrate every table, the whole schema (threads, messages, attachments, inboxes, plus the FTS5 search table, the send ledger, and the rest) ships as the reference. One command provisions the D1 database, the R2 bucket for blobs, and the Worker, with the migrations already applied:

npx tinbox init yourdomain.com

You get the exact tables and indexes described here, the blob/metadata split wired to R2, and the threading resolver, without writing the migration yourself. Use it as the canonical reference the next time you model mail in SQLite.

Related reading from here:

• parsing raw MIME email in JavaScript: how the columns in messages actually get filled from an inbound .eml.
• storing email attachments in R2: the other half of the blob/metadata split, with signed retrieval.
• the full Tinbox stack: the schema is the foundation; the inbox UI, sender, and AI agent all sit on top of it.

Deploy this yourself: npx tinbox init yourdomain.com. The schema, migrations, and docs are part of what the bootstrapper scaffolds.