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