PRO:PER db erd
Tables (9)
kpi_records
- id — bigint — Primary key — NOT NULL
- kpi_id — bigint — Foreign key → kpis.id — NOT NULL
- measured_date — date — NOT NULL
- value — decimal(15, 4) — NOT NULL — 측정일의 KPI 실적값
- evidence_note — text — NOT NULL — 산출 근거 또는 출처 설명
- created_by — bigint — Foreign key → users.id — NOT NULL — 실적을 입력한 팀 관리자
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
kpis
- id — bigint — Primary key — NOT NULL
- project_id — bigint — Foreign key → projects.id — NOT NULL — 프로젝트당 대표 KPI 한 개
- name — varchar(120) — NOT NULL — 예: 평균 첫 답변 시간
- unit — varchar(30) — NOT NULL — 예: 시간, %, 회
- direction — ENUM('INCREASE', 'DECREASE') — NOT NULL
- measurement_method — text — NOT NULL
- baseline_date — date — NOT NULL — 기준값을 측정한 날짜
- baseline_value — decimal(15, 4) — NOT NULL
- target_value — decimal(15, 4) — NOT NULL
- due_date — date — NOT NULL
- data_source — varchar(255) — NOT NULL — 실적값의 출처
- update_cycle — varchar(50) — NOT NULL — 예: 매주 월요일
- created_by — bigint — Foreign key → users.id — NOT NULL
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
organizations
- id — bigint — Primary key — NOT NULL
- name — varchar(100) — NOT NULL — 기업명
- status — ENUM('ACTIVE', 'INACTIVE') — NOT NULL
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
project_goal_links
- id — bigint — Primary key — NOT NULL
- project_id — bigint — Foreign key → projects.id — NOT NULL
- goal_id — bigint — Foreign key → team_goals.id — NOT NULL
- contribution_description — text — NOT NULL — 이 프로젝트가 해당 팀 목표에 기여하는 방식
- created_by — bigint — Foreign key → users.id — NOT NULL — 목표를 연결한 팀 관리자
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
projects
- id — bigint — Primary key — NOT NULL
- team_id — bigint — Foreign key → teams.id — NOT NULL — 프로젝트를 운영하는 팀
- owner_id — bigint — Foreign key → users.id — NOT NULL — 프로젝트 담당자
- title — varchar(200) — NOT NULL
- description — text
- start_date — date — NOT NULL
- end_date — date — NOT NULL
- status — ENUM('PLANNED', 'IN_PROGRESS', 'ON_HOLD', 'DONE') — NOT NULL
- created_by — bigint — Foreign key → users.id — NOT NULL
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
tasks
- id — bigint — Primary key — NOT NULL
- project_id — bigint — Foreign key → projects.id — NOT NULL
- assignee_id — bigint — Foreign key → users.id — NOT NULL
- created_by — bigint — Foreign key → users.id — NOT NULL
- title — varchar(200) — NOT NULL
- due_date — date — NOT NULL
- priority — ENUM('HIGH', 'MEDIUM', 'LOW') — NOT NULL
- status — ENUM('TODO', 'IN_PROGRESS', 'DONE') — NOT NULL
- contribution_note — text — NOT NULL — 상위 목표·프로젝트에 기여하는 이유
- progress_note — text — 담당자의 최신 진행 내용 또는 어려움
- completed_at — timestamp — DONE 전환 시 기록하며 업무를 다시 열면 NULL로 변경
- last_adjusted_by — bigint — Foreign key → users.id — 최근 계획을 조정한 팀 관리자
- last_adjustment_reason — text — 최근 담당자·기한·우선순위 조정 사유
- last_adjusted_at — timestamp
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
team_goals
- id — bigint — Primary key — NOT NULL
- team_id — bigint — Foreign key → teams.id — NOT NULL
- title — varchar(200) — NOT NULL
- description — text
- start_date — date — NOT NULL
- end_date — date — NOT NULL
- created_by — bigint — Foreign key → users.id — NOT NULL
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
teams
- id — bigint — Primary key — NOT NULL
- organization_id — bigint — Foreign key → organizations.id — NOT NULL — 소속 기업
- name — varchar(100) — NOT NULL — 팀명
- description — varchar(500) — 팀 역할 또는 소개
- manager_id — bigint — Foreign key → users.id — 팀 관리자. 팀 생성 직후에는 비어 있을 수 있음
- status — ENUM('ACTIVE', 'INACTIVE') — NOT NULL
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL
users
- id — bigint — Primary key — NOT NULL
- organization_id — bigint — Foreign key → organizations.id — NOT NULL
- team_id — bigint — Foreign key → teams.id — 시스템 관리자 또는 미배정 사용자는 NULL
- email — varchar(255) — NOT NULL
- password_hash — varchar(255) — NOT NULL — 원문 비밀번호 저장 금지
- name — varchar(60) — NOT NULL
- role — ENUM('SYSTEM_ADMIN', 'TEAM_MANAGER', 'MEMBER') — NOT NULL
- status — ENUM('ACTIVE', 'INACTIVE') — NOT NULL
- created_at — timestamp — NOT NULL
- updated_at — timestamp — NOT NULL