Database Schema

This document describes the database schema for the tracker REST API system, including tables, relationships, TimescaleDB features, and PostGIS spatial capabilities.

Overview

The tracker system uses PostgreSQL with TimescaleDB and PostGIS extensions for comprehensive location tracking and geofencing. The schema is designed to efficiently handle:

• User management with role-based access control
• Apple FindMy tracker device integration
• Time-series location data with continuous aggregation
• PostGIS-based geofencing for storage and delivery locations
• Brand and production run organization
• Real-time notification processing with deduplication

Extensions and Types

Database Extensions

-- TimescaleDB for time-series data management
CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA public;

-- PostGIS for spatial data types and functions
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;

Custom Types

-- Tracker status enumeration
CREATE TYPE public.tracker_status AS ENUM (
    'CREATED',
    'IN_TRANSIT',
    'IN_STORAGE',
    'DELIVERED'
);

Core Tables

Users Table

CREATE TABLE public.users (
    id integer NOT NULL,
    name character varying NOT NULL,
    email character varying NOT NULL,
    password_hash character varying NOT NULL,
    client_list integer[],
    roles text[] DEFAULT '{}'::text[] NOT NULL,
    notification_preferences jsonb DEFAULT '{"ui": true, "email": false}'::jsonb NOT NULL
);

-- Indexes
CREATE UNIQUE INDEX idx_users_email ON public.users USING btree (email);

Purpose: Stores user authentication and authorization information.

Key Features:

• Array-based client access control via client_list
• Multiple role support via roles array
• JSONB notification preferences
• Unique email constraint

Brands Table

CREATE TABLE public.brands (
    id integer NOT NULL,
    name character varying NOT NULL,
    logo character varying,
    client_id integer
);

-- Indexes
CREATE INDEX idx_brands_client_id ON public.brands USING btree (client_id);

-- Foreign Keys
ALTER TABLE ONLY public.brands
    ADD CONSTRAINT brands_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.clients(id);

Purpose: Manages brand information linked to clients.

Clients Table

CREATE TABLE public.clients (
    id integer NOT NULL,
    name character varying NOT NULL,
    logo character varying
);

Purpose: Simple client management for organizational hierarchy.

Production Runs Table

CREATE TABLE public.production_runs (
    id integer NOT NULL,
    start_date timestamp without time zone NOT NULL,
    end_date timestamp without time zone,
    brand_id integer,
    description character varying,
    removal_date timestamp with time zone,
    image_url character varying
);

-- Indexes
CREATE INDEX idx_production_runs_brand_id ON public.production_runs USING btree (brand_id);
CREATE INDEX idx_production_runs_start_date ON public.production_runs USING btree (start_date);
CREATE INDEX idx_production_runs_end_date ON public.production_runs USING btree (end_date);

-- Foreign Keys
ALTER TABLE ONLY public.production_runs
    ADD CONSTRAINT production_runs_brand_id_fkey FOREIGN KEY (brand_id) REFERENCES public.brands(id);

Purpose: Manages production runs with lifecycle dates for tracker batches.

Key Features:

• Start/end date tracking for active production runs
• Removal date for tracker disposal scheduling
• Brand association for organizational structure

Tracker Management

Trackers Table

CREATE TABLE public.trackers (
    id integer NOT NULL,
    name character varying NOT NULL,
    advertisement_key character varying NOT NULL,
    private_key character varying NOT NULL,
    hashed_advertisement_key character varying NOT NULL,
    last_report_requested timestamp without time zone,
    last_report_received timestamp without time zone,
    production_run_id integer,
    current_status public.tracker_status,
    current_state_start timestamp with time zone,
    mac_address character varying,
    last_position_report timestamp without time zone,
    last_status_check timestamp without time zone,
    last_processed_at timestamp with time zone
);

-- Indexes
CREATE INDEX idx_trackers_name ON public.trackers USING btree (name);
CREATE INDEX idx_trackers_mac_address ON public.trackers USING btree (mac_address);
CREATE INDEX idx_trackers_production_run_id ON public.trackers USING btree (production_run_id);
CREATE INDEX idx_trackers_production_run_current_status ON public.trackers USING btree (production_run_id, current_status);
CREATE INDEX idx_trackers_last_report_received ON public.trackers USING btree (last_report_received);
CREATE INDEX idx_trackers_last_position_report ON public.trackers USING btree (last_position_report);
CREATE INDEX idx_trackers_last_status_check ON public.trackers USING btree (last_status_check);
CREATE INDEX ix_trackers_last_processed_at ON public.trackers USING btree (last_processed_at);

-- Foreign Keys
ALTER TABLE ONLY public.trackers
    ADD CONSTRAINT trackers_production_run_id_fkey FOREIGN KEY (production_run_id) REFERENCES public.production_runs(id);

Purpose: Core tracker device management with Apple FindMy integration.

Key Features:

• Apple FindMy cryptographic keys (advertisement_key, private_key, hashed_advertisement_key)
• Status tracking with timestamps (current_status, current_state_start)
• Service processing timestamps for queue management
• MAC address for physical device identification

Status History Table

CREATE TABLE public.status_history (
    id integer NOT NULL,
    timestamp timestamp without time zone NOT NULL,
    status public.tracker_status NOT NULL,
    tracker_id integer,
    storage_location_id integer,
    delivery_location_id integer,
    location_report_id integer
);

-- Indexes
CREATE INDEX idx_status_history_tracker_timestamp ON public.status_history USING btree (tracker_id, timestamp);
CREATE INDEX idx_status_history_status ON public.status_history USING btree (status);
CREATE INDEX idx_status_history_location_report_id ON public.status_history USING btree (location_report_id);

-- Foreign Keys
ALTER TABLE ONLY public.status_history
    ADD CONSTRAINT status_history_tracker_id_fkey FOREIGN KEY (tracker_id) REFERENCES public.trackers(id);
ALTER TABLE ONLY public.status_history
    ADD CONSTRAINT status_history_storage_location_id_fkey FOREIGN KEY (storage_location_id) REFERENCES public.storage_locations(id);
ALTER TABLE ONLY public.status_history
    ADD CONSTRAINT status_history_delivery_location_id_fkey FOREIGN KEY (delivery_location_id) REFERENCES public.delivery_locations(id);

Purpose: Tracks all tracker status changes with location context.

Time-Series Location Data

Location Reports Table (Hypertable)

CREATE TABLE public.location_reports (
    id integer NOT NULL,
    hashed_adv_key text NOT NULL,
    timestamp timestamp with time zone NOT NULL,
    location public.geography(Point,4326),
    confidence integer,
    horizontal_accuracy double precision,
    nearest_city text
);

-- Convert to TimescaleDB hypertable
SELECT create_hypertable('location_reports', 'timestamp', chunk_time_interval => INTERVAL '1 week');

-- Indexes
CREATE INDEX idx_location_report_hashed_adv_key ON public.location_reports USING btree (hashed_adv_key);
CREATE INDEX idx_location_report_location ON public.location_reports USING gist (location);
CREATE INDEX idx_location_report_timestamp ON public.location_reports USING btree (timestamp);
CREATE INDEX idx_location_reports_confidence_accuracy ON public.location_reports USING btree (confidence, horizontal_accuracy) WHERE ((confidence >= 2) AND (horizontal_accuracy IS NOT NULL));
CREATE INDEX idx_location_reports_timestamp_confidence ON public.location_reports USING btree (timestamp, confidence) WHERE (confidence >= 2);
CREATE UNIQUE INDEX uq_hashed_adv_key_timestamp ON public.location_reports USING btree (hashed_adv_key, timestamp);

Purpose: Stores raw location reports from Apple FindMy service.

TimescaleDB Features:

• Hypertable partitioned by timestamp with 1-week chunks
• Automatic continuous aggregate generation
• Optimized for time-series queries

Key Features:

• PostGIS geography type for accurate distance calculations
• Apple FindMy confidence levels (1-3, where 3 is highest)
• Horizontal accuracy in meters
• Reverse geocoded nearest city information

TimescaleDB Continuous Aggregates

Hourly Location Aggregation

CREATE VIEW public.location_history_hourly AS
SELECT
    bucket,
    tracker_id,
    mac_address,
    location,
    latest_timestamp,
    nearest_city,
    confidence,
    horizontal_accuracy
FROM _timescaledb_internal._materialized_hypertable_34;

Purpose: Hourly aggregated location data with China manufacturing location filtering.

Key Features:

• Uses most recent location per hour (not averaging to prevent impossible locations)
• Excludes China manufacturing locations (coordinates 73.6-134.8°E, 18.2-53.5°N)
• Includes MAC address for device identification
• Maintains confidence and accuracy metrics

Daily Location Aggregation

CREATE VIEW public.location_history_daily AS
SELECT
    day,
    tracker_id,
    mac_address,
    location,
    latest_timestamp,
    nearest_city,
    confidence,
    horizontal_accuracy
FROM _timescaledb_internal._materialized_hypertable_35;

Purpose: Daily aggregated location data for historical analysis.

Unified Location History View

CREATE VIEW public.location_history_view AS
-- Recent data (last 24 hours) from hourly aggregates
SELECT
    row_number() OVER (ORDER BY bucket, tracker_id) AS id,
    bucket AS timestamp,
    location,
    nearest_city,
    tracker_id,
    mac_address,
    confidence,
    horizontal_accuracy
FROM public.location_history_hourly
WHERE bucket > (now() - '24:00:00'::interval)

UNION ALL

-- Historical data (older than 24 hours, up to 1 year) from daily aggregates
SELECT
    (row_number() OVER (ORDER BY day, tracker_id) + 1000000) AS id,
    latest_timestamp AS timestamp,
    location,
    nearest_city,
    tracker_id,
    mac_address,
    confidence,
    horizontal_accuracy
FROM public.location_history_daily
WHERE day <= (now() - '24:00:00'::interval)
AND day > (now() - '1 year'::interval);

Purpose: Provides unified access to both recent and historical location data.

Materialized Location History

CREATE MATERIALIZED VIEW public.location_history AS
SELECT
    id,
    timestamp,
    location,
    nearest_city,
    tracker_id,
    mac_address,
    confidence,
    horizontal_accuracy
FROM public.location_history_view
WITH NO DATA;

-- Indexes
CREATE UNIQUE INDEX location_history_id_idx ON public.location_history USING btree (id);
CREATE UNIQUE INDEX idx_location_history_unique_tracker_timestamp ON public.location_history USING btree (tracker_id, timestamp);

Purpose: Materialized view for fast access to location history with manual refresh control.

Geofencing System

Storage Locations Table

CREATE TABLE public.storage_locations (
    id integer NOT NULL,
    name character varying NOT NULL,
    location public.geography(Point,4326) NOT NULL,
    production_run_id integer,
    geofence_size_meters integer DEFAULT 200 NOT NULL,
    CONSTRAINT ck_storage_locations_geofence_size CHECK (((geofence_size_meters >= 100) AND (geofence_size_meters <= 1000) AND ((geofence_size_meters % 50) = 0)))
);

-- Indexes
CREATE INDEX idx_storage_locations_geom ON public.storage_locations USING gist (location);
CREATE INDEX idx_storage_locations_location ON public.storage_locations USING gist (location);

-- Foreign Keys
ALTER TABLE ONLY public.storage_locations
    ADD CONSTRAINT storage_locations_production_run_id_fkey FOREIGN KEY (production_run_id) REFERENCES public.production_runs(id);

Purpose: Defines storage location geofences with configurable radius.

Key Features:

• PostGIS geography for accurate distance calculations
• Configurable geofence radius (100-1000m in 50m increments)
• Production run association for lifecycle management

Delivery Locations Table

CREATE TABLE public.delivery_locations (
    id integer NOT NULL,
    name character varying NOT NULL,
    location public.geography(Point,4326) NOT NULL,
    production_run_id integer,
    geofence_size_meters integer DEFAULT 200 NOT NULL,
    CONSTRAINT ck_delivery_locations_geofence_size CHECK (((geofence_size_meters >= 100) AND (geofence_size_meters <= 1000) AND ((geofence_size_meters % 50) = 0)))
);

-- Indexes
CREATE INDEX idx_delivery_locations_geom ON public.delivery_locations USING gist (location);
CREATE INDEX idx_delivery_locations_location ON public.delivery_locations USING gist (location);

-- Foreign Keys
ALTER TABLE ONLY public.delivery_locations
    ADD CONSTRAINT delivery_locations_production_run_id_fkey FOREIGN KEY (production_run_id) REFERENCES public.production_runs(id);

Purpose: Defines delivery location geofences with configurable radius.

Geofence Events Table

CREATE TABLE public.geofence_events (
    id integer NOT NULL,
    tracker_id integer NOT NULL,
    location_report_id integer,
    delivery_location_id integer,
    storage_location_id integer,
    event_type character varying(20) NOT NULL,
    timestamp timestamp with time zone NOT NULL,
    accuracy_radius double precision,
    created_at timestamp with time zone NOT NULL,
    confidence integer
);

-- Indexes
CREATE INDEX idx_geofence_events_tracker_id ON public.geofence_events USING btree (tracker_id);
CREATE INDEX idx_geofence_events_timestamp ON public.geofence_events USING btree (timestamp);
CREATE INDEX idx_geofence_events_tracker_timestamp ON public.geofence_events USING btree (tracker_id, timestamp);
CREATE INDEX idx_geofence_events_type ON public.geofence_events USING btree (event_type);
CREATE INDEX idx_geofence_events_delivery_location ON public.geofence_events USING btree (delivery_location_id);
CREATE INDEX idx_geofence_events_storage_location ON public.geofence_events USING btree (storage_location_id);
CREATE INDEX idx_geofence_events_duplicate_check ON public.geofence_events USING btree (tracker_id, timestamp, delivery_location_id, storage_location_id);

-- Foreign Keys
ALTER TABLE ONLY public.geofence_events
    ADD CONSTRAINT geofence_events_tracker_id_fkey FOREIGN KEY (tracker_id) REFERENCES public.trackers(id) ON DELETE CASCADE;
ALTER TABLE ONLY public.geofence_events
    ADD CONSTRAINT geofence_events_delivery_location_id_fkey FOREIGN KEY (delivery_location_id) REFERENCES public.delivery_locations(id) ON DELETE SET NULL;
ALTER TABLE ONLY public.geofence_events
    ADD CONSTRAINT geofence_events_storage_location_id_fkey FOREIGN KEY (storage_location_id) REFERENCES public.storage_locations(id) ON DELETE SET NULL;

Purpose: Records all geofence entry and exit events for tracking and analytics.

Key Features:

• Event type tracking (entry/exit)
• Links to both storage and delivery locations
• Confidence and accuracy metrics from Apple FindMy
• Duplicate prevention indexing

Junction Tables

Production Run Storage Locations

CREATE TABLE public.production_run_storage_locations (
    production_run_id integer,
    storage_location_id integer
);

-- Foreign Keys
ALTER TABLE ONLY public.production_run_storage_locations
    ADD CONSTRAINT production_run_storage_locations_production_run_id_fkey FOREIGN KEY (production_run_id) REFERENCES public.production_runs(id);
ALTER TABLE ONLY public.production_run_storage_locations
    ADD CONSTRAINT production_run_storage_locations_storage_location_id_fkey FOREIGN KEY (storage_location_id) REFERENCES public.storage_locations(id);

Production Run Delivery Locations

CREATE TABLE public.production_run_delivery_locations (
    production_run_id integer,
    delivery_location_id integer
);

-- Foreign Keys
ALTER TABLE ONLY public.production_run_delivery_locations
    ADD CONSTRAINT production_run_delivery_locations_production_run_id_fkey FOREIGN KEY (production_run_id) REFERENCES public.production_runs(id);
ALTER TABLE ONLY public.production_run_delivery_locations
    ADD CONSTRAINT production_run_delivery_locations_delivery_location_id_fkey FOREIGN KEY (delivery_location_id) REFERENCES public.delivery_locations(id);

Purpose: Many-to-many relationships for shared locations across production runs.

Supporting Tables

Geocoding Cache Table

CREATE TABLE public.geocoding_cache (
    lat_rounded numeric(9,2) NOT NULL,
    lon_rounded numeric(9,2) NOT NULL,
    nearest_city character varying,
    country character varying,
    state character varying,
    postal_code character varying,
    last_updated timestamp without time zone DEFAULT now() NOT NULL
);

-- Indexes
CREATE INDEX idx_geocoding_cache_coords ON public.geocoding_cache USING btree (lat_rounded, lon_rounded);

Purpose: Caches reverse geocoding results to reduce API calls.

Key Features:

• Coordinates rounded to 2 decimal places for efficient caching
• 1-year TTL via last_updated timestamp
• Comprehensive location data (city, country, state, postal code)

Images Table

CREATE TABLE public.images (
    id integer NOT NULL,
    original_filename character varying NOT NULL,
    storage_filename character varying NOT NULL,
    mime_type character varying NOT NULL,
    size integer NOT NULL,
    width integer NOT NULL,
    height integer NOT NULL,
    url_path character varying NOT NULL,
    uploaded_at timestamp without time zone DEFAULT now() NOT NULL
);

-- Indexes
CREATE UNIQUE INDEX idx_images_storage_filename ON public.images USING btree (storage_filename);
CREATE INDEX idx_images_uploaded_at ON public.images USING btree (uploaded_at);

Purpose: Manages uploaded image metadata and storage paths.

Processed Notifications Table

CREATE TABLE public.processed_notifications (
    id integer NOT NULL,
    channel text NOT NULL,
    task text NOT NULL,
    payload_hash text NOT NULL,
    processed_at timestamp without time zone DEFAULT now()
);

-- Indexes
CREATE INDEX idx_processed_notifications_lookup ON public.processed_notifications USING btree (channel, task, payload_hash);
CREATE UNIQUE INDEX processed_notifications_channel_task_payload_hash_key ON public.processed_notifications USING btree (channel, task, payload_hash);

Purpose: Prevents duplicate notification processing in TaskiQ pipeline.

APScheduler Jobs Table

CREATE TABLE public.apscheduler_jobs (
    id character varying(191) NOT NULL,
    next_run_time double precision,
    job_state bytea NOT NULL
);

-- Indexes
CREATE INDEX ix_apscheduler_jobs_next_run_time ON public.apscheduler_jobs USING btree (next_run_time);

Purpose: Manages scheduled background jobs and tasks.

System Tables

Alembic Version

CREATE TABLE public.alembic_version (
    version_num character varying(32) NOT NULL
);

Purpose: Tracks database migration version for Alembic.

PostGIS Spatial Reference Systems

-- System table created by PostGIS extension
-- Contains spatial reference system definitions

Purpose: PostGIS system table for coordinate reference systems.

Database Functions

Geofence Matching Function

CREATE FUNCTION public.match_geofences() RETURNS void
    LANGUAGE plpgsql

Purpose: Processes location history to detect geofence events and update tracker status.

Key Features:

• 12-hour threshold for status changes
• Confidence filtering (≥2 for medium to high confidence)
• Storage location priority over delivery locations
• Automatic status history creation

Notification Functions

CREATE FUNCTION public.notify_celery_task_persistent(channel_name text, task_name text, payload jsonb DEFAULT '{}'::jsonb) RETURNS void
    LANGUAGE plpgsql

Purpose: Sends persistent notifications to TaskiQ with error handling.

Location Report Triggers

CREATE FUNCTION public.on_location_report_insert() RETURNS trigger
    LANGUAGE plpgsql

CREATE FUNCTION public.on_hourly_aggregate_change() RETURNS trigger
    LANGUAGE plpgsql

Purpose: Triggers geocoding batch processing when location data is inserted or aggregated.

Performance Optimization

Query Patterns

1. Time-based Location Queries: Always include timestamp constraints for optimal chunk elimination
2. Spatial Queries: Use PostGIS geography functions with GIST indexes for distance calculations
3. Tracker Filtering: Use tracker_id or hashed_adv_key for efficient data access
4. Geofence Detection: Leverage spatial indexes for ST_DWithin operations

Index Strategy

• Spatial Indexes: GIST indexes on all geography columns
• Time-series Indexes: Composite indexes on (tracker_id, timestamp) patterns
• Unique Constraints: Prevent duplicate location reports and notifications
• Partial Indexes: Confidence-based filtering for high-quality data

TimescaleDB Monitoring

-- Check hypertable chunk information
SELECT * FROM timescaledb_information.chunks
WHERE hypertable_name = 'location_reports';

-- Monitor continuous aggregate refresh
SELECT * FROM timescaledb_information.continuous_aggregates;

-- Check compression status
SELECT * FROM timescaledb_information.compression_settings;

Schema Migration

The schema is managed using Alembic migrations with TimescaleDB and PostGIS support:

1. Extension Management: Extensions are created before table definitions
2. Hypertable Conversion: Tables are created first, then converted to hypertables
3. Spatial Index Creation: PostGIS indexes are created after table population
4. Continuous Aggregate Setup: Aggregates are created after sufficient data accumulation

This schema provides a robust foundation for real-time location tracking, geofencing, and analytics with enterprise-grade performance and reliability.

Quick Reference

Common Query Patterns

Get tracker locations (last 7 days)

SELECT
    lr.timestamp,
    ST_X(lr.location::geometry) as longitude,
    ST_Y(lr.location::geometry) as latitude,
    lr.nearest_city,
    lr.confidence
FROM location_reports lr
JOIN trackers t ON t.hashed_advertisement_key = lr.hashed_adv_key
WHERE t.id = 123
  AND lr.timestamp > NOW() - INTERVAL '7 days'
  AND lr.confidence >= 2
ORDER BY lr.timestamp DESC;

Find trackers within 1km of a location

SELECT DISTINCT t.id, t.name
FROM trackers t
JOIN location_reports lr ON t.hashed_advertisement_key = lr.hashed_adv_key
WHERE ST_DWithin(
    lr.location,
    ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326),
    1000
)
AND lr.timestamp > NOW() - INTERVAL '1 day'
AND lr.confidence >= 2;

Get production run summary

SELECT
    pr.description,
    b.name as brand_name,
    c.name as client_name,
    COUNT(t.id) as total_trackers,
    COUNT(CASE WHEN t.current_status = 'DELIVERED' THEN 1 END) as delivered_count
FROM production_runs pr
JOIN brands b ON pr.brand_id = b.id
JOIN clients c ON b.client_id = c.id
LEFT JOIN trackers t ON pr.id = t.production_run_id
WHERE pr.id = 1
GROUP BY pr.id, pr.description, b.name, c.name;

Check geofence events for a tracker

SELECT
    ge.timestamp,
    ge.event_type,
    COALESCE(sl.name, dl.name) as location_name,
    ge.accuracy_radius
FROM geofence_events ge
LEFT JOIN storage_locations sl ON ge.storage_location_id = sl.id
LEFT JOIN delivery_locations dl ON ge.delivery_location_id = dl.id
WHERE ge.tracker_id = 123
ORDER BY ge.timestamp DESC
LIMIT 10;

PostGIS Geography vs Geometry

Always use geography for distance calculations:

-- Correct: Use geography for accurate distances
ST_DWithin(location1, location2, 200)  -- 200 meters

-- For coordinate extraction, cast to geometry:
ST_X(location::geometry)  -- longitude
ST_Y(location::geometry)  -- latitude

TimescaleDB Usage

Use appropriate views for time ranges:

• Recent data (last 24h): Use location_history_hourly
• Historical data: Use location_history_daily
• Unified access: Use location_history materialized view

Refresh materialized views:

REFRESH MATERIALIZED VIEW location_history;

Key Relationships

clients → brands → production_runs → trackers
trackers ← location_reports (via hashed_advertisement_key)
storage_locations/delivery_locations → geofence_events → trackers

Status Enum Values

• CREATED - Initial tracker state
• IN_TRANSIT - Currently in transit
• IN_STORAGE - At storage location
• DELIVERED - Final delivery location