openmail/crates/openmail-store/migrations/0001_init.sql

-- OpenMail initial schema.
--
-- Design notes that are load-bearing:
--
--  * UUIDv7 primary keys, generated by the application, not the database.
--    They sort by creation time, so `ORDER BY id` is a free chronological
--    index and pagination cursors are opaque-but-ordered without a second
--    column.
--
--  * Raw MIME and attachments are NEVER stored here. Rows hold an object-store
--    key. Message metadata is queried constantly and is small; raw MIME is
--    written once, read rarely, and is arbitrarily large. Keeping blobs out is
--    what lets the metadata working set stay in RAM.
--
--  * `pods` exists from day one even though v0.1 is single-tenant. Retrofitting
--    tenancy into a live schema costs far more than an unused column, and it is
--    what keeps a hosted offering possible later without a migration.

CREATE TABLE pods (
    id          uuid PRIMARY KEY,
    name        text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE domains (
    id              uuid PRIMARY KEY,
    pod_id          uuid NOT NULL REFERENCES pods(id) ON DELETE CASCADE,
    name            text NOT NULL,
    -- Reference to the DKIM private key in the secret store. The key itself is
    -- never a column: a database backup must not be a signing-key compromise.
    dkim_privkey_ref text,
    dkim_selector   text,
    verified        boolean NOT NULL DEFAULT false,
    created_at      timestamptz NOT NULL DEFAULT now(),
    UNIQUE (name)
);

CREATE TABLE inboxes (
    id              uuid PRIMARY KEY,
    pod_id          uuid NOT NULL REFERENCES pods(id) ON DELETE CASCADE,
    -- Stored lowercase; the API lowercases on the way in. Two inboxes differing
    -- only by case is a support ticket, not a feature.
    address         text NOT NULL UNIQUE,
    display_name    text,
    metadata        jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE threads (
    id              uuid PRIMARY KEY,
    inbox_id        uuid NOT NULL REFERENCES inboxes(id) ON DELETE CASCADE,
    subject         text,
    -- Normalised subject used by the heuristic threading fallback. Stored so
    -- the match is an index lookup, not a scan with a function call.
    subject_norm    text,
    message_count   integer NOT NULL DEFAULT 0,
    labels          text[] NOT NULL DEFAULT '{}',
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

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

    -- The Message-ID header. NOT unique: real senders reuse and forge them.
    -- Indexed for threading lookups, never used as a key.
    message_id_hdr  text,
    in_reply_to     text,
    references_hdr  text[] NOT NULL DEFAULT '{}',

    from_addr       text NOT NULL,
    to_addrs        text[] NOT NULL DEFAULT '{}',
    cc_addrs        text[] NOT NULL DEFAULT '{}',
    subject         text,

    body_text       text,
    body_html       text,
    -- The reply with quoted history stripped. What an agent actually reads.
    extracted_text  text,
    preview         text,

    -- Inbound auth verdicts, recorded at receipt. Null means "not evaluated"
    -- (e.g. an ingested message), which is distinct from "failed".
    spf             text,
    dkim            text,
    dmarc           text,
    junk_score      real,

    labels          text[] NOT NULL DEFAULT '{}',
    headers         jsonb NOT NULL DEFAULT '{}'::jsonb,
    raw_object_key  text NOT NULL,
    size_bytes      bigint NOT NULL DEFAULT 0,

    search          tsvector,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE attachments (
    id              uuid PRIMARY KEY,
    message_id      uuid NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    filename        text,
    content_type    text,
    size_bytes      bigint NOT NULL DEFAULT 0,
    object_key      text NOT NULL,
    inline          boolean NOT NULL DEFAULT false,
    content_id      text
);

CREATE TABLE drafts (
    id              uuid PRIMARY KEY,
    inbox_id        uuid NOT NULL REFERENCES inboxes(id) ON DELETE CASCADE,
    thread_id       uuid REFERENCES threads(id) ON DELETE SET NULL,
    to_addrs        text[] NOT NULL DEFAULT '{}',
    cc_addrs        text[] NOT NULL DEFAULT '{}',
    bcc_addrs       text[] NOT NULL DEFAULT '{}',
    subject         text,
    body_text       text,
    body_html       text,
    send_at         timestamptz,
    -- Caller-supplied idempotency key. A retried send must not send twice.
    client_id       text,
    created_at      timestamptz NOT NULL DEFAULT now(),
    UNIQUE (inbox_id, client_id)
);

CREATE TABLE api_keys (
    id              uuid PRIMARY KEY,
    pod_id          uuid NOT NULL REFERENCES pods(id) ON DELETE CASCADE,
    -- Only the hash. A database dump must not be a set of live credentials.
    token_hash      bytea NOT NULL UNIQUE,
    name            text,
    scopes          text[] NOT NULL DEFAULT '{}',
    last_used_at    timestamptz,
    created_at      timestamptz NOT NULL DEFAULT now(),
    revoked_at      timestamptz
);

CREATE TABLE webhooks (
    id              uuid PRIMARY KEY,
    pod_id          uuid NOT NULL REFERENCES pods(id) ON DELETE CASCADE,
    url             text NOT NULL,
    event_types     text[] NOT NULL DEFAULT '{}',
    secret          bytea NOT NULL,
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE outbox (
    id              uuid PRIMARY KEY,
    message_id      uuid NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    status          text NOT NULL DEFAULT 'queued',
    attempts        integer NOT NULL DEFAULT 0,
    last_error      text,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE events (
    id              uuid PRIMARY KEY,
    inbox_id        uuid REFERENCES inboxes(id) ON DELETE CASCADE,
    type            text NOT NULL,
    payload         jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at      timestamptz NOT NULL DEFAULT now()
);

-- Indexes -------------------------------------------------------------------

CREATE INDEX messages_inbox_created  ON messages (inbox_id, created_at DESC);
CREATE INDEX messages_thread         ON messages (thread_id);
CREATE INDEX messages_msgid          ON messages (message_id_hdr) WHERE message_id_hdr IS NOT NULL;
CREATE INDEX messages_search         ON messages USING gin (search);
CREATE INDEX messages_labels         ON messages USING gin (labels);
CREATE INDEX threads_inbox_updated   ON threads (inbox_id, updated_at DESC);
-- The heuristic threading lookup: same inbox, same normalised subject, recent.
CREATE INDEX threads_subject_norm    ON threads (inbox_id, subject_norm, updated_at DESC)
    WHERE subject_norm IS NOT NULL;
-- The sender's hot path: due work only.
CREATE INDEX outbox_due              ON outbox (next_attempt_at) WHERE status = 'queued';
CREATE INDEX events_inbox_created    ON events (inbox_id, created_at DESC);