Lapisan Bronze

Lapisan Bronze berisi dua skema data yang berbeda, masing-masing melayani aspek berbeda dari platform telematika dan intelijen bisnis:

raw_business_data - berisi tabel, atribut, dan nilai yang terkait dengan informasi bisnis, seperti kendaraan, karyawan, geofence yang ditambahkan oleh pengguna, dll.
raw_telematics_data - berisi tabel, atribut, dan nilai yang terkait dengan data telematika yang dikirimkan dari perangkat yang dipantau, seperti lokasi, input, output, dan peristiwa.

Setiap skema dioptimalkan untuk domain data dan pola akses spesifiknya, memberikan cakupan komprehensif untuk kebutuhan operasional, telematik, dan manajemen aset.

`raw_business_data` struktur

Skema ini berisi 40+ tabel yang dipilih dengan cermat untuk mencakup berbagai aspek bisnis dan kasus penggunaan. Tabel-tabel ini mewakili entitas bisnis inti Anda, struktur organisasi, dan data operasional.

Diagram interaktif skema raw_business_data tersedia di dbdiagram.io: https://dbdiagram.io/d/V3-bronze-layer-68ecfd1c2e68d21b4131089a

Temukan detail skema raw business data di bawah.

skema raw_business_data


Tabel "vehicle_service_tasks" {

  "record_added_at" timestamp [not null]

  "start_mileage" numeric

  "comment" "character varying(255)"

  "status" "character varying(10)" [not null]

  "completion_date" timestamp

  "start_engine_hours" numeric

  "service_task_id" integer [not null]

  "is_notification_push_enabled" boolean [not null]

  "date_notification_interval" interval

  "predicted_datetime" timestamp

  "cost" numeric [not null]

  "mileage_limit" numeric

  "notification_emails" text

  "is_unplanned" boolean [not null]

  "is_repeat" boolean [not null]

  "completion_engine_hours" integer

  "engine_hours_limit" numeric

  "mileage_repeat_interval" integer

  "vehicle_id" integer [not null]

  "engine_hours_notification_interval" integer

  "start_date" timestamp

  "mileage_notification_interval" integer

  "date_repeat_interval" interval

  "description" "character varying(255)"

  "notification_sms_phone_numbers" text

  "end_date" timestamp

  "engine_hours_repeat_interval" integer

  "completion_mileage" integer

}

  

Tabel "garages" {

  "record_added_at" timestamp [not null]

  "garage_id" integer [not null]

  "longitude" numeric

  "mechanic_name" "character varying(255)"

  "radius" integer [not null]

  "latitude" numeric

  "organization_label" "character varying(255)"

  "user_id" integer [not null]

  "dispatcher_name" "character varying(255)"

  "address" "character varying(255)"

}

  

Tabel "driver_history" {

  "server_datetime" timestamp [not null]

  "address" "character varying(255)"

  "updated_by" integer [not null]

  "object_id" integer

  "longitude" numeric

  "latitude" numeric

  "driver_history_id" integer [not null]

  "hardware_key" "character varying(64)"

  "new_employee_id" integer

  "changed_datetime" timestamp

  "record_added_at" timestamp [not null]

  "old_employee_id" integer

}

  

Tabel "departments" {

  "record_added_at" timestamp [not null]

  "department_label" "character varying(255)" [not null]

  "latitude" numeric

  "department_id" integer [not null]

  "address" "character varying(255)"

  "radius" integer [not null]

  "longitude" numeric

  "user_id" integer [not null]

}

  

Tabel "checkins" {

  "radius" integer [not null]

  "latitude" numeric [not null]

  "employee_id" integer [not null]

  "longitude" numeric [not null]

  "record_added_at" timestamp [not null]

  "actual_datetime" timestamp [not null]

  "user_id" integer [not null]

  "form_id" integer [not null]

  "address" "character varying(255)"

  "planned_datetime" timestamp [not null]

  "object_id" integer [not null]

  "checkin_id" integer [not null]

  "comment" text

}

  

Tabel "statuses" {

  "order_sort" integer [not null]

  "listing_id" integer [not null]

  "color" "character varying(6)" [not null]

  "status_id" integer [not null]

  "status_label" "character varying(200)" [not null]

  "record_added_at" timestamp [not null]

  "is_deleted" boolean [not null]

}

  

Tabel "places_linked_entity_fields" {

  "value" bigint [not null]

  "record_added_at" timestamp [not null]

  "place_id" integer [not null]

  "field_id" integer [not null]

}

  

Tabel "places_text_fields" {

  "place_id" integer [not null]

  "record_added_at" timestamp [not null]

  "value" text [not null]

  "field_id" integer [not null]

}

  

Tabel "users2zones" {

  "zone_id" integer [not null]

  "record_added_at" timestamp [not null]

  "user_id" integer [not null]

}

  

Tabel "objects" {

  "record_added_at" timestamp [not null]

  "create_datetime" timestamp [not null]

  "client_id" integer [not null]

  "group_id" integer

  "object_label" "character varying(100)"

  "model" "character varying(64)"

  "is_clone" boolean [not null]

  "is_deleted" boolean [not null]

  "device_id" integer [not null]

  "object_id" integer [not null]

}

  

Tabel "device_output_name" {

  "device_id" integer [not null]

  "record_added_at" timestamp [not null]

  "label" "character varying(100)" [not null]

  "number" integer [not null]

}

  

Tabel "geofence_points" {

  "longitude" numeric [not null]

  "number" integer [not null]

  "zone_id" integer [not null]

  "record_added_at" timestamp [not null]

  "latitude" numeric [not null]

}

  

Tabel "custom_fields" {

  "record_added_at" timestamp [not null]

  "entity_id" integer [not null]

  "is_required" boolean [not null]

  "custom_field_label" text [not null]

  "parameters" jsonb

  "custom_field_type" integer [not null]

  "description" text

  "custom_field_id" integer [not null]

}

  

Tabel "places_decimal_fields" {

  "field_id" integer [not null]

  "record_added_at" timestamp [not null]

  "place_id" integer [not null]

  "value" numeric [not null]

}

  

Tabel "task_history" {

  "task_id" integer [not null]

  "activity" integer [not null]

  "task_history_id" integer [not null]

  "record_added_at" timestamp [not null]

  "user_id" integer [not null]

  "event_datetime" timestamp [not null]

  "payload" text

}

  

Tabel "tags" {

  "tag_label" "character varying(64)" [not null]

  "color" "character varying(6)"

  "user_id" integer [not null]

  "record_added_at" timestamp [not null]

  "tag_id" integer [not null]

}

  

Tabel "places" {

  "description" text

  "custom_fields" jsonb

  "place_id" integer [not null]

  "external_id" "character varying(32)"

  "record_added_at" timestamp [not null]

  "user_id" integer

  "latitude" numeric

  "radius" integer

  "place_label" "character varying(256)"

  "assigned_datetime" timestamp

  "address" "character varying(256)"

  "longitude" numeric

}

  

Tabel "status_listings" {

  "user_id" integer [not null]

  "is_supervisor_controlled" boolean [not null]

  "is_deleted" boolean [not null]

  "status_listing_id" integer [not null]

  "is_employee_controlled" boolean [not null]

  "record_added_at" timestamp [not null]

  "status_listing_label" "character varying(200)" [not null]

}

  

Tabel "models" {

  "record_added_at" timestamp [not null]

  "model_id" integer [not null]

  "has_battery_level" boolean [not null]

  "alternative_label" "character varying(50)" [not null]

  "vendor" "character varying(30)" [not null]

  "is_clone" boolean

  "has_altitude" boolean [not null]

  "has_phone" boolean [not null]

  "type_output_control" "character varying(30)" [not null]

  "has_gsm_roaming" boolean [not null]

  "has_gsm_level" boolean [not null]

  "model" "character varying(255)" [not null]

  "type_special_control" "character varying(255)" [not null]

  "digital_amount" integer [not null]

  "has_detach_button" boolean [not null]

  "has_gsm_name" boolean [not null]

  "analog_amount" integer [not null]

  "outputs_amount" integer [not null]

}

  

Tabel "vehicle_trackers_history" {

  "vehicle_id" integer [not null]

  "record_added_at" timestamp [not null]

  "object_id" integer [not null]

  "changed_datetime" timestamp [not null]

  "vehicle_tracker_history_id" integer [not null]

}

  

Tabel "groups" {

  "group_id" integer [not null]

  "group_color" "character varying(6)" [not null]

  "group_label" "character varying(255)" [not null]

  "client_id" integer [not null]

  "record_added_at" timestamp [not null]

}

  

Tabel "sensor_description" {

  "record_added_at" timestamp [not null]

  "parameters" jsonb

  "input_id" integer [not null]

  "accuracy" numeric [not null]

  "sensor_units" "character varying(10)"

  "multiplier" doubleprecision [not null]

  "input_label" "character varying(64)"

  "sensor_label" "character varying(100)"

  "units_type" integer [not null]

  "divider" doubleprecision [not null]

  "group_id" integer [not null]

  "sensor_id" integer [not null]

  "device_id" integer [not null]

  "sensor_type" "character varying(45)" [not null]

  "group_type" integer [not null]

}

  

Tabel "entities" {

  "entity_label" jsonb

  "record_added_at" timestamp [not null]

  "entity_id" integer [not null]

  "builtin_type" integer [not null]

  "user_id" integer [not null]

}

  

Tabel "zones" {

  "address" "character varying(255)"

  "radius" integer [not null]

  "zone_id" integer [not null]

  "circle_center_latitude" numeric [not null]

  "client_id" integer [not null]

  "zone_label" "character varying(100)"

  "color" "character varying(6)" [not null]

  "zone_type" "character varying(20)" [not null]

  "circle_center_longitude" numeric [not null]

  "latitude" numeric [not null]

  "record_added_at" timestamp [not null]

  "longitude" numeric [not null]

}

  

Tabel "vehicles" {

  "vehicle_id" integer [not null]

  "payload_length" numeric

  "vin" "character varying(20)"

  "free_insurance_policy_number" "character varying(50)"

  "vehicle_label" "character varying(100)"

  "payload_width" numeric

  "color" "character varying(6)"

  "trailer" "character varying(255)"

  "object_id" integer

  "vehicle_status_id" integer

  "liability_insurance_valid_till" timestamp

  "manufacture_year" integer

  "fuel_grade" "character varying(16)"

  "fuel_cost" numeric

  "fuel_tank_volume" numeric

  "model" "character varying(100)"

  "garage_id" integer

  "payload_height" numeric

  "max_speed" numeric

  "registration_number" "character varying(32)"

  "tyre_size" "character varying(50)"

  "passenger_capacity" integer

  "record_added_at" timestamp [not null]

  "trailer_reg_number" "character varying(32)"

  "free_insurance_valid_till_date" timestamp

  "gross_weight" numeric

  "standard_fuel_consumption" numeric

  "fuel_type" integer

  "payload_weight" numeric

  "additional_info" text

  "vehicle_subtype" "character varying(32)"

  "liability_insurance_policy_number" "character varying(50)"

  "frame_number" "character varying(32)"

  "user_id" integer [not null]

  "vehicle_type" integer [not null]

  "chassis_number" "character varying(32)"

  "tyres_number" integer

  "wheel_arrangement" "character varying(16)"

}

  

Tabel "tag_links" {

  "entity_id" integer [not null]

  "record_added_at" timestamp [not null]

  "entity_type" integer [not null]

  "ordinal" integer [not null]

  "tag_id" integer [not null]

}

  

Tabel "rules" {

  "rule_id" integer [not null]

  "object_id" integer [not null]

  "parameters" jsonb

  "alert_phone" "character varying(210)" [not null]

  "event_type" "character varying(100)" [not null]

  "client_id" integer [not null]

  "is_push_enabled" boolean [not null]

  "event_comment1" "character varying(255)" [not null]

  "event_label" "character varying(255)" [not null]

  "description" "character varying(255)" [not null]

  "record_added_at" timestamp [not null]

  "alert_sms" text [not null]

  "event_group" integer [not null]

  "created_at" timestamp [not null]

  "maximum" integer [not null]

  "is_deleted" boolean [not null]

  "alert_email" text [not null]

  "event_comment2" "character varying(255)" [not null]

}

  

Tabel "status_history" {

  "longitude" numeric

  "new_status_id" integer

  "status_history_id" integer [not null]

  "device_id" integer [not null]

  "updated_by" integer [not null]

  "address" "character varying(255)"

  "latitude" numeric

  "record_added_at" timestamp [not null]

  "server_datetime" timestamp [not null]

  "changed_datetime" timestamp

  "old_status_id" integer

}

  

Tabel "rules2zones" {

  "zone_id" integer [not null]

  "record_added_at" timestamp [not null]

  "rule_id" integer [not null]

}

  

Tabel "forms" {

  "object_id" integer [not null]

  "description" text

  "form_label" "character varying(255)" [not null]

  "fields" text

  "created_at" timestamp [not null]

  "submission_address" "character varying(255)"

  "submission_latitude" numeric

  "form_id" integer [not null]

  "submission_longitude" numeric

  "is_submission_in_zone" boolean [not null]

  "values" text

  "record_added_at" timestamp [not null]

  "task_id" integer

  "submitted_at" timestamp

}

  

Tabel "sensor_calibration_data" {

  "value" numeric [not null]

  "sensor_id" integer [not null]

  "record_added_at" timestamp [not null]

  "volume" numeric [not null]

  "device_id" integer [not null]

  "sensor_calibration_id" bigint [not null]

}

  

Tabel "rules2objects" {

  "object_params" jsonb

  "param_group_number" integer [not null]

  "object_id" integer [not null]

  "record_added_at" timestamp [not null]

  "rule_id" integer [not null]

}

  

Tabel "tasks" {

  "time_from" timestamp

  "stay_duration_minutes" interval

  "external_id" "character varying(100)"

  "object_id" integer

  "task_type" integer

  "arrival_duration_minutes" interval

  "status" integer

  "arrival_datetime" timestamp

  "record_added_at" timestamp [not null]

  "task_id" integer [not null]

  "user_id" integer

  "status_change_datetime" timestamp

  "order_sort" integer

  "time_to" timestamp

  "max_delay_minuts" integer

  "is_stay_control_enabled" boolean

  "address" "character varying(255)"

  "task_label" "character varying(200)" [not null]

  "longitude" numeric

  "created_by" integer

  "description" text [not null]

  "radius" integer

  "latitude" numeric

  "stay_duration" integer

  "created_at" timestamp [not null]

  "custom_fields" jsonb

  "parent_task_id" integer

}

  

Tabel "places_bigint_fields" {

  "field_id" integer [not null]

  "value" bigint [not null]

  "place_id" integer [not null]

  "record_added_at" timestamp [not null]

}

  

Tabel "devices" {

  "is_sim_blocked" boolean [not null]

  "device_id" integer [not null]

  "device_imei" "character varying(64)" [not null]

  "network_label" "character varying(50)" [not null]

  "status_listing_id" integer [not null]

  "signal_level" numeric [not null]

  "phone" "character varying(32)" [not null]

  "has_roaming" boolean [not null]

  "created_at" timestamp [not null]

  "owner_id" integer [not null]

  "record_added_at" timestamp [not null]

}

  

Tabel "description_parametrs" {

  "description" "character varying(150)"

  "record_added_at" timestamp [not null]

  "type" "character varying(100)" [not null]

  "key" integer [not null]

}

  

Tabel "users" {

  "company_label" "character varying(255)" [not null]

  "registration_datetime" timestamp

  "first_name" "character varying(100)" [not null]

  "master_id" integer

  "last_name" "character varying(100)" [not null]

  "birth_date" timestamp

  "timezone_label" "character varying(30)"

  "middle_name" "character varying(100)" [not null]

  "user_id" integer [not null]

  "locale" "character varying(10)" [not null]

  "record_added_at" timestamp [not null]

}

  

Tabel "counters" {

  "sensor_id" integer

  "multiplier" numeric [not null]

  "counter_id" integer [not null]

  "device_id" integer [not null]

  "counter_type" integer [not null]

  "record_added_at" timestamp [not null]

}

  

Tabel "employees" {

  "driver_license_valid_till" timestamp

  "record_added_at" timestamp [not null]

  "last_name" "character varying(100)"

  "department_id" integer

  "citizen_id_number" "character varying(32)"

  "first_name" "character varying(100)"

  "driver_license_categories" "character varying(32)"

  "user_id" integer [not null]

  "phone_number" "character varying(32)"

  "object_id" integer

  "is_deleted" boolean [not null]

  "driver_license_issue_date" boolean

  "hardware_key" "character varying(64)"

  "middle_name" "character varying(100)"

  "address" "character varying(255)"

  "latitude" numeric

  "employee_id" integer [not null]

  "personnel_number" "character varying(15)"

  "fuel_cost" doubleprecision

  "driver_license_number" "character varying(32)"

  "email" "character varying(100)"

  "fuel_consumption" doubleprecision

  "radius" integer [not null]

  "longitude" numeric

}

  

Tabel "places_longtext_fields" {

  "field_id" integer [not null]

  "value" text [not null]

  "record_added_at" timestamp [not null]

  "place_id" integer [not null]

}

  

Tabel "raw_device_data" {

  "device_id" integer

  "device_time" timestamp

  "created_at" timestamp

  "gps_fix_type" integer

  "longitude" integer

  "latitude" integer

  "altitude" integer

  "speed" integer

  "satellites" integer

  "hdop" integer

  "event_id" integer

  "inputs" jsonb

  "states" jsonb

}

  

Tabel "groups_objects" {

  "groups_client_id" integer

  "objects_client_id" integer

  

  Indeks {

    (groups_client_id, objects_client_id) [pk]

  }

}

  

Ref:"employees"."employee_id" < "checkins"."employee_id"

  

Ref:"objects"."object_id" < "checkins"."object_id"

  

Ref:"forms"."form_id" < "checkins"."form_id"

  

Ref:"sensor_description"."sensor_id" < "counters"."sensor_id"

  

Ref:"devices"."device_id" < "counters"."device_id"

  

Ref:"entities"."entity_id" < "custom_fields"."entity_id"

  

Ref:"departments"."department_id" < "employees"."department_id"

  

Ref:"users"."user_id" < "departments"."user_id"

  

Ref:"description_parametrs"."key" < "counters"."counter_type"

  

Ref:"description_parametrs"."key" < "custom_fields"."custom_field_type"

  

Ref:"description_parametrs"."key" < "driver_history"."updated_by"

  

Ref:"description_parametrs"."key" < "entities"."builtin_type"

  

Ref:"description_parametrs"."key" < "sensor_description"."units_type"

  

Ref:"description_parametrs"."key" < "status_history"."updated_by"

  

Ref:"description_parametrs"."key" < "tasks"."status"

  

Ref:"description_parametrs"."key" < "tasks"."created_at"

  

Ref:"description_parametrs"."key" < "tasks"."task_type"

  

Ref:"description_parametrs"."key" < "vehicles"."fuel_type"

  

Ref:"description_parametrs"."key" < "task_history"."activity"

  

Ref:"description_parametrs"."key" < "sensor_description"."group_type"

  

Ref:"devices"."device_id" < "device_output_name"."device_id"

  

Ref:"status_listings"."status_listing_id" < "devices"."status_listing_id"

  

Ref:"employees"."employee_id" < "driver_history"."new_employee_id"

  

Ref:"employees"."employee_id" < "driver_history"."old_employee_id"

  

Ref:"objects"."object_id" < "driver_history"."object_id"

  

Ref:"objects"."object_id" < "employees"."object_id"

  

Ref:"users"."user_id" < "employees"."user_id"

  

Ref:"users"."user_id" < "entities"."user_id"

  

Ref:"tasks"."task_id" < "forms"."task_id"

  

Ref:"objects"."object_id" < "forms"."object_id"

  

Ref:"objects"."object_id" < "tasks"."object_id"

  

Ref:"users"."user_id" < "garages"."user_id"

  

Ref:"groups"."client_id" < "groups_objects"."groups_client_id"

  

Ref:"objects"."client_id" < "groups_objects"."objects_client_id"

  

Ref:"models"."model" < "objects"."model"

  

Ref:"devices"."device_id" < "objects"."device_id"

  

Ref:"users"."user_id" < "places"."user_id"

  

Ref:"custom_fields"."custom_field_id" < "places_bigint_fields"."field_id"

  

Ref:"places"."place_id" < "places_bigint_fields"."place_id"

  

Ref:"custom_fields"."custom_field_id" < "places_decimal_fields"."field_id"

  

Ref:"places"."place_id" < "places_decimal_fields"."place_id"

  

Ref:"custom_fields"."custom_field_id" < "places_linked_entity_fields"."field_id"

  

Ref:"places"."place_id" < "places_linked_entity_fields"."place_id"

  

Ref:"custom_fields"."custom_field_id" < "places_longtext_fields"."field_id"

  

Ref:"places"."place_id" < "places_longtext_fields"."place_id"

  

Ref:"custom_fields"."custom_field_id" < "places_text_fields"."field_id"

  

Ref:"places"."place_id" < "places_text_fields"."place_id"

  

Ref:"rules"."rule_id" < "rules2zones"."rule_id"

  

Ref:"objects"."object_id" < "rules2objects"."object_id"

  

Ref:"rules"."rule_id" < "rules2objects"."object_id"

  

Ref:"zones"."zone_id" < "rules2zones"."zone_id"

  

Ref:"sensor_description"."sensor_id" < "sensor_calibration_data"."device_id"

  

Ref:"sensor_description"."device_id" < "sensor_calibration_data"."sensor_id"

  

Ref:"devices"."device_id" < "sensor_description"."device_id"

  

Ref:"statuses"."status_id" < "status_history"."new_status_id"

  

Ref:"statuses"."status_id" < "status_history"."old_status_id"

  

Ref:"devices"."device_id" < "status_history"."device_id"

  

Ref:"users"."user_id" < "status_listings"."user_id"

  

Ref:"status_listings"."status_listing_id" < "statuses"."listing_id"

  

Ref:"tags"."tag_id" < "tag_links"."tag_id"

  

Ref:"users"."user_id" < "tags"."user_id"

  

Ref:"tasks"."task_id" < "task_history"."task_id"

  

Ref:"users"."user_id" < "task_history"."user_id"

  

Ref:"tasks"."parent_task_id" < "tasks"."task_id"

  

Ref:"users"."user_id" < "tasks"."user_id"

  

Ref:"users"."master_id" < "users"."user_id"

  

Ref:"users"."user_id" < "users2zones"."user_id"

  

Ref:"zones"."zone_id" < "users2zones"."zone_id"

  

Ref:"vehicles"."vehicle_id" < "vehicle_service_tasks"."vehicle_id"

  

Ref:"objects"."object_id" < "vehicle_trackers_history"."object_id"

  

Ref:"vehicles"."vehicle_id" < "vehicle_trackers_history"."vehicle_id"

  

Ref:"garages"."garage_id" < "vehicles"."garage_id"

  

Ref:"objects"."object_id" < "vehicles"."object_id"

  

Ref:"users"."user_id" < "vehicles"."user_id"

  

Ref:"zones"."zone_id" < "geofence_points"."zone_id"

  

Ref:"sensor_calibration_data"."value" < "sensor_calibration_data"."device_id"

  

Ref:"devices"."device_id" < "raw_device_data"."device_id"

  

Ref:"users"."user_id" < "devices"."owner_id"

  

Ref:"users"."user_id" < "objects"."client_id"

Frekuensi pembaruan

Data dalam skema ini disinkronkan dengan DB inti. Pembaruan terjadi secara bertahap saat perubahan terjadi di basis data MySQL sumber, biasanya kurang dari 5 menit sejak perubahan sumber.

`description_parameters`

Sistem mencakup data referensi untuk menstandarisasi nilai di seluruh basis data:

Tipe referensi

Deskripsi

Contoh nilai

Definisi tipe

Tipe entitas standar

vehicle_type: car, truck, bus

Kode status

Nilai status tugas dan sistem

tasks_status: unassigned, assigned, done

Definisi satuan

Satuan pengukuran untuk sensor

units_type: liter, gallon, celsius

Klasifikasi entitas

Kategori entitas bisnis

entities_type: place, task, customer

Tabel utama menurut kategori

Tabel dalam raw_business_data skema diatur ke dalam kategori fungsional untuk memudahkan navigasi. Tabel di bawah merangkum tabel utama menurut tujuan bisnis mereka:

Ikhtisar Skema Database

Kategori

Nama tabel

Deskripsi

Struktur organisasi

users
departments
employees
groups

Akun pengguna dengan informasi profil
Departemen dengan data geolokasi
Detail karyawan dan pengemudi
Grup organisasi tracker

Objek dan perangkat

devices
models
objects
vehicles
sensor_description

Perangkat pelacak fisik
Spesifikasi model perangkat
Objek yang dimonitor
Detail dan spesifikasi kendaraan
Detail konfigurasi sensor

Tempat dan zona

places
zones
garages
tags

Titik minat dengan geolokasi
Area pemantauan geofence
Lokasi layanan kendaraan
Label organisasi

Data operasional

tasks
forms
checkins
events
statuses
vehicle_service_tasks

Penugasan dan pelacakan tugas
Formulir pengumpulan data
Catatan kehadiran berbasis lokasi
Peristiwa sistem dan notifikasi
Definisi status
Catatan pemeliharaan kendaraan

`raw_telematics_data` struktur

Skema raw_telematics_data mengandung tiga tipe tabel utama yang bekerja bersama untuk menyediakan data perangkat yang komprehensif.

Diagram interaktif skema raw_telematics_data tersedia di dbdiagram.io: https://dbdiagram.io/d/v1-schema-telematics-bd-67a0acef263d6cf9a0d8e750

Temukan detail skema data telemetri mentah di bawah.

skema raw_telematics_data

Tabel tracking_data_core {

  device_id integer [primary key]

  device_time timestampz [primary key]

  platform_time timestampz

  record_added_at timestampz [default: `now()`]

  latitude integer

  longitude integer

  speed integer

  altitude integer

  satellites integer

  event_id integer

  gps_fix_type integer

  hdop integer

  

  indexes {(device_id, device_time)}

}

  

Tabel inputs {

  event_id integer [primary key]

  device_id integer [primary key]

  record_added_at timestampz [default: `now()`]

  device_time timestampz [primary key]

  sensor_name text [primary key]

  value text

  indexes {(device_id, device_time)}

}

  

Tabel states {

  event_id integer [primary key]

  device_id serial [primary key]

  record_added_at timestampz [default: `now()`]

  device_time timestampz [primary key]

  state_name text [primary key]

  value text

  indexes {(device_id, device_time)}

}

  

Ref: inputs.(device_id, device_time) > tracking_data_core.(device_id, device_time)

Ref: states.(device_id, device_time) > tracking_data_core.(device_id, device_time)

Tabel utama menurut kategori

Setiap tabel memiliki tujuan spesifik dalam menangkap aspek berbeda dari informasi perangkat:

`tracking_data_core`

Tujuan: Data inti lokasi dan gerakan

Atribut

Rincian

Kolom kunci

device_id, device_time, platform_time, latitude, longitude, speed, altitude, satellites, hdop, event_id

Pengindeksan

Dioptimalkan dengan indeks pada (device_id, device_time)

Catatan khusus

Data lokasi (lintang dan bujur) menggunakan format integer dengan presisi 10⁷ untuk kinerja TimescaleDB yang optimal Kecepatan juga disimpan dalam integer, jadi Anda perlu membaginya dengan 100

`inputs`

Tujuan: Pembacaan sensor dari perangkat

Atribut

Rincian

Kolom kunci

input_id, device_id, device_time, sensor_name, value

Konten

Pembacaan analog (level bahan bakar, suhu, tegangan), nilai terhitung (RPM mesin)

Hubungan

FROM raw_telematics_data.inputs AS i
JOIN raw_business_data.sensor_description AS sd
    ON i.device_id = sd.device_id AND i.sensor_name = sd.input_label
JOIN raw_telematics_data.tacking_data_core AS tdc
    ON i.device_id = tdc.device_id AND i.device_time = tdc.device_time

`states`

Tujuan: Indikator status perangkat dan mode operasional

Atribut

Rincian

Kolom Kunci

state_id, device_id, device_time, state_name, value

Konten

Indikator mode operasi (bekerja, menganggur, mati), status komponen (kontak, pintu)

Format Nilai

Nilai Boolean (1/0) atau kode status spesifik

Data dalam skema ini diambil langsung dari perangkat, dengan latensi minimal (biasanya beberapa detik). Skema dioptimalkan untuk data deret waktu menggunakan TimescaleDB untuk penyimpanan dan pengambilan yang efisien.

Informasi tambahan

Validasi data

Basis data menegakkan integritas data melalui beberapa mekanisme:

KONSTRANGSI CHECK memvalidasi bahwa nilai berada dalam rentang yang dapat diterima
Kunci asing memastikan hubungan antar tabel tetap konsisten
KONSTRANGSI NOT NULL menjamin bahwa bidang yang diperlukan selalu memiliki nilai
Nilai DEFAULT memberikan nilai cadangan ketika data tidak diberikan secara eksplisit

Optimisasi kueri

Tabel diatur dengan strategi pengindeksan spesifik:

Semua tabel menyertakan indeks berbasis waktu pada record_added_at
Kolom kunci asing memiliki indeks khusus untuk kinerja join
Kombinasi kolom yang sering digunakan memiliki indeks komposit
TimescaleDB menyediakan indeks khusus untuk kueri deret waktu

`repo` struktur data

Skema ini saat ini dalam pengembangan. Jika Anda tertarik akses awal atau memiliki pertanyaan tentang fungsionalitas ini, silakan hubungi [email protected].

Skema repo skema menyediakan kerangka kerja komprehensif untuk mengelola struktur organisasi, aset, perangkat, dan hubungan mereka dalam lingkungan multi-tenant. Dibangun di atas PostgreSQL 14+ dengan ekstensi ltree, skema mendukung organisasi hierarkis, definisi bidang kustom untuk setiap tipe entitas, kontrol akses berbasis peran dengan pembatasan tingkat objek, dan jejak audit lengkap dengan pelacakan perubahan tingkat bidang. Semua entitas dapat diperluas tanpa modifikasi skema, dilokalkan untuk penerapan internasional, dan dihubungkan melalui hubungan polimorfik fleksibel.

Skema ini menangani skenario manajemen data yang kompleks termasuk hierarki aset armada di berbagai tingkat organisasi, platform SaaS multi-tenant yang membutuhkan isolasi data, operasi yang dipandu kepatuhan dengan kebutuhan audit terperinci, dan sistem yang membutuhkan model data dinamis yang dapat disesuaikan melalui bidang kustom daripada migrasi basis data.

Diagram interaktif darirepo skema data tersedia di dbdiagram.io: https://dbdiagram.io/d/Navixy-Repo-data-schema-68ad788c1e7a611967a0930e

Temukan repo detail skema di bawah.

repo skema data

// ============================================
// Skema DataHub Baru - Customer Journey
// PostgreSQL 14+ dengan ekstensi ltree
// Versi: 2.0 (Konsep)
// ============================================

// ============================================
// TABEL REFERENSI DASAR (hierarki ci_base)
// ============================================

Tabel ci_base {
  id uuid [primary key]
  code text [not null]
  title_en text [not null]
  order int
  is_system boolean [not null, default: false]
  discriminator text [not null]
  catalog_id uuid
  organization_id uuid
  parent_id uuid
  path ltree
  is_hierarchical boolean [default: false]
  extra jsonb
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  
  indexes {
    (parent_id) [name: 'idx_ci_parent']
    (path) [type: gist, name: 'idx_ci_path_gist']
    (catalog_id) [name: 'idx_ci_catalog']
    (organization_id) [name: 'idx_ci_org']
    (discriminator) [name: 'idx_ci_discriminator']
    (code) [unique, name: 'uq_ci_code_per_type']
    (organization_id, code) [unique, name: 'uq_ci_org_code']
  }
}

Tabel ci_module {
  id uuid [primary key]
}

Tabel ci_catalog_category {
  id uuid [primary key]
}

Tabel ci_country {
  id uuid [primary key]
}

Tabel ci_role {
  id uuid [primary key]
}

Tabel ci_entity_type {
  id uuid [primary key]
}

Tabel ci_device_status {
  id uuid [primary key]
}

Tabel ci_permission_scope {
  id uuid [primary key]
  module_id uuid
  entity_type_id uuid
  category text
}

Tabel ci_device_type {
  id uuid [primary key]
}

Tabel ci_asset_type {
  id uuid [primary key]
  category_id uuid
}

Tabel ci_asset_type_category {
  id uuid [primary key]
}

Tabel ci_inventory_type {
  id uuid [primary key]
}

Tabel ci_organization_type {
  id uuid [primary key]
}

Tabel ci_user_type {
  id uuid [primary key]
}

Tabel ci_asset_group_type {
  id uuid [primary key]
  max_items int
  color text
  icon text
  allowed_asset_type_id uuid
}

Tabel ci_device_relation_type {
  id uuid [primary key]
}

Tabel ci_tag {
  id uuid [primary key]
  entity_type_id uuid
  color text
}

// ============================================
// ENTITAS DASAR DENGAN DUKUNGAN BIDANG KUSTOM
// ============================================

Tabel customizable_entity {
  id uuid [primary key]
  entity_type_id uuid [not null]
  cf_data jsonb
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (entity_type_id) [name: 'idx_customizable_entity_type']
  }
}

// ============================================
// ENTITAS BISNIS INTI
// ============================================

Tabel organization {
  id uuid [primary key]
  parent_id uuid
  path ltree
  organization_type_id uuid [not null]
  title_en text [not null]
  is_active boolean [not null, default: true]
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  deleted_by uuid
  
  indexes {
    (parent_id) [name: 'idx_org_parent']
    (path) [type: gist, name: 'idx_org_path_gist']
    (organization_type_id) [name: 'idx_org_type']
  }
}

Tabel catalog {
  id uuid [primary key]
  organization_id uuid [not null]
  module_id uuid
  category_id uuid
  title_en text [not null]
  is_system boolean [not null, default: false]
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (organization_id) [name: 'idx_catalog_org']
    (module_id) [name: 'idx_catalog_module']
  }
}

Tabel user {
  id uuid [primary key]
  organization_id uuid [not null]
  user_type_id uuid [not null]
  identity_provider text [not null]
  identity_provider_id uuid [not null]
  full_name text [not null]
  is_active boolean [not null, default: true]
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  deleted_by uuid
  
  indexes {
    (organization_id) [name: 'idx_user_org']
    (user_type_id) [name: 'idx_user_type']
    (organization_id, identity_provider, identity_provider_id) [unique, name: 'uq_user_org_idp']
  }
}

// ============================================
// KONTROL AKSES (ACL)
// ============================================

Tabel user_role {
  id uuid [primary key]
  user_id uuid [not null]
  role_id uuid [not null]
  assigned_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  assigned_by uuid
  
  indexes {
    (user_id) [name: 'idx_user_role_user']
    (role_id) [name: 'idx_user_role_role']
    (user_id, role_id) [unique, name: 'uq_user_role']
  }
}

Tabel acl_role_permission {
  id uuid [primary key]
  role_id uuid [not null]
  permission_scope_id uuid [not null]
  target_entity_id uuid
  actions int [not null]
  granted_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  granted_by uuid
  
  indexes {
    (role_id) [name: 'idx_acl_role_perm_role']
    (permission_scope_id) [name: 'idx_acl_role_perm_scope']
    (role_id, permission_scope_id, target_entity_id) [unique, name: 'uq_acl_role_permission']
  }
}

Tabel acl_user_scope {
  id uuid [primary key]
  user_id uuid [not null]
  permission_scope_id uuid [not null]
  target_entity_id uuid [not null]
  actions int [not null]
  
  indexes {
    (user_id, permission_scope_id) [name: 'idx_acl_user_scope_user']
    (user_id, permission_scope_id, target_entity_id) [unique, name: 'uq_acl_user_scope']
  }
}

// ============================================
// ENTITAS BISNIS
// ============================================

Tabel asset {
  id uuid [primary key]
  organization_id uuid [not null]
  asset_type_id uuid [not null]
  label text [not null]
  description text
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  deleted_by uuid
  
  indexes {
    (organization_id) [name: 'idx_asset_org']
    (asset_type_id) [name: 'idx_asset_type']
  }
}

Tabel inventory {
  id uuid [primary key]
  organization_id uuid [not null]
  inventory_type_id uuid [not null]
  code text [not null]
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  deleted_by uuid
  
  indexes {
    (organization_id) [name: 'idx_inventory_org']
    (inventory_type_id) [name: 'idx_inventory_type']
    (organization_id, code) [unique, name: 'uq_inventory_org_code']
  }
}

Tabel device {
  id uuid [primary key]
  organization_id uuid [not null]
  device_type_id uuid [not null]
  status_id uuid [not null]
  hw_id text
  label text [not null]
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  deleted_by uuid
  
  indexes {
    (organization_id) [name: 'idx_device_org']
    (device_type_id) [name: 'idx_device_type']
    (status_id) [name: 'idx_device_status']
    (hw_id) [name: 'idx_device_hw_id']
  }
}

Tabel device_asset_link {
  id uuid [primary key]
  device_id uuid [unique, not null]
  asset_id uuid [not null]
  linked_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  linked_by uuid
  
  indexes {
    (device_id) [unique, name: 'idx_device_asset_link_device']
    (asset_id) [name: 'idx_device_asset_link_asset']
  }
}

Tabel device_inventory_link {
  id uuid [primary key]
  device_id uuid [unique, not null]
  inventory_id uuid [not null]
  linked_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  linked_by uuid
  
  indexes {
    (device_id) [unique, name: 'idx_device_inventory_link_device']
    (inventory_id) [name: 'idx_device_inventory_link_inventory']
  }
}

Tabel device_relation {
  id uuid [primary key]
  master_id uuid [not null]
  slave_id uuid [not null]
  relation_type_id uuid [not null]
  
  indexes {
    (master_id) [name: 'idx_device_relation_master']
    (slave_id) [name: 'idx_device_relation_slave']
    (master_id, slave_id, relation_type_id) [unique, name: 'uq_device_relation']
  }
}

Tabel asset_group {
  id uuid [primary key]
  organization_id uuid [not null]
  group_type_id uuid [not null]
  title_en text [not null]
  description text
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  deleted_at timestamptz
  deleted_by uuid
  
  indexes {
    (organization_id) [name: 'idx_asset_group_org']
    (group_type_id) [name: 'idx_asset_group_type']
  }
}

Tabel asset_group_item {
  id uuid [primary key]
  group_id uuid [not null]
  asset_id uuid [not null]
  attached_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  detached_at timestamptz
  
  indexes {
    (group_id) [name: 'idx_asset_group_item_group']
    (asset_id) [name: 'idx_asset_group_item_asset']
    (group_id, asset_id, detached_at) [unique, name: 'uq_asset_group_item']
  }
}

Tabel entity_tag {
  id uuid [primary key]
  tag_id uuid [not null]
  entity_id uuid [not null]
  tagged_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (tag_id) [name: 'idx_entity_tag_tag']
    (entity_id) [name: 'idx_entity_tag_entity']
    (tag_id, entity_id) [unique, name: 'uq_entity_tag']
  }
}

// ============================================
// LOKALISASI
// ============================================

Tabel i18n_text {
  entity_id uuid [pk]
  field_code text [pk]
  locale text [pk]
  text_value text [not null]
  
  indexes {
    (entity_id) [name: 'idx_i18n_entity']
    (locale) [name: 'idx_i18n_locale']
  }
}

// ============================================
// BIDANG KUSTOM - DEFINISI
// ============================================

Tabel custom_field_def {
  id uuid [primary key]
  organization_id uuid [not null]
  owner_entity_type_id uuid [not null]
  code text [not null]
  title_en text [not null]
  field_type text [not null]
  is_multi boolean [not null, default: false]
  is_required boolean [not null, default: false]
  order int
  ref_entity_type_id uuid
  ref_catalog_id uuid
  created_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  updated_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (organization_id) [name: 'idx_cfd_org']
    (owner_entity_type_id) [name: 'idx_cfd_owner_type']
    (organization_id, owner_entity_type_id, code) [unique, name: 'uq_cfd_org_type_code']
  }
}

// ============================================
// BIDANG KUSTOM - NILAI (berdasarkan tipe)
// ============================================

Tabel custom_field_value_text {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  value text [not null]
  
  indexes {
    (field_def_id, value) [name: 'idx_cfv_text_value']
  }
}

Tabel custom_field_value_number {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  value numeric [not null]
  
  indexes {
    (field_def_id, value) [name: 'idx_cfv_number_value']
  }
}

Tabel custom_field_value_boolean {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  value boolean [not null]
  
  indexes {
    (field_def_id, value) [name: 'idx_cfv_boolean_value']
  }
}

Tabel custom_field_value_date {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  value date [not null]
  
  indexes {
    (field_def_id, value) [name: 'idx_cfv_date_value']
  }
}

Tabel custom_field_value_datetime {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  value timestamptz [not null]
  
  indexes {
    (field_def_id, value) [name: 'idx_cfv_datetime_value']
  }
}

Tabel custom_field_value_entity {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  ref_entity_id uuid [not null]
  
  indexes {
    (field_def_id, ref_entity_id) [name: 'idx_cfv_entity_value']
  }
}

Tabel custom_field_value_catalog {
  customizable_entity_id uuid [pk]
  field_def_id uuid [pk]
  value_index smallint [pk]
  ref_item_id uuid [not null]
  
  indexes {
    (field_def_id, ref_item_id) [name: 'idx_cfv_catalog_value']
  }
}

// ============================================
// AUDIT
// ============================================

Tabel audit_event {
  id uuid [primary key]
  event_category text [not null]
  user_id uuid
  identity_provider_id uuid
  ip_address inet
  user_agent text
  aggregate_type text
  aggregate_id uuid
  event_type text [not null]
  event_data jsonb
  occurred_at timestamptz [not null, default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (user_id, occurred_at) [name: 'idx_audit_event_user']
    (aggregate_type, aggregate_id, occurred_at) [name: 'idx_audit_event_aggregate']
    (event_category, occurred_at) [name: 'idx_audit_event_category']
    (event_type, occurred_at) [name: 'idx_audit_event_type']
  }
}

// ============================================
// HUBUNGAN
// ============================================

Ref: ci_base.catalog_id > catalog.id
Ref: ci_base.organization_id > organization.id
Ref: ci_base.parent_id > ci_base.id

Ref: ci_module.id - ci_base.id
Ref: ci_catalog_category.id - ci_base.id
Ref: ci_country.id - ci_base.id
Ref: ci_role.id - ci_base.id
Ref: ci_entity_type.id - ci_base.id
Ref: ci_device_status.id - ci_base.id
Ref: ci_permission_scope.id - ci_base.id
Ref: ci_device_type.id - ci_entity_type.id
Ref: ci_asset_type.id - ci_entity_type.id
Ref: ci_asset_type_category.id - ci_base.id
Ref: ci_inventory_type.id - ci_entity_type.id
Ref: ci_organization_type.id - ci_entity_type.id
Ref: ci_user_type.id - ci_entity_type.id
Ref: ci_asset_group_type.id - ci_entity_type.id
Ref: ci_device_relation_type.id - ci_base.id
Ref: ci_tag.id - ci_base.id

Ref: ci_permission_scope.module_id > ci_module.id
Ref: ci_permission_scope.entity_type_id > ci_entity_type.id
Ref: ci_asset_type.category_id > ci_asset_type_category.id
Ref: ci_asset_group_type.allowed_asset_type_id > ci_asset_type.id
Ref: ci_tag.entity_type_id > ci_entity_type.id

Ref: customizable_entity.entity_type_id > ci_entity_type.id

Ref: organization.id - customizable_entity.id
Ref: organization.parent_id > organization.id
Ref: organization.organization_type_id > ci_organization_type.id
Ref: organization.deleted_by > user.id

Ref: catalog.organization_id > organization.id
Ref: catalog.module_id > ci_module.id
Ref: catalog.category_id > ci_catalog_category.id

Ref: user.id - customizable_entity.id
Ref: user.organization_id > organization.id
Ref: user.user_type_id > ci_user_type.id
Ref: user.deleted_by > user.id

Ref: user_role.user_id > user.id
Ref: user_role.role_id > ci_role.id
Ref: user_role.assigned_by > user.id

Ref: acl_role_permission.role_id > ci_role.id
Ref: acl_role_permission.permission_scope_id > ci_permission_scope.id
Ref: acl_role_permission.granted_by > user.id

Ref: acl_user_scope.user_id > user.id
Ref: acl_user_scope.permission_scope_id > ci_permission_scope.id

Ref: asset.id - customizable_entity.id
Ref: asset.organization_id > organization.id
Ref: asset.asset_type_id > ci_asset_type.id
Ref: asset.deleted_by > user.id

Ref: inventory.id - customizable_entity.id
Ref: inventory.organization_id > organization.id
Ref: inventory.inventory_type_id > ci_inventory_type.id
Ref: inventory.deleted_by > user.id

Ref: device.id - customizable_entity.id
Ref: device.organization_id > organization.id
Ref: device.device_type_id > ci_device_type.id
Ref: device.status_id > ci_device_status.id
Ref: device.deleted_by > user.id

Ref: device_asset_link.device_id - device.id
Ref: device_asset_link.asset_id > asset.id
Ref: device_asset_link.linked_by > user.id

Ref: device_inventory_link.device_id - device.id
Ref: device_inventory_link.inventory_id > inventory.id
Ref: device_inventory_link.linked_by > user.id

Ref: device_relation.master_id > device.id
Ref: device_relation.slave_id > device.id
Ref: device_relation.relation_type_id > ci_device_relation_type.id

Ref: asset_group.id - customizable_entity.id
Ref: asset_group.organization_id > organization.id
Ref: asset_group.group_type_id > ci_asset_group_type.id
Ref: asset_group.deleted_by > user.id

Ref: asset_group_item.group_id > asset_group.id
Ref: asset_group_item.asset_id > asset.id

Ref: entity_tag.tag_id > ci_tag.id

Ref: custom_field_def.organization_id > organization.id
Ref: custom_field_def.owner_entity_type_id > ci_entity_type.id
Ref: custom_field_def.ref_entity_type_id > ci_entity_type.id
Ref: custom_field_def.ref_catalog_id > catalog.id

Ref: custom_field_value_text.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_text.field_def_id > custom_field_def.id

Ref: custom_field_value_number.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_number.field_def_id > custom_field_def.id

Ref: custom_field_value_boolean.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_boolean.field_def_id > custom_field_def.id

Ref: custom_field_value_date.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_date.field_def_id > custom_field_def.id

Ref: custom_field_value_datetime.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_datetime.field_def_id > custom_field_def.id

Ref: custom_field_value_entity.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_entity.field_def_id > custom_field_def.id
Ref: custom_field_value_entity.ref_entity_id > customizable_entity.id

Ref: custom_field_value_catalog.customizable_entity_id > customizable_entity.id
Ref: custom_field_value_catalog.field_def_id > custom_field_def.id
Ref: custom_field_value_catalog.ref_item_id > ci_base.id

Ref: audit_event.user_id > user.id

Frekuensi pembaruan

Data di repo skema disinkronkan secara real-time dengan sistem sumber. Pembaruan terjadi segera saat perubahan terjadi, dengan jejak audit yang merekam semua modifikasi untuk kepatuhan dan analisis historis.

`ci_base`

Skema repo skema menggunakan pola Single Table Inheritance untuk semua data referensi melalui ci_base tabel:

Skema repo skema menggunakan Single Table Inheritance pola untuk semua data referensi melalui ci_base tabel. Desain ini mengonsolidasikan kamus sistem, klasifikasi, dan item referensi yang ditentukan pengguna ke dalam satu struktur terpadu, memberikan konsistensi dan fleksibilitas di seluruh skema.

Arsitektur:

Skema ci_base tabel berfungsi sebagai dasar untuk semua data referensi, menggunakan discriminator field untuk mengidentifikasi jenis referensi tertentu. Setiap jenis referensi memiliki tabel terkait (seperti ci_device_type, ci_asset_type) yang berbagi id sebagai ci_base, menciptakan hubungan pewarisan yang type-safe.

Bagaimana entitas bisnis terhubung ke ci_base:

Semua entitas bisnis dalam repo skema merujuk ci_base subtipe untuk menentukan klasifikasi dan perilaku mereka:

organization → merujuk ci_organization_type (yang mewarisi dari ci_entity_type → ci_base)
user → merujuk ci_user_type (yang mewarisi dari ci_entity_type → ci_base)
device → merujuk ci_device_type dan ci_device_status (keduanya mewarisi dari ci_base)
asset → merujuk ci_asset_type (yang mewarisi dari ci_entity_type → ci_base)
inventory → merujuk ci_inventory_type (yang mewarisi dari ci_entity_type → ci_base)
asset_group → merujuk ci_asset_group_type (yang mewarisi dari ci_entity_type → ci_base)

Kategori jenis referensi:

Kategori

Tabel

Tujuan

Konfigurasi sistem

ci_module, ci_country, ci_role

Mendefinisikan modul sistem, referensi geografis, dan peran pengguna

Definisi jenis entitas

ci_entity_type, ci_device_type, ci_asset_type, ci_inventory_type, ci_organization_type, ci_user_type, ci_asset_group_type

Mengklasifikasikan semua entitas bisnis berdasarkan jenis

Status dan klasifikasi

ci_device_status, ci_asset_type_category

Melacak status entitas dan mengelompokkan jenis ke dalam kategori

Kontrol akses

ci_permission_scope

Mendefinisikan izin apa yang dapat diberikan (terkait dengan ci_module dan ci_entity_type)

Hubungan

ci_device_relation_type

Mendefinisikan jenis hubungan antara perangkat (master-slave, cadangan, dll.)

Kategorisasi

ci_tag, ci_catalog_category

Memungkinkan penandaan yang fleksibel dan organisasi katalog

Contoh pola kueri

-- Dapatkan semua jenis perangkat untuk suatu organisasi (sistem + khusus)
SELECT cb.id, cb.code, cb.title_en, cb.is_system
FROM repo.ci_base cb
JOIN repo.ci_device_type dt ON dt.id = cb.id
WHERE cb.discriminator = 'device_type'
  AND (cb.is_system = true OR cb.organization_id = $org_id)
  AND cb.deleted_at IS NULL;

-- Dapatkan jenis aset beserta kategorinya
SELECT 
  cb.code as asset_type_code,
  cb.title_en as asset_type_name,
  cat_cb.title_en as category_name
FROM repo.ci_base cb
JOIN repo.ci_asset_type at ON at.id = cb.id
LEFT JOIN repo.ci_asset_type_category cat ON cat.id = at.category_id
LEFT JOIN repo.ci_base cat_cb ON cat_cb.id = cat.id
WHERE cb.discriminator = 'asset_type'
  AND cb.deleted_at IS NULL;

-- Dapatkan struktur tag hierarkis
SELECT cb.id, cb.code, cb.title_en, cb.path, cb.parent_id
FROM repo.ci_base cb
JOIN repo.ci_tag t ON t.id = cb.id
WHERE cb.discriminator = 'tag'
  AND cb.deleted_at IS NULL
ORDER BY cb.path;

Hubungan

FROM repo.user_role AS ur JOIN repo.acl_role_permission AS rp ON rp.role_id = ur.role_id WHERE ur.user_id = $user_id

Catatan khusus

Bekerja dengan user_role dan acl_user_scope untuk menentukan izin akhir pengguna

`audit_event`

Tujuan: Log audit terpadu untuk semua perubahan sistem

Atribut

Rincian

Kolom kunci

id, event_category, user_id, aggregate_type, aggregate_id, event_type, event_data, occurred_at

Pengindeksan

Indeks pada (user_id, occurred_at), (aggregate_type, aggregate_id, occurred_at), (event_category, occurred_at)

Catatan khusus

Dipartisi berdasarkan occurred_at (bulanan), dua kategori: auth (autentikasi) dan domain (peristiwa bisnis), menyimpan delta perubahan tingkat bidang dalam event_data JSONB

Hubungan data

Skema repo skema menerapkan pola hubungan yang canggih untuk pemodelan data yang fleksibel:

Struktur hirarkis

Organisasi menggunakan path ltree untuk kueri pohon yang efisien
Item referensi (ci_base) mendukung hierarki opsional
Pemeliharaan path otomatis melalui trigger basis data

Pola pewarisan

Pewarisan tabel: customizable_entity → entitas bisnis (organization, user, device, asset, inventory, asset_group)
Pewarisan ID: ci_base → tabel jenis referensi
Diskriminasi tipe melalui entity_type_id dan discriminator field

Hubungan polimorfik

Beberapa tabel menggunakan referensi polimorfik tanpa constraint foreign key untuk fleksibilitas maksimum:

acl_role_permission.target_entity_id → setiap customizable_entity
acl_user_scope.target_entity_id → setiap customizable_entity
entity_tag.entity_id → setiap customizable_entity

Hubungan ini divalidasi di tingkat aplikasi.

Informasi tambahan

Validasi data

Skema repo skema menegakkan integritas data melalui beberapa mekanisme:

Kendala basis data

Kendala UNIQUE dengan dukungan soft delete (indeks parsial WHERE deleted_at IS NULL)
Kendala CHECK (mis., device_relation memastikan master_id ≠ slave_id)
Kendala NOT NULL pada bidang yang wajib diisi
Nilai DEFAULT untuk cap waktu dan flag boolean

Validasi di tingkat aplikasi

Validasi jenis entitas untuk referensi polimorfik
Validasi katalog untuk referensi bidang khusus
Validasi jenis bidang khusus
Manajemen array bidang multi-nilai

Optimisasi kueri

Tabel diatur dengan strategi pengindeksan spesifik:

Indeks standar:

Semua foreign key memiliki indeks khusus
Indeks berbasis waktu pada created_at, updated_at, deleted_at
Indeks komposit untuk kolom yang sering digabungkan

Indeks khusus:

Indeks GiST pada path ltree untuk kueri hierarkis
Indeks unik parsial yang mendukung soft delete
Indeks nilai bidang khusus untuk penyaringan dan pengurutan
Indeks event audit pada waktu + entitas untuk pencarian efisien

Pertimbangan kinerja:

Direkomendasikan connection pooling (PgBouncer)
Pemeliharaan VACUUM rutin untuk tabel besar
Kemungkinan partisi masa depan untuk device tabel berdasarkan organization_id
View materialized untuk perhitungan kontrol akses yang kompleks

PreviousIkhtisar skema NextPenjelajah untuk DataHub

Last updated 12 days ago

Was this helpful?

raw_business_data struktur

Frekuensi pembaruan

description_parameters

Tabel utama menurut kategori

Ikhtisar Skema Database

raw_telematics_data struktur

Tabel utama menurut kategori

tracking_data_core

inputs

states

Informasi tambahan

Validasi data

Optimisasi kueri

repo struktur data

Frekuensi pembaruan

ci_base

Tabel utama menurut kategori

organization

user

device

asset

inventory

asset_group

custom_field_def

acl_role_permission

audit_event

Hubungan data

Informasi tambahan

Validasi data

Optimisasi kueri

`raw_business_data` struktur

`description_parameters`

`raw_telematics_data` struktur

`tracking_data_core`

`inputs`

`states`

`repo` struktur data

`ci_base`

`organization`

`user`

`device`

`asset`

`inventory`

`asset_group`

`custom_field_def`

`acl_role_permission`

`audit_event`