Storing AECS email

AECS gives every email the same shape. That makes storage predictable: the same tables, keys and indexes work for every mailbox. This section turns AECS-1 Appendix C into ready-to-use schemas.

Status

These are reference schemas and examples that show the architecture, not a complete storage integration. The SQLite / D1 schema, its queries and the to-rows.mjs mapper are tested in this repo. The Cloudflare Worker has not been run in the Workers runtime, and the MySQL, MongoDB and DynamoDB examples have not been run against a server. The SDK storage helpers (d1Store, getThread and others, AECS-SDK-1 §3.7–3.8) are still planned. Migrations, retention, retries and access control are left to your application.

The layout in one picture

Tier	Where	Holds	Read by
Hot	aecs_messages	IDs, sender, subject, dates, forAI, counts, spec version	Every list, thread and LLM query
Warm	aecs_bodies	text, clean	Opening one message
Cold	Object storage (R2, S3, GCS)	raw.eml, body.html, attachment bytes	Re-parsing, rendering, download
Derived	aecs_addresses, aecs_references, aecs_threads, aecs_attachments	One row per participant, reference, thread, attachment	Participant search, replies, conversation list

Lists and LLM context read only the hot row: metadata plus a bounded forAI, a few KB. The full body is a single primary-key read away when someone opens the message, and the raw message sits in object storage for re-parsing.

Keys

• Every key starts with mailbox_id. The same message can land in many mailboxes.
• message_key = lowercase hex SHA-256 of messageId, and thread_key likewise for threadId. Fixed 64-character keys keep indexes small, are safe in object-storage paths, and fit Cloudflare Vectorize’s 64-byte ID limit. To find a message by Message-ID, hash it first.
• ts is the sort key: metadata.timestamp, or processedAt when the Date header is missing. The true metadata.date is kept in its own column.
• thread.position is never stored. It is computed when a thread is read.

The reads, and the index behind each

Read	Index
Last N messages of a thread, for an LLM	aecs_messages (mailbox_id, thread_key, ts, message_key)
Inbox page, newest first, keyset-paginated	aecs_messages (mailbox_id, ts DESC, message_key DESC)
Conversation list	aecs_threads (mailbox_id, last_ts DESC)
All mail with one person	aecs_addresses (mailbox_id, email, ts DESC)
Replies to a Message-ID	aecs_references (mailbox_id, ref_id)
One message by Message-ID	primary key (mailbox_id, message_key)

From NormalizedEmail to rows

to-rows.mjs maps a parsed email to exactly these rows and blobs. It has no dependencies and runs in Node 18+, Workers, Deno and browsers. It accepts any AECS-1-conformant object: only messageId and threadId are required, and omitted or null fields become empty arrays or null columns.

import { parse } from "@mvrx/aecs";
import { toRows } from "./to-rows.mjs";

const email = await parse(raw);
const rows = await toRows(email, { mailboxId: "user-123" });
// rows.message, rows.body, rows.addresses, rows.references, rows.attachments, rows.blobs

Writing rules

1. Blobs first: write rows.blobs and attachment bytes to object storage before the rows.
2. One transaction per message.
3. Idempotent: “insert if absent” on the message; bump the thread summary only if that insert created a row. Re-delivered mail changes nothing.
4. Keep spec_version: when AECS changes derived fields, find older rows and re-parse them from raw.eml.

Pick your database

Database	Guide	Example files
Cloudflare (D1 + R2 + Vectorize)	Cloudflare	sqlite.sql, sqlite-queries.sql, cloudflare-worker.ts
SQLite	SQLite	sqlite.sql, sqlite-queries.sql
PostgreSQL	PostgreSQL	postgresql.sql
MySQL	MySQL	mysql.sql
MongoDB	MongoDB	mongodb.js
DynamoDB	DynamoDB	dynamodb.md

The SQLite / D1 schema and every query in sqlite-queries.sql run in this repository’s test suite, including a check that no read scans the messages table.