sfa

Tables (22)

ai_report_jobs

  • job_id — VARCHAR(64) — Primary key — NOT NULL — UUID
  • user_id — VARCHAR(16) — Foreign key → users.user_id — NOT NULL
  • query_text — VARCHAR(500) — NOT NULL
  • status — ENUM('QUEUED', 'RUNNING', 'DONE', 'FAILED') — NOT NULL
  • narrative — TEXT — markdown 분석 요약
  • charts — JSON — {type, title, data}
  • fmea — JSON — {failure_mode, effect, cause, S, O, D, rpn, action}
  • requested_at — DATETIME — NOT NULL
  • started_at — DATETIME
  • completed_at — DATETIME
  • error_message — VARCHAR(500)

ai_report_pdfs

  • report_id — BIGINT — Primary key — NOT NULL
  • job_id — VARCHAR(64) — Foreign key → ai_report_jobs.job_id — NOT NULL
  • pdf_path — VARCHAR(255) — NOT NULL
  • generated_by — VARCHAR(16) — Foreign key → users.user_id — NOT NULL
  • generated_at — DATETIME — NOT NULL
  • download_count — INT — NOT NULL

ai_suggestions

  • suggestion_id — BIGINT — Primary key — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • parameter_tag — VARCHAR(64) — NOT NULL — 제어 대상 태그 (예: FURN_01_TEMP)
  • current_value — DOUBLE
  • suggested_value — DOUBLE
  • yield_impact — DOUBLE — 0~1 수율 영향도
  • confidence — DOUBLE — 0~1 모델 신뢰도
  • status — ENUM('PENDING', 'APPLIED', 'REJECTED', 'EXPIRED') — NOT NULL
  • valid_until — DATETIME — 만료 시각
  • generated_at — DATETIME — NOT NULL

alarms

  • alarm_id — BIGINT — Primary key — NOT NULL
  • source_type — ENUM('EQP', 'ENV') — NOT NULL
  • equipment_param_id — BIGINT — Foreign key → equipment_parameters.param_id — source_type=EQP일 때
  • environment_param_id — BIGINT — Foreign key → environment_parameters.param_id — source_type=ENV일 때
  • occurred_at — DATETIME — NOT NULL
  • severity — ENUM('INFO', 'WARN', 'ERR') — NOT NULL
  • message — VARCHAR(255)
  • status — ENUM('NEW', 'ACK', 'IN_PROGRESS', 'DONE') — NOT NULL
  • ack_user_id — VARCHAR(16) — Foreign key → users.user_id
  • ack_at — DATETIME
  • start_user_id — VARCHAR(16) — Foreign key → users.user_id
  • start_at — DATETIME
  • done_user_id — VARCHAR(16) — Foreign key → users.user_id
  • done_at — DATETIME
  • last_occurred_at — DATETIME — dedup: 동일 source 재발생 시 갱신
  • occurrence_count — INT — NOT NULL
  • INDEX — idx_alarm_status
  • INDEX — idx_alarm_occurred

audit_logs

  • id — BIGINT — Primary key — NOT NULL
  • user_id — VARCHAR(16) — Foreign key → users.user_id — 비로그인 이벤트는 NULL 가능
  • action — VARCHAR(64) — NOT NULL — LOGIN_SUCCESS / LOGIN_FAIL / REGISTER / PW_CHANGE / LOCK 등
  • target_type — VARCHAR(32) — 대상 리소스 타입 (USER, ALARM, SUGGESTION 등)
  • target_id — VARCHAR(64)
  • detail — JSON
  • occurred_at — DATETIME — NOT NULL

email_verifications

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(128) — NOT NULL
  • code — VARCHAR(6) — NOT NULL — 6자리 숫자
  • purpose — ENUM('SIGNUP', 'FIND_ID', 'FIND_PW') — NOT NULL
  • expires_at — DATETIME — NOT NULL — 발급 후 3분
  • used — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

environment_measurements

  • id — BIGINT — Primary key — NOT NULL
  • param_id — BIGINT — Foreign key → environment_parameters.param_id — NOT NULL
  • measured_value — DOUBLE — NOT NULL
  • measured_at — DATETIME(3) — NOT NULL
  • INDEX — idx_env_meas_param_at

environment_parameters

  • param_id — BIGINT — Primary key — NOT NULL
  • sensor_id — VARCHAR(16) — Foreign key → environment_sensors.sensor_id — NOT NULL
  • tag_code — VARCHAR(64) — NOT NULL — 예: ENV_TEMP_01_VAL
  • tag_name — VARCHAR(64)
  • unit — VARCHAR(16)
  • normal_min — DOUBLE
  • normal_max — DOUBLE
  • abnormal_condition — VARCHAR(255)
  • collection_period — VARCHAR(8)
  • data_type — ENUM('Float', 'Int', 'Bool')
  • param_category — ENUM('MEASUREMENT', 'STATUS', 'ALARM')

environment_sensors

  • sensor_id — VARCHAR(16) — Primary key — NOT NULL — ENV_TEMP_01 등
  • sensor_type — ENUM('PARTICLE', 'TEMP', 'HUMIDITY', 'AMC', 'VIBRATION', 'ESD') — NOT NULL
  • zone_code — ENUM('FRONT', 'BACK', 'EQP') — NOT NULL
  • sensor_name — VARCHAR(64)
  • standard_ref — VARCHAR(64) — ISO 14644-1 / SEMI F21 등

equipment_measurements

  • id — BIGINT — Primary key — NOT NULL
  • param_id — BIGINT — Foreign key → equipment_parameters.param_id — NOT NULL
  • measured_value — DOUBLE — NOT NULL
  • measured_at — DATETIME(3) — NOT NULL
  • INDEX — idx_eq_meas_param_at

equipment_parameters

  • param_id — BIGINT — Primary key — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • tag_code — VARCHAR(64) — NOT NULL — 태그명 1:1 매핑 (예: FURN_01_TEMP)
  • tag_name — VARCHAR(64) — NOT NULL
  • unit — VARCHAR(16)
  • normal_min — DOUBLE
  • normal_max — DOUBLE
  • abnormal_condition — VARCHAR(255)
  • collection_period — VARCHAR(8) — 1s / 5s / 1m / lot
  • data_type — ENUM('Float', 'Int', 'Bool')
  • param_category — ENUM('PROCESS', 'OEE', 'STATUS')
  • is_controllable — BOOLEAN — NOT NULL — AI 제어 대상 여부

equipments

  • equipment_id — VARCHAR(16) — Primary key — NOT NULL — FURN_01 ~ PROBE_03
  • equipment_name — VARCHAR(64) — NOT NULL
  • step_no — VARCHAR(2) — Foreign key → processes.step_no — NOT NULL
  • unit_no — TINYINT — NOT NULL — 1 / 2 / 3

manager_notes

  • note_id — BIGINT — Primary key — NOT NULL
  • user_id — VARCHAR(16) — Foreign key → users.user_id — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • note_text — VARCHAR(500) — NOT NULL
  • created_at — DATETIME — NOT NULL

oee_metrics

  • id — BIGINT — Primary key — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • period_start — DATETIME — NOT NULL — 1분 집계 구간 시작 시각
  • availability — DOUBLE — STATUS=1 누적시간 / 계획가동시간
  • performance — DOUBLE — (이상CT × PROD_COUNT) / 실제가동시간
  • quality — DOUBLE — (PROD_COUNT - NG_COUNT) / PROD_COUNT
  • oee — DOUBLE — availability × performance × quality
  • computed_at — DATETIME — NOT NULL
  • INDEX — idx_oee_eq_period

optimization_actions

  • action_id — BIGINT — Primary key — NOT NULL
  • suggestion_id — BIGINT — Foreign key → ai_suggestions.suggestion_id — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • parameter_tag — VARCHAR(64) — NOT NULL
  • before_value — DOUBLE
  • after_value — DOUBLE
  • action_type — ENUM('APPLY','REJECT') — NOT NULL
  • user_id — VARCHAR(16) — Foreign key → users.user_id — NOT NULL
  • comment — VARCHAR(200) — ARCHAR(200
  • acted_at — DATETIME — NOT NULL

partners

  • step_no — VARCHAR(2) — Primary key — NOT NULL — 01=Wafer제조 / 08=패키징
  • product_name — VARCHAR(64) — NOT NULL
  • stock_qty — INT — NOT NULL
  • stock_unit — VARCHAR(16) — NOT NULL — 장 / Box 등
  • deadline — DATE — NOT NULL — 납기일
  • company_name — VARCHAR(64) — NOT NULL
  • contact_tel — VARCHAR(32)
  • contact_email — VARCHAR(128)
  • updated_at — DATETIME — NOT NULL

pm_schedules

  • id — BIGINT — Primary key — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • scheduled_at — DATETIME — NOT NULL
  • pm_type — ENUM('ROUTINE','EMERGENCY') — NOT NULL

processes

  • step_no — VARCHAR(2) — Primary key — NOT NULL — 01~08
  • process_name — VARCHAR(32) — NOT NULL — 산화공정 / 포토공정 / 식각공정 / 박막증착 / 금속배선 / 테스트
  • equipment_type — VARCHAR(64) — Vertical Diffusion Furnace 등
  • equipment_code — VARCHAR(8) — FURN / TRACK / ETCH / PECVD / SPTT / PROBE
  • sort_order — TINYINT — NOT NULL — 공정 순서 1~8
  • has_equipment — BOOLEAN — NOT NULL — FALSE = 협력사 카드 (STEP01, STEP08)

status_change_logs

  • id — BIGINT — Primary key — NOT NULL
  • equipment_id — VARCHAR(16) — Foreign key → equipments.equipment_id — NOT NULL
  • prev_status — TINYINT — 0=idle / 1=run / 2=alarm
  • new_status — TINYINT
  • changed_at — DATETIME — NOT NULL

suggested_questions

  • chip_id — INT — Primary key — NOT NULL
  • label — VARCHAR(64) — NOT NULL
  • query_template — TEXT — NOT NULL
  • sort_order — TINYINT — NOT NULL
  • active — BOOLEAN — NOT NULL

user_line_map

  • id — BIGINT — Primary key — NOT NULL
  • user_id — VARCHAR(16) — Foreign key → users.user_id — NOT NULL — LINE_MGR·WORKER만 사용
  • line_code — VARCHAR(32) — NOT NULL — 담당 라인 코드
  • created_at — DATETIME — NOT NULL

users

  • user_id — VARCHAR(16) — Primary key — NOT NULL — 영문+숫자 4~16자, 등록 후 변경 불가
  • password_hash — VARCHAR(60) — NOT NULL — bcrypt cost>=10
  • name — VARCHAR(64) — NOT NULL
  • email — VARCHAR(128) — NOT NULL
  • factory_code — VARCHAR(8) — 소속공장 코드 (ICN, PTK 등)
  • role — ENUM('ADMIN', 'LINE_MGR', 'WORKER') — NOT NULL
  • status — ENUM('ACTIVE', 'INACTIVE', 'LOCKED') — NOT NULL
  • failed_login_count — INT — NOT NULL
  • last_login_at — DATETIME
  • must_change_password — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL