Skip to main content

Database Schema

This document provides a comprehensive overview of the database schema used in the Tracker GraphQL application, including tables, relationships, and indexes.

Overview​

The Tracker GraphQL application uses PostgreSQL with TimescaleDB and PostGIS extensions to store and manage data. The database schema is designed to efficiently handle:

  • Tracker devices and their location history
  • Client and brand information
  • Production runs
  • Storage and delivery locations
  • User management and authentication
  • Geospatial data and geocoding
  • Time-series data for location tracking

Entity-Relationship Diagram​

The following diagram illustrates the relationships between the main tables in the database:

Tables​

Base​

The Base class is the foundation for all models in the application, using SQLAlchemy's declarative base.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

The application automatically creates the TimescaleDB extension when the database is initialized:

create_timescaledb_extension = DDL("CREATE EXTENSION IF NOT EXISTS timescaledb;")
event.listen(
    Base.metadata,
    "after_create",
    create_timescaledb_extension.execute_if(dialect="postgresql"),
)

Client​

Table Name: clients

Description: Represents organizations that use the tracking system.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • name (String, Not Null): Client name
  • logo (String): URL to client logo image

Relationships:

  • One-to-many with Brand: A client can have multiple brands

Brand​

Table Name: brands

Description: Represents brands owned by clients.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • name (String, Not Null): Brand name
  • logo (String): URL to brand logo image
  • client_id (Integer, Foreign Key): Reference to the client that owns this brand

Relationships:

  • Many-to-one with Client: A brand belongs to one client
  • One-to-many with ProductionRun: A brand can have multiple production runs

Indexes:

  • idx_brands_client_id: Improves query performance when filtering brands by client

ProductionRun​

Table Name: production_runs

Description: Represents a production batch that is tracked through the system.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • start_date (DateTime, Not Null): When the production run started
  • end_date (DateTime): When the production run ended (can be null if ongoing)
  • brand_id (Integer, Foreign Key): Reference to the brand associated with this production run
  • description (String): Optional description of the production run
  • image_url (String): Optional URL to an image representing the production run

Relationships:

  • Many-to-one with Brand: A production run belongs to one brand
  • One-to-many with Tracker: A production run can have multiple trackers
  • Many-to-many with DeliveryLocation: Through junction table production_run_delivery_locations
  • Many-to-many with StorageLocation: Through junction table production_run_storage_locations
  • One-to-many with DeliveryLocation (direct): A production run can have multiple delivery locations
  • One-to-many with StorageLocation (direct): A production run can have multiple storage locations

Indexes:

  • idx_production_runs_brand_id: Improves query performance when filtering production runs by brand
  • idx_production_runs_start_date: Optimizes queries that filter or sort by start date
  • idx_production_runs_end_date: Optimizes queries that filter or sort by end date

Tracker​

Table Name: trackers

Description: Represents physical tracker devices that report location data.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • name (String, Not Null): Tracker name/identifier
  • advertisement_key (String, Not Null): Public key used for advertising
  • private_key (String, Not Null): Private key for secure communication
  • hashed_advertisement_key (String, Not Null): Hashed version of the advertisement key
  • last_report_requested (DateTime): When a location report was last requested
  • last_report_received (DateTime): When a location report was last received
  • production_run_id (Integer, Foreign Key): Reference to the production run this tracker is part of
  • current_status (Enum): Current status of the tracker (CREATED, IN_TRANSIT, IN_STORAGE, DELIVERED)
  • current_state_start (DateTime): When the current status began
  • total_transit_time (Interval): Accumulated time spent in transit
  • total_storage_time (Interval): Accumulated time spent in storage

Relationships:

  • Many-to-one with ProductionRun: A tracker belongs to one production run
  • One-to-many with LocationHistory: A tracker has multiple location history records
  • One-to-many with StatusHistory: A tracker has multiple status history records
  • One-to-many with LocationProcessingStatus: A tracker has multiple location processing status records
  • Many-to-many with User: Through UserTrackerNotification for subscriptions
  • One-to-many with Notification: A tracker can generate multiple notifications

Indexes:

  • idx_trackers_production_run_current_status: Composite index for filtering trackers by production run and status
  • idx_trackers_last_report_received: Optimizes queries that filter or sort by last report time
  • idx_trackers_id: Index on the primary key for faster joins
  • Index on name: Improves performance when searching by tracker name

LocationHistory​

Table Name: location_history

Description: Stores the location history of trackers. This is implemented as a materialized view in TimescaleDB.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • timestamp (DateTime, Not Null): When the location was recorded
  • location (Geometry('POINT', 4326), Not Null): Geographic coordinates stored as PostGIS point
  • nearest_city (String): Name of the nearest city (populated by geocoding)
  • tracker_id (Integer, Foreign Key, Not Null): Reference to the tracker
  • confidence (Integer): Confidence level of the location data

Relationships:

  • Many-to-one with Tracker: A location history record belongs to one tracker

Indexes:

  • idx_location_history_tracker_timestamp: Composite index for efficiently querying location history by tracker and time
  • idx_location_history_nearest_city: Improves performance when filtering by nearest city

Methods:

  • get_coordinates(session): Extracts latitude and longitude from the PostGIS geometry

LocationReport​

Table Name: location_reports

Description: Raw location data collected from trackers before processing.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • hashed_adv_key (String, Not Null): Hashed advertisement key of the tracker
  • timestamp (DateTime, Not Null): When the location was reported
  • location (Geometry('POINT', 4326)): Geographic coordinates stored as PostGIS point
  • confidence (Integer): Confidence level of the location data
  • nearest_city (String): Name of the nearest city (populated by geocoding)

Indexes:

  • idx_location_report_location: Spatial index for geographic queries
  • idx_location_report_timestamp: Improves performance when filtering by time
  • idx_location_report_hashed_adv_key: Optimizes lookups by hashed advertisement key
  • uq_hashed_adv_key_timestamp: Unique constraint to prevent duplicate reports

Methods:

  • get_coordinates(session): Extracts latitude and longitude from the PostGIS geometry

LocationProcessingStatus​

Table Name: location_processing_status

Description: Tracks which location history rows have been processed by the geofence processing system.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • tracker_id (Integer, Foreign Key, Not Null): Reference to the tracker
  • time_bucket (DateTime, Not Null): Time bucket for processing
  • processed (Boolean): Whether this time bucket has been processed
  • processed_at (DateTime): When the processing occurred

Relationships:

  • Many-to-one with Tracker: A processing status record belongs to one tracker

Indexes:

  • idx_location_processing_processed: Improves performance when filtering by processed status
  • idx_location_processing_tracker_id: Optimizes lookups by tracker
  • uq_tracker_time_bucket: Unique constraint on tracker_id and time_bucket

StorageLocation​

Table Name: storage_locations

Description: Represents locations where trackers can be stored.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • name (String, Not Null): Location name
  • location (Geometry('POINT'), Not Null): Geographic coordinates stored as PostGIS point
  • production_run_id (Integer, Foreign Key): Reference to the production run this storage location is associated with

Relationships:

  • One-to-many with StatusHistory: A storage location can be referenced in multiple status history records
  • Many-to-one with ProductionRun: A storage location can be associated with a production run

Indexes:

  • idx_storage_locations_geom: Spatial index for geographic queries

Methods:

  • get_coordinates(session): Extracts latitude and longitude from the PostGIS geometry

DeliveryLocation​

Table Name: delivery_locations

Description: Represents locations where trackers can be delivered.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • name (String, Not Null): Location name
  • location (Geometry('POINT'), Not Null): Geographic coordinates stored as PostGIS point
  • production_run_id (Integer, Foreign Key): Reference to the production run this delivery location is associated with

Relationships:

  • One-to-many with StatusHistory: A delivery location can be referenced in multiple status history records
  • Many-to-one with ProductionRun: A delivery location can be associated with a production run

Indexes:

  • idx_delivery_locations_geom: Spatial index for geographic queries

Methods:

  • get_coordinates(session): Extracts latitude and longitude from the PostGIS geometry

StatusHistory​

Table Name: status_history

Description: Tracks changes in tracker status over time.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • timestamp (DateTime, Not Null): When the status change occurred
  • status (Enum, Not Null): Status value (CREATED, IN_TRANSIT, IN_STORAGE, DELIVERED)
  • tracker_id (Integer, Foreign Key): Reference to the tracker
  • storage_location_id (Integer, Foreign Key): Reference to a storage location (if status is IN_STORAGE)
  • delivery_location_id (Integer, Foreign Key): Reference to a delivery location (if status is DELIVERED)

Relationships:

  • Many-to-one with Tracker: A status history record belongs to one tracker
  • Many-to-one with StorageLocation: A status history record can reference a storage location
  • Many-to-one with DeliveryLocation: A status history record can reference a delivery location

Indexes:

  • idx_status_history_tracker_timestamp: Composite index for efficiently querying status history by tracker and time
  • idx_status_history_status: Improves performance when filtering by status

User​

Table Name: users

Description: Represents users of the system.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • name (String, Not Null): User's name
  • email (String, Not Null, Unique): User's email address
  • password_hash (String, Not Null): Hashed password
  • client_list (Array of Integer): List of client IDs the user has access to
  • notification_preferences (JSONB): User's notification preferences

Indexes:

  • idx_users_email: Improves performance when looking up users by email

Methods:

  • set_password(password): Sets the password hash
  • check_password(password): Verifies a password against the stored hash

UserTrackerNotification​

Table Name: user_tracker_notifications

Description: Represents subscriptions of users to tracker notifications.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • user_id (Integer, Foreign Key, Not Null): Reference to the user
  • tracker_id (Integer, Foreign Key, Not Null): Reference to the tracker
  • created_at (DateTime): When the subscription was created

Relationships:

  • Many-to-one with User: A subscription belongs to one user
  • Many-to-one with Tracker: A subscription is for one tracker

Indexes:

  • idx_user_tracker_notifications_user_id: Improves performance when looking up subscriptions by user
  • idx_user_tracker_notifications_tracker_id: Improves performance when looking up subscriptions by tracker

Notification​

Table Name: notifications

Description: Stores notifications sent to users.

Columns:

  • id (Integer, Primary Key): Unique identifier
  • user_id (Integer, Foreign Key, Not Null): Reference to the user
  • tracker_id (Integer, Foreign Key, Not Null): Reference to the tracker
  • message (Text, Not Null): Notification message
  • status (String, Not Null): Status of the notification
  • read (Boolean): Whether the notification has been read
  • created_at (DateTime): When the notification was created

Relationships:

  • Many-to-one with User: A notification belongs to one user
  • Many-to-one with Tracker: A notification is about one tracker

Indexes:

  • idx_notifications_user_id: Improves performance when looking up notifications by user
  • idx_notifications_user_read: Composite index for efficiently querying unread notifications for a user

Association Tables​

production_run_delivery_locations​

Description: Junction table for the many-to-many relationship between production runs and delivery locations.

Columns:

  • production_run_id (Integer, Foreign Key): Reference to a production run
  • delivery_location_id (Integer, Foreign Key): Reference to a delivery location

production_run_storage_locations​

Description: Junction table for the many-to-many relationship between production runs and storage locations.

Columns:

  • production_run_id (Integer, Foreign Key): Reference to a production run
  • storage_location_id (Integer, Foreign Key): Reference to a storage location

TimescaleDB Integration​

The application leverages TimescaleDB for efficient time-series data management:

  1. The location_history table is implemented as a materialized view that combines data from continuous aggregates
  2. Raw location data is stored in the location_reports table
  3. Continuous aggregates provide efficient access to historical data at different time resolutions
  4. Automatic data retention policies ensure efficient storage usage

For more details on the TimescaleDB integration, see the TimescaleDB Integration document.

Spatial Data with PostGIS​

The application uses PostGIS for spatial data management:

  1. Location data is stored as PostGIS POINT geometries
  2. Spatial indexes (GiST) are used for efficient geographic queries
  3. PostGIS functions are used for spatial operations like distance calculations
  4. The get_coordinates methods extract latitude and longitude from PostGIS geometries

Indexes and Performance Optimization​

The database schema includes various indexes to optimize query performance:

  1. Primary Key Indexes: Automatically created for all primary key columns
  2. Foreign Key Indexes: Created for foreign key columns to improve join performance
  3. Composite Indexes: Used for frequently combined filter conditions (e.g., tracker_id + timestamp)
  4. Spatial Indexes: GiST indexes for PostGIS geometry columns
  5. Unique Constraints: Enforce data integrity and improve lookup performance

Geofence Processing​

The application includes a geofence processing system that:

  1. Monitors location history data
  2. Detects when trackers enter or exit defined geographic areas (storage and delivery locations)
  3. Updates tracker status based on geofence events
  4. Generates notifications for status changes

The location_processing_status table tracks which location data has been processed to avoid duplicate processing.