E205_OLD
Tables (23)
actuator
- actuator_id — BIGINT — Primary key — NOT NULL — 물리장치(히터/조명/급여기/펌프)
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- type — ENUM('HEATER', 'LIGHT', 'FEEDER', 'PUMP') — NOT NULL
- name — VARCHAR(50) — NOT NULL
- current_state — ENUM('ON', 'OFF', 'UNKNOWN') — NOT NULL
- created_at — DATETIME(3) — NOT NULL
- updated_at — DATETIME(3) — NOT NULL
actuator_event
- event_id — BIGINT — Primary key — NOT NULL — ON/OFF·원인·시작·종료·결과 — FR-005
- actuator_id — BIGINT — Foreign key → actuator.actuator_id — NOT NULL
- action — ENUM('ON', 'OFF') — NOT NULL
- cause — ENUM('SCHEDULE', 'MANUAL', 'AUTO', 'SAFETY') — NOT NULL
- cause_ref — VARCHAR(100)
- started_at — DATETIME(3) — NOT NULL
- ended_at — DATETIME(3)
- result — ENUM('SUCCESS', 'FAILED', 'PENDING') — NOT NULL
- created_at — DATETIME(3) — NOT NULL
ai_report
- report_id — BIGINT — Primary key — NOT NULL
- tank_id — BIGINT — Foreign key — NOT NULL
- alert_id — BIGINT — Foreign key — NOT NULL
- status — VARCHAR(20) — NOT NULL
- analysis — TEXT
- recommendation — TEXT
- input_context — JSON
- model — VARCHAR(50)
- generated_at — DATETIME
- created_at — DATETIME — NOT NULL
ai_result
- ai_result_id — BIGINT — Primary key — NOT NULL — AI 결과 — FR-015/016
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- result_type — ENUM('DETECTION', 'ACTIVITY', 'FEEDING_RESPONSE', 'ABNORMAL_BEHAVIOR') — NOT NULL
- value — JSON
- snapshot_id — BIGINT — Foreign key → snapshot.snapshot_id
- detected_at — DATETIME(3) — NOT NULL
- received_at — DATETIME(3) — NOT NULL
- created_at — DATETIME(3) — NOT NULL
alert
- alert_id — BIGINT — Primary key — NOT NULL — 알림 상태머신 OPEN→ACK→RESOLVED
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- alert_type — ENUM('WATER_QUALITY', 'LEAK', 'SENSOR', 'DEVICE', 'BEHAVIOR') — NOT NULL
- sensor_type — VARCHAR(20)
- severity — ENUM('INFO', 'WARNING', 'CRITICAL') — NOT NULL
- status — ENUM('OPEN', 'ACKNOWLEDGED', 'RESOLVED') — NOT NULL
- message — VARCHAR(255) — NOT NULL
- triggered_at — DATETIME(3) — NOT NULL
- last_alert_at — DATETIME(3) — NOT NULL
- acknowledged_by — BIGINT — Foreign key → user.user_id
- acknowledged_at — DATETIME(3)
- resolved_at — DATETIME(3)
- created_at — DATETIME(3) — NOT NULL
app_user
- user_id — BIGINT — Primary key — NOT NULL
- email — VARCHAR(255) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- nickname — VARCHAR(50) — NOT NULL
- status — VARCHAR(20) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
audit_log
- audit_id — BIGINT — Primary key — NOT NULL — 민감 액션 감사 — NFR-006
- actor_id — BIGINT — Foreign key → user.user_id
- action — ENUM('MANUAL_CONTROL', 'SETTING_CHANGE', 'ALERT_ACK') — NOT NULL
- target_type — VARCHAR(50) — NOT NULL
- target_id — BIGINT
- before_value — JSON
- after_value — JSON
- created_at — DATETIME(3) — NOT NULL
device
- device_id — BIGINT — Primary key — NOT NULL — RPi5/Jetson 게이트웨이 레지스트리 — FIX-2
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- device_type — ENUM('RPI5', 'JETSON') — NOT NULL
- api_key_hash — VARCHAR(255) — NOT NULL
- status — ENUM('ACTIVE', 'REVOKED') — NOT NULL
- last_seen_at — DATETIME(3)
- created_at — DATETIME(3) — NOT NULL
device_command
- command_id — BIGINT — Primary key — NOT NULL — 수동명령 — FR-009
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- actuator_id — BIGINT — Foreign key → actuator.actuator_id
- issued_by — BIGINT — Foreign key → user.user_id — NOT NULL
- command_type — VARCHAR(30) — NOT NULL
- payload — JSON
- validation_result — ENUM('ACCEPTED', 'REJECTED') — NOT NULL
- reject_reason — VARCHAR(255)
- dispatch_status — ENUM('SENT', 'FAILED', 'PENDING') — NOT NULL
- created_at — DATETIME(3) — NOT NULL
feeding_schedule
- schedule_id — BIGINT — Primary key — NOT NULL — 예약 급여 — FR-013
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- feed_time — TIME — NOT NULL
- amount_g — DECIMAL(6, 2) — NOT NULL
- active — BOOLEAN — NOT NULL
- created_by — BIGINT — Foreign key → user.user_id
- created_at — DATETIME(3) — NOT NULL
- updated_at — DATETIME(3) — NOT NULL
marine_species
- species_id — BIGINT — Primary key — NOT NULL — 해수 어종 레퍼런스 — FR-019
- common_name — VARCHAR(100) — NOT NULL
- scientific_name — VARCHAR(100)
- min_tank_liter — INT
- temp_min — DECIMAL(5, 2)
- temp_max — DECIMAL(5, 2)
- ph_min — DECIMAL(4, 2)
- ph_max — DECIMAL(4, 2)
- salinity_min — DECIMAL(5, 2)
- salinity_max — DECIMAL(5, 2)
- aggression — ENUM('PEACEFUL', 'SEMI_AGGRESSIVE', 'AGGRESSIVE')
- care_note — VARCHAR(500)
- created_at — DATETIME(3) — NOT NULL
push_token
- token_id — BIGINT — Primary key — NOT NULL — 사용자↔앱 FCM 토큰
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- fcm_token — VARCHAR(255) — NOT NULL
- platform — ENUM('ANDROID') — NOT NULL
- created_at — DATETIME(3) — NOT NULL
- updated_at — DATETIME(3) — NOT NULL
salinity_record
- id — BIGINT — Primary key — NOT NULL — 수동 입력 염도 — FR-014
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- value — DECIMAL(10, 4) — NOT NULL
- unit — VARCHAR(10) — NOT NULL
- measured_at — DATETIME(3) — NOT NULL
- created_at — DATETIME(3) — NOT NULL
sensor_reading
- id — BIGINT — Primary key — NOT NULL — 원본 시계열(월 파티션) — FIX-1
- measured_at — DATETIME(3) — Primary key — NOT NULL
- tank_id — BIGINT — NOT NULL
- sensor_type — VARCHAR(20) — NOT NULL
- value — DECIMAL(10, 4) — NOT NULL
- unit — VARCHAR(10) — NOT NULL
- status — VARCHAR(20) — NOT NULL
- received_at — DATETIME(3) — NOT NULL
sensor_reading_1d
- tank_id — BIGINT — Primary key — NOT NULL
- sensor_type — VARCHAR(20) — Primary key — NOT NULL
- bucket_start — DATETIME — Primary key — NOT NULL
- avg_value — DECIMAL(10,4)
- min_value — DECIMAL(10,4)
- max_value — DECIMAL(10,4)
- sample_count — INT
sensor_reading_1h
- tank_id — BIGINT — Primary key — NOT NULL — 1시간 집계(7d/30d 조회) — NFR-005
- sensor_type — VARCHAR(20) — Primary key — NOT NULL
- bucket_start — DATETIME — Primary key — NOT NULL
- avg_value — DECIMAL(10, 4)
- min_value — DECIMAL(10, 4)
- max_value — DECIMAL(10, 4)
- sample_count — INT
sensor_reading_1m
- tank_id — BIGINT — Primary key — NOT NULL — 1분 집계(24h 조회) — NFR-005
- sensor_type — VARCHAR(20) — Primary key — NOT NULL
- bucket_start — DATETIME — Primary key — NOT NULL
- avg_value — DECIMAL(10, 4)
- min_value — DECIMAL(10, 4)
- max_value — DECIMAL(10, 4)
- sample_count — INT
snapshot
- snapshot_id — BIGINT — Primary key — NOT NULL — 증거 스냅샷 경로 메타 — Q5
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- file_path — VARCHAR(255) — NOT NULL
- reason — ENUM('AI_DETECTION', 'ALERT', 'MANUAL') — NOT NULL
- captured_at — DATETIME(3) — NOT NULL
- expires_at — DATETIME(3)
- created_at — DATETIME(3) — NOT NULL
tank
- tank_id — BIGINT — Primary key — NOT NULL — 어항 (해수 전용) — FR-001
- name — VARCHAR(100) — NOT NULL
- volume_liter — INT
- description — VARCHAR(255)
- created_by — BIGINT — Foreign key → user.user_id — 감사용 참고값
- created_at — DATETIME(3) — NOT NULL
- updated_at — DATETIME(3) — NOT NULL
tank_livestock
- livestock_id — BIGINT — Primary key — NOT NULL — 어항 사육 생물 — FR-001
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- species_id — BIGINT — Foreign key → marine_species.species_id — NOT NULL
- nickname — VARCHAR(50)
- quantity — INT — NOT NULL
- added_at — DATETIME(3) — NOT NULL
- created_at — DATETIME(3) — NOT NULL
tank_member
- id — BIGINT — Primary key — NOT NULL — 어항 접근권한 단일 진실원 — FR-020
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- role — ENUM('OWNER', 'MEMBER', 'VIEWER') — NOT NULL
- created_at — DATETIME(3) — NOT NULL
tank_setting
- tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL — 목표수질·자동제어 설정 (어항 1:1) — FR-008
- target_temp_min — DECIMAL(10, 4)
- target_temp_max — DECIMAL(10, 4)
- target_ph_min — DECIMAL(10, 4)
- target_ph_max — DECIMAL(10, 4)
- target_salinity_min — DECIMAL(10, 4)
- target_salinity_max — DECIMAL(10, 4)
- auto_control_enabled — BOOLEAN — NOT NULL
- alert_config — JSON
- updated_by — BIGINT — Foreign key → user.user_id
- updated_at — DATETIME(3) — NOT NULL
user
- user_id — BIGINT — Primary key — NOT NULL — 계정 (JWT 인증) — FR-001
- email — VARCHAR(255) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- nickname — VARCHAR(50) — NOT NULL
- status — ENUM('ACTIVE', 'SUSPENDED') — NOT NULL
- created_at — DATETIME(3) — NOT NULL
- updated_at — DATETIME(3) — NOT NULL