witan2
Tables (19)
data_source
- id — BIGINT — Primary key — NOT NULL — 출처ID
- code — VARCHAR(50) — NOT NULL — 출처코드(MFDS_INGR_API, EU_COSING, CN_IECIC 등)
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 대상관할
- name — VARCHAR(200) — NOT NULL — 출처명
- data_kind — VARCHAR(20) — NOT NULL — 데이터종류(INGREDIENT/REGULATION)
- source_type — VARCHAR(20) — NOT NULL — 수집방식(API/CRAWL/FILE)
- base_url — VARCHAR(500) — 수집URL
- is_official — BOOLEAN — NOT NULL — 해당국 공식출처 여부
- priority — SMALLINT — NOT NULL — 우선순위(공홈100/타기관정리50/내부30)
- collector_key — VARCHAR(50) — NOT NULL — 수집기 구현키
- schedule_cron — VARCHAR(50) — 수집주기
- active — BOOLEAN — NOT NULL — 사용여부
function_code
- code — VARCHAR(50) — Primary key — NOT NULL — 기능·용도코드(PRESERVATIVE/UV_FILTER/COLORANT/FRAGRANCE/EMOLLIENT 등)
- name — VARCHAR(100) — NOT NULL — 기능명
- description — VARCHAR(500) — 설명
ingredient
- id — BIGINT — Primary key — NOT NULL — 내부ID
- ingredient_code — VARCHAR(20) — NOT NULL — 공개 원료코드(ING-0000001, 불변·재사용금지)
- inci_norm — VARCHAR(500) — 정체성 기준 INCI(정규화, 없으면 NULL)
- display_name — VARCHAR(500) — NOT NULL — 대표표시명(캐시)
- status — VARCHAR(20) — NOT NULL — 상태(ACTIVE/MERGED/RETIRED)
- merged_into_id — BIGINT — Foreign key → ingredient.id — 병합대상ID(중복 판명 시)
- created_at — DATETIME — NOT NULL — 생성일시
- updated_at — DATETIME — 수정일시
ingredient_code
- id — BIGINT — Primary key — NOT NULL — ID
- ingredient_id — BIGINT — Foreign key → ingredient.id — NOT NULL — 원료ID
- code_type — VARCHAR(20) — NOT NULL — 코드종류(CAS/EC/CI/UNII)
- code_value — VARCHAR(50) — NOT NULL — 코드값(CAS는 체크디지트 검증값)
- qualifier — VARCHAR(50) — 한정어(L-/DL-/generic/hydrate)
- data_source_id — BIGINT — Foreign key → data_source.id — NOT NULL — 출처ID
- match_method — VARCHAR(20) — 연결방법(SOURCE_ROW/CAS_EXACT/NAME_EXACT/MANUAL)
- verified — BOOLEAN — NOT NULL — 검수여부
- verified_by — BIGINT — 검수자ID
- verified_at — DATETIME — 검수일시
ingredient_function
- ingredient_id — BIGINT — Foreign key → ingredient.id — NOT NULL — 원료ID
- function_code — VARCHAR(50) — Foreign key → function_code.code — NOT NULL — 기능코드(PRESERVATIVE 등)
- data_source_id — BIGINT — Foreign key → data_source.id — NOT NULL — 출처ID
ingredient_name
- id — BIGINT — Primary key — NOT NULL — ID
- ingredient_id — BIGINT — Foreign key → ingredient.id — NOT NULL — 원료ID
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 관할(INTL=국제공통)
- lang — VARCHAR(10) — NOT NULL — 언어(BCP47: ko/en/zh-Hans/zh-Hant/ja/th)
- name_type — VARCHAR(20) — NOT NULL — 유형(OFFICIAL/INCI/SYNONYM/ALT)
- name — VARCHAR(500) — NOT NULL — 명칭 원문
- name_norm — VARCHAR(500) — NOT NULL — 검색용 정규화명
- data_source_id — BIGINT — Foreign key → data_source.id — NOT NULL — 출처ID
- verified — BOOLEAN — NOT NULL — 검수여부
- verified_by — BIGINT — 검수자ID
- verified_at — DATETIME — 검수일시
ingredient_source_record
- id — BIGINT — Primary key — NOT NULL — ID
- data_source_id — BIGINT — Foreign key → data_source.id — NOT NULL — 출처ID
- source_key — VARCHAR(500) — NOT NULL — 출처 내 식별값(식약처=표준명, CosIng=ref no)
- ingredient_id — BIGINT — Foreign key → ingredient.id — 연결된 원료ID(매칭 전 NULL)
- match_status — VARCHAR(20) — NOT NULL — 매칭상태(AUTO/VERIFIED/PENDING/REJECTED)
- raw_payload — JSON — NOT NULL — 원본 행 그대로
- record_hash — VARCHAR(64) — NOT NULL — 원본해시(변경감지)
- first_seen_snapshot_id — BIGINT — Foreign key → source_snapshot.id — NOT NULL — 최초 발견 스냅샷
- last_seen_snapshot_id — BIGINT — Foreign key → source_snapshot.id — NOT NULL — 마지막 확인 스냅샷
- removed_at — DATETIME — 원천에서 사라진 일시
jurisdiction
- code — VARCHAR(10) — Primary key — NOT NULL — 관할코드(KR/EU/CN/JP/US/TW/ASEAN/TH/MY/INTL 등)
- name — VARCHAR(100) — NOT NULL — 관할명
- parent_code — VARCHAR(10) — Foreign key → jurisdiction.code — 상위관할(TH→ASEAN)
jurisdiction_regime_rule
- id — BIGINT — Primary key — NOT NULL — 규칙ID
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 관할
- use_purpose — VARCHAR(50) — Foreign key → function_code.code — 적용 용도(NULL=기본 규칙)
- regime — VARCHAR(20) — NOT NULL — 방식(NEGATIVE/POSITIVE)
- required_list_id — BIGINT — Foreign key → regulation_list.id — POSITIVE일 때 반드시 등재돼야 할 목록
- effective_from — DATE — 시행일
- effective_to — DATE — 종료일(NULL=현행)
- legal_basis — VARCHAR(300) — 법적근거
record_change
- id — BIGINT — Primary key — NOT NULL — 변경ID
- snapshot_id — BIGINT — Foreign key → source_snapshot.id — NOT NULL — 발견 스냅샷ID
- record_type — VARCHAR(20) — NOT NULL — 대상(INGREDIENT_RECORD/REGULATION_ENTRY)
- record_id — BIGINT — NOT NULL — 대상ID
- change_type — VARCHAR(20) — NOT NULL — 유형(ADDED/UPDATED/REMOVED)
- field — VARCHAR(50) — 필드
- old_value — TEXT — 이전값
- new_value — TEXT — 새값
- changed_at — DATETIME — NOT NULL — 발견일시
regulation_condition
- id — BIGINT — Primary key — NOT NULL — 조건ID
- entry_id — BIGINT — Foreign key → regulation_entry.id — NOT NULL — 규제항목ID
- product_scope — VARCHAR(30) — 제품유형 표준코드(LEAVE_ON/RINSE_OFF/ORAL 등)
- product_scope_raw — VARCHAR(500) — 제품유형 원문
- use_purpose — VARCHAR(50) — Foreign key → function_code.code — 적용 용도(FRAGRANCE 등, NULL=모든 용도)
- body_part — VARCHAR(100) — 사용부위
- max_conc — DECIMAL(12, 6) — 최대농도
- conc_unit — VARCHAR(10) — 단위(%/ppm)
- calc_basis — VARCHAR(200) — 환산기준(산으로서 등)
- min_age — SMALLINT — 최소연령
- warning_text — TEXT — 경고문구
- other_text — TEXT — 기타조건 원문
regulation_cross_check
- id — BIGINT — Primary key — NOT NULL — 대조ID
- ingredient_id — BIGINT — Foreign key → ingredient.id — NOT NULL — 원료ID
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 관할
- primary_entry_id — BIGINT — Foreign key → regulation_entry.id — 우선출처 항목(공홈)
- secondary_entry_id — BIGINT — Foreign key → regulation_entry.id — 대조출처 항목(식약처 정리본 등)
- result — VARCHAR(20) — NOT NULL — 결과(AGREE/CONFLICT/ONLY_PRIMARY/ONLY_SECONDARY)
- diff — JSON — 차이 내역(상태·농도 등)
- review_status — VARCHAR(20) — NOT NULL — 검수(NOT_NEEDED/PENDING/RESOLVED)
- reviewed_by — BIGINT — 검수자ID
- reviewed_at — DATETIME — 검수일시
- checked_at — DATETIME — NOT NULL — 대조일시
regulation_entry
- id — BIGINT — Primary key — NOT NULL — 규제항목ID
- data_source_id — BIGINT — Foreign key → data_source.id — NOT NULL — 출처ID
- snapshot_id — BIGINT — Foreign key → source_snapshot.id — NOT NULL — 수집 스냅샷ID
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 적용관할
- logical_key — VARCHAR(100) — NOT NULL — 버전관통 키(EU:II:1460 등)
- regulation_list_id — BIGINT — Foreign key → regulation_list.id — NOT NULL — 소속 규제목록ID
- substance_name — VARCHAR(1000) — NOT NULL — 원문 물질명
- is_group — BOOLEAN — NOT NULL — 그룹항목(염·에스터 등)
- designation_type — VARCHAR(30) — NOT NULL — 지정방식(SUBSTANCE/GROUP/BOTANICAL_PART/CLASSIFICATION_REF)
- raw_text — TEXT — 원문 전체
- published_at — DATE — 공포일
- effective_from — DATE — 시행일
- effective_to — DATE — 종료일(NULL=현행)
regulation_entry_code
- code_type — VARCHAR(20) — Primary key — NOT NULL — 코드종류(CAS/EC)
- code_value — VARCHAR(50) — Primary key — NOT NULL — 코드값
- entry_id — BIGINT — Foreign key → regulation_entry.id — NOT NULL — 규제항목ID
- qualifier — VARCHAR(50) — 한정어
regulation_entry_name
- name_type — VARCHAR(20) — Primary key — NOT NULL — 유형(CHEMICAL/INN/INCI/LOCAL/NOTICE)
- lang — VARCHAR(10) — Primary key — NOT NULL — 언어(BCP47)
- name_norm — VARCHAR(500) — Primary key — NOT NULL — 매칭용 정규화명
- entry_id — BIGINT — Foreign key → regulation_entry.id — NOT NULL — 규제항목ID
- name — VARCHAR(1000) — NOT NULL — 명칭 원문
regulation_entry_scope
- entry_id — BIGINT — Foreign key → regulation_entry.id — NOT NULL — 규제항목ID
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 하위관할(TH/MY 등)
- applies — BOOLEAN — NOT NULL — 적용여부(FALSE=예외국)
- effective_from — DATE — 하위관할별 시행일
regulation_list
- id — BIGINT — Primary key — NOT NULL — 규제목록ID
- jurisdiction — VARCHAR(10) — Foreign key → jurisdiction.code — NOT NULL — 관할
- code — VARCHAR(50) — NOT NULL — 목록코드(EU_ANNEX_II, EU_ANNEX_V, KR_BYEOLPYO_1, CN_IECIC 등)
- name — VARCHAR(200) — NOT NULL — 목록명
- list_kind — VARCHAR(20) — NOT NULL — 성격(PROHIBITED/RESTRICTED/PERMITTED)
- category — VARCHAR(50) — Foreign key → function_code.code — 대상 용도(function_code, NULL=전체/일반)
- legal_basis — VARCHAR(300) — 법적근거(규정명·조항)
regulation_match
- entry_id — BIGINT — Foreign key → regulation_entry.id — NOT NULL — 규제항목ID
- ingredient_id — BIGINT — Foreign key → ingredient.id — NOT NULL — 원료ID
- method — VARCHAR(20) — NOT NULL — 방법(CAS_EXACT/NAME_EXACT/GROUP_RULE/FUZZY/MANUAL)
- status — VARCHAR(20) — NOT NULL — 상태(AUTO/VERIFIED/PENDING/REJECTED)
- confidence — DECIMAL(4, 3) — 신뢰도
- reviewed_by — BIGINT — 검수자ID
- reviewed_at — DATETIME — 검수일시
source_snapshot
- id — BIGINT — Primary key — NOT NULL — 스냅샷ID
- data_source_id — BIGINT — Foreign key → data_source.id — NOT NULL — 출처ID
- source_version — VARCHAR(50) — 원천버전(EU 2025/1175, ACD 2026-1 등)
- fetched_at — DATETIME — NOT NULL — 수집일시
- content_hash — VARCHAR(64) — 원본해시(변경감지)
- storage_path — VARCHAR(500) — 원본보관경로(PDF/HTML/JSON)
- total_count — INT — 원천 전체건수
- fetched_count — INT — 실제 수신건수
- status — VARCHAR(20) — NOT NULL — 상태(RUNNING/SUCCESS/FAILED/SKIPPED_NO_CHANGE)