📬 受信メールを宛先アドレスで振り分け、合わないものを隔離する PostgreSQL の関数

2026-11-16(月) 15:00

📬 受信メールを宛先アドレスで振り分け、合わないものを隔離する PostgreSQL の関数

SupabaseResend

Resend の email.received Webhook で届いた受信メールを、PostgreSQL の関数で宛先アドレスごとに利用者の拠点へ振り分け、合わないものを隔離する手順を解説します。正規化、照合、再送対策までを SQL で実装します。

前提と注意事項

この記事では以下を前提としています。

Supabase の PostgreSQL を使用しています。
受信メールは Resend の email.received Webhook で受け取ります。
複合機から転送された FAX のメールを、利用者の会社・拠点ごとに振り分ける例で解説します。
例で使うドメインは fax.example.com です。ご自身の環境のドメインに置き換えてください。
この記事では、受信用アドレスを発行する管理画面、Webhook を受け取る Route Handler、隔離したメールを割り当て直す操作は扱いません。

この記事のゴール

受信メールを宛先アドレスで利用者の拠点に振り分け、合わないものを「隔離」として記録し、振り分けられたメールには添付を取得するジョブを作成するところまでをゴールとします。

構成概要

Webhook の内容を PostgreSQL の関数にそのまま渡し、正規化、照合、隔離の判定をすべて SQL 側で行います。全体の流れは以下のとおりです。

Webhook から PostgreSQL の関数で宛先を判定し、受信または隔離として記録する流れ

メールアドレスを正規化する関数を作成する

メールアドレスは "表示名 <addr>" の形式で届くことがあり、大文字と小文字も混在します。そのため、照合の前にアドレスを正規化します。

normalize_email_address 関数は、<> の中があればそれを取り出し、前後の空白を取り除いて小文字にします。結果が空文字の場合は NULL を返します。

normalize_email_address()

create or replace function normalize_email_address(p_address text)
returns text
language sql
immutable
as $$
  select nullif(lower(trim(coalesce(substring(p_address from '<([^>]*)>'), p_address))), '')
$$;

次に、アドレスの配列がすべて正規化済みかを判定する email_addresses_normalized 関数を作成します。

email_addresses_normalized()

create or replace function email_addresses_normalized(p_addresses text[])
returns boolean
language sql
immutable
as $$
  select coalesce(bool_and(x = normalize_email_address(x)), true) from unnest(coalesce(p_addresses, '{}')) x
$$;

どちらの関数も immutable にしています。immutable は、同じ引数なら必ず同じ結果を返す関数であることを PostgreSQL に伝える指定です。次の章では、この2つの関数を check 制約で使います。check 制約は行を追加・更新したときにだけ評価されるので、制約で使う関数は、同じ引数に対していつも同じ結果を返す必要があります。PostgreSQL はこの点を確かめないため、関数を作る側が守ります。この2つの関数は文字列を操作するだけなので、この条件を満たします。

受信用アドレスのテーブルを作成する

次に、拠点ごとの受信用アドレスを管理する fax_inboxes テーブルを作成します。アドレスは運営者が管理画面で発行する想定です。

アドレスのローカル部（@ より前の部分）は、既定で {会社の slug}-{拠点名}-{ランダム4文字} の形式にします。例えば office-a7k2@fax.example.com のようなアドレスです。ローカル部は手で直すこともできます。ランダムな部分には、読み間違えやすい 0、o、1、l を除いた英数字を使います。

発行の際は、表示名と許可する From（1行に1件）も入力します。発行後にアドレスは変更できません。表示名と許可する From だけを編集できます。

以下のように、テーブルを作成します。

fax_inboxes

create table fax_inboxes (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id) on delete cascade,
  address text not null unique check (address = normalize_email_address(address)),
  label text not null,                       -- 複合機の置き場所など
  enabled boolean not null default true,
  allowed_senders text[] not null default '{}' check (email_addresses_normalized(allowed_senders)),
  created_at timestamptz not null default now()
);

tenant_id は利用者の会社・拠点を表します。tenants テーブルはすでに存在している前提です。check 制約では、前の章で作成した2つの関数を使い、アドレスが正規化済みであることを確かめます。

宛先で振り分ける規則を実装する

振り分けの規則は以下のとおりです。

Webhook の to と received_for の両方を正規化し、fax_inboxes.address と完全一致で照合します。
有効な受信アドレスが1件に定まるときだけ、その拠点に配ります。
無効なアドレスは件数に数えません。アドレスを切り替えている間に新旧の両方へ送られても、メールを落とさないためです。

照合と隔離理由の判定

判定の部分は、以下のようになります。関数の中の一部の抜き出しです。

関数の中の判定の部分

select array_agg(distinct a) into v_addresses
  from (select normalize_email_address(x) as a
          from unnest(coalesce(p_to, '{}') || coalesce(p_received_for, '{}')) x) s
 where a is not null;
v_from := normalize_email_address(p_from);
 
select count(*), count(*) filter (where i.enabled) into v_any_count, v_enabled_count
  from fax_inboxes i where i.address = any(coalesce(v_addresses, '{}'));
 
if v_any_count = 0 then
  v_reason := 'no_inbox';
elsif v_enabled_count > 1 then
  v_reason := 'multiple_inboxes';
elsif v_enabled_count = 0 then
  v_reason := 'inbox_disabled';
else
  select * into v_inbox from fax_inboxes i where i.address = any(v_addresses) and i.enabled;
  if v_from is null or not (v_from = any(v_inbox.allowed_senders)) then
    v_reason := 'sender_not_allowed';
  elsif jsonb_array_length(v_attachments) = 0 then
    v_reason := 'no_attachments';
  end if;
  if v_reason is null then
    v_tenant_id := v_inbox.tenant_id;
  end if;
end if;

隔離の理由は以下のとおりです。

no_inbox: どの受信アドレスにも当たらなかった場合です。
multiple_inboxes: 有効なアドレスが2件以上に当たった場合です。
inbox_disabled: 当たったアドレスがすべて無効だった場合です。
sender_not_allowed: From が allowed_senders に含まれない場合です。allowed_senders が空の場合は、すべて隔離されます。
no_attachments: 添付がない場合です。

隔離した行の扱い

隔離した行は、テナント ID を NULL のままにします。これにより、利用者の画面からは見えなくなります。一方で、どのアドレスに当たったかは運営者の確認用に残します。運営者は内容を確認したうえで、割り当て直すことができます。

Webhook の再送を一意制約で吸収する

Webhook は、再送されたり、同じものが同時に到着したりすることがあります。そこで、Resend の email_id に一意制約を付け、同じ email_id が来たときは新しい行を作らずに既存の行を返します。このとき、戻り値の created は false になります。

受信の記録と添付取得ジョブを1トランザクションで作成する

まず、すでに記録済みの email_id かどうかを確認します。記録済みであれば、既存の行を返して終了します。

既存の行の確認

select * into v_existing from fax_received_emails e
 where e.provider = 'resend' and e.provider_email_id = p_provider_email_id;
if found then
  return query select v_existing.id, v_existing.status, v_existing.quarantine_reason, v_existing.tenant_id, false;
  return;
end if;

記録済みでなければ、受信の行を登録します。同時に同じ Webhook が2つ到着した場合は、on conflict do nothing により、負けた方は行が登録されません。その場合は既存の行を読み直して返します。

受信の記録と添付取得ジョブの作成

insert into fax_received_emails (provider, provider_email_id, inbox_id, tenant_id, from_address, status, quarantine_reason, attachments)
values ('resend', p_provider_email_id, v_inbox_id, v_tenant_id, v_from,
        case when v_reason is null then 'received' else 'quarantined' end, v_reason, v_attachments)
on conflict (provider, provider_email_id) do nothing
returning id into v_id;
 
if v_id is null then
  -- 同時に来た同じ Webhook に負けた
  select * into v_existing from fax_received_emails e
   where e.provider = 'resend' and e.provider_email_id = p_provider_email_id;
  return query select v_existing.id, v_existing.status, v_existing.quarantine_reason, v_existing.tenant_id, false;
  return;
end if;
 
if v_reason is null then
  insert into document_jobs (kind, fax_received_email_id) values ('fetch_email', v_id);
end if;

隔離でない場合だけ、document_jobs に fetch_email のジョブを作成します。受信の記録とジョブの作成は、関数の中で1つのトランザクションとして実行されます。

関数は security definer にし、service role だけが呼べるようにしています。Webhook を受け取る Route Handler から呼び出す想定です。

fax_received_emails テーブルと document_jobs テーブルの定義は、この記事では扱いません。

トラブルシューティング

添付のメタが大きすぎる

添付のメタ（attachments）は、Webhook のペイロードをそのまま保存しません。使う5項目（id、filename、content_type、content_disposition、content_id）だけを、それぞれ255文字までに切って jsonb に入れます。

それでも 64 KiB を超える場合は、check 制約でエラーにして再送させるのではなく、理由を attachments_too_large として隔離します。エラーにすると Webhook の再送が繰り返されてしまうためです。

隔離したメールの添付が取得できなくなる

隔離したメールの添付は、取得しに行きません。保管も課金もしないためです。そのため、Resend 側の保持期間が切れると、添付を取り戻せなくなります。運営者の確認は早めに行ってください。

宛先や送信元の問題で隔離したメールは、割り当て直した後に添付を取得できるよう、添付のメタを残しておきます。

FAX の送信元の電話番号が取得できない

FAX の送信元の電話番号は、メールの From とは別のものです。From には複合機のアドレスが入ります。番号は件名や本文に出る機種がありますが、取得できる形が分かるまでは NULL にしています。

まとめ

この記事では、受信メールを宛先アドレスで利用者の拠点に振り分け、合わないものを隔離する PostgreSQL の関数の実装を解説しました。ポイントを整理すると以下のとおりです。

アドレスは normalize_email_address で正規化し、check 制約で正規化済みであることを保証すること
有効な受信アドレスが1件に定まるときだけ配り、無効なアドレスは数えないこと
合わないものはテナント ID を NULL にして隔離し、理由を記録すること
email_id の一意制約と on conflict do nothing で、Webhook の再送や同時到着を吸収すること

受信メールを宛先ごとに安全に振り分けたい方にとって参考になれば幸いです。

2026-11-16(月) 15:00