Skip to content

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: 2026-07-08 10:15:00 (synced to live database schema)

Table of Contents

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: 100 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 city1
state CHARACTER VARYING Yes - State containing the nearest city1
postal_code CHARACTER VARYING Yes - Postal code of the geographic location1
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)

geofence_rules

Used to store reusable geofence rule definitions and enablement state

Field Name Data Type Nullable Constraints Description
id INTEGER No PRIMARY KEY, DEFAULT: nextval('geofence_rules_id... PK
name CHARACTER VARYING No - Rule name
description TEXT Yes - Human-readable rule description
geofence_type CHARACTER VARYING No - Type of geofence being targeted
geofence_id INTEGER Yes - Optional geofence identifier
rule_type CHARACTER VARYING No - Type of rule
conditions JSONB Yes - Rule conditions payload
enabled BOOLEAN No DEFAULT: true Whether the rule is active
created_by INTEGER Yes FK → users.id User that created the rule
created_at TIMESTAMPTZ No DEFAULT: now() When the rule was created
updated_at TIMESTAMPTZ No DEFAULT: now() When the rule was last updated

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, UNIQUE WITH timestamp PK required for aggregation
hashed_adv_key TEXT No - The only relationship to the tracker table3
timestamp TIMESTAMPTZ No PRIMARY KEY, UNIQUE WITH id 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)

password_reset_tokens

Used for password recovery workflows

Field Name Data Type Nullable Constraints Description
id INTEGER No PRIMARY KEY, DEFAULT: nextval('password_reset_to... PK
user_id INTEGER No FK → users.id, ON DELETE CASCADE User that owns the token
token_hash CHARACTER VARYING No UNIQUE Secure hash of the reset token
expires_at TIMESTAMPTZ No - Token expiry timestamp
used_at TIMESTAMPTZ Yes - When the token was consumed
created_at TIMESTAMPTZ No DEFAULT: now() When the token was issued

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 date2
end_date TIMESTAMP Yes - Production run end date2
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 -
parent_id INTEGER Yes FK → production_runs.id Optional parent production run

spatial_ref_sys

Used by PostGIS (system table)

Field Name Data Type Nullable Constraints Description
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: 100 Geofence size in metres

user_tracker_notifications

Used for tracker subscription records between users and trackers

Field Name Data Type Nullable Constraints Description
id INTEGER No PRIMARY KEY, DEFAULT: nextval('user_tracker_notif... PK
user_id INTEGER No FK → users.id Subscribed user
tracker_id INTEGER No FK → trackers.id Subscribed tracker
created_at TIMESTAMPTZ Yes DEFAULT: now() Subscription creation timestamp

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 key3
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_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
auth_provider CHARACTER VARYING Yes - OAuth provider name
provider_subject_id CHARACTER VARYING Yes - OAuth provider subject identifier

Live Schema Additions

The database now also includes the following live elements that were added after the 2025 cleanup pass:

  • users.auth_provider
  • users.provider_subject_id
  • production_runs.parent_id
  • geofence_rules
  • password_reset_tokens
  • user_tracker_notifications
  • storage_locations.geofence_size_meters and delivery_locations.geofence_size_meters now default to 100

  1. Not currently used, but valuable data we retried from reverse geocode service. ↩↩↩

  2. Start and end date is critical for establishing if a related tracker should be considered active for processing. ↩↩

  3. Key relationship for use by findmy to relate to reports fetched from Apple. ↩↩