Hereby Database Schema — Reference

Generated from the live hereby_platform_local_1 database on 2026-05-20. Authoritative for the tables it lists — do not hand-edit (regenerate via the snippet at the bottom).

⚠️ This snapshot is stale (2026-05-20). The codebase now has 113 entity files (packages/database/src/entities/*.entity.ts) vs the 78 tables captured here. Whole domains added since are not in this snapshot: drive (drive_folders, drive_files, drive_access_grants, drive_share_links), AI (ai_conversations, ai_messages, doc_embeddings), e-sign (e_sign_records, sign_webhook_inbox), annual-leave-promotion (7 tables: batch/batch-item/event/plan-item/selection-snapshot/sign-attempt + root), account layer (accounts), offsite work (offsite_work_records, offsite_work_types), work-time-closing, and additional sheet/org-parameter tables. Regenerate before trusting the table list. Column definitions for the tables that ARE listed remain valid.

• 78 tables (snapshot; current entity count is 113), 1134 columns, 161 foreign keys, 130 unique indexes.
• All tables live in schema hereby. Every table extending BaseEntity carries id / createdAt / updatedAt / deletedAt (soft-delete) — these aren't called out per table to keep the doc scannable.
• → marks foreign keys. Unique constraints are listed under each table (partial indexes show their WHERE clause — important for soft-delete uniqueness).

Index

• Identity & org — organizations, employees, employment_contracts, employee_certifications, teams, team_members, team_permissions, roles, role_permissions, permissions, permissions_closure, auth_tokens, login_otp_codes, device_tokens
• Payroll & records — payroll_records, payroll_cell_memos, allowance_records, deduction_records, allowance_item_codes, deduction_item_codes, salary_grades, salary_grade_categories, tax_bracket_configs, organization_parameters, organization_parameter_history
• Attendance / shifts / leave — attendances, shifts, overtime_records, leave_requests, leave_entitlement_rules, leave_of_absence_records
• Incentives & treatments — fixed_incentives, incentive_presets, incentive_preset_categories, treatment_categories, treatment_items
• Sheets (workbook) — sheet_views, sheet_rows, sheet_cells, sheet_templates
• Settlement & subsidy — year_end_settlements, resignation_records, education_subsidies, grade_promotion_requests, certificate_issuances
• Nurse scheduler — nurse_profiles, nurse_schedules, nurse_schedule_entries, nurse_preferences, work_type_change_requests, wards, ward_daily_records
• General affairs — general_affairs_notices, notice_acknowledgments, mandatory_trainings, training_completions, recurring_tasks, recurring_task_executions, task_checklist_items, checklist_completions
• Inventory — inventory_categories, inventory_items
• Notifications — notification_events, notification_deliveries, notification_preferences, notification_mute_settings
• Communications & docs — conversations, conversation_members, messages, documents, document_templates, e_sign_records
• Integration / system — secom_events, secom_sync_state, ota_bundles, migrations

Identity & org

organizations

Column	Type	Null	Default
name	varchar(255)	NOT NULL
code	varchar(50)	NULL
timezone	varchar(50)	NOT NULL	'Asia/Seoul'
locale	varchar(10)	NOT NULL	'ko-KR'
currency	varchar(3)	NOT NULL	'KRW'
fiscalYearStart	integer	NOT NULL	1
settings	jsonb	NOT NULL	'{}'
isActive	boolean	NOT NULL	true
subscriptionTier	varchar(50)	NOT NULL	'starter'
subscriptionExpiresAt	date	NULL
billingInfo	jsonb	NULL
legalName	varchar(255)	NULL
taxId	varchar(50)	NULL
type	USER-DEFINED	NOT NULL	'SME'
size	USER-DEFINED	NOT NULL	'SMALL'
status	USER-DEFINED	NOT NULL	'TRIAL'
medicalField	varchar(100)	NULL
website	varchar()	NULL
email	varchar()	NULL
phone	varchar()	NULL
fax	varchar()	NULL
address	jsonb	NULL
billingAddress	jsonb	NULL
logo	varchar()	NULL
foundedDate	date	NULL
fiscalYearEnd	varchar()	NULL
sealImage	text	NULL
representativeName	varchar(100)	NULL
defaultShiftId	uuid	NULL

References: defaultShiftId → shifts

Unique:

• id
• code

employees

Column	Type	Null	Default
employeeNumber	varchar()	NOT NULL
firstName	varchar()	NOT NULL
lastName	varchar()	NOT NULL
middleName	varchar()	NULL
email	varchar()	NOT NULL
phone	varchar()	NULL
password	varchar()	NOT NULL
authMethods	ARRAY	NOT NULL	'{email,phone_otp}'[]
biometricId	varchar()	NULL
status	USER-DEFINED	NOT NULL	'ACTIVE'
hireDate	date	NOT NULL
profilePicture	varchar()	NULL
address	text	NULL
emergencyContact	jsonb	NULL
bankAccount	text	NULL
organizationId	uuid	NULL
lastLoginAt	timestamp	NULL
passwordChangedAt	timestamp	NULL
isSystemAdmin	boolean	NOT NULL	false
shiftId	uuid	NULL
mustChangePassword	boolean	NOT NULL	false
birthDate	date	NULL
residentNumber	text	NULL
attendanceMethod	USER-DEFINED	NULL

References: organizationId → organizations, shiftId → shifts

Unique:

• organizationId, employeeNumber WHERE (deletedAt IS NULL)
• email
• phone WHERE (phone IS NOT NULL)
• phone
• email
• phone
• id

employment_contracts

Column	Type	Null	Default
contractNumber	varchar()	NOT NULL
employeeId	uuid	NOT NULL
contractType	USER-DEFINED	NOT NULL
wageType	USER-DEFINED	NOT NULL
baseSalary	numeric(15,2)	NOT NULL
hourlyRate	numeric(15,2)	NULL
startDate	date	NOT NULL
endDate	date	NULL
standardWeeklyHours	integer	NOT NULL	40
maxWeeklyHours	integer	NOT NULL	52
allowances	jsonb	NULL
deductions	jsonb	NULL
benefits	jsonb	NULL
isActive	boolean	NOT NULL	true
terminationReason	varchar()	NULL
metadata	jsonb	NULL
salaryGradeId	uuid	NULL
chainId	uuid	NOT NULL
version	integer	NOT NULL	1
supersededAt	timestamp	NULL
hobongAmount	numeric(15,2)	NOT NULL	0
longevityAmount	numeric(15,2)	NOT NULL	0

References: employeeId → employees, salaryGradeId → salary_grades

Unique:

• chainId, version
• id

employee_certifications

Column	Type	Null	Default
employeeId	uuid	NOT NULL
type	USER-DEFINED	NOT NULL
name	varchar()	NOT NULL
licenseNumber	text	NULL
issuingAuthority	varchar()	NULL
issueDate	date	NULL
expiryDate	date	NULL
isActive	boolean	NOT NULL	true
notes	varchar()	NULL
organizationId	uuid	NOT NULL
storagePath	varchar()	NULL

References: organizationId → organizations, employeeId → employees

Unique:

• id

teams

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
description	varchar()	NULL
isActive	boolean	NOT NULL	true
leaderId	uuid	NULL
metadata	jsonb	NULL
organizationId	uuid	NOT NULL
parentTeamId	uuid	NULL

References: parentTeamId → teams, leaderId → employees, organizationId → organizations

Unique:

• id

team_members

Column	Type	Null	Default
teamId	uuid	NOT NULL
employeeId	uuid	NOT NULL
roleId	uuid	NULL
joinedAt	date	NOT NULL
leftAt	date	NULL
isActive	boolean	NOT NULL	true

References: roleId → roles, teamId → teams, employeeId → employees

Unique:

• id
• teamId, employeeId

team_permissions

Column	Type	Null	Default
teamId	uuid	NOT NULL
permissionId	uuid	NOT NULL
grantedAt	date	NULL
expiresAt	date	NULL
grantedBy	varchar()	NULL
isActive	boolean	NOT NULL	true
scope	jsonb	NULL

References: teamId → teams, permissionId → permissions

Unique:

• id
• teamId, permissionId

roles

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
description	varchar()	NULL
type	USER-DEFINED	NOT NULL	'custom'
isActive	boolean	NOT NULL	true
metadata	jsonb	NULL
organizationId	uuid	NULL

References: organizationId → organizations

Unique:

• id

role_permissions

Column	Type	Null	Default
roleId	uuid	NOT NULL
permissionId	uuid	NOT NULL

References: permissionId → permissions, roleId → roles

Unique:

• roleId, permissionId

permissions

Column	Type	Null	Default
code	varchar()	NOT NULL
name	varchar()	NOT NULL
description	varchar()	NULL
resource	varchar()	NOT NULL
actions	ARRAY	NOT NULL
conditions	jsonb	NULL
isActive	boolean	NOT NULL	true
parentId	uuid	NULL

References: parentId → permissions

Unique:

• code
• id

permissions_closure

Column	Type	Null	Default
id_ancestor	uuid	NOT NULL
id_descendant	uuid	NOT NULL

References: id_descendant → permissions, id_ancestor → permissions

Unique:

• id_ancestor, id_descendant

auth_tokens

Column	Type	Null	Default
token	varchar()	NOT NULL
tokenType	USER-DEFINED	NOT NULL
status	USER-DEFINED	NOT NULL	'ACTIVE'
employeeId	uuid	NOT NULL
expiresAt	timestamp	NOT NULL
lastUsedAt	timestamp	NULL
ipAddress	varchar()	NULL
userAgent	varchar()	NULL
deviceId	varchar()	NULL
metadata	jsonb	NULL
organizationId	varchar()	NOT NULL

References: employeeId → employees

Unique:

• id
• token
• token

login_otp_codes

Column	Type	Null	Default
phone	varchar()	NOT NULL
codeHash	varchar()	NOT NULL
employeeId	uuid	NOT NULL
organizationId	uuid	NOT NULL
expiresAt	timestamp	NOT NULL
consumedAt	timestamp	NULL
attempts	integer	NOT NULL	0
ipAddress	varchar()	NULL

References: employeeId → employees

Unique:

• id

device_tokens

Column	Type	Null	Default
token	varchar()	NOT NULL
platform	USER-DEFINED	NOT NULL
isActive	boolean	NOT NULL	true
lastUsedAt	timestamp	NULL
employeeId	uuid	NOT NULL
organizationId	uuid	NOT NULL

References: employeeId → employees, organizationId → organizations

Unique:

• token
• id

Payroll & records

payroll_records

Column	Type	Null	Default
payrollNumber	varchar()	NOT NULL
employeeId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
periodStart	date	NOT NULL
periodEnd	date	NOT NULL
totalDeductions	numeric(15,2)	NOT NULL	'0'
netPay	numeric(15,2)	NOT NULL	'0'
status	USER-DEFINED	NOT NULL	'draft'
paymentDate	date	NULL
paymentMethod	varchar()	NULL
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
paidAt	timestamp	NULL
confirmedBy	varchar()	NULL
confirmedAt	timestamp	NULL
grossPay	numeric(15,2)	NOT NULL	0
snapshot	jsonb	NULL
sentBy	varchar()	NULL
sentAt	timestamp	NULL

References: organizationId → organizations, employeeId → employees

Unique:

• payrollNumber
• id

payroll_cell_memos

Column	Type	Null	Default
organizationId	uuid	NOT NULL
employeeId	uuid	NULL
year	integer	NULL
month	integer	NULL
columnKey	varchar(100)	NOT NULL
memo	text	NOT NULL
status	USER-DEFINED	NOT NULL	'open'
createdById	uuid	NOT NULL
resolvedById	uuid	NULL
resolvedAt	timestamp	NULL
sheetViewId	uuid	NULL
sheetRowId	uuid	NULL

References: organizationId → organizations, employeeId → employees

Unique:

• id

allowance_records

Column	Type	Null	Default
employeeId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
amount	numeric(15,2)	NOT NULL
paymentMethod	USER-DEFINED	NOT NULL	'salary'
reason	varchar()	NOT NULL
date	date	NULL
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
payrollRecordId	uuid	NULL
isAutoGenerated	boolean	NOT NULL	false
sourceType	USER-DEFINED	NULL
sourceId	uuid	NULL
inputSnapshot	jsonb	NULL
formulaVersion	varchar(32)	NULL
sourceSheetId	uuid	NOT NULL
sourceSheetRowId	uuid	NOT NULL
sourceSheetColumnKey	varchar(64)	NOT NULL
appliedParameters	jsonb	NULL
allowanceItemCodeId	uuid	NULL

References: employeeId → employees, organizationId → organizations, allowanceItemCodeId → allowance_item_codes, sourceSheetRowId → sheet_rows, sourceSheetId → sheet_views, payrollRecordId → payroll_records

Unique:

• id
• sourceSheetId, sourceSheetRowId, sourceSheetColumnKey WHERE (deletedAt IS NULL)

deduction_records

Column	Type	Null	Default
employeeId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
amount	numeric(15,2)	NOT NULL
reason	varchar()	NOT NULL
date	date	NULL
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
payrollRecordId	uuid	NULL
isAutoGenerated	boolean	NOT NULL	false
sourceType	USER-DEFINED	NULL
sourceId	uuid	NULL
inputSnapshot	jsonb	NULL
formulaVersion	varchar(32)	NULL
sourceSheetId	uuid	NOT NULL
sourceSheetRowId	uuid	NOT NULL
sourceSheetColumnKey	varchar(64)	NOT NULL
appliedParameters	jsonb	NULL
deductionItemCodeId	uuid	NULL

References: organizationId → organizations, employeeId → employees, deductionItemCodeId → deduction_item_codes, sourceSheetId → sheet_views, payrollRecordId → payroll_records, sourceSheetRowId → sheet_rows

Unique:

• id
• sourceSheetId, sourceSheetRowId, sourceSheetColumnKey WHERE (deletedAt IS NULL)

allowance_item_codes

Column	Type	Null	Default
code	varchar(4)	NOT NULL
name	varchar()	NOT NULL
displayOrder	integer	NOT NULL	0
isActive	boolean	NOT NULL	true
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• organizationId, code
• id

deduction_item_codes

Column	Type	Null	Default
code	varchar(4)	NOT NULL
name	varchar()	NOT NULL
displayOrder	integer	NOT NULL	0
isActive	boolean	NOT NULL	true
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• organizationId, code
• id

salary_grades

Column	Type	Null	Default
grade	integer	NOT NULL
baseSalary	numeric(15,2)	NOT NULL
hobongAmount	numeric(15,2)	NOT NULL	'0'
totalSalary	numeric(15,2)	NOT NULL
annualSalary	numeric(15,2)	NULL
effectiveYear	integer	NOT NULL
minimumWage	numeric(15,2)	NULL
monthlyHours	numeric(7,2)	NOT NULL	'209'
status	USER-DEFINED	NOT NULL	'active'
notes	varchar()	NULL
organizationId	uuid	NOT NULL
categoryId	uuid	NULL

References: organizationId → organizations, categoryId → salary_grade_categories

Unique:

• organizationId, categoryId, grade, effectiveYear WHERE (deletedAt IS NULL)
• id

salary_grade_categories

Column	Type	Null	Default
name	varchar()	NOT NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

tax_bracket_configs

Column	Type	Null	Default
minIncome	numeric(15,2)	NOT NULL
maxIncome	numeric(15,2)	NOT NULL
rate	numeric(8,4)	NOT NULL
deduction	numeric(15,2)	NOT NULL	0
effectiveYear	integer	NOT NULL
isActive	boolean	NOT NULL	true
notes	varchar()	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

organization_parameters

Column	Type	Null	Default
organizationId	uuid	NOT NULL
namespace	varchar(64)	NOT NULL
key	varchar(64)	NOT NULL
type	varchar(16)	NOT NULL
numberValue	numeric(,)	NULL
stringValue	text	NULL
booleanValue	boolean	NULL
label	varchar(200)	NOT NULL
description	text	NULL
unit	varchar(16)	NULL
updatedBy	uuid	NULL

References: organizationId → organizations

Unique:

• id
• organizationId, namespace, key WHERE (deletedAt IS NULL)

organization_parameter_history

Column	Type	Null	Default
parameterId	uuid	NOT NULL
organizationId	uuid	NOT NULL
namespace	varchar(64)	NOT NULL
key	varchar(64)	NOT NULL
type	varchar(16)	NOT NULL
numberValue	numeric(,)	NULL
stringValue	text	NULL
booleanValue	boolean	NULL
effectiveFrom	timestamp	NOT NULL	now()
changedBy	uuid	NULL
reason	text	NULL

References: parameterId → organization_parameters

Unique:

• id

Attendance / shifts / leave

attendances

Column	Type	Null	Default
employeeId	uuid	NOT NULL
date	date	NOT NULL
shiftId	uuid	NULL
scheduledCheckIn	timestamp	NULL
scheduledCheckOut	timestamp	NULL
actualCheckIn	timestamp	NULL
actualCheckOut	timestamp	NULL
status	USER-DEFINED	NOT NULL	'scheduled'
type	USER-DEFINED	NOT NULL	'regular'
regularHours	numeric(5,2)	NOT NULL	'0'
overtimeHours	numeric(5,2)	NOT NULL	'0'
nightHours	numeric(5,2)	NOT NULL	'0'
weekendHours	numeric(5,2)	NOT NULL	'0'
holidayHours	numeric(5,2)	NOT NULL	'0'
breakHours	numeric(5,2)	NOT NULL	'0'
totalHours	numeric(5,2)	NOT NULL	'0'
location	jsonb	NULL
notes	varchar()	NULL
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
metadata	jsonb	NULL
organizationId	uuid	NULL

References: employeeId → employees, shiftId → shifts, organizationId → organizations

Unique:

• employeeId, scheduledCheckIn WHERE ((scheduledCheckIn IS NOT NULL) AND (deletedAt IS NULL))
• id

shifts

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
description	varchar()	NULL
type	USER-DEFINED	NOT NULL
startTime	time without time zone	NOT NULL
endTime	time without time zone	NOT NULL
workHours	numeric(5,2)	NOT NULL
breakHours	numeric(5,2)	NOT NULL	'1'
nightDifferentialRate	numeric(5,2)	NULL
weekendDifferentialRate	numeric(5,2)	NULL
holidayDifferentialRate	numeric(5,2)	NULL
overtimeRate	numeric(5,2)	NOT NULL	1.5
isActive	boolean	NOT NULL	true
applicableDays	jsonb	NULL
metadata	jsonb	NULL
organizationId	uuid	NULL

References: organizationId → organizations

Unique:

• id

overtime_records

Column	Type	Null	Default
employeeId	uuid	NOT NULL
workDate	date	NOT NULL
startTime	time without time zone	NULL
endTime	time without time zone	NULL
overtimeHours	numeric(7,2)	NOT NULL
mealBreakHours	numeric(7,2)	NOT NULL	'0'
category	USER-DEFINED	NOT NULL	'weekday'
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
chainId	uuid	NOT NULL
version	integer	NOT NULL	1
supersededAt	timestamp	NULL
rejectionReason	text	NULL
report	jsonb	NULL

References: employeeId → employees, organizationId → organizations

Unique:

• id
• chainId, version

leave_requests

Column	Type	Null	Default
employeeId	uuid	NOT NULL
leaveType	USER-DEFINED	NOT NULL
startDate	date	NOT NULL
endDate	date	NOT NULL
reason	text	NOT NULL
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
rejectionReason	varchar()	NULL
isHalfDay	boolean	NOT NULL	false
halfDayPeriod	USER-DEFINED	NULL
attachments	jsonb	NULL
organizationId	uuid	NOT NULL

References: employeeId → employees, organizationId → organizations

Unique:

• id

leave_entitlement_rules

Column	Type	Null	Default
yearsOfServiceMin	integer	NOT NULL
yearsOfServiceMax	integer	NULL
entitledDays	numeric(4,1)	NOT NULL
accrualType	USER-DEFINED	NOT NULL	'annual'
maxDays	numeric(4,1)	NULL
description	varchar(200)	NULL
sortOrder	integer	NOT NULL	0
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• organizationId, yearsOfServiceMin
• id

leave_of_absence_records

Column	Type	Null	Default
employeeId	uuid	NOT NULL
reason	varchar()	NOT NULL
startDate	date	NOT NULL
expectedReturnDate	date	NULL
actualReturnDate	date	NULL
status	USER-DEFINED	NOT NULL	'active'
processedBy	varchar()	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations, employeeId → employees

Unique:

• id

Incentives & treatments

fixed_incentives

Column	Type	Null	Default
employeeId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
incentiveType	varchar()	NOT NULL
position	varchar()	NULL
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
chainId	uuid	NOT NULL
version	integer	NOT NULL	1
supersededAt	timestamp	NULL
category	USER-DEFINED	NOT NULL	'other'
workDate	date	NULL
unitCount	integer	NOT NULL	1
unitPrice	numeric(15,2)	NOT NULL	0
totalAmount	numeric(15,2)	NOT NULL	0
splitRatio	numeric(5,2)	NULL
metadata	jsonb	NULL
sessionId	uuid	NULL

References: organizationId → organizations, employeeId → employees

Unique:

• chainId, version
• id

incentive_presets

Column	Type	Null	Default
code	varchar()	NOT NULL
name	varchar()	NOT NULL
description	text	NULL
unitPrice	numeric(15,2)	NOT NULL	0
isActive	boolean	NOT NULL	true
sortOrder	integer	NOT NULL	0
categoryId	uuid	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations, categoryId → incentive_preset_categories

Unique:

• id
• organizationId, code WHERE (deletedAt IS NULL)

incentive_preset_categories

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
description	text	NULL
applicableTeams	jsonb	NULL
isActive	boolean	NOT NULL	true
sortOrder	integer	NOT NULL	0
organizationId	uuid	NOT NULL
isAttendanceVerifiable	boolean	NOT NULL	false

References: organizationId → organizations, organizationId → organizations

Unique:

• id

treatment_categories

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
description	text	NULL
hospitalRatio	numeric(5,2)	NOT NULL	0.5
therapistRatio	numeric(5,2)	NOT NULL	0.5
isActive	boolean	NOT NULL	true
sortOrder	integer	NOT NULL	0
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

treatment_items

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
ratio	varchar()	NOT NULL	'1:1'
unitPrice	numeric(15,2)	NOT NULL	0
isActive	boolean	NOT NULL	true
sortOrder	integer	NOT NULL	0
organizationId	uuid	NOT NULL
treatmentCategoryId	uuid	NOT NULL

References: organizationId → organizations, treatmentCategoryId → treatment_categories

Unique:

• id

Sheets (workbook)

sheet_views

Column	Type	Null	Default
organizationId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
name	varchar(120)	NOT NULL
visibility	varchar(16)	NOT NULL
createdBy	uuid	NOT NULL
filters	jsonb	NOT NULL
columnLayout	jsonb	NOT NULL	'[]'
groupBy	jsonb	NOT NULL	'["employeeId"]'
sort	jsonb	NULL
description	text	NULL
lockedAt	timestamp	NULL
lockedBy	uuid	NULL
viewKind	varchar(16)	NOT NULL	'row'
groupId	varchar(64)	NULL
autoPopulate	jsonb	NULL
computeConfig	jsonb	NULL
lastMaterializedAt	timestamp	NULL
slug	varchar(64)	NULL
rowBasis	varchar(16)	NULL

References: organizationId → organizations

Unique:

• id
• organizationId, year, month, slug WHERE ((slug IS NOT NULL) AND (deletedAt IS NULL))

sheet_rows

Column	Type	Null	Default
sheetViewId	uuid	NOT NULL
employeeId	uuid	NULL
source	varchar(8)	NOT NULL	'manual'
sourceAllowanceRecordId	uuid	NULL
sourceDeductionRecordId	uuid	NULL
sourceOvertimeRecordId	uuid	NULL
sourceFixedIncentiveId	uuid	NULL
sortOrder	integer	NOT NULL	0
sourceTable	varchar(32)	NULL
sourceRecordId	uuid	NULL

References: sourceOvertimeRecordId → overtime_records, employeeId → employees, sourceFixedIncentiveId → fixed_incentives, sheetViewId → sheet_views, sourceDeductionRecordId → deduction_records, sourceAllowanceRecordId → allowance_records

Unique:

• id
• sheetViewId, sourceFixedIncentiveId WHERE ((sourceFixedIncentiveId IS NOT NULL) AND (deletedAt IS NULL))
• sheetViewId, sourceDeductionRecordId WHERE ((sourceDeductionRecordId IS NOT NULL) AND (deletedAt IS NULL))
• sheetViewId, sourceAllowanceRecordId WHERE ((sourceAllowanceRecordId IS NOT NULL) AND (deletedAt IS NULL))
• sheetViewId, sourceOvertimeRecordId WHERE ((sourceOvertimeRecordId IS NOT NULL) AND (deletedAt IS NULL))
• sheetViewId, sourceTable, sourceRecordId WHERE ((sourceTable IS NOT NULL) AND (sourceRecordId IS NOT NULL) AND (deletedAt IS NULL))

sheet_cells

Column	Type	Null	Default
sheetRowId	uuid	NOT NULL
columnKey	varchar(64)	NOT NULL
kind	varchar(12)	NOT NULL
literalValue	jsonb	NULL
refAllowanceRecordId	uuid	NULL
refDeductionRecordId	uuid	NULL
refOvertimeRecordId	uuid	NULL
refFixedIncentiveId	uuid	NULL
formulaExpression	text	NULL
computedValue	jsonb	NULL
computedAt	timestamp	NULL
inputSnapshot	jsonb	NULL

References: sheetRowId → sheet_rows, refAllowanceRecordId → allowance_records, refDeductionRecordId → deduction_records, refOvertimeRecordId → overtime_records, refFixedIncentiveId → fixed_incentives

Unique:

• sheetRowId, columnKey WHERE (deletedAt IS NULL)
• id

sheet_templates

Column	Type	Null	Default
organizationId	uuid	NULL
isSystem	boolean	NOT NULL	false
name	varchar(120)	NOT NULL
description	text	NULL
viewKind	varchar(16)	NOT NULL	'row'
filters	jsonb	NOT NULL	'{}'
columnLayout	jsonb	NOT NULL	'[]'
groupId	varchar(64)	NULL
sortOrder	integer	NOT NULL	100
createdBy	varchar(64)	NULL

References: organizationId → organizations

Unique:

• id

Settlement & subsidy

year_end_settlements

Column	Type	Null	Default
employeeId	uuid	NOT NULL
settlementYear	integer	NOT NULL
applicationDate	date	NULL
applicationMonth	integer	NULL
status	USER-DEFINED	NOT NULL	'pending'
calculatedBy	varchar()	NULL
calculatedAt	timestamp	NULL
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
chainId	uuid	NOT NULL
version	integer	NOT NULL	1
supersededAt	timestamp	NULL

References: organizationId → organizations, employeeId → employees

Unique:

• chainId, version
• id

resignation_records

Column	Type	Null	Default
employeeId	uuid	NOT NULL
resignationDate	date	NOT NULL
status	USER-DEFINED	NOT NULL	'pending'
reason	varchar()	NULL
processedBy	varchar()	NULL
processedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
chainId	uuid	NOT NULL
version	integer	NOT NULL	1
supersededAt	timestamp	NULL

References: organizationId → organizations, employeeId → employees

Unique:

• chainId, version
• id

education_subsidies

Column	Type	Null	Default
employeeId	uuid	NOT NULL
courseName	varchar()	NOT NULL
paymentDate	date	NOT NULL
receiptVerified	boolean	NOT NULL	false
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL
chainId	uuid	NOT NULL
version	integer	NOT NULL	1
supersededAt	timestamp	NULL

References: employeeId → employees, organizationId → organizations

Unique:

• id
• chainId, version

grade_promotion_requests

Column	Type	Null	Default
employeeId	uuid	NOT NULL
organizationId	uuid	NOT NULL
currentGradeId	uuid	NULL
currentGrade	integer	NOT NULL
nextGrade	integer	NOT NULL
nextGradeId	uuid	NULL
promotionDate	date	NOT NULL
promotionType	varchar()	NOT NULL	'hire_anniversary'
yearsOfService	integer	NOT NULL	0
currentBaseSalary	numeric(15,2)	NOT NULL	0
newBaseSalary	numeric(15,2)	NOT NULL	0
currentHobongAmount	numeric(15,2)	NOT NULL	0
newHobongAmount	numeric(15,2)	NOT NULL	0
status	varchar()	NOT NULL	'pending'
approvedBy	uuid	NULL
approvedAt	timestamp	NULL
rejectedReason	text	NULL
notes	text	NULL

References: organizationId → organizations, employeeId → employees, currentGradeId → salary_grades, nextGradeId → salary_grades

Unique:

• id

certificate_issuances

Column	Type	Null	Default
employeeId	uuid	NOT NULL
certificateNumber	varchar()	NULL
purpose	varchar()	NOT NULL
organizationId	uuid	NOT NULL
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
rejectedReason	varchar()	NULL
certificateHtml	text	NULL
templateId	uuid	NULL

References: templateId → document_templates, organizationId → organizations, employeeId → employees

Unique:

• id

Nurse scheduler

nurse_profiles

Column	Type	Null	Default
employeeId	uuid	NOT NULL
workType	USER-DEFINED	NOT NULL
wardId	uuid	NOT NULL
isHeadNurse	boolean	NOT NULL	false
organizationId	uuid	NOT NULL
metadata	jsonb	NULL

References: employeeId → employees, organizationId → organizations, wardId → wards

Unique:

• id
• employeeId, wardId WHERE (deletedAt IS NULL)

nurse_schedules

Column	Type	Null	Default
wardId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
status	USER-DEFINED	NOT NULL	'draft'
confirmedBy	varchar()	NULL
confirmedAt	timestamp	NULL
publishedAt	timestamp	NULL
organizationId	uuid	NOT NULL
settings	jsonb	NULL
metadata	jsonb	NULL
confirmedByEmployeeId	uuid	NULL
startDate	date	NULL
endDate	date	NULL

References: wardId → wards, confirmedByEmployeeId → employees, organizationId → organizations

Unique:

• id
• wardId, year, month, organizationId
• wardId, year, month, startDate, organizationId WHERE (deletedAt IS NULL)

nurse_schedule_entries

Column	Type	Null	Default
scheduleId	uuid	NOT NULL
employeeId	uuid	NOT NULL
date	date	NOT NULL
assignmentType	USER-DEFINED	NOT NULL
shiftId	uuid	NULL
isAiGenerated	boolean	NOT NULL	false
isManualOverride	boolean	NOT NULL	false
notes	text	NULL

References: employeeId → employees, shiftId → shifts, scheduleId → nurse_schedules

Unique:

• scheduleId, employeeId, date
• id

nurse_preferences

Column	Type	Null	Default
employeeId	uuid	NOT NULL
year	integer	NOT NULL
month	integer	NOT NULL
wardId	uuid	NOT NULL
organizationId	uuid	NOT NULL
vacationDates	jsonb	NOT NULL	'[]'
restrictedDates	jsonb	NOT NULL	'[]'
unavailableDates	jsonb	NOT NULL	'[]'
notes	text	NULL
submittedAt	timestamp	NULL
status	USER-DEFINED	NOT NULL	'draft'

References: wardId → wards, employeeId → employees, organizationId → organizations

Unique:

• id
• employeeId, year, month

work_type_change_requests

Column	Type	Null	Default
employeeId	uuid	NOT NULL
currentWorkType	USER-DEFINED	NOT NULL
requestedWorkType	USER-DEFINED	NOT NULL
reason	text	NOT NULL
effectiveDate	date	NULL
status	USER-DEFINED	NOT NULL	'pending'
approvedBy	varchar()	NULL
approvedAt	timestamp	NULL
rejectionReason	text	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations, employeeId → employees

Unique:

• id

wards

Column	Type	Null	Default
code	varchar()	NOT NULL
name	varchar()	NOT NULL
description	text	NULL
displayOrder	integer	NOT NULL	0
isActive	boolean	NOT NULL	true
schedulingConfig	jsonb	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• organizationId, code WHERE (deletedAt IS NULL)
• id

ward_daily_records

Column	Type	Null	Default
wardId	uuid	NOT NULL
date	date	NOT NULL
patientCount	integer	NOT NULL	0
updatedById	uuid	NULL
organizationId	uuid	NOT NULL
notes	text	NULL

References: updatedById → employees, organizationId → organizations, wardId → wards

Unique:

• id
• wardId, date, organizationId

General affairs

general_affairs_notices

Column	Type	Null	Default
title	varchar()	NOT NULL
content	text	NOT NULL
category	USER-DEFINED	NOT NULL
priority	USER-DEFINED	NOT NULL	'normal'
publishedAt	timestamp	NULL
expiresAt	timestamp	NULL
isPublished	boolean	NOT NULL	false
createdById	varchar()	NOT NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

notice_acknowledgments

Column	Type	Null	Default
noticeId	uuid	NOT NULL
employeeId	uuid	NOT NULL
organizationId	uuid	NOT NULL
acknowledgedAt	timestamp	NOT NULL	now()

References: employeeId → employees, noticeId → general_affairs_notices, organizationId → organizations

Unique:

• noticeId, employeeId
• id

mandatory_trainings

Column	Type	Null	Default
name	varchar()	NOT NULL
category	USER-DEFINED	NOT NULL
legalBasis	varchar()	NULL
frequency	USER-DEFINED	NOT NULL
requiredHours	numeric(5,1)	NULL
targetAudience	varchar()	NULL
penaltyAmount	numeric(15,2)	NULL
penaltyDescription	varchar()	NULL
description	text	NULL
isActive	boolean	NOT NULL	true
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

training_completions

Column	Type	Null	Default
mandatoryTrainingId	uuid	NOT NULL
employeeId	uuid	NOT NULL
completedDate	date	NOT NULL
expiryDate	date	NOT NULL
certificateNumber	varchar()	NULL
hours	numeric(5,1)	NULL
provider	varchar()	NULL
status	USER-DEFINED	NOT NULL	'completed'
notes	varchar()	NULL
organizationId	uuid	NOT NULL

References: employeeId → employees, organizationId → organizations, mandatoryTrainingId → mandatory_trainings

Unique:

• id

recurring_tasks

Column	Type	Null	Default
title	varchar()	NOT NULL
description	text	NULL
category	USER-DEFINED	NOT NULL
frequency	USER-DEFINED	NOT NULL
assignedTo	varchar()	NULL
dueDay	integer	NULL
isActive	boolean	NOT NULL	true
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

recurring_task_executions

Column	Type	Null	Default
recurringTaskId	uuid	NOT NULL
scheduledDate	date	NOT NULL
completedDate	date	NULL
completedBy	varchar()	NULL
status	USER-DEFINED	NOT NULL	'pending'
notes	text	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations, recurringTaskId → recurring_tasks

Unique:

• id

task_checklist_items

Column	Type	Null	Default
recurringTaskId	uuid	NOT NULL
title	varchar()	NOT NULL
sortOrder	integer	NOT NULL	0
isRequired	boolean	NOT NULL	true
organizationId	uuid	NOT NULL

References: recurringTaskId → recurring_tasks, organizationId → organizations

Unique:

• id

checklist_completions

Column	Type	Null	Default
taskChecklistItemId	uuid	NOT NULL
taskExecutionId	uuid	NOT NULL
isCompleted	boolean	NOT NULL	false
completedBy	varchar()	NULL
completedAt	timestamp	NULL
notes	varchar()	NULL
organizationId	uuid	NOT NULL

References: taskChecklistItemId → task_checklist_items, organizationId → organizations, taskExecutionId → recurring_task_executions

Unique:

• id

Inventory

inventory_categories

Column	Type	Null	Default
code	varchar()	NULL
name	varchar()	NOT NULL
description	text	NULL
isActive	boolean	NOT NULL	true
sortOrder	integer	NOT NULL	0
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

inventory_items

Column	Type	Null	Default
name	varchar()	NOT NULL
code	varchar()	NULL
categoryId	uuid	NOT NULL
quantity	integer	NOT NULL	0
unit	varchar()	NOT NULL	'개'
minimumStock	integer	NOT NULL	0
expirationDate	date	NULL
location	varchar()	NULL
supplier	varchar()	NULL
isRefundCandidate	boolean	NOT NULL	false
refundMemo	text	NULL
refundQuantity	integer	NULL
notes	text	NULL
isActive	boolean	NOT NULL	true
organizationId	uuid	NOT NULL

References: organizationId → organizations, categoryId → inventory_categories

Unique:

• id

Notifications

notification_events

Column	Type	Null	Default
organizationId	uuid	NOT NULL
actorId	uuid	NULL
targetEmployeeId	uuid	NULL
type	USER-DEFINED	NOT NULL
action	USER-DEFINED	NOT NULL
title	varchar()	NOT NULL
message	text	NOT NULL
referenceType	varchar()	NULL
referenceId	uuid	NULL
groupKey	varchar()	NULL
metadata	jsonb	NULL

Unique:

• id

notification_deliveries

Column	Type	Null	Default
eventId	uuid	NOT NULL
recipientId	uuid	NOT NULL
channel	USER-DEFINED	NOT NULL	'in_app'
status	USER-DEFINED	NOT NULL	'pending'
isSeen	boolean	NOT NULL	false
isRead	boolean	NOT NULL	false
isDismissed	boolean	NOT NULL	false
seenAt	timestamp	NULL
readAt	timestamp	NULL
sentAt	timestamp	NULL

References: recipientId → employees, eventId → notification_events

Unique:

• id

notification_preferences

Column	Type	Null	Default
employeeId	uuid	NOT NULL
organizationId	uuid	NOT NULL
eventType	varchar()	NOT NULL	'*'
channel	USER-DEFINED	NOT NULL	'in_app'
enabled	boolean	NOT NULL	true

References: organizationId → organizations, employeeId → employees

Unique:

• employeeId, eventType, channel
• id

notification_mute_settings

Column	Type	Null	Default
employeeId	uuid	NOT NULL
organizationId	uuid	NOT NULL
muteType	USER-DEFINED	NOT NULL
muteTargetId	uuid	NULL
eventType	USER-DEFINED	NULL
mutedAt	timestamp	NOT NULL	now()
expiresAt	timestamp	NULL

References: employeeId → employees

Unique:

• id
• employeeId, muteType, eventType WHERE (eventType IS NOT NULL)
• employeeId, muteType, muteTargetId WHERE (muteTargetId IS NOT NULL)

Communications & docs

conversations

Column	Type	Null	Default
type	USER-DEFINED	NOT NULL
title	varchar()	NULL
createdBy	varchar()	NOT NULL
lastMessageAt	timestamp	NULL
lastMessageContent	varchar()	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id

conversation_members

Column	Type	Null	Default
conversationId	uuid	NOT NULL
employeeId	uuid	NOT NULL
joinedAt	timestamp	NOT NULL	now()
lastReadAt	timestamp	NULL
unreadCount	integer	NOT NULL	0
customName	varchar()	NULL
role	USER-DEFINED	NOT NULL	'member'
leftAt	timestamp	NULL
status	USER-DEFINED	NOT NULL	'active'

References: conversationId → conversations, employeeId → employees

Unique:

• conversationId, employeeId
• id

messages

Column	Type	Null	Default
conversationId	uuid	NOT NULL
senderId	uuid	NULL
content	text	NOT NULL
type	USER-DEFINED	NOT NULL	'text'
readBy	jsonb	NULL
organizationId	uuid	NOT NULL
attachments	jsonb	NULL

References: conversationId → conversations, senderId → employees, organizationId → organizations

Unique:

• id

documents

Column	Type	Null	Default
employeeId	uuid	NOT NULL
filename	varchar()	NOT NULL
originalFilename	varchar()	NOT NULL
mimeType	varchar()	NOT NULL
fileSize	integer	NOT NULL
storagePath	varchar()	NOT NULL
category	USER-DEFINED	NOT NULL	'other'
documentType	varchar()	NOT NULL
status	USER-DEFINED	NOT NULL	'processing'
reviewedBy	varchar()	NULL
reviewedAt	timestamp	NULL
notes	varchar()	NULL
metadata	jsonb	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations, employeeId → employees

Unique:

• id

document_templates

Column	Type	Null	Default
code	varchar(50)	NOT NULL
name	varchar()	NOT NULL
bodyTemplate	text	NOT NULL
cssTemplate	text	NULL
isActive	boolean	NOT NULL	true
sortOrder	integer	NOT NULL	0
description	varchar()	NULL
organizationId	uuid	NOT NULL

References: organizationId → organizations

Unique:

• id
• organizationId, code WHERE (deletedAt IS NULL)

e_sign_records

Column	Type	Null	Default
organizationId	uuid	NOT NULL
employeeId	uuid	NOT NULL
sentById	uuid	NOT NULL
herebyTemplateId	integer	NOT NULL
herebyContractId	integer	NULL
herebyRedirectUrl	text	NULL
templateNameSnapshot	varchar()	NOT NULL
recipientNameSnapshot	varchar()	NOT NULL
recipientEmailSnapshot	varchar()	NOT NULL
recipientPhoneSnapshot	varchar()	NULL
errorMessage	text	NULL
status	USER-DEFINED	NOT NULL	'sending'
sentAt	timestamp	NOT NULL
signedAt	timestamp	NULL

References: employeeId → employees, sentById → employees, organizationId → organizations

Unique:

• herebyContractId WHERE (herebyContractId IS NOT NULL)
• id

Integration / system

secom_events

Column	Type	Null	Default
eventDatetime	varchar(20)	NOT NULL
terminalId	varchar(100)	NOT NULL
cardNumber	varchar(100)	NULL
employeeNumber	varchar(100)	NOT NULL
employeeName	varchar(200)	NULL
rawEventType	varchar(50)	NULL
authType	varchar(10)	NULL
ackMode	varchar(10)	NULL
ackTime	varchar(20)	NULL
rawData	jsonb	NULL
eventType	USER-DEFINED	NOT NULL	'unknown'
status	USER-DEFINED	NOT NULL	'pending'
errorMessage	text	NULL
processedAt	timestamp with time zone	NULL
organizationId	uuid	NOT NULL
employeeId	uuid	NULL
attendanceId	uuid	NULL
pulledAt	timestamp with time zone	NOT NULL	now()

References: attendanceId → attendances, employeeId → employees, organizationId → organizations

Unique:

• id

secom_sync_state

Column	Type	Null	Default
sourceKey	varchar(50)	NOT NULL
schemaName	varchar(100)	NOT NULL
organizationId	uuid	NOT NULL
enabled	boolean	NOT NULL	true
lastGatewayCreatedAt	timestamp	NOT NULL	'1970-01-01 00:00:00'
lastRunAt	timestamp with time zone	NULL
lastPulledCount	integer	NOT NULL	0

References: organizationId → organizations

Unique:

• sourceKey

ota_bundles

Column	Type	Null	Default
appId	varchar()	NOT NULL
platform	USER-DEFINED	NOT NULL
bundleVersion	varchar()	NOT NULL
minNativeVersion	varchar()	NOT NULL
channel	varchar()	NOT NULL	'production'
s3Key	varchar()	NOT NULL
downloadUrl	varchar()	NOT NULL
checksum	varchar()	NOT NULL
fileSize	integer	NOT NULL
releaseNotes	text	NULL
isActive	boolean	NOT NULL	true
expiresAt	timestamp	NULL
createdBy	varchar()	NULL

Unique:

• s3Key
• id
• appId, platform, bundleVersion

migrations

Column	Type	Null	Default
timestamp	bigint	NOT NULL
name	varchar()	NOT NULL

Unique:

• id

---

Regenerate

# Re-extract metadata from the live DB (uses .env DB_*):
cd packages/database && \
  psql ... -tA -c "<columns query>" > /tmp/_cols.txt && \
  psql ... -tA -c "<fks query>" > /tmp/_fks.txt && \
  psql ... -tA -c "<unique-idx query>" > /tmp/_uniq.txt
# Then re-run the Python generator (see git history of this file).

Source of truth: the live database schema (TypeORM migrations). Entities + migrations: packages/database/src/entities/, packages/database/src/migrations/.