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 dikirim dari perangkat yang dipantau, seperti lokasi, input, output, dan peristiwa.

Setiap skema dioptimalkan untuk domain data dan pola akses spesifiknya, menyediakan 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 merepresentasikan entitas inti bisnis 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 rincian skema data bisnis mentah di bawah ini.

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]
  "calibration_data" jsonb
}

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 "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:"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:"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 inkremental saat perubahan terjadi di database MySQL sumber, biasanya kurang dari 5 menit sejak perubahan sumber.

description_parameters

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

Jenis referensi	Deskripsi	Contoh nilai
Definisi tipe	Jenis 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 kunci menurut kategori

Tabel-tabel dalam raw_business_data skema diatur ke dalam kategori fungsional untuk navigasi yang lebih mudah. Tabel di bawah merangkum tabel-tabel kunci menurut tujuan bisnisnya:

Entitas bisnis inti

users

Tujuan: Informasi akun pengguna dan profil

Atribut	Rincian
Kolom kunci	user_id, company_label, first_name, last_name, middle_name, locale, timezone_label, master_id, registration_datetime
Relasi	Pengguna induk melalui master_id, terkait dengan employees, departments, places, tasks melalui user_id
Catatan khusus	Entitas pusat yang menghubungkan data organisasi; master_id memungkinkan hierarki pengguna untuk struktur akun multi-level

employees

Tujuan: Catatan karyawan dan pengemudi dengan detail lisensi dan penugasan

Atribut	Rincian
Kolom kunci	employee_id, user_id, object_id, department_id, first_name, last_name, driver_license_number, driver_license_valid_till, hardware_key
Relasi	Tautan ke users, departments, objects (tracker yang ditugaskan), dilacak di driver_history dan checkins
Catatan khusus	Hardware key memungkinkan identifikasi pengemudi melalui iButton atau RFID; mendukung geofencing dengan latitude, longitude, radius kolom

departments

Tujuan: Unit organisasi dengan lokasi geografis

Atribut	Rincian
Kolom kunci	department_id, user_id, department_label, latitude, longitude, radius, address
Relasi	Menghubungkan karyawan ke struktur organisasi melalui department_id
Catatan khusus	Kolom lokasi mendukung analitik berbasis geofence untuk pelaporan tingkat departemen

Pelacakan dan pemantauan

devices

Tujuan: Informasi perangkat pelacakan fisik dan status jaringan

Atribut	Rincian
Kolom kunci	device_id, owner_id, device_imei, phone, status_listing_id, network_label, signal_level, has_roaming, created_at
Relasi	Entitas inti yang menghubungkan ke objects, models, sensor_description, counters; owner_id references users.user_id
Catatan khusus	Semua data telematik dalam raw_telematics_data skema mereferensikan tabel ini melalui device_id

objects

Tujuan: Entitas yang dimonitor (kendaraan, aset, personel)

Atribut	Rincian
Kolom kunci	object_id, client_id, device_id, object_label, model, group_id, create_datetime, is_deleted
Relasi	Pusat yang menghubungkan perangkat ke pengguna (client_id), detail kendaraan, riwayat pelacakan, tugas, dan aturan
Catatan khusus	Mewakili "unit yang dapat dilacak" dalam sistem; satu objek per perangkat yang aktif digunakan

models

Tujuan: Spesifikasi perangkat keras perangkat dan kapabilitasnya

Atribut	Rincian
Kolom kunci	model_id, model, vendor, alternative_label, analog_amount, digital_amount, outputs_amount, has_battery_level, has_altitude, has_phone
Konten	Flag kapabilitas Boolean menunjukkan bidang data mana yang tersedia dari tipe perangkat ini
Catatan khusus	Gunakan flag kapabilitas untuk menentukan sensor dan input yang valid saat mengambil data telematik

sensor_description

Tujuan: Konfigurasi sensor dan data kalibrasi

Atribut	Rincian
Kolom kunci	sensor_id, device_id, sensor_label, input_label, sensor_type, units_type, multiplier, divider, accuracy, calibration_data
Relasi	Mengaitkan input perangkat (dari raw_telematics_data.inputs) ke logika bisnis melalui device_id dan input_label matching
Catatan khusus	calibration_data (JSONB) menyimpan tabel kalibrasi spesifik-sensor untuk sensor level bahan bakar; multiplier dan divider mengonversi nilai mentah ke satuan

Manajemen aset

vehicles

Tujuan: Spesifikasi kendaraan, dokumentasi, dan parameter operasional

Atribut	Rincian
Kolom kunci	vehicle_id, user_id, object_id, garage_id, vehicle_label, registration_number, vin, manufacture_year, fuel_type, fuel_cost, standard_fuel_consumption, max_speed
Relasi	Tautan ke objects (tracker saat ini), garages (lokasi layanan), vehicle_service_tasks; dilacak di vehicle_trackers_history
Catatan khusus	Kolom dimensi fisik (payload_length, payload_width, payload_height, gross_weight) mendukung analitik perencanaan muatan; tanggal asuransi memungkinkan pelacakan kepatuhan

garages

Tujuan: Lokasi fasilitas layanan dan pemeliharaan

Atribut	Rincian
Kolom kunci	garage_id, user_id, latitude, longitude, radius, address, organization_label, mechanic_name, dispatcher_name
Relasi	Direferensikan oleh vehicles.garage_id untuk penetapan lokasi layanan
Catatan khusus	Kolom lokasi memungkinkan deteksi kunjungan layanan berbasis geofence dan analisis kedekatan

vehicle_service_tasks

Tujuan: Jadwal pemeliharaan dan riwayat layanan

Atribut	Rincian
Kolom kunci	service_task_id, vehicle_id, description, status, cost, start_date, end_date, completion_date, predicted_datetime, mileage_limit, engine_hours_limit
Konten	Mendukung tiga jenis pemicu: berbasis tanggal, berbasis jarak, berbasis jam mesin; pengaturan notifikasi untuk email, SMS, push
Catatan khusus	is_repeat dan kolom interval memungkinkan jadwal pemeliharaan berulang; is_unplanned membedakan pemeliharaan terjadwal vs. reaktif

Lokasi dan perutean

zones

Tujuan: Area geofence untuk pemantauan dan otomatisasi

Atribut	Rincian
Kolom kunci	zone_id, client_id, zone_label, zone_type, latitude, longitude, circle_center_latitude, circle_center_longitude, radius, address, color
Konten	Jenis zona mencakup lingkaran, poligon (didefinisikan melalui geofence_points), dan klasifikasi area khusus
Relasi	Direferensikan oleh rules2zones, users2zones; simpul poligon disimpan di geofence_points
Catatan khusus	Fungsi PostGIS dapat digunakan untuk memeriksa titik-di-dalam-poligon untuk analisis geofence yang kompleks

places

Tujuan: Titik minat dengan atribut kustom

Atribut	Rincian
Kolom kunci	place_id, user_id, place_label, latitude, longitude, radius, address, description, external_id, custom_fields
Relasi	Diperluas dengan nilai bidang kustom melalui places_text_fields, places_decimal_fields, places_bigint_fields, places_longtext_fields, places_linked_entity_fields
Catatan khusus	custom_fields JSONB menyediakan akses cepat; tabel terkait memungkinkan pemfilteran dan pengurutan berdasarkan atribut kustom

geofence_points

Tujuan: Koordinat simpul poligon untuk bentuk geofence kompleks

Atribut	Rincian
Kolom kunci	zone_id, number, latitude, longitude
Relasi	Beberapa catatan per zone_id mendefinisikan batas poligon; number kolom menentukan urutan simpul
Catatan khusus	Query dengan ORDER BY number untuk membangun kembali jalur poligon; gunakan bersama PostGIS ST_MakePolygon untuk operasi geometri

Manajemen tugas dan alur kerja

tasks

Tujuan: Penugasan work order dan pelacakan eksekusi

Atribut	Rincian
Kolom kunci	task_id, user_id, object_id, parent_task_id, task_label, status, task_type, latitude, longitude, radius, arrival_datetime, created_at, status_change_datetime, custom_fields
Konten	Mendukung tugas hirarkis melalui parent_task_id; jendela waktu didefinisikan oleh time_from/time_to; validasi geofence dengan lokasi dan radius
Relasi	Tautan ke forms (pengumpulan data), task_history (perubahan status), objects (tracker yang ditugaskan)
Catatan khusus	stay_duration dan arrival_duration_minutes memungkinkan pemantauan kepatuhan untuk tugas pengiriman dan layanan

forms

Tujuan: Formulir pengumpulan data dan pengiriman

Atribut	Rincian
Kolom kunci	form_id, task_id, object_id, form_label, kolom, values, submitted_at, submission_latitude, submission_longitude, is_submission_in_zone
Konten	kolom mendefinisikan struktur formulir (JSON); values mengandung data yang dikirim (JSON)
Relasi	Tautan ke tasks (work order terkait), objects (pengirim), direferensikan di checkins
Catatan khusus	Flag validasi lokasi is_submission_in_zone memungkinkan aturan pengiriman formulir berbasis geofence

checkins

Tujuan: Catatan kehadiran dan aktivitas berbasis lokasi

Atribut	Rincian
Kolom kunci	checkin_id, employee_id, object_id, form_id, user_id, planned_datetime, actual_datetime, latitude, longitude, radius, address, comment
Relasi	Menghubungkan karyawan ke formulir dan lokasi; melacak penyimpangan dari jadwal yang direncanakan
Catatan khusus	Varians waktu antara planned_datetime dan actual_datetime memungkinkan pelaporan ketepatan waktu; radius menentukan toleransi lokasi yang dapat diterima

task_history

Tujuan: Jejak audit untuk peristiwa siklus hidup tugas

Atribut	Rincian
Kolom kunci	task_history_id, task_id, user_id, activity, event_datetime, payload
Konten	Jenis aktivitas didefinisikan dalam description_parametrs; payload menyimpan rincian khusus-peristiwa (teks)
Catatan khusus	Esensial untuk analisis penyelesaian tugas, pelaporan transisi status, dan pelacakan aktivitas pengguna

Aturan dan otomatisasi

rules

Tujuan: Pemicu peristiwa dan konfigurasi peringatan

Atribut	Rincian
Kolom kunci	rule_id, object_id, client_id, event_type, event_label, event_group, description, parameters, alert_email, alert_sms, alert_phone, is_push_enabled, created_at, is_deleted
Konten	Parameter aturan (JSONB) mendefinisikan kondisi pemicu; mendukung notifikasi email, SMS, telepon, dan push
Relasi	Tautan ke objek melalui rules2objects, zona melalui rules2zones
Catatan khusus	event_type mendefinisikan skenario pemantauan spesifik (pelanggaran kecepatan, pelanggaran geofence, ambang sensor); maximum kolom memungkinkan agregasi peristiwa untuk pemberitahuan berbasis ambang

rules2objects

Tujuan: Asosiasi aturan-ke-objek dengan parameter spesifik-instansi

Atribut	Rincian
Kolom kunci	rule_id, object_id, param_group_number, object_params
Konten	object_params (JSONB) memungkinkan kustomisasi aturan per-objek (mis. batas kecepatan berbeda per kendaraan)
Catatan khusus	Relasi banyak-ke-banyak memungkinkan satu aturan memantau beberapa objek dengan parameter berbeda

rules2zones

Tujuan: Mengaitkan aturan dengan pemicu geofence

Atribut	Rincian
Kolom kunci	rule_id, zone_id
Catatan khusus	Relasi banyak-ke-banyak memungkinkan pemantauan multi-zona untuk satu aturan (mis. peringatan saat memasuki salah satu dari beberapa area terbatas)

Status dan kategorisasi

statuses

Tujuan: Definisi status kustom untuk daftar status

Atribut	Rincian
Kolom kunci	status_id, listing_id, status_label, color, order_sort, is_deleted
Relasi	Grup status diorganisir oleh listing_id (mereferensikan status_listings); digunakan dalam status_history
Catatan khusus	order_sort mendefinisikan urutan tampilan; warna memungkinkan pembedaan visual dalam pelaporan

status_listings

Tujuan: Definisi set status untuk perangkat atau karyawan

Atribut	Rincian
Kolom kunci	status_listing_id, user_id, status_listing_label, is_supervisor_controlled, is_employee_controlled, is_deleted
Relasi	Direferensikan oleh devices.status_listing_id dan statuses.listing_id
Catatan khusus	Flag kontrol menentukan siapa yang dapat mengubah status: supervisor-saja, layanan mandiri karyawan, atau keduanya

status_history

Tujuan: Jejak audit untuk perubahan status

Atribut	Rincian
Kolom kunci	status_history_id, device_id, old_status_id, new_status_id, updated_by, changed_datetime, server_datetime, latitude, longitude, address
Relasi	Tautan ke devices, statuses (lama dan baru), description_parametrs (untuk updated_by peran)
Catatan khusus	Pengambilan lokasi memungkinkan analisis geografis dari transisi status; berguna untuk pelaporan lokasi mulai/akhir hari kerja

tags

Tujuan: Label kategorisasi yang didefinisikan pengguna

Atribut	Rincian
Kolom kunci	tag_id, user_id, tag_label, color
Relasi	Diterapkan ke entitas melalui tag_links; ruang lingkup didefinisikan oleh pengguna
Catatan khusus	Sistem kategorisasi fleksibel yang mendukung banyak tag per entitas

tag_links

Purpose: Mengaitkan tag dengan entitas yang ditandai

Atribut	Rincian
Kolom kunci	tag_id, entity_type, entity_id, ordinal
Konten	entity_type mengidentifikasi tabel (kendaraan, karyawan, tugas, dll.); ordinal menentukan urutan tampilan
Catatan khusus	Relasi polymorphic memungkinkan penandaan di berbagai jenis entitas

Grup dan hirarki

groups

Tujuan: Pengelompokan organisasi untuk objek (tracker)

Atribut	Rincian
Kolom kunci	group_id, client_id, group_label, group_color
Relasi	Direferensikan oleh objects.group_id; kepemilikan klien melalui client_id (mereferensikan users)
Catatan khusus	Memungkinkan organisasi seperti folder dari entitas pemantauan untuk pelaporan dan izin

groups_objects

Tujuan: Relasi banyak-ke-banyak antara grup dan objek

Atribut	Rincian
Kolom kunci	Kunci primer komposit: groups_client_id, objects_client_id
Catatan khusus	Memungkinkan objek menjadi bagian dari beberapa grup secara bersamaan; query dengan kedua client_id nilai untuk keanggotaan grup

Kolom kustom dan entitas

entities

Tujuan: Definisi jenis entitas untuk kerangka bidang kustom

Atribut	Rincian
Kolom kunci	entity_id, user_id, entity_label, builtin_type
Relasi	Direferensikan oleh custom_fields untuk menentukan bidang kustom mana yang berlaku untuk jenis entitas mana
Catatan khusus	builtin_type terkait ke description_parametrs untuk klasifikasi entitas yang ditentukan sistem

custom_fields

Tujuan: Definisi bidang kustom

Atribut	Rincian
Kolom kunci	custom_field_id, entity_id, custom_field_label, custom_field_type, description, is_required, parameters
Konten	parameters (JSONB) menyimpan konfigurasi spesifik-jenis-bidang (aturan validasi, opsi dropdown, dll.)
Relasi	Mendefinisikan atribut kustom yang tersedia untuk entitas; tipe bidang terkait ke description_parametrs
Catatan khusus	Memungkinkan perluasan skema dinamis tanpa perubahan basis data; digunakan secara luas di places dan tasks

Pelacakan historis

driver_history

Tujuan: Log perubahan penugasan karyawan-ke-objek

Atribut	Rincian
Kolom kunci	driver_history_id, object_id, old_employee_id, new_employee_id, hardware_key, changed_datetime, server_datetime, updated_by, latitude, longitude, address
Relasi	Melacak penugasan pengemudi ke kendaraan dari waktu ke waktu; terhubung ke employees dan objects
Catatan khusus	Esensial untuk pelaporan spesifik-pengemudi saat pengemudi berpindah kendaraan; pengambilan lokasi memungkinkan analisis lokasi perubahan penugasan

vehicle_trackers_history

Tujuan: Log perubahan penugasan kendaraan-ke-tracker

Atribut	Rincian
Kolom kunci	vehicle_tracker_history_id, vehicle_id, object_id, changed_datetime
Relasi	Melacak perangkat GPS mana yang dipasang di kendaraan mana dari waktu ke waktu
Catatan khusus	Kritis untuk analisis data historis saat tracker dipindahkan antar kendaraan; memungkinkan atribusi jarak dan penggunaan yang akurat

Data referensi dan pencarian

description_parametrs

Tujuan: Data referensi sistem dan nilai enumerasi

Atribut	Rincian
Kolom kunci	key, type, description
Konten	Menyediakan label yang dapat dibaca manusia untuk nilai bernomor di seluruh basis data (status tugas, jenis bahan bakar, jenis counter, dll.)
Relasi	Direferensikan melalui kunci asing dari beberapa tabel untuk kategorisasi yang terstandarisasi
Catatan khusus	Esensial untuk menerjemahkan kode integer menjadi nilai yang dapat dibaca dalam pelaporan; type mengelompokkan enumerasi terkait

counters

Tujuan: Konfigurasi pelacakan odometer dan jam mesin

Atribut	Rincian
Kolom kunci	counter_id, device_id, counter_type, sensor_id, multiplier
Relasi	Mengaitkan perangkat ke pembacaan sensor yang merepresentasikan counter jarak atau waktu
Catatan khusus	multiplier mengonversi pulsa sensor ke satuan aktual (km, mil, jam); counter_type dari description_parametrs menentukan jenis pengukuran

device_output_name

Tujuan: Label kustom untuk saluran keluaran perangkat

Atribut	Rincian
Kolom kunci	device_id, number, label
Konten	Memetakan nomor saluran keluaran ke nama yang ditetapkan pengguna (mis. "Door Lock", "Engine Block")
Catatan khusus	Mengaktifkan pelaporan yang dapat dibaca saat menganalisis perintah dan status keluaran perangkat

raw_telematics_data struktur

The raw_telematics_data skema berisi tiga jenis tabel utama yang bekerja sama untuk menyediakan data perangkat yang komprehensif.

ERD data telematik mentah lapisan Bronze

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

Temukan detail skema data telematik mentah di bawah.

skema raw_telematics_data

Table 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)}

}

  

Table 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)}

}

  

Table 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 kunci menurut kategori

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

tracking_data_core

Tujuan: Data inti lokasi dan gerak

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 (latitude dan longitude) 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 (engine RPM)
Relasi	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
Field Kunci	state_id, device_id, device_time, state_name, value
Konten	Indikator mode operasi (working, idle, off), status komponen (ignition, doors)
Format Nilai	Nilai boolean (1/0) atau kode status spesifik

Data dalam skema ini diambil langsung dari perangkat, dengan latensi minimal (biasanya 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:

• CHECK constraints memvalidasi bahwa nilai berada dalam rentang yang dapat diterima

• Foreign keys memastikan hubungan antar tabel tetap konsisten

• NOT NULL constraints menjamin bahwa field yang diwajibkan selalu memiliki nilai

• DEFAULT values memberikan nilai cadangan ketika data tidak disediakan secara eksplisit

Optimisasi query

Tabel diatur dengan strategi pengindeksan spesifik:

• Semua tabel menyertakan indeks berbasis waktu pada record_added_at

• Kolom foreign key memiliki indeks khusus untuk kinerja join

• Kombinasi kolom yang sering digunakan memiliki indeks komposit

• TimescaleDB menyediakan indeks khusus untuk query deret waktu

repo struktur data

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

The 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 field kustom untuk jenis entitas apa pun, kontrol akses berbasis peran dengan pembatasan tingkat objek, dan jejak audit lengkap dengan pelacakan perubahan tingkat field. Semua entitas dapat diperluas tanpa modifikasi skema, dilokalkan untuk penyebaran internasional, dan dihubungkan melalui hubungan polimorfik yang fleksibel.

Skema ini menangani skenario manajemen data kompleks termasuk hirarki aset armada di seluruh tingkat organisasi, platform SaaS multi-tenant yang memerlukan isolasi data, operasi yang didorong kepatuhan dengan persyaratan audit terperinci, dan sistem yang membutuhkan model data dinamis yang dapat disesuaikan melalui field kustom daripada migrasi database.

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

Temukan repo detail skema di bawah.

repo skema data

// ============================================
// New DataHub Schema - Customer Journey
// PostgreSQL 14+ with ltree extension
// Version: 2.0 (Concept)
// ============================================

// ============================================
// BASE REFERENCE TABLES (ci_base hierarchy)
// ============================================

Table 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']
  }
}

Table ci_module {
  id uuid [primary key]
}

Table ci_catalog_category {
  id uuid [primary key]
}

Table ci_country {
  id uuid [primary key]
}

Table ci_role {
  id uuid [primary key]
}

Table ci_entity_type {
  id uuid [primary key]
}

Table ci_device_status {
  id uuid [primary key]
}

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

Table ci_device_type {
  id uuid [primary key]
}

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

Table ci_asset_type_category {
  id uuid [primary key]
}

Table ci_inventory_type {
  id uuid [primary key]
}

Table ci_organization_type {
  id uuid [primary key]
}

Table ci_user_type {
  id uuid [primary key]
}

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

Table ci_device_relation_type {
  id uuid [primary key]
}

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

// ============================================
// BASE ENTITY WITH CUSTOM FIELDS SUPPORT
// ============================================

Table 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']
  }
}

// ============================================
// CORE BUSINESS ENTITIES
// ============================================

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

// ============================================
// ACCESS CONTROL (ACL)
// ============================================

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

// ============================================
// BUSINESS ENTITIES
// ============================================

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

// ============================================
// LOCALIZATION
// ============================================

Table 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']
  }
}

// ============================================
// CUSTOM FIELDS - DEFINITIONS
// ============================================

Table 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']
  }
}

// ============================================
// CUSTOM FIELDS - VALUES (by type)
// ============================================

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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']
  }
}

Table 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
// ============================================

Table 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']
  }
}

// ============================================
// RELATIONSHIPS
// ============================================

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 waktu nyata dengan sistem sumber. Pembaruan terjadi segera saat perubahan berlangsung, dengan jejak audit yang merekam semua modifikasi untuk kepatuhan dan analisis historis.

ci_base

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

The 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 didefinisikan pengguna ke dalam satu struktur terpadu, memberikan konsistensi dan fleksibilitas di seluruh skema.

Arsitektur:

The ci_base tabel berfungsi sebagai fondasi untuk semua data referensi, menggunakan discriminator field untuk mengidentifikasi tipe referensi spesifik. Setiap tipe referensi memiliki tabel yang sesuai (seperti ci_device_type, ci_asset_type) yang berbagi id sebagai ci_base, menciptakan hubungan pewarisan yang aman tipe.

Bagaimana entitas bisnis terhubung ke ci_base:

Semua entitas bisnis dalam repo skema merujuk ci_base subtipe untuk mendefinisikan 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 tipe referensi:

Kategori	Tabel	Tujuan
Konfigurasi sistem	ci_module, ci_country, ci_role	Mendefinisikan modul sistem, referensi geografis, dan peran pengguna
Definisi tipe 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 tipe
Status dan klasifikasi	ci_device_status, ci_asset_type_category	Melacak status entitas dan mengelompokkan tipe ke dalam kategori
Kontrol akses	ci_permission_scope	Mendefinisikan izin apa yang dapat diberikan (terkait dengan ci_module dan ci_entity_type)
Relasi	ci_device_relation_type	Mendefinisikan tipe hubungan antar perangkat (master-slave, backup, dll.)
Kategorisasi	ci_tag, ci_catalog_category	Memungkinkan penandaan yang fleksibel dan organisasi katalog

Contoh pola kueri

-- Dapatkan semua tipe perangkat untuk sebuah organisasi (sistem + kustom)
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 tipe 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 hirarkis
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;

Tabel kunci menurut kategori

Tabel-tabel dalam repo skema diatur ke dalam kategori fungsional. Deskripsi berikut merangkum tabel terpenting berdasarkan tujuan bisnisnya.

organization

Tujuan: Manajemen organisasi hirarkis

Atribut	Rincian
Kolom kunci	id, parent_id, path, organization_type_id, title_en, is_active, deleted_at
Pengindeksan	Indeks GiST pada path untuk kueri hirarkis, indeks pada parent_id dan organization_type_id
Catatan khusus	Menggunakan ltree untuk hierarki multi-level, mewarisi dari customizable_entity untuk dukungan bidang kustom

user

Tujuan: Akun pengguna dan autentikasi

Atribut	Rincian
Kolom kunci	id, organization_id, user_type_id, identity_provider, identity_provider_id, full_name, is_active
Pengindeksan	Indeks unik pada (organization_id, identity_provider, identity_provider_id)
Catatan khusus	Integrasi penyedia identitas eksternal (Keycloak, Auth0, Okta), mewarisi dari customizable_entity

device

Tujuan: Perangkat pelacakan fisik

Atribut	Rincian
Kolom kunci	id, organization_id, device_type_id, status_id, hw_id, label
Pengindeksan	Indeks pada organization_id, device_type_id, status_id, hw_id
Catatan khusus	Identifier perangkat keras untuk pelacakan perangkat, mewarisi dari customizable_entity untuk bidang kustom

asset

Tujuan: Aset fisik atau virtual

Atribut	Rincian
Kolom kunci	id, organization_id, asset_type_id, label, description
Pengindeksan	Indeks pada organization_id dan asset_type_id
Catatan khusus	Mewarisi dari customizable_entity, terhubung ke perangkat melalui device_asset_link

inventory

Tujuan: Catatan inventaris dan gudang

Atribut	Rincian
Kolom kunci	id, organization_id, inventory_type_id, code
Pengindeksan	Indeks unik pada (organization_id, code)
Catatan khusus	Kode unik dalam organisasi, terhubung ke perangkat melalui device_inventory_link

asset_group

Tujuan: Pengelompokan aset dengan pelacakan historis

Atribut	Rincian
Kolom kunci	id, organization_id, group_type_id, title_en, description
Relasi	FROM repo.asset_group AS ag JOIN repo.asset_group_item AS agi ON agi.group_id = ag.id JOIN repo.asset AS a ON a.id = agi.asset_id WHERE agi.detached_at IS NULL
Catatan khusus	Keanggotaan berbasis waktu melalui asset_group_item, kueri anggota saat ini dengan WHERE detached_at IS NULL

custom_field_def

Tujuan: Definisi bidang kustom dan metadata

Atribut	Rincian
Kolom kunci	id, organization_id, owner_entity_type_id, code, field_type, is_multi, is_required
Konten	Tipe bidang mencakup text, number, boolean, date, datetime, entity_ref, catalog_item_ref
Catatan khusus	Memungkinkan bidang kustom yang fleksibel untuk jenis entitas apa pun, nilai disimpan di custom_field_value_* tabel

acl_role_permission

Tujuan: Manajemen izin berbasis peran

Atribut	Rincian
Kolom kunci	id, role_id, permission_scope_id, target_entity_id, actions
Konten	Bitmask aksi (READ=1, UPDATE=2, DELETE=4, CREATE=8), izin spesifik target atau luas untuk tipe entitas
Relasi	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

The repo skema mengimplementasikan 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 hirarki opsional

• Pemeliharaan path otomatis melalui trigger database

Pola pewarisan

• Pewarisan tabel: customizable_entity → entitas bisnis (organization, user, device, asset, inventory, asset_group)

• Pewarisan ID: ci_base → tabel tipe referensi

• Diskriminasi tipe melalui entity_type_id dan discriminator kolom

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 pada tingkat aplikasi.

Informasi tambahan

Validasi data

The repo skema menegakkan integritas data melalui beberapa mekanisme:

Keterbatasan database

• KONSTRAINT UNIQUE dengan dukungan soft delete (indeks parsial WHERE deleted_at IS NULL)

• KONSTRAINT CHECK (mis., device_relation memastikan master_id ≠ slave_id)

• KONSTRAINT NOT NULL pada bidang yang wajib

• Nilai DEFAULT untuk timestamp dan flag boolean

Validasi pada tingkat aplikasi

• Validasi tipe entitas untuk referensi polimorfik

• Validasi katalog untuk referensi bidang kustom

• Validasi tipe bidang kustom

• Manajemen array bidang multi-nilai

Optimisasi query

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 dijoin

Indeks khusus:

• Indeks GiST pada path ltree untuk kueri hirarkis

• Indeks unik parsial yang mendukung soft delete

• Indeks nilai bidang kustom untuk penyaringan dan pengurutan

• Indeks audit event pada waktu + entitas untuk pencarian yang efisien

Pertimbangan performa:

• Disarankan penggunaan connection pooling (PgBouncer)

• Perawatan VACUUM rutin untuk tabel besar

• Kemungkinan partisi di masa depan untuk device tabel oleh organization_id

• View materialized untuk perhitungan kontrol akses yang kompleks