Database Table Field Documentation

This document contains all database tables with their field names, types, and space for descriptions.

Generated: 2025-08-19 17:36:27

Updated: 2025-08-19 19:00:00 (Post-cleanup migration 649a4d1f4161)

alembic_version

Used to track Alembic migrations

Field Name	Data Type	Nullable	Constraints	Description
version_num	VARCHAR(32)	No	PRIMARY KEY	Current migration version

apscheduler_jobs

Use for scheduling service tasks

Field Name	Data Type	Nullable	Constraints	Description
id	VARCHAR(191)	No	PRIMARY KEY	PK
next_run_time	DOUBLE PRECISION	Yes	-	When the job will run next
job_state	BYTEA	No	-	Job State

brands

Used for Brands related to clients

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('brands_id_seq'::re...	PK
name	CHARACTER VARYING	No	-	Brand Name
logo	CHARACTER VARYING	Yes	-	Brand Logo
client_id	INTEGER	Yes	FK → clients.id	Client Relationship

clients

Used to keep a simple client record

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('clients_id_seq'::r...	PK
name	CHARACTER VARYING	No	-	Client Name
logo	CHARACTER VARYING	Yes	-	Client Logo

delivery_locations

Used to identify geofences for delivery locations

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('delivery_locations...	PK
name	CHARACTER VARYING	No	-	Location Name
location	GEOGRAPHY	No	-	Geography location
production_run_id	INTEGER	Yes	FK → production_runs.id	Production Run Relationship
geofence_size_meters	INTEGER	No	DEFAULT: 200	Radius of Geofence in 50m increments

geocoding_cache

Used by the unified geofencing service to geocode locations. Uses rounding to 2 decimals

Field Name	Data Type	Nullable	Constraints	Description
lat_rounded	NUMERIC(9,2)	No	PRIMARY KEY	PK
lon_rounded	NUMERIC(9,2)	No	PRIMARY KEY	PK
nearest_city	CHARACTER VARYING	Yes	-	Nearest City to the rounded geographic coordinate
country	CHARACTER VARYING	Yes	-	Country containing the nearest city¹
state	CHARACTER VARYING	Yes	-	State containing the nearest city¹
postal_code	CHARACTER VARYING	Yes	-	Postal code of the geographic location¹
last_updated	TIMESTAMP	No	DEFAULT: now()	Used for TTL, currently 1 year

geofence_events

Used to track when a tracker device enters or leaves a geofence location

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('geofence_events_id...	PK
tracker_id	INTEGER	No	FK → trackers.id	Relationship
location_report_id	INTEGER	Yes	-	Location report relationship
delivery_location_id	INTEGER	Yes	FK → delivery_locations.id	Relationship
storage_location_id	INTEGER	Yes	FK → storage_locations.id	Relationship
event_type	VARCHAR(20)	No	-	Entry or exit
timestamp	TIMESTAMPTZ	No	-	Timestamp of event
accuracy_radius	DOUBLE PRECISION	Yes	-	Horizontal accuracy from Apple
created_at	TIMESTAMPTZ	No	-	Row created timestamp
confidence	INTEGER	Yes	-	Confidence from Apple (1,2 or 3 where 3 = highest)

images

Used to store links to uploaded images

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('images_id_seq'::re...	PK
original_filename	CHARACTER VARYING	No	-	Original filename
storage_filename	CHARACTER VARYING	No	-	What it gets stored as on server
mime_type	CHARACTER VARYING	No	-	Mime type - Helps filter what we store
size	INTEGER	No	-	Size in bytes
width	INTEGER	No	-	Width in pixels
height	INTEGER	No	-	Height in pixels
url_path	CHARACTER VARYING	No	-	Path to stored image
uploaded_at	TIMESTAMP	No	DEFAULT: now()	Timestamp when it was uploaded

location_reports

These are the raw reports as pulled from Apple using findmy

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY	PK required for aggregation
hashed_adv_key	TEXT	No	-	The only relationship to the tracker table³
timestamp	TIMESTAMPTZ	No	PRIMARY KEY	PK required for aggregation
location	GEOGRAPHY	Yes	-	Geographic location
confidence	INTEGER	Yes	-	Confidence from Apple (1,2 or 3 where 3 = highest)
horizontal_accuracy	DOUBLE PRECISION	Yes	-	Horizontal accuracy in metres from Apple
nearest_city	TEXT	Yes	-	Populated by reverse geocoding (unified geofencing)

processed_notifications

Used by TaskiQ notification service for deduplication and pipeline orchestration. Prevents duplicate notifications from being processed and provides cleanup of old notification records.

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('processed_notifica...	PK
channel	TEXT	No	-	Notification channel identifier
task	TEXT	No	-	Task name that was processed
payload_hash	TEXT	No	-	Hash of payload for deduplication
processed_at	TIMESTAMP	Yes	DEFAULT: now()	When the notification was processed

production_run_delivery_locations

Planned for globally shared delivery locations across production runs, but currently unused (no data, no use case). Junction table exists in models but contains no data.

Field Name	Data Type	Nullable	Constraints	Description
production_run_id	INTEGER	Yes	FK → production_runs.id	Production run that could use this delivery location
delivery_location_id	INTEGER	Yes	FK → delivery_locations.id	Delivery location that could be shared across runs

production_run_storage_locations

Planned for globally shared storage locations across production runs, but currently unused (no data, no use case). Junction table exists in models but contains no data.

Field Name	Data Type	Nullable	Constraints	Description
production_run_id	INTEGER	Yes	FK → production_runs.id	Production run that could use this storage location
storage_location_id	INTEGER	Yes	FK → storage_locations.id	Storage location that could be shared across runs

production_runs

Used to store production run details that are related to Brands

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('production_runs_id...	PK
start_date	TIMESTAMP	No	-	Production run start date²
end_date	TIMESTAMP	Yes	-	Production run end date²
brand_id	INTEGER	Yes	FK → brands.id	Brand Relationship
description	CHARACTER VARYING	Yes	-	Description of Brand
removal_date	TIMESTAMPTZ	Yes	-	This date relates to when the tracker is disposed of
image_url	CHARACTER VARYING	Yes	-

spatial_ref_sys

Used by PostGIS (system table)

Field Name	Data Type	Nullable	Constraints
srid	INTEGER	No	PRIMARY KEY
auth_name	VARCHAR(256)	Yes	-
auth_srid	INTEGER	Yes	-
srtext	VARCHAR(2048)	Yes	-
proj4text	VARCHAR(2048)	Yes	-

status_history

Used to keep track of when a tracker device changes its status, eg. from IN_TRANSIT to IN_STORAGE, etc.

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('statushistory_id...	PK
timestamp	TIMESTAMP	No	PRIMARY KEY	Timestamp of status change
status	TRACKER_STATUS	No	-	Tracker status from Enum
tracker_id	INTEGER	Yes	FK → trackers.id	Relationship to tracker table
storage_location_id	INTEGER	Yes	FK → storage_locations.id	Relationship to storage location
delivery_location_id	INTEGER	Yes	FK → delivery_locations.id	Relationship to delivery location
location_report_id	INTEGER	Yes	-	Relationship to location report

storage_locations

Geofences for storage locations

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('storagelocations...	PK
name	CHARACTER VARYING	No	-	Name of storage location
location	GEOGRAPHY	No	-	Geographical location
production_run_id	INTEGER	Yes	FK → production_runs.id	Relationship to production run
geofence_size_meters	INTEGER	No	DEFAULT: 200	Geofence size in metres

trackers

Main table of all tracker devices containing public, private keys, etc. as written to tracker chip for use by findmy

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('trackers_id_seq'::...	PK
name	CHARACTER VARYING	No	-	Name - usually made from public key
advertisement_key	CHARACTER VARYING	No	-	Advertisement Key
private_key	CHARACTER VARYING	No	-	Private Key
hashed_advertisement_key	CHARACTER VARYING	No	-	Hashed advertisement key³
last_report_requested	TIMESTAMP	Yes	-	Last time we asked Apple for a location report for this device
last_report_received	TIMESTAMP	Yes	-	Last time we got a report from Apple
production_run_id	INTEGER	Yes	FK → production_runs.id	Relationship to production run
current_status	TRACKER_STATUS	Yes	-	CREATED, IN_TRANSIT, IN_STORAGE, DELIVERED
current_state_start	TIMESTAMPTZ	Yes	-	When the current state became active
mac_address	CHARACTER VARYING	Yes	-	Identity of tracker, on the printed QR label
last_position_report	TIMESTAMP	Yes	-	Last time Apple saw the device
last_status_check	TIMESTAMP	Yes	-	Used by geofencing and status services to track when tracker was last processed
last_processed_at	TIMESTAMPTZ	Yes	-	Last time the device was processed by the queue

users

Used for authentication and access control

Field Name	Data Type	Nullable	Constraints	Description
id	INTEGER	No	PRIMARY KEY, DEFAULT: nextval('users_id_seq'::reg...	PK
name	CHARACTER VARYING	No	-	User name
email	CHARACTER VARYING	No	-	Email address
password_hash	CHARACTER VARYING	No	-	Password Hash
client_list	ARRAY	Yes	-	Array of client data the user can access (ACL)
roles	ARRAY	No	DEFAULT: '{}'::text[]	User, Manager or Admin
notification_preferences	JSONB	No	DEFAULT: '{"ui": true, "email": fals...	NOT USED

Database Cleanup Status

COMPLETED: Database cleanup migration 649a4d1f4161 was successfully applied on 2025-08-19 18:55:33.

Removed Elements

The following unused database elements have been successfully removed:

Removed Tables (8 total)

geofence_alerts - Only referenced in migrations, no actual code usage
geofence_analytics - Only referenced in migrations, no actual code usage
geofence_backfill_state - Only referenced in migrations, no actual code usage
tracker_geofence_state - Only referenced in migrations, no actual code usage
location_processing_status - Had model definitions but no service usage
notifications - No service usage found, only model definitions exist
pending_notifications - No service usage found, only model definitions exist
notification_errors - No service usage found, only model definitions exist

Removed Fields (4 total)

trackers.total_transit_time - Defined in models/schemas but never updated by services
trackers.total_storage_time - Defined in models/schemas but never updated by services
trackers.last_location_id - Marked as NOT USED
trackers.last_location_report_id - Marked as NOT USED

Preserved Elements

The following elements were kept as they serve active or planned purposes:

Junction Tables for Planned Features

production_run_delivery_locations - Junction table for planned global location sharing
production_run_storage_locations - Junction table for planned global location sharing

Actively Used Elements

processed_notifications - ACTIVELY USED by TaskiQ notification service
trackers.last_status_check - ACTIVELY USED by geofencing and status services

Cleanup Results

The completed cleanup achieved:

Removed 8 unused tables (significant schema simplification)
Removed 4 unused fields from the trackers table
Preserved all actively used functionality
Reduced database complexity while maintaining system integrity

The database schema is now cleaner and more maintainable, with all unused elements removed and all active functionality preserved.

Not currently used, but valuable data we retried from reverse geocode service. ↩↩↩
Start and end date is critical for establishing if a related tracker should be considered active for processing. ↩↩
Key relationship for use by findmy to relate to reports fetched from Apple. ↩↩