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