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)