4) ERD & 데이터 흐름

💡 쉽게 말하면: 데이터를 어떤 표에 어떻게 나눠 담는지, 그리고 국립국악원 API에서 받아온 음원이 어떤 순서로 DB에 들어가 검색 가능해지는지를 정리했습니다.

10-1. MVP ERD — 4개 물리 테이블

erDiagram
    jangdan ||--o{ gugak_tracks : "장단 정의"
    gugak_tracks ||--o{ track_emotion_tags : "감성 태그"
    gugak_tracks ||--o{ match_logs : "매칭 이력"

    jangdan {
        varchar50 name PK "자진모리·중모리·굿거리·휘모리·세마치·엇모리"
        int loop_unit_beats "12·12·12·4·6·10"
    }
    gugak_tracks {
        uuid id PK
        varchar200 title
        varchar100 artist
        varchar50 instrument
        varchar50 jangdan_name FK
        int bpm
        jsonb cue_points "의도적 비정규화"
        text audio_url
        varchar20 public_license_type "KOGL_1 | KOGL_2"
        text description_ko
        text description_en
        vector1536 embedding "pgvector"
        timestamptz created_at
    }
    track_emotion_tags {
        uuid id PK
        uuid track_id FK
        varchar20 emotion_tag
        int sort_order "0 = primary_emotion"
    }
    match_logs {
        uuid id PK
        text input_text
        varchar5 lang
        uuid matched_track_id FK
        float similarity_score
        timestamptz created_at
    }

10-2. 테이블별 설계 근거

`jangdan` — 마스터 (3NF)

컬럼	타입	비고
`name`	`VARCHAR(50)` PK	장단명 자체가 자연키
`loop_unit_beats`	`INTEGER`	루프 단위(박)

시드 6건은 마이그레이션과 db_init에서 삽입합니다. 루프 단위를 트랙마다 저장하지 않는 이유는 정규화 설계 §5-3에 있습니다.

`gugak_tracks` — 메인

컬럼	특기사항
`jangdan_name`	`jangdan.name` FK — 컬럼명이 `jangdan`이 아니라 `jangdan_name`
`cue_points`	JSONB, MVP는 `[]` — WaveSurfer 전달 전용 비정규화
`embedding`	`Vector(1536)`, nullable — 임베딩 파이프라인이 나중에 채움
`public_license_type`	`KOGL_1` / `KOGL_2` 문자열
~~`emotion_tags`~~	v4.0에서 제거 (ARRAY, 1NF 위반)
~~`loop_unit_beats`~~	v4.0에서 제거 (3NF 위반)

`track_emotion_tags` — 감성 (1NF)

컬럼	비고
`track_id`	`ON DELETE CASCADE` — 트랙 삭제 시 태그도 삭제
`emotion_tag`	인덱스 `ix_track_emotion_tags_tag`
`sort_order`	순서 보존, `0`이 대표 감성

인덱스 두 개(track_id, emotion_tag)로 조회와 필터 양쪽을 커버합니다.

`match_logs` — 이력

similarity_score가 Float입니다(v4.0 타입 수정). 로그 저장 실패는 사용자 응답을 막지 않도록 Interactor에서 예외를 삼킵니다.

10-3. v1.1 이후 확장 테이블

테이블	마일스톤	용도
`users`	v1.1	회원 (이메일 · Google OAuth)
`email_verifications`	v1.1	이메일 인증 토큰
`saved_tracks`	v1.1	저장한 트랙
`download_logs`	v1.1	샘플 다운로드 이력

FK 참조 순서 때문에 ORM import 순서가 정해져 있습니다.

# db_init.py / alembic env.py
# [MVP] FK 참조 순서: jangdan → track → emotion_tag → match_log
# [v1.1] user_orm, saved_track_orm, download_log_orm, email_verification_orm

10-4. 데이터 적재 흐름 (배치)

flowchart TB
  A["국립국악원 API<br/>phrasedataview2/view (XML)"] -->|"collect_gugak_500.py<br/>page 순회 · sleep 0.4s"| B["gugak_500.csv<br/>500행"]
  B -->|"Collect_tracks.py<br/>--dry-run 검증 후 적재"| C["파생 규칙 적용<br/>jangdan · bpm · license · emotions"]
  C --> D[("gugak_tracks<br/>+ track_emotion_tags")]
  D -->|"embed_pipeline.py<br/>배치 20건 · sleep 0.5s"| E["Gemini 임베딩"]
  E --> F[("gugak_tracks.embedding<br/>Vector")]
  F --> G["DISCOVER 검색 가능"]

단계	스크립트	재실행 안전성
수집	`collect_gugak_500.py`	페이지 단위 진행
적재	`Collect_tracks.py`	`(phrase_cd, title)` 기존 키 스킵
임베딩	`embed_pipeline.py`	임베딩 없는 트랙만 조회 (이어받기)
검증	`check_schema.py`	스키마·건수 확인
초기화	`reset_neon_schema.py`	개발 중 스키마 리셋

10-5. 마이그레이션

# 최초 마이그레이션 생성
alembic revision --autogenerate -m "init soundbridge tables"

# NeonDB main 브랜치에 적용
alembic upgrade head

# 롤백
alembic downgrade -1

001_init_soundbridge_tables.py가 하는 일:

CREATE EXTENSION IF NOT EXISTS vector — 테이블 생성보다 먼저
jangdan 생성 + 시드 6건 INSERT
gugak_tracks 생성 (FK → jangdan.name)
track_emotion_tags 생성 + 인덱스 2개
match_logs 생성

downgrade()는 역순으로 드롭합니다(match_logs → 인덱스 → track_emotion_tags → gugak_tracks → jangdan). FK 때문에 순서를 지키지 않으면 실패합니다.

pgvector 확장은 마이그레이션 맨 앞에서 켜야 합니다. Vector 컬럼이 있는 테이블을 먼저 만들면 타입을 찾지 못해 실패합니다.

10-6. 조회 쿼리 패턴

트랙 상세 (joinedload 필수)

select(GugakTrackOrm)
  .options(joinedload(GugakTrackOrm.emotion_tag_rows),
           joinedload(GugakTrackOrm.jangdan_rel))
  .where(GugakTrackOrm.id.in_(track_ids))
# → result.unique().scalars().all()

감성 필터 (JOIN)

query.join(TrackEmotionTagOrm, TrackEmotionTagOrm.track_id == GugakTrackOrm.id) \
     .where(TrackEmotionTagOrm.emotion_tag.in_(emotions)) \
     .distinct()

루프 단위 필터 (JOIN)

query.join(JangdanOrm, JangdanOrm.name == GugakTrackOrm.jangdan_name) \
     .where(JangdanOrm.loop_unit_beats == loop_unit)

벡터 유사도

SELECT id FROM gugak_tracks ORDER BY embedding <=> :vec LIMIT :k

10-7. 인덱스 현황과 계획

인덱스	대상	상태
PK	전 테이블	✅
`ix_track_emotion_tags_track_id`	`track_emotion_tags.track_id`	✅
`ix_track_emotion_tags_tag`	`track_emotion_tags.emotion_tag`	✅
FK 인덱스	`gugak_tracks.jangdan_name`	미생성 — 장단 6종뿐이라 선택도가 낮아 효용 제한적
HNSW	`gugak_tracks.embedding`	v1.2 계획 — 500건 규모에서는 순차 스캔으로 충분
복합 인덱스	`(instrument, bpm)`	필터 사용 패턴 관측 후 판단

10-8. 알려진 정합성 이슈

이슈	영향
`embedding` 차원 1536 vs `embed_pipeline.py` 768	임베딩 적재 실패 — 선결 과제
`embed_pipeline.py`가 참조하는 `track_embeddings` 테이블	현재 스키마에는 없음(임베딩은 `gugak_tracks.embedding` 컬럼). 스크립트를 현 스키마에 맞춰 수정 필요
감성 태그 어휘	수집 규칙이 `EmotionTag` enum 밖 값 생성 → 필터가 잡지 못함
장단 폴백 편중	대부분 `중모리`로 적재되어 장단·루프 필터의 변별력 저하

이 네 가지는 부록 · 핵심 기술 요약의 잔여 과제 표에도 함께 정리해 두었습니다.