feat(affiliate): Rakuten SFTP 기반 아이템 카탈로그 DB 구축

[CIOI]

배경

Antonioli affiliate 승인을 받았고, Rakuten Affiliate에서 제공하는 상품 feed를 기반으로 decoded의 아이템 카탈로그 DB를 구축하려고 한다.

Rakuten feed는 SFTP 서버에서 .txt 파일 형태로 내려받는 방식이며, 이 파일을 전처리한 뒤 DB에 적재하고 이후 상품 검색/매칭을 위한 item-level embedding까지 준비하는 것이 목표다.

현재까지 정리된 방향

• 초기 파트너/머천트: Antonioli
• feed 제공 방식: Rakuten Affiliate SFTP
• feed 파일 형식: .txt
• 초기 예상 규모: 약 12,000개 상품
• 카탈로그 데이터는 런타임 검색/매칭/affiliate 링크에 사용되므로 기본 저장 위치는 operation DB가 적합하다.
• assets DB는 raw feed 격리, 검수, 재처리, 승격 플로우가 필요하다고 판단될 때만 staging 용도로 검토한다.
• embedding은 Cody/raw_posts 벡터 검색 경로와 맞출 경우 gemini-embedding-2 + output_dimensionality=768 방향이 유력하다.

샘플 feed에서 확인한 주요 필드

샘플 Antonioli row 기준으로 다음 값들이 포함된다.

• 외부 상품 ID / product id
• 상품명
• 카테고리 계층
• affiliate URL
• 대표 이미지 URL
• long description
• brand
• 재고 상태
• 가격 / 통화
• 추가 이미지 URL 목록
• product type
• size / color / gender / age group

Embedding 텍스트 후보

우선 텍스트 embedding에는 검색 품질에 직접 도움이 되는 필드만 넣는다.

• 상품명
• brand
• 정규화된 category
• long description (중복 제거 후 1회만 사용)
• 필요 시 color / gender 등 짧은 속성

다음 값들은 embedding 텍스트에 넣지 않고 DB 메타데이터/필터/조인용으로 보관한다.

• SKU / 외부 ID
• affiliate URL
• 상품 URL
• 이미지 URL
• 가격
• tracking URL

구현 범위 초안

• operation DB에 affiliate item catalog 스키마 설계
• raw SFTP feed staging 테이블이 필요한지 결정
• Rakuten SFTP 접속 정보/env/secret 계약 정의
• SFTP에서 .txt feed 파일을 내려받는 흐름 구현
• Rakuten feed row 파싱 및 validation 구현
• Antonioli/Rakuten 필드를 decoded canonical catalog 필드로 정규화
• merchant + external_item_id 기준 idempotent upsert 구현
• sync run 기록, 성공/실패 row 수, 에러 샘플, 마지막 성공 시각 추적
• embedding source text 생성 함수 구현
• 초기 약 12,000개 상품 embedding backfill 배치 구현
• 필요 시 catalog item vector index/RPC/query path 설계
• parser / normalization / idempotent upsert 테스트 추가
• 초기 import 및 주기적 sync 운영 runbook 작성

열려 있는 질문

• raw feed row를 assets DB에 먼저 저장한 뒤 operation DB로 승격할지, 아니면 operation DB에 바로 normalized row를 적재할지?
• item embedding을 catalog table의 전용 vector 컬럼으로 둘지, generic embeddings 테이블의 entity_type으로 확장할지?
• 첫 retrieval 대상은 Cody outfit matching인지, 기존 solution 유사도 검색인지, 새로운 visual search flow인지?
• MVP에서 텍스트 embedding만 할지, 대표 상품 이미지 embedding도 같이 할지?
• Rakuten SFTP feed의 실제 갱신 주기와 파일 naming convention은 무엇인지?
• 품절 상품은 검색 대상에서 제외할지, DB에는 유지하되 필터링할지?
• category는 Rakuten/Antonioli 원문을 그대로 둘지, decoded 내부 category taxonomy로 매핑할지?

Acceptance Criteria

• Rakuten SFTP feed를 내려받아 로컬 또는 worker에서 파싱할 수 있다.
• Antonioli 상품 row가 중복 없이 catalog DB에 upsert된다.
• 실패 row와 실패 사유를 디버깅 가능한 형태로 확인할 수 있다.
• embedding source text 생성 로직이 deterministic하고 테스트로 보호된다.
• 초기 약 12,000개 상품에 대한 batch embedding/backfill 경로가 있다.
• 초기 import와 이후 sync를 어떻게 실행/검증하는지 문서화되어 있다.

[CIOI]

결정 업데이트: catalog 저장 위치

초기 Antonioli/Rakuten item feed는 operation DB가 아니라 assets DB에 먼저 적재한다.

이유

• affiliate feed의 전체 item catalog는 아직 사용자-facing 검증본 데이터가 아니다.
• decoded operation DB에는 매칭이 성립한 item만 승격한다.
• 따라서 feed sync / preprocessing / embedding / candidate retrieval 단계는 assets 쪽 staging inventory로 보는 것이 맞다.
• operation DB는 matched item 또는 solution/catalog reference처럼 런타임 검증본만 소유한다.

수정된 데이터 흐름

1. Rakuten SFTP에서 .txt.gz feed 다운로드
2. feed 파싱 및 Antonioli/Rakuten field normalization
3. assets DB에 raw/normalized affiliate catalog item upsert
4. assets DB에서 embedding 생성 및 vector retrieval 준비
5. Cody/spot/item matching에서 candidate item 검색
6. 매칭이 확정된 item만 operation DB로 promotion/upsert
7. operation DB는 matched item, solution/link, user-facing commerce data만 유지

설계 영향

• assets schema에 affiliate catalog staging table이 필요하다.
• operation schema에는 전체 feed catalog를 두지 않는다.
• assets ↔ operation 간 cross-project FK는 만들지 않는다.
• promotion은 worker/API가 idempotent write로 처리한다.
• 기존 issue의 "operation DB 우선" 가정은 이 결정으로 대체한다.

[CIOI]

결정 업데이트: Rakuten feed sync 전략

Rakuten Product Catalog 파일 구성 기준으로 다음 운영 전략을 채택한다.

파일 의미

• *_mp.txt.gz: full Product Catalog feed. 현재 advertiser catalog 전체 snapshot.
• *_mp_delta.txt.gz: delta feed. 최근 advertiser feed 처리 이후 insert/update/delete 된 product row만 포함.
• *_mp_template.txt.gz: full feed field/schema template 확인용.
• *_mp_deltatemplate.txt.gz: delta feed field/schema template 확인용. delta는 마지막 Modification field가 추가된다.
• ADDITIONAL/: advertiser가 main/global feed 외 추가 feed를 제공할 때 쓰는 디렉토리. 동일 언어/통화 추가 feed가 full/delta 형태로 들어갈 수 있다.

Rakuten delta file의 마지막 Modification field는 다음 값을 가진다.

• I: insert
• U: update
• D: delete

초기 sync / 주기 sync

1. 최초 bootstrap은 full feed(*_mp.txt.gz)를 다운로드해 assets DB에 upsert한다.
2. 이후 daily 또는 더 짧은 주기로 delta feed(*_mp_delta.txt.gz)를 다운로드한다.
3. delta row별로 I/U는 upsert, D는 hard delete가 아니라 deleted=true / is_active=false / deleted_at 처리한다.
4. delta feed의 header timestamp와 file checksum/size를 기록해 같은 delta를 중복 처리하지 않는다.
5. Rakuten 문서 권장처럼 정기적으로 full feed를 다시 pull해서 전체 catalog 정합성을 reconciliation한다.

권장 cadence

• MVP: full bootstrap 1회 → delta daily sync
• 안정화 후: delta는 1일 1회 이상 가능. Rakuten은 advertiser가 하루 여러 번 feed를 처리할 수 있으므로 timestamp 기준으로 변경 여부 확인
• full reconciliation: 월 1회부터 시작. drift가 자주 보이면 주 1회로 조정

Reconciliation 방식

• full feed를 임시 staging/sync run 단위로 적재한다.
• full feed에 존재하는 row는 upsert한다.
• 기존 assets catalog에는 있지만 이번 full feed에 없는 row는 바로 삭제하지 않고 inactive/deleted 처리한다.
• row count, inserted/updated/deleted/inactivated, parse error count를 sync run에 저장한다.

설계 메모

• delta만 장기간 적용하면 누락/실패/중복 처리 때문에 drift가 생길 수 있으므로 full reconciliation은 반드시 둔다.
• operation DB에는 matched item만 promotion한다는 원칙은 유지한다.
• 전체 Rakuten/Antonioli feed와 embedding/search 후보군은 assets DB가 소유한다.

[CIOI]

결정 업데이트: affiliate item embedding / cheap alternative scope

대체품 검색 기능의 목적은 고가 상품과 의미적으로 유사하면서 더 저렴한 affiliate item 후보를 찾는 것이므로, affiliate catalog embedding은 Cody/raw_posts embedding과 직접 공유하지 않고 별도 item-search 인덱스로 둔다.

Embedding 모델 결정

• Provider: OpenAI
• Model: text-embedding-3-small
• Dimensions: 768
• 대상: assets DB의 affiliate catalog item 전용 embedding

결정 이유

• 대체품 검색에서 embedding은 최종 가격 판단이 아니라 유사 후보 생성에 사용한다.
• 가격, 재고, 삭제 여부, 카테고리, 성별/색상/사이즈 등은 embedding text에 넣지 않고 구조화된 필터/랭킹 feature로 처리한다.
• OpenAI text-embedding-3-small은 Gemini embedding 대비 비용이 약 7.5-10배 낮아 catalog scale에 적합하다.
• 원본 고가 item과 후보 affiliate item은 같은 모델/차원으로 임베딩해야 하므로, cheap alternative search용 item embedding은 별도 OpenAI 768-dim index로 관리한다.
• Cody/raw_posts의 gemini-embedding-2 768-dim 경로와 직접 벡터 비교하지 않는다. 필요하면 query/source item도 OpenAI item-search document로 재임베딩한다.

Embedding text 원칙

Embedding source text에는 semantic similarity에 필요한 상품 속성만 포함한다.

포함:

• product title
• brand
• normalized category / product type
• deduplicated long description
• color / gender / material / silhouette 같은 짧은 속성

제외:

• price
• currency
• affiliate URL / product URL / image URL
• SKU / external ID / tracking URL
• availability / deleted flag

가격 비교와 cheap alternative 판단은 DB column 기반 filter/rerank에서 처리한다.

수정된 작업 범위

1. Rakuten full/delta feed sync는 기존 결정대로 assets DB가 소유한다.
2. assets DB에 normalized affiliate catalog item table과 sync run tracking을 둔다.
3. affiliate item embedding은 OpenAI text-embedding-3-small 768-dim으로 생성한다.
4. embedding/backfill 대상은 active + in-stock item을 기본으로 하되, deleted/inactive 상태는 DB에 보존하고 검색 대상에서 제외한다.
5. cheap alternative retrieval은 vector similarity로 후보를 넓게 가져온 뒤, price/category/attribute filter와 rerank로 최종 정렬한다.
6. operation DB에는 전체 feed catalog를 넣지 않고, matching 또는 user-facing 노출이 확정된 item만 promotion한다.

Step-by-step implementation breakdown

Step 1. Feed schema and storage

• assets DB에 affiliate catalog staging/normalized table 설계
• merchant, external item id, product title, brand, category, price, currency, availability, URLs, image URLs, raw payload, deleted/is_active fields 정의
• merchant + external_item_id 기준 unique key 정의

Step 2. Sync run tracking

• feed sync run table 설계
• feed type(full/delta), file name, header timestamp, checksum, file size, row counts, error samples, status, started/finished timestamps 저장
• 같은 delta file 중복 처리 방지

Step 3. Full feed bootstrap

• *_mp.txt.gz 다운로드/파싱
• normalized affiliate catalog item upsert
• parse failure row와 reason 기록
• 최초 12k item import 검증

Step 4. Delta feed sync

• *_mp_delta.txt.gz 다운로드/파싱
• Modification=I/U는 idempotent upsert
• Modification=D는 hard delete 대신 is_active=false, deleted=true, deleted_at 처리
• daily sync 기준으로 시작

Step 5. Full reconciliation

• 월 1회 full feed 재수집으로 drift 보정
• full feed에 없는 기존 row는 즉시 삭제하지 않고 inactive/deleted 처리
• inserted/updated/inactivated/error counts를 sync run에 저장

Step 6. Embedding source text builder

• deterministic item search document 생성 함수 구현
• price/URL/SKU/tracking 값은 text에서 제외
• title/brand/category/product type/description/color/gender/material 중심으로 구성
• parser/normalization/text builder 테스트 추가

Step 7. Embedding backfill

• active item 중 embedding이 없거나 source text hash가 바뀐 row만 batch 처리
• OpenAI text-embedding-3-small, dimensions=768 사용
• embedding model, dimensions, source text hash, embedded_at 기록

Step 8. Vector index and retrieval

• assets affiliate catalog item embedding에 pgvector index 생성
• top-k vector candidate retrieval 함수/RPC/API 설계
• 기본 필터: active, in-stock, same broad category, compatible gender/audience

Step 9. Cheap alternative rerank

• candidate price가 source price보다 충분히 낮은지 필터링
• semantic similarity + category match + attribute match + price savings score로 rerank
• exact duplicate/canonical same product 제외

Step 10. Promotion boundary

• retrieval 후보는 assets DB에 유지
• 사용자가 채택하거나 matching이 확정된 item만 operation DB에 idempotent promotion
• assets ↔ operation cross-project FK는 만들지 않음

Step 11. Runbook and verification

• 초기 import 실행법, daily delta sync 실행법, full reconciliation 실행법 문서화
• 검증 항목: row count, duplicate count, active/deleted count, embedding coverage, top-k sanity samples