Capa Bronze

La capa Bronze contiene dos esquemas de datos distintos, cada uno que sirve a diferentes aspectos de la plataforma de telemática e inteligencia empresarial:

raw_business_data - que contiene tablas, atributos y valores relacionados con la información empresarial, como vehículos, empleados, geocercas añadidas por los usuarios, etc.
raw_telematics_data - que contiene tablas, atributos y valores relacionados con los datos telemáticos transmitidos desde dispositivos en monitoreo, como ubicaciones, entradas, salidas y eventos.

Cada esquema está optimizado para su dominio de datos específico y patrones de acceso, proporcionando una cobertura integral de las necesidades operativas, telemáticas y de gestión de activos.

`raw_business_data` estructura

Este esquema contiene más de 40 tablas cuidadosamente seleccionadas para cubrir varios aspectos empresariales y casos de uso. Estas tablas representan sus entidades comerciales principales, la estructura organizativa y los datos operativos.

El diagrama interactivo del esquema raw_business_data está disponible en dbdiagram.io: https://dbdiagram.io/d/V3-bronze-layer-68ecfd1c2e68d21b4131089a

Consulte los detalles del esquema de datos empresariales sin procesar a continuación.

esquema raw_business_data


Tabla "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

}

  

Tabla "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)"

}

  

Tabla "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

}

  

Tabla "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]

}

  

Tabla "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

}

  

Tabla "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]

}

  

Tabla "places_linked_entity_fields" {

  "value" bigint [not null]

  "record_added_at" timestamp [not null]

  "place_id" integer [not null]

  "field_id" integer [not null]

}

  

Tabla "places_text_fields" {

  "place_id" integer [not null]

  "record_added_at" timestamp [not null]

  "value" text [not null]

  "field_id" integer [not null]

}

  

Tabla "users2zones" {

  "zone_id" integer [not null]

  "record_added_at" timestamp [not null]

  "user_id" integer [not null]

}

  

Tabla "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]

}

  

Tabla "device_output_name" {

  "device_id" integer [not null]

  "record_added_at" timestamp [not null]

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

  "number" integer [not null]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "places_decimal_fields" {

  "field_id" integer [not null]

  "record_added_at" timestamp [not null]

  "place_id" integer [not null]

  "value" numeric [not null]

}

  

Tabla "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

}

  

Tabla "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]

}

  

Tabla "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

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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)"

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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

}

  

Tabla "rules2zones" {

  "zone_id" integer [not null]

  "record_added_at" timestamp [not null]

  "rule_id" integer [not null]

}

  

Tabla "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

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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

}

  

Tabla "places_bigint_fields" {

  "field_id" integer [not null]

  "value" bigint [not null]

  "place_id" integer [not null]

  "record_added_at" timestamp [not null]

}

  

Tabla "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]

}

  

Tabla "description_parametrs" {

  "description" "character varying(150)"

  "record_added_at" timestamp [not null]

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

  "key" integer [not null]

}

  

Tabla "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]

}

  

Tabla "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]

}

  

Tabla "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

}

  

Tabla "places_longtext_fields" {

  "field_id" integer [not null]

  "value" text [not null]

  "record_added_at" timestamp [not null]

  "place_id" integer [not null]

}

  

Tabla "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

}

  

Tabla "groups_objects" {

  "groups_client_id" integer

  "objects_client_id" integer

  

  Índices {

    (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"

Frecuencia de actualización

Los datos en este esquema se sincronizan con la base de datos principal. Las actualizaciones ocurren de forma incremental a medida que se producen cambios en la base de datos MySQL de origen, típicamente en menos de 5 minutos desde el cambio en la fuente.

`description_parameters`

El sistema incluye datos de referencia para estandarizar valores en toda la base de datos:

Tipo de referencia

Descripción

Valores de ejemplo

Definiciones de tipo

Tipos de entidad estándar

vehicle_type: car, truck, bus

Códigos de estado

Valores de estado de tareas y del sistema

tasks_status: unassigned, assigned, done

Definiciones de unidades

Unidades de medida para sensores

units_type: liter, gallon, celsius

Clasificaciones de entidad

Categorías de entidades comerciales

entities_type: place, task, customer

Tablas clave por categoría

Las tablas en el raw_business_data esquema están organizadas en categorías funcionales para facilitar la navegación. La tabla a continuación resume las tablas clave según su propósito empresarial:

Descripción general del esquema de base de datos

Categoría

Nombre de la tabla

Descripción

Estructura organizativa

users
departments
employees
groups

Cuentas de usuario con información de perfil
Departamentos con datos de geolocalización
Detalles de empleados y conductores
Grupos de organización de rastreadores

Objetos y dispositivos

devices
models
objects
vehicles
sensor_description

Dispositivos de seguimiento físicos
Especificaciones de modelos de dispositivos
Objetos monitorizados
Detalles y especificaciones de vehículos
Detalles de configuración de sensores

Lugares y zonas

places
zones
garages
tags

Puntos de interés con geolocalización
Áreas de monitoreo geovalladas
Ubicaciones de servicio de vehículos
Etiquetas organizativas

Datos operativos

tasks
forms
checkins
events
statuses
vehicle_service_tasks

Asignaciones de tareas y seguimiento
Formularios de recopilación de datos
Registros de asistencia basados en la ubicación
Eventos y notificaciones del sistema
Definiciones de estado
Registros de mantenimiento de vehículos

`raw_telematics_data` estructura

El raw_telematics_data esquema contiene tres tipos de tablas principales que trabajan conjuntamente para proporcionar datos completos del dispositivo.

El diagrama interactivo del esquema raw_telematics_data está disponible en dbdiagram.io: https://dbdiagram.io/d/v1-schema-telematics-bd-67a0acef263d6cf9a0d8e750

Encuentre los detalles del esquema de datos telemáticos sin procesar a continuación.

esquema raw_telematics_data

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

}

  

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

}

  

Tabla 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)

Tablas clave por categoría

Cada tabla tiene un propósito específico para capturar diferentes aspectos de la información del dispositivo:

`tracking_data_core`

Propósito: Datos principales de ubicación y movimiento

Atributo

Detalles

Campos clave

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

Indexación

Optimizado con índice en (device_id, device_time)

Notas especiales

Los datos de ubicación (latitud y longitud) usan formato entero con precisión de 10⁷ para un rendimiento óptimo en TimescaleDB La velocidad también se almacena como entero, por lo que debe dividirse entre 100

`inputs`

Propósito: Lecturas de sensores desde dispositivos

Atributo

Detalles

Campos clave

input_id, device_id, device_time, sensor_name, value

Contenido

Lecturas analógicas (nivel de combustible, temperatura, voltaje), valores calculados (RPM del motor)

Relaciones

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`

Propósito: Indicadores de estado del dispositivo y modos operativos

Atributo

Detalles

Campos clave

state_id, device_id, device_time, state_name, value

Contenido

Indicadores de modo operativo (trabajando, inactivo, apagado), estados de componentes (encendido, puertas)

Formato de valor

Valores booleanos (1/0) o códigos de estado específicos

Los datos en este esquema se ingieren directamente desde los dispositivos, con latencia mínima (típicamente segundos). El esquema está optimizado para datos de series temporales usando TimescaleDB para almacenamiento y recuperación eficientes.

Información adicional

Validación de datos

La base de datos aplica integridad de datos mediante múltiples mecanismos:

Restricciones CHECK validar que los valores estén dentro de rangos aceptables
Claves externas asegurar que las relaciones entre tablas permanezcan consistentes
Restricciones NOT NULL garantizar que los campos obligatorios siempre tengan valores
Valores DEFAULT proporcionar valores por defecto cuando los datos no se suministran explícitamente

Optimización de consultas

Las tablas se organizan con estrategias de indexación específicas:

Todas las tablas incluyen índices basados en el tiempo en record_added_at
Las columnas de claves externas tienen índices dedicados para el rendimiento de joins
Las combinaciones de columnas de uso frecuente tienen índices compuestos
TimescaleDB proporciona índices especializados para consultas de series temporales

`repo` estructura de datos

Este esquema está actualmente en desarrollo. Si está interesado en acceso anticipado o tiene preguntas sobre esta funcionalidad, por favor contacte [email protected].

El repo El esquema proporciona un marco integral para gestionar estructuras organizativas, activos, dispositivos y sus relaciones en entornos multiinquilino. Construido sobre PostgreSQL 14+ con la extensión ltree, el esquema admite organizaciones jerárquicas, definiciones de campos personalizados para cualquier tipo de entidad, control de acceso basado en roles con restricciones a nivel de objeto y registros de auditoría completos con seguimiento de cambios a nivel de campo. Todas las entidades pueden extenderse sin modificaciones del esquema, localizarse para despliegues internacionales y vincularse mediante relaciones polimórficas flexibles.

El esquema aborda escenarios complejos de gestión de datos, incluyendo jerarquías de activos de flotas a través de niveles organizativos, plataformas SaaS multiinquilino que requieren aislamiento de datos, operaciones sujetas a cumplimiento con requisitos detallados de auditoría y sistemas que necesitan modelos de datos dinámicos adaptables mediante campos personalizados en lugar de migraciones de base de datos.

El diagrama interactivo derepo el esquema de datos está disponible en dbdiagram.io: https://dbdiagram.io/d/Navixy-Repo-data-schema-68ad788c1e7a611967a0930e

Encuentre el repo detalles del esquema a continuación.

repo esquema de datos

// ============================================
// Nuevo esquema DataHub - Customer Journey
// PostgreSQL 14+ con la extensión ltree
// Versión: 2.0 (Concepto)
// ============================================

// ============================================
// TABLAS DE REFERENCIA BASE (jerarquía ci_base)
// ============================================

Tabla 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
  
  índices {
    (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']
  }
}

Tabla ci_module {
  id uuid [primary key]
}

Tabla ci_catalog_category {
  id uuid [primary key]
}

Tabla ci_country {
  id uuid [primary key]
}

Tabla ci_role {
  id uuid [primary key]
}

Tabla ci_entity_type {
  id uuid [primary key]
}

Tabla ci_device_status {
  id uuid [primary key]
}

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

Tabla ci_device_type {
  id uuid [primary key]
}

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

Tabla ci_asset_type_category {
  id uuid [primary key]
}

Tabla ci_inventory_type {
  id uuid [primary key]
}

Tabla ci_organization_type {
  id uuid [primary key]
}

Tabla ci_user_type {
  id uuid [primary key]
}

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

Tabla ci_device_relation_type {
  id uuid [primary key]
}

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

// ============================================
// ENTIDAD BASE CON SOPORTE DE CAMPOS PERSONALIZADOS
// ============================================

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

// ============================================
// ENTIDADES CORE DE NEGOCIO
// ============================================

Tabla 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
  
  índices {
    (parent_id) [name: 'idx_org_parent']
    (path) [type: gist, name: 'idx_org_path_gist']
    (organization_type_id) [name: 'idx_org_type']
  }
}

Tabla 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`]
  
  índices {
    (organization_id) [name: 'idx_catalog_org']
    (module_id) [name: 'idx_catalog_module']
  }
}

Tabla 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
  
  índices {
    (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']
  }
}

// ============================================
// CONTROL DE ACCESO (ACL)
// ============================================

Tabla 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
  
  índices {
    (user_id) [name: 'idx_user_role_user']
    (role_id) [name: 'idx_user_role_role']
    (user_id, role_id) [unique, name: 'uq_user_role']
  }
}

Tabla 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
  
  índices {
    (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']
  }
}

Tabla 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]
  
  índices {
    (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']
  }
}

// ============================================
// ENTIDADES DE NEGOCIO
// ============================================

Tabla 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
  
  índices {
    (organization_id) [name: 'idx_asset_org']
    (asset_type_id) [name: 'idx_asset_type']
  }
}

Tabla 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
  
  índices {
    (organization_id) [name: 'idx_inventory_org']
    (inventory_type_id) [name: 'idx_inventory_type']
    (organization_id, code) [unique, name: 'uq_inventory_org_code']
  }
}

Tabla 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
  
  índices {
    (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']
  }
}

Tabla 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
  
  índices {
    (device_id) [unique, name: 'idx_device_asset_link_device']
    (asset_id) [name: 'idx_device_asset_link_asset']
  }
}

Tabla 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
  
  índices {
    (device_id) [unique, name: 'idx_device_inventory_link_device']
    (inventory_id) [name: 'idx_device_inventory_link_inventory']
  }
}

Tabla device_relation {
  id uuid [primary key]
  master_id uuid [not null]
  slave_id uuid [not null]
  relation_type_id uuid [not null]
  
  índices {
    (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']
  }
}

Tabla 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
  
  índices {
    (organization_id) [name: 'idx_asset_group_org']
    (group_type_id) [name: 'idx_asset_group_type']
  }
}

Tabla 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
  
  índices {
    (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']
  }
}

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

// ============================================
// LOCALIZACIÓN
// ============================================

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

// ============================================
// CAMPOS PERSONALIZADOS - DEFINICIONES
// ============================================

Tabla 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`]
  
  índices {
    (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']
  }
}

// ============================================
// CAMPOS PERSONALIZADOS - VALORES (por tipo)
// ============================================

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

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

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

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

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

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

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

// ============================================
// AUDITORÍA
// ============================================

Tabla 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`]
  
  índices {
    (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']
  }
}

// ============================================
// RELACIONES
// ============================================

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

Frecuencia de actualización

Los datos en el repo esquema se sincronizan en tiempo real con los sistemas de origen. Las actualizaciones ocurren inmediatamente a medida que suceden los cambios, con registros de auditoría que capturan todas las modificaciones para cumplimiento y análisis histórico.

`ci_base`

El repo el esquema utiliza un patrón de Herencia de Tabla Única para todos los datos de referencia a través de la ci_base tabla:

El repo el esquema utiliza un Herencia de Tabla Única patrón para todos los datos de referencia a través de la ci_base tabla. Este diseño consolida diccionarios del sistema, clasificaciones y elementos de referencia definidos por el usuario en una estructura unificada, proporcionando consistencia y flexibilidad en todo el esquema.

Arquitectura:

El ci_base la tabla sirve como la base para todos los datos de referencia, utilizando un discriminador campo para identificar el tipo de referencia específico. Cada tipo de referencia tiene una tabla correspondiente (como ci_device_type, ci_asset_type) que comparte el mismo id que ci_base, creando una relación de herencia segura por tipo.

Cómo las entidades de negocio se conectan a ci_base:

Todas las entidades de negocio en el repo esquema hacen referencia a ci_base subtipos para definir su clasificación y comportamiento:

organization → referencia ci_organization_type (que hereda de ci_entity_type → ci_base)
user → referencia ci_user_type (que hereda de ci_entity_type → ci_base)
device → referencia ci_device_type y ci_device_status (ambos heredan de ci_base)
asset → referencia ci_asset_type (que hereda de ci_entity_type → ci_base)
inventory → referencia ci_inventory_type (que hereda de ci_entity_type → ci_base)
asset_group → referencia ci_asset_group_type (que hereda de ci_entity_type → ci_base)

Categorías de tipos de referencia:

Categoría

Tablas

Propósito

Configuración del sistema

ci_module, ci_country, ci_role

Definen módulos del sistema, referencias geográficas y roles de usuario

Definiciones de tipos de entidad

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

Clasifican todas las entidades de negocio por tipo

Estado y clasificación

ci_device_status, ci_asset_type_category

Agrupan estados de entidad y tipos de grupo en categorías

Control de acceso

ci_permission_scope

Definen qué permisos se pueden otorgar (conectado a ci_module y ci_entity_type)

Relaciones

ci_device_relation_type

Definen tipos de relaciones entre dispositivos (maestro-esclavo, respaldo, etc.)

Categorización

ci_tag, ci_catalog_category

Permiten etiquetado flexible y organización de catálogos

Patrones de consultas de ejemplo

-- Obtener todos los tipos de dispositivo para una organización (sistema + personalizados)
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;

-- Obtener tipo de activo con su categoría
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;

-- Obtener estructura jerárquica de etiquetas
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;

Relaciones

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

Notas especiales

Funciona con user_role y acl_user_scope para determinar los permisos finales del usuario

`audit_event`

Propósito: Registro de auditoría unificado para todos los cambios del sistema

Atributo

Detalles

Campos clave

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

Indexación

Índices en (user_id, occurred_at), (aggregate_type, aggregate_id, occurred_at), (event_category, occurred_at)

Notas especiales

Particionado por occurred_at (mensual), dos categorías: auth (autenticación) y domain (eventos de negocio), almacena deltas de cambio a nivel de campo en event_data JSONB

Relaciones de datos

El repo el esquema implementa patrones sofisticados de relaciones para modelado de datos flexible:

Estructuras jerárquicas

Las organizaciones usan rutas ltree para consultas de árbol eficientes
Los elementos de referencia (ci_base) admiten jerarquías opcionales
Mantenimiento automático de rutas mediante triggers de base de datos

Patrones de herencia

Herencia de tablas: customizable_entity → entidades de negocio (organization, user, device, asset, inventory, asset_group)
Herencia de ID: ci_base → tablas de tipo de referencia
Discriminación de tipo via entity_type_id y discriminador campos

Relaciones polimórficas

Ciertas tablas usan referencias polimórficas sin restricciones de clave externa para máxima flexibilidad:

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

Estas relaciones se validan a nivel de la aplicación.

Información adicional

Validación de datos

El repo el esquema aplica integridad de datos mediante múltiples mecanismos:

Restricciones de la base de datos

Restricciones UNIQUE con soporte de borrado suave (índices parciales WHERE deleted_at IS NULL)
Restricciones CHECK (por ejemplo, device_relation asegura master_id ≠ slave_id)
Restricciones NOT NULL en campos requeridos
Valores DEFAULT para marcas de tiempo y banderas booleanas

Validación a nivel de la aplicación

Validación de tipo de entidad para referencias polimórficas
Validación de catálogo para referencias de campos personalizados
Validación de tipo de campo personalizado
Gestión de arrays de campos multi-valor

Optimización de consultas

Las tablas se organizan con estrategias de indexación específicas:

Índices estándar:

Todas las claves foráneas tienen índices dedicados
Índices basados en tiempo en created_at, updated_at, deleted_at
Índices compuestos para columnas frecuentemente unidas

Índices especializados:

Índices GiST en rutas ltree para consultas jerárquicas
Índices únicos parciales que soportan borrado suave
Índices de valores de campos personalizados para filtrado y ordenación
Índices de eventos de auditoría por tiempo + entidad para búsquedas eficientes

Consideraciones de rendimiento:

Se recomienda agrupación de conexiones (PgBouncer)
Mantenimiento regular VACUUM para tablas grandes
Posible particionado futuro para device tabla por organization_id
Vistas materializadas para cálculos complejos de control de acceso

AnteriorDescripción general del esquema SiguienteExplorador para DataHub

Última actualización hace 10 días

¿Te fue útil?

raw_business_data estructura

Frecuencia de actualización

description_parameters

Tablas clave por categoría

Descripción general del esquema de base de datos

raw_telematics_data estructura

Tablas clave por categoría

tracking_data_core

inputs

states

Información adicional

Validación de datos

Optimización de consultas

repo estructura de datos

Frecuencia de actualización

ci_base

Tablas clave por categoría

organization

user

device

asset

inventory

asset_group

custom_field_def

acl_role_permission

audit_event

Relaciones de datos

Información adicional

Validación de datos

Optimización de consultas

`raw_business_data` estructura

`description_parameters`

`raw_telematics_data` estructura

`tracking_data_core`

`inputs`

`states`

`repo` estructura de datos

`ci_base`

`organization`

`user`

`device`

`asset`

`inventory`

`asset_group`

`custom_field_def`

`acl_role_permission`

`audit_event`