dev-database

name	dev-database
description	データベースアダプター（PostgreSQL/MySQL両対応）の開発・修正を行う際に使用。SqlExecutorパターン、マイグレーション、DB固有SQL実装時に役立つ。

データベースアダプター開発ガイド

ドキュメント

• libs/idp-server-database/README.md - データベース設計・マイグレーション

---

機能概要

データベースアダプターは、PostgreSQL/MySQL両方をサポートする永続化層。

• 両DB対応: 全機能でPostgreSQL/MySQL両方実装必須
• SqlExecutorパターン: DB固有SQLを抽象化
• Flyway: マイグレーション管理
• 読み書き分離: @Transaction / @Transaction(readOnly = true) アノテーション

---

モジュール構成

libs/
├── idp-server-core-adapter/                    # 永続化実装
│   └── .../adapters/datasource/
│       ├── audit/
│       │   ├── command/
│       │   │   ├── AuditLogSqlExecutor.java       # インターフェース
│       │   │   ├── AuditLogSqlExecutors.java      # ファクトリ
│       │   │   ├── PostgresqlExecutor.java        # PostgreSQL実装
│       │   │   └── MysqlExecutor.java             # MySQL実装
│       │   └── query/
│       │       └── (同様の構造)
│       ├── token/
│       ├── identity/
│       ├── authentication/
│       ├── ciba/
│       └── ...
│
├── idp-server-database/                        # マイグレーション
│   ├── postgresql/
│   │   ├── V0_9_0__init_lib.sql               # 初期スキーマ
│   │   └── V1_0_0__*.sql                      # バージョン別マイグレーション
│   └── mysql/
│       ├── V0_9_0__init_lib.mysql.sql         # ※ .mysql.sql 接尾辞
│       └── V0_9_21_1__*.mysql.sql
│
└── idp-server-platform/                        # 共通基盤
    └── .../datasource/
        ├── DatabaseType.java                   # POSTGRESQL, SPANNER, MYSQL
        ├── SqlExecutor.java                    # SQL実行ヘルパー
        └── TransactionManager.java             # トランザクション管理

---

SqlExecutor パターン

基本構造

AuditLogSqlExecutor.java:

// 1. インターフェース定義（Tenant第一引数）
public interface AuditLogSqlExecutor {
    void insert(Tenant tenant, AuditLog auditLog);
}

PostgresqlExecutor.java:

// 2. PostgreSQL実装
public class PostgresqlExecutor implements AuditLogSqlExecutor {
    @Override
    public void insert(Tenant tenant, AuditLog auditLog) {
        // SqlExecutorが内部でTransactionManager.getConnection()を使用
        SqlExecutor sqlExecutor = new SqlExecutor();
        String sqlTemplate = """
            INSERT INTO audit_log (
                id, type, description, tenant_id, client_id, user_id, ...
            ) VALUES (
                ?::uuid, ?, ?, ?::uuid, ...
            );
            """;
        List<Object> params = new ArrayList<>();
        params.add(auditLog.id().value());
        // ...
        sqlExecutor.execute(sqlTemplate, params);
    }
}

MysqlExecutor.java:

// 3. MySQL実装
public class MysqlExecutor implements AuditLogSqlExecutor {
    @Override
    public void insert(Tenant tenant, AuditLog auditLog) {
        SqlExecutor sqlExecutor = new SqlExecutor();
        String sqlTemplate = """
            INSERT INTO audit_log (
                id, type, description, tenant_id, client_id, user_id, ...
            ) VALUES (
                ?, ?, ?, ?, ...
            );
            """;
        // MySQL固有: UUIDキャストなし
        // ...
    }
}

AuditLogSqlExecutors.java:

// 4. ファクトリ（ディスパッチャー）
public class AuditLogSqlExecutors {
    Map<DatabaseType, AuditLogSqlExecutor> executors;

    public AuditLogSqlExecutors() {
        executors = new HashMap<>();
        executors.put(DatabaseType.POSTGRESQL, new PostgresqlExecutor());
        executors.put(DatabaseType.MYSQL, new MysqlExecutor());
    }

    public AuditLogSqlExecutor get(DatabaseType databaseType) {
        return executors.get(databaseType);
    }
}

---

読み書き分離

EntryServiceレイヤーで @Transaction アノテーションにより制御:

// 書き込み（Primary）
@Transaction
public class OAuthFlowEntryService { ... }

// 読み取り専用（Replica）
@Transaction(readOnly = true)
public class UserinfoEntryService { ... }

@Transaction(readOnly = true)
public class OidcMetaDataEntryService { ... }

---

PostgreSQL vs MySQL 差異

INSERT時の競合処理

操作	PostgreSQL	MySQL
UPSERT	ON CONFLICT (id) DO UPDATE SET ...	ON DUPLICATE KEY UPDATE ...
INSERT IGNORE	ON CONFLICT DO NOTHING	INSERT IGNORE INTO ...

UUID型

操作	PostgreSQL	MySQL
UUIDキャスト	?::uuid	? (文字列として保存)

JSON操作

操作	PostgreSQL	MySQL
JSON型	jsonb	JSON
JSON抽出	column->>'key'	JSON_UNQUOTE(JSON_EXTRACT(column, '$.key'))
JSON検索	column @> '{"key": "value"}'	JSON_CONTAINS(column, '"value"', '$.key')

日時操作

操作	PostgreSQL	MySQL
現在時刻	NOW()	NOW()
タイムゾーン	AT TIME ZONE 'UTC'	CONVERT_TZ(..., '+00:00', 'UTC')
間隔	INTERVAL '1 day'	INTERVAL 1 DAY

---

マイグレーション

ファイル命名規則

PostgreSQL: V{major}_{minor}_{patch}__{description}.sql
MySQL:      V{major}_{minor}_{patch}__{description}.mysql.sql

例:
- postgresql/V1_2_0__add_user_status_column.sql
- mysql/V1_2_0__add_user_status_column.mysql.sql

注意: MySQLは .mysql.sql 接尾辞が必須。

実行方法

# Docker経由（推奨）
docker compose up flyway-migrator

# 直接実行
./gradlew :libs:idp-server-database:flywayMigrate

両DB対応マイグレーション

PostgreSQL と MySQL で同じバージョン番号のファイルを作成:

libs/idp-server-database/
├── postgresql/
│   └── V1_2_0__add_user_status_column.sql
└── mysql/
    └── V1_2_0__add_user_status_column.mysql.sql

---

Command / Query 分離

構造パターン

datasource/{domain}/
├── command/                    # 書き込み操作
│   ├── {Domain}CommandSqlExecutor.java
│   ├── {Domain}CommandSqlExecutors.java
│   ├── PostgresqlExecutor.java
│   └── MysqlExecutor.java
└── query/                      # 読み取り操作
    ├── {Domain}QuerySqlExecutor.java
    ├── {Domain}QuerySqlExecutors.java
    ├── PostgresqlExecutor.java
    └── MysqlExecutor.java

---

DatabaseType

DatabaseType.java:

public enum DatabaseType {
    POSTGRESQL,
    SPANNER,     // Google Cloud Spanner
    MYSQL;
}

---

コマンド

# ビルド
./gradlew :libs:idp-server-core-adapter:compileJava
./gradlew :libs:idp-server-database:build

# マイグレーション確認
./gradlew :libs:idp-server-database:flywayInfo

# テスト
./gradlew :libs:idp-server-core-adapter:test

---

チェックリスト

新規DataSource実装時:

• SqlExecutor インターフェース作成（Tenant第一引数）
• PostgresqlExecutor 実装
• MysqlExecutor 実装
• SqlExecutors ファクトリ作成
• DataSource クラス作成
• PostgreSQL マイグレーション追加（.sql）
• MySQL マイグレーション追加（.mysql.sql）
• 両DBでテスト実行

---

ページネーション（LIMIT/OFFSET）

必須ルール

1. ORDER BYを必ず指定する — ORDER BYなしのLIMIT/OFFSETは非決定的。ページ送りでレコードが重複・欠落する。
2. tie-breakerとしてPKを追加する — ORDER BY created_at DESC だけでは、同一created_atのレコード間でページ境界が非決定的。ORDER BY created_at DESC, id DESC のようにPKを追加して完全に決定的にする。
3. CTEで先にLIMITしてからJOINする — 全行をJOIN→GROUP BY→LIMITするとデータ量に比例して遅くなる。CTEで先にLIMIT/OFFSETしてからJOINすれば、JOINの対象行がLIMIT件数分のみになり大幅に高速化できる。

パターン

-- NG: 全行JOIN→GROUP BY→LIMIT（200万行で3秒）
SELECT idp_user.*, roles, permissions
FROM idp_user
LEFT JOIN idp_user_roles ON ...
WHERE idp_user.tenant_id = ?
GROUP BY idp_user.id
ORDER BY idp_user.created_at DESC, idp_user.id DESC
LIMIT ? OFFSET ?;

-- OK: CTEで先にLIMIT→JOIN（200万行で185ms）
WITH paged_users AS (
  SELECT id, created_at FROM idp_user
  WHERE tenant_id = ?
  ORDER BY created_at DESC, id DESC
  LIMIT ? OFFSET ?
)
SELECT idp_user.*, roles, permissions
FROM paged_users
JOIN idp_user ON idp_user.id = paged_users.id
LEFT JOIN idp_user_roles ON ...
WHERE idp_user.id IN (SELECT id FROM paged_users)
GROUP BY idp_user.id
ORDER BY idp_user.created_at DESC, idp_user.id DESC;

JOINテーブルの条件をCTEに含める

role/permissionなどJOINテーブルでフィルタする場合、CTEの外に条件を残すとページネーションが壊れる（LIMIT後にさらに絞り込まれて件数が減る）。CTE内にJOINを含めて正しく絞り込んでからLIMITする。

if (hasRoleOrPermissionFilter) {
  cteFrom = """
    SELECT DISTINCT idp_user.id, idp_user.created_at FROM idp_user
    LEFT JOIN idp_user_roles ON idp_user.id = idp_user_roles.user_id
    LEFT JOIN role ON idp_user_roles.role_id = role.id
    ...
    """;
} else {
  cteFrom = "SELECT id, created_at FROM idp_user ";
}

---

悲観ロック（SELECT FOR UPDATE）

背景（Issue #1454）

authentication_transaction のように状態遷移を持つリソースに対して、複数リクエストが同時にDELETEを実行すると、ON DELETE CASCADE による子テーブル（authentication_interactions）の行削除順序が異なり、PostgreSQLでデッドロックが発生する。

Process A: DELETE authentication_transaction → CASCADE DELETE interactions (row 1, row 2)
Process B: DELETE authentication_transaction → CASCADE DELETE interactions (row 2, row 1)
→ 循環待ち → deadlock detected

対策パターン

親テーブルの行を SELECT FOR UPDATE で先にロックし、並行操作を直列化する。

// QueryRepository インターフェース
AuthenticationTransaction getForUpdate(Tenant tenant, AuthenticationTransactionIdentifier identifier);
AuthenticationTransaction getForUpdate(Tenant tenant, AuthorizationIdentifier identifier);

-- PostgreSQL
SELECT ... FROM authentication_transaction
WHERE id = ?::uuid AND tenant_id = ?::uuid
FOR UPDATE

-- MySQL（同一構文で動作）
SELECT ... FROM authentication_transaction
WHERE id = ? AND tenant_id = ?
FOR UPDATE

EntryService での使い方

@Transaction 内で getForUpdate() により再取得し、以降の処理はロック済みオブジェクトを使う。

@Transaction
public class CibaFlowEntryService {
  public AuthenticationInteractionRequestResult interact(
      ..., AuthenticationTransaction authenticationTransaction, ...) {

    // 引数の authenticationTransaction はトランザクション外で取得済み。
    // @Transaction 内で FOR UPDATE 付きで再取得して悲観ロックを取る。
    AuthenticationTransaction lockedTransaction =
        authenticationTransactionQueryRepository.getForUpdate(
            tenant, authenticationTransaction.identifier());

    // 以降は lockedTransaction を使い、元の authenticationTransaction は使わない
  }
}

適用箇所

状態遷移を持ち、並行書き込み（DELETE/UPDATE）が発生しうるリソースに適用:

EntryService	メソッド	理由
CibaFlowEntryService	interact()	FIDO-UAF認証完了 + キャンセルの同時到着
OAuthFlowEntryService	interact(), authorize(), deny(), callbackFederation()	二重クリック、タブ重複
UserOperationEntryService	interact()	デバイス認証操作の並行実行

注意点

• SELECT FOR UPDATE はトランザクション内でのみ有効（@Transaction 必須）
• ロック保持中に外部サービス呼び出し（FIDO-UAF等）があると、その応答時間分だけロックが保持される。現状はデッドロック（500）よりマシというトレードオフ
• 読み取り専用クエリ（get() / findList()）には FOR UPDATE を付けない

---

SQL エラーハンドリング

SqlError 分類

SqlErrorClassifier がDB固有のエラーコードを統一分類し、SqlExecutor が適切な例外に変換する。

SqlError	PostgreSQL	MySQL	例外クラス	HTTP
UNIQUE_VIOLATION	23505	1062	SqlDuplicateKeyException	409
FK_VIOLATION	23503	1451/1452	SqlForeignKeyViolationException	404
NOT_NULL_VIOLATION	23502	1048	SqlBadRequestException	400
CHECK_VIOLATION	23514	3819	SqlBadRequestException	400
DEADLOCK_DETECTED	40P01	1213	SqlTransactionConflictException	409
SERIALIZATION_FAILURE	40001	1205	SqlTransactionConflictException	409
OTHER	—	—	SqlRuntimeException	500

FK違反の用途

authentication_transaction がCASCADE DELETEされた後に、別トランザクションが子テーブルへINSERT/UPDATEしようとした場合に発生。ApiExceptionHandler で「セッション期限切れ」として404を返す。

トランザクション競合の用途

SELECT FOR UPDATE を導入してもデッドロックが完全にゼロにはならない（想定外のロック順序、アプリケーション外からの操作等）。SqlTransactionConflictException → 409でクライアントにリトライ可能であることを伝える。

---

トラブルシューティング

SQL構文エラー

問題	原因	解決策
PostgreSQLで動くがMySQLでエラー	DB固有構文の混在	両Executor実装を確認
UUIDキャストエラー	?::uuidがMySQLで失敗	MySQL版では文字列として扱う
JSONクエリ失敗	JSON関数の差異	上記JSON操作表を参照

デッドロック / ロック競合

問題	原因	解決策
deadlock detected (PostgreSQL 40P01)	CASCADE DELETE で子テーブルの行ロック順序が競合	親テーブルに SELECT FOR UPDATE を適用
Lock wait timeout exceeded (MySQL 1205)	FOR UPDATE のロック保持時間が長い	外部サービス呼び出しをロック区間外に分離検討
FK violation after concurrent DELETE	別トランザクションが先に親行を削除済み	SqlForeignKeyViolationException で404返却

マイグレーション失敗

問題	原因	解決策
MySQLでファイル認識されない	接尾辞が.sql	.mysql.sqlに変更
バージョン競合	既存マイグレーションとの衝突	flyway repair 実行
スキーマ不整合	手動変更との不一致	docker compose down -v で初期化

---

Redis Cache 実装

CacheStore インターフェース

public interface CacheStore {
  <T> void put(String key, T value);
  <T> void put(String key, T value, int timeToLiveSeconds);
  <T> Optional<T> find(String key, Class<T> type);
  boolean exists(String key);
  void delete(String key);
  long increment(String key, int timeToLiveSeconds);  // Lua スクリプトによるアトミックカウンター
}

実装: JedisCacheStore（Jedis ベース）。全操作で例外はログ出力のみ（non-blocking）。

用途

• Rate limiting カウンター（increment）
• 認証試行回数トラッキング
• トークン Introspection キャッシュ（TTL 60秒）

---

暗号化・ハッシュ化

AesCipher vs HmacHasher

要件	AesCipher	HmacHasher
元の値を復元する必要がある	✓ (decrypt)	✗
検索キーとして使う	✗	✓ (hash して比較)
機密データ保存	✓	✗
PII（email, phone）	✓ (復号して表示)	✗

• AesCipher: AES-256-GCM、12byte IV、EncryptedData（ciphertext + IV）を返す
• HmacHasher: HMAC-SHA256、Base64URL エンコード、一方向（verify メソッドなし）

---

Row Level Security (RLS) - PostgreSQL Only

PostgreSQL で行レベルのテナント分離を実現。

ALTER TABLE client_configuration ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON client_configuration
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

TransactionManager が SET LOCAL app.tenant_id = '{tenantId}' でコネクションレベルの設定を行い、RLS ポリシーが自動でフィルタリングする。LOCAL スコープのためトランザクション終了時に自動クリア。

MySQL では RLS 非対応のため、アプリケーション層での明示的 WHERE 句が必要。

---

関連スキル

スキル	用途
/ops-local-env	ローカルDB環境構築
/dev-architecture	アーキテクチャ全体像
/ops-deployment	運用・監視