テーブル定義書 - Issue駆動開発業務委託プラットフォーム

概要

PostgreSQL を前提としたテーブル定義書。全テーブルで UUID v7 を主キーとして採用し、時系列ソートに対応する。タイムスタンプは全て timestamptz（タイムゾーン付き）で管理する。

1. projects テーブル

GitLabプロジェクトと1:1で紐付くプロジェクト情報。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	プロジェクトID
name	varchar(255)	NOT NULL	プロジェクト名
description	text		プロジェクト説明
gitlab_project_id	integer	NOT NULL, UNIQUE	GitLabプロジェクトID
gitlab_project_url	varchar(512)		GitLabプロジェクトURL
budget_limit	numeric(12,2)	NOT NULL, DEFAULT 0	予算上限（円）
start_date	date	NOT NULL	プロジェクト開始日
end_date	date		プロジェクト終了予定日
status	varchar(20)	NOT NULL, DEFAULT 'draft'	ステータス（draft/active/completed/suspended）
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時
updated_at	timestamptz	NOT NULL, DEFAULT now()	更新日時

CHECK制約:

status IN ('draft', 'active', 'completed', 'suspended')
budget_limit >= 0
end_date IS NULL OR end_date >= start_date

インデックス:

idx_projects_gitlab_project_id ON (gitlab_project_id) -- GitLab連携検索
idx_projects_status ON (status) -- ステータス絞り込み

2. users テーブル

プラットフォーム利用者。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	ユーザーID
email	varchar(255)	NOT NULL, UNIQUE	メールアドレス
name	varchar(255)	NOT NULL	表示名
role	varchar(20)	NOT NULL	プラットフォームロール（pm/reviewer/contractor）
gitlab_user_id	integer	UNIQUE	GitLabユーザーID
skill_set	jsonb	DEFAULT '[]'::jsonb	スキルセット（例: ["Python", "TypeScript", "AWS"]）
is_active	boolean	NOT NULL, DEFAULT true	アクティブフラグ
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時
updated_at	timestamptz	NOT NULL, DEFAULT now()	更新日時

CHECK制約:

role IN ('pm', 'reviewer', 'contractor')

インデックス:

idx_users_email ON (email) -- メールアドレス検索
idx_users_gitlab_user_id ON (gitlab_user_id) WHERE gitlab_user_id IS NOT NULL -- GitLab連携検索
idx_users_role ON (role) -- ロール別検索
idx_users_skill_set USING GIN ON (skill_set) -- スキル検索

3. project_members テーブル

プロジェクトとユーザーの関連（多対多）。プロジェクト内でのロールを保持。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	メンバーID
project_id	uuid	NOT NULL, FK -> projects(id)	プロジェクトID
user_id	uuid	NOT NULL, FK -> users(id)	ユーザーID
role	varchar(20)	NOT NULL	プロジェクト内ロール（pm/reviewer/contractor）
joined_at	timestamptz	NOT NULL, DEFAULT now()	参加日時
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時

UNIQUE制約:

uq_project_members_project_user ON (project_id, user_id) -- 同一プロジェクトに同一ユーザーは1レコード

CHECK制約:

role IN ('pm', 'reviewer', 'contractor')

インデックス:

idx_project_members_project_id ON (project_id)
idx_project_members_user_id ON (user_id)

4. issues テーブル

GitLab Issueと紐付く課題情報。報酬額・難易度・見積工数を管理。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	Issue ID
project_id	uuid	NOT NULL, FK -> projects(id)	所属プロジェクトID
title	varchar(500)	NOT NULL	Issue タイトル
description	text		Issue 説明
gitlab_issue_id	integer	NOT NULL	GitLab Issue ID（グローバル）
gitlab_issue_iid	integer	NOT NULL	GitLab Issue IID（プロジェクト内連番）
gitlab_issue_url	varchar(512)		GitLab Issue URL
reward_amount	numeric(10,2)	NOT NULL, DEFAULT 0	報酬額（円）
difficulty	varchar(10)	NOT NULL, DEFAULT 'medium'	難易度（easy/medium/hard）
estimated_hours	numeric(5,1)		見積工数（時間）
status	varchar(20)	NOT NULL, DEFAULT 'open'	ステータス
required_skills	jsonb	DEFAULT '[]'::jsonb	必要スキル
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時
updated_at	timestamptz	NOT NULL, DEFAULT now()	更新日時

UNIQUE制約:

uq_issues_project_gitlab ON (project_id, gitlab_issue_iid) -- プロジェクト内でGitLab Issue IIDは一意

CHECK制約:

difficulty IN ('easy', 'medium', 'hard')
status IN ('open', 'assigned', 'in_progress', 'in_review', 'done', 'rejected')
reward_amount >= 0
estimated_hours IS NULL OR estimated_hours > 0

インデックス:

idx_issues_project_id ON (project_id)
idx_issues_status ON (status) -- ステータス絞り込み（カンバン表示）
idx_issues_project_status ON (project_id, status) -- プロジェクト内ステータス絞り込み
idx_issues_difficulty ON (difficulty) -- 難易度フィルタ
idx_issues_required_skills USING GIN ON (required_skills) -- スキルマッチング

5. assignments テーブル

IssueへのContractorアサイン情報。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	アサインID
issue_id	uuid	NOT NULL, FK -> issues(id)	Issue ID
contractor_id	uuid	NOT NULL, FK -> users(id)	担当Contractor ID
started_at	timestamptz		着手日時
deadline	timestamptz		期限
completed_at	timestamptz		完了日時
status	varchar(30)	NOT NULL, DEFAULT 'assigned'	ステータス
mr_url	varchar(512)		Merge Request URL
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時
updated_at	timestamptz	NOT NULL, DEFAULT now()	更新日時

CHECK制約:

status IN ('assigned', 'in_progress', 'submitted', 'completed', 'revision_requested', 'cancelled')
completed_at IS NULL OR completed_at >= started_at

インデックス:

idx_assignments_issue_id ON (issue_id)
idx_assignments_contractor_id ON (contractor_id)
idx_assignments_status ON (status)
idx_assignments_contractor_status ON (contractor_id, status) -- Contractor別稼働状況
idx_assignments_deadline ON (deadline) WHERE deadline IS NOT NULL AND status NOT IN ('completed', 'cancelled') -- ETA予測用

6. reviews テーブル

レビュー結果の記録。同一Assignmentに対して複数回のイテレーションが発生しうる。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	レビューID
assignment_id	uuid	NOT NULL, FK -> assignments(id)	アサインID
reviewer_id	uuid	NOT NULL, FK -> users(id)	レビュワーID
result	varchar(20)	NOT NULL	レビュー結果（approved/changes_requested）
comment	text		レビューコメント
iteration	integer	NOT NULL, DEFAULT 1	イテレーション番号
reviewed_at	timestamptz	NOT NULL, DEFAULT now()	レビュー日時
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時

CHECK制約:

result IN ('approved', 'changes_requested')
iteration >= 1

インデックス:

idx_reviews_assignment_id ON (assignment_id)
idx_reviews_reviewer_id ON (reviewer_id)
idx_reviews_assignment_iteration ON (assignment_id, iteration) -- イテレーション順取得

7. rewards テーブル

報酬の確定・支払い管理。Assignmentと1:1。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	報酬ID
assignment_id	uuid	NOT NULL, UNIQUE, FK -> assignments(id)	アサインID（1:1）
amount	numeric(10,2)	NOT NULL	報酬額（円）
status	varchar(20)	NOT NULL, DEFAULT 'pending'	ステータス（pending/confirmed/paid）
confirmed_date	date		確定日
payment_month	varchar(7)		支払い月（YYYY-MM形式）
note	text		備考
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時
updated_at	timestamptz	NOT NULL, DEFAULT now()	更新日時

CHECK制約:

status IN ('pending', 'confirmed', 'paid')
amount >= 0
payment_month IS NULL OR payment_month ~ '^\d{4}-(0[1-9]|1[0-2])$'

インデックス:

idx_rewards_assignment_id ON (assignment_id) -- UNIQUE制約でカバー
idx_rewards_status ON (status) -- ステータス絞り込み
idx_rewards_payment_month ON (payment_month) WHERE payment_month IS NOT NULL -- 支払い月集計
idx_rewards_status_payment ON (status, payment_month) -- 支払い確定金額一覧

8. budget_snapshots テーブル

プロジェクト予算の月次スナップショット。マネージャーダッシュボード用。

カラム名	型	制約	説明
id	uuid	PK, DEFAULT gen_random_uuid()	スナップショットID
project_id	uuid	NOT NULL, FK -> projects(id)	プロジェクトID
snapshot_month	varchar(7)	NOT NULL	スナップショット月（YYYY-MM形式）
budget_limit	numeric(12,2)	NOT NULL	その時点の予算上限
consumed_amount	numeric(12,2)	NOT NULL, DEFAULT 0	消化額（confirmed + paid）
pending_amount	numeric(12,2)	NOT NULL, DEFAULT 0	保留額（pending）
remaining_amount	numeric(12,2)	NOT NULL	残予算
consumption_rate	numeric(5,2)	NOT NULL, DEFAULT 0	消化率（%）
created_at	timestamptz	NOT NULL, DEFAULT now()	作成日時

UNIQUE制約:

uq_budget_snapshots_project_month ON (project_id, snapshot_month)

CHECK制約:

snapshot_month ~ '^\d{4}-(0[1-9]|1[0-2])$'
consumption_rate >= 0 AND consumption_rate <= 100

インデックス:

idx_budget_snapshots_project_id ON (project_id)
idx_budget_snapshots_project_month ON (project_id, snapshot_month) -- UNIQUE制約でカバー
idx_budget_snapshots_snapshot_month ON (snapshot_month) -- 月次横断集計

共通設計方針

updated_at の自動更新トリガー

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

以下のテーブルに適用:

projects
users
issues
assignments
rewards

sql

CREATE TRIGGER trg_projects_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 他テーブルも同様

論理削除

本システムでは論理削除は採用せず、ステータス管理で対応する。例: プロジェクトの無効化は status = 'suspended'、ユーザーの無効化は is_active = false。

タイムゾーン

全ての timestamptz カラムはUTCで保存し、アプリケーション層で Asia/Tokyo に変換する。

JSON カラム

skill_set と required_skills は JSONB 型で配列として格納する。 GINインデックスにより @> 演算子でのスキルマッチングクエリを高速化する。

sql

-- スキルマッチング例
SELECT i.* FROM issues i
WHERE i.required_skills @> '["Python"]'::jsonb
  AND i.status = 'open';

テーブル定義書 - Issue駆動開発 業務委託プラットフォーム ​

概要 ​

1. projects テーブル ​

2. users テーブル ​

3. project_members テーブル ​

4. issues テーブル ​

5. assignments テーブル ​

6. reviews テーブル ​

7. rewards テーブル ​

8. budget_snapshots テーブル ​

共通設計方針 ​

updated_at の自動更新トリガー ​

論理削除 ​

タイムゾーン ​

JSON カラム ​

テーブル定義書 - Issue駆動開発業務委託プラットフォーム

概要

1. projects テーブル

2. users テーブル

3. project_members テーブル

4. issues テーブル

5. assignments テーブル

6. reviews テーブル

7. rewards テーブル

8. budget_snapshots テーブル

共通設計方針

updated_at の自動更新トリガー

論理削除

タイムゾーン

JSON カラム