Skip to content

Database

Jump to bottom
Sergio Hernandez edited this page Jul 12, 2026 · 3 revisions

Database

TrackHub utilizes PostgreSQL as its robust data backbone, enhanced with the PostGIS extension to efficiently process and analyze geographical data. This combination empowers the platform to perform spatial queries and complex geolocation tasks with precision and speed, making it an ideal solution for location-based services.

The platform uses two separate databases to enforce domain separation between identity/authorization concerns and business data:

Database Purpose Extensions
TrackHubSecurity Identity, users, roles, policies, OAuth clients —
TrackHub Accounts, assets, positions, geofences PostGIS

TrackHub Database (Manager)

This database stores all business domain data: accounts, transporters, devices, operators, positions, groups, and geofences.

erDiagram
    Account ||--o{ User : "has"
    Account ||--o{ Operator : "has"
    Account ||--o{ Transporter : "has"
    Account ||--o{ Device : "has"
    Account ||--o{ "Group" : "has"
    Account ||--o| AccountSettings : "configures"

    User ||--o{ UserGroup : "belongs to"
    "Group" ||--o{ UserGroup : "contains"
    "Group" ||--o{ TransporterGroup : "assigns"

    Transporter ||--o{ TransporterGroup : "belongs to"
    Transporter ||--o| TransporterPosition : "current position"
    Transporter }o--|| TransporterType : "classified as"

    Device }o--o| Transporter : "installed in"
    Device }o--|| Operator : "managed by"

    Operator ||--o{ Credential : "authenticates via"

    Account {
        uuid AccountId PK
        string Name
        string Description
        short Type
        bool Active
    }

    AccountSettings {
        uuid AccountId PK
        short Maps
        int OnlineInterval
        int StoringInterval
        int RefreshMapInterval
        bool GeofencingEnabled
        bool TripManagementEnabled
    }

    User {
        uuid UserId PK
        string FirstName
        string LastName
        string Username
        bool Active
        uuid AccountId FK
    }

    Operator {
        uuid OperatorId PK
        string Name
        string Description
        short ProtocolTypeId
        uuid AccountId FK
    }

    Credential {
        uuid CredentialId PK
        string Uri
        string Username
        string Password
        string Token
        datetime TokenExpiration
        string RefreshToken
        string Salt
        string Key
        string Key2
        uuid OperatorId FK
    }

    Transporter {
        uuid TransporterId PK
        string Name
        short TransporterTypeId FK
        uuid AccountId FK
    }

    TransporterType {
        short TransporterTypeId PK
        string Name
        string Description
    }

    Device {
        uuid DeviceId PK
        string Name
        int Identifier
        string Serial
        short DeviceTypeId
        string Description
        uuid TransporterId FK
        uuid OperatorId FK
        uuid AccountId FK
    }

    TransporterPosition {
        uuid TransporterId PK
        double Latitude
        double Longitude
        double Altitude
        double Speed
        datetime EventDate
        string GeocodeAddress
        string DeviceTime
        string ServerTime
        int Attributes
    }

    "Group" {
        uuid GroupId PK
        string Name
        string Description
        bool Active
        uuid AccountId FK
    }

    UserGroup {
        uuid UserId PK
        uuid GroupId PK
    }

    TransporterGroup {
        uuid TransporterId PK
        uuid GroupId PK
    }
Loading

Geofencing Schema

The geofencing tables reside in the same database under a dedicated schema, using PostGIS geometry types for spatial operations:

erDiagram
    Geofence ||--o{ GeofenceEvent : "triggers"
    Transporter ||--o{ GeofenceEvent : "enters/exits"

    Geofence {
        uuid GeofenceId PK
        string Name
        string Description
        short GeofenceTypeId
        geometry Geom
        uuid AccountId FK
    }

    GeofenceEvent {
        uuid GeofenceEventId PK
        uuid TransporterId FK
        uuid GeofenceId FK
        datetime DatetimeIn
        datetime DatetimeOut
        double Latitude
        double Longitude
    }
Loading

The vw_transporter_position view provides user-scoped access to transporter positions by joining through the group membership chain:

transporter_position → transporters → transporter_group → user_group

This ensures each user only sees positions for transporters in their assigned groups.

TrackHubSecurity Database

This database manages authentication, authorization, and the permission model. It implements a flexible RBAC + Policy-based access control system.

erDiagram
    User ||--o| Client : "OAuth client"
    User ||--o{ UserRole : "assigned"
    User ||--o{ UserPolicy : "granted"

    Role ||--o{ UserRole : "assigned to"
    Role ||--o{ ResourceActionRole : "grants"
    Role }o--o| Role : "parent hierarchy"

    Policy ||--o{ UserPolicy : "granted to"
    Policy ||--o{ ResourceActionPolicy : "defines"

    Resource ||--o{ ResourceAction : "has"
    Action ||--o{ ResourceAction : "applied to"

    ResourceAction ||--o{ ResourceActionPolicy : "controlled by"
    ResourceAction ||--o{ ResourceActionRole : "restricted by"

    User {
        uuid UserId PK
        string Username
        string Password
        bool Active
        int LoginAttempts
        bool Verified
        uuid AccountId
    }

    Client {
        uuid ClientId PK
        uuid UserId FK
    }

    Role {
        int RoleId PK
        string Name
        string Description
        int ParentRoleId FK
        bool Active
    }

    Policy {
        int PolicyId PK
        string Name
        string Description
    }

    Resource {
        int ResourceId PK
        string ResourceName
        string Description
    }

    Action {
        int ActionId PK
        string ActionName
        string Description
    }

    ResourceAction {
        int ResourceId PK
        int ActionId PK
    }

    ResourceActionPolicy {
        int ResourceActionPolicyId PK
        int ResourceId FK
        int ActionId FK
        int PolicyId FK
    }

    ResourceActionRole {
        int ResourceActionRoleId PK
        int ResourceId FK
        int ActionId FK
        int RoleId FK
    }

    UserPolicy {
        int PolicyId PK
        uuid UserId PK
    }

    UserRole {
        int RoleId PK
        uuid UserId PK
    }
Loading

Permission Resolution

When a user attempts an action on a resource, the authorization pipeline resolves permissions through two paths:

graph LR
    subgraph Check["Authorization Check"]
        req["Request:<br/>Resource + Action"]
    end

    subgraph PolicyPath["Policy Path"]
        up["UserPolicy"]
        rap["ResourceActionPolicy"]
    end

    subgraph RolePath["Role Path"]
        ur["UserRole"]
        rar["ResourceActionRole"]
    end

    req --> up --> rap --> grant["✅ Granted"]
    req --> ur --> rar --> grant
    req -->|Neither path matches| deny["❌ Denied"]
Loading

Access is granted if either the policy-based or role-based path resolves to a matching Resource + Action pair.

Multi-Tenant Isolation

Tenant isolation is enforced at the database level through the AccountId foreign key:

Entity Isolation Column Strategy
Users AccountId Direct FK
Operators AccountId Direct FK
Transporters AccountId Direct FK
Devices AccountId Direct FK
Groups AccountId Direct FK
Geofences AccountId Direct FK

All queries filter by the authenticated user's AccountId, ensuring complete data isolation between tenants.

Clone this wiki locally