영유아 식단 적재 → 도감 재정의 파이프라인 (런북)

📌 호출법 — 식단 파일을 한 폴더에 모은 뒤, 이 문서 링크를 주면서 "이 파이프라인 돌려줘"라고 하면 된다. 그러면 ① 파싱·정제·집계 → ② 승격후보 안전스크린 → ③ SQL 반영(롤백 스냅샷 포함)을 순서대로 실행한다. 파일은 .xlsx / .csv가 가장 깨끗하고, 여러 센터·여러 달 섞여 있어도 된다.
↓ 이 zip 배치는 3경로 중 경로1 — 전체 인입·중복제거·빈도 운용은 바로 아래 「3경로 운용전략」 참고.

🔀

3경로 인입 + 데이터 운용전략 검토요망 · 2026-06-20

식단 데이터가 들어오는 길이 3개다. 아래 ①~⑤ 배치 런북은 그중 경로1(관리자 zip)의 상세일 뿐 — 셋을 한 곳(institution_menus)으로 수렴하고 (기관,월) 멱등키로 중복 제거하는 게 운용의 핵심.

🔑 단일 원칙 — 모든 경로를 {institution_id, month, date, slot, menus[], ingredients[], source}로 정규화하고 dedup 키 = (기관, 월). OCR 직후 (기관,월) 확정 → 이미 있으면 집계 스킵 + 기존 ingredients 즉시 반환(재OCR·재분해·재집계 0), 없으면 분해·적재. 기관 미상(집밥·매칭 실패)은 집계 제외, 개인 기록으로만.

경로	입력	적재 테이블	dedup	기관 연결
① zip (관리자)	식단표.zip(급식관리지원센터)	CSV→도감 (+선택 `institution_menus`=`batch_import`)	파일=센터-월	파일명/내용 센터명
② daycare-eval	daycare-eval.html 업로드	`institution_menus`+items+scores	✅ (기관,월) UNIQUE upsert	명시 institution_id
③ 부모 기록탭	care OCR/수기	`meal_logs` (place=daycare·source=daycare_menu·ocrMonth)	△ `(institution_id, ocrMonth)`로 도출 가능	`children.institution_id` (InstitutionSelect·온보딩+기록탭)

※ 경로2는 이미 (institution_id,month) 멱등 upsert. source에 batch_import가 있어 경로1도 같은 테이블로 흘릴 수 있고, children.institution_id(디렉터리 선택·불변 FK·institutions 표준데이터)가 있어 경로3도 (기관,월)로 환원 가능 → 3경로 → institution_menus 수렴이 정답.

⚠️ 경로3 전제 — 기관 등록 필수화 — children.institution_id가 현재 nullable(optional·?? null)이라 부모가 건너뛰면 (기관,월) 키가 안 나온다. 식단표 저장("기관 급식으로 저장") 전 기관 등록을 요구(미등록이면 InstitutionSelect로 유도)해야 경로3이 dedup·집계에 합류. 그리고 경로3의 daycare_menu는 개인 meal_logs뿐 아니라 institution_menus로도 수렴(기존 (기관,월) 있으면 기존 분해 반환).

빈도는 두 층으로 분리 (가장 중요)

baseline (youa-freq)

소스: 경로1 + 누적 institution_menus
단위: (기관-월) 1벌당 등장률
용도: 도감 2축 등급 → 코칭 노출 우선순위 (모집단 기준·안정)

live observed

소스: 최근 N개월 institution_menus(+옵션 개인)
단위: (기관-월) dedup 후 동률안전 상위%
용도: 추천 랭킹 동률가산 (라이브 자가보정)

⚠️ warm 정정 포인트 — 런타임 freq warm이 meal_logs/institution_menu_items의 '행'을 직접 카운트하면 인기 어린이집이 부모 수만큼 과대계상. → (기관,월) dedup 후 집계로 바꿔야 정확(이사님 dedup 규칙의 집계측 적용).

정제 · 배치 운용

정제(3경로 공통) — parse_and_aggregate의 CANON(쌀→멥쌀·콩→콩(대두)·요거트→요구르트…)+SEASON 제외+노이즈컷을 단일 사전으로. 메뉴→식재료 분해는 menuMap+learned_menus 캐시 = 공통 분해기(중복 분해·비용 방지).
경로1(zip) — 온디맨드 CLI(폴더 줄 때). 기존 3단계 유지 + 선택적 institution_menus(batch_import) 적재.
경로2·3(라이브) — 적재=실시간(멱등 게이트). 집계=새벽 coach 크론 1회: (a) 증분 baseline(새 기관-월만)→youa_pct, (b) live observed 런타임 warm(재배포0·정적파일 무변경=I-01-9 안전).
멱등 — 같은 (기관,월) 재실행해도 결과 불변.

잠그고 갈 결정 3가지

1. 경로1 귀속 — ⓐ baseline 집계 전용 (권장) / ⓑ per-어린이집 institution_menus까지 (센터=다수 어린이집이라 ⓐ가 깔끔)
2. 추천 빈도원 — ⓐ baseline(youa)만 / ⓑ baseline + live observed warm(dedup 버전으로 수정) (권장) / ⓒ live 보류
3. 경로3 집밥(home) — ⓐ제외(급식만) (권장) / ⓑ포함(가정 노출 반영)

✅ 확정: 1-ⓐ / 2-ⓑ / 3-ⓐ + 기관 필수화 — warm (기관,월) dedup 적용 완료(코드 라이브) · 멱등 적재 게이트 + 기관 필수화 구현 진행.

📊

데이터 변환 흐름 — 식단표 1장 따라가기 (end-to-end)

예: ○○어린이집 2026-06 식단표 사진 1장(점심 = 흑미밥·소고기무국·순두부조림·배추김치·오이무침)이 어떤 모듈·외부 API를 거쳐 어떤 DB에 어떤 형태로 저장되는지.

범례 외부 OCR외부 LLMDB·Storage 우리 모듈 code 🍚 = 예시 "소고기무국" 추적

입력식단표 1장 — 3경로

① zip(관리자) · ② daycare-eval 업로드 · ③ 부모 기록탭. 🍚 사진 속 "소고기무국"

▼

S1OCR 전사 (사진 → 글자)Naver CLOVA OCR V2Supabase Storage

app/api/ocr/route.ts — 원본 사진을 eval-uploads 버킷 저장 → CLOVA 표 인식(실패 시 일반 OCR 폴백·회전 보정) → raw 텍스트.

📦 ocr_logs (감사) — image_url · ocr_text · model · input/output_tokens

▼

S2메뉴 추출 + 이름 교정Claude Sonnet 4.6 · Vision

OCR 텍스트 + 원본 이미지(날짜 격자) → JSON schema 강제 → items[{date, slot, menu}]. 오인식 교정(돈까스→돈가스), 알레르기기호(①②) 제거, 한 칸=한 메뉴.
🍚 → {date:"2", slot:"점심", menu:"소고기무국"}

▼

S3메뉴 → 식재료 분해·정제DeepSeek V4-Flash폴백 Claude Haiku

meal/parse + menuMapCore — 4단 폴백(앞쪽일수록 무료·즉시):

0 learned 캐시 → 1 menu-dict 3천+ → 1b 부분일치 → 2 substring 스캔 → 3 LLM

표준명 정규화(lexicon: 달걀→계란) + canon/CANON_VOCAB 검증(양념 제외·환각 차단).
🍚 소고기무국 → [소고기, 무] · 순두부조림 → [두부] · 흑미밥 → [쌀]

📦 learned_menus (사전 캐시·멱등 upsert) — menu PK · ingredients[] · hits++ → 다음부터 무료

▼ 경로별 최종 저장 ▼

②③ 기관institution_menus

api/institution/menu + institutionScore — buildMenuItemRows(분해) + computeDiversityScore(영양 0~100) + DeepSeek 한줄총평.

📦 institution_menus — (기관,월) 1벌
📦 institution_menu_items — {menu_date, slot, menus[], ingredients[]}
📦 institution_scores — score · red_groups[] · summary

① 부모meal_logs

care/page.tsx · saveDaycareMenu — 우리 아이 끼니로 저장(미래날짜 차단·부모 입력 보호).

📦 meal_logs — {child_id, log_date, slot, menus[], ingredients[], place:'daycare', source:'daycare_menu'}

▼ 새벽 크론 · 배치(다운스트림) ▼

S5집계 · 백필 · 도감 갱신결정론

• remapMenus.backfillUnmappedMenus — 인입 때 못 푼 끼니만 LLM 백필 + learned 학습
• warmIngredientFreqFromSql — institution_menu_items를 (기관,월) dedup → 급식 빈도 메모리캐시 → 추천 리밸런싱
• (경로1 zip 배치) parse_and_aggregate + apply_dogam_redefine → ingredients 도감 2축 재등급(↑ 위 시각화)

외부 API

CLOVA OCR V2 사진→글자(표 인식)
Claude Sonnet 4.6 이미지→메뉴 추출·교정
DeepSeek V4-Flash 메뉴→식재료·기관 총평 (폴백 Claude Haiku 4.5)
Supabase Storage(사진)·PostgreSQL(전 테이블)
시드: 공공데이터 표준데이터·NEIS → institutions·elem_count

최종 저장 테이블

institution_menu_items — 기관 식단(날짜·끼니·메뉴[]·식재료[])
institution_menus·institution_scores — 기관-월 1벌·점수
meal_logs — 부모 개인기록(아이별)
learned_menus — 메뉴→식재료 사전 캐시
ingredients — 도감(등급) · ocr_logs — 감사

🌙

야간 통합 배치 파이프라인 설계·검토요망

3경로 인입 데이터를 ocr_logs 한 큐로 모으고(멱등), 밤에 크론이 한 번에 정제→학습→도감→영양→공출현까지 돌린다.

🔑 ocr_logs = 3경로 통합 인입 큐 — zip·daycare-eval·부모기록탭 모두 OCR/파싱 직후 ocr_logs에 적재(source·institution_id·month·child_id·items[]·processed=false). 멱등: insert 전 (institution_id, month) 존재 체크 → 있으면 스킵(재OCR·재처리 0, 기존 결과 재사용).

큐ocr_logs (미처리 processed=false)

3경로 인입분이 한 곳에. 야간 크론이 WHERE processed=false로 픽업.

▼ 야간 크론(단일 처리기) ▼

1메뉴 정제 → learned_menusDeepSeek 미해결분

canon/표준명 → menuMap(dict·rule·scan) → 미해결 LLM 분해 → CANON_VOCAB 검증(양념 제외·환각 차단) → learned_menus 학습(다음부터 무료).

▼

2최종 테이블 식재료 backfill

institution_menu_items · meal_logs의 빈 ingredients[] 채움. 현재 remap은 meal_logs만 → institution_menu_items 갭 메움(신규).

▼

3처음 보는 식재료 → 도감 + 영양✅ enrich 크론 기존

신규 식재료를 enrich_queue에 큐잉 → enrich 크론(KST 04시)이 DeepSeek 분류 + 영양 메타 → ingredients 등재.

⚠️ 안전 게이트 유지 — 영유아 안전 스크린(질식·한방·나트륨·알레르기)+사람 검토는 별도(dogam-promotion). 위험군은 무조건 등재 금지, 보류 큐로.

▼

4공출현 네트워크 업데이트🆕 야간화 필요

새 (기관-월) 식단의 식재료 동시출현 → ingredient_edges 증분 갱신(사촌·궁합). 현재 오프라인(gen-food-graph)만 — 크론은 warm read뿐. 야간 증분 생성은 신규 빌드.

▼

5메모리 warm + 마킹✅ 기존

warmGraphFromSql(그래프) + warmIngredientFreqFromSql((기관,월) 빈도 리밸런싱) → processed=true 마킹. 멱등(재실행 동일).

✅ 이미 있는 것(재사용)

enrich_queue→enrich 크론(04시): 식재료 분류+영양 메타→ingredients
warmGraphFromSql·warmIngredientFreqFromSql(야간 read·빈도)
meal/parse 4단 폴백·learned_menus 캐시·remap(meal_logs 백필)

🆕 새로 만들 것

ocr_logs 큐 스키마 확장 + 3경로 적재 + (기관,월) 멱등
야간 통합 처리기(큐 픽업→정제→learned→backfill→enrich_queue 큐잉)
institution_menu_items 식재료 backfill(현재 갭)
공출현 야간 증분 생성(gen-food-graph 포팅/증분)

1

입력 — 어떤 파일을 주면 되나

폴더 하나에 모아서 경로만 알려주면 폴더째 읽는다. 형식별 처리 난이도:

형식	처리	권장
.xlsx (발주량산출서·식단표) / .csv	헤더 자동감지 파싱(음식명·식재료명 컬럼)	⭐ 최고
.zip (xlsx 묶음)	내부 xlsx 전부 파싱	⭐
.hwp (한글)	직접 불가 → 엑셀로 변환 후 투입	△
PDF(이미지) · 식단표 사진	OCR(Sonnet Vision) 별도 → csv화 후 투입	▽

※ 깨끗한 구조화 전국 식단 공개 API는 없음(식약처 어린이/유아 식단 API 15118065·15118061 = 삭제됨, NEIS는 요리명만, info.childcare·구청은 HWP). 그래서 받은 파일을 직접 적재하는 이 경로가 현실적 최선.

2

파이프라인 3단계

결정론(파싱·집계·SQL) + LLM(승격후보 안전스크린)만 멀티에이전트. 도감 이름은 큐레이션돼 노이즈와 무관.

S1파싱 · 정제 · 빈도집계 · 2축 재정의안결정론

python parse_and_aggregate.py --in ~/Desktop/menus --out /tmp/menu_out
헤더 자동감지로 음식명·식재료명 컬럼 정렬(컬럼 어긋남 노이즈 해결) · bare-xlsx/zip 분기 · 식재료 표준화·양념 제외 · 노이즈 컷(숫자/날짜/알레르기기호①②/브랜드) · 파일단위 등장률 집계 · SQL ingredients.elem_count(초등축)와 조인.
산출 → dogam_redefine.csv(사람 1회 검토) · promo_candidates.json(승격후보)

▼

S2승격후보 안전스크린 — 멀티에이전트LLM · 적대검증

Workflow dogam-promotion-safety-screen 에 promo_candidates.json 투입. 후보별: 분류(원물/가공/음료/양념/국물) + 영유아 2~6세 안전(질식·한방약성·나트륨·알레르기) + 승격판정 적대 재검증.
산출 → screen_result.json (result.promoted = 승격확정). 가공·음료·양념·국물·안전결격은 빈출해도 기각.

▼

S3SQL 반영 — 롤백 스냅샷 자동 저장결정론

python apply_dogam_redefine.py --redefine dogam_redefine.csv --promoted screen_result.json (--dry 미리보기)
① ingredients_snapshot_<날짜>.json 저장(롤백) ② 도감 2축 재등급(meta + 상향·신규 grade 갱신) ③ 승격확정 tier=dogam 추가. 하향검토는 자동변경 안 함(meta 플래그만 — 사람 검토).

3

2축 등급 기준

영유아축 (본 수집)

등장률 = 그 식재료가 등장한 파일 비율
매일군 ≥70% · 자주 ≥35% · 가끔 ≥10% · 드묾

초등축 (SQL elem_count · NEIS)

초등매일 ≥300 · 자주 ≥100 · 가끔 ≥10 · 드묾

결합등급 = 둘 중 높은 쪽 → 코칭 노출 우선순위
연령플래그 = 공통 / 영유아특이(초등엔 적은데 영유아 매일: 계란·브로콜리·기장·두유) / 초등특이
등재 자격 = 단일 원물 식재료 + 영유아 안전 통과. 빈출도는 후보선정 기준일 뿐, 등재 기준 아님(가공·음료·양념·국물·안전결격은 빈출해도 탈락)

🖼 "도감 2축 재정의 SQL 반영" 이란 — 한눈에

① 영유아축 수집 식단(본 파이프라인)
등장률 = 식재료가 등장한 기관-월 비율
매일 ≥70% · 자주 ≥35% · 가끔 ≥10% · 드묾

② 초등축 SQL ingredients.elem_count(NEIS)
등장 횟수 기준
매일 ≥300 · 자주 ≥100 · 가끔 ≥10 · 드묾

▼ 두 축 합치기

③ 결합등급 = MAX(영유아, 초등) + 연령플래그(공통/영유아특이/초등특이)
예) 계란 = 영유아 매일 × 초등 가끔 → 결합 매일 · 영유아특이

▼ SQL 반영 apply_dogam_redefine.py

1. 스냅샷
ingredients_snapshot_<날짜>.json 저장 — 롤백 안전망

2. PATCH
ingredients(도감) 행별 grade·meta 갱신 — 상향·신규만, per row

3. 승격
안전스크린 통과분 tier=dogam 추가(신규 등재)

→ 결과: ingredients(도감) 테이블의 식재료 등급(grade)이 '영유아 실측 빈도'까지 반영해 갱신됨. 이 등급이 곧 코칭 노출 우선순위. 하향은 자동 안 함(meta 플래그만·사람 검토) · 같은 식단 다시 돌려도 멱등.

4

함정 · 교훈 (하드코딩된 주의)

컬럼 어긋남 — 센터마다 XLSX 레이아웃 달라 식재료칸에 숫자/날짜/음식명이 샌다. detect_cols가 헤더('음식명'/'식재료명')로 컬럼을 자동 정렬해 해결.

bare-xlsx vs zip — 발주량산출서는 bare xlsx인데 PK매직이라 zip로 오인. xl/workbook.xml 유무로 분기.

PostgREST 벌크 upsert 금지 — 부분 키 merge-duplicates는 NOT NULL 위반(23502). 기존행 수정은 반드시 PATCH per row.

롤백 / 한글필터 — apply가 스냅샷 저장(복원은 그 grade로 PATCH). URL 필터에 한글·이모지 들어가면 quote 필요.

5

현재 상태 · 남은 일

6

수집 센터

383,607

식재료 행

6,121

고유 메뉴

157

도감 재등급

+12

신규 승격

2026-06-15 dietary4u 6센터(강서·중랑·울산동구·동작·강동·노원) 적재 → 도감 2축 재정의 SQL 반영(ingredients 393→395)
승격 12: 새송이·애느타리버섯 · 치커리·유채·개비름·돌나물·비타민채·고려엉겅퀴(잎채소) · 꽃양배추·미니파프리카·무말랭이 · 감귤
미완 ① 푸드체이닝 적재(learned_menus·ingredient_edges) — 노이즈 정제 후
미완 ② 하향검토 10(미나리·콩·대구·달래·고추·순무·갈치·쑥·더덕·조개) — 사람 검토

산출 구글시트: 센터별 수집현황 · 도감 재정의안(2축) — 메모리 2026-06-15-dogam-redefine-dietary4u 참조.