edge cloud

Tables (39)

activity_logs

  • activity_log_id — bigint — Primary key — NOT NULL
  • activity_type — varchar(30) — NOT NULL
  • target_type — varchar(30) — NOT NULL
  • target_id — bigint — NOT NULL
  • summary — varchar(200) — NOT NULL
  • created_by — bigint — Foreign key → users.user_id
  • created_at — timestamptz — NOT NULL

alarm_conditions

  • condition_id — bigint — Primary key — NOT NULL
  • regulation_id — bigint — Foreign key → alarm_regulations.regulation_id — NOT NULL
  • condition_group — integer — NOT NULL
  • unit_id — bigint — Foreign key → units.unit_id — NOT NULL
  • comparison_operator — varchar(5) — NOT NULL
  • threshold_value — numeric(14,4) — NOT NULL
  • threshold_value_max — numeric(14,4)
  • logic_operator_to_next — varchar(5)
  • created_at — timestamptz — NOT NULL

alarm_log_reads

  • alarm_log_read_id — bigint — Primary key — NOT NULL
  • alarm_log_id — bigint — NOT NULL
  • alarm_triggered_at — timestamptz — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • read_at — timestamptz — NOT NULL

alarm_logs

  • alarm_log_id — bigint — Primary key — NOT NULL
  • triggered_at — timestamptz — Primary key — NOT NULL
  • regulation_id — bigint — Foreign key → alarm_regulations.regulation_id — NOT NULL
  • state_id — bigint — NOT NULL
  • state_classified_at — timestamptz — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • severity — varchar(20) — NOT NULL
  • acknowledged_at — timestamptz
  • acknowledged_by — varchar(60)
  • resolved_at — timestamptz
  • alarm_status — varchar(20) — NOT NULL
  • snapshot_detail — jsonb
  • acknowledged_by_user_id — bigint — Foreign key → users.user_id

alarm_regulations

  • regulation_id — bigint — Primary key — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • applies_to_state — varchar(20) — NOT NULL
  • regulation_name — varchar(100) — NOT NULL
  • severity — varchar(20) — NOT NULL
  • condition_logic — varchar(10) — NOT NULL
  • is_enabled — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL

approval_requests

  • approval_request_id — bigint — Primary key — NOT NULL
  • request_type — varchar(30) — NOT NULL
  • target_id — bigint — NOT NULL
  • requested_by — bigint — Foreign key → users.user_id
  • approver_user_id — bigint — Foreign key → users.user_id
  • status — varchar(20) — NOT NULL
  • comment — varchar(200)
  • requested_at — timestamptz — NOT NULL
  • decided_at — timestamptz

asset_meters

  • asset_meter_id — bigint — Primary key — NOT NULL
  • asset_id — bigint — Foreign key → assets.asset_id — NOT NULL
  • meter_name — varchar(40) — NOT NULL
  • unit_name — varchar(20) — NOT NULL
  • last_reading — numeric(18,4) — NOT NULL
  • last_reading_at — timestamptz

assets

  • asset_id — bigint — Primary key — NOT NULL
  • asset_no — varchar(40) — NOT NULL
  • asset_name — varchar(100) — NOT NULL
  • asset_type — varchar(40) — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • manufacturer — varchar(80)
  • model_no — varchar(80)
  • criticality — varchar(20) — NOT NULL
  • asset_status — varchar(20) — NOT NULL
  • description — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

classified_states

  • state_id — bigint — Primary key — NOT NULL
  • classified_at — timestamptz — Primary key — NOT NULL
  • raw_log_id — bigint — NOT NULL
  • edge_id — bigint — Foreign key → edge_devices.edge_id — NOT NULL
  • unit_id — bigint — Foreign key → units.unit_id — NOT NULL
  • start_time — timestamptz — NOT NULL
  • finish_time — timestamptz — NOT NULL
  • machine_state — varchar(20) — NOT NULL
  • representative_value — numeric(14,4)
  • classification_method — varchar(30)

contracts

  • contract_id — bigint — Primary key — NOT NULL
  • contract_no — varchar(40) — NOT NULL
  • vendor_id — bigint — Foreign key → vendors.vendor_id — NOT NULL
  • contract_name — varchar(100) — NOT NULL
  • start_date — date — NOT NULL
  • end_date — date
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

edge_devices

  • edge_id — bigint — Primary key — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • product_id — bigint — Foreign key → product_master.product_id — NOT NULL
  • serial_no — varchar(60) — NOT NULL
  • install_date — date
  • purchase_date — date
  • inspection_cycle_days — integer
  • next_inspection_due — date
  • device_status — varchar(20) — NOT NULL
  • firmware_version — varchar(30)
  • asset_id — bigint — Foreign key → assets.asset_id
  • created_at — timestamptz — NOT NULL

edge_lifecycle

  • lifecycle_id — bigint — Primary key — NOT NULL
  • edge_id — bigint — Foreign key → edge_devices.edge_id — NOT NULL
  • inspection_date — date — NOT NULL
  • inspection_type — varchar(20) — NOT NULL
  • inspector_name — varchar(60) — NOT NULL
  • inspection_result — varchar(20) — NOT NULL
  • result_detail — text
  • next_due_date — date
  • inspector_user_id — bigint — Foreign key → users.user_id
  • created_at — timestamptz — NOT NULL

failure_codes

  • failure_code_id — bigint — Primary key — NOT NULL
  • failure_code — varchar(30) — NOT NULL
  • failure_class — varchar(30) — NOT NULL
  • description — varchar(200) — NOT NULL
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL

inventory_balances

  • inventory_balance_id — bigint — Primary key — NOT NULL
  • inventory_item_id — bigint — Foreign key → inventory_items.inventory_item_id — NOT NULL
  • storeroom_id — bigint — Foreign key → storerooms.storeroom_id — NOT NULL
  • on_hand_quantity — bigint — NOT NULL
  • reserved_quantity — bigint — NOT NULL
  • updated_at — timestamptz — NOT NULL

inventory_items

  • inventory_item_id — bigint — Primary key — NOT NULL
  • item_no — varchar(40) — NOT NULL
  • item_name — varchar(100) — NOT NULL
  • item_description — varchar(200)
  • rotating — boolean — NOT NULL
  • status — varchar(20) — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

job_plans

  • job_plan_id — bigint — Primary key — NOT NULL
  • job_plan_no — varchar(40) — NOT NULL
  • job_plan_name — varchar(100) — NOT NULL
  • work_type — varchar(30) — NOT NULL
  • description — varchar(200)
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL

labor_crews

  • labor_crew_id — bigint — Primary key — NOT NULL
  • crew_code — varchar(30) — NOT NULL
  • crew_name — varchar(100) — NOT NULL
  • site_id — bigint — Foreign key → sites.site_id — NOT NULL
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL

locations

  • location_id — bigint — Primary key — NOT NULL
  • parent_id — bigint — Foreign key → locations.location_id — NOT NULL
  • location_type — varchar(20) — NOT NULL
  • location_code — varchar(40) — NOT NULL
  • location_name — varchar(100) — NOT NULL
  • depth — smallint — NOT NULL
  • is_active — boolean — NOT NULL
  • metadata — jsonb
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

organizations

  • organization_id — bigint — Primary key — NOT NULL
  • organization_code — varchar(30) — NOT NULL
  • organization_name — varchar(100) — NOT NULL
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

preventive_maintenance_plans

  • pm_id — bigint — Primary key — NOT NULL
  • pm_no — varchar(40) — NOT NULL
  • asset_id — bigint — Foreign key → assets.asset_id — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • description — varchar(200) — NOT NULL
  • trigger_type — varchar(30) — NOT NULL
  • frequency_value — integer — NOT NULL
  • frequency_unit — varchar(20) — NOT NULL
  • next_due_date — date — NOT NULL
  • active — boolean — NOT NULL
  • last_generated_work_order_id — bigint — Foreign key → work_orders.work_order_id
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

product_functions

  • product_function_id — bigint — Primary key — NOT NULL
  • product_id — bigint — Foreign key → product_master.product_id — NOT NULL
  • product_function — varchar(60) — NOT NULL
  • created_at — timestamptz — NOT NULL

product_master

  • product_id — bigint — Primary key — NOT NULL
  • product_name — varchar(60) — NOT NULL
  • supported_formats — text — NOT NULL
  • created_at — timestamptz — NOT NULL

purchase_orders

  • purchase_order_id — bigint — Primary key — NOT NULL
  • purchase_order_no — varchar(40) — NOT NULL
  • purchase_requisition_id — bigint — Foreign key → purchase_requisitions.purchase_requisition_id
  • vendor_id — bigint — Foreign key → vendors.vendor_id — NOT NULL
  • site_id — bigint — Foreign key → sites.site_id — NOT NULL
  • description — varchar(200) — NOT NULL
  • status — varchar(20) — NOT NULL
  • order_date — date — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

purchase_requisitions

  • purchase_requisition_id — bigint — Primary key — NOT NULL
  • requisition_no — varchar(40) — NOT NULL
  • site_id — bigint — Foreign key → sites.site_id — NOT NULL
  • description — varchar(200) — NOT NULL
  • status — varchar(20) — NOT NULL
  • requested_by — bigint — Foreign key → users.user_id
  • created_purchase_order_id — bigint
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

raw_data_logs

  • raw_log_id — bigint
  • edge_id — bigint — Foreign key → edge_devices.edge_id — NOT NULL
  • unit_id — bigint — Foreign key → units.unit_id — NOT NULL
  • start_time — timestamptz — NOT NULL
  • finish_time — timestamptz — NOT NULL
  • data_value — bytea
  • file_path — text
  • storage_path_id — bigint — Foreign key → storage_paths.storage_path_id
  • data_format — varchar(20) — NOT NULL
  • sequence_no — integer
  • ingested_at — timestamptz — NOT NULL

roles

  • role_id — bigint — Primary key — NOT NULL
  • role_code — varchar(30) — NOT NULL
  • role_name — varchar(60) — NOT NULL
  • description — varchar(200)
  • is_system_role — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL

security_group_memberships

  • security_group_membership_id — bigint — Primary key — NOT NULL
  • security_group_id — bigint — Foreign key → security_groups.security_group_id — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • assigned_at — timestamptz — NOT NULL

security_groups

  • security_group_id — bigint — Primary key — NOT NULL
  • security_group_code — varchar(30) — NOT NULL
  • security_group_name — varchar(100) — NOT NULL
  • description — varchar(200)
  • created_at — timestamptz — NOT NULL

service_requests

  • service_request_id — bigint — Primary key — NOT NULL
  • request_no — varchar(40) — NOT NULL
  • asset_id — bigint — Foreign key → assets.asset_id — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • request_type — varchar(30) — NOT NULL
  • description — varchar(200) — NOT NULL
  • long_description — text
  • requester — varchar(100) — NOT NULL
  • status — varchar(30) — NOT NULL
  • reported_at — timestamptz — NOT NULL
  • created_work_order_id — bigint — Foreign key → work_orders.work_order_id
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

sites

  • site_id — bigint — Primary key — NOT NULL
  • organization_id — bigint — Foreign key → organizations.organization_id — NOT NULL
  • site_code — varchar(30) — NOT NULL
  • site_name — varchar(100) — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id
  • status — varchar(20) — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

storage_paths

  • storage_path_id — bigint — Primary key — NOT NULL
  • folder_key — varchar(20) — NOT NULL
  • base_path — text — NOT NULL
  • target_data_format — varchar(20)
  • target_unit_id — bigint — Foreign key → units.unit_id
  • storage_tier — varchar(10) — NOT NULL
  • retention_days — integer
  • is_active — boolean — NOT NULL

storerooms

  • storeroom_id — bigint — Primary key — NOT NULL
  • site_id — bigint — Foreign key → sites.site_id — NOT NULL
  • storeroom_code — varchar(30) — NOT NULL
  • storeroom_name — varchar(100) — NOT NULL
  • description — varchar(200)
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

units

  • unit_id — bigint — Primary key — NOT NULL
  • unit_code — varchar(20) — NOT NULL
  • unit_symbol — varchar(10) — NOT NULL
  • unit_description — varchar(80)
  • data_category — varchar(20) — NOT NULL
  • value_type — varchar(20) — NOT NULL

user_location_assignments

  • assignment_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • assignment_scope — varchar(20) — NOT NULL
  • assigned_at — timestamptz — NOT NULL
  • assigned_by — bigint — Foreign key → users.user_id

user_roles

  • user_role_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • role_id — bigint — Foreign key → roles.role_id — NOT NULL
  • assigned_at — timestamptz — NOT NULL
  • assigned_by — bigint — Foreign key → users.user_id

user_sessions

  • session_id — bigint — Primary key — NOT NULL
  • session_token — text — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • ip_address — varchar(64)
  • user_agent — text
  • logged_in_at — timestamptz — NOT NULL
  • logged_out_at — timestamptz
  • last_active_at — timestamptz — NOT NULL
  • is_active — boolean — NOT NULL

users

  • user_id — bigint — Primary key — NOT NULL
  • username — varchar(60) — NOT NULL
  • email — varchar(120) — NOT NULL
  • password_hash — text — NOT NULL
  • full_name — varchar(100) — NOT NULL
  • phone — varchar(30)
  • employee_no — varchar(40)
  • status — varchar(20) — NOT NULL
  • last_login_at — timestamptz
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

vendors

  • vendor_id — bigint — Primary key — NOT NULL
  • vendor_code — varchar(30) — NOT NULL
  • vendor_name — varchar(100) — NOT NULL
  • contact_name — varchar(100)
  • contact_email — varchar(120)
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

work_orders

  • work_order_id — bigint — Primary key — NOT NULL
  • work_order_no — varchar(40) — NOT NULL
  • asset_id — bigint — Foreign key → assets.asset_id — NOT NULL
  • location_id — bigint — Foreign key → locations.location_id — NOT NULL
  • work_type — varchar(30) — NOT NULL
  • description — varchar(200) — NOT NULL
  • long_description — text
  • priority — varchar(20) — NOT NULL
  • status — varchar(20) — NOT NULL
  • target_start_date — timestamptz
  • target_finish_date — timestamptz
  • scheduled_start_date — timestamptz
  • scheduled_finish_date — timestamptz
  • actual_start_date — timestamptz
  • actual_finish_date — timestamptz
  • reported_at — timestamptz — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL