Supabase JWT Custom Claims Authentication

Reduce API database queries by embedding user metadata (role, company_id, facility_id) into JWT tokens.

Performance Benefits

•Eliminates 2 DB queries per API request (40% reduction)
•Secure: JWT is signed and tamper-proof
•Scalable: Reduces database load as user base grows

Implementation Workflow

1. Create PostgreSQL Function

Create a migration file in supabase/migrations/:

sql

-- Template available in assets/migration_template.sql
CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  user_claims jsonb;
  current_facility uuid;
BEGIN
  -- Get primary facility for the user
  SELECT facility_id INTO current_facility
  FROM _user_facility
  WHERE user_id = (event->>'user_id')::uuid
    AND is_current = true
    AND is_primary = true
  LIMIT 1;

  -- If no primary facility, get any current facility
  IF current_facility IS NULL THEN
    SELECT facility_id INTO current_facility
    FROM _user_facility
    WHERE user_id = (event->>'user_id')::uuid
      AND is_current = true
    LIMIT 1;
  END IF;

  -- Build custom claims from m_users table
  SELECT jsonb_build_object(
    'role', role,
    'company_id', company_id,
    'current_facility_id', current_facility
  ) INTO user_claims
  FROM m_users
  WHERE id = (event->>'user_id')::uuid
    AND is_active = true
    AND deleted_at IS NULL;

  -- If user not found or inactive, return event unchanged
  IF user_claims IS NULL THEN
    RETURN event;
  END IF;

  -- Add custom claims to app_metadata
  RETURN jsonb_set(
    event,
    '{claims, app_metadata}',
    COALESCE(event->'claims'->'app_metadata', '{}'::jsonb) || user_claims
  );
END;
$$;

GRANT EXECUTE ON FUNCTION public.custom_access_token_hook TO authenticated;
GRANT EXECUTE ON FUNCTION public.custom_access_token_hook TO service_role;

Important: Customize the function to match your database schema (table names, column names).

2. Apply Migration

Use Supabase MCP to apply the migration:

typescript

await mcp__supabase__apply_migration({
  project_id: "your-project-id",
  name: "create_jwt_custom_claims_hook",
  query: "-- SQL from above"
});

3. Create Helper Module

Create lib/auth/jwt.ts (template available in assets/jwt-helper-template.ts):

typescript

import { createClient } from '@/utils/supabase/server';

export interface JWTMetadata {
  role: 'site_admin' | 'company_admin' | 'facility_admin' | 'staff';
  company_id: string;
  current_facility_id: string;
}

export async function getAuthenticatedUserMetadata(): Promise<JWTMetadata | null> {
  const supabase = await createClient();

  const {
    data: { user },
    error: authError,
  } = await supabase.auth.getUser();

  if (authError || !user) {
    return null;
  }

  const { role, company_id, current_facility_id } = user.app_metadata || {};

  if (!role || !company_id || !current_facility_id) {
    return null;
  }

  return {
    role,
    company_id,
    current_facility_id,
  };
}

export function hasPermission(
  metadata: JWTMetadata,
  allowedRoles: JWTMetadata['role'][]
): boolean {
  return allowedRoles.includes(metadata.role);
}

4. Use in API Routes

Replace database queries with JWT metadata retrieval:

Before (with DB queries):

typescript

// ❌ OLD: 2 DB queries
const { data: userData } = await supabase
  .from('m_users')
  .select('role, company_id')
  .eq('id', user.id)
  .single();

const { data: userFacility } = await supabase
  .from('_user_facility')
  .select('facility_id')
  .eq('user_id', user.id)
  .single();

After (with JWT):

typescript

// ✅ NEW: 0 DB queries
import { getAuthenticatedUserMetadata } from '@/lib/auth/jwt';

const metadata = await getAuthenticatedUserMetadata();
if (!metadata) {
  return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
}

const { role, company_id, current_facility_id } = metadata;

5. Configure Supabase Hook

Manual configuration required in Supabase Dashboard:

•Go to: https://supabase.com/dashboard/project/{project-id}/database/hooks
•Click "Enable Hooks" (if first time)
•Click "Create a new hook"
•
Configure:
- •Name: Custom Access Token Hook
- •Table: (leave blank)
- •Events: ✓ Custom Access Token (auth.jwt)
- •Type: SQL Function
- •Function: public.custom_access_token_hook
•Save

6. Verify Implementation

After configuration:

•Log in to the application
•
Check JWT token in browser DevTools:
- •Application > Session Storage
- •Decode JWT at https://jwt.io
•
Verify app_metadata contains:
- •role
- •company_id
- •current_facility_id

Troubleshooting

See references/troubleshooting.md for common issues and solutions.

重要: JWTトークンの取得方法

問題: session.user.app_metadataはJWTペイロードのapp_metadataを反映しません。

Supabase SDKのsession.user.app_metadataは、Custom Access Token Hookで追加したカスタムクレームを含みません。正しくデータを取得するには、JWTトークンを直接デコードする必要があります。

間違った実装（動作しない）:

typescript

const { data: { session } } = await supabase.auth.getSession();
const role = session.user.app_metadata?.role; // ❌ カスタムクレームが取得できない

正しい実装:

typescript

const { data: { session } } = await supabase.auth.getSession();

// JWTトークンを直接デコード
const accessToken = session.access_token;
const tokenParts = accessToken.split('.');
const payload = JSON.parse(Buffer.from(tokenParts[1], 'base64').toString());

// payloadからカスタムクレームを取得
const role = payload.app_metadata?.role; // ✅ 正しく取得できる
const company_id = payload.app_metadata?.company_id;
const current_facility_id = payload.app_metadata?.current_facility_id;

理由:

•Custom Access Token Hookは{claims, app_metadata}にカスタムクレームを追加
•JWTペイロードには正しく含まれているが、SDKのsession.userオブジェクトには反映されない
•JWTトークンを直接デコードすることで、Hookで追加したカスタムクレームにアクセス可能

正しいヘルパー実装: assets/correct-jwt-helper.tsを参照してください。

重要: middleware (Edge Runtime) での使用

問題: middleware は Edge Runtime で動作するため、Node.js の Buffer が使用できません。

間違った実装（Edge Runtime では動作しない）:

typescript

// ❌ Buffer は Edge Runtime で使用不可
const payload = JSON.parse(Buffer.from(tokenParts[1], 'base64').toString());

正しい実装（Edge Runtime 対応）:

typescript

// ✅ atob() を使用（Edge Runtime 対応）
function decodeJwtPayload(token: string): Record<string, unknown> | null {
    try {
        const parts = token.split('.');
        if (parts.length !== 3) return null;

        // Base64URL を Base64 に変換
        const base64 = parts[1].replace(/-/g, '+').replace(/_/g, '/');
        const jsonPayload = decodeURIComponent(
            atob(base64)
                .split('')
                .map(c => '%' + ('00' + c.charCodeAt(0).toString(16)).slice(-2))
                .join('')
        );

        return JSON.parse(jsonPayload);
    } catch {
        return null;
    }
}

// middleware での使用例
const { data: { session } } = await supabase.auth.getSession();
if (session?.access_token) {
    const payload = decodeJwtPayload(session.access_token);
    const role = payload?.app_metadata?.role as string | undefined;
}

重要なポイント:

•getUser() の user.app_metadata にはカスタムクレームが含まれない
•getSession() の session.access_token をデコードして取得する必要がある
•Edge Runtime では Buffer が使えないため atob() を使用
•JWT は Base64URL 形式なので - → +、_ → / への変換が必要

Security Notes

•JWT tokens are signed by Supabase and cannot be tampered with
•Always validate on the server side, never trust client-side data
•Hook runs on every authentication, so keep the function performant
•Custom claims are embedded at login time; changes to user data require re-login to update JWT

Performance Metrics

Metric	Before	After	Improvement
DB queries per API request	2	0	100%
Total queries (login + API)	5	3	40%
Response time	Baseline	-30ms avg	Faster