テーブル定義#

この機能の対象となるテーブルは以下の通りです。auth.usersテーブルのidを外部キーとして参照しています。

create table public.users (
  id uuid not null,
  username character varying(255) not null,
  display_name character varying(255) null,
  avatar_url text null,
  created_at timestamp with time zone not null default now(),
  constraint users_pkey primary key (id),
  constraint users_id_fkey foreign KEY (id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;

挑戦1：最初のDatabase Function#

SupabaseのドキュメントやAI（Gemini）の助けを借りて、最初のDatabase FunctionとTriggerを作成しました。auth.usersに新しいレコードがINSERTされた後、raw_user_meta_dataフィールドからDiscordの情報を抜き出してpublic.usersに挿入するという狙いです。

Functionのコード (バージョン1)

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO public.users (id, username, display_name, avatar_url)
  VALUES (
    new.id,
    new.raw_user_meta_data->>'full_name', -- Discordのユーザー名
    new.raw_user_meta_data->>'custom_claims'->>'global_name', -- Discordの表示名
    new.raw_user_meta_data->>'avatar_url' -- DiscordのアバターURL
  );
  RETURN new;
END;
$$;

-- Triggerの設定
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

これを設定してDiscord認証を試したところ、認証は成功するものの、public.usersへのデータ挿入は失敗。Supabaseのログには以下のエラーが出ていました。

failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: operator does not exist: text ->> unknown (SQLSTATE 42883)

operator does not exist: text ->> unknownというエラーメッセージから、raw_user_meta_dataがtext型であり、JSONを操作する->>演算子が直接使えないことが原因だと分かりました。

挑戦2：::jsonによる型キャスト#

PostgreSQLの公式ドキュメントを調べたところ、text型のデータをJSONとして扱うには、明示的にjson型へキャストする必要があることが判明しました。そこで、::jsonを追記して関数を修正しました。

Functionのコード (バージョン2)

-- ... (前半は同じ)
BEGIN
  INSERT INTO public.users (id, username, display_name, avatar_url)
  VALUES (
    new.id,
    new.raw_user_meta_data::json->>'full_name',
    new.raw_user_meta_data::json->>'custom_claims'->>'global_name', -- ここも修正
    new.raw_user_meta_data::json->>'avatar_url'
  );
  RETURN new;
END;
-- ... (後半は同じ)

しかし、これでも結果は変わらず、同じエラーが出続けてしまいました。

挑戦3：ネストされたJSONへのアクセス方法#

再度、PostgreSQLのドキュメントとraw_user_meta_dataの構造をじっくり見比べました。

raw_user_meta_dataの構造例

{
  "full_name": "atoboshisubaru",
  "avatar_url": "[https://cdn.discordapp.com/](https://cdn.discordapp.com/)...",
  "custom_claims": {
    "global_name": "あとぼしすばる"
  },
  ...
}

問題はdisplay_nameを取得しようとしているcustom_claimsの中のglobal_nameでした。->>演算子はトップレベルのキーにしか使えません。ネストされたJSONオブジェクト内の値にアクセスするには、別の演算子が必要だったのです。

そして、ついに正解にたどり着きました。ネストされたJSONの値を取得するには、#>>演算子を使い、パスを配列形式で指定します。

Functionのコード (完成版)

-- ... (前半は同じ)
BEGIN
  INSERT INTO public.users (id, username, display_name, avatar_url)
  VALUES (
    new.id,
    new.raw_user_meta_data::json->>'full_name',
    -- ネストされた値の取得方法を #>> に変更！
    new.raw_user_meta_data::json#>>'{custom_claims,global_name}', 
    new.raw_user_meta_data::json->>'avatar_url'
  );
  RETURN new;
END;
-- ... (後半は同じ)

この修正により、ついに認証後のデータ同期が成功しました！