wantedAI

Tables (21)

approval_gates

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • decision — VARCHAR(20) — NOT NULL — APPROVED/REJECTED
  • comment — TEXT
  • reviewed_by — BIGINT — Foreign key → members.id — NOT NULL
  • reviewed_at — DATETIME — NOT NULL — 실제 컬럼명은 created_at(BaseTimeEntity). API 응답에서 reviewedAt으로 노출
  • updated_at — DATETIME — NOT NULL — 결정은 불변이라 사실상 created_at과 항상 같음

candidate_version_ingredients

  • id — BIGINT — Primary key — NOT NULL
  • candidate_version_id — BIGINT — Foreign key → candidate_versions.id — NOT NULL
  • ingredient_external_id — VARCHAR(100) — 조향 AI 원료 문자열 ID(예: dihydromyrcenol). ingredient 도메인 로컬 미러가 생기기 전까지 내부 FK 대신 임시로 이 값을 그대로 저장
  • ingredient_name — VARCHAR(200) — NOT NULL
  • pyramid — VARCHAR(10) — top/heart/base
  • concentrate_percent — DECIMAL(5, 2)
  • finished_product_percent — DECIMAL(5, 2)
  • price_per_kg — DECIMAL(10, 2)
  • availability — DECIMAL(5, 2)

candidate_versions

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • parent_version_id — BIGINT — Foreign key → candidate_versions.id
  • cost — DECIMAL(15, 2)
  • supply_conditions — JSON
  • generation_rationale — TEXT
  • ai_provider — VARCHAR(50) — 예: modal
  • ai_gpu_used — BOOLEAN
  • ai_response_status — VARCHAR(50) — 예: prototype_ready
  • ai_latency_ms — INT
  • raw_response — TEXT — 조향 AI 응답 원문(JSON) 그대로 보관 — 재현·감사용. temporal_* 필드는 조회 시 이걸 다시 파싱해 노출
  • created_by — BIGINT — Foreign key → members.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

candidates

  • id — BIGINT — Primary key — NOT NULL
  • request_id — BIGINT — Foreign key → fragrance_requests.id — NOT NULL
  • project_id — BIGINT — Foreign key → projects.id — NOT NULL — 요청→프로젝트를 매번 조회하지 않도록 denormalize (evidence 도메인이 증거 보고서 Job 등록 시 필요)
  • created_by — BIGINT — Foreign key → members.id — NOT NULL — 조회 접근 제어 단순화를 위해 denormalize (원래는 request.created_by로만 추적 가능했음)
  • current_version_id — BIGINT — Foreign key → candidate_versions.id
  • status — VARCHAR(30) — NOT NULL — UNDER_REVIEW/CONFIRMED_FOR_EXPERIMENT/IN_SENSORY_TEST/APPROVED/REJECTED
  • job_id — BIGINT — Foreign key → jobs.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

catalog_sync_runs

  • id — BIGINT — Primary key — NOT NULL
  • job_id — BIGINT — Foreign key → jobs.id — NOT NULL
  • project_id — BIGINT — Foreign key → projects.id — NOT NULL — 카탈로그는 전역이지만 권한(FRAGRANCE_RND/ORG_ADMIN)·Job 소속을 위해 보관
  • status — VARCHAR(20) — NOT NULL — PENDING/RUNNING/SUCCEEDED/FAILED/CANCELLED — 저장 시점엔 항상 SUCCEEDED
  • raw_snapshot — TEXT — GET /v1/catalog 응답 원문(JSON). 가공하지 않고 그대로 보관
  • reference_molecule_count — INT — 스냅샷의 reference_molecules
  • safety_screened_count — INT — 스냅샷의 safety_screened
  • prototype_active_count — INT — 스냅샷의 prototype_active_total
  • catalog_version — VARCHAR(50)
  • registry_sha256 — VARCHAR(64)
  • synced_at — DATETIME — NOT NULL
  • requested_by — BIGINT — Foreign key → members.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

evidence_logs

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — NOT NULL
  • candidate_version_id — BIGINT
  • action — VARCHAR(255) — NOT NULL
  • actor_id — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL

evidence_reports

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • job_id — BIGINT — Foreign key → jobs.id
  • status — VARCHAR(20) — NOT NULL — Job 성공 시에만 행이 생성되므로 항상 SUCCEEDED. 진행/실패는 GET /jobs/{jobId}로 확인
  • report_data — TEXT — 후보·버전·안전·예측·감사이력·관능검증을 모은 JSON 번들 (사실상의 보고서 본문)
  • pdf_object_key — VARCHAR(500) — S3 오브젝트 키. URL이 아니다 — 버킷이 비공개라 조회 때마다 15분짜리 서명 URL을 새로 발급
  • requested_by — BIGINT — Foreign key → members.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

experiment_status_logs

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • status — VARCHAR(30) — NOT NULL — UNDER_REVIEW/CONFIRMED_FOR_EXPERIMENT/IN_SENSORY_TEST/APPROVED/REJECTED
  • changed_by — BIGINT — Foreign key → members.id — NOT NULL
  • changed_at — DATETIME — NOT NULL — 실제 컬럼명은 created_at(BaseTimeEntity). API 응답에서 changedAt으로 노출
  • updated_at — DATETIME — NOT NULL — 변경 기록은 불변이라 사실상 created_at과 항상 같음

fragrance_requests

  • id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.id — NOT NULL
  • created_by — BIGINT — Foreign key → members.id — NOT NULL
  • raw_text — TEXT — NOT NULL — 최대 2000자, AI 서비스 brief 제한과 동일
  • product_category — VARCHAR(30) — EAU_DE_PARFUM 등 8종 — 조향 AI product_category와 1:1
  • target_region — VARCHAR(10) — KR/EU/US — 조향 AI target_region과 동일
  • risk_tier — INT — AI 서비스가 1~2만 허용
  • intensity — VARCHAR(20) — LIGHT/MODERATE/STRONG — 화면 표시용, AI로 전달되지 않음
  • longevity — VARCHAR(20) — LOW/MEDIUM/HIGH — 화면 표시용, AI로 전달되지 않음
  • usage_concentration_percent — DECIMAL(5, 2) — 0 초과 30 이하
  • max_ingredient_count — INT — 6~20
  • max_ingredient_price_per_kg — DECIMAL(10, 2) — 0 초과 300 이하
  • accords_csv — VARCHAR(500) — 향조 목록, 콤마 연결 저장
  • status — VARCHAR(20) — NOT NULL — MISSING_FIELDS/DRAFT/CONFIRMED/BLOCKED
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

jobs

  • id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.id — NOT NULL
  • job_type — VARCHAR(30) — NOT NULL — CANDIDATE_GENERATION/CATALOG_SYNC/EVIDENCE_REPORT 사용. REQUEST_STRUCTURING·SUPPLY_IMPACT_ANALYSIS·PREDICTION 미사용(해당 도메인이…
  • status — VARCHAR(20) — NOT NULL — PENDING/RUNNING/SUCCEEDED/FAILED/CANCELLED
  • retryable — BOOLEAN
  • failure_reason — VARCHAR(255)
  • ai_call_queued_at — DATETIME — AI 서비스 호출 직렬화(동시 1개) 대기열 진입 시각
  • ai_call_started_at — DATETIME
  • input_payload — TEXT — 재시도 시 원요청 재구성용 입력값 (job_type별 자유 형식, 예: request_id)
  • result_ref_id — BIGINT — 성공 시 생성된 도메인 리소스 ID (candidate_id 등)
  • created_by — BIGINT — Foreign key → members.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

members

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • created_at — DATETIME — NOT NULL

predictions

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • candidate_version_id — BIGINT — Foreign key → candidate_versions.id — NOT NULL
  • proxy_scores — JSON
  • uncertainty — DECIMAL(5,4)
  • is_out_of_distribution — BOOLEAN
  • abstained — BOOLEAN
  • abstain_reason — VARCHAR(255)
  • created_at — DATETIME — NOT NULL

project_members

  • id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.id — NOT NULL
  • member_id — BIGINT — Foreign key → members.id — NOT NULL
  • role — VARCHAR(20) — NOT NULL — ORG_ADMIN/PROJECT_MANAGER/PERFUMER/FRAGRANCE_RND/PRODUCT_BRAND/SENSORY_SCIENTIST/SAFETY_REVIEWER/SUPPLIER/AUDITOR — enu…
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

projects

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL — 최대 100자
  • description — VARCHAR(500) — 최대 500자, nullable
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

refresh_tokens

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → members.id — NOT NULL
  • token_hash — VARCHAR(64) — NOT NULL — SHA-256 해시. 원문은 저장하지 않음
  • expires_at — DATETIME — NOT NULL
  • revoked_at — DATETIME — null=활성. 회전(rotation)·재사용 탐지·비밀번호 변경 시 채워짐
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

safety_evaluations

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • candidate_version_id — BIGINT — Foreign key → candidate_versions.id — NOT NULL
  • passed — BOOLEAN — NOT NULL
  • result_detail — JSON
  • evaluated_at — DATETIME — NOT NULL

sensory_test_results

  • id — BIGINT — Primary key — NOT NULL
  • sensory_test_id — BIGINT — Foreign key → sensory_tests.id — NOT NULL
  • result_data — TEXT — 패널·측정 방식이 도구마다 달라 구조 미강제. 제출된 JSON을 문자열로 그대로 저장
  • correlation_with_prediction — DECIMAL(5, 4) — 백엔드가 계산하지 않는다 — 감각과학 담당자가 직접 산출해 제출한 값
  • recorded_by — BIGINT — Foreign key → members.id — NOT NULL
  • recorded_at — DATETIME — NOT NULL — 실제 컬럼명은 created_at(BaseTimeEntity). API 응답에서 recordedAt으로 노출
  • updated_at — DATETIME — NOT NULL

sensory_tests

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • plan_detail — TEXT
  • status — VARCHAR(20) — NOT NULL — PLANNED → (첫 결과 등록 시) COMPLETED
  • planned_by — BIGINT — Foreign key → members.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

supply_change_affected_candidates

  • id — BIGINT — Primary key — NOT NULL
  • supply_change_id — BIGINT — Foreign key → supply_changes.id — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • candidate_version_id — BIGINT — Foreign key → candidate_versions.id — NOT NULL — 영향 원료를 참조하던 버전 (등록 시점 스냅샷)
  • ingredient_concentrate_percent — DECIMAL(5, 2) — 그 조향식에서 이 원료의 농축액 기준 배합비율(%), AI 응답값 그대로
  • review_status — VARCHAR(20) — NOT NULL — PENDING_REVIEW/REVIEWED
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

supply_changes

  • id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.id — NOT NULL
  • ingredient_external_id — VARCHAR(100) — NOT NULL — 조향 AI 원료 문자열 ID (candidate_version_ingredients와 동일 체계). FK 없음
  • change_type — VARCHAR(30) — NOT NULL — PRICE_INCREASE/PRICE_DECREASE/DISCONTINUED/LEAD_TIME_INCREASE/SUPPLY_RESTORED/OTHER
  • previous_price_per_kg — DECIMAL(10, 2)
  • new_price_per_kg — DECIMAL(10, 2)
  • note — TEXT
  • analysis_status — VARCHAR(20) — NOT NULL — 스키마 호환용. 영향 분석은 등록 시 동기 수행 → 항상 SUCCEEDED. SUPPLY_IMPACT_ANALYSIS Job 미사용
  • affected_candidate_count — INT — NOT NULL
  • reported_by — BIGINT — Foreign key → members.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

supply_review_decisions

  • id — BIGINT — Primary key — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidates.id — NOT NULL
  • supply_change_id — BIGINT — Foreign key → supply_changes.id — nullable — 특정 공급 변경과 연결되면 해당 impact 행을 REVIEWED로 전이
  • decision — VARCHAR(30) — NOT NULL — KEEP_FORMULA/REVISE_FORMULA/DISCARD_CANDIDATE
  • rationale — TEXT — NOT NULL
  • decided_by — BIGINT — Foreign key → members.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL