Skip to content

Database

Jump to bottom
Sergio Hernandez edited this page Jul 24, 2026 · 5 revisions

Database

TrackHub runs on PostgreSQL, with the PostGIS extension for spatial data. The platform uses two databases to keep identity and authorization concerns separate from business data:

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

Related pages: Architecture · Deployment and Operations

Schema ownership

Within the TrackHub database, each service owns a schema. Services read other schemas only through read-only mappings excluded from their migrations — one owner per table, always.

Schema Owner Migrated by Contents
app Manager Manager Accounts, users, groups, transporters, devices, operators, credentials, documents, drivers, alerts, notifications, announcements, audit, background job runs, reports
map Manager Manager Geocoding providers, points of interest
telemetry Manager (tables) / Telemetry (service) Manager Latest positions, position history, operator sync runs, operator health checks
geofencing Geofencing Geofencing Geofences and geofence visit events (PostGIS)
trip TripManagement TripManagement Trips, stops, deliveries, proofs of delivery, route plans, toll catalog (PostGIS)
security Security Security Users, roles, policies, resources, actions, service-client permissions, driver credentials
public — OpenIddict / views OpenIddict tables; database views

Telemetry has no migrations of its own. Its telemetry-schema tables are created by the Manager migrations, which is why the Telemetry service must be pointed at the same TrackHub database. The Telemetry service maps and serves those tables; it does not own their DDL.

AuthorityServer owns only the OpenIddict* tables. Its AuthorityDbContext migration is part of the documented install sequence and targets the Security database. Its SecurityDbContext is a narrow read-only projection of Security-owned tables, and its migration is deliberately empty — every configuration is ExcludeFromMigrations().

Read-only projections

A service that needs another owner's data for scoping maps a minimal, read-only projection rather than making a network call per request:

Service Reads (read-only, excluded from its migrations)
Telemetry app.users, app.groups, app.user_group, app.transporters, app.transporter_group, app.transporter_device_assignments, app.devices, app.operators, app.accounts, app.account_features
Geofencing app.accounts, app.account_features, app.audit_events
TripManagement app.accounts, app.account_features, app.audit_events, app.drivers, app.transporters, geofencing.geofences
Manager telemetry.* (it owns the DDL)

TrackHub database — master data (app, map)

The Manager service owns 39 mapped entities. The core master-data relationships:

erDiagram
    Account ||--o{ User : "has"
    Account ||--o{ Operator : "has"
    Account ||--o{ Transporter : "has"
    Account ||--o{ Device : "has"
    Account ||--o{ "Group" : "has"
    Account ||--o{ Driver : "employs"
    Account ||--o| AccountSettings : "configures"
    Account ||--o{ AccountFeature : "enables"
    Account ||--o| AccountBranding : "branded by"

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

    Transporter ||--o{ TransporterGroup : "belongs to"
    Transporter }o--|| TransporterType : "classified as"
    Transporter ||--o{ TransporterDeviceAssignment : "carries"
    Device ||--o{ TransporterDeviceAssignment : "assigned to"
    Device }o--|| Operator : "managed by"

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

    Driver ||--o{ DriverQualification : "holds"
    Driver ||--o{ DriverTransporterAssignment : "assigned to"
    Transporter ||--o{ DriverTransporterAssignment : "driven by"

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

    AccountFeature {
        uuid AccountFeatureId PK
        uuid AccountId FK
        string FeatureKey
        bool Enabled
    }

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

    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
    }

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

    Driver {
        uuid DriverId PK
        string FirstName
        string LastName
        string Phone
        uuid AccountId FK
    }

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

app also holds the documents module (documents, document_versions, document_types, document_signatures), the alerts and notifications module (alert_events, alert_subscriptions, notification_rules, notification_deliveries, notification_templates), platform announcements, public link grants, audit events, background job runs, account support grants and the report catalog. See Manager for what each module does.

map holds geocoding_providers and points_of_interest.

Telemetry schema

Owned as DDL by Manager, served by the Telemetry service.

erDiagram
    Transporter ||--o| TransporterPosition : "current fix"
    Transporter ||--o{ TransporterPositionHistory : "track"
    Operator ||--o{ OperatorSyncRun : "sync attempts"
    Operator ||--o{ OperatorHealthCheck : "probes"

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

    TransporterPositionHistory {
        bigint PositionHistoryId PK
        uuid TransporterId FK
        double Latitude
        double Longitude
        double Speed
        datetime EventDate
        string GeocodeAddress
        json Attributes
        string IdempotencyKey
    }

    OperatorSyncRun {
        bigint OperatorSyncRunId PK
        uuid OperatorId FK
        string SyncType
        int DeviceCount
        int PositionCount
        string Result
        string Error
        datetime StartedAt
    }

    OperatorHealthCheck {
        bigint OperatorHealthCheckId PK
        uuid OperatorId FK
        string CheckType
        string Status
        int LatencyMs
        datetime CheckedAt
    }
Loading

Operator health and sync status are derived at read time from these two tables — the operator row carries no health rollup columns. See Telemetry.

attributes is a PostgreSQL json column. json has no equality operator, so Distinct(), GroupBy() or set operations over an entity or projection that includes it fail at runtime with error 42883. De-duplicate with EXISTS or key-based predicates. EF InMemory will not catch this.

Geofencing schema

Uses PostGIS geometry (SRID 4326) with a GiST index on the geofence polygon.

erDiagram
    Geofence ||--o{ GeofenceEvent : "records visits"

    Geofence {
        uuid GeofenceId PK
        string Name
        string Description
        short GeofenceTypeId
        geometry Geom
        geometry CircleCenter
        double CircleRadiusMeters
        bool AlertOnEntry
        bool AlertOnExit
        int DwellThresholdMinutes
        bool Active
        uuid AccountId FK
    }

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

A circle geofence is metadata plus geometry: CircleCenter and CircleRadiusMeters are stored alongside Geom, which always holds a polygon (a 64-segment buffer computed at write time). Detection, indexing, reporting and overlays are therefore shape-agnostic; editors render the true circle from the metadata.

One row is one visit — entry plus a nullable departure. See Geofencing.

Trip schema

The TripManagement service owns 13 tables in the trip schema, also PostGIS at SRID 4326. Trip execution is evaluated against stored geometry: a route LineString, a buffered corridor Polygon, and a per-stop arrival Polygon snapshotted when the trip starts — so editing a linked geofence mid-trip cannot move a running trip's arrival area.

erDiagram
    Trip ||--o| RoutePlan : "planned by"
    Trip ||--o{ TripStop : "has"
    Trip ||--o{ TripAssignment : "assigned to"
    Trip ||--o{ TripEvent : "records"
    Trip ||--o{ TripShare : "shared via"
    Trip ||--o{ TripDocument : "attaches"
    TripStop ||--o{ Delivery : "delivers"
    TripStop ||--o{ ProofOfDelivery : "proven by"
    TollStation ||--o{ TollTariff : "priced by"
    TollVehicleClass ||--o{ TollTariff : "classifies"

    Trip {
        uuid TripId PK
        uuid AccountId
        string Code
        string Status
        uuid TransporterId
        uuid DriverId
        uuid RoutePlanId
        string ExternalReference
        string CustomerName
        string OriginName
        geometry OriginPoint
        datetime PlannedStartAt
        datetime ActualStartAt
        datetime ActualEndAt
        datetime LastPositionAt
        geometry LastPoint
        double ActualDistanceMeters
        string TollVehicleClass
        datetime DeviationOpenedAt
        int ConsecutiveOutsideFixes
    }

    RoutePlan {
        uuid RoutePlanId PK
        uuid AccountId
        uuid TripId FK
        string Provider
        geometry Geom
        geometry CorridorGeom
        int CorridorMeters
        double PlannedDistanceMeters
        int PlannedDurationSeconds
        string WaypointsJson
        string LegsJson
        string Status
        decimal EstimatedTollAmount
        string TollCurrency
        string TollStatus
        string TollStationsJson
    }

    TripStop {
        uuid TripStopId PK
        uuid AccountId
        uuid TripId FK
        int Sequence
        string Name
        geometry Point
        uuid GeofenceId
        geometry ArrivalGeom
        int ArrivalRadiusMeters
        datetime PlannedArrivalFrom
        datetime PlannedArrivalTo
        string Status
        datetime ActualArrivalAt
        datetime ActualDepartureAt
        datetime OutsideSinceAt
        datetime EtaAt
        string EtaSource
        bool RequiresPod
    }

    Delivery {
        uuid DeliveryId PK
        uuid AccountId
        uuid TripStopId FK
        string Reference
        string ClientName
        string Status
        int SequenceIndex
    }

    ProofOfDelivery {
        uuid ProofOfDeliveryId PK
        uuid AccountId
        uuid TripStopId FK
        uuid DeliveryId FK
        string ReceiverName
        datetime CapturedAt
        double Latitude
        double Longitude
        uuid ClientEventId
    }

    TripAssignment {
        uuid TripAssignmentId PK
        uuid AccountId
        uuid TripId FK
        uuid DriverId
        uuid TransporterId
        string Status
        datetime AssignedAt
        datetime EndedAt
    }

    TripEvent {
        uuid TripEventId PK
        uuid AccountId
        uuid TripId FK
        uuid TripStopId FK
        string EventType
        datetime OccurredAt
        string Source
        string PayloadJson
        string IdempotencyKey
    }

    TripShare {
        uuid TripShareId PK
        uuid AccountId
        uuid TripId FK
        uuid PublicLinkGrantId
        bool IncludeDriverName
        bool IncludeVehicle
        bool IncludeLivePosition
        bool IncludeStopDetail
        bool IncludePodSummary
        bool IncludeRoute
        datetime ExpiresAt
        datetime RevokedAt
    }

    TripDocument {
        uuid TripDocumentId PK
        uuid AccountId
        uuid TripId FK
        uuid TripStopId FK
        uuid ProofOfDeliveryId FK
        uuid DocumentId
        string Kind
    }

    TollStation {
        uuid TollStationId PK
        string Name
        string Code
        geometry Point
        string Country
        string Region
        string RoadName
        string Direction
        string Operator
        bool Active
    }

    TollTariff {
        uuid TollTariffId PK
        uuid TollStationId FK
        string TollVehicleClassCode
        decimal Amount
        string Currency
        date EffectiveFrom
        date EffectiveTo
    }

    TollVehicleClass {
        uuid TollVehicleClassId PK
        string Code
        string Name
        int SortOrder
        bool Active
    }

    TransporterTollClass {
        uuid TransporterTollClassId PK
        uuid AccountId
        short TransporterTypeId
        uuid TransporterId
        string TollVehicleClassCode
    }
Loading

Tables — trip.trips, trip.trip_stops, trip.trip_deliveries, trip.trip_pods, trip.trip_assignments, trip.trip_events, trip.trip_documents, trip.trip_shares, trip.route_plans, plus the platform-level toll reference data trip.toll_stations, trip.toll_tariffs, trip.toll_vehicle_classes and the per-account mapping trip.transporter_toll_classes.

Spatial (GiST) indexes — ix_route_plans_geom_gist, ix_route_plans_corridorgeom_gist, ix_trip_stops_arrivalgeom_gist, ix_toll_stations_point_gist.

Tenant and lookup indexes — every account-scoped table carries an accountid-leading composite index (for example ix_trips_accountid_status_plannedstartat). Uniqueness comes from ux_trips_accountid_code, ux_trip_stops_tripid_sequence, ux_trip_events_idempotencykey, ux_trip_pods_tripstopid_clienteventid, ux_trip_shares_publiclinkgrantid, ux_toll_vehicle_classes_code and ux_toll_tariffs_station_class_open, among others.

Migrations: InitialCreate, AddTripDetectionState, AddPublicShareDisclosureAndTollIntegrity.

TrackHubSecurity database

Authentication, authorization and the permission model — a RBAC + policy system.

erDiagram
    User ||--o{ UserRole : "assigned"
    User ||--o{ UserPolicy : "granted"
    User ||--o{ DriverCredential : "driver login"

    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"

    Client ||--o{ ServiceClientPermission : "granted"

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

    Client {
        uuid ClientId PK
        string Name
        uuid UserId FK
    }

    ServiceClientPermission {
        uuid ServiceClientPermissionId PK
        uuid ClientId FK
        string Resource
        string Action
        string Scope
        string Audience
        uuid AccountId
        bool AllowCrossAccount
        bool Active
    }

    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
    }

    DriverCredential {
        uuid DriverCredentialId PK
        uuid DriverId
        string Username
        string Password
        bool Active
    }
Loading

The database also holds security.driver_device_registrations (driver mobile device binding) and, in the public schema, the four OpenIddict tables (OpenIddictApplications, OpenIddictAuthorizations, OpenIddictScopes, OpenIddictTokens).

Permission resolution

graph LR
    req["Request:<br/>Resource + Action"]
    up["UserPolicy"]
    rap["ResourceActionPolicy"]
    ur["UserRole"]
    rar["ResourceActionRole"]
    grant["✅ Granted"]
    deny["❌ Denied"]

    req --> up --> rap --> grant
    req --> ur --> rar --> grant
    req -->|Neither path matches| deny
Loading

Access is granted if either the policy path or the role path resolves to a matching Resource + Action pair. See User Permissions Overview.

Views

View Schema Purpose
vw_transporter_position public User-scoped transporter positions, joined through transporter_position → transporters → transporter_group → user_group
vw_geofence public User-scoped geofence access
vw_users per-owner Resolves the acting user's account locally, avoiding a cross-service call per request
vw_visible_transporter per-owner The single source of portal group visibility — app.transporters → app.transporter_group → app.user_group

Group visibility is expressed once, as a view, and reused — a user only ever sees transporters in their assigned groups. Administrator and Manager roles read account-wide.

Multi-tenant isolation

Isolation is enforced in two places, and both matter:

  1. In the request pipeline — AccountScopeBehavior is fail-closed and denies any request that cannot resolve an account. This is the primary control. See Architecture.
  2. In the schema — every tenant-owned table carries an AccountId, with an accountid-leading composite index.
Entity family Isolation column
Users, groups, transporters, devices, operators, drivers AccountId (direct FK)
Documents, alerts, notification rules and deliveries AccountId
Geofences and geofence events AccountId (the event row carries it directly, not only through its geofence)
Trips, stops, deliveries, proofs of delivery, route plans AccountId on every table
Toll catalog (toll_stations, toll_tariffs, toll_vehicle_classes) none — platform reference data by design

Conventions

Timestamps

All timestamps are DateTimeOffset in UTC, end to end. System.DateTime is banned in src and tests — use DateTimeOffset.UtcNow, never DateTime.UtcNow or .Now. The only exception is a third-party API that requires DateTime, which gets an inline value.UtcDateTime at the call site.

  • PostgreSQL columns are always timestamp with time zone. The companion offset interval columns that older versions carried are gone.
  • Every DbContext overrides ConfigureConventions and calls configurationBuilder.UseUtcTimestamps() (Common.Infrastructure).
  • Ingestion converts provider timestamps to UTC at the mapper boundary: epochs are UTC by definition; offset-carrying ISO strings are normalized with ToUniversalTime(); naive timestamps are assumed UTC, never machine-local (beware DateTimeOffset.Parse defaults).
  • Presentation converts to the viewer's timezone at display time only. Daily or bucketed reports must bucket by an account-level IANA timezone id computed at query time, never pre-baked into storage.

Index names

Index names must fit PostgreSQL's 63-character identifier limit. When an auto-generated multi-column index name would exceed it, give it an explicit .HasDatabaseName(...) in the IEntityTypeConfiguration<T>. PostgreSQL silently truncates over-long names to 63 characters while EF applies its own ~-based truncation — the two diverge, so the same schema can end up with different index names depending on how it was applied. Worked examples: Manager's NotificationDeliveryConfiguration and AlertSubscriptionConfiguration.

Pagination

Paged list queries return a …PageVm { Items, TotalCount } envelope, take int? Skip / int? Take, and clamp through PageRequest.Clamp — default 50, maximum 500. Order by a human column then the primary key, so tied values cannot lose or duplicate rows across page boundaries.

Lookup and allocator endpoints (id + display-name projections that must be complete sets) fetch through LookupLimits.FetchSize and pass the result to LookupLimits.EnsureWithinCeiling, which fails loudly rather than silently truncating an over-ceiling set.

Localized text

Localized strings never live in the database. Server-rendered text comes from .resx resources through Common.Domain.Localization.ResourceLocalizer. Account-authored content (notification template overrides, announcement text) is user content, not a system string, and is stored per language. See Manager.

TrackHub Wiki

Home

Platform

Services

Engineering

User docs

Clone this wiki locally