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