Craft ERP 認証設計 — ユーザー管理テーブルを分割しない理由

本記事の前提となる Craft ERP（生成AI × 仕様駆動開発による小売業向け基幹業務システムの開発プロジェクト）の概要・目的・技術選定方針は Craft ERP Overview にまとめています。

1. はじめに

Craft ERPでは、通常のログインID・パスワード認証に加え、Google認証やCloudflare Accessなどの外部認証への対応を検討します。本検討では、認証機能そのものだけでなく、マルチテナント環境におけるユーザー識別、データモデル、障害調査・運用保守時の可読性まで含めて設計方針を整理しました。

今回の議論で最も重要となった設計原則は、次のとおりです。

開発時の構造美より、運用時の可読性を優先する。テーブルを開いた人が、業務データの状態を初見で理解できる設計を目指す。

Craft ERPは、開発者だけがデータベースを見るシステムではありません。本番運用では、障害や不具合の調査、データ確認、保守作業などを、開発時の設計経緯を知らない担当者が行う可能性があります。また、今後は複数の開発者や生成AIが仕様・実装・調査に関与することも想定されます。

そのため、正規化や責務分離だけを目的としてテーブルを細分化するのではなく、業務上ひとまとまりとなる情報は、可能な限り1つのテーブルから状態を把握できる構造を採用します。

2. 本検討の前提

• マルチテナントシステムである
• 企業ごとにユーザーを管理する
• 同一人物が複数企業に所属する可能性がある
• 同一人物が同じGoogleアカウントを複数企業で利用する可能性がある
• 1つのCraft ERPユーザーは、原則として1つの認証方式を利用する
• 1ユーザーへGoogle、Cloudflare Accessなど複数の外部認証を同時に紐づける運用は想定しない
• ローカル認証と外部認証を併用するユーザーも原則として想定しない
• 外部認証ユーザーの固定パスワードはCraft ERPで管理しない
• 認証方式の違いを含め、m_userを確認するだけでユーザー状態を把握できる設計を優先する

認証方式は、当面次の3種類を想定します。

local
google
cloudflare_access

3. Google認証

3.1 Google認証の位置付け

Google認証では、Craft ERPがGoogleアカウントのパスワードを受け取ることはありません。ユーザーはGoogleの認証画面でGoogleアカウントへログインし、Googleが本人確認を行います。

ユーザー
  ↓
Googleログイン画面
  ↓
Googleが本人確認
  ↓
Google IDトークン
  ↓
Craft ERP

Craft ERPはGoogleが発行したIDトークンを検証し、その認証結果を利用してCraft ERP上のユーザーを特定します。Google公式ドキュメントでは、Google Identity ServicesやOpenID Connectを利用してIDトークンを受け取り、サーバー側でトークンを検証する方式が説明されています。

3.2 Google IDトークン

Google IDトークンはJWT形式であり、認証されたユーザーに関するクレームを含みます。

{
  "iss": "https://accounts.google.com",
  "aud": "Craft ERPのGoogle Client ID",
  "sub": "109876543210987654321",
  "email": "user@example.com",
  "email_verified": true,
  "name": "山田太郎",
  "exp": 1780000000
}

3.3 ユーザー識別には sub を使用する

Google認証では、メールアドレスを恒久的なユーザー識別子として使用しません。Googleは、アカウント管理システムではIDトークンのsubを一意識別子として使用するよう明示しています。メールアドレスは変更される可能性があるためです。

provider_subject = Google IDトークンのsub
provider_email   = Google IDトークンのemail（表示・障害調査用）

3.4 Google側の設定

Google Cloud側では、Craft ERPをWebアプリケーションとして登録します。

1. Google Cloudプロジェクトを作成する
2. Google Auth PlatformのBrandingを設定する
3. Audienceを設定する
4. Web application型のOAuth Client IDを作成する
5. Authorized JavaScript originsを登録する
6. 必要な場合はAuthorized redirect URIsを登録する
7. Client IDをCraft ERPへ設定する
8. Client Secretを使用する構成では、サーバー側のSecretとして管理する

Authorized JavaScript origins

http://localhost:5173
https://erp.example.com

（バックエンドコールバック方式を採用する場合）
Authorized redirect URIs

http://localhost:8080/api/auth/google/callback
https://erp.example.com/api/auth/google/callback

3.5 Craft ERPでの実装

1. ユーザーが企業を指定する
2. Google認証を開始する
3. Googleで本人確認する
4. Craft ERPがGoogle IDトークンを受け取る
5. サーバー側でIDトークンを検証する
6. subを取得する
7. company_id + auth_provider + provider_subjectでm_userを検索する
8. Craft ERPのユーザーを特定する
9. Craft ERPのセッションを開始する

検証対象には少なくとも署名・iss・aud・expを含めます。利用方式に応じてnonceなども適切に検証します。

SELECT *
FROM m_user
WHERE company_id = :company_id
  AND auth_provider = 'google'
  AND provider_subject = :provider_subject
  AND status = 'active';

4. Cloudflare Access認証

4.1 Cloudflare Accessの位置付け

Cloudflare Accessは、Craft ERPの前段で利用者を認証し、許可されたリクエストだけをオリジンサーバーへ通します。

ユーザー
  ↓
Cloudflare Access
  ↓
本人確認・Accessポリシー判定
  ↓
Cloudflare Access JWT
  ↓
Craft ERP

Cloudflare Accessでは、Google、Microsoft Entra ID、OIDC、SAML、Cloudflare IdP、メールOTPなどを認証方法として利用できます。Craft ERPから見ると、上流の認証方法にかかわらず、Cloudflare Accessが発行するApplication Tokenを検証する構成となります。

4.2 Cf-Access-Jwt-Assertion

Cloudflare Accessは、認証済みリクエストをオリジンサーバーへ送る際、Application Tokenを次のHTTPヘッダーへ設定します。

Cf-Access-Jwt-Assertion: eyJ...

ブラウザー経由ではCF_Authorization Cookieも利用されます。Craft ERPのFastAPIは、Cf-Access-Jwt-AssertionからJWTを取得します。

4.3 Cloudflare Access JWT

{
  "iss": "https://example.cloudflareaccess.com",
  "aud": ["Craft ERP Application Audience"],
  "sub": "7335d417-61da-459d-899c-0a01c76a2f94",
  "email": "user@example.com",
  "type": "app",
  "exp": 1780000000
}

Craft ERPでは、検証済みJWTからprovider_subject（sub）とprovider_email（email）を取得します。Cloudflare AccessのJWT全体をユーザーマスタへ保存する必要はありません。JWTはセッショントークンであり、署名、有効期限、Audienceなどの情報を含みます。

4.4 Cloudflare Access JWTの検証

HTTPヘッダー名だけを信用してはなりません。外部から同じ名前のヘッダーを送信される可能性があるため、JWTそのものを検証します。主な検証対象は署名・iss・aud・expです。

Cloudflare Accessの公開鍵を使用してJWTの署名を検証します。公開鍵はCloudflareの証明書エンドポイントから取得できます。

https://<team-name>.cloudflareaccess.com/cdn-cgi/access/certs

Cloudflareは、JWTヘッダーの存在確認だけでは不十分であり、JWTと署名を検証する必要があると説明しています。

4.5 Cloudflare AccessのメールOTP

Cloudflare Accessでは、外部IdPを利用せず、許可されたメールアドレスへOne-time PINを送信して認証することができます。

1. ユーザーがCraft ERPへアクセス
2. Cloudflare Accessのログイン画面を表示
3. メールアドレスを入力
4. CloudflareがAccessポリシーを確認
5. 許可済みメールアドレスへPINを送信
6. ユーザーがPINを入力
7. Cloudflare Accessが認証
8. Application Tokenを発行
9. Craft ERPへアクセス

Cloudflare公式仕様では、PINは次の性質を持ちます。

• 発行から10分で失効する
• 1回のみ使用できる
• 新しいPINを要求すると以前のPINは無効になる
• Accessポリシーで許可されたメールアドレスにのみ送信される

未許可ユーザーに対しても、画面上はコードを送信したように表示されます。これにより、許可済みメールアドレスの存在を推測されにくくしています。

メールOTPでは固定パスワードをCraft ERPへ登録しません（password_hash = NULL）。メールボックスへアクセスできることを本人確認手段とします。

4.6 Cloudflare側の設定

概念的な設定手順は次のとおりです。

【Identity Provider】
Zero Trust → Integrations → Identity providers
  Google / Microsoft Entra ID / One-time PIN / Generic OIDC / Generic SAML

【Access Application】
Zero Trust → Access controls → Applications
  対象ホスト名の例: erp.example.com

【Access Policy】
個別メールアドレス: user1@example.com, user2@example.com
メールドメイン:     @example.com

ただし、ドメイン単位の許可はCloudflare Accessを通過できる範囲を広げます。Craft ERP内部では、必ずm_userの存在、company_id、statusなどを確認します。

Cloudflare Access認証成功
        +
Craft ERPユーザーが存在
        +
対象企業に所属
        +
m_user.status = active
        ↓
Craft ERP利用許可

5. マルチテナント環境でのユーザー識別

5.1 GoogleやCloudflareは所属企業を判断しない

Google認証は「Google上の誰であるか」を確認します。Cloudflare Accessは「Cloudflare Access上で認証・許可された誰であるか」を確認します。どちらも、Craft ERPで企業Aとしてログインするのか、企業Bとしてログインするのかは判断しません。

同じ人物が企業Aと企業Bの両方に所属している場合、m_userには2レコード存在します。

同じGoogle subが複数レコードに存在しても問題ありません。Craft ERPでは、企業Aのユーザーと企業Bのユーザーは別の業務ユーザーだからです。

5.2 企業を先に確定する

ログイン時には、GoogleやCloudflareの認証結果だけでユーザーを検索しません。同一人物が複数企業へ所属している場合、複数件がヒットするためです。そのため、Craft ERPでは先にcompany_idを確定します。

企業コード A001
        ↓
company_id = 企業A

Google認証
        ↓
sub = 123456789...

company_id + auth_provider + provider_subject
        ↓
m_userを特定

SELECT *
FROM m_user
WHERE company_id = :company_id
  AND auth_provider = :auth_provider
  AND provider_subject = :provider_subject;

外部認証ユーザーの識別単位は、Craft ERPでは company_id + auth_provider + provider_subject の組み合わせとします。

5.3 provider_issuer を m_user に持たせない理由

OpenID Connectでは、subはIssuer内で一意かつ再割り当てされないSubject Identifierとして定義されており、一般論では iss + sub の組み合わせによる識別が重要です。

しかし、Craft ERPのm_userではauth_provider（google / cloudflare_access）を保持します。現時点では、認証方式ごとのIssuer・Audience・検証設定はシステム側の認証設定として管理するため、同じIssuer情報をユーザーごとに繰り返し保存する必要性は低いと判断し、m_user.provider_issuerは採用しません。

将来、企業ごとに任意のOIDCやSAML接続先を自由登録できる機能を実装する場合は、認証プロバイダー設定の別管理を再検討します。

6. 当初検討した分散型スキーマ

SSO対応の一般的な設計として、当初はユーザー本体と認証情報を分離する案を検討しました。

m_user
  ├── m_user_password
  └── m_user_identity
          └── s_auth_provider

7. 分散型スキーマのメリット

• 複数認証方式を1ユーザーへ紐づけられる — m_user_identityを1対多にすることで、ローカル + Google + Cloudflare Access など複数の外部Identityを保持できる
• 認証情報と業務ユーザーを責務分離できる — 業務上のユーザー／外部認証との対応／ローカル認証情報、と技術的な責務が明確になる
• 認証方式追加時の拡張性が高い — 新しいOIDC、SAML、Microsoft Entra IDなどの追加時にm_userの変更を抑えられる

8. 分散型スキーマを採用しなかった理由

Craft ERPでは、分散型スキーマの拡張性よりも、運用時の可読性を優先します。

8.1 1ユーザー1認証方式という要件

Craft ERPでは、原則として1ユーザーが複数の外部認証を同時利用する運用を想定しません。1対多を必要としないのであれば、認証情報を別テーブルに分割する積極的な理由は弱くなります。

8.2 障害調査時に複数テーブルの理解が必要になる

分散型の場合、ログイン障害を調査する担当者は次の順序でテーブルを確認する可能性があります。

m_userを見る
  ↓
認証情報が見つからない
  ↓
m_user_identityを確認
  ↓
auth_provider_idしか分からない
  ↓
s_auth_providerを確認
  ↓
Google認証だと判明

または m_user_passwordを確認

調査前にデータモデルの理解が必要になり、テーブル名を知っているだけではユーザーの認証状態を把握できません。

8.3 開発者と運用担当者は同じとは限らない

開発者は、なぜテーブルを分割したのかを理解しています。一方、運用担当者は設計経緯を知らない場合があります。障害時には、まずデータを見て現在の状態を把握する必要があります。テーブルが過度に分散していると、「どのテーブルを見るべきか調べる → テーブル間の関連を理解する → JOIN条件を確認する → データ状態を確認する」という作業から始めることになります。Craft ERPでは、この「データ構造を理解するための調査」を減らします。

8.4 人間だけでなくAIの理解コストも考慮する

Craft ERPは生成AIを開発・レビュー・調査へ活用します。AIがユーザー認証の問題を調査する場合も、データが複数テーブルへ分散していれば、スキーマ全体と関連性をコンテキストへ与える必要があります。m_userに認証状態を集約すれば、1テーブルを見るだけで「ユーザー名・所属企業・認証方式・外部認証Subject・ローカルログインID・利用状態」の全体像を把握できます。これは人間とAIの双方にとって理解しやすい構造です。

9. Craft ERPのテーブル分割方針

Craft ERPでは、正規化を否定しません。伝票と明細のように、データ構造そのものが1対多の場合は分割します。

t_sales_order（1）→ t_sales_order_detail（N）
　1件の受注に複数商品が存在するため、分割はデータ構造上必要

m_user（1）→ 認証方式（1）
　Craft ERPの要件上は1対1

1対1の情報を技術的な責務だけを理由に分離しません。今回の議論から、次の設計原則を採用します。

正規化を目的としてテーブルを分割しない。データの多重化、1対多関係、ライフサイクルの明確な違い、またはセキュリティ上の分離理由がある場合にテーブルを分割する。業務上ひとまとまりの情報は、可能な限り1つのテーブルから状態を把握できる構造とする。

10. 最終的な m_user スキーマ

10.1 フィールド構成

10.2 設計上のポイント

• user_id — ULIDを使用しPostgreSQLではUUID型へ格納。GoogleやCloudflareのユーザー識別子とは別物。
• code / login_id — いずれも企業単位で一意（→ 第12章の部分ユニークインデックス）。
• partner_id — m_partnerには一般顧客も含まれるため、指定できるのは「ユーザー所属可能なパートナー」に限定。判定方法はm_partnerの分類設計で別途定義。
• 氏名項目 — 構造化した氏名（漢字・カナ）と表示名nameを分離。nameは外部認証側の表示名変更で自動更新しない。
• email と provider_email — 前者はCraft ERPの業務・連絡先情報、後者は外部認証が返したメール情報として用途を分ける。provider_emailは恒久的な識別キーに使用しない。
• provider_metadata — 外部認証の補助属性をJSONBで保存。ID Token、Access JWT、Access Token、一時PINそのものは保存しない。
• password_hash — 平文パスワードや復号可能なパスワードは保存しない。
• 論理削除 — 論理削除フラグは設けず、deleted_at IS NULLを未削除とする。通常検索ではWHERE deleted_at IS NULLを使用。
• 監査項目 — created_by等はユーザー操作ではm_user.user_id、システム処理ではNULL。

11. 認証方式別のデータ例

【ローカル認証】
auth_provider     = local
provider_subject  = NULL
provider_email    = NULL
login_id          = yamada
password_hash     = $argon2id$...

【Google認証】
auth_provider     = google
provider_subject  = 109876543210987654321
provider_email    = user@example.com
login_id          = NULL
password_hash     = NULL

【Cloudflare Access認証】
auth_provider     = cloudflare_access
provider_subject  = 7335d417-61da-459d-899c-0a01c76a2f94
provider_email    = sato@example.co.jp
login_id          = NULL
password_hash     = NULL

同じGoogleアカウントで複数企業に所属する場合は、company_idの異なる別レコードとして管理します（同じprovider_subjectが複数レコードに存在してよい）。

12. 推奨制約

12.1 一意性は「未削除ユーザー」だけに適用する

m_userは論理削除（deleted_at）を採用するため、一意性制約を通常のUNIQUE制約で定義すると、論理削除済みユーザーが一意キーを専有し続けます。

退職者を論理削除
        ↓
同じユーザーコードで再登録
        ↓
UNIQUE制約違反で登録できない

同様に、同じGoogleアカウントを一度削除して再登録するケースも失敗します。そのため、一意性が必要な項目にはPostgreSQLの部分ユニークインデックスを使用し、未削除（deleted_at IS NULL）のレコードだけを一意性の対象とします。

CREATE UNIQUE INDEX uq_m_user_company_code
    ON m_user (company_id, code)
    WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX uq_m_user_company_login_id
    ON m_user (company_id, login_id)
    WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX uq_m_user_external_identity
    ON m_user (company_id, auth_provider, provider_subject)
    WHERE deleted_at IS NULL;

同じGoogleアカウントが異なる企業へ所属することは許可します。同じ企業内で、同じ外部認証アカウントを複数の未削除ユーザーへ紐づけることは許可しません。論理削除済みレコードは一意性の対象外となるため、削除後の再登録が可能です。

12.2 認証方式によるCHECK制約

CHECK (
    (
        auth_provider = 'local'
        AND login_id IS NOT NULL
        AND password_hash IS NOT NULL
        AND provider_subject IS NULL
    )
    OR
    (
        auth_provider IN ('google', 'cloudflare_access')
        AND provider_subject IS NOT NULL
        AND login_id IS NULL
        AND password_hash IS NULL
    )
)

招待フローを採用する場合の注意：上記の制約は、ローカル認証ユーザーのpassword_hashが登録時点で必ず設定されることを前提とします。「管理者がユーザーを登録し、本人が初回ログイン時にパスワードを設定する」という招待フローを採用する場合、status = 'pending'の間はpassword_hashがNULLとなるため、この制約と矛盾します。その場合は次のようにstatusと組み合わせて緩和します。

AND (password_hash IS NOT NULL OR status = 'pending')

招待フローの採用有無とstatusのコード体系は、別途確定します。

12.3 論理削除と監査

論理削除フラグは設けず、deleted_atのNULL有無で判定します。矛盾データ（未削除なのに削除担当者がいる）はCHECK制約で防止します。

CHECK (
    deleted_at IS NOT NULL
    OR deleted_by IS NULL
)

created_by・updated_by・deleted_byはm_user.user_idへの外部キー（自己参照）とし、値あり＝ユーザー操作、NULL＝システム処理と定義します。m_user自体も論理削除とし、過去の操作主体となったユーザーを物理削除しません。

13. DDLイメージ

CREATE TABLE m_user (
    user_id UUID NOT NULL,
    company_id UUID NOT NULL,
    code VARCHAR(20) NOT NULL,

    partner_id UUID,
    authority_group_id UUID NOT NULL,

    name_last_jp VARCHAR(100),
    name_first_jp VARCHAR(100),
    name_last_kana VARCHAR(100),
    name_first_kana VARCHAR(100),
    name VARCHAR(200) NOT NULL,

    department VARCHAR(100),
    job_title VARCHAR(100),
    email VARCHAR(255),

    auth_provider VARCHAR(100) NOT NULL,
    provider_subject VARCHAR(255),
    provider_email VARCHAR(255),
    provider_metadata JSONB,

    login_id VARCHAR(100),
    password_hash VARCHAR(255),

    status VARCHAR(20) NOT NULL,
    metadata JSONB,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    CONSTRAINT pk_m_user PRIMARY KEY (user_id),

    CONSTRAINT fk_m_user_company
        FOREIGN KEY (company_id) REFERENCES m_company (company_id),
    CONSTRAINT fk_m_user_partner
        FOREIGN KEY (partner_id) REFERENCES m_partner (partner_id),
    CONSTRAINT fk_m_user_authority_group
        FOREIGN KEY (authority_group_id) REFERENCES m_authority_group (authority_group_id),
    CONSTRAINT fk_m_user_created_by
        FOREIGN KEY (created_by) REFERENCES m_user (user_id),
    CONSTRAINT fk_m_user_updated_by
        FOREIGN KEY (updated_by) REFERENCES m_user (user_id),
    CONSTRAINT fk_m_user_deleted_by
        FOREIGN KEY (deleted_by) REFERENCES m_user (user_id),

    CONSTRAINT ck_m_user_auth
        CHECK (
            (
                auth_provider = 'local'
                AND login_id IS NOT NULL
                AND password_hash IS NOT NULL
                AND provider_subject IS NULL
            )
            OR
            (
                auth_provider IN ('google', 'cloudflare_access')
                AND provider_subject IS NOT NULL
                AND login_id IS NULL
                AND password_hash IS NULL
            )
        ),

    CONSTRAINT ck_m_user_deleted
        CHECK (
            deleted_at IS NOT NULL
            OR deleted_by IS NULL
        )
);

-- 一意性は未削除レコードのみを対象とする（部分ユニークインデックス）
CREATE UNIQUE INDEX uq_m_user_company_code
    ON m_user (company_id, code)
    WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX uq_m_user_company_login_id
    ON m_user (company_id, login_id)
    WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX uq_m_user_external_identity
    ON m_user (company_id, auth_provider, provider_subject)
    WHERE deleted_at IS NULL;

これは現時点の設計イメージです。statusのコード体系、招待フロー（初回ログイン時パスワード設定）の採用有無、m_partnerでユーザー所属可能なパートナーを判定する方式、m_authority_groupのアクセス権構造、パスワードロック管理などは別途確定します。

14. 最終結論

Craft ERPでは、ユーザー本体、ローカル認証、外部認証を複数テーブルへ分割せず、m_userへ集約します。採用理由は、単純にテーブル数を減らすためではありません。最大の目的は、運用担当者、開発者、生成AIがm_userを確認した時点で、ユーザー管理の全体像を理解できるようにすることです。

m_userを見る
    ↓
どの企業のユーザーか分かる
    ↓
どの認証方式か分かる
    ↓
外部認証の紐づき状態が分かる
    ↓
ローカル認証の利用有無が分かる
    ↓
ユーザーが利用可能か分かる

Craft ERPでは、技術的な責務分離や正規化を機械的に優先しません。データ構造として1対多である場合、データ重複を防ぐ必要がある場合、ライフサイクルが明確に異なる場合、またはセキュリティ上の分離が必要な場合にはテーブルを分割します。それ以外では、業務上ひとまとまりの情報を可能な限り集約し、データを直接確認した人が初見で状態を理解できる設計を優先します。

開発時の構造美より、運用時の可読性を優先する。テーブルを開いた人が、業務データの状態を初見で理解できる設計を目指す。

この方針を、Craft ERPのテーブル設計における基本原則の一つとします。

参考資料

• Google OpenID Connect — developers.google.com
• Verify the Google ID token on your server side — developers.google.com
• Get your Google API client ID — developers.google.com
• Cloudflare Access: Validate JWTs — developers.cloudflare.com
• Cloudflare Access: Application token — developers.cloudflare.com
• Cloudflare Access: One-time PIN login — developers.cloudflare.com
• OpenID Connect Core 1.0 — openid.net
• OWASP Password Storage Cheat Sheet — cheatsheetseries.owasp.org