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
raw_business_data structureThis 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.
Find raw business data schema details below.
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
description_parametersThe system includes reference data to standardize values across the database:
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:
Database Schema Overview
Organizational structure
users
departments
employees
groups
User accounts with profile information
Departments with geolocation data
Employee and driver details
Tracker organization groups
Objects and devices
devices
models
objects
vehicles
sensor_description
Physical tracking devices
Device model specifications
Monitored objects
Vehicle details and specifications
Sensor configuration details
Places and zones
places
zones
garages
tags
Points of interest with geolocation
Geofenced monitoring areas
Vehicle service locations
Organizational labels
Operational data
tasks
forms
checkins
events
statuses
vehicle_service_tasks
Task assignments and tracking
Data collection forms
Location-based attendance records
System events and notifications
Status definitions
Vehicle maintenance records
raw_telematics_data structure
raw_telematics_data structureThe raw_telematics_data schema contains three primary table types that work together to provide comprehensive device data.
Find raw telematics data schema details below.
Key tables by category
Each table serves a specific purpose in capturing different aspects of device information:
tracking_data_core
tracking_data_corePurpose: Core location and motion data
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
inputsPurpose: Sensor readings from devices
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_timestates
statesPurpose: Device status indicators and operational modes
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_atForeign 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
repo data structureThis 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.
Find the repo schema details below.
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
ci_baseThe 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→ referencesci_organization_type(which inherits fromci_entity_type→ci_base)user→ referencesci_user_type(which inherits fromci_entity_type→ci_base)device→ referencesci_device_typeandci_device_status(both inherit fromci_base)asset→ referencesci_asset_type(which inherits fromci_entity_type→ci_base)inventory→ referencesci_inventory_type(which inherits fromci_entity_type→ci_base)asset_group→ referencesci_asset_group_type(which inherits fromci_entity_type→ci_base)
Reference type categories:
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
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
organizationPurpose: Hierarchical organization management
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
userPurpose: User accounts and authentication
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
devicePurpose: Physical tracking devices
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
assetPurpose: Physical or virtual assets
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
inventoryPurpose: Inventory and warehouse records
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
asset_groupPurpose: Asset grouping with historical tracking
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
custom_field_defPurpose: Custom field definitions and metadata
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
acl_role_permissionPurpose: Role-based permission management
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
audit_eventPurpose: Unified audit log for all system changes
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 hierarchiesAutomatic 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 tablesType discrimination via
entity_type_idanddiscriminatorfields
Polymorphic relationships
Certain tables use polymorphic references without foreign key constraints for maximum flexibility:
acl_role_permission.target_entity_id→ anycustomizable_entityacl_user_scope.target_entity_id→ anycustomizable_entityentity_tag.entity_id→ anycustomizable_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_atIS NULL)CHECK constraints (e.g.,
device_relationensuresmaster_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_atComposite 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
devicetable byorganization_idMaterialized views for complex access control calculations
Last updated
Was this helpful?