일감

테이블 (16)

admins

  • id — UUID
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — TEXT — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

attendance_correction_requests

  • id — UUID
  • attendance_id — UUID — 외래 키 → attendances.id — NOT NULL
  • worker_id — UUID — NOT NULL
  • reason_type — TEXT — NOT NULL
  • requested_check_in_at — TIMESTAMPTZ
  • requested_check_out_at — TIMESTAMPTZ
  • reason — TEXT
  • status — TEXT — NOT NULL
  • reviewed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — ( (status = 'PENDING' AND reviewed_at IS NULL)
  • REJECTED) — AND — NOT NULL

attendances

  • id — UUID
  • schedule_id — UUID — 외래 키 → schedules.id — NOT NULL
  • worker_id — UUID — NOT NULL
  • status — TEXT — NOT NULL
  • checked_in_at — TIMESTAMPTZ
  • checked_out_at — TIMESTAMPTZ
  • is_late — BOOLEAN — NOT NULL
  • overtime_requested — BOOLEAN — NOT NULL
  • overtime_reason — TEXT
  • requested_overtime_start_at — TIMESTAMPTZ
  • requested_overtime_end_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

audit_logs

  • id — UUID
  • action_type — TEXT — NOT NULL
  • actor_id — UUID — 외래 키 → admins.id
  • target_id — UUID — NOT NULL
  • target_type — TEXT — NOT NULL
  • payload — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL

fixed_schedule_patterns

  • id — UUID
  • worker_id — UUID — 외래 키 → workers.id — NOT NULL
  • day_of_week — SMALLINT — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • break_start — TIME
  • break_end — TIME
  • apply_from — DATE — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • CHECK — ( (break_start IS NULL AND break_end IS NULL) — NOT NULL

minimum_staffing_rules

  • id — UUID
  • organization_id — UUID — 외래 키 → organizations.id — NOT NULL
  • label — TEXT — NOT NULL
  • day_of_weeks — SMALLINT — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • min_count — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — (end_time > start_time)

organization_closures

  • id — UUID
  • organization_id — UUID — 외래 키 → organizations.id — NOT NULL
  • label — TEXT — NOT NULL
  • closure_type — TEXT — NOT NULL
  • day_of_weeks — SMALLINT
  • closure_date — DATE
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — ( (closure_type IN ('weekly', 'biweekly') — NOT NULL

organizations

  • id — UUID
  • admin_id — UUID — 외래 키 → admins.id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • business_number — CHAR(10) — NOT NULL
  • address — TEXT — NOT NULL
  • latitude — DECIMAL(10,8) — NOT NULL
  • longitude — DECIMAL(11,8) — NOT NULL
  • gps_radius_meters — INT — NOT NULL
  • work_type — TEXT — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

pay_policies

  • id — UUID
  • organization_id — UUID — 외래 키 → organizations.id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • pay_type — TEXT — NOT NULL
  • hourly_rate — DECIMAL(12,2)
  • daily_rate — DECIMAL(12,2)
  • monthly_rate — DECIMAL(12,2)
  • tax_type — TEXT — NOT NULL
  • night_pay_rate — DECIMAL(4,2) — NOT NULL
  • overtime_pay_rate — DECIMAL(4,2) — NOT NULL
  • period_start_day — INT — NOT NULL
  • period_end_day — INT — NOT NULL
  • pay_day — INT — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — ( (pay_type = 'hourly' AND hourly_rate IS NOT NULL AND daily_rate IS NULL AND monthly_rate IS NULL) — NOT NULL

payroll_extra_items

  • id — UUID
  • payroll_statement_id — UUID — 외래 키 → payroll_statements.id — NOT NULL
  • label — TEXT — NOT NULL
  • item_type — TEXT — NOT NULL
  • amount — DECIMAL(12,2) — NOT NULL

payroll_statements

  • id — UUID
  • worker_id — UUID — 외래 키 → workers.id — NOT NULL
  • organization_id — UUID — NOT NULL
  • pay_policy_snapshot — JSONB — NOT NULL
  • period_start — DATE — NOT NULL
  • period_end — DATE — NOT NULL
  • pay_date — DATE — NOT NULL
  • status — TEXT — NOT NULL
  • signed — BOOLEAN — NOT NULL
  • signed_at — TIMESTAMPTZ
  • base_pay — DECIMAL(12,2) — NOT NULL
  • weekly_holiday_pay — DECIMAL(12,2) — NOT NULL
  • night_shift_pay — DECIMAL(12,2) — NOT NULL
  • overtime_pay — DECIMAL(12,2) — NOT NULL
  • income_tax — DECIMAL(12,2) — NOT NULL
  • local_income_tax — DECIMAL(12,2) — NOT NULL
  • national_pension — DECIMAL(12,2) — NOT NULL
  • health_insurance — DECIMAL(12,2) — NOT NULL
  • long_term_care_insurance — DECIMAL(12,2) — NOT NULL
  • employment_insurance — DECIMAL(12,2) — NOT NULL
  • total_deduction — DECIMAL(12,2) — NOT NULL
  • net_pay — DECIMAL(12,2) — NOT NULL
  • issued_at — TIMESTAMPTZ
  • calculated_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — (period_end > period_start)
  • CHECK — ( (status IN ('calculating', 'calculated') — NOT NULL
  • CHECK — ( (signed = FALSE AND signed_at IS NULL) — NOT NULL

schedule_cycles

  • id — UUID
  • organization_id — UUID — 외래 키 → organizations.id — NOT NULL
  • work_start_date — DATE — NOT NULL
  • work_end_date — DATE — NOT NULL
  • submit_start_date — DATE — NOT NULL
  • submit_end_date — DATE — NOT NULL
  • confirm_end_date — DATE — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • CHECK — (work_end_date > work_start_date)
  • CHECK — (submit_end_date > submit_start_date)
  • CHECK — (submit_end_date < work_start_date)
  • CHECK — (confirm_end_date >= submit_end_date)
  • CHECK — (confirm_end_date < work_start_date)

schedules

  • id — UUID
  • worker_id — UUID — 외래 키 → workers.id — NOT NULL
  • organization_id — UUID — NOT NULL
  • schedule_cycle_id — UUID
  • date — DATE — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • ends_next_day — BOOLEAN — NOT NULL
  • break_start — TIME
  • break_end — TIME
  • status — TEXT — NOT NULL
  • published_at — TIMESTAMPTZ
  • cancelled_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — ( (break_start IS NULL AND break_end IS NULL) — NOT NULL
  • CHECK — ( (status = 'DRAFT' AND published_at IS NULL AND cancelled_at IS NULL) — NOT NULL

unavailability_time_slots

  • id — UUID
  • work_unavailability_request_id — UUID — 외래 키 → work_unavailability_requests.id — NOT NULL
  • unavailable_start — TIME — NOT NULL
  • unavailable_end — TIME — NOT NULL
  • CHECK — (unavailable_end > unavailable_start)

work_unavailability_requests

  • id — UUID
  • worker_id — UUID — 외래 키 → workers.id — NOT NULL
  • schedule_cycle_id — UUID — NOT NULL
  • date — DATE — NOT NULL
  • status — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

workers

  • id — UUID
  • organization_id — UUID — 외래 키 → organizations.id — NOT NULL
  • pay_policy_id — UUID
  • name — VARCHAR(50) — NOT NULL
  • employee_number — VARCHAR(20) — NOT NULL
  • schedule_type — TEXT — NOT NULL
  • phone — VARCHAR(20)
  • joined_at — DATE — NOT NULL
  • resigned_at — DATE
  • oauth_provider — TEXT
  • oauth_provider_id — TEXT
  • push_token — TEXT
  • deleted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • CHECK — ( (oauth_provider IS NULL AND oauth_provider_id IS NULL) — NOT NULL