Payroll Schema Refactor

Target database schema for representing all payroll-adjacent business terms (base salary, hourly rate, working hours, overtime, incentives, settlements, other allowances, other deductions, etc.) without duplicating calculated amounts across entities.

This document is schema-design only. Sheet importers will be reconciled in a separate pass once the schema stabilizes.

The recipe layer lives on sheet column metadata (item code, source module, commit target) plus an org-wide parameter store. AllowanceRecord / DeductionRecord are sheet-rooted (lineage triple sourceSheetId / sourceSheetRowId / sourceSheetColumnKey is NOT NULL) and exist only as the output of a sheet's "✓ 커밋" (commit) action. See Payroll Provenance and Calculators for the current flow.

---

1. Principle: Three Layers, One Direction

┌─────────────────────────────────────────────────────────────────┐
│  Layer 1 — SOURCE OBSERVATIONS  (what happened, no money)       │
│  Attendance, OvertimeRecord, LeaveRequest, FixedIncentive,      │
│  SportsIncentive, EducationSubsidy, ResignationRecord,          │
│  YearEndSettlement, WardDailyRecord, (new) TreatmentEvent,      │
│  (new) OnCallShift                                              │
└────────────────────────┬────────────────────────────────────────┘
                         │ feeds calculators
┌────────────────────────▼────────────────────────────────────────┐
│  Layer 2 — RECIPES  (how to price the observations)             │
│  Sheet column metadata + org-wide parameter store,             │
│  TaxBracketConfig, SalaryGrade, EmploymentContract             │
└────────────────────────┬────────────────────────────────────────┘
                         │ sheet commit (✓ 커밋)
┌────────────────────────▼────────────────────────────────────────┐
│  Layer 3 — ATOMIC LEDGER  (the money)                           │
│  AllowanceRecord, DeductionRecord ───► PayrollRecord (header)   │
└─────────────────────────────────────────────────────────────────┘

Rules:

1. A monetary amount lives on exactly one entity: AllowanceRecord or DeductionRecord.
2. Source entities (Layer 1) hold raw observations only — dates, hours, counts, IDs, status.
3. Rates, multipliers, and unit prices live on recipes (Layer 2) — sheet column metadata + the org parameter store. Never on source entities, never on the ledger.
4. Calculators read Layer 1 + Layer 2 → write Layer 3 on sheet commit. Data flows downward only.
5. Derived values (hourly rate from base salary, remaining annual leave, totals) are computed on read and never stored.

---

2. Fields to Remove from Source Entities

Entity	Fields to remove	Why
OvertimeRecord	hourlyRate, multiplier, calculatedPay	Recomputable from hours × Contract.baseSalary/209 × the multiplier on the sheet column / org parameter store
OvertimeStatus enum	PAID value	Paid lifecycle belongs on AllowanceRecord.status
SportsIncentive	unitPrice, patientCount, amount	Recipe → sheet column metadata; outcome → AllowanceRecord
EducationSubsidy	courseAmount	Outcome → AllowanceRecord
LeaveRequest	totalDays	Derived from startDate / endDate / isHalfDay
ResignationRecord	settlementAmount, healthInsuranceRecon, yearEndRecon, annualLeavePayout, unusedLeaveDays, totalSettlement	Each is its own ledger line
YearEndSettlement	adjustmentAmount, installmentMonths, installmentAmount	Adjustment → DeductionRecord; installment plan → sheet column metadata (§3)

After removal, each source entity holds only: employeeId, period keys (year/month or date), the qualifying business attributes (category, type, status, approvedBy/At), and append-only versioning fields (chainId, version, supersededAt).

---

3. Fields to Add

3.1 Atomic ledger — provenance + audit snapshot

Both AllowanceRecord and DeductionRecord gain:

Field	Type	Purpose
sourceType	enum (PayrollSourceType)	Which source module generated this row
sourceId	uuid, nullable	Specific source row id (null for aggregated sources)
inputSnapshot	jsonb	Frozen calculator inputs: { hours, hourlyRate, multiplier, unitPrice, count, ... }
formulaVersion	string	Recipe version at calc time, for replay

The inputSnapshot makes every amount auditable without joining back to source tables (which may have been versioned away).

3.2 Recipes — installment plans, sub-categorization

Installment plans and sub-categorization are modeled on sheet column metadata (with values backed by the org-wide parameter store):

Field	Type	Purpose
splitMonths	int, nullable	If set, generated amount is spread equally across N months
splitFromYearMonth	string, nullable	First month of the split window
subCategory	enum, nullable	Reporting filter: structural / celebration / welfare_loan / voluntary / legacy

splitMonths replaces YearEndSettlement.installmentMonths/installmentAmount. subCategory distinguishes 생일·1주년·이달의직원 from structural allowances, and 기숙사·학자금 from voluntary deductions.

---

4. Business Term → Schema Mapping

Business term	Layer	Target
기본급 (base salary)	Recipe + Ledger	Stored on EmploymentContract.baseSalary and SalaryGrade.baseSalary. Monthly value materialized as AllowanceRecord with sourceType=employment_contract, itemCode 01.
시급 (hourly base salary)	Derived	Not stored. Computed as Contract.baseSalary / 209 whenever needed.
근무일자 (working date)	Source	Attendance.date
근무시간 (working hours)	Source	Attendance.totalHours (raw per-day); aggregated by calculator
연장근무 시간 (overtime hours)	Source	OvertimeRecord.overtimeHours (raw only)
식사시간 (meal break)	Source	OvertimeRecord.mealBreakHours
연장 근무계 (overtime totals)	Ledger	AllowanceRecord rows with itemCodes 09/10/12/13, one per overtime category. Output of OvertimeRecordCalculator.
수당 단가 (allowance unit price)	Recipe	Unit price / multiplier on the sheet column metadata (+ org parameter store). Not per-employee.
입원환자 / 치료횟수	Source + Ledger	FixedIncentive with manually-entered unitCount × unitPrice (e.g. category nursing or rehabilitation). No separate event entity — count is operator-entered, not fed by a real-time clinical module.
퇴사자 (resignee)	Source	ResignationRecord — date + reason + status only, no amounts
남은 연차일수 (remaining annual leave)	Derived	LeaveEntitlementRule(yearsOfService) − Σ LeaveRequest.days. Computed on read.
건강보험 퇴직정산 금액	Ledger	DeductionRecord with sourceType=resignation_record, itemCode 12. Output of ResignationRecordCalculator.
당직의 수당 (doctor on-call)	Source + Ledger	FixedIncentive with category on_call, manually-entered unitCount × unitPrice. No separate schedule entity — entries are manually recorded by HR or the doctor.
인센티브 case	Source + Ledger	FixedIncentive versioned chain → AllowanceRecord itemCode 23, sourceType=fixed_incentive
기타수당 — 생일/1주년/이달의직원	Recipe + Ledger	Each celebration type is its own sheet column with subCategory=celebration. Generates AllowanceRecord with sourceType=manual.
기타공제 — 기숙사/학자금	Recipe + Ledger	Each as its own sheet column with subCategory=welfare_loan/dormitory. Generates DeductionRecord with sourceType=manual.

---

5. No New Source Entities

Earlier drafts proposed TreatmentEvent and OnCallShift as new source-layer entities. Decision: not needed.

Both 입원환자/치료횟수 and 당직 일정 are operator-entered counts — there is no real-time clinical or scheduling module feeding them. They fit the existing FixedIncentive(unitCount × unitPrice = totalAmount) shape exactly:

• 입원환자/치료횟수 → FixedIncentive(category=nursing | rehabilitation, ...) with unitCount = treatment count
• 당직 일정 → FixedIncentive(category=on_call) with unitCount = shift count and unitPrice = per-shift rate

Creating new entities would duplicate this surface for no gain. The focus stays on the sheet experience — how managers efficiently enter and audit these numbers — rather than on schema fragmentation.

If a real-time feed appears later (clinical record system, on-call scheduling integration), revisit this decision.

---

6. Refactored Entity Field Inventories

The exact post-refactor shape of each entity in Layer 1.

OvertimeRecord

employeeId, workDate, overtimeHours, mealBreakHours, category (weekday/saturday/holiday/night), status (pending/approved/rejected), approvedBy, approvedAt, notes, chainId, version, supersededAt, organizationId.

SportsIncentive

employeeId, year, month, incentiveType, position, presetId?, status, approvedBy, approvedAt, notes, chainId, version, supersededAt, organizationId.

EducationSubsidy

employeeId, courseName, paymentDate, receiptVerified, status, approvedBy, approvedAt, notes, chainId, version, supersededAt, organizationId.

LeaveRequest

employeeId, leaveType, startDate, endDate, reason, status, approvedBy, approvedAt, rejectionReason, isHalfDay, halfDayPeriod?, attachments?, organizationId. (no totalDays)

ResignationRecord

employeeId, resignationDate, reason?, status (pending/processing/settled/completed), processedBy, processedAt, notes, chainId, version, supersededAt, organizationId. (no monetary fields)

YearEndSettlement

employeeId, settlementYear, applicationDate?, applicationMonth?, status (pending/calculated/approved/applied/completed), calculatedBy, calculatedAt, approvedBy, approvedAt, notes, chainId, version, supersededAt, organizationId. (no amount/installment fields — installment plan lives on the sheet column metadata splitMonths/splitFromYearMonth)

FixedIncentive

Unchanged structurally — already separates unitCount × unitPrice = totalAmount. Stays the canonical entity for any operator-entered count × rate incentive (nursing, rehabilitation, on-call, etc).

---

7. Open Schema Questions

These need a decision before the refactor migration is authored.

1. Sheet UX for FixedIncentive entry — since all volume-based and on-call incentives flow through FixedIncentive(unitCount × unitPrice), the manager's primary pain point is entering and auditing the count column efficiently. Sheet patterns to consider: per-category presets, bulk-paste from external spreadsheets, copy-from-last-month, in-row recalc preview. This is the focus of the next phase.
2. YearEndSettlement installment representation — confirm the sheet column splitMonths is sufficient, or whether installments need their own entity (e.g. when employee changes mid-installment).
3. Backfill of inputSnapshot — for existing AllowanceRecord / DeductionRecord rows that pre-date this field, do we backfill from current source data, or accept null for historical rows? Recommendation: accept null; flag in UI as "legacy".
4. subCategory enum values — list above (structural / celebration / welfare_loan / voluntary / legacy) is a starting point. Confirm with HR.
5. 휴직 (LeaveOfAbsence) interaction — LeaveOfAbsenceRecord exists but no calculator consumes it. Need an insurance calculator pass that prorates base salary for partial-month leave.

---

See also

• Payroll Provenance — current data flow
• Calculators — pipeline that consumes source entities and writes the atomic ledger
• Database Reference — current entity field inventory