{"id":"cmq4qiahw000zml019e274ygw","docId":"cmq4nh0wc004jpl01xnzvljyr","version":5,"title":"신스키마 ERD v0.5.1","content":"# 신스키마 `irosafe_shred` ERD v0.5.1 (통합본)\n\n> **상태**: v0.5.1 — antigravity 메인 (v0.5) + 본 워커 보강 (v0.5 보강) 통합. Phase A 종료 기준 산출물.\n\n작성: 2026-06-06 KST · MVP 6/30 (D-24)\n선행 산출물:\n- `01_신스키마_ERD_v0.5.md` (antigravity, 2026-06-06) — 메인 골격 + mermaid + 트랙 이원화 + 자연키 보존\n- `02_ERD_v0.5_보강.md` (담당 워커, 2026-06-06) — AI 어시스턴트 P0 5건 + 회의 결정 19건 + RBAC 3단계 + 운영자 #1~#5 답변\n- `06_마이그_매핑.md` (worker, 2026-06-06) — 좁힘 마이그 매트릭스\n\n## 0. 통합 의사결정\n\n| AI 어시스턴트 P0 권고 | v0.5.1 채택 |\n|---|---|\n| P0-1 계약 트랙 테이블 물리 분리 | **부분 채택** — `contract.track` ENUM + `branch_office_id NULL` (원콜) 패턴 유지. MVP 24일 안에 테이블 통째 분리 비용 큼. Phase 7월에 nullable 문제 누적 시 재분리. |\n| P0-2 `recurring_schedule_rule` 신설 | **채택** — `branch_office.operation_cycle_*` 폐기하지 않고 동시 운영 (마이그 호환). 신규 rule 우선. |\n| P0-3 `migration_legacy_ref` 통합 ref | **채택** — antigravity 의 `mig_pk_mapping_log` 폐기, 통합 ref 사용 (영구 보존). |\n| P0-4 콘솔 1:N + event 분리 | **채택** — `console_bag_history` 유지 + `console_event` 신설 (콘솔 자체 이벤트). |\n| P0-5 RBAC 3단계 명시 + permission 매트릭스 | **채택** — `member.role` ENUM 값 WORKER/OFFICE/ADMIN, `permission` + `role_permission` 신설. |\n\n## 1. 도메인 맵\n\n| 도메인 | 책임 | MVP IN | 참고 |\n|---|---|---|---|\n| `auth` | 인증·권한·세션 | ✓ RBAC 3단계 | bcrypt 확정 (#2) |\n| `partner` | 협력사·외주업체 + 지점 | ✓ 자연키 + legacy_ref | 마이그 정합 |\n| `asset` | 차량·콘솔·바코드 | ✓ console 1:N | |\n| `operation` | 계약·일정·증명서·콘솔 이벤트 | ✓ **핵심** 트랙 이원화 | recurring_schedule_rule |\n| `work` | 작업 이력·정산 | minimal | MVP 후 풀 |\n| `comm` | 알림·발송 이력 | minimal | |\n| `system` | 코드 마스터·감사·환경 | ✓ 지역·품목 | |\n| `migration` | 레거시 이관 추적 | ✓ legacy_ref | 영구 보존 |\n\n## 2. ERD 통합 다이어그램 (Mermaid)\n\n**🖼 다이어그램 (SVG 렌더)**\n\n[![ERD v0.5.1 다이어그램](https://dev-shred.irosafe.com/assets/erd-v0.5.1.svg)](https://dev-shred.irosafe.com/assets/erd-v0.5.1.svg)\n\n- 🔍 [전체 보기 (새창)](https://dev-shred.irosafe.com/assets/erd-v0.5.1.svg)\n- 📥 [원본 .mmd 다운로드](https://dev-shred.irosafe.com/assets/erd-v0.5.1.mmd)\n- ✏️ [mermaid.live 에디터에서 열기](https://mermaid.live)\n\n<details>\n<summary>📋 Mermaid 원본 코드 (펼치기)</summary>\n\n```mermaid\nerDiagram\n    %% [1. AUTH 도메인] — RBAC 3단계 명시\n    member {\n        BIGINT id PK\n        VARCHAR login_id UK \"자연키 (로그인 ID)\"\n        VARCHAR password_hash \"bcrypt $2a$/$2b$ length=60\"\n        VARCHAR name\n        VARCHAR phone\n        VARCHAR email\n        BIGINT subcontractor_id FK \"외주업체 소속 (nullable)\"\n        ENUM status \"ACTIVE, SUSPENDED, LEFT\"\n        VARCHAR legacy_user_seq UK \"마이그 자연키\"\n    }\n    role {\n        BIGINT id PK\n        VARCHAR code UK \"WORKER, OFFICE, ADMIN\"\n        VARCHAR name\n        INT sort_order\n    }\n    member_role {\n        BIGINT id PK\n        BIGINT member_id FK\n        BIGINT role_id FK\n    }\n    permission {\n        BIGINT id PK\n        VARCHAR code UK \"CALENDAR_VIEW_DAY 등\"\n        VARCHAR scope \"calendar/schedule/partner/operation/system/auth\"\n        VARCHAR name\n    }\n    role_permission {\n        BIGINT id PK\n        BIGINT role_id FK\n        BIGINT permission_id FK\n    }\n    session {\n        BIGINT id PK\n        BIGINT member_id FK\n        VARCHAR jwt_id UK \"JWT jti claim\"\n        DATETIME issued_at\n        DATETIME expires_at\n        DATETIME revoked_at \"blacklist\"\n        VARCHAR client_ip\n        VARCHAR user_agent\n    }\n    login_audit {\n        BIGINT id PK\n        VARCHAR login_id\n        BIGINT member_id FK\n        ENUM result \"SUCCESS, FAIL_PWD, FAIL_BLOCKED, FAIL_NOT_FOUND\"\n        VARCHAR client_ip\n        DATETIME attempted_at\n    }\n    menu {\n        BIGINT id PK\n        VARCHAR code UK\n        VARCHAR name\n        INT depth\n        BIGINT parent_menu_id FK\n        VARCHAR url\n        INT sort_order\n        BOOLEAN is_used\n    }\n    menu_auth {\n        BIGINT id PK\n        BIGINT menu_id FK\n        BIGINT member_id FK\n        BOOLEAN can_read\n        BOOLEAN can_write\n    }\n\n    %% [2. PARTNER 도메인] — 협력사 마이그 정합\n    subcontractor {\n        BIGINT id PK\n        VARCHAR code UK \"자연키 (협력사 코드)\"\n        VARCHAR business_number UK \"자연키 (10자리 정규화)\"\n        VARCHAR name\n        VARCHAR representative_name\n        VARCHAR phone\n        VARCHAR address\n        VARCHAR zipcode\n        VARCHAR area_code FK\n        BIGINT attach_file_id FK\n        CHAR color_hex \"협력사 컬러 매핑 (캘린더)\"\n        ENUM status \"ACTIVE, SUSPENDED, CLOSED\"\n        VARCHAR legacy_subcontractor_seq UK\n    }\n    subcontractor_branch {\n        BIGINT id PK\n        BIGINT subcontractor_id FK\n        VARCHAR name\n        VARCHAR phone\n        VARCHAR position\n        BOOLEAN is_primary\n    }\n\n    %% [3. OPERATION 도메인] — 계약 트랙 이원화 (P0-1 부분 채택)\n    contract {\n        BIGINT id PK\n        VARCHAR code UK \"자연키 (계약코드)\"\n        ENUM track \"ONE_CALL, RECURRING\"\n        VARCHAR name\n        VARCHAR company_name\n        VARCHAR business_number\n        VARCHAR customer_phone\n        VARCHAR address\n        VARCHAR zipcode\n        DATE started_on\n        DATE ended_on \"default = started_on + 1년 (정기)\"\n        BOOLEAN auto_extend \"default TRUE (정기)\"\n        ENUM shred_option \"ON_SITE, INTAKE\"\n        BOOLEAN is_branch \"정기 본사 여부\"\n        TIME visit_start_time\n        TIME visit_end_time\n        ENUM certificate_type \"YEARLY, ONE_TIME, COWAY, AH, HDD\"\n        ENUM status \"CONTRACT, EXPIRED, CANCELLED, TERMINATED\"\n        VARCHAR legacy_contract_id UK\n    }\n    contract_subcontractor {\n        BIGINT id PK\n        BIGINT contract_id FK\n        BIGINT subcontractor_id FK\n    }\n    branch_office {\n        BIGINT id PK\n        VARCHAR code UK \"자연키 (지점코드)\"\n        BIGINT contract_id FK \"정기 본사 contract\"\n        VARCHAR name \"지점명 — OFFICE+ Lock\"\n        VARCHAR manager_name\n        VARCHAR manager_phone\n        VARCHAR address\n        VARCHAR zipcode\n        VARCHAR area_code FK\n        DATE setup_started_on\n        DATE visit_ends_on\n        BIGINT subcontractor_id FK\n        ENUM status \"ACTIVE, SUSPENDED, CLOSED\"\n        VARCHAR legacy_branch_office_id UK\n    }\n    recurring_schedule_rule {\n        BIGINT id PK\n        BIGINT branch_office_id FK\n        SMALLINT month_count \"월 N회 (회의)\"\n        VARCHAR preferred_weekdays \"MON,WED 등 hint\"\n        ENUM preferred_time_window \"MORNING, AFTERNOON, EVENING\"\n        TEXT notes\n        DATE effective_from\n        DATE effective_to\n        DATETIME created_at\n        DATETIME updated_at\n    }\n    operation {\n        BIGINT id PK\n        BIGINT contract_id FK\n        BIGINT branch_office_id FK \"원콜은 NULL\"\n        DATE visit_started_on\n        DATE visit_ended_on\n        TIME visit_start_time\n        TIME visit_end_time\n        ENUM status \"PENDING, IN_PROGRESS, COMPLETED, CANCELED\"\n    }\n    schedule {\n        BIGINT id PK\n        BIGINT operation_id FK\n        ENUM track \"ONE_CALL, RECURRING\"\n        BIGINT branch_office_id FK\n        BIGINT rule_id FK \"track=RECURRING 일 때 생성 근거\"\n        DATE visit_started_on\n        DATE visit_ended_on\n        TIME visit_start_time\n        TIME visit_end_time\n        DATE original_visit_started_on\n        TEXT change_reason\n        ENUM status \"PLANNED, CONFIRMED, COMPLETED, RESCHEDULED, CANCELED\"\n        BIGINT subcontractor_id FK \"캐시\"\n    }\n    schedule_assignment {\n        BIGINT id PK\n        BIGINT schedule_id FK\n        BIGINT member_id FK \"작업자\"\n        BOOLEAN is_primary\n        DATETIME assigned_at\n        BIGINT assigned_by FK\n    }\n    schedule_assignment_audit {\n        BIGINT id PK\n        BIGINT schedule_id FK\n        BIGINT prev_worker_id FK\n        BIGINT new_worker_id FK\n        ENUM change_reason \"TAENGGYEO, OFFICE_REASSIGN, LEAVE, OTHER\"\n        BIGINT changed_by FK\n        DATETIME changed_at\n        TEXT note\n    }\n    schedule_manager {\n        BIGINT id PK\n        BIGINT schedule_id FK\n        BIGINT member_id FK\n        VARCHAR name\n        VARCHAR phone\n        ENUM role \"LEAD, ASSISTANT, SECURITY\"\n    }\n    disposal {\n        BIGINT id PK\n        BIGINT schedule_id FK\n        BIGINT console_id FK\n        VARCHAR bag_barcode\n        ENUM bag_status \"COLLECTED, SHREDDED\"\n        DATETIME disposed_at\n    }\n    certificate {\n        BIGINT id PK\n        BIGINT schedule_id FK UK\n        VARCHAR cert_file_name\n        VARCHAR cert_file_path\n        BOOLEAN signed_yn\n        BIGINT security_manager_id FK\n        INT quantity\n        DECIMAL fee\n        ENUM type \"YEARLY, ONE_TIME, COWAY, AH, HDD\"\n        ENUM status \"ISSUED, UNISSUED, VOIDED\"\n        VARCHAR legacy_shredding_seq UK\n    }\n    certificate_worktype {\n        BIGINT id PK\n        BIGINT certificate_id FK\n        ENUM work_type \"BOX, A4_BOX, KG, MADAE, HDD\"\n        VARCHAR unit \"item, kg\"\n        DECIMAL quantity\n        DECIMAL weight\n    }\n\n    %% [4. ASSET 도메인] — 콘솔 1:N + barcode + console_event\n    console {\n        BIGINT id PK\n        VARCHAR code UK \"자연키 (콘솔 코드)\"\n        BIGINT branch_office_id FK \"지점 1:N (P0-4)\"\n        BIGINT subcontractor_id FK \"보조 — 콘솔 소유 협력사\"\n        VARCHAR name\n        VARCHAR current_bag_barcode\n        ENUM bag_status \"READY, IN_USE, COLLECTED, SHREDDED\"\n        DATETIME bag_inserted_at\n        DATETIME bag_withdrew_at\n        ENUM status \"AVAILABLE, DEPLOYED, MAINTENANCE, RETIRED\"\n        VARCHAR legacy_console_id UK\n    }\n    barcode {\n        BIGINT id PK\n        BIGINT console_id FK\n        VARCHAR code UK \"QR 또는 barcode 값\"\n        ENUM format \"QR, CODE128, CODE39\"\n        DATETIME printed_at\n        BOOLEAN active\n    }\n    console_bag_history {\n        BIGINT id PK\n        BIGINT console_id FK\n        VARCHAR barcode\n        ENUM event \"INSERTED, WITHDRAWN, SHREDDED\"\n        DATETIME event_at\n    }\n    console_event {\n        BIGINT id PK\n        BIGINT console_id FK\n        BIGINT schedule_id FK\n        ENUM event_type \"COLLECTED, SHREDDED, RELOCATED, STATUS_CHANGE\"\n        DATETIME occurred_at\n        BIGINT actor_member_id FK\n        DECIMAL quantity_kg\n        TEXT note\n    }\n    car {\n        BIGINT id PK\n        BIGINT subcontractor_id FK\n        VARCHAR plate_number\n        VARCHAR model\n        VARCHAR color\n        VARCHAR internal_no\n    }\n    car_dispatch {\n        BIGINT id PK\n        BIGINT car_id FK\n        BIGINT branch_office_id FK\n        DATETIME dispatch_started_at\n        DATETIME dispatch_ended_at\n        TEXT purpose\n    }\n\n    %% [5. WORK 도메인] — minimal MVP\n    work_log {\n        BIGINT id PK\n        DATE worked_on\n        BIGINT dispatcher_id FK\n        BIGINT worker_id FK\n        BIGINT car_id FK\n        BIGINT unload_subcontractor_id FK\n    }\n    work_job {\n        BIGINT id PK\n        BIGINT work_log_id FK\n        BIGINT schedule_id FK\n        BIGINT certificate_id FK\n        DATE worked_on\n        BIGINT worker_id FK\n        INT amount\n        DECIMAL weight\n        DECIMAL service_price\n        ENUM pay_type \"CASH, CARD, TRANSFER, MIXED\"\n    }\n    collection_item {\n        BIGINT id PK\n        BIGINT schedule_id FK\n        VARCHAR item_code FK \"REGION / ITEM 마스터\"\n        VARCHAR unit \"item, kg\"\n        DECIMAL quantity\n    }\n\n    %% [6. COMM 도메인] — minimal\n    attach_file {\n        BIGINT id PK\n        ENUM target_type \"SUBCONTRACTOR_BIZNUM, CERTIFICATE, SIGNATURE, NOTICE, MISC\"\n        BIGINT target_id\n        VARCHAR original_name\n        VARCHAR stored_name\n        VARCHAR path\n        BIGINT size\n    }\n    notification {\n        BIGINT id PK\n        BIGINT target_member_id FK\n        ENUM kind \"SCHEDULE_REASSIGNED, SCHEDULE_BULK_UPDATE, BRANCH_RENAMED, CERTIFICATE_ISSUED, OTHER\"\n        JSON payload_json\n        DATETIME read_at\n        DATETIME created_at\n    }\n    kakao_send {\n        BIGINT id PK\n        VARCHAR template_code\n        VARCHAR recipient_phone\n        TEXT message\n        VARCHAR response_code\n        DATETIME requested_at\n        DATETIME received_at\n    }\n\n    %% [7. SYSTEM 도메인]\n    code {\n        BIGINT id PK\n        VARCHAR category \"REGION, ITEM, CHANGE_REASON 등\"\n        VARCHAR code\n        VARCHAR name\n        VARCHAR parent_code\n        INT sort_order\n        BOOLEAN active\n    }\n    audit_log {\n        BIGINT id PK\n        BIGINT actor_member_id FK\n        VARCHAR action\n        VARCHAR target_type\n        BIGINT target_id\n        JSON payload_json\n        DATETIME occurred_at\n    }\n    config {\n        VARCHAR cfg_key PK\n        VARCHAR cfg_value\n        VARCHAR description\n    }\n\n    %% [8. MIGRATION 도메인] — P0-3 통합 ref\n    migration_legacy_ref {\n        BIGINT id PK\n        VARCHAR entity_type \"partner / branch_office / contract / schedule / member / console 등\"\n        BIGINT new_id \"신모델 PK\"\n        VARCHAR legacy_table \"ILSA_SHREDDING.SSF_*\"\n        VARCHAR legacy_pk_name\n        VARCHAR legacy_pk_value\n        VARCHAR legacy_code \"자연키 별칭\"\n        DATETIME migrated_at\n        VARCHAR migration_batch_id\n        ENUM status \"MAPPED, REVIEW_NEEDED, DROPPED\"\n        TEXT note\n    }\n    mig_staging_contract_error {\n        BIGINT id PK\n        BIGINT legacy_pk\n        TEXT raw_row\n        VARCHAR error_reason\n        DATETIME captured_at\n        BOOLEAN resolved\n    }\n\n    %% 관계 매핑\n    member ||--o{ member_role : \"has_roles\"\n    role ||--o{ member_role : \"assigned\"\n    role ||--o{ role_permission : \"grants\"\n    permission ||--o{ role_permission : \"granted_to\"\n    member ||--o{ session : \"owns\"\n    member ||--o{ login_audit : \"logs\"\n    member ||--o{ menu_auth : \"menus\"\n    menu ||--o{ menu_auth : \"authorized\"\n    subcontractor ||--o{ member : \"belongs_to\"\n    subcontractor ||--o{ subcontractor_branch : \"has_contacts\"\n    subcontractor ||--o{ contract_subcontractor : \"associated\"\n    contract ||--o{ contract_subcontractor : \"associated\"\n    contract ||--o{ branch_office : \"has_branches\"\n    branch_office ||--o{ recurring_schedule_rule : \"schedule_rule\"\n    branch_office ||--o{ console : \"contains\"\n    console ||--o{ barcode : \"labeled_with\"\n    console ||--o{ console_bag_history : \"bag_events\"\n    console ||--o{ console_event : \"events\"\n    schedule ||--o{ console_event : \"triggered_by\"\n    subcontractor ||--o{ car : \"owns\"\n    car ||--o{ car_dispatch : \"dispatched\"\n    branch_office ||--o{ car_dispatch : \"receives\"\n    contract ||--o{ operation : \"initiates\"\n    branch_office ||--o{ operation : \"undergoes\"\n    operation ||--o{ schedule : \"has_schedules\"\n    recurring_schedule_rule ||--o{ schedule : \"generates\"\n    subcontractor ||--o{ schedule : \"assigned_to\"\n    schedule ||--o{ schedule_manager : \"managed_by\"\n    schedule ||--o{ schedule_assignment : \"assignments\"\n    member ||--o{ schedule_assignment : \"assigned_as\"\n    schedule ||--o{ schedule_assignment_audit : \"change_history\"\n    schedule ||--o{ disposal : \"shred_events\"\n    console ||--o{ disposal : \"processed_in\"\n    schedule ||--|| certificate : \"has_certificate\"\n    certificate ||--o{ certificate_worktype : \"breakdown\"\n    member ||--o{ certificate : \"security_signed\"\n    work_log ||--o{ work_job : \"jobs\"\n    schedule ||--o{ work_job : \"executed\"\n    schedule ||--o{ collection_item : \"items\"\n```\n\n</details>\n\n## 3. 핵심 변경 사항 (v0.5 → v0.5.1)\n\n| 항목 | v0.5 | v0.5.1 |\n|---|---|---|\n| 계약 트랙 식별 | `is_annual` + `is_branch` 플래그 | **`track` ENUM('ONE_CALL','RECURRING')** 추가 (명시) |\n| 정기 만료 정책 | 단순 `ended_on` | **`auto_extend BOOLEAN`** + default = `started_on + 1년` (회의 결정) |\n| 반복 일정 규칙 | `branch_office.operation_cycle_*` 만 | **`recurring_schedule_rule` 신설** (month_count + weekday/time hint + effective_from/to) |\n| RBAC | `member.role ENUM('SUPER','ADMIN','USER')` | **`role` 테이블** (`WORKER/OFFICE/ADMIN`) + `permission` + `role_permission` + `member_role` |\n| 콘솔 이벤트 | `console_bag_history` 만 | **`console_event` 추가** (콘솔 자체 이벤트: COLLECTED/SHREDDED/RELOCATED/STATUS_CHANGE) |\n| 담당자 변경 이력 | `schedule_manager` 만 | **`schedule_assignment` + `schedule_assignment_audit`** 신설 (change_reason ENUM: TAENGGYEO 명시) |\n| 마이그 추적 | `mig_pk_mapping_log` 임시 | **`migration_legacy_ref` 영구** (entity_type, status='MAPPED'/'REVIEW_NEEDED'/'DROPPED') |\n| 자루상태 ENUM | `READY/IN_USE/COLLECTED/SHREDDED` | (동일) + 마이그 시 C1100000 격리 (운영자 #3) |\n| 증명서 상태 | `ISSUED/RESCINDED` | **`ISSUED/UNISSUED/VOIDED`** 3단계 (운영자 #4 — 50% 미발급은 정상) |\n| WORK_TYPE | 단일 ENUM | **`certificate_worktype.unit ENUM('item','kg')`** + `quantity` 분리 (운영자 #5 — kg 중량형) |\n| 협력사 컬러 | 없음 | `subcontractor.color_hex` (캘린더 협력사 컬러 매핑) |\n| password_hash | 단순 VARCHAR | **`CHAR(60)` bcrypt 명시** (운영자 #2 코드 확정) |\n| Crm1472 endpoint | 미명시 | 신규 시스템 `/crm/save` 호환 endpoint 유지 (운영자 #1 자체 CRM, B6 결정) |\n\n## 4. RBAC permission 매트릭스\n\n| permission code | scope | WORKER | OFFICE | ADMIN |\n|---|---|---|---|---|\n| `CALENDAR_VIEW_DAY` | calendar | ✓ | ✓ | ✓ |\n| `CALENDAR_VIEW_WEEK` | calendar | | ✓ | ✓ |\n| `CALENDAR_VIEW_MONTH` | calendar | | ✓ | ✓ |\n| `SCHEDULE_REASSIGN_SELF` (땡겨오기) | schedule | ✓ | ✓ | ✓ |\n| `SCHEDULE_BULK_UPDATE` | schedule | | ✓ | ✓ |\n| `SCHEDULE_BULK_DELETE` | schedule | | ✓ | ✓ |\n| `BRANCH_RENAME` (지점명 변경) | partner | | ✓ | ✓ |\n| `CONTRACT_CREATE` | operation | | ✓ | ✓ |\n| `CONTRACT_EDIT` | operation | | ✓ | ✓ |\n| `BULK_UPLOAD_EXCEL` (정기 대량) | operation | | ✓ | ✓ |\n| `BATCH_TRIGGER` (매월 25일) | operation | | | ✓ |\n| `SYSTEM_CONFIG` | system | | | ✓ |\n| `USER_MANAGE` | auth | | | ✓ |\n\n→ Spring Method Security `@PreAuthorize(\"hasAuthority('BRANCH_RENAME')\")`.\n\n## 5. ENUM 전체 (v0.5.1)\n\n| ENUM | 값 |\n|---|---|\n| `member.status` | ACTIVE, SUSPENDED, LEFT |\n| `role.code` | WORKER, OFFICE, ADMIN |\n| `subcontractor.status` | ACTIVE, SUSPENDED, CLOSED |\n| `branch_office.status` | ACTIVE, SUSPENDED, CLOSED |\n| `contract.track` | ONE_CALL, RECURRING |\n| `contract.shred_option` | ON_SITE, INTAKE |\n| `contract.certificate_type` | YEARLY, ONE_TIME, COWAY, AH, HDD |\n| `contract.status` | CONTRACT, EXPIRED, CANCELLED, TERMINATED |\n| `recurring_schedule_rule.preferred_time_window` | MORNING, AFTERNOON, EVENING |\n| `operation.status` | PENDING, IN_PROGRESS, COMPLETED, CANCELED |\n| `schedule.track` | ONE_CALL, RECURRING |\n| `schedule.status` | PLANNED, CONFIRMED, COMPLETED, RESCHEDULED, CANCELED |\n| `schedule_manager.role` | LEAD, ASSISTANT, SECURITY |\n| `schedule_assignment_audit.change_reason` | TAENGGYEO, OFFICE_REASSIGN, LEAVE, OTHER |\n| `console.status` | AVAILABLE, DEPLOYED, MAINTENANCE, RETIRED |\n| `console.bag_status` | READY, IN_USE, COLLECTED, SHREDDED |\n| `barcode.format` | QR, CODE128, CODE39 |\n| `console_bag_history.event` | INSERTED, WITHDRAWN, SHREDDED |\n| `console_event.event_type` | COLLECTED, SHREDDED, RELOCATED, STATUS_CHANGE |\n| `certificate.type` | YEARLY, ONE_TIME, COWAY, AH, HDD |\n| `certificate.status` | ISSUED, UNISSUED, VOIDED |\n| `certificate_worktype.work_type` | BOX, A4_BOX, KG, MADAE, HDD |\n| `certificate_worktype.unit` | item, kg |\n| `disposal.bag_status` | COLLECTED, SHREDDED |\n| `attach_file.target_type` | SUBCONTRACTOR_BIZNUM, CERTIFICATE, SIGNATURE, NOTICE, MISC |\n| `notification.kind` | SCHEDULE_REASSIGNED, SCHEDULE_BULK_UPDATE, BRANCH_RENAMED, CERTIFICATE_ISSUED, OTHER |\n| `login_audit.result` | SUCCESS, FAIL_PWD, FAIL_BLOCKED, FAIL_NOT_FOUND |\n| `work_job.pay_type` | CASH, CARD, TRANSFER, MIXED |\n| `migration_legacy_ref.status` | MAPPED, REVIEW_NEEDED, DROPPED |\n\n→ Java `@Enumerated(EnumType.STRING)`. 마이그 시 `SSF_CMMN_CODE` 매핑 (06_마이그_매핑.md §3 참고).\n\n## 6. 인덱스\n\n- `member(login_id)`, `session(jwt_id)`, `session(member_id, expires_at)`\n- `subcontractor(business_number)`, `subcontractor(name)`, `subcontractor(area_code)`\n- `branch_office(subcontractor_id)`, `branch_office(contract_id)`, `branch_office(name)`\n- `contract(code)`, `contract(track, status)`, `contract(ended_on, auto_extend)` (자동 연장 배치)\n- `recurring_schedule_rule(branch_office_id, effective_from)` (배치)\n- `schedule(visit_started_on)`, `schedule(subcontractor_id, visit_started_on)`, `schedule(branch_office_id, visit_started_on)`, `schedule(track, visit_started_on)` — **캘린더 조회 최우선**\n- `schedule_assignment(schedule_id, member_id)`\n- `schedule_assignment_audit(schedule_id, changed_at)`\n- `console(branch_office_id, status)`, `barcode(code)`\n- `console_event(console_id, occurred_at)`, `console_event(schedule_id)`\n- `certificate(schedule_id)` UK, `certificate(type, status)`\n- `migration_legacy_ref(entity_type, legacy_pk_value)`, `migration_legacy_ref(entity_type, new_id)`, `migration_legacy_ref(migration_batch_id)`\n\n## 7. Audit (Envers `_audit`)\n\n다음 테이블에 envers 적용:\n- `branch_office` (지점명 변경 — 회의 Lock 결정)\n- `schedule` (일정 변경)\n- `schedule_assignment` (담당자 변경)\n- `recurring_schedule_rule` (반복 규칙 변경)\n- `contract` (계약 변경)\n- `member` (권한 변경)\n\n> `schedule_assignment_audit` 와 envers `_audit` 는 **이중 운영**: envers 는 자동 row 이력, `schedule_assignment_audit` 는 change_reason ENUM 명시 (땡겨오기 추적).\n\n## 8. `system.config` 기본값\n\n| key | value | 설명 |\n|---|---|---|\n| `RECURRING_BATCH_DAY` | `25` | 매월 N일 배치 실행 |\n| `RECURRING_AUTO_EXTEND_YEARS` | `1` | 자동 연장 N년 (회의 결정) |\n| `JWT_ACCESS_TTL_SEC` | `7200` | 2시간 (AI 어시스턴트 권고 — refresh 7월) |\n| `LOGIN_FAIL_LOCK_THRESHOLD` | `5` | 연속 실패 시 잠금 |\n| `LOGIN_FAIL_LOCK_DURATION_MIN` | `30` | 잠금 시간 |\n| `COLOR_PALETTE_PARTNER` | `[\"#0e7c7b\",\"#2563eb\",\"#b45309\",\"#e11d48\",\"#7c3aed\",\"#16a34a\",\"#475569\"]` | 협력사 컬러 (round-robin) |\n| `CRM_CALLBACK_HOST` | `13.124.4.31` | 자체 CRM (운영자 #1) |\n\n## 9. 마이그 정합 — AI 어시스턴트 P0-3 통합\n\n`migration_legacy_ref` 가 `mig_pk_mapping_log` 대체 (영구 보존).\n\n검증 (Phase E7):\n```sql\nSELECT COUNT(*) FROM migration_legacy_ref WHERE status='REVIEW_NEEDED';\nSELECT entity_type, COUNT(*) FROM migration_legacy_ref GROUP BY entity_type;\nSELECT COUNT(*) FROM subcontractor s\n  LEFT JOIN migration_legacy_ref m ON m.entity_type='partner' AND m.new_id=s.id\n  WHERE m.id IS NULL; -- 마이그 누락\n```\n\nStaging 격리 (`mig_staging_contract_error`) — 변환 실패 row 보존.\n\n## 10. 운영자 확인 5건 → ERD 반영\n\n| # | 답 | v0.5.1 반영 |\n|---|---|---|\n| #1 Crm1472 = 자체 CRM | 자체 운영 | `config.CRM_CALLBACK_HOST=13.124.4.31` + 신규 `/crm/save` 호환 endpoint 유지 |\n| #2 password_hash = bcrypt | 코드 확정 | `member.password_hash CHAR(60)` + BCryptPasswordEncoder |\n| #3 CRUSHER_STATUS='C1100000' 1건 | 데이터 결함 | `console.bag_status` ENUM sub-code 만. 마이그 시 CS2207 `REVIEW_NEEDED` |\n| #4 certificate 50% 미발급 | 운영 흐름 정상 | `certificate.status ENUM('ISSUED','UNISSUED','VOIDED')` |\n| #5 WORK_TYPE='kg' 687건 | 중량형 단위 | `certificate_worktype.unit ENUM('item','kg')` + `quantity` 분리 |\n\n## 11. MVP 6/30 — Phase B~D 진입 준비\n\n이 ERD 로 Phase B (스택 골격) + Phase C (도메인 신규 구축) + Phase D (자동화) 모두 진입 가능:\n- Phase B: `member`, `role`, `permission`, `session` + JWT Security 6\n- Phase C1: `member` + `role` + `permission` CRUD\n- Phase C2: `contract` + `branch_office` 트랙 분리 CRUD\n- Phase C3: `schedule` + 캘린더 (track + visit_started_on 인덱스)\n- Phase C4: `subcontractor` + `subcontractor_branch`\n- Phase C5: `console` + `barcode` + `console_event`\n- Phase C6: `certificate`\n- Phase C7: `collection_item` + `code` 마스터\n- Phase C8: `code` 카테고리 (REGION/ITEM/CHANGE_REASON)\n- Phase D1: `recurring_schedule_rule` 배치 (매월 25일)\n- Phase D4: `schedule_assignment_audit` 트리거\n\n## 12. 다음 step (Phase A 종료)\n\n- [ ] tui — Flyway V1__initial_schema.sql 본 ERD 기반 DDL 작성\n- [ ] opencode — JPA 엔티티 7+ 도메인 본 ERD 기반 작성\n- [ ] AI 어시스턴트 — 본 v0.5.1 재검토 (P0 변경 + 마이그 정합)\n- [ ] design — 와이어프레임 7장 (P1+P2+P3) 회수\n- [ ] antigravity — 화면 명세 finalize\n- [ ] `_docs/03_DB재설계/07_ENUM_v0.5.md` — ENUM 풀 정의 (별도 문서로 분리)\n\n## 변경 이력\n\n- v0.4 (2026-05-08): 마이그 기반 7 도메인 풀 마이그\n- v0.5 (2026-06-06, antigravity): 신규 구축 + mermaid + 트랙 이원화 + 자연키 보존 + Staging\n- v0.5 보강 (2026-06-06, worker): AI 어시스턴트 P0 5건 + 회의 19건 + RBAC 3단계\n- **v0.5.1 (2026-06-06, worker 통합본): 두 산출물 통합. AI 어시스턴트 P0 5건 채택 결정 명시. 운영자 5건 ENUM/매핑 반영. Phase B~D 진입 준비 완료.**\n","sourceHash":null,"archivedAt":"2026-06-08T13:52:18+09:00","archivedBy":"sync"}