E205

Tables (19)

actuator

  • actuator_id — BIGINT — Primary key — NOT NULL
  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • current_state — VARCHAR(20) — NOT NULL
  • feed_g_per_sec — DECIMAL(6,3)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

actuator_event

  • event_id — BIGINT — Primary key — NOT NULL
  • actuator_id — BIGINT — Foreign key → actuator.actuator_id — NOT NULL
  • action — VARCHAR(10) — NOT NULL
  • cause — VARCHAR(20) — NOT NULL
  • cause_ref — VARCHAR(100)
  • started_at — DATETIME — NOT NULL
  • ended_at — DATETIME
  • result — VARCHAR(20) — NOT NULL
  • amount_g — DECIMAL(6,2)
  • created_at — DATETIME — NOT NULL

ai_report

  • report_id — BIGINT — Primary key — NOT NULL
  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • alert_id — BIGINT — Foreign key → alert.alert_id — 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

alert

  • alert_id — BIGINT — Primary key — NOT NULL
  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • alert_type — VARCHAR(20) — NOT NULL
  • sensor_type — VARCHAR(20)
  • severity — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • message — VARCHAR(255) — NOT NULL
  • triggered_at — DATETIME — NOT NULL
  • last_alert_at — DATETIME — NOT NULL
  • acknowledged_by — BIGINT — Foreign key → app_user.user_id
  • acknowledged_at — DATETIME
  • resolved_at — DATETIME
  • created_at — DATETIME — 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
  • actor_id — BIGINT — Foreign key → app_user.user_id
  • action — VARCHAR(20) — NOT NULL
  • target_type — VARCHAR(50) — NOT NULL
  • target_id — BIGINT
  • before_value — JSON
  • after_value — JSON
  • created_at — DATETIME — NOT NULL

device

  • device_id — BIGINT — Primary key — NOT NULL
  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • device_type — VARCHAR(20) — NOT NULL
  • api_key_hash — VARCHAR(255) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • last_seen_at — DATETIME
  • created_at — DATETIME — NOT NULL

device_command

  • command_id — BIGINT — Primary key — NOT NULL
  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • actuator_id — BIGINT — Foreign key → actuator.actuator_id
  • issued_by — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • command_type — VARCHAR(20) — NOT NULL
  • payload — JSON
  • validation_result — VARCHAR(20) — NOT NULL
  • reject_reason — VARCHAR(255)
  • dispatch_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL

feeding_schedule

  • schedule_id — BIGINT — Primary key — NOT NULL
  • 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 → app_user.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

marine_species

  • species_id — BIGINT — Primary key — NOT NULL
  • 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 — VARCHAR(20)
  • diet — VARCHAR(20)
  • reef_safe — BOOLEAN
  • care_level — VARCHAR(20)
  • care_note — VARCHAR(500)
  • created_at — DATETIME — NOT NULL

push_token

  • token_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • fcm_token — VARCHAR(255) — NOT NULL
  • platform — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sensor_reading

  • id — BIGINT — Primary key — NOT NULL
  • measured_at — DATETIME — 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 — 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
  • 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
  • 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

tank

  • tank_id — BIGINT — Primary key — NOT NULL
  • owner_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • tank_size — VARCHAR(20)
  • volume_liter — INT
  • setup_date — DATE
  • description — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

tank_livestock

  • livestock_id — BIGINT — Primary key — NOT NULL
  • 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 — NOT NULL
  • created_at — DATETIME — NOT NULL

tank_member

  • id — BIGINT — Primary key — NOT NULL
  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • user_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL

tank_setting

  • tank_id — BIGINT — Foreign key → tank.tank_id — NOT NULL
  • 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)
  • baseline_salinity — DECIMAL(10,4)
  • baseline_water_level — DECIMAL(10,4)
  • baseline_at — DATETIME
  • auto_control_enabled — BOOLEAN — NOT NULL
  • alert_config — JSON
  • updated_by — BIGINT — Foreign key → app_user.user_id
  • updated_at — DATETIME — NOT NULL