Database Schema
This page documents the database schema used by the Tracker API. The database uses PostgreSQL with PostGIS and TimescaleDB extensions for geospatial and time-series data.
Entity Relationship Diagram
erDiagram
Client ||--o{ Brand : has
Brand ||--o{ ProductionRun : has
ProductionRun ||--o{ Tracker : has
ProductionRun ||--o{ DeliveryLocation : has
ProductionRun ||--o{ StorageLocation : has
Tracker ||--o{ LocationHistory : has
Tracker ||--o{ StatusHistory : has
DeliveryLocation ||--o{ StatusHistory : "referenced in"
StorageLocation ||--o{ StatusHistory : "referenced in"
User ||--o{ UserTrackerNotification : subscribes
Tracker ||--o{ UserTrackerNotification : "subscribed by"
User ||--o{ Notification : receives
Tracker ||--o{ Notification : generates
Tracker ||--o{ LocationProcessingStatus : has
Tables
Users
The users table stores user information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| name | String | User's name |
| String | User's email (unique) | |
| password_hash | String | Hashed password |
| client_list | Integer[] | List of client IDs the user has access to |
| roles | Text[] | User roles (e.g., "admin", "user") |
| notification_preferences | JSONB | User's notification preferences |
Clients
The clients table stores client information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| name | String | Client name |
| logo | String | URL to client logo |
Brands
The brands table stores brand information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| name | String | Brand name |
| logo | String | URL to brand logo |
| client_id | Integer | Foreign key to clients table |
Production Runs
The production_runs table stores production run information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| start_date | DateTime | Production run start date |
| end_date | DateTime | Production run end date (optional) |
| brand_id | Integer | Foreign key to brands table |
| description | String | Production run description |
| image_url | String | URL to production run image |
Trackers
The trackers table stores tracker information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| name | String | Tracker name |
| advertisement_key | String | Advertisement key for BLE |
| private_key | String | Private key for encryption |
| hashed_advertisement_key | String | Hashed advertisement key for lookup |
| last_report_requested | DateTime | Last time a report was requested |
| last_report_received | DateTime | Last time a report was received |
| production_run_id | Integer | Foreign key to production_runs table |
| current_status | Enum | Current tracker status |
| current_state_start | DateTime | When the current status started |
| total_transit_time | Interval | Total time in transit |
| total_storage_time | Interval | Total time in storage |
Location History
The location_history table stores tracker location history.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| timestamp | DateTime | Timestamp of the location report |
| location | Geography(POINT) | Geospatial point |
| nearest_city | String | Nearest city name |
| tracker_id | Integer | Foreign key to trackers table |
| confidence | Integer | Location confidence level |
| horizontal_accuracy | Float | Horizontal accuracy in meters |
Status History
The status_history table stores tracker status history.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| timestamp | DateTime | Timestamp of the status change |
| status | Enum | Tracker status |
| tracker_id | Integer | Foreign key to trackers table |
| storage_location_id | Integer | Foreign key to storage_locations table |
| delivery_location_id | Integer | Foreign key to delivery_locations table |
Storage Locations
The storage_locations table stores storage location information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| name | String | Location name |
| location | Geography(POINT) | Geospatial point |
| production_run_id | Integer | Foreign key to production_runs table |
| geofence_size_meters | Integer | Geofence radius in meters (default: 100m) |
Delivery Locations
The delivery_locations table stores delivery location information.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| name | String | Location name |
| location | Geography(POINT) | Geospatial point |
| production_run_id | Integer | Foreign key to production_runs table |
| geofence_size_meters | Integer | Geofence radius in meters (default: 100m) |
Location Reports
The location_reports table stores raw location reports from trackers.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| hashed_adv_key | String | Hashed advertisement key |
| timestamp | DateTime | Timestamp of the report |
| location | Geography(POINT) | Geospatial point |
| confidence | Integer | Location confidence level |
| horizontal_accuracy | Float | Horizontal accuracy in meters |
| nearest_city | String | Nearest city name |
Location Processing Status
The location_processing_status table tracks which location reports have been processed.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| tracker_id | Integer | Foreign key to trackers table |
| time_bucket | DateTime | Time bucket for processing |
| processed | Boolean | Whether the bucket has been processed |
| processed_at | DateTime | When the bucket was processed |
Notifications
The notifications table stores user notifications.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| user_id | Integer | Foreign key to users table |
| tracker_id | Integer | Foreign key to trackers table |
| message | Text | Notification message |
| status | String | Notification status |
| read | Boolean | Whether the notification has been read |
| created_at | DateTime | When the notification was created |
User Tracker Notifications
The user_tracker_notifications table stores user subscriptions to tracker notifications.
| Column | Type | Description |
|---|---|---|
| id | Integer | Primary key |
| user_id | Integer | Foreign key to users table |
| tracker_id | Integer | Foreign key to trackers table |
| created_at | DateTime | When the subscription was created |
Junction Tables
Production Run Delivery Locations
The production_run_delivery_locations table is a junction table for the many-to-many relationship between production runs and delivery locations.
| Column | Type | Description |
|---|---|---|
| production_run_id | Integer | Foreign key to production_runs table |
| delivery_location_id | Integer | Foreign key to delivery_locations table |
Production Run Storage Locations
The production_run_storage_locations table is a junction table for the many-to-many relationship between production runs and storage locations.
| Column | Type | Description |
|---|---|---|
| production_run_id | Integer | Foreign key to production_runs table |
| storage_location_id | Integer | Foreign key to storage_locations table |