dd

Tables (27)

ai_models

  • model_id — SERIAL — Primary key — NOT NULL
  • model_version — VARCHAR(30) — NOT NULL
  • model_type — VARCHAR(30) — NOT NULL
  • deployed_at — TIMESTAMP WITH TIME ZONE

amr_devices

  • device_id — SERIAL — Primary key — NOT NULL
  • greenhouse_id — INT — Foreign key → greenhouses.greenhouse_id
  • device_code — VARCHAR(50) — NOT NULL
  • is_online — BOOLEAN
  • nas_storage_usage_pct — NUMERIC(5, 2)
  • last_heartbeat — TIMESTAMP WITH TIME ZONE

camera_runs

  • run_id — SERIAL — Primary key — NOT NULL
  • line_id — INT — Foreign key → rail_lines.line_id
  • device_id — INT — Foreign key → amr_devices.device_id
  • run_time — TIMESTAMP WITH TIME ZONE — NOT NULL
  • total_frames — INT — NOT NULL
  • coverage_pct — NUMERIC(5, 2)
  • status — VARCHAR(20)
  • retry_count — INT

crop_growth_stages

  • stage_id — SERIAL — Primary key — NOT NULL
  • greenhouse_id — INT — Foreign key → greenhouses.greenhouse_id
  • stage_name — VARCHAR(50) — NOT NULL
  • current_truss_number — INT
  • pruning_status — BOOLEAN
  • started_at — DATE — NOT NULL
  • ended_at — DATE

dataset_files

  • file_id — SERIAL — Primary key — NOT NULL
  • dataset_id — INT — Foreign key → datasets.dataset_id
  • file_name — VARCHAR(255) — NOT NULL
  • file_path — VARCHAR(500) — NOT NULL
  • file_size_bytes — BIGINT — NOT NULL

datasets

  • dataset_id — SERIAL — Primary key — NOT NULL
  • dataset_name — VARCHAR(100) — NOT NULL
  • dataset_type — VARCHAR(20) — NOT NULL
  • total_count — BIGINT
  • status — VARCHAR(20)
  • last_updated_at — TIMESTAMP WITH TIME ZONE

detection_results

  • detection_id — SERIAL — Primary key — NOT NULL
  • image_id — INT — Foreign key → raw_images.image_id
  • model_id — INT — Foreign key → ai_models.model_id
  • disease_type — VARCHAR(50) — NOT NULL
  • confidence — NUMERIC(5, 2) — NOT NULL
  • detection_unit — VARCHAR(30)
  • bbox_x — NUMERIC(6,2) — NOT NULL
  • bbox_y — NUMERIC(6,2) — NOT NULL
  • bbox_w — NUMERIC(6,2) — NOT NULL
  • bbox_h — NUMERIC(6,2) — NOT NULL
  • change_status — VARCHAR(50)
  • action_summary — TEXT
  • created_at — TIMESTAMP WITH TIME ZONE

farms

  • farm_id — SERIAL — Primary key — NOT NULL
  • farm_name — VARCHAR(100) — NOT NULL
  • cultivar — VARCHAR(50)
  • facility_type — VARCHAR(50)
  • created_at — TIMESTAMP WITH TIME ZONE

greenhouses

  • greenhouse_id — SERIAL — Primary key — NOT NULL
  • farm_id — INT — Foreign key → farms.farm_id
  • greenhouse_name — VARCHAR(100) — NOT NULL
  • total_rails — INT
  • total_length_meters — NUMERIC(6, 2) — NOT NULL

inquiries

  • inquiry_id — SERIAL — Primary key — NOT NULL
  • farm_id — INT — Foreign key → farms.farm_id
  • user_id — INT — Foreign key → users.user_id
  • question — TEXT — NOT NULL
  • status — VARCHAR(20)
  • created_at — TIMESTAMP WITH TIME ZONE

inquiry_answers

  • answer_id — SERIAL — Primary key — NOT NULL
  • inquiry_id — INT — Foreign key → inquiries.inquiry_id
  • admin_id — INT — Foreign key → users.user_id
  • answer_content — TEXT — NOT NULL
  • answered_at — TIMESTAMP WITH TIME ZONE

label_tasks

  • task_id — SERIAL — Primary key — NOT NULL
  • image_id — INT — Foreign key → raw_images.image_id
  • assigned_user_id — INT — Foreign key → users.user_id
  • status — VARCHAR(20)
  • assigned_date — DATE

notice_farm_maps

  • map_id — SERIAL — Primary key — NOT NULL
  • notice_id — INT — Foreign key → notices.notice_id
  • farm_id — INT — Foreign key → farms.farm_id

notices

  • notice_id — SERIAL — Primary key — NOT NULL
  • author_id — INT — Foreign key → users.user_id
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE

polygon_labels

  • polygon_id — SERIAL — Primary key — NOT NULL
  • task_id — INT — Foreign key → label_tasks.task_id
  • label_name — VARCHAR(50) — NOT NULL
  • leaf_unit_type — VARCHAR(20)
  • polygon_points — JSONB — NOT NULL
  • is_prelabel — BOOLEAN
  • created_at — TIMESTAMP WITH TIME ZONE

rail_lines

  • line_id — SERIAL — Primary key — NOT NULL
  • greenhouse_id — INT — Foreign key → greenhouses.greenhouse_id
  • line_number — INT — NOT NULL
  • length_meters — NUMERIC(6, 2) — NOT NULL

raw_images

  • image_id — SERIAL — Primary key — NOT NULL
  • run_id — INT — Foreign key → camera_runs.run_id
  • position_meters — NUMERIC(6, 2) — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • resolution — VARCHAR(20)
  • captured_at — TIMESTAMP WITH TIME ZONE — NOT NULL

sensor_devices

  • sensor_id — SERIAL — Primary key — NOT NULL
  • greenhouse_id — INT — Foreign key → greenhouses.greenhouse_id
  • sensor_type — VARCHAR(30) — NOT NULL
  • is_active — BOOLEAN

sensor_error_logs

  • error_id — SERIAL — Primary key — NOT NULL
  • sensor_id — INT — Foreign key → sensor_devices.sensor_id
  • error_type — VARCHAR(50) — NOT NULL
  • occurred_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • resolved_at — TIMESTAMP WITH TIME ZONE

sensor_time_series

  • time_series_id — BIGSERIAL — Primary key — NOT NULL
  • farm_id — INT — Foreign key → farms.farm_id
  • recorded_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • inside_temp — NUMERIC(4, 1)
  • inside_humidity — NUMERIC(4, 1)
  • co2_ppm — NUMERIC(6, 1)
  • solar_radiation — NUMERIC(6, 1)
  • roof_window_open_pct — NUMERIC(4, 1)
  • ground_temp — NUMERIC(4, 1)
  • is_valid — BOOLEAN

spray_histories

  • spray_id — SERIAL — Primary key — NOT NULL
  • detection_id — INT — Foreign key → detection_results.detection_id
  • device_id — INT — Foreign key → amr_devices.device_id
  • spray_status — VARCHAR(20)
  • sprayed_at — TIMESTAMP WITH TIME ZONE

user_farm_access

  • access_id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.user_id
  • farm_id — INT — Foreign key → farms.farm_id

user_feedbacks

  • feedback_id — SERIAL — Primary key — NOT NULL
  • detection_id — INT — Foreign key → detection_results.detection_id
  • user_id — INT — Foreign key → users.user_id
  • feedback_type — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE

user_role_mappings

  • mapping_id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.user_id
  • role_id — INT — Foreign key → user_roles.role_id

user_roles

  • role_id — SERIAL — Primary key — NOT NULL
  • role_name — VARCHAR(20) — NOT NULL

users

  • user_id — SERIAL — Primary key — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • user_name — VARCHAR(50) — NOT NULL
  • user_code — VARCHAR(10) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE

weather_forecasts

  • forecast_id — SERIAL — Primary key — NOT NULL
  • farm_id — INT — Foreign key → farms.farm_id
  • outside_temp — NUMERIC(4, 1)
  • outside_humidity — NUMERIC(4, 1)
  • weather_condition — VARCHAR(50)
  • ventilation_guide — TEXT
  • forecasted_at — TIMESTAMP WITH TIME ZONE — NOT NULL