管理画面フォーマット開発編 #1
Prisma × PostgreSQLで進めるDB設計

管理画面フォーマット（UIのみ版）を土台に、バックエンドの第一弾としてのDB設計

初回公開日2025/9/10

最終更新日2025/11/10

0. はじめに

本記事は、これまでに公開してきた 管理画面フォーマット（UIのみ版） を土台に、バックエンド開発へ進む最初のステップです。具体的には、Prisma × PostgreSQL を用いた DB設計 に焦点を当てます。

◯ 管理画面フォーマット（UIのみ版）については下記を参照ください

まとめ記事：【管理画面フォーマット制作編 #9】 Shadcn/ui で作る管理画面フォーマット ─ デモ公開とカスタマイズ方法
デモページ：管理画面フォーマット（UIのみ版）

本記事の位置づけ

これまでのシリーズでは、ログイン画面・ユーザー管理・サイドバーなど、UI部分を中心に整備してきました。その成果をまとめた「UIデモ」までを完了した今、UIに接続するデータの基盤を設計することが次のステップとなります。

DB設計は単なるデータ格納の定義ではなく、サービスの将来像を見据えたスキーマの骨格を決める重要工程です。とりわけDELOGsプロジェクトでは、複数のWebサービスや法人利用を前提とした開発を目指しています。

また、ソースコードは上記の 【管理画面フォーマット制作編 #9】 Shadcn/ui で作る管理画面フォーマット ─ デモ公開とカスタマイズ方法 に記載したGithubにあるソースコードを改変していきます。

本記事のゴール

本記事では、以下の2点をゴールとします。

ゴール	内容
Account関連の正規化	企業ID（Account）を起点に、支店（Branch）、部署（Department）、担当者（Contact）、利用契約（Subscription）を正規化して設計する
displayIdルール確立	2文字接頭辞 + 8桁ゼロ埋めの一貫した形式を定義し、主体テーブルにのみ採用する
管理画面フォーマットで利用する各テーブルの設計	ユーザ（User）、ロール（Role）、メニュー（Menu）

これにより、ユーザーやロール、メニューといった管理画面の主要要素を 法人利用に耐えうる形で整理し、今後の記事で取り扱うAPIやRBAC設計へとつなげます。

前提環境と採用技術

DELOGsプロジェクトにおける開発環境・技術スタックは下記の通りです。

区分	採用技術・環境
フロント	Next.js 15、shadcn/ui、Tailwind CSS
サーバ	Ubuntu 24.04 LTS、Nginx、Node.js（PM2管理）
DB	PostgreSQL 13（さくらのクラウド・アプライアンス構成）
ORマッパー	Prisma

UIのみ版で整えた土台に対し、今回の記事からはDB設計を開始し、次回以降でスキーマ実装やSeed投入へ進んでいきます。

管理画面フォーマット開発編の完成形（デモとGithubリポジトリ）

「管理画面フォーマット開発編 #1〜#11」の記事内容についてのデモページです。下記より確認可能です。
管理画面フォーマット開発編の完成版デモ： https://delogs.jp/demo/dashboard-format

Githubにも公開済みです。
管理画面フォーマット開発編のGihubリポジトリ： https://github.com/delogs-jp/dashboard-format-fullstack

Prismaの準備

【管理画面フォーマット制作編 #9】 Shadcn/ui で作る管理画面フォーマット ─ デモ公開とカスタマイズ方法 の段階では、まだプロジェクトに Prisma をインストールしていません。

過去記事 Prisma × PostgreSQLで始めるユーザー・ロール管理 を参考にインストールを実行します。

DB作成

zsh

psql -h 192.168.XX.XX -p 5432 -U your_user 

上記でDBの接続して、下記コマンドで管理画面フォーマット用のDBを作成します。

zsh

CREATE DATABASE delogs_demo
  WITH ENCODING 'UTF8'
       LC_COLLATE='ja_JP.UTF-8'
       LC_CTYPE='ja_JP.UTF-8'
       TEMPLATE=template0;

delogs_demoがデータベース名ですので、任意の名称に置き換えてください。

Prismaのインストール

zsh

npm install prisma --save-dev
npm install @prisma/client @prisma/extension-read-replicas

@prisma/extension-read-replicasはPrismaでリードレプリカDBを扱うためのプラグインです。リード用DBがある環境では、インストールすると便利です。詳細は、 【自サーバでNext.jsアプリを動かす#5】リードレプリカ（スレーブ）DBの利用 を参照ください。

Prismaの初期化

zsh

npx prisma init

.envの設定

ローカル環境からのDB接続に設定を行ってください。

env

# 例
DATABASE_URL="postgresql://dbuser:dbuserpassword@127.0.0.1:5433/delogs_demo?schema=public"
DATABASE_URL_REPLICA="postgresql://dbuser:dbuserpassword@127.0.0.1:5434/delogs_demo?schema=public"

上記はローカルPCからSSHトンネルでDBサーバへ接続する場合の例になっています。

これで準備完了です。次章から設計を行っていきます。

1. 設計原則 ─ 命名・共通カラム・論理削除

DB設計を進めるにあたり、まずは全テーブルに共通する「設計原則」を定義します。これらは、スキーマを一貫性のある形で保ち、将来的な拡張や保守を容易にするための基本ルールとなります。

命名規則

命名規則は、PrismaとPostgreSQLの両方において可読性と拡張性を両立するために定めます。基本方針は「モデル名は単数形・PascalCase」「カラムはcamelCase」です。

区分	規則	例
テーブル（Prismaモデル名）	単数形、PascalCase	`User`, `Account`, `Branch`
テーブル（DB物理名）	ダブルクオート付きPascalCase（Prisma既定）	`"User"`, `"Account"`
カラム名	camelCase	`createdAt`, `updatedAt`
外部キー	`<参照先名>Id`	`accountId`, `branchId`
インデックス名	`<Table>_<Column>_idx`	`User_email_idx`

共通カラム

すべての「主体テーブル」に共通して持たせるカラムを定義します。中間テーブルは対象外とします。

カラム	型	説明
id	UUID	プライマリキー
displayId	VARCHAR(10)	2文字接頭辞 + 8桁ゼロ埋め（例: `AC00000001`）。主体テーブルのみ
createdAt	TIMESTAMP	レコード作成日時
updatedAt	TIMESTAMP	レコード更新日時
deletedAt	TIMESTAMP NULLABLE	論理削除を採用する場合に利用

これにより、すべての主要テーブルが共通の監査基盤を備え、画面やAPIで一貫した扱いが可能になります。

論理削除の扱い

データ削除の扱いは、システム運用上の要件に直結するため、明確にルールを定めます。

論理削除（deletedAtを利用）を基本とする
履歴管理や復元が容易になり、誤削除による影響を軽減できます。
物理削除とするケース
中間テーブル（UserRole, MenuRole など）は冗長データが少なく、再作成も容易なため物理削除を許容します。

区分	採用方式	理由
主体テーブル（Account, Branch, User, Menu など）	論理削除	監査性・復元性を優先
中間テーブル（UserRole, MenuRole など）	物理削除	再作成が容易で履歴保持の必要性が低い

以上をまとめると、主体テーブルは「UUID + displayId + 監査カラム」を必須とし、削除は論理削除を基本とする。一方で、中間テーブルはシンプルな構造を維持し、物理削除で運用する方針となります。

2. displayId 仕様

本章では、各テーブルで共通して利用する displayId の仕様を整理します。
displayId は「人がUI上で確認・識別するためのID」であり、UUIDとは別に発行されます。

基本仕様

displayId は以下のルールに統一します。

項目	仕様
形式	2文字接頭辞 + 8桁ゼロ埋めの数値（ハイフンなし）
長さ	固定10文字
対象テーブル	主体テーブル（Account, Branch, Department, Contact, Subscription, User, Role, Menu）
非対象テーブル	中間テーブル（UserRole, MenuRoleなど）
採番方式	PostgreSQLのシーケンスを利用し、専用関数でprefixと連結
表示用途	UI上の識別、検索、管理画面の一覧表示など

例）AC00000001, US00000042, MN00001000

対象テーブルと接頭辞一覧

displayId の接頭辞は、テーブルを直感的に識別できる2文字に固定します。

テーブル	接頭辞	サンプル
Account	`AC`	`AC00000001`
Branch	`BR`	`BR00000001`
Department	`DP`	`DP00000001`
Contact	`CT`	`CT00000001`
Subscription	`SB`	`SB00000001`
User	`US`	`US00000001`
Role	`RL`	`RL00000001`
Menu	`MN`	`MN00000001`
UserRole	―	UUIDのみ
MenuRole	―	UUIDのみ

採番の仕組み

displayId は PostgreSQL のシーケンスと関数を組み合わせて採番します。
この仕組みにより、アプリ側から特別な処理を追加せずとも一貫したID発行が可能です。

シーケンス：テーブルごとに専用シーケンスを用意
例：account_display_id_seq, user_display_id_seq
関数：共通関数 generate_display_id(seq_name, prefix) を利用
Prisma定義：@default(dbgenerated(...)) でDB側関数を呼び出す

運用上の留意点

UUIDと併用：内部処理や外部キー参照はUUIDを利用し、displayIdはUI/検索用途に限定する
変更不可：一度採番されたdisplayIdは更新禁止。ユニーク制約を必須とする
検索最適化：displayId列にはユニークインデックスを付与して検索性能を確保する
桁数の余裕：8桁採番で最大9,999万件まで対応可能。将来的な拡張にも十分な余地がある

以上により、displayId は「人が読みやすい内部識別子」として標準化され、UUIDと併用することで安全性と可読性を両立させます。

3. ER概観 ─ マルチテナントとスコープ

本章では、本システムのER（Entity-Relationship）の全体像を示し、マルチテナント対応とスコープの考え方を整理します。
User は departmentId と roleId が必須であり、契約（Subscription）は部署単位で管理することを基本方針とします。

全体像

DELOGsプロジェクトでは法人利用を前提に、会社 → 支店 → 部署 → ユーザ/担当者という階層構造を採用します。
Userは必ず部署に属し、さらにロールを1つだけ持ちます。契約は部署単位で結ばれ、ログイン認証・利用制御・契約管理が同一スコープで一致するように設計します。

主体テーブルと関係性

テーブル	説明	主な関係
Account	会社マスタ	複数の Branch を持つ
Branch	支店	Account に属し、複数の Department を持つ
Department	部署	Branch に属し、複数の User と Contact を持つ
Contact	担当者	Department に属する（業務上の窓口や責任者）
Subscription	契約	Department 単位で契約期間・プラン・状態を管理
User	利用ユーザー	Department に必ず所属し、1つの Role を持つ
Role	権限ロール	User に付与され、priority・権限フラグで可視や機能を制御
Menu	管理画面メニュー	Role.priority と Menu.minPriority の比較で可視制御

注：
User は単一ロール前提、Menu 可視は minPriority 比較のみによって行います。

簡易版ER図

以下に主要テーブルの関係を文字図で示します。

txt

Account (AC) 1 ──── * Branch (BR) 1 ──── * Department (DP) 1 ──── * User (US) ──── 1 Role (RL)
                                               │
                                               └──── * Contact (CT)

Department (DP) 1 ──── * Subscription (SB)

Role (RL: priority, canEditData, canDownloadData)
      │
      └─── 判定 ───> Menu (MN: minPriority, visible)

スコープの考え方

スコープとは「どの単位で制御をかけるか」を指します。
本システムでは Department を最小スコープと定義し、契約・ログイン・RBAC の全てをこの単位に揃えます。

項目	スコープ	補足
契約（Subscription）	Department 単位	部署ごとに契約期間・状態・プランを管理
ログイン	Department 単位	ログイン時に入力される「アカウントID」は `department.code`
RBAC	Department 単位	User は必ず `departmentId` と `roleId` を持ち、権限判定に利用

このように 部署を最小単位とする設計により、ログイン・契約・権限を統一的に扱うことが可能となります。
次章からは、Account 系の各テーブル設計を順に詳細化していきます。

4. Account系正規化設計

Account 系は「会社 → 支店 → 部署 → 担当者 → 契約」という法人組織の構造を表す基盤です。以降は 主体テーブルに共通のカラムとして id(UUID) / displayId / isActive(BOOLEAN) / createdAt / updatedAt / deletedAt(NULLABLE) を前提にします（※中間テーブルは isActive を持ちません）。

Account（会社マスタ）

Account は最上位のエンティティで、会社そのものを表します。支店や部署のツリーの根となるため、最小限の会社情報を保持します。

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`AC00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
name	TEXT	✔︎	会社名
headquartersAddress	TEXT		本店所在地
invoiceNumber	TEXT		インボイス登録番号
remarks	TEXT		備考（任意メモ）
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム（論理削除は任意）

Branch（支店）

Branch は Account に属する支店を表します。

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`BR00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
accountId	UUID	✔︎	Account へのFK
name	TEXT	✔︎	支店名
address	TEXT		支店所在地
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

Department（部署）

Department は Branch に属し、User は必ず Department に所属します。

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`DP00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
code	TEXT	✔︎	ログイン時に使用
branchId	UUID	✔︎	Branch へのFK
name	TEXT	✔︎	部署名
phone	TEXT		代表電話
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

Contact（担当者）

契約・運用に関わる窓口情報です。部署に紐づけます。

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`CT00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
departmentId	UUID	✔︎	Department へのFK
name	TEXT	✔︎	担当者名
email	TEXT		メール
phone	TEXT		電話
duty	TEXT		担当区分（例: 契約窓口/請求窓口/技術窓口など）
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

Subscription（契約：部署単位）

部署（Department）単位で、申込み〜利用終了までを管理します。ログインスコープと契約スコープを一致させます。

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`SB00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
departmentId	UUID	✔︎	Department へのFK
statusId	UUID	✔︎	SubscriptionStatus へのFK
planId	UUID	✔︎	SubscriptionPlan へのFK
startDate	DATE	✔︎	利用開始日
endDate	DATE		利用終了日
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

SubscriptionStatus（契約ステータス・マスタ）

Subscription（契約：部署単位）で利用する契約ステータスを管理します。

カラム	型	必須	説明
id	UUID	✔︎	主キー
code	TEXT	✔︎	一意コード（例: `active`, `expired`, `pending`）
name	TEXT	✔︎	表示名
description	TEXT		説明
isActive	BOOLEAN	✔︎	有効/無効
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

SubscriptionPlan（契約プラン・マスタ）

Subscription（契約：部署単位）で利用する契約プランを管理します。

カラム	型	必須	説明
id	UUID	✔︎	主キー
code	TEXT	✔︎	一意コード（例: `basic`, `pro`, `enterprise`）
name	TEXT	✔︎	表示名
description	TEXT		説明
monthlyPrice	NUMERIC		月額（必要なら通貨管理は別途）
isActive	BOOLEAN	✔︎	有効/無効
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

Account系の関係図

以下に、Account 系エンティティの関係を示した文字図を掲載します。

txt

Account (AC)
  └─ Branch (BR)
       └─ Department (DP)
            ├─ Contact (CT)
            └─ Subscription (SB) ──┬─ SubscriptionStatus (master)
                                   └─ SubscriptionPlan   (master)

設計のポイント

User の所属は Department 必須：契約・ログイン・権限の最小単位を揃える。
Subscription は部署単位：契約管理と利用制御を直結。
Contact は任意：実運用の窓口情報として保持。
displayId は接頭辞 + 8桁ゼロ埋め：AC/BR/DP/CT/SB を採用。
isActive は主体テーブルで必須：UIの有効/無効切替・論理削除と併用しやすい。
契約は部署単位 + マスタ参照：statusId と planId を最初からFK化。
schema.prisma（9章で作成します）は、上記を前提に FK/ユニーク/インデックス を定義します。

5. User 設計（US）

User テーブルは 認証・権限・契約 の三要素をつなぐ中心的なエンティティです。
特に本設計では、必ず Department（部署）に所属させることで、契約スコープ（Subscription）とログインスコープを一致させます。ログインは departmentId + email + hashedPassword の3要素で行います。

User（ユーザー）

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`US00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
departmentId	UUID	✔︎	Department FK（ログイン時のスコープ）
roleId	UUID	✔︎	Role FK（ユーザーは常に1ロール）
email	TEXT	✔︎	ログイン・通知用メール（部署内で一意）
hashedPassword	TEXT	✔︎	パスワード（ハッシュ）
name	TEXT	✔︎	氏名（表示名）
phone	TEXT		電話番号
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

制約・運用ポイント

一意制約：@@unique([departmentId, email])（部署内でメールを一意）
- 同一人物が部署をまたぐ場合は、部署ごとに別ユーザとして登録可能。
正規化・正規化：email は小文字化・トリム等で正規化して保存（衝突回避）。
セキュリティ：hashedPassword は PBKDF2/argon2/bcrypt 等を採用。パスワード再発行は Server Action + 一時トークンで実装。
アバター：DB でパスは管理せず、/var/www/private/avatars/{userId}.ext のなどの実ファイル存在で判定（専用 Route Handler で配信予定）。

User の関係図

以下に、User と関連エンティティの関係を文字図で示します。

txt

Department (DP)
   └─ User (US) ── 1 ── Role (RL)
        ↑
   login key: (departmentId + email) + password

認証フロー（要旨）

入力：department.code + email + password
解決：department.code → departmentIdを解決 → User を departmentId + email で検索 → hashedPassword 照合
結果：成功時に User と Role をセッションへ格納（httpOnly Cookie）、以降の可視・機能判定に利用

権限判定の位置づけ

メニュー可視：Role.priority >= Menu.minPriority（親の minPriority 継承後の実効値）
機能可否：Role.canDownloadData／Role.canEditData を参照
補足：User は中間テーブルを介さず roleId 直付け。複数ロールや例外可視が必要になった場合は将来拡張で中間を増設

インデックス指針

種別	対象	目的
UNIQUE	`(departmentId, email)`	ログイン検索の一意性保証
INDEX	`departmentId`	部署内一覧・JOIN 最適化
INDEX	`roleId`	ロール別集計・抽出
INDEX	`isActive`	有効ユーザ抽出
INDEX	`createdAt`	作成日時ソート（管理UI）

この構成により、部署スコープに根ざしたログインと、ロールに基づく単調な可視・機能判定をシンプルに実現します。

6. Role 設計（RL）

Role テーブルは、ユーザーの権限を一元的に管理するためのエンティティです。
本設計では、ユーザーは必ず 1つの Role を持ち、priority に基づく階層判定と、canDownloadData / canEditData による機能別の制御を組み合わせます。

Role（ロール）

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`RL00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
code	TEXT	✔︎	一意コード（例: `ADMIN`, `EDITOR`）
name	TEXT	✔︎	表示名（UI 表示用）
priority	INT	✔︎	権限レベル（数値が大きいほど強い権限）
badgeColor	TEXT		UI 表示用カラーコード
isSystem	BOOLEAN	✔︎	システム定義ロールかどうか
canDownloadData	BOOLEAN	✔︎	データダウンロード可否
canEditData	BOOLEAN	✔︎	データ編集可否
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

権限設計の考え方

階層制御：priority によって、メニューや機能の最低必要権限（minPriority）と比較し、可視性や操作可否を判定します。
機能制御：同じ priority レベルのロールでも、canDownloadData / canEditData の値によって細かな違いを設けられます。
システムロール：isSystem = true のものは、削除・無効化不可の初期ロールとして保持します。

Role の関係図

txt

Role (RL)
   └─ User (US) が必ず1つ参照
        ├─ priority >= Menu.minPriority でメニュー可視
        ├─ canDownloadData = true ならデータ出力可
        └─ canEditData = true なら編集操作可

設計のポイント

単一ロール付与：ユーザーは必ず1つのロールを持ち、複数ロールは想定しない。
メニュー制御：Menu 側の minPriority と Role の priority を比較して表示判定。
機能制御：Role 内部のフラグで補足制御（編集／ダウンロード）。
UI連動：badgeColor により一覧やプロフィール画面での視認性を高める。
マスタ性：ロールは運用上頻繁に増減させないことを前提に設計。

このように Role 設計を定義することで、メニュー可視性と機能操作の両面を一元管理し、UI 側の権限制御をシンプルに実装できます。

7. Menu（MN）の設計

Menu テーブルは、管理画面におけるナビゲーション構造を管理するエンティティです。
UI 層では menu.mock.ts に定義されていた情報を起点にしていましたが、本設計ではそれを正規化し、DB テーブルとして永続化します。
Menu は 階層構造を持ち、さらに Role.priority × Menu.minPriority の比較により可視制御を行います。

Menu（メニュー本体）

Menu は階層型のメニュー情報を表し、表示名・遷移先・アイコン・優先度などを保持します。

カラム	型	必須	説明
id	UUID	✔︎	主キー
displayId	VARCHAR(10)	✔︎	`MN00000001`
isActive	BOOLEAN	✔︎	有効/無効（既定: true）
parentId	UUID		親メニューへの FK（NULL ならルート）
title	TEXT	✔︎	表示名
href	TEXT		遷移先パス（内部/外部を含む）
isExternal	BOOLEAN		外部 URL かどうか
iconName	TEXT		アイコン名（UI で Lucide 等に解決）
match	TEXT（ENUM想定）	✔︎	マッチ方法（`exact` or `prefix`）
pattern	TEXT		追加のマッチパターン
minPriority	INT		可視化に必要な最小権限（親から継承、上書き不可）
isSection	BOOLEAN	✔︎	セクション見出しかどうか
sortOrder	INT	✔︎	同階層内での並び順
remarks	TEXT		備考
createdAt / updatedAt / deletedAt	TIMESTAMP	✔︎ / ✔︎ /	共通カラム

可視制御の仕組み

Menu 可視判定は Role.priority と Menu.minPriority の比較で行います。
これにより、上位ロールは下位ロールの可視範囲を包含する単調性が保証されます。
また、親メニューの minPriority は子メニューに継承され、子での上書きは不可とします。

Menu の関係図

以下に、Menu と Role による可視制御の関係を文字図で示します。

txt

Role (RL: priority)
       │
       └─── priority >= Menu.minPriority → Menu (MN)
                     │
                     └─ parentId により階層構造を構築

設計のポイント

項目	内容
階層構造	`parentId` により多階層（ルート → サブ → サブサブ）を表現
並び順	同階層内は `sortOrder ASC` で表示
可視制御	`Role.priority >= Menu.minPriority` で表示可否を判定
継承ルール	親の `minPriority` は子に強制継承。子での下げは不可
UI要素	`iconName` や `isSection` で UI レンダリングを補助
外部リンク対応	`href` + `isExternal` により外部 URL も管理可能

この設計により、Menu は 階層構造・並び順・可視制御を一元的に管理でき、UI 層は「ログインユーザの Role.priority を渡して、可視メニューを取得 → sortOrder 順で描画」するだけで済みます。

8. インデックス・ユニーク制約設計

この章では、各テーブルの ユニークキー と 推奨インデックス を体系化します。
設計方針は「検索・照合で多用するキーに最小限のインデックス」「UI要件（並び順・可視判定）を高速化」「将来拡張に耐える制約の粒度」です。

基本方針

本プロジェクトのクエリは「部署スコープ」「表示ID検索」「メニュー表示」「ログイン照合」が中心です。
したがって以下を必須とします。

displayId は主体テーブルで UNIQUE（人が扱う識別子の一意性担保）
外部キー列は基本的に INDEX（JOIN/絞り込みを高速化）
並び順に使う列は複合INDEX（例：(parentId, sortOrder)）
よく使うフラグは単体INDEX（例：isActive、minPriority）

ユニークキー設計（横断）

各テーブルのユニーク性を一覧化します。

テーブル	ユニークキー	用途/理由
Account	`displayId`	人間可読IDの一意性
Branch	`displayId`	同上
Department	`displayId`	同上
Contact	`displayId`	同上
Subscription	`displayId`	同上
SubscriptionStatus	`code`	マスタの論理一意名
SubscriptionPlan	`code`	同上
User	`displayId`	人間可読IDの一意性
User	`(departmentId, email)`	ログイン照合キー（部署内メール一意）
Role	`displayId`	人間可読IDの一意性
Role	`code`	システム内の論理一意名（`ADMIN` など）
Menu	`displayId`	人間可読IDの一意性
Menu	`(parentId, sortOrder)`	同階層で表示順の一意性（重複防止）

外部キー・参照整合性

UIとSeedの運用を考慮し、外部キーの 参照整合性 は次の原則を推奨します。

FK 先	参照元	onUpdate	onDelete	理由
Account	Branch	CASCADE	RESTRICT	名前変更伝播、安易な削除を防ぐ
Branch	Department	CASCADE	RESTRICT	同上
Department	User	CASCADE	RESTRICT	ユーザ孤児化を防ぐ
Department	Contact	CASCADE	SET NULL/RESTRICT	運用方針で選択（窓口一時未設定可否）
Department	Subscription	CASCADE	RESTRICT	契約履歴保持を優先
Role	User	CASCADE	RESTRICT	ロール削除を厳格に
Menu（親）	Menu（子）	CASCADE	RESTRICT	ツリーの整合性保持

補足：物理削除は最小限。主体テーブルは原則「論理削除（deletedAt）」で扱います。

テーブル別インデックス

性能上の効果が高いものに絞って記載しています。必要に応じて運用で追加します。

Account / Branch / Department（組織階層）

テーブル	推奨 INDEX	目的
Account	`displayId UNIQUE`	UI検索
	`isActive`	有効一覧
Branch	`displayId UNIQUE`	UI検索
	`accountId`	JOIN高速化
	`isActive`	有効一覧
Department	`code UNIQUE`	UI検索・ログイン前解決
	`branchId`	JOIN高速化
	`isActive`	有効一覧

Contact

推奨 INDEX	目的
`displayId UNIQUE`	UI検索
`departmentId`	組織別一覧
`isActive`	有効一覧
（オプション） DBレベル部分一致 `LOWER(email)`	窓口検索の高速化（Prismaは functional index を SQL で追加）

Subscription / SubscriptionStatus / SubscriptionPlan

テーブル	推奨 INDEX	目的
Subscription	`displayId UNIQUE`	UI検索
	`departmentId`	組織別契約参照
	`(departmentId, startDate DESC)`	直近契約の高速取得
	`statusId`, `planId`	マスタ絞り込み
SubscriptionStatus	`code UNIQUE`	論理一意
	`isActive`	有効一覧
SubscriptionPlan	`code UNIQUE`	論理一意
	`isActive`	有効一覧

発展：部署単位の契約期間 重複禁止 は PostgreSQL の EXCLUDE 制約（USING gist + daterange）で表現可能（Prismaは直接未対応のため SQL で追加）。

User

推奨 INDEX	目的
`displayId UNIQUE`	UI検索
`(departmentId, email) UNIQUE`	ログインキー
`departmentId`	部署別一覧
`roleId`	ロール別集計
`isActive`	有効ユーザ抽出
`createdAt`	新着ユーザ表示

補足：メールは LOWER(email) を保存時に正規化し、比較の一貫性を担保します。

Role

推奨 INDEX	目的
`displayId UNIQUE`	UI検索
`code UNIQUE`	論理一意
`isActive`	有効一覧
`priority`	並び替え・可視閾値比較の補助

推奨 INDEX	目的
`displayId UNIQUE`	UI検索
`(parentId, sortOrder) UNIQUE`	階層内の表示順重複防止
`parentId`	ツリー構築 JOIN
`isActive`	有効メニュー抽出
`minPriority`	ロール閾値での可視判定前処理
（ENUM） `match`	`exact/prefix` のフィルタ（ENUMは内部的に高速）

パフォーマンス運用の要点

項目	推奨	補足
低選択性フラグ	`isActive` に単体INDEX	1/0 フラグでもフィルタ初期段階に有効
並び替え	`(parentId, sortOrder)` 複合	Menu の描画を 1 クエリで完結
ログイン	`(departmentId, email)` UNIQUE	WHERE + UNIQUE で即ヒット
可視判定	`minPriority` 単体INDEX	ロール閾値前処理に寄与
正規化	`LOWER(email)` 保存	大文字小文字の不一致解消
分析	`createdAt` INDEX	新着/期間集計の定番
監査	`deletedAt` に INDEX は不要	論理削除は「IS NULL」主体のため状況に応じて

注意：インデックスは 書き込みコスト とトレードオフ。測定（EXPLAIN ANALYZE）の上、最小構成から段階的に追加します。

9. Prismaスキーマの作成（モデル一覧）

ここでは、これまでに設計してきた Account系 / User系 / Menu系 の要件を、実運用可能な prisma/schema.prisma として 完成版 まで落とし込みます。
本スキーマは PostgreSQL を対象とし、主体テーブルのみ displayId（接頭辞2文字 + 8桁） を持ちます。ログインは departmentId + email + hashedPassword 方式、RBAC は 単一ロール で、メニュー可視は Role.priority >= Menu.minPriority により判定します。

txt

（文字図）モデル相互の関係・採番
Account(AC)
 └─ Branch(BR)
      └─ Department(DP)
           ├─ Contact(CT)
           ├─ Subscription(SB) ──┬─ SubscriptionStatus(master)
           │                      └─ SubscriptionPlan(master)
           └─ User(US) ── 1 ── Role(RL)
                                 │
                                 └─ priority >= Menu.minPriority → Menu(MN)

displayId: 主体テーブルのみ（AC/BR/DP/CT/SB/US/RL/MN）
採番      : DB側関数 `generate_display_id(seq, prefix)` を @default(dbgenerated(...)) で呼び出し

`schema.prisma`への追記

prisma/schema.prismaを下記の内容で作成します。

prisma

// prisma/schema.prisma

// ==============================
// Generator / DataSource
// ==============================
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// ==============================
// Enums
// ==============================
enum MenuMatchMode {
  exact
  prefix
  regex
}

// ==============================
// Account 系
// ==============================
model Account {
  // 主キー / 識別
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('account_display_id_seq','AC')")) @db.VarChar(10)
  // 共通
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  // 本体
  name                String
  headquartersAddress String?
  invoiceNumber       String?
  remarks             String?

  // リレーション
  branches Branch[]

  @@index([isActive])
  @@index([createdAt])
}

model Branch {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('branch_display_id_seq','BR')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  accountId String
  name      String
  address   String?
  remarks   String?

  // リレーション
  account     Account      @relation(fields: [accountId], references: [id], onDelete: Restrict)
  departments Department[]

  @@index([accountId])
  @@index([isActive])
  @@index([createdAt])
}

model Department {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('department_display_id_seq','DP')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  // ★ ログイン用コード（人間入力・推測困難な固定文字列）
  code String @unique

  branchId String
  name     String
  phone    String?
  remarks  String?

  // リレーション
  branch        Branch         @relation(fields: [branchId], references: [id], onDelete: Restrict)
  contacts      Contact[]
  subscriptions Subscription[]
  users         User[]

  @@index([branchId])
  @@index([isActive])
  @@index([createdAt])
}

model Contact {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('contact_display_id_seq','CT')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  departmentId String
  name         String
  email        String?
  phone        String?
  duty         String? // 担当区分（契約/請求/技術 等）
  remarks      String?

  // リレーション
  department Department @relation(fields: [departmentId], references: [id], onDelete: Restrict)

  @@index([departmentId])
  @@index([isActive])
  @@index([createdAt])
}

model Subscription {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('subscription_display_id_seq','SB')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  departmentId String
  statusId     String
  planId       String
  startDate    DateTime  @db.Date
  endDate      DateTime? @db.Date
  remarks      String?

  // リレーション
  department Department         @relation(fields: [departmentId], references: [id], onDelete: Restrict)
  status     SubscriptionStatus @relation(fields: [statusId], references: [id], onDelete: Restrict)
  plan       SubscriptionPlan   @relation(fields: [planId], references: [id], onDelete: Restrict)

  @@index([departmentId])
  @@index([statusId])
  @@index([planId])
  @@index([startDate])
  @@index([isActive])
}

model SubscriptionStatus {
  id        String    @id @default(uuid())
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  code        String  @unique
  name        String
  description String?

  subscriptions Subscription[]

  @@index([isActive])
}

model SubscriptionPlan {
  id        String    @id @default(uuid())
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  code         String   @unique
  name         String
  description  String?
  monthlyPrice Decimal?

  subscriptions Subscription[]

  @@index([isActive])
}

// ==============================
// User / Role
// ==============================
model Role {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('role_display_id_seq','RL')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  code            String  @unique // 例: ADMIN / EDITOR / VIEWER ...
  name            String
  priority        Int
  badgeColor      String?
  isSystem        Boolean @default(false)
  canDownloadData Boolean
  canEditData     Boolean
  remarks         String?

  users User[]

  @@index([priority])
  @@index([isActive])
}

model User {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('user_display_id_seq','US')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  departmentId     String
  roleId           String
  email            String // login & notice
  hashedPassword   String
  name             String
  phone            String?
  remarks          String?

  // ログイン失敗ロック
  failedLoginCount Int       @default(0)
  lockedUntil      DateTime? @db.Timestamptz

  // リレーション
  department Department @relation(fields: [departmentId], references: [id], onDelete: Restrict)
  role       Role       @relation(fields: [roleId], references: [id], onDelete: Restrict)

  // Session への逆側（双方向）リレーション
  sessions Session[] @relation("UserSessions")

  // 部署内メール一意（ログインキー）
  @@unique([departmentId, email])
  @@index([departmentId])
  @@index([roleId])
  @@index([isActive])
  @@index([createdAt])
}

// Session 定義
model Session {
  id        String   @id @default(uuid())
  userId    String
  user      User     @relation("UserSessions", fields: [userId], references: [id], onDelete: Cascade)

  expiresAt DateTime @db.Timestamptz
  createdAt DateTime @default(now()) @db.Timestamptz
  revokedAt DateTime?                @db.Timestamptz

  ip        String?
  userAgent String?

  @@index([userId])
  @@index([expiresAt])
  @@index([revokedAt])
}

// ==============================
// Menu
// ==============================
model Menu {
  id        String    @id @default(uuid())
  displayId String    @unique @default(dbgenerated("generate_display_id('menu_display_id_seq','MN')")) @db.VarChar(10)
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now()) @db.Timestamptz
  updatedAt DateTime  @updatedAt      @db.Timestamptz
  deletedAt DateTime?                  @db.Timestamptz

  parentId    String?
  title       String
  href        String?
  isExternal  Boolean? // 外部URLか
  iconName    String?
  match       MenuMatchMode
  pattern     String?
  minPriority Int? // 親から継承（アプリ層で強制する）
  isSection   Boolean
  sortOrder   Int
  remarks     String?

  // リレーション（自己参照）
  parent   Menu?  @relation("MenuToMenu", fields: [parentId], references: [id], onDelete: Restrict)
  children Menu[] @relation("MenuToMenu")

  @@index([parentId])
  @@index([minPriority])
  @@index([isActive])
  @@index([sortOrder])
  @@index([createdAt])
}

スキーマのポイント（実装メモ）

displayId 採番：@default(dbgenerated("generate_display_id('...','XX')")) は DB側の関数 を呼び出します。本文中の関数名・シーケンス名は想定です（10章の実装で PostgreSQL に作成）。
論理削除：主体テーブルは isActive + deletedAt を保持。中間テーブルが存在しないため、削除時は原則「論理削除」を運用（外部キーは onDelete: Restrict）。
ログイン：User は @@unique([departmentId, email]) により部署内でメール一意。入力された department.code → departmentId 解決後に照合。
RBAC：User.roleId で単一ロール紐付け。メニュー可視は Role.priority >= Menu.minPriority。
Menu 継承ルール：minPriority は親から継承（アプリ層で解決）。DB上は NULL 許容で、親に依存しない定義も可能に。
索引：8章の指針どおり、displayId のユニーク・FK・isActive・createdAt を中心にインデックスを付与済み。
型の粒度：displayId は @db.VarChar(10)、startDate/endDate は @db.Date を明示。monthlyPrice は Decimal（必要に応じて精度/通貨設計を別章で）。

これで prisma migrate dev による初期マイグレーションが可能です。次章では PostgreSQL のシーケンス & 関数（generate_display_id）と、seed の雛形を実装します。

10. Prismaマイグレーションと migration.sql 編集

本章では、前章で定義した schema.prisma をもとに 初回マイグレーション を作成し、生成された migration.sql を編集して PostgreSQL に自動採番関数とシーケンスを組み込みます。
最終的に npx prisma migrate dev を実行するだけで、UUID + displayId の二重識別子を備えた堅牢なDBスキーマが完成します。

マイグレーションファイルの生成

まずは --create-only オプションでマイグレーションファイルを生成します。下記のコマンドを実行します。

zsh

npx prisma migrate dev --create-only -n init

これで、DB適用されない状態で、マイグレーション用のsqlファイルが生成されます。この時点では、Prisma は displayId の dbgenerated(...) をそのまま出力するため、PostgreSQL 側に関数やシーケンスを定義する必要があります。

prisma/migrations/タイムスタンプ_init/migration.sqlが生成されているので、これに若干の追記をしていきます。

共通関数の追加

migration.sql の冒頭に、displayId 自動採番のための共通関数を追加します。

sql

-- 共通関数：シーケンスと接頭辞を受け取り、2文字 + 8桁ゼロ埋めのIDを生成
CREATE OR REPLACE FUNCTION public.generate_display_id(seq_name TEXT, prefix TEXT)
RETURNS TEXT AS $$
DECLARE
  new_val BIGINT;
BEGIN
  EXECUTE format('SELECT nextval(%L)', seq_name) INTO new_val;
  RETURN prefix || lpad(new_val::TEXT, 8, '0');
END;
$$ LANGUAGE plpgsql;

この関数により、generate_display_id('user_display_id_seq','US') のように呼び出すと US00000001 が返されます。

シーケンス設定の追加

続いて、主体テーブルごとにシーケンスを作成します。先程の共通関数の直後に下記の記述を挿入します。

sql

$$ LANGUAGE plpgsql;

-- ===========================
-- displayId 用シーケンス作成
-- ===========================
CREATE SEQUENCE IF NOT EXISTS public.account_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.branch_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.department_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.contact_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.subscription_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.user_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.role_display_id_seq;
CREATE SEQUENCE IF NOT EXISTS public.menu_display_id_seq;

-- CreateEnum
CREATE TYPE "public"."MenuMatchMode" AS ENUM ('exact', 'prefix', 'regex');

-- CreateTable
CREATE TABLE "public"."Account" (

これで、PostgreSQL が account_display_id_seq 等のカウンタを永続的に保持してくれます。

マイグレーションの実行

migration.sqlの編集が完了したら、マイグレーションを実行します。

zsh

npx prisma migrate dev

これで、DBにテーブルが作成されたことが確認できると思います。npx prisma studioの実行画面は下図の通りです。

11. データ投入戦略（Seed戦略）

本章では、設計したデータベースに 初期データを投入する方法 を整理します。
「Seed（シード）」という言葉はやや専門的ですが、要は 開発・検証のために事前に準備するデータ投入の仕組み のことです。
DB設計を行った直後に「最低限のマスタデータ」や「テストユーザー」を投入することで、管理画面フォーマットを即座に動作確認できる状態にします。

データ投入の目的

初期データ投入の目的は、大きく以下の3つに分類されます。

区分	目的	具体例
マスタデータ	システム全体で参照する基準情報を登録	Role、SubscriptionPlan、SubscriptionStatus、Menu
テストデータ	開発・E2Eテストで必要な動作確認データを準備	ダミーのAccount、Branch、Department、User
運用開始補助	管理者ユーザーや最初のログインアカウントを登録	`admin@example.com` の初期ユーザー

これにより「アプリを起動してすぐログイン・画面操作ができる」状態をつくり出します。

パスワード暗号化の準備

パスワードはargon2で暗号化します。下記でプロジェクトにインストールしておきます。

zsh

npm install argon2

Prisma Seed の仕組み

これは最近知ったのすが、Prisma には prisma db seed という公式の仕組みがあります。かなり便利です。 package.json にスクリプトを定義し、prisma/seed.ts に投入処理を記述する流れです。

投入対象：Prisma Client を経由してテーブルへ登録
実行タイミング：npx prisma migrate dev 実行後、または手動で npx prisma db seed
特徴：型安全に投入可能、再実行が容易

txt

文字図：Seed投入の流れ

prisma/schema.prisma   ──▶  migrate dev  ──▶  DB作成
                                      │
                                      ▼
                               seed.ts（Prisma Client）
                                      │
                                      ▼
                             初期データ（Role, Menu, User…）

データ投入ファイルの作成

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";
import argon2 from "argon2";

const prisma = new PrismaClient();

// ▼ 小道具：メールの正規化（小文字・trim）
const normalizeEmail = (email: string) => email.trim().toLowerCase();

// ▼ 今回の初期データ（必要に応じて .env 化）
const ORG = {
  accountName: "DELOGs株式会社",
  branchName: "本社",
  departmentName: "システム管理部",
  // 仕様: 15文字以上 / 大文字・小文字・数字を各1以上（推測困難）
  // 例: "Aa2024-Dept-Admin-01"（19文字、要件充足）
  departmentCode: "Aa2024-Dept-Admin-01",
};

const ADMIN = {
  email: "admin@example.com",
  password: "AdminPassword012345", // seed用の仮パスワード（本番は発行フローで都度生成）
  name: "管理者",
  roleCode: "ADMIN",
};

async function main() {
  // =====================================================
  // 1) Role（upsert）
  // =====================================================
  const roles = [
    {
      code: "ADMIN",
      name: "管理者",
      priority: 100,
      canEditData: true,
      canDownloadData: true,
      isSystem: true,
    },
    {
      code: "EDITOR",
      name: "編集者",
      priority: 50,
      canEditData: true,
      canDownloadData: false,
    },
    {
      code: "VIEWER",
      name: "閲覧者",
      priority: 10,
      canEditData: false,
      canDownloadData: false,
    },
  ];
  for (const r of roles) {
    await prisma.role.upsert({
      where: { code: r.code },
      update: {
        name: r.name,
        priority: r.priority,
        canEditData: r.canEditData,
        canDownloadData: r.canDownloadData,
        isSystem: !!r.isSystem,
        isActive: true,
      },
      create: {
        code: r.code,
        name: r.name,
        priority: r.priority,
        canEditData: r.canEditData,
        canDownloadData: r.canDownloadData,
        isSystem: !!r.isSystem,
        isActive: true,
      },
    });
  }

  // =====================================================
  // 2) SubscriptionPlan（upsert）
  // =====================================================
  const plans = [
    { code: "basic", name: "ベーシックプラン", monthlyPrice: 1000 },
    { code: "pro", name: "プロプラン", monthlyPrice: 5000 },
  ];
  for (const p of plans) {
    await prisma.subscriptionPlan.upsert({
      where: { code: p.code },
      update: { name: p.name, monthlyPrice: p.monthlyPrice, isActive: true },
      create: {
        code: p.code,
        name: p.name,
        monthlyPrice: p.monthlyPrice,
        isActive: true,
      },
    });
  }

  // =====================================================
  // 3) SubscriptionStatus（upsert）
  // =====================================================
  const statuses = [
    { code: "active", name: "有効" },
    { code: "expired", name: "期限切れ" },
    { code: "pending", name: "審査中" },
  ];
  for (const s of statuses) {
    await prisma.subscriptionStatus.upsert({
      where: { code: s.code },
      update: { name: s.name, isActive: true },
      create: { code: s.code, name: s.name, isActive: true },
    });
  }

  // =====================================================
  // 4) 組織階層の用意（Account → Branch → Department）
  //     - name は UNIQUE ではないので、findFirst で再利用を試みる
  //     - Department は code UNIQUE を利用して upsert
  // =====================================================
  const account =
    (await prisma.account.findFirst({ where: { name: ORG.accountName } })) ??
    (await prisma.account.create({
      data: { name: ORG.accountName, isActive: true },
    }));

  const branch =
    (await prisma.branch.findFirst({
      where: { name: ORG.branchName, accountId: account.id },
    })) ??
    (await prisma.branch.create({
      data: { name: ORG.branchName, accountId: account.id, isActive: true },
    }));

  const department = await prisma.department.upsert({
    where: { code: ORG.departmentCode }, // UNIQUE
    update: {
      name: ORG.departmentName,
      branchId: branch.id,
      isActive: true,
    },
    create: {
      code: ORG.departmentCode,
      name: ORG.departmentName,
      branchId: branch.id,
      isActive: true,
    },
  });

  // =====================================================
  // 5) Department 契約（Subscription）を 1 件用意（status=active, plan=pro）
  // =====================================================
  const statusActive = await prisma.subscriptionStatus.findUnique({
    where: { code: "active" },
  });
  const planPro = await prisma.subscriptionPlan.findUnique({
    where: { code: "pro" },
  });

  if (!statusActive || !planPro) {
    throw new Error(
      "SubscriptionStatus(active) または SubscriptionPlan(pro) が見つかりません。",
    );
  }

  // 既に部署に active の購読が存在するなら作成スキップ（簡易判定）
  const existingSub = await prisma.subscription.findFirst({
    where: { departmentId: department.id, statusId: statusActive.id },
  });

  if (!existingSub) {
    await prisma.subscription.create({
      data: {
        departmentId: department.id,
        statusId: statusActive.id,
        planId: planPro.id,
        startDate: new Date(),
        isActive: true,
      },
    });
  }

  // =====================================================
  // 6) 初期管理ユーザー（argon2 でハッシュ）
  //     - ログインは Department.code + User.email + password
  // =====================================================
  const adminRole = await prisma.role.findUnique({
    where: { code: ADMIN.roleCode },
  });
  if (!adminRole) throw new Error("ADMIN ロールが見つかりません。");

  const adminEmail = normalizeEmail(ADMIN.email);
  const adminHash = await argon2.hash(ADMIN.password);

  // 部署内メール一意（@@unique([departmentId, email])）で upsert
  // Prisma の upsert は複合Uniqueには直接使えないため、事前検索 → update or create の流れにします
  const existingAdmin = await prisma.user.findFirst({
    where: { departmentId: department.id, email: adminEmail },
  });

  if (existingAdmin) {
    await prisma.user.update({
      where: { id: existingAdmin.id },
      data: {
        name: ADMIN.name,
        roleId: adminRole.id,
        hashedPassword: adminHash,
        isActive: true,
      },
    });
  } else {
    await prisma.user.create({
      data: {
        departmentId: department.id,
        roleId: adminRole.id,
        email: adminEmail,
        hashedPassword: adminHash,
        name: ADMIN.name,
        isActive: true,
      },
    });
  }

  // （任意）Menu は UI 実装側の要件に合わせて別 Seed で投入すると管理しやすいです
}

main()
  .then(async () => {
    console.log("Seeding completed.");
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error("Seeding failed:", e);
    await prisma.$disconnect();
    process.exit(1);
  });

上記スクリプトでは、以下の流れでデータを投入します。

Role（ロールマスタ）の作成 ── ADMIN / EDITOR / VIEWER を投入
SubscriptionPlan / SubscriptionStatus の作成 ── 契約マスタを登録
最小の Account / Branch / Department を作成
部署に紐づく「管理者ユーザー」を作成し、ログイン可能に設定

package.jsonでパスを通す

まず、tsx で seedコマンドが実行できるようにします。

zsh

npm i -D tsx typescript @types/node

次に、 プロジェクト直下 のpackage.jsonに下記を追記します。

json

  "prisma": {
    "seed": "tsx prisma/seed.ts"
  },

どこに記載してもよいのですが、下記のようにscripts{}の下に追記しました。

json

  "scripts": {
    "dev": "next dev",
    "build": "next build",
    "start": "next start",
    "lint": "next lint"
  },
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  },
  "dependencies": {
    "@hookform/resolvers": "^5.2.1",

データ投入を実行

ここまで完了したら、下記のコマンドでデータを投入します。

zsh

npx prisma db seed

実行時のワーニングについて
上記のコマンドを実行すると、warn The configuration property package.json#prismais deprecated and will be removed in Prisma 7. Please migrate to a Prisma config file (e.g.,prisma.config.ts).というワーニングが表示されます。これは Prisma 7 以降で package.json 内の prisma.seed 設定が廃止予定 であるために表示されるものです。現時点（Prisma 6 系）では従来の書き方で問題なく動作しますが、将来を見据えて prisma.config.ts へ移行することが推奨されています。

 // prisma.config.ts
 import { defineConfig } from "@prisma/config";
 
 export default defineConfig({
  seed: {
    command: "tsx prisma/seed.ts",
  },
});

npx prisma studioで各テーブルを参照すると下図のようにデータが投入されていることが確認できると思います。

運用上の工夫

初期データ投入は一度で終わりではなく、運用の中で再投入・追加が必要になる場合があります。
以下の工夫を取り入れると運用が楽になります。

工夫	内容	メリット
idempotent 設計	`upsert` を利用して「存在すれば更新、なければ作成」	Seed を何度でも実行可能
複数ファイル化	`prisma/seed/roles.ts` のように分割	責務分離・保守性向上
環境別Seed	dev/prod で投入データを分岐	本番は最小マスタのみ投入、開発はダミーデータ多数投入
faker利用	`@faker-js/faker` を使いダミーユーザー生成	E2Eテストや画面確認に便利

これらを組み合わせることで、 Seedは「初期投入スクリプト」ではなく「開発・運用の基盤」 として活用できます。まだ、使いこなせていないですが、しっかり勉強していきたいと思います。

Seedのまとめ

「Seed」はデータベースに投入する 初期データの仕組み を指す
マスタデータ（Role, Menu, Plan, Status）と最小限の管理者ユーザーを投入することが重要
PrismaのSeed機能を使うことで、型安全・再実行可能・環境別に柔軟な投入ができる
運用の工夫として upsert やファイル分割を活用する

12. まとめと次回予告

本稿では、管理画面フォーマットのデータ基盤として Prisma × PostgreSQL によるスキーマ設計を固め、displayId の自動採番、インデックス設計、そして 初期データ投入（Seed） までを整備しました。
Seed は「開発環境を即座に再現できるための装置」であり、upsert による冪等性・環境別の分岐・メニューの分割投入などを取り入れることで、運用の手戻りを抑えられます。

⚠️ Prisma 7 以降では package.json#prisma.seed が非推奨となるため、将来的に prisma.config.ts へ移行します（本稿の手順でも現状は問題なく動作します）。

次回は ログイン機能 を実装します。
department.code + email + password を入力とし、argon2 によるハッシュ照合、セッション管理、レート制限・ロックアウトの導入までを段階的に解説します。UI と RBAC（Role.priority × Menu.minPriority）の接続まで含めて、**「ログインして実際の画面が動く」**ところまで到達します。

参考文献

本章では、本記事の内容を補強するために参照した外部資料をまとめます。
Prisma、argon2、TypeScript など、実装に直結する公式ドキュメントを中心に整理しました。

Prisma 関連

Prisma を利用する上で参照すべき公式ドキュメントや議論スレッドです。
マイグレーションや Seed 戦略に関して、基本とベストプラクティスを確認できます。

分類	リンク
Prisma Migrate	Prisma 公式ドキュメント - Prisma Migrate
Prisma Seeding	Prisma 公式ドキュメント - Seeding データベース
ベストプラクティス	Prisma GitHub Discussions - Seeding best practices

セキュリティ関連

ユーザーパスワードの保存に利用する argon2 に関する参考資料です。
アルゴリズムの特性や利用方法を確認する際に役立ちます。

argon2 パッケージ (npm)

TypeScript 関連

Prisma や Node.js 環境で型安全にコードを書くための基礎資料です。
特に「Declaration Merging」に関する知識はプロジェクトの拡張に有効です。

TypeScript Handbook - Declaration Merging

この記事の執筆・編集担当

松本孝太郎

DELOGs編集部/中年新米プログラマー

ここ数年はReact&MUIのフロントエンドエンジニアって感じでしたが、Next.jsを学んで少しずつできることが広がりつつあります。その実践記録をできるだけ共有していければと思っています。

この記事の更新履歴

2025/11/6
デモ環境とGithubリポジトリについての記述を追加
2025/9/12
リードレプリカを扱うために@prisma/extension-read-replicasのインストールについて追記
2025/9/11
Prismaスキーマの各テーブルモデルについて、DateTime型のカラムは@db.Timestamptzをつけるように修正。PostgreSQLなら、あえて設定しなくてもUTC時間になりますが、念のため。
2025/9/10
初回公開

管理画面で受け付けたパスワード再発行依頼を、Server Action・Shadcn/uiのデータテーブル・メール送信を組み合わせて運用可能なワークフローに統合

2025/10/15公開

[管理画面フォーマット開発編 #11] パスワード再発行依頼とメールテンプレート統合のイメージ

[管理画面フォーマット開発編 #10] メニュー管理UIをDB連携する

グローバルで一貫したMenuテーブルを保ちながら、部署ごとにメニュー表示をカスタマイズ

2025/10/12公開

[管理画面フォーマット開発編 #10] メニュー管理UIをDB連携するのイメージ

[管理画面フォーマット開発編 #9 後編] 部署別ロール対応 ─ プロフィール管理の改修

DepartmentRole導入に伴い、プロフィール管理で「実効ロール」を参照するように修正と一部ついでの変更

2025/10/8公開

[管理画面フォーマット開発編 #9 前編] 部署別ロール対応 ─ ユーザ管理の改修

DepartmentRole導入に伴い、ユーザ管理で「実効ロール」を参照するように修正

2025/10/5公開

[管理画面フォーマット開発編 #9 前編] 部署別ロール対応 ─ ユーザ管理の改修のイメージ

[管理画面フォーマット開発編 #8 後編] 部署別ロール ─ 管理UIとServer Action実装

部署ごとのロールを実際に操作できるように、Server Actionと管理画面UIを構築

2025/10/2公開

管理画面フォーマット開発編 #1Prisma × PostgreSQLで進めるDB設計

0. はじめに

本記事の位置づけ

本記事のゴール

前提環境と採用技術

管理画面フォーマット開発編の完成形（デモとGithubリポジトリ）

Prismaの準備

DB作成

Prismaのインストール

Prismaの初期化

.envの設定

1. 設計原則 ─ 命名・共通カラム・論理削除

命名規則

共通カラム

論理削除の扱い

2. displayId 仕様

基本仕様

対象テーブルと接頭辞一覧

採番の仕組み

運用上の留意点

3. ER概観 ─ マルチテナントとスコープ

全体像

主体テーブルと関係性

簡易版ER図

スコープの考え方

4. Account系 正規化設計

Account（会社マスタ）

Branch（支店）

Department（部署）

Contact（担当者）

Subscription（契約：部署単位）

SubscriptionStatus（契約ステータス・マスタ）

SubscriptionPlan（契約プラン・マスタ）

Account系の関係図

設計のポイント

5. User 設計（US）

User（ユーザー）

制約・運用ポイント

User の関係図

認証フロー（要旨）

権限判定の位置づけ

インデックス指針

6. Role 設計（RL）

Role（ロール）

権限設計の考え方

Role の関係図

設計のポイント

7. Menu（MN）の設計

Menu（メニュー本体）

可視制御の仕組み

Menu の関係図

設計のポイント

8. インデックス・ユニーク制約設計

基本方針

ユニークキー設計（横断）

外部キー・参照整合性

テーブル別インデックス

Account / Branch / Department（組織階層）

Contact

Subscription / SubscriptionStatus / SubscriptionPlan

User

Role

Menu

パフォーマンス運用の要点

9. Prismaスキーマの作成（モデル一覧）

schema.prismaへの追記

スキーマのポイント（実装メモ）

10. Prismaマイグレーションと migration.sql 編集

マイグレーションファイルの生成

共通関数の追加

シーケンス設定の追加

マイグレーションの実行

11. データ投入戦略（Seed戦略）

データ投入の目的

パスワード暗号化の準備

Prisma Seed の仕組み

データ投入ファイルの作成

package.jsonでパスを通す

データ投入を実行

運用上の工夫

管理画面フォーマット開発編 #1
Prisma × PostgreSQLで進めるDB設計

4. Account系正規化設計

`schema.prisma`への追記