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