JSONから完全なユーザーテーブルスキーマを生成する

本番環境対応のユーザーテーブルの構築

ユーザーテーブルはほぼすべてのアプリケーションデータベースの基盤です。JSONユーザーオブジェクトを適切に設計されたSQLスキーマに変換するには、データ型、制約、インデックス、セキュリティの考慮事項への注意が必要です。

JSON例

{
  "id": "usr_7f3b2a1e",
  "email": "alice@example.com",
  "username": "alice_dev",
  "display_name": "Alice Johnson",
  "password_hash": "$2b$12$LJ3m4ys...",
  "avatar_url": "https://cdn.example.com/avatars/alice.jpg",
  "role": "admin",
  "is_active": true,
  "is_verified": true,
  "last_login_at": "2024-06-14T22:15:00Z",
  "created_at": "2024-01-10T08:00:00Z",
  "updated_at": "2024-06-14T22:15:00Z"
}

生成されるSQL

CREATE TABLE users (
  id VARCHAR(20) PRIMARY KEY,
  email VARCHAR(320) NOT NULL,
  username VARCHAR(50) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  avatar_url VARCHAR(2048) NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'viewer',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  last_login_at TIMESTAMP WITH TIME ZONE NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT uq_users_username UNIQUE (username),
  CONSTRAINT chk_users_role CHECK (role IN ('admin', 'editor', 'viewer'))
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_role ON users (role);
CREATE INDEX idx_users_created_at ON users (created_at);

主要な設計判断

メールフィールド — RFC 5321の最大値に従いVARCHAR(320)。ログインクエリがこのカラムにヒットするため、常にUNIQUEかつインデックス付き。

パスワードハッシュ — VARCHAR(255)はbcrypt/argon2の出力を保存します。平文パスワードは絶対に保存しないでください。カラム名password_hashは意図を示します。

文字列ID — プレフィックス付き文字列ID（usr_7f3b2a1e）は現代のAPIで一般的なパターンです。型安全性を提供します（ユーザーIDが期待される場所に注文IDを誤って渡すことができません）。

is_activeとis_verifiedの分離 — 2つのブールフラグは単一のstatusフィールドよりも明確です。activeはアカウントが停止されていないことを意味し、verifiedはメールが確認されたことを意味します。

タイムスタンプ — created_atとupdated_atはタイムゾーンとデフォルト値付き。last_login_atはnullableです。新しく作成されたユーザーはまだログインしていない可能性があるためです。

セキュリティの考慮事項

• password_hashカラムはデフォルトでSELECTクエリに表示すべきではありません。除外するビューの作成を検討してください。
• ログイン試行のレート制限をアプリケーション層に追加してください。
• emailを小文字で保存し、関数インデックスを追加することを検討してください：CREATE UNIQUE INDEX ON users (LOWER(email))。

論理削除の拡張

ユーザーアカウントには通常、論理削除が好まれます：

deleted_at TIMESTAMP WITH TIME ZONE NULL

削除後のメール再利用を許可するために、部分ユニークインデックスを追加してください：WHERE deleted_at IS NULL。