Email archival to R2 cold storage while keeping search working

Your inbox table started small and fast. Two years and a few hundred thousand messages later, every HTML body you have ever received is sitting in D1, most of them never reopened, all of them slowing down the queries that only wanted a subject line. Here is how to move the dead weight to R2 without losing search.

Email is append-only and mostly cold. You read a message once, maybe reply, and then it sits there forever. The bodies, especially the HTML ones, are by far the largest part of a message and the part you almost never look at again. Keeping all of that hot in your primary database is the email equivalent of renting climate-controlled storage for boxes you will never open.

The obvious move is to push old bodies to cheap object storage. Cold-body archival is one of several jobs R2 quietly does in a mail stack; the smart ways to use R2 in an email stack cover the rest. The thing that stops most people is search: if you delete the body from the database, does full-text search still find the message? It depends entirely on how your search index is built, and getting that part right is what makes safe archival possible. We will build the whole thing by hand first, hit the trap that breaks search, then show the design that avoids it.

How do I archive old emails to cheap storage while keeping them searchable?

Move the heavy fields (the full body_text and body_html) of old messages out of D1 into a Cloudflare R2 object, and leave behind the metadata you need to list and search the message: id, subject, sender, date, a short preview, and the R2 key. The one rule that makes this safe is that your full-text search index must hold its own copy of the searchable text, so the index keeps matching even after the body is gone from the main table.

That is the whole idea. The rest of this is mechanics and the one gotcha that will bite you if you skip the rule above.

Why is keeping every message body in D1 a problem?

D1 is SQLite, and SQLite reads the entire row off disk when you touch any column in it. A message row with a 40 KB HTML body is 40 KB that gets pulled into memory even when your query only selected subject and date for a list view. Multiply that across an inbox list query that scans a few hundred rows and you are reading megabytes to render a list that shows none of those bodies.

The cost is real on two axes:

• Query speed. Wide rows mean fewer rows per page, more pages read, slower scans. Your "show me the inbox" query gets slower as bodies pile up, even with the right indexes.
• Storage economics. D1 is billed in a way that rewards small hot rows. R2 is object storage priced for blobs with no egress fees. A cold HTML body that gets opened twice a year belongs in R2, not in a row that participates in every list scan.

The fix is to split the message: metadata and a short preview stay in D1 where they are scanned constantly, and the full body moves to R2 where it is read only on demand.

The hard way: archive by hand and watch search break

Let us build the naive version, because the way it breaks teaches the lesson. Assume a messages table roughly like this:

CREATE TABLE messages (
  id          TEXT PRIMARY KEY,
  inbox_id    TEXT NOT NULL,
  subject     TEXT NOT NULL DEFAULT '',
  from_addr   TEXT NOT NULL,
  body_text   TEXT,
  body_html   TEXT,
  body_preview TEXT,
  date        INTEGER NOT NULL,
  status      TEXT NOT NULL DEFAULT 'unread'
);

First, create and bind an R2 bucket:

npx wrangler r2 bucket create email-archive

{
  "r2_buckets": [
    {
      "binding": "ARCHIVE",
      "bucket_name": "email-archive",
    },
  ],
}

Add a column to remember where an archived body went:

ALTER TABLE messages ADD COLUMN body_r2_key TEXT;

Now a Worker route that archives one batch of cold messages. The selection policy here is "read, older than the cutoff, not already archived":

// archive.ts
interface Env {
  DB: D1Database;
  ARCHIVE: R2Bucket;
}

const SIX_MONTHS_MS = 1000 * 60 * 60 * 24 * 182;

export async function archiveColdBatch(env: Env, now = Date.now()) {
  const cutoff = now - SIX_MONTHS_MS;

  const { results } = await env.DB.prepare(
    `SELECT id, body_text, body_html
       FROM messages
      WHERE date < ?
        AND status = 'read'
        AND body_r2_key IS NULL
        AND (body_text IS NOT NULL OR body_html IS NOT NULL)
      LIMIT 200`,
  )
    .bind(cutoff)
    .all<{ id: string; body_text: string | null; body_html: string | null }>();

  for (const row of results) {
    const key = `bodies/${row.id}.json`;
    const payload = JSON.stringify({
      body_text: row.body_text,
      body_html: row.body_html,
    });

    // 1. Write the body to R2.
    await env.ARCHIVE.put(key, payload, {
      httpMetadata: { contentType: "application/json" },
    });

    // 2. Record the key and drop the body from D1.
    await env.DB.prepare(
      `UPDATE messages
          SET body_r2_key = ?, body_text = NULL, body_html = NULL
        WHERE id = ?`,
    )
      .bind(key, row.id)
      .run();
  }

  return results.length;
}

Run it, watch a few hundred bodies move to R2, and your inbox list query immediately gets lighter. Feels great. Then a user searches for a phrase that only appears in an archived body, and gets nothing.

# Before archiving:
$ curl 'https://app.example.com/api/search?q=quarterly+invoice'
{"total": 3, "rows": [...]}

# After archiving the same messages:
$ curl 'https://app.example.com/api/search?q=quarterly+invoice'
{"total": 0, "rows": []}

The messages still exist. Their metadata is right there in D1. But search lost them. Why?

Why did archiving the body break search?

Your search broke because the full-text index was reading the body back out of the messages table at query time, and you just nulled that body. This is the defining trait of an external-content FTS5 table: it does not store the indexed text itself, it stores only the index structure and reads the actual content from a base table whenever it needs it. The moment body_text becomes NULL, the index points at nothing.

There are two ways FTS5 can hold message text, and the difference decides whether archival is safe:

	External-content FTS5	Standalone FTS5
Where the text lives	Read from the base table on demand	Copied into the index itself
D1 storage	Smaller (no duplicated text)	Larger (text stored twice)
Survives the body being deleted	No, the index goes blank	Yes, the index keeps its own copy
Right choice for archival	No	Yes

External-content tables save space by not duplicating the text. That is exactly the wrong trade when your plan is to delete the text. For an archival design you want the index to own a copy of the searchable text so it is completely independent of whether the body still lives in the row.

The right way: a standalone FTS5 index that owns its text

Build the FTS5 table as a standalone (non-external-content) table that stores subject, sender, and body text directly, and keep it synced with triggers. This is the design Tinbox ships, and it is what makes body archival a non-event for search.

-- A standalone FTS5 index: it stores its OWN copy of the searchable text.
CREATE VIRTUAL TABLE messages_fts USING fts5(
  message_id UNINDEXED,
  inbox_id   UNINDEXED,
  subject,
  from_addr,
  from_name,
  body,
  tokenize = 'porter unicode61'
);

-- Keep it in sync. On insert, copy the text into the index.
CREATE TRIGGER messages_fts_ai AFTER INSERT ON messages BEGIN
  INSERT INTO messages_fts (message_id, inbox_id, subject, from_addr, from_name, body)
  VALUES (new.id, new.inbox_id, new.subject, new.from_addr,
          COALESCE(new.from_name, ''),
          COALESCE(new.body_text, new.body_preview, ''));
END;

CREATE TRIGGER messages_fts_ad AFTER DELETE ON messages BEGIN
  DELETE FROM messages_fts WHERE message_id = old.id;
END;

The body column inside messages_fts holds a real copy of the indexed text. When the message was first written, the insert trigger copied its body into the index. From that point on the index never needs the messages.body_text column again. Search runs entirely against messages_fts:

SELECT messages.id
  FROM messages_fts f
  JOIN messages ON messages.id = f.message_id
 WHERE messages_fts MATCH ?         -- the user's query, against the index's own copy
   AND messages.inbox_id = ?
 ORDER BY messages.date DESC
 LIMIT ? OFFSET ?;

The join back to messages is only for ordering by date and pulling list metadata, all of which stays in D1. The MATCH clause runs against the index's own stored copy of the body. So now go back and run the archive job from before: null out body_text and body_html on a thousand cold messages, and search keeps returning all of them, because the text it matches against was never in those columns at query time. It lives in the index.

One detail to get right: do not let your archive UPDATE re-fire a sync trigger that rebuilds the index from the now-null body. In the trigger design above, archival only touches body_text and body_html, and the update path should leave messages_fts alone (or re-insert from body_preview, which is still present). Keep the FTS row stable across archival and you are safe.

How do I rehydrate an archived message when someone opens it?

When a user opens an archived message, read the body from R2 using the stored body_r2_key and merge it into the row before returning it. List views and search never do this, so the R2 read happens only on the rare open.

async function getMessage(env: Env, id: string) {
  const row = await env.DB.prepare(
    `SELECT id, subject, from_addr, date, body_text, body_html,
            body_preview, body_r2_key
       FROM messages WHERE id = ?`,
  )
    .bind(id)
    .first<MessageRow>();

  if (!row) return null;

  // Hot message: body is still in D1.
  if (row.body_text != null || row.body_html != null) return row;

  // Archived message: pull the body from R2 cold storage.
  if (row.body_r2_key) {
    const obj = await env.ARCHIVE.get(row.body_r2_key);
    if (obj) {
      const body = await obj.json<{ body_text: string | null; body_html: string | null }>();
      return { ...row, body_text: body.body_text, body_html: body.body_html };
    }
  }

  // R2 object missing (should not happen): fall back to the preview.
  return { ...row, body_text: row.body_preview };
}

The list of an inbox, the search results, and the thread timeline all render from D1 columns alone: subject, sender, date, preview. The full body is fetched from R2 only when someone clicks into the message. That is the access pattern R2 is priced for, and the reason this split is cheap.

How do I run archival continuously without moving everything at once?

Run the batch job on a Cron Trigger so it archives a bounded number of cold messages each tick, never the whole table in one request. Workers have CPU and time limits, and one giant migration of every old body will blow past them and risk partial writes. A small, idempotent batch on a schedule drains the backlog over time and keeps up with new mail forever.

{
  "triggers": {
    "crons": ["0 4 * * *"], // 04:00 UTC daily
  },
}

export default {
  async scheduled(_event: ScheduledEvent, env: Env, ctx: ExecutionContext) {
    ctx.waitUntil(archiveColdBatch(env));
  },
};

Because the selection query filters on body_r2_key IS NULL, a retried or overlapping run never double-archives a message, and the LIMIT keeps each run inside Worker limits. If you have a large backlog, raise the cron frequency until it is drained, then drop back to daily. For more on running scheduled work like this, see scheduling recurring email jobs with Cron Triggers.

Or skip all of this

Everything above is the manual path, and it is worth understanding because the failure mode (search going blank after archival) is subtle and the fix (a standalone index that owns its text) is not the default most people would reach for. But you do not have to assemble it yourself.

Tinbox is a self-contained email stack that you deploy to your own Cloudflare account, and it already ships the two pieces that make archival safe. The raw .eml and attachment blobs are stored in R2 from the moment a message arrives, never inline in D1, and the search index is a standalone FTS5 table that holds its own copy of the searchable text, kept in sync by triggers exactly as shown above. That means the hard part, search surviving without the body in D1, is built in. Moving cold bodies to R2 follows the same pattern, runs on your account, and the data and the bill stay yours. There is no per-message or per-gigabyte SaaS tax sitting between you and your own archive.

If you want the schema this article borrows from, the reference is in the D1 email inbox schema design (and the things to know before you put email in D1 for the gotchas), the attachment side of R2 storage is covered in storing email attachments in R2, and the full backup-and-restore story is in backing up a Cloudflare email stack.

Frequently asked questions

Will moving bodies to R2 slow down opening a message? Slightly, by one R2 read on the rare open. R2 reads are fast and have no egress fee, and you only pay the latency when a user actually opens an archived message, not on list or search. The overwhelming majority of message views in a real inbox are recent (still hot in D1), so the average open stays fast.

What about the raw .eml and attachments? Those should already be in R2, not D1, from the moment the message is received. This article is specifically about archiving the parsed body_text and body_html that start their life in D1 for fast rendering. Attachments and the raw message are a separate, always-cold class of data that belongs in R2 from day one.

Can I archive to a different storage backend? The pattern is storage-agnostic. The R2 calls (put, get) are the only backend-specific part, so you could point them at any object store. R2 is the natural fit on Cloudflare because it sits next to your D1 and Worker with no egress cost, which matters for a store you read from on every archived-message open.

Deploy this yourself: npx tinbox init <domain>. The standalone FTS5 index and R2-backed storage come configured out of the box, so search keeps working as your archive grows. See the docs for the archival and storage settings.