공급사 상품 등록 — 단어 유사도 점수 자동매칭 데이터 모델

v2는 "그룹이 신뢰 가능하게 가진 건 그룹 상품명 + 카테고리뿐"이라는 판단으로 그룹 상품명 LIKE + 대분류를 1차 검색으로 삼고 조건값 9개로 후보를 좁혔습니다. 9/10 개발 회의에서 이 구조는 공급사에게 그룹 상품명·대분류를 요구하는 것 자체가 무리(공급사는 그룹 개념을 모름)이고, LIKE 부분일치 범위를 정할 수 없다는 이유로 폐기됐습니다. v3는 공급사가 자기 상품 정보(★6 + ☆3)만 적으면 시스템이 기존 히어로들과 단어 유사도 점수를 내서 자동/후보/신규로 나눕니다.

1차 / 2차 범위

진입 · 화면 분리 · 권한 분기 (v2 승계)

공급사 상품 리스트(product/manage 동일 컬럼) · 하위 행 복제 제거 · 어드민 신규 메뉴 공존은 v2 그대로. 리스트의 "카테고리" 컬럼은 히어로가 가진 값(category1~3)을 표시만 하고 양식에서는 받지 않습니다.

양식 v3 ↔ 기존 컬럼 대응표 (request_item · seller_product)

v2 대응표를 승계하되 그룹 상품명·대분류 제거, 원산지 세부사항 필수 이동을 반영. 컬럼명은 개발DB 스키마 실측(두 테이블 모두 같은 이름 사용). ★유사도 필수 · ☆유사도 선택 · —유사도 미사용.

-- 매칭 상태 enum (임계와 1:1 · 회의 확정값)
CREATE TYPE match_state AS ENUM ('AUTO','CANDIDATE','NEW','ERROR');
--   AUTO      : score = 1.000            (자동 매칭 · 히어로 확정 · 후보 미노출)
--   CANDIDATE : 0.750 <= score <= 0.990  (후보 리스트 · MD 선택)   ※ 0.99~1.0 사이 반올림 규칙 🟠(§4R)
--   NEW       : score <= 0.740 또는 후보 0  (신규)
--   ERROR     : 필수 누락·형식 오류 (매칭 시도 전 제외)

-- 배치(요청) : 기존 seller_product_request 에 진행률·제출 시각 추가
ALTER TABLE seller_product_request
  ADD COLUMN matched_count INT NOT NULL DEFAULT 0,      -- 🆕 진행률 = matched_count / total_count
  ADD COLUMN matched_at    TIMESTAMP,                    -- 🆕 배치 매칭 완료(알림 트리거)
  ADD COLUMN submitted_at  TIMESTAMP;                    -- 🆕 [MD 검수로 제출] 시각 (이력·검수 큐 등재 기준)
-- status(varchar 20) 값에 QUEUED / MATCHING / MATCHED / SUBMITTED 추가 · 기존 값(승인/거절 계열)과의 병합 🟠

-- 행(항목) : 기존 seller_product_request_item 에 유사도 결과·검수 축 추가
ALTER TABLE seller_product_request_item
  ADD COLUMN row_no           INT,                        -- 🆕 엑셀 원본 행번호(오류·재업로드 표시)
  ADD COLUMN match_state      match_state,                -- 🆕 AUTO / CANDIDATE / NEW / ERROR
  ADD COLUMN match_score      NUMERIC(4,3),               -- 🆕 최고 후보 점수 0.000~1.000 (AUTO=1.000)
  ADD COLUMN match_candidates JSONB,                      -- 🆕 상위 N 후보 스냅샷 (아래 구조)
  ADD COLUMN norm_json        JSONB,                      -- 🆕 정규화 결과 {name_tokens, tax, storage, weight:{v,u}, origin_raw, brand, ...}
  ADD COLUMN error_code       VARCHAR(24),                -- 🆕 required_missing / bad_tax / bad_storage / unit_missing / bad_number …(기존 fail_reason은 사람용 문구)
  ADD COLUMN review_state     VARCHAR(10) NOT NULL DEFAULT 'PENDING', -- 🆕 PENDING / APPROVED / REJECTED (행별 검수 · v2 승계)
  ADD COLUMN reject_reason    VARCHAR(200),               -- 🆕 행별 거절 사유 (요청 단위 reject_reason은 기존 유지)
  ADD COLUMN reviewed_by      VARCHAR(100),
  ADD COLUMN reviewed_at      TIMESTAMP,
  ADD COLUMN total_available_quantity INT,               -- 🆕 양식 '재고수량' (seller_product엔 있고 item엔 없음)
  ADD COLUMN order_limit      INT;                       -- 🆕 양식 '수량제한'
-- 확정 히어로 = 기존 hero_product_code 컬럼 재사용 (AUTO: 시스템 세팅 / CANDIDATE: MD 선택 / NEW: NULL)
-- 편입 그룹 = 기존 group_id · main_product_name 컬럼 재사용 (채우는 규칙 🟠 그룹 생성 로직)
-- main_product_name · category1~3 : 양식 매핑 제거. 컬럼은 남기되 공급사 입력 경로 없음.

-- match_candidates JSONB 구조 (설계 표기 · 상위 N=5 제안)
-- [ { "hero": "H0001234", "score": 0.917, "group_id": 1234, "main_product_name": "감귤(10~11과),1kg,국산",
--     "fields": { "product_name": 1.0, "taxation": 1.0, "storage": 1.0, "weight": 1.0, "origin_raw": 0.5, "brand": 1.0,
--                 "origin": 1.0, "grade": null, "order_unit": 1.0 },
--     "diff": ["origin_raw"] },   … ]     -- diff = '무엇이 다른가' 하이라이트용

-- 인덱스
CREATE INDEX ix_spri_request_state ON seller_product_request_item(request_id, match_state);   -- 결과·검수 화면 상태별 목록
CREATE INDEX ix_spri_review        ON seller_product_request_item(review_state, created_at);
-- 후보 프리필터용(🟠 §4R · §9 성능) : pg_trgm 이 되면
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_sp_name_trgm       ON seller_product USING gin (product_name gin_trgm_ops);
CREATE INDEX ix_sp_tax_storage     ON seller_product(taxation, storage) WHERE use_yn = 'U';  -- use_yn 조건값은 후보 범위 🟠에 따름

🟠 미정 — status 값 병합 · match_score 반올림(0.995를 AUTO로 볼지) · norm_json 키 · 후보 범위(use_yn·타 공급사 포함) · 프리필터 인덱스 채택 여부. 물리 컬럼명은 구현 시 확정.

1. 검증(ERROR 선별) — 필수 9열 빈값·과세/보관 enum 미매핑·중량 단위 없음·net공급가 비숫자·주문 배수 0/음수 → match_state='ERROR' + error_code. 매칭 시도 없이 제외(오류행만 재업로드).
2. 정규화(입력 행 + 히어로 양쪽 동일) — 상품명 토큰화(공백·괄호·콤마 분리, 소문자, 특수문자 제거) · 과세/보관 enum 코드화 · 중량 "1KG/1kg/1,000g/1000 G" → 1000 G(계열 변환) · 원산지 세부/브랜드/원산지/등급 trim·공백 제거 · 주문 배수 int. 결과는 norm_json에 스냅샷(§4R 정규화 표 재사용).
3. 후보 프리필터(🟠 성능) — 전 히어로와 N×M을 계산하지 않도록 비교 대상을 먼저 줄임. 후보안: 과세·보관 등가 AND 상품명 trigram 유사도 하한(예: product_name % 입력명) → 수십~수백 건. 프리필터 없이 전건 계산할지, 하한을 얼마로 둘지는 🟠(§9).
4. 점수 산출 — 프리필터 후보 각각에 대해 ★6 + ☆3(있을 때) 필드별 유사도 → 가중 평균 score ∈ [0,1]. score 내림차순 상위 N(제안 5)을 match_candidates에, 최고값을 match_score에.
5. 분기(임계 ✅) — 아래 표. AUTO는 hero_product_code를 시스템이 세팅하고 후보 미노출. CANDIDATE는 후보 리스트를 노출해 MD가 선택(공급사 결과 화면에서 선택 허용 여부 🟠). NEW는 신규(그룹 처리 🟠).

유사도 계산 필드 — ★필수 6 · ☆선택 3 · 미사용 3

가중치 예시는 합 1.0이 되도록 적은 후보안일 뿐 확정이 아닙니다. 예시대로면 ★ 하나가 완전히 틀릴 때 0.65~0.90 사이로 떨어져 대부분 CANDIDATE, 둘이 틀리면 NEW 근처가 됩니다 — 임계 0.75와 가중치는 함께 튜닝해야 합니다(스테이지 실데이터로, 9/17).

점수 산식 (후보안 · 개발 결정)

-- 필드별 유사도 s_f ∈ [0,1], 가중치 w_f. ☆ 필드는 입력·히어로 양쪽에 값이 있을 때만 포함(없으면 가중치 재정규화).
score(item, hero) = Σ_f∈F  w_f · s_f(norm(item.f), norm(hero.f))  /  Σ_f∈F w_f
   F = ★6 ∪ { ☆f | item.f ≠ null AND hero.f ≠ null }

-- 분기 (회의 확정 임계)
best = max_hero score          -- 프리필터 후보 중
best = 1.000            → AUTO        (1.0 동점 다수면 CANDIDATE 강등 · 제안)
0.750 ≤ best ≤ 0.990    → CANDIDATE   (상위 N 저장)
best ≤ 0.740 | 후보 0   → NEW
-- 0.990 < best < 1.000 구간: numeric(4,3) 저장 시 0.991~0.999 는 정의상 CANDIDATE. 반올림 자리수 🟠

개발 결정 항목 표

정규화 (재사용) — 유사도 전 전처리

v2 §4R의 정규화 표는 match_key 정확일치용이 아니라 유사도 계산 전 전처리로 그대로 재사용합니다. 표기가 달라도 같은 정규화 값이면 해당 필드 s_f = 1.

입력 통제 (v2 승계 · v3 조정)

한계 · 오매칭 방지

1. 롤백 안전장치 — AUTO도 hero_product_code 재지정으로 되돌릴 수 있게(MD 사후 수정). match_candidates에 감사 스냅샷 유지.
2. 튜닝 루프 — 스테이지 업로드 → match_score 분포 → 가중치·프리필터 하한 조정. 설정값으로 두면 재배포 없이 가능.
3. 2차 확장 — 단건 API 동기 호출 · 정규화 캐시 컬럼 · 자동 승인.

필수 열 9 (★ = 유사도 계산)

선택 열 23 (☆ = 유사도 보조 · 있으면 반영)

예시행(양식 v3 실물): A0111355 / 제주 노지 감귤 1kg / 400 / 감귤 · 면세 · 냉장 · 1KG · 제주 · 제주농협 / 국내산 · — · 1 / D-1 · MFC · 가능 · 1 / 특성 "10~11과". 컬럼 대응은 §2 표.

대량 업로드 = 비동기 배치 매칭 (유지 · 성능 🟠)

1. 업로드·접수 — 파일 저장 + seller_product_request(status=QUEUED, total_count) + 행 파싱·검증(ERROR 즉시 확정) 후 즉시 응답(요청 id).
2. 비동기 매칭 워커 — status=MATCHING. 청크 단위로 정규화 → 프리필터 → 점수 → 분기, matched_count 증가 → 진행률.
3. 완료 알림 — status=MATCHED + matched_at. 공급사/MD 알림. 화면은 폴링 또는 푸시 🟠.
4. 결과 확인 — AUTO / CANDIDATE / NEW / ERROR 탭. CANDIDATE 후보 선택(공급사 화면에서 허용 여부 🟠 — 회의 문구는 "MD 선택"), NEW 확인, ERROR 재업로드.
5. [MD 검수로 제출] — 제출 전 검증: CANDIDATE 미선택 행·NEW 필수 미입력 행이 있으면 제출 불가(ERROR는 제외 대상). status=SUBMITTED + submitted_at → 이력·검수 큐 등재.

1. 승계 대상 — match_state ∈ {AUTO, CANDIDATE→확정}이고 hero_product_code가 정해진 행.
2. 그룹 이미지 조회 — 확정 히어로의 main_product_name으로 seller_product_group_image WHERE main_product_name = ? AND del_yn <> 'Y' ORDER BY picture_grade, picture_no. 공급사가 이미지를 안 올려도 됨.
3. 승계 방식 — 참조(그룹명 키 조인) vs 복사(image1~3_path에 스냅샷). 🟠 미정(v2 승계). 기존 행 테이블에 image1~3_path가 있어 복사가 구조상 쉬움.
4. NEW만 직접 — 그룹이 없으므로 직접 업로드. 신규 그룹 생성 여부(🟠)에 따라 그룹 대표 이미지가 될지 결정.

행 매칭 상태(match_state) ↔ 임계

배치 상태(request.status)

QUEUED/MATCHING은 업로드 화면 진행률로만 보이고 이력엔 없음. SUBMITTED부터 등록 이력·검수 큐. 기존 status 값과 병합 🟠.

MD 검수 화면 — 개별 행 (v2 승계 · v3 컬럼 조정)

등록 이력 페이지 = SUBMITTED 배치만 · 행별 검수 결과 부분집계(승인 N · 거절 M · 대기 K) — v2 승계. 원천 테이블(seller_product_request vs _excel_history)은 🟠 데이터소스.

인덱스(초안)

마이그레이션 순서

1. enum · ALTER — match_state 타입 · seller_product_request/_item 컬럼 추가(§3). 기존 데이터에 영향 없음(전부 nullable/default).
2. 정규화·유사도 서비스 — 토큰화 · 단위 변환표 · 동의어 사전 · 가중치 설정값. 입력·히어로 공용 함수.
3. 프리필터 인덱스 — pg_trgm 확장 가능 여부 확인 후 채택(🟠). 불가 시 과세·보관 등가 + 상품명 첫 토큰 prefix 등 대안.
4. 배치 워커 — QUEUED→MATCHING→MATCHED, 진행률, 알림.
5. 양식 v3 배포 — 필수 9 · 선택 23 · 과세/보관 드롭다운 · 대분류 드롭다운 없음.
6. 스테이지 튜닝(9/17) — 실데이터 업로드로 score 분포 확인 → 가중치·프리필터 하한 조정.

일정