Bronze layer

The Bronze layer contains two distinct data schemas, each serving different aspects of the telematics and business intelligence platform:

• raw_business_data - containing tables, attributes, and values related to business information, such as vehicles, employees, geofences added by users, etc.

• raw_telematics_data - containing tables, attributes, and values related to the telematics data transmitting from devices under monitoring, such as locations, inputs, outputs, and events.

Each schema is optimized for its specific data domain and access patterns, providing comprehensive coverage of operational, telematic, and asset management needs.

raw_business_data structure

This schema contains 40+ carefully selected tables to cover various business aspects and use cases. These tables represent your core business entities, organizational structure, and operational data.

The interactive diagram of raw_business_data schema is available on dbdiagram.io: https://dbdiagram.io/d/V3-bronze-layer-68ecfd1c2e68d21b4131089a

Find raw business data schema details below.

raw_business_data schema

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

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

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

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

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

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

Table "places_linked_entity_fields" {
  "value" bigint [not null]
  "record_added_at" timestamp [not null]
  "place_id" integer [not null]
  "field_id" integer [not null]
}

Table "places_text_fields" {
  "place_id" integer [not null]
  "record_added_at" timestamp [not null]
  "value" text [not null]
  "field_id" integer [not null]
}

Table "users2zones" {
  "zone_id" integer [not null]
  "record_added_at" timestamp [not null]
  "user_id" integer [not null]
}

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

Table "device_output_name" {
  "device_id" integer [not null]
  "record_added_at" timestamp [not null]
  "label" "character varying(100)" [not null]
  "number" integer [not null]
}

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

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

Table "places_decimal_fields" {
  "field_id" integer [not null]
  "record_added_at" timestamp [not null]
  "place_id" integer [not null]
  "value" numeric [not null]
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Table "rules2zones" {
  "zone_id" integer [not null]
  "record_added_at" timestamp [not null]
  "rule_id" integer [not null]
}

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


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

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

Table "places_bigint_fields" {
  "field_id" integer [not null]
  "value" bigint [not null]
  "place_id" integer [not null]
  "record_added_at" timestamp [not null]
}

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

Table "description_parametrs" {
  "description" "character varying(150)"
  "record_added_at" timestamp [not null]
  "type" "character varying(100)" [not null]
  "key" integer [not null]
}

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

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

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

Table "places_longtext_fields" {
  "field_id" integer [not null]
  "value" text [not null]
  "record_added_at" timestamp [not null]
  "place_id" integer [not null]
}

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

Table "groups_objects" {
  "groups_client_id" integer
  "objects_client_id" integer

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

Update frequency

Data in this schema is synchronized with the core DB. Updates occur incrementally as changes happen in the source MySQL database, typically less than 5 minutes of the source change.

description_parameters

The system includes reference data to standardize values across the database:

Reference type	Description	Example values
Type definitions	Standard entity types	vehicle_type: car, truck, bus
Status codes	Task and system status values	tasks_status: unassigned, assigned, done
Unit definitions	Measurement units for sensors	units_type: liter, gallon, celsius
Entity classifications	Business entity categories	entities_type: place, task, customer

Key tables by category

The tables in the raw_business_data schema are organized into functional categories for easier navigation. The table below summarizes key tables by their business purpose:

Core business entities

users

Description: User accounts containing profile information, company affiliation, localization settings (timezone, locale), and hierarchical relationships via master_id for multi-level account structures

Attribute	Details
Key fields	- user_id - Unique user identifier - company_label - Name of the company associated with the user - first_name - Username - last_name - User's last name - middle_name - User's patronymic - locale - User language settings - timezone_label - Time zone in IANA format - master_id - Primary user ID (if the current one is a subordinate) - registration_datetime - Date of registration in the system - birth_date - User's date of birth
Relationships	Parent user via master_id, linked to employees, departments, places, tasks through user_id
Special notes	Central entity connecting organizational data; master_id enables user hierarchies for multi-level account structures

employees

Description: Employee and driver records used to represent people working for the organization, including personal information, licensing details, department assignments, hardware keys for iButton/RFID identification, and location data with geofencing support

Attribute	Details
Key fields	- employee_id - Employee entity identifier - user_id - User entity identifier - object_id - Entity identifier object - department_id - An ID of the department to which employee assigned - first_name - The first_name attribute of the employees table - last_name - The last_name attribute of the employees table - middle_name - The middle_name attribute of the employees table - driver_license_number - Driver's license number - driver_license_categories - Driver license categories - driver_license_issue_date - Issue date of a driver license - driver_license_valid_till - Date till a driver license valid - hardware_key - A hardware key - email - Employee's email - phone_number - Employee's phone without "+" sign - address - Address of the location - personnel_number - Employee/driver personnel number - citizen_id_number - Social Security number - latitude - Location associated with this employee - longitude - Location associated with this employee - radius - Location associated with this employee in meters - fuel_consumption - The fuel_consumption attribute of the employees table - fuel_cost - The fuel_cost attribute of the employees table - is_deleted - The is_deleted attribute of the employees table
Relationships	Links to users, departments, objects (assigned tracker), tracked in driver_history and checkins
Special notes	Hardware key enables driver identification via iButton or RFID; supports geofencing with latitude, longitude, radius fields

departments

Description: Organizational units with geographic location data (latitude, longitude, radius) enabling geofence-based analytics for department-level reporting and employee location association

Attribute	Details
Key fields	- department_id - Department entity identifier - user_id - User entity identifier - department_label - The department_label attribute of the departments table - latitude - Location associated with this departments - longitude - Location associated with this departments - radius - Geolocation size in meters - address - The address attribute of the departments table
Relationships	Links employees to organizational structure through department_id
Special notes	Location fields support geofence-based analytics for department-level reporting

Tracking and monitoring

devices

Description: Physical tracking device registry with hardware identifiers (IMEI), SIM card information, network connectivity status (signal strength, roaming, operator), and status listing assignments for device lifecycle management

Attribute	Details
Key fields	- device_id - Device ID - owner_id - ID of the device owner in whose account the beacon was added - device_imei - Device IMEI - phone - The device's SIM card number - status_listing_id - Device Status ID - network_label - The name of the network to which the SIM card is connected - signal_level - Device signal strength - has_roaming - Roaming availability flag - is_sim_blocked - SIM card lock flag - created_at - Date and time the entry was created
Relationships	Core entity linking to objects, models, sensor_description, counters; owner_id references users.user_id
Special notes	All telematic data in raw_telematics_data schema references this table via device_id

objects

Description: Central registry of monitored entities (vehicles, assets, personnel) linking physical devices to organizational structure through client_id and group_id, representing the "trackable unit" with one active object per device

Attribute	Details
Key fields	- object_id - Entity identifier object - client_id - Client entity identifier - device_id - Device entity identifier - object_label - Name of the object - model - Device model - group_id - Entity ID group - create_datetime - Date and time of creation of a new row on the server - is_deleted - The is_deleted attribute of the objects table - is_clone - Clone sign
Relationships	Central hub connecting devices to users (client_id), vehicle details, tracking history, tasks, and rules
Special notes	Represents the "trackable unit" in the system; one object per device in active use

models

Description: Central registry of monitored entities (vehicles, assets, personnel) linking physical devices to organizational structure through client_id and group_id, representing the "trackable unit" with one active object per device

Attribute	Details
Key fields	- model_id - Entity model identifier - model - The model attribute of the models table - vendor - The name of the company that released the tracker - alternative_label - The alternative_label attribute of the models table - analog_amount - Number of analog inputs of the tracker - digital_amount - Number of discrete inputs of the tracker - outputs_amount - Number of discrete tracker outputs - has_battery_level - Determines whether the tracker transmits battery charge readings - has_altitude - Determines whether the tracker transmits altitude - has_phone - Is there a SIM card? - has_gsm_level - Can a tracker transmit GSM signal strength? - has_gsm_name - Can the tracker transmit the GSM network name or operator code (MCC + MNC)? - has_gsm_roaming - Can the tracker transmit roaming status? - has_detach_button - Does the tracker have a detachment sensor? - type_output_control - Tracker Output Control Profile - type_special_control - Contains specialized settings and functional modules for individual device models, such as the dangerous driving mode (hbm_telfm) for Teltonika equipment - is_clone - Is the model a clone of another model?
Content	Boolean capability flags indicate which data fields are available from this device type
Special notes	Use capability flags to determine valid sensors and inputs when querying telematic data

sensor_description

Description: Comprehensive sensor configuration linking device inputs to business logic, including input mappings, measurement units, conversion factors (multiplier/divider), calibration tables for fuel sensors, accuracy thresholds, and grouping logic for aggregated sensor readings

Attribute	Details
Key fields	- sensor_id - Sensor entity identifier - device_id - Device entity identifier - sensor_label - Sensor name for UI - input_label - The name of the message field (attribute) from which the sensor data is taken. If equal to "input_status," it is a discrete sensor - sensor_type - Sensor type - units_type - Units of measurement - multiplier - Multiplier - the number by which to multiply the field value. For measuring sensors only - divider - Divisor - the number by which to divide the field value. For measuring sensors only - accuracy - A specified percentage for calculating the absolute error of the tank volume. This error is used to determine when refills or drains are occurring. This is used only for fuel sensors - calibration_data - The calibration_data attribute of the sensor_description table - input_id - Input number for discrete sensor - group_id - Sensors of the same type with the same group_id and source_id are considered to belong to the same group. Their data is summed or averaged, depending on the group_type value. This is required for aggregated sensors. It is used in measurement sensors - group_type - 0 - sum up the values of sensors within a group, 1 - average - sensor_units - User-entered unit name if units_type=0 (custom) - parameters - Optional object with additional parameters parent_ids - optional array of parent_ids for composite sensor. volume - double. Optional. Volume for composite sensor. parent_ids - optional. int array. Array of parent_ids for composite sensor. volume - optional. Double. Volume for composite sensor. min - optional. Double. Min acceptable raw value for a sensor. max - optional. Double. Max acceptable raw value for a sensor. max_lowering_by_time - optional. Double. Maximum legal value lowering per hour. max_lowering_by_mileage - optional. Double. Maximum legal value lowering per 100 km. ignore_drains_in_move - optional. Boolean. Default is false. If true, the fuel drains will not be detected during movement. ignore_refuels_in_move - optional. Boolean. Default is false. If true, the refuels will not be detected during movement. refuel_gap_minutes - optional. Integer. Default is 5. The time in minutes after the start of the movement, refuels will be detected during movement. custom_field_name - optional. Boolean. Default false. The parameter determines whether the input_name field is a custom value was entered by user. This makes sense only if the tracker model has the feature has_custom_fields
Relationships	Links device inputs (from raw_telematics_data.inputs) to business logic through device_id and input_label matching
Special notes	calibration_data (JSONB) stores sensor-specific calibration tables for fuel level sensors; multiplier and divider convert raw values to units

Asset management

vehicles

Description: Comprehensive vehicle registry containing specifications (dimensions, weight, capacity), documentation (VIN, registration, insurance), operational parameters (fuel consumption, tank volume), and current tracker assignment via object_id for fleet management and compliance tracking

Attribute	Details
Key fields	- vehicle_id - Vehicle entity identifier - user_id - User entity identifier - object_id - Entity identifier object - garage_id - Garage entity identifier - vehicle_label - The vehicle_label attribute of the vehicles table - registration_number - Reg number/ license plate of a vehicle - vin - The vin attribute of the vehicles table - manufacture_year - The manufacture_year attribute of the vehicles table - fuel_type - The fuel_type attribute of the vehicles table - fuel_cost - The fuel_cost attribute of the vehicles table - fuel_tank_volume - The fuel_tank_volume attribute of the vehicles table - max_speed - The max_speed attribute of the vehicles table - model - The model attribute of the vehicles table - color - The color attribute of the vehicles table - trailer - The trailer attribute of the vehicles table - additional_info - The additional_info attribute of the vehicles table - vehicle_type - The vehicle_type attribute of the vehicles table - vehicle_subtype - The vehicle_subtype attribute of the vehicles table - vehicle_status_id - Entity identifier vehicle status - chassis_number - The chassis_number attribute of the vehicles table - frame_number - The frame_number attribute of the vehicles table - trailer_reg_number - The trailer_reg_number attribute of the vehicles table - payload_weight - The payload_weight attribute of the vehicles table - payload_height - The payload_height attribute of the vehicles table - payload_length - The payload_length attribute of the vehicles table - payload_width - The payload_width attribute of the vehicles table - passenger_capacity - A maximum number of passengers - gross_weight - The gross_weight attribute of the vehicles table - standard_fuel_consumption - Normal average fuel consumption in liters per 100 km - fuel_grade - The fuel_grade attribute of the vehicles table - wheel_arrangement - The wheel_arrangement attribute of the vehicles table - tyre_size - Vehicle size: dimensions and wheel size - tyres_number - Number of wheels - liability_insurance_policy_number - The liability_insurance_policy_number attribute of the vehicles table - liability_insurance_valid_till - The date till liability insurance valid - free_insurance_policy_number - The free_insurance_policy_number attribute of the vehicles table - free_insurance_valid_till_date - The date till free insurance valid
Relationships	Links to objects (current tracker), garages (service location), vehicle_service_tasks; tracked in vehicle_trackers_history
Special notes	Physical dimension fields (payload_length, payload_width, payload_height, gross_weight) support load planning analytics; insurance dates enable compliance tracking

garages

Description: Service and maintenance facility locations with geographic coordinates (latitude, longitude, radius), contact information for mechanics and dispatchers, enabling geofence-based service visit detection and proximity analysis

Attribute	Details
Key fields	- garage_id - Garage entity identifier - user_id - User entity identifier - latitude - Location object - longitude - Location object - radius - Geolocation size in meters - address - Location object - organization_label - Depot ID - mechanic_name - Mechanic name - dispatcher_name - Dispatcher name
Relationships	Referenced by vehicles.garage_id for service location assignment
Special notes	Location fields enable geofence-based service visit detection and proximity analysis

vehicle_service_tasks

Description: Maintenance schedule and service history tracking with multiple trigger types (date-based, mileage-based, engine-hours-based), recurring task intervals, multi-channel notifications (email, SMS, push), and distinction between planned (is_repeat) and unplanned maintenance events

Attribute	Details
Key fields	- service_task_id - Service task entity identifier - vehicle_id - Vehicle entity identifier - description - The description attribute of the vehicle_service_tasks table - status - The status value of the status attribute - cost - The cost attribute of the vehicle_service_tasks table - start_date - The date and time associated with the start_date attribute - end_date - The date and time associated with the end_date attribute - completion_date - The date and time associated with the completion_date attribute - predicted_datetime - The date and time associated with the predicted_datetime attribute - mileage_limit - The mileage_limit attribute of the vehicle_service_tasks table - engine_hours_limit - The engine_hours_limit attribute of the vehicle_service_tasks table - start_mileage - The start_mileage attribute of the vehicle_service_tasks table - start_engine_hours - The start_engine_hours attribute of the vehicle_service_tasks table - mileage_notification_interval - The mileage_notification_interval attribute of the vehicle_service_tasks table - engine_hours_notification_interval - The engine_hours_notification_interval attribute of the vehicle_service_tasks table - date_notification_interval - Converting an integer N to N days - mileage_repeat_interval - The mileage_repeat_interval attribute of the vehicle_service_tasks table - engine_hours_repeat_interval - The engine_hours_repeat_interval attribute of the vehicle_service_tasks table - date_repeat_interval - Converting an integer N to N days - notification_emails - The notification_emails attribute of the vehicle_service_tasks table - notification_sms_phone_numbers - The notification_sms_phone_numbers attribute of the vehicle_service_tasks table - is_notification_push_enabled - The is_notification_push_enabled attribute of the vehicle_service_tasks table - completion_mileage - The completion_mileage attribute of the vehicle_service_tasks table - completion_engine_hours - The completion_engine_hours attribute of the vehicle_service_tasks table - is_repeat - The is_repeat attribute of the vehicle_service_tasks table - is_unplanned - The is_unplanned attribute of the vehicle_service_tasks table - comment - The comment attribute of the vehicle_service_tasks table
Content	Supports three trigger types: date-based, mileage-based, engine-hours-based; notification settings for email, SMS, push
Special notes	is_repeat and interval fields enable recurring maintenance schedules; is_unplanned distinguishes scheduled vs. reactive maintenance

Location and routing

zones

Description: Geofenced areas defining virtual perimeters using circles or polygons for monitoring vehicle/asset entry and exit events, supporting rule-based automation and location analytics with color coding for visual differentiation

Attribute	Details
Key fields	- zone_id - Zone entity identifier - client_id - Client entity identifier - zone_label - The zone_label attribute of the zones table - zone_type - The zone_type attribute of the zones table - latitude - Optional object, the bounding box which can fully contain the returned result - longitude - Optional object, the bounding box which can fully contain the returned result - circle_center_latitude - The circle_center_latitude attribute of the zones table - circle_center_longitude - The circle_center_longitude attribute of the zones table - radius - Geolocation size in meters - address - The address attribute of the zones table - color - The color attribute of the zones table
Content	Zone types include circle, polygon (defined via geofence_points), and special area classifications
Relationships	Referenced by rules2zones, users2zones; polygon vertices stored in geofence_points
Special notes	PostGIS functions can be used to check point-in-polygon for complex geofence analysis

places

Description: Points of interest with geographic coordinates, radius definitions, and extensible custom field support for storing customer contact information and business-specific data, enabling CRM/ERP integration via external_id and location-based reporting

Attribute	Details
Key fields	- place_id - Place entity identifier - user_id - User entity identifier - place_label - The place_label attribute of the places table - latitude - Location object - longitude - Location object - radius - Geolocation size in meters - address - The address attribute of the places table - description - The description attribute of the places table - external_id - ID for integration with external systems (CRM) - custom_fields - Additional fields - assigned_datetime - Date and time of assignment of the point of interest to the user
Relationships	Extended with custom field values through places_text_fields, places_decimal_fields, places_bigint_fields, places_longtext_fields, places_linked_entity_fields
Special notes	custom_fields JSONB provides quick access; related tables enable filtering and sorting on custom attributes

geofence_points

Description: Ordered vertex coordinates (number field determines sequence) defining polygon boundaries for complex geofence shapes, enabling precise geographic perimeters beyond simple circular zones, used with PostGIS ST_MakePolygon for geometric operations

Attribute	Details
Key fields	- zone_id - An ID of the zone to which this form is attached - number - Serial number - latitude - Location - longitude - Location
Relationships	Multiple records per zone_id define polygon boundaries; number field determines vertex order
Special notes	Query with ORDER BY number to reconstruct polygon path; use with PostGIS ST_MakePolygon for geometric operations

Task and workflow management

tasks

Description: Work order assignments with location validation (latitude, longitude, radius), time windows (time_from, time_to), visit duration requirements (stay_duration_minutes, arrival_duration_minutes), hierarchical structure via parent_task_id, and status tracking for field service management and delivery operations

Attribute	Details
Key fields	- task_id - Task entity identifier - user_id - User entity identifier - object_id - Entity identifier object - parent_task_id - Parent task entity identifier - task_label - The task_label attribute of the tasks table - status - The status value of the status attribute - task_type - Task type, task, route or checkpoint - latitude - The latitude attribute of the tasks table - longitude - The longitude attribute of the tasks table - radius - Geolocation size in meters - arrival_datetime - When the tracker arrives in the task area. IGNORED when creating/updating - created_at - The created_at attribute of the tasks table - status_change_datetime - Date and time of task update - time_from - The date and time associated with the time_from attribute - time_to - The date and time associated with the time_to attribute - stay_duration - The stay_duration attribute of the tasks table - stay_duration_minutes - Visit duration. The time a mobile worker must spend at the assignment site to successfully complete the task. Multiple visits are cumulative - arrival_duration_minutes - Ignore random visits shorter than the specified duration. When calculating the minimum duration, visits shorter than the specified duration will be ignored - max_delay_minuts - Acceptable lateness. The maximum amount of time an employee can be late. Any task completed during this time will be marked as "late" - is_stay_control_enabled - The is_stay_control_enabled attribute of the tasks table - address - The address attribute of the tasks table - description - Description attribute of the tasks table - custom_fields - The custom_fields attribute of the tasks table - external_id - External entity identifier - order_sort - The order_sort attribute of the tasks table - created_by - Source of the created task
Content	Supports hierarchical tasks via parent_task_id; time windows defined by time_from/time_to; geofence validation with location and radius
Relationships	Links to forms (data collection), task_history (status changes), objects (assigned tracker)
Special notes	stay_duration and arrival_duration_minutes enable compliance monitoring for delivery and service tasks

forms

Description: Configurable data collection forms for capturing structured information during task completion or mobile app check-ins, with fields and values stored as JSON, optional location validation (is_submission_in_zone), and mandatory submission requirements when attached to tasks

Attribute	Details
Key fields	- form_id - Form entity identifier - task_id - An ID of the task to which this form is attached - object_id - Entity identifier object - form_label - User-defined form label - fields - If true, form can be submitted only in task zone - values - A map with field IDs as keys and field_value objects as values. Key used to link field and its corresponding value - submitted_at - Date when form values last submitted - submission_latitude - Location at which form values last submitted - submission_longitude - Location at which form values last submitted - submission_address - Location at which form values last submitted - is_submission_in_zone - If true, form can be submitted only in task zone - description - Date when this form was created (or attached to the task) - created_at - Date when this form was created (or attached to the task)
Content	fields defines form structure (JSON); values contains submitted data (JSON)
Relationships	Links to tasks (associated work order), objects (submitter), referenced in checkins
Special notes	Location validation flag is_submission_in_zone enables geofence-based form submission rules

checkins

Description: Location-based attendance and activity records submitted via mobile application, tracking planned versus actual arrival times (planned_datetime vs actual_datetime) with geographic coordinates and location accuracy measurements (radius) for punctuality reporting

Attribute	Details
Key fields	- checkin_id - Checkin entity identifier - employee_id - The identifier of the employee entity is also the identifier for drivers - object_id - Employee device - form_id - Form entity identifier - user_id - Employee user - planned_datetime - Device time when check-in was performed - actual_datetime - Server time when the request/message was processed - latitude - Location at which checkins submitted - longitude - Location at which checkins submitted - radius - Error in positioning at a point in meters - address - Check-in address - comment - The comment attribute of the checkins table
Relationships	Connects employees to forms and locations; tracks deviation from planned schedule
Special notes	Time variance between planned_datetime and actual_datetime enables punctuality reporting; radius defines acceptable location tolerance

task_history

Description: Complete audit trail of task lifecycle events capturing all status changes, assignments, updates, and field modifications with timestamps (event_datetime), user attribution, and activity types (create, update, assign, status_change) stored in payload field for compliance and workflow analysis

Attribute	Details
Key fields	- task_history_id - Task history entity identifier - task_id - Task entity identifier - user_id - User entity identifier - activity - Operation which happened. Can be "create", "update", "assign" or "status_change" - event_datetime - Date and time of the event - payload - Depends on operation. Typically, contains fields which were changed during operation
Content	Activity types defined in description_parametrs; payload stores event-specific details (text)
Special notes	Essential for task completion analysis, status transition reporting, and user activity tracking

Rules and automation

rules

Description: Event detection rules with configurable trigger conditions (speeding, geofence violations, sensor thresholds, idle time) stored in parameters (JSONB), and multi-channel notification settings (alert_email, alert_sms, alert_phone, is_push_enabled) for automated monitoring and alerting based on device and server data

Attribute	Details
Key fields	- rule_id - Rule entity identifier - object_id - Entity identifier object - client_id - Client entity identifier - event_type - The event_type attribute of the rules table - event_label - The event_label attribute of the rules table - event_group - The event_group attribute of the rules table - description - Description attribute of the rules table - parameters - Event parameters. For more detailson availablee parameeters, see Navixy API docs. - alert_email - Mail for notifications - alert_sms - Phone numbers for SMS notifications - alert_phone - Telephones for voice calls - is_push_enabled - If true, push notifications are available - created_at - The created_at attribute of the rules table - is_deleted - The is_deleted attribute of the rules table - maximum - Limits applied to various rules. For example, for the idle time rule with the engine running in minutes - event_comment1 - The event_comment1 attribute of the rules table - event_comment2 - The event_comment2 attribute of the rules table
Content	Rule parameters (JSONB) define trigger conditions; supports email, SMS, phone, and push notifications
Relationships	Links to objects via rules2objects, zones via rules2zones
Special notes	event_type defines specific monitoring scenario (speeding, geofence breach, sensor threshold); maximum field enables event aggregation for threshold-based alerting

rules2objects

Description: Many-to-many relationship linking rules to monitored objects with per-object parameter customization via object_params (JSONB), allowing different threshold values (e.g., speed limits) for each vehicle or asset within the same rule

Attribute	Details
Key fields	- rule_id - Rule entity identifier - object_id - Entity identifier object - param_group_number - The param_group_number attribute of the rules2objects table - object_params - The object_params attribute of the rules2objects table
Content	object_params (JSONB) enables per-object rule customization (e.g., different speed limits per vehicle)
Special notes	Many-to-many relationship allows one rule to monitor multiple objects with different parameters

rules2zones

Description: Many-to-many relationship associating rules with geofenced zones, enabling a single rule to monitor entry/exit events across multiple geographic areas for complex spatial monitoring scenarios

Attribute	Details
Key fields	- rule_id - Rule entity identifier - zone_id - Zone entity identifier
Special notes	Many-to-many relationship enables multi-zone monitoring for a single rule (e.g., alert when entering any of several restricted areas)

Status and categorization

statuses

Description: Custom status definitions within status listings, including display properties (color for website display, order_sort for positioning) used to represent device or employee work states with soft delete support via is_deleted flag

Attribute	Details
Key fields	- status_id - Entity identifier status - listing_id - Listing entity identifier - status_label - Status value of the status_label attribute - color - Color used for display on the website - order_sort - Sort position within the listing status - is_deleted - The is_deleted attribute of the statuses table
Relationships	Groups of statuses organized by listing_id (references status_listings); used in status_history
Special notes	order_sort defines display sequence; color enables visual differentiation in reporting

status_listings

Description: Status set definitions controlling which status values are available for devices or employees, with permission flags (is_supervisor_controlled, is_employee_controlled) determining whether supervisors, employees, or both can change status values

Attribute	Details
Key fields	- status_listing_id - Status listing entity identifier - user_id - User entity identifier - status_listing_label - Status value of the status_listing_label attribute - is_supervisor_controlled - If true supervisors can change working status, eg using mobile monitoring app - is_employee_controlled - If true employees can change their own working status, eg using mobile tracking app - is_deleted - The is_deleted attribute of the status_listings table
Relationships	Referenced by devices.status_listing_id and statuses.listing_id
Special notes	Control flags determine who can change statuses: supervisor-only, employee self-service, or both

status_history

Description: Audit trail of all device status transitions with timestamps (changed_datetime on device, server_datetime on server), user attribution (updated_by), and location capture (latitude, longitude, address) enabling geographic analysis of status changes and workday start/end location reporting

Attribute	Details
Key fields	- status_history_id - Entity identifier status history - device_id - Device entity identifier - old_status_id - Entity identifier old status - new_status_id - Entity identifier new status - updated_by - The date and time associated with the updated_by attribute - changed_datetime - Date and time of assigning a new status on the device - server_datetime - Date and time of assigning the new server status - latitude - Locating devices during status changes - longitude - Locating devices during status changes - address - Locating devices during status changes
Relationships	Links to devices, statuses (old and new), description_parametrs (for updated_by role)
Special notes	Location capture enables geographic analysis of status transitions; useful for workday start/end location reporting

tags

Description: User-defined categorization labels with color coding that enable quick filtering and searching across multiple entity types (places, geofences, employees, tasks, trackers, vehicles) for flexible organization

Attribute	Details
Key fields	- tag_id - Entity ID tag - user_id - User entity identifier - tag_label - The tag_label attribute of the tags table - color - The color attribute of the tags table
Relationships	Applied to entities via tag_links; scope defined by user
Special notes	Flexible categorization system supporting multiple tags per entity

tag_links

Description: Polymorphic relationship table associating tags with any entity type via entity_type and entity_id, with ordinal field for display order management, enabling flexible multi-entity tagging

Attribute	Details
Key fields	- tag_id - Entity ID tag - entity_type - The entity_type attribute of the tag_links table - entity_id - Entity identifier - ordinal - The ordinal attribute of the tag_links table
Content	entity_type identifies table (vehicle, employee, task, etc.); ordinal defines display order
Special notes	Polymorphic relationship enables tagging across different entity types

Groups and hierarchy

groups

Description: Organizational grouping structure for trackers enabling visual organization in the user interface with customizable colors (group_color) and hierarchical folder-like management, currently serving a purely visual function

Attribute	Details
Key fields	- group_id - Tracker group (linked by objects.group_id). The division into groups can be seen in the list of beacons, for example - client_id - Client entity identifier - group_label - User-specified group title, 1 to 60 printable characters, eg "Employees" - group_color - Group color in web format (without #), eg "FF6DDC". Determines the color of tracker markers on the map
Relationships	Referenced by objects.group_id; client ownership via client_id (references users)
Special notes	Enables folder-like organization of monitoring entities for reporting and permissions

groups_objects

Description: Many-to-many relationship between groups and objects using composite primary key (groups_client_id, objects_client_id), enabling objects to belong to multiple groups simultaneously for flexible organizational structures

Attribute	Details
Key fields	- groups_client_id - Client entity identifier for groups - objects_client_id - Client entity identifier for objects
Special notes	Enables objects to belong to multiple groups simultaneously; query with both client_id values for group membership

Custom fields and entities

entities

Description: Entity type registry defining which business entities support custom fields and their field layout structure (sections, field_order) stored in entity_label (JSONB), enabling dynamic schema extension across places, tasks, and other entities without database changes

Attribute	Details
Key fields	- entity_id - Entity identifier - user_id - User entity identifier - entity_label - id - int. Entity identifier. type - enum. Currently, only "place" is supported. layout - object describes the layout of fields for entity. sections - array of objects. Each section can contain one or more fields. At least one section must exist in a layout. label - string. Name of section. field_order - string array. Built-in fields and IDs of custom fields (as strings) - builtin_type - The builtin_type attribute of the entities table
Relationships	Referenced by custom_fields to define which custom fields apply to which entity types
Special notes	builtin_type links to description_parametrs for system-defined entity classifications

custom_fields

Description: Custom field definitions enabling dynamic schema extension for entity types, with configurable field types (custom_field_type), validation rules and options in parameters (JSONB), and requirement flags (is_required) for flexible data capture across places, tasks, and other entities

Attribute	Details
Key fields	- custom_field_id - Custom field entity identifier - entity_id - Entity identifier - custom_field_label - Field name - custom_field_type - Data type in the field - description - Field Description - is_required - Is this required or not? - parameters - Field parameters
Content	parameters (JSONB) stores field-type-specific configuration (validation rules, dropdown options, etc.)
Relationships	Defines available custom attributes for entities; field type links to description_parametrs
Special notes	Enables dynamic schema extension without database changes; used extensively in places and tasks

Historical tracking

driver_history

Description: Complete audit trail of employee-to-vehicle assignments over time tracking old_employee_id to new_employee_id transitions with timestamps (changed_datetime, server_datetime), location data (latitude, longitude, address), hardware key information, and user attribution (updated_by) enabling driver-specific analytics when drivers switch between vehicles

Attribute	Details
Key fields	- driver_history_id - Driver history entity identifier - object_id - Entity identifier object - old_employee_id - Old employee entity identifier - new_employee_id - New employee entity identifier - hardware_key - The hardware_key attribute of the driver_history table - changed_datetime - Date and time changes were made to the device - server_datetime - Date and time of changes made on the server - updated_by - The date and time associated with the updated_by attribute - latitude - The latitude attribute of the driver_history table - longitude - The longitude attribute of the driver_history table - address - The address attribute of the driver_history table
Relationships	Tracks driver assignments to vehicles over time; links to employees and objects
Special notes	Essential for driver-specific reporting when drivers switch vehicles; location capture enables assignment change location analysis

vehicle_trackers_history

Description: Audit trail tracking which GPS devices (object_id) were installed in which vehicles (vehicle_id) over time with change timestamps (changed_datetime), enabling accurate historical data attribution and mileage calculation when trackers are moved between vehicles

Attribute	Details
Key fields	- vehicle_tracker_history_id - Vehicle tracker history entity identifier - vehicle_id - Vehicle entity identifier - object_id - Entity identifier object - changed_datetime - The date and time associated with the changed_datetime attribute
Relationships	Tracks which GPS device was installed in which vehicle over time
Special notes	Critical for historical data analysis when trackers are moved between vehicles; enables accurate mileage and usage attribution

Reference and lookup data

description_parametrs

Description: System-wide reference data providing human-readable labels (description) for enumerated integer values (key) used throughout the database, organized by type field (e.g., task_status, fuel_type, counter_type, entity_classification) for consistent value translation in reporting and UI display

Attribute	Details
Key fields	- key - Possible value in the attribute - type - A composite attribute consisting of the table name followed by an underscore and the name of an attribute in the table - description - Implied value of an attribute
Content	Provides human-readable labels for coded values throughout the database (task status, fuel types, counter types, etc.)
Relationships	Referenced via foreign keys from multiple tables for standardized categorization
Special notes	Essential for translating integer codes to readable values in reporting; type field groups related enumerations

counters

Description: Odometer and engine-hour counter configurations linking device sensor readings (sensor_id) to distance or time measurements with multiplier coefficients for unit conversion (km, miles, hours) and counter_type from description_parametrs defining measurement type

Attribute	Details
Key fields	- counter_id - Internal ID - device_id - Device entity identifier - counter_type - Counter type - sensor_id - Sensor entity identifier - multiplier - Coefficient for converting values into one of the metrics (km, l, etc.)
Relationships	Links devices to sensor readings that represent distance or time counters
Special notes	multiplier converts sensor pulses to actual units (km, miles, hours); counter_type from description_parametrs defines measurement type

device_output_name

Description: Custom labels for device output channels mapping numeric output identifiers (number) to user-defined names (label) such as "Door Lock" or "Engine Block" for readable reporting and analysis of device output commands and states

Attribute	Details
Key fields	- device_id - Device entity identifier - number - The number attribute of the device_output_name table - label - The label attribute of the device_output_name table
Content	Maps output channel numbers to user-defined names (e.g., "Door Lock", "Engine Block")
Special notes	Enables readable reporting when analyzing device output commands and states

raw_telematics_data structure

The raw_telematics_data schema contains three primary table types that work together to provide comprehensive device data.

Bronze layer raw telematics data ERD

The interactive diagram of raw_telematics_data schema is available on dbdiagram.io: https://dbdiagram.io/d/v1-schema-telematics-bd-67a0acef263d6cf9a0d8e750

Find raw telematics data schema details below.

raw_telematics_data schema

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)

Key tables by category

Each table serves a specific purpose in capturing different aspects of device information:

tracking_data_core

Purpose: Core location and motion data

Attribute	Details
Key fields	device_id, device_time, platform_time, latitude, longitude, speed, altitude, satellites, hdop, event_id
Indexing	Optimized with index on (device_id, device_time)
Special notes	Location data (latitude and longitude) uses integer format with 10⁷ precision for optimal TimescaleDB performance Speed is also stored in integer, so you need to divide it with 100

inputs

Purpose: Sensor readings from devices

Attribute	Details
Key fields	input_id, device_id, device_time, sensor_name, value
Content	Analog readings (fuel level, temperature, voltage), calculated values (engine RPM)
Relationships	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

Purpose: Device status indicators and operational modes

Attribute	Details
Key Fields	state_id, device_id, device_time, state_name, value
Content	Operating mode indicators (working, idle, off), component statuses (ignition, doors)
Value Format	Boolean values (1/0) or specific status codes

Data in this schema is ingested directly from devices, with minimal latency (typically seconds). The schema is optimized for time-series data using TimescaleDB for efficient storage and retrieval.

Additional information

Data validation

The database enforces data integrity through multiple mechanisms:

• CHECK constraints validate that values fall within acceptable ranges

• Foreign keys ensure relationships between tables remain consistent

• NOT NULL constraints guarantee that required fields always have values

• DEFAULT values provide fallback when data isn't explicitly provided

Query optimization

Tables are organized with specific indexing strategies:

• All tables include time-based indexes on record_added_at

• Foreign key columns have dedicated indexes for join performance

• Frequently used column combinations have composite indexes

• TimescaleDB provides specialized indexes for time-series queries

repo data structure

This schema is currently in development. If you're interested in early access or have questions about this functionality, please contact [email protected].

The repo schema provides a comprehensive framework for managing organizational structures, assets, devices, and their relationships in multi-tenant environments. Built on PostgreSQL 14+ with the ltree extension, the schema supports hierarchical organizations, custom field definitions for any entity type, role-based access control with object-level restrictions, and complete audit trails with field-level change tracking. All entities can be extended without schema modifications, localized for international deployments, and linked through flexible polymorphic relationships.

The schema addresses complex data management scenarios including fleet asset hierarchies across organizational levels, multi-tenant SaaS platforms requiring data isolation, compliance-driven operations with detailed audit requirements, and systems needing dynamic data models adaptable through custom fields rather than database migrations.

The interactive diagram ofrepo data schema is available on dbdiagram.io: https://dbdiagram.io/d/Navixy-Repo-data-schema-68ad788c1e7a611967a0930e

Find the repo schema details below.

repo data schema

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

Update frequency

Data in the repo schema is synchronized in real-time with source systems. Updates occur immediately as changes happen, with audit trails capturing all modifications for compliance and historical analysis.

ci_base

The repo schema uses a Single Table Inheritance pattern for all reference data through the ci_base table:

The repo schema uses a Single Table Inheritance pattern for all reference data through the ci_base table. This design consolidates system dictionaries, classifications, and user-defined reference items into one unified structure, providing consistency and flexibility across the entire schema.

Architecture:

The ci_base table serves as the foundation for all reference data, using a discriminator field to identify the specific reference type. Each reference type has a corresponding table (like ci_device_type, ci_asset_type) that shares the same id as ci_base, creating a type-safe inheritance relationship.

How business entities connect to ci_base:

All business entities in the repo schema reference ci_base subtypes to define their classification and behavior:

• organization → references ci_organization_type (which inherits from ci_entity_type → ci_base)

• user → references ci_user_type (which inherits from ci_entity_type → ci_base)

• device → references ci_device_type and ci_device_status (both inherit from ci_base)

• asset → references ci_asset_type (which inherits from ci_entity_type → ci_base)

• inventory → references ci_inventory_type (which inherits from ci_entity_type → ci_base)

• asset_group → references ci_asset_group_type (which inherits from ci_entity_type → ci_base)

Reference type categories:

Category	Tables	Purpose
System configuration	ci_module, ci_country, ci_role	Define system modules, geographic references, and user roles
Entity type definitions	ci_entity_type, ci_device_type, ci_asset_type, ci_inventory_type, ci_organization_type, ci_user_type, ci_asset_group_type	Classify all business entities by type
Status and classification	ci_device_status, ci_asset_type_category	Track entity states and group types into categories
Access control	ci_permission_scope	Define what permissions can be granted (connected to ci_module and ci_entity_type)
Relationships	ci_device_relation_type	Define types of relationships between devices (master-slave, backup, etc.)
Categorization	ci_tag, ci_catalog_category	Enable flexible tagging and catalog organization

Example query patterns

-- Get all device types for an organization (system + custom)
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;

-- Get asset type with its category
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;

-- Get hierarchical tag structure
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;

Key tables by category

The tables in the repo schema are organized into functional categories. The descriptions below summarize the most important tables by their business purpose.

organization

Purpose: Hierarchical organization management

Attribute	Details
Key fields	id, parent_id, path, organization_type_id, title_en, is_active, deleted_at
Indexing	GiST index on path for hierarchical queries, indexes on parent_id and organization_type_id
Special notes	Uses ltree for multi-level hierarchies, inherits from customizable_entity for custom fields support

user

Purpose: User accounts and authentication

Attribute	Details
Key fields	id, organization_id, user_type_id, identity_provider, identity_provider_id, full_name, is_active
Indexing	Unique index on (organization_id, identity_provider, identity_provider_id)
Special notes	External identity provider integration (Keycloak, Auth0, Okta), inherits from customizable_entity

device

Purpose: Physical tracking devices

Attribute	Details
Key fields	id, organization_id, device_type_id, status_id, hw_id, label
Indexing	Indexes on organization_id, device_type_id, status_id, hw_id
Special notes	Hardware identifier for device tracking, inherits from customizable_entity for custom fields

asset

Purpose: Physical or virtual assets

Attribute	Details
Key fields	id, organization_id, asset_type_id, label, description
Indexing	Indexes on organization_id and asset_type_id
Special notes	Inherits from customizable_entity, linked to devices via device_asset_link

inventory

Purpose: Inventory and warehouse records

Attribute	Details
Key fields	id, organization_id, inventory_type_id, code
Indexing	Unique index on (organization_id, code)
Special notes	Unique codes within organization, linked to devices via device_inventory_link

asset_group

Purpose: Asset grouping with historical tracking

Attribute	Details
Key fields	id, organization_id, group_type_id, title_en, description
Relationships	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
Special notes	Time-based membership via asset_group_item, query current members with WHERE detached_at IS NULL

custom_field_def

Purpose: Custom field definitions and metadata

Attribute	Details
Key fields	id, organization_id, owner_entity_type_id, code, field_type, is_multi, is_required
Content	Field types include text, number, boolean, date, datetime, entity_ref, catalog_item_ref
Special notes	Enables flexible custom fields for any entity type, values stored in type-specific custom_field_value_* tables

acl_role_permission

Purpose: Role-based permission management

Attribute	Details
Key fields	id, role_id, permission_scope_id, target_entity_id, actions
Content	Action bitmask (READ=1, UPDATE=2, DELETE=4, CREATE=8), target-specific or entity-type-wide permissions
Relationships	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
Special notes	Works with user_role and acl_user_scope to determine final user permissions

audit_event

Purpose: Unified audit log for all system changes

Attribute	Details
Key fields	id, event_category, user_id, aggregate_type, aggregate_id, event_type, event_data, occurred_at
Indexing	Indexes on (user_id, occurred_at), (aggregate_type, aggregate_id, occurred_at), (event_category, occurred_at)
Special notes	Partitioned by occurred_at (monthly), two categories: auth (authentication) and domain (business events), stores field-level change deltas in event_data JSONB

Data relationships

The repo schema implements sophisticated relationship patterns for flexible data modeling:

Hierarchical structures

• Organizations use ltree paths for efficient tree queries

• Reference items (ci_base) support optional hierarchies

• Automatic path maintenance via database triggers

Inheritance patterns

• Table inheritance: customizable_entity → business entities (organization, user, device, asset, inventory, asset_group)

• ID inheritance: ci_base → reference type tables

• Type discrimination via entity_type_id and discriminator fields

Polymorphic relationships

Certain tables use polymorphic references without foreign key constraints for maximum flexibility:

• acl_role_permission.target_entity_id → any customizable_entity

• acl_user_scope.target_entity_id → any customizable_entity

• entity_tag.entity_id → any customizable_entity

These relationships are validated at the application level.

Additional information

Data validation

The repo schema enforces data integrity through multiple mechanisms:

Database constraints

• UNIQUE constraints with soft delete support (partial indexes WHERE deleted_at IS NULL)

• CHECK constraints (e.g., device_relation ensures master_id ≠ slave_id)

• NOT NULL constraints on required fields

• DEFAULT values for timestamps and boolean flags

Application-level validation

• Entity type validation for polymorphic references

• Catalog validation for custom field references

• Custom field type validation

• Multi-value field array management

Query optimization

Tables are organized with specific indexing strategies:

Standard indexes:

• All foreign keys have dedicated indexes

• Time-based indexes on created_at, updated_at, deleted_at

• Composite indexes for frequently joined columns

Specialized indexes:

• GiST indexes on ltree paths for hierarchical queries

• Partial unique indexes supporting soft delete

• Custom field value indexes for filtering and sorting

• Audit event indexes on time + entity for efficient lookups

Performance considerations:

• Connection pooling recommended (PgBouncer)

• Regular VACUUM maintenance for large tables

• Possible future partitioning for device table by organization_id

• Materialized views for complex access control calculations