iDrv5-MyFR8 Database Schema Documentation

**Database**: PostgreSQL 15+ **Server**: Supabase (aws-1-ap-northeast-2.pooler.supabase.com) **Database Name**: postgres **Total Tables**: 69+ **Total Inde…

Database: PostgreSQL 15+ Server: Supabase (aws-1-ap-northeast-2.pooler.supabase.com) Database Name: postgres Total Tables: 69+ Total Indexes: 100+

Overview
Entity Relationship Diagrams
Schema Details by Module
Key Relationships
Indexes and Performance
Reference Data
Audit and Timestamps

Overview

The iDrv5-MyFR8 platform uses a comprehensive PostgreSQL database schema designed for multi-tenant SaaS logistics management. The schema is organized into seven functional modules:

Module	Tables	Purpose
Core	12	Tenants, companies, users, customers
Transport	9	Zones, rates, surcharges, bookings, quotes
Asset Management	9	Vehicles, fleets, maintenance, fuel, tracking
Workflow	9	Workflow definitions, instances, states, rules
Operations	11	LTL, linehaul, dispatch, manifests, pallets
Compliance	10	Certifications, incidents, training, audits
Staff Management	8	Profiles, schedules, leave, performance

Entity Relationship Diagrams

Core Domain Diagram

erDiagram
    TENANTS ||--o{ COMPANIES : "hosts"
    TENANTS ||--|| TENANT_SETTINGS : "has"
    COMPANIES ||--o{ USERS : "employs"
    COMPANIES ||--o{ CUSTOMERS : "serves"
    CUSTOMERS ||--o{ CUSTOMER_ADDRESSES : "has"
    USERS ||--o{ NOTIFICATIONS : "receives"

    TENANTS {
        serial id PK
        uuid tenant_id UK
        varchar company_name
        varchar subdomain UK
        varchar status
        varchar plan_type
        timestamp subscription_start
        timestamp subscription_end
    }

    TENANT_SETTINGS {
        serial id PK
        uuid tenant_id FK
        varchar logo_url
        varchar primary_color
        varchar region
        varchar timezone
        varchar currency
        text[] enabled_modules
        jsonb module_settings
    }

    COMPANIES {
        serial id PK
        uuid uuid UK
        uuid tenant_id FK
        varchar name
        varchar abn
        varchar company_code UK
        varchar status
        boolean is_customer
        boolean is_transport_company
        jsonb enabled_modules
    }

    USERS {
        serial id PK
        uuid uuid UK
        integer company_id FK
        varchar username UK
        varchar email UK
        varchar password_hash
        varchar role
        text[] permissions
        boolean is_active
        boolean is_super_admin
    }

    CUSTOMERS {
        serial id PK
        uuid uuid UK
        integer company_id FK
        varchar name
        varchar account_number UK
        varchar status
        decimal credit_limit
        boolean is_active
    }

    CUSTOMER_ADDRESSES {
        serial id PK
        integer customer_id FK
        varchar address_type
        varchar address_line1
        varchar suburb
        varchar state
        varchar postcode
        boolean is_default
        jsonb operating_hours
    }

    NOTIFICATIONS {
        serial id PK
        integer user_id FK
        varchar title
        text message
        varchar notification_type
        boolean is_read
        jsonb data
    }

Transport & Rates Diagram

erDiagram
    COMPANIES ||--o{ ZONES : "defines"
    COMPANIES ||--o{ SERVICE_LEVELS : "offers"
    COMPANIES ||--o{ SURCHARGES : "applies"
    CUSTOMERS ||--o{ RATE_CARDS : "has"
    RATE_CARDS ||--o{ RATE_ENTRIES : "contains"
    ZONES ||--o{ ZONE_SUBURBS : "includes"
    ZONES ||--o{ RATE_ENTRIES : "origin"
    ZONES ||--o{ RATE_ENTRIES : "destination"
    SERVICE_LEVELS ||--o{ RATE_ENTRIES : "priced_by"
    SURCHARGES ||--o{ CUSTOMER_SURCHARGES : "customized"
    CUSTOMERS ||--o{ CUSTOMER_SURCHARGES : "has"
    CUSTOMERS ||--o{ BOOKINGS : "creates"
    CUSTOMERS ||--o{ QUOTES : "requests"
    CUSTOMERS ||--o{ UNIFIED_QUOTES : "requests"

    ZONES {
        serial id PK
        integer company_id FK
        varchar name
        varchar short_name
        varchar code UK
        varchar zone_type
        varchar state
        text boundary_geojson
        decimal center_lat
        decimal center_lng
        varchar service_flag
        boolean is_active
    }

    ZONE_SUBURBS {
        serial id PK
        integer zone_id FK
        varchar suburb_name
        varchar postcode
        varchar state_code
        decimal latitude
        decimal longitude
    }

    SERVICE_LEVELS {
        serial id PK
        integer company_id FK
        varchar name
        varchar description
        integer priority
        decimal base_cost_multiplier
        decimal transit_time_multiplier
        boolean is_active
    }

    RATE_CARDS {
        serial id PK
        integer customer_id FK
        varchar name
        varchar rate_type
        varchar description
        date effective_date
        date expiry_date
        boolean is_active
    }

    RATE_ENTRIES {
        serial id PK
        integer rate_card_id FK
        integer origin_zone_id FK
        integer destination_zone_id FK
        integer service_level_id FK
        decimal rate_per_kg
        decimal rate_per_pallet
        decimal flat_rate
        decimal minimum_charge
        integer transit_time_hours
        boolean is_active
    }

    SURCHARGES {
        serial id PK
        integer company_id FK
        varchar name
        varchar type
        decimal amount
        decimal percentage
        varchar applies_to
        boolean is_active
    }

    CUSTOMER_SURCHARGES {
        serial id PK
        integer customer_id FK
        integer surcharge_id FK
        boolean is_enabled
        varchar adjustment_type
        decimal adjustment_value
    }

    BOOKINGS {
        serial id PK
        integer company_id FK
        integer customer_id FK
        varchar booking_number UK
        integer service_level_id FK
        varchar job_type
        varchar status
        date pickup_date
        date delivery_date
        decimal total_amount
    }

    QUOTES {
        serial id PK
        integer company_id FK
        integer customer_id FK
        varchar quote_number UK
        varchar status
        date valid_until
        decimal total_amount
    }

    UNIFIED_QUOTES {
        serial id PK
        integer company_id FK
        integer customer_id FK
        varchar quote_number UK
        varchar origin_suburb
        varchar dest_suburb
        decimal weight
        decimal total_amount
        varchar status
    }

Operations Diagram

erDiagram
    CUSTOMERS ||--o{ LTL_BOOKINGS : "places"
    USERS ||--o{ LTL_PICKUP_JOBS : "assigned_to"
    VEHICLES ||--o{ LTL_PICKUP_JOBS : "uses"
    LTL_BOOKINGS }o--|| LTL_PICKUP_JOBS : "collected_by"
    LTL_BOOKINGS }o--|| CONSOLIDATION_MANIFESTS : "consolidated_in"
    CONSOLIDATION_MANIFESTS }o--|| LINEHAUL_DISPATCHES : "dispatched_via"
    USERS ||--o{ LINEHAUL_DISPATCHES : "driven_by"
    VEHICLES ||--o{ LINEHAUL_DISPATCHES : "uses"
    LTL_BOOKINGS }o--|| DELIVERY_MANIFESTS : "delivered_by"
    USERS ||--o{ DELIVERY_MANIFESTS : "driver"
    VEHICLES ||--o{ DELIVERY_MANIFESTS : "vehicle"
    USERS ||--o{ DRIVER_MANIFESTS : "has"
    DRIVER_MANIFESTS ||--o{ MANIFEST_SHIPMENTS : "contains"
    DISPATCH_CENTERS ||--o{ DISPATCH_JOBS : "manages"
    USERS ||--o{ DISPATCH_JOBS : "assigned_driver"
    VEHICLES ||--o{ DISPATCH_JOBS : "assigned_vehicle"
    DISPATCH_JOBS ||--o{ JOB_STATUS_UPDATES : "tracks"
    PALLET_TYPES ||--o{ PALLET_TRACKING : "defines"

    LTL_BOOKINGS {
        serial id PK
        varchar booking_reference UK
        integer customer_id FK
        varchar booking_status
        timestamp pickup_date_requested
        timestamp delivery_date_requested
        decimal total_weight
        integer pallet_count
        varchar pallet_type
    }

    LTL_PICKUP_JOBS {
        serial id PK
        varchar job_reference UK
        integer driver_id FK
        integer vehicle_id FK
        varchar job_status
        timestamp estimated_pickup_time
        timestamp actual_pickup_time
    }

    CONSOLIDATION_MANIFESTS {
        serial id PK
        varchar manifest_reference UK
        date consolidation_date
        varchar warehouse_location
        varchar manifest_status
        integer total_ltl_bookings
        decimal total_weight
    }

    LINEHAUL_DISPATCHES {
        serial id PK
        varchar dispatch_reference UK
        varchar origin_depot
        varchar destination_depot
        integer primary_driver_id FK
        integer vehicle_id FK
        varchar dispatch_status
        timestamp scheduled_departure
    }

    DELIVERY_MANIFESTS {
        serial id PK
        varchar manifest_reference UK
        date delivery_date
        integer driver_id FK
        integer vehicle_id FK
        varchar manifest_status
        integer total_deliveries
    }

    DRIVER_MANIFESTS {
        serial id PK
        integer driver_id FK
        varchar manifest_code UK
        varchar vehicle_registration
        boolean is_active
        timestamp shift_start
        timestamp shift_end
    }

    MANIFEST_SHIPMENTS {
        serial id PK
        integer manifest_id FK
        integer booking_id
        varchar status
        integer delivery_sequence
        boolean requires_signature
        timestamp delivered_at
    }

    DISPATCH_CENTERS {
        serial id PK
        varchar center_name
        varchar location
        decimal coverage_radius_km
        boolean is_active
        jsonb operating_hours
    }

    DISPATCH_JOBS {
        serial id PK
        varchar job_reference UK
        integer dispatch_center_id FK
        varchar job_type
        varchar priority
        integer assigned_driver_id FK
        integer assigned_vehicle_id FK
        varchar job_status
    }

    JOB_STATUS_UPDATES {
        serial id PK
        integer job_id FK
        varchar old_status
        varchar new_status
        decimal latitude
        decimal longitude
        timestamp status_timestamp
    }

    PALLET_TYPES {
        serial id PK
        varchar name
        varchar standard_size
        integer length_mm
        integer width_mm
        decimal max_weight_kg
        boolean is_active
    }

    PALLET_TRACKING {
        serial id PK
        varchar pallet_id UK
        integer pallet_type_id FK
        varchar current_status
        varchar current_location
        integer total_uses
        decimal deposit_amount
    }

Asset Management Diagram

erDiagram
    COMPANIES ||--o{ FLEETS : "owns"
    FLEETS ||--o{ VEHICLES : "contains"
    VEHICLES ||--o{ MAINTENANCE_RECORDS : "has"
    VEHICLES ||--o{ VEHICLE_INSPECTIONS : "undergoes"
    VEHICLES ||--o{ FUEL_RECORDS : "consumes"
    MAINTENANCE_RECORDS ||--o{ MAINTENANCE_ITEMS : "includes"
    USERS ||--o{ ASSET_TRACKING : "operates"

    ASSETS {
        serial id PK
        varchar asset_code UK
        varchar name
        varchar asset_type
        varchar status
        date purchase_date
        decimal purchase_cost
        decimal current_value
        varchar manufacturer
        varchar model
    }

    FLEETS {
        serial id PK
        integer company_id FK
        varchar name
        varchar description
        integer fleet_manager_id FK
        boolean is_active
    }

    VEHICLES {
        serial id PK
        varchar registration UK
        varchar make
        varchar model
        integer year
        integer fleet_id FK
        varchar vehicle_type
        decimal capacity_weight
        decimal capacity_volume
        integer capacity_pallets
        varchar status
        decimal current_lat
        decimal current_lng
        date insurance_expiry
        date registration_expiry
    }

    MAINTENANCE_RECORDS {
        serial id PK
        integer vehicle_id FK
        varchar maintenance_type
        text description
        date maintenance_date
        integer odometer_reading
        decimal total_cost
        varchar status
        varchar priority
    }

    MAINTENANCE_ITEMS {
        serial id PK
        integer maintenance_record_id FK
        varchar item_name
        varchar part_number
        integer quantity
        decimal unit_cost
        decimal total_cost
    }

    VEHICLE_INSPECTIONS {
        serial id PK
        integer vehicle_id FK
        varchar inspection_type
        date inspection_date
        integer inspector_id FK
        varchar overall_result
        jsonb checklist_items
        text defects_found
    }

    FUEL_RECORDS {
        serial id PK
        integer vehicle_id FK
        date fuel_date
        varchar fuel_type
        decimal quantity_liters
        decimal total_cost
        integer odometer_reading
        integer driver_id FK
        decimal fuel_efficiency_kmpl
    }

    ASSET_TRACKING {
        serial id PK
        varchar asset_type
        integer asset_id
        decimal latitude
        decimal longitude
        timestamp tracked_at
        varchar engine_status
        decimal speed_kmh
        integer driver_id FK
    }

Workflow Diagram

erDiagram
    WORKFLOW_DEFINITIONS ||--o{ WORKFLOW_INSTANCES : "creates"
    WORKFLOW_DEFINITIONS ||--o{ WORKFLOW_STATES : "defines"
    WORKFLOW_DEFINITIONS ||--o{ WORKFLOW_RULES : "has"
    WORKFLOW_INSTANCES ||--o{ WORKFLOW_TRANSITIONS : "records"
    WORKFLOW_INSTANCES ||--o{ WORKFLOW_ASSIGNMENTS : "creates"
    WORKFLOW_INSTANCES ||--o{ WORKFLOW_HISTORY : "logs"
    WORKFLOW_INSTANCES ||--o{ WORKFLOW_NOTIFICATIONS : "triggers"
    WORKFLOW_DEFINITIONS ||--o{ WORKFLOW_METRICS : "measured_by"

    WORKFLOW_DEFINITIONS {
        serial id PK
        varchar name
        varchar code UK
        varchar entity_type
        varchar version
        varchar initial_state
        jsonb states
        jsonb transitions
        boolean is_active
    }

    WORKFLOW_INSTANCES {
        serial id PK
        varchar reference_number
        varchar entity_type
        integer entity_id
        integer workflow_definition_id FK
        varchar current_state
        varchar status
        date due_date
        integer company_id FK
        jsonb workflow_data
    }

    WORKFLOW_STATES {
        serial id PK
        integer definition_id FK
        varchar state_name
        varchar display_name
        varchar state_type
        varchar color_code
        boolean requires_approval
        boolean auto_transition
    }

    WORKFLOW_RULES {
        serial id PK
        integer definition_id FK
        varchar rule_name
        varchar rule_type
        varchar trigger_state
        text condition_expression
        varchar action_type
        jsonb action_config
        integer priority
    }

    WORKFLOW_TRANSITIONS {
        serial id PK
        integer instance_id FK
        integer definition_id FK
        varchar from_state
        varchar to_state
        varchar transition_name
        integer user_id FK
        timestamp transition_date
    }

    WORKFLOW_ASSIGNMENTS {
        serial id PK
        integer instance_id FK
        varchar state_name
        varchar assigned_to_user
        varchar assigned_to_role
        varchar task_name
        varchar priority
        date due_date
        varchar status
    }

    WORKFLOW_HISTORY {
        serial id PK
        integer instance_id FK
        varchar event_type
        varchar old_state
        varchar new_state
        integer user_id FK
        jsonb event_data
        timestamp event_timestamp
    }

    WORKFLOW_NOTIFICATIONS {
        serial id PK
        integer instance_id FK
        varchar notification_type
        varchar recipient_type
        varchar recipient_identifier
        varchar subject
        text message
        varchar status
    }

    WORKFLOW_METRICS {
        serial id PK
        integer definition_id FK
        integer instance_id FK
        varchar metric_name
        varchar metric_type
        decimal metric_value
        date measurement_date
    }

Compliance Diagram

erDiagram
    COMPLIANCE_CATEGORIES ||--o{ ACCREDITATIONS : "categorizes"
    COMPLIANCE_CATEGORIES ||--o{ STAFF_CERTIFICATIONS : "categorizes"
    COMPANIES ||--o{ ACCREDITATIONS : "holds"
    USERS ||--o{ STAFF_CERTIFICATIONS : "holds"
    COMPANIES ||--o{ SAFETY_INCIDENTS : "reports"
    COMPANIES ||--o{ TRAINING_PROGRAMS : "offers"
    TRAINING_PROGRAMS ||--o{ TRAINING_RECORDS : "tracks"
    USERS ||--o{ TRAINING_RECORDS : "attends"
    COMPLIANCE_CATEGORIES ||--o{ COMPLIANCE_CHECKLISTS : "defines"
    COMPLIANCE_CHECKLISTS ||--o{ CHECKLIST_ASSESSMENTS : "assessed_by"
    COMPANIES ||--o{ REGULATORY_INSPECTIONS : "undergoes"

    COMPLIANCE_CATEGORIES {
        serial id PK
        varchar category_name UK
        varchar description
        varchar regulatory_body
        varchar category_type
        boolean is_mandatory
        boolean is_active
    }

    ACCREDITATIONS {
        serial id PK
        integer company_id FK
        integer category_id FK
        varchar accreditation_name
        varchar accreditation_number
        varchar issuing_authority
        date issue_date
        date expiry_date
        varchar status
    }

    STAFF_CERTIFICATIONS {
        serial id PK
        integer user_id FK
        integer category_id FK
        varchar certification_name
        varchar certification_number
        date issue_date
        date expiry_date
        varchar status
        varchar competency_level
    }

    SAFETY_INCIDENTS {
        serial id PK
        varchar incident_number UK
        integer company_id FK
        timestamp incident_date
        varchar incident_type
        varchar severity_level
        text incident_description
        varchar investigation_status
        varchar status
    }

    TRAINING_PROGRAMS {
        serial id PK
        integer company_id FK
        varchar program_name
        varchar program_code UK
        varchar program_type
        integer duration_hours
        varchar frequency_type
        boolean provides_certification
        boolean is_active
    }

    TRAINING_RECORDS {
        serial id PK
        integer program_id FK
        integer user_id FK
        date session_date
        varchar attendance_status
        decimal assessment_score
        varchar assessment_result
        varchar certificate_number
        date certificate_expiry_date
    }

    COMPLIANCE_CHECKLISTS {
        serial id PK
        integer company_id FK
        integer category_id FK
        varchar checklist_name
        varchar checklist_type
        varchar frequency
        jsonb checklist_items
        decimal pass_threshold_score
        boolean is_active
    }

    CHECKLIST_ASSESSMENTS {
        serial id PK
        integer checklist_id FK
        integer assessed_by FK
        date assessment_date
        varchar assessment_type
        decimal total_score
        varchar overall_result
        jsonb checklist_responses
        varchar status
    }

    REGULATORY_INSPECTIONS {
        serial id PK
        integer company_id FK
        varchar inspection_number UK
        varchar inspection_type
        varchar regulatory_body
        date actual_date
        varchar overall_result
        decimal compliance_score
        varchar status
    }

    COMPLIANCE_AUDIT_TRAILS {
        serial id PK
        varchar entity_type
        integer entity_id
        varchar action_type
        jsonb old_values
        jsonb new_values
        integer user_id FK
        timestamp action_timestamp
    }

Staff Management Diagram

erDiagram
    USERS ||--|| USER_PROFILES : "has"
    USER_PROFILES ||--o{ CERTIFICATIONS : "holds"
    USER_PROFILES ||--o{ SCHEDULES : "works"
    USER_PROFILES ||--o{ TIME_RECORDS : "logs"
    USER_PROFILES ||--o{ LEAVE_REQUESTS : "requests"
    USER_PROFILES ||--o{ LEAVE_BALANCES : "tracks"
    USER_PROFILES ||--o{ PERFORMANCE_REVIEWS : "receives"
    USER_PROFILES ||--o{ DISCIPLINARY_ACTIONS : "receives"

    USER_PROFILES {
        serial id PK
        integer user_id FK UK
        varchar employee_id
        date date_of_birth
        varchar department
        varchar position
        integer reporting_manager_id FK
        varchar employment_type
        date start_date
        decimal base_salary
        decimal hourly_rate
        jsonb availability
        boolean is_active
    }

    CERTIFICATIONS {
        serial id PK
        integer user_profile_id FK
        varchar certification_name
        varchar certification_type
        varchar issuing_authority
        varchar certification_number
        date issue_date
        date expiry_date
        varchar status
    }

    SCHEDULES {
        serial id PK
        integer user_profile_id FK
        varchar schedule_name
        varchar schedule_type
        date effective_date
        time shift_start_time
        time shift_end_time
        integer[] days_of_week
        boolean is_active
    }

    TIME_RECORDS {
        serial id PK
        integer user_profile_id FK
        date work_date
        timestamp clock_in_time
        timestamp clock_out_time
        decimal regular_hours
        decimal overtime_hours
        decimal total_hours
        varchar status
        boolean payroll_processed
    }

    LEAVE_REQUESTS {
        serial id PK
        integer user_profile_id FK
        varchar leave_type
        date start_date
        date end_date
        decimal total_days
        varchar reason
        varchar status
        integer reviewed_by FK
    }

    LEAVE_BALANCES {
        serial id PK
        integer user_profile_id FK
        varchar leave_type
        date balance_date
        decimal opening_balance
        decimal accrued_this_period
        decimal used_this_period
        decimal closing_balance
        boolean is_current
    }

    PERFORMANCE_REVIEWS {
        serial id PK
        integer user_profile_id FK
        date review_period_start
        date review_period_end
        varchar review_type
        integer reviewer_id FK
        varchar status
        varchar overall_rating
        decimal overall_score
        jsonb action_items
    }

    DISCIPLINARY_ACTIONS {
        serial id PK
        integer user_profile_id FK
        varchar incident_reference UK
        date incident_date
        varchar action_type
        varchar severity_level
        text incident_description
        varchar status
        date review_date
    }

Schema Details by Module

1. Core Schema

tenants

Multi-tenant support for SaaS model.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
tenant_id	UUID	UNIQUE, DEFAULT uuid_generate_v4()	Unique tenant identifier
company_name	VARCHAR(200)	NOT NULL	Company name
subdomain	VARCHAR(100)	UNIQUE	Tenant subdomain
status	VARCHAR(20)		active, inactive, suspended
plan_type	VARCHAR(20)		starter, growth, enterprise
admin_email	VARCHAR(255)		Admin email
admin_name	VARCHAR(200)		Admin name
phone	VARCHAR(50)		Phone number
website	VARCHAR(255)		Website URL
address_line1	VARCHAR(255)		Address line 1
address_line2	VARCHAR(255)		Address line 2
city	VARCHAR(100)		City
state	VARCHAR(100)		State
postal_code	VARCHAR(20)		Postal code
country	VARCHAR(100)		Country
subscription_start	TIMESTAMP		Subscription start date
subscription_end	TIMESTAMP		Subscription end date
user_count	INTEGER	DEFAULT 0	Number of users
last_login	TIMESTAMP		Last login time
total_bookings	INTEGER	DEFAULT 0	Total bookings
monthly_bookings	INTEGER	DEFAULT 0	Monthly bookings
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp
created_by	VARCHAR(100)		Created by user

Indexes: idx_tenants_tenant_id, idx_tenants_subdomain, idx_tenants_status

tenant_settings

Tenant-specific configuration and branding.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
tenant_id	UUID	FK -> tenants.tenant_id, ON DELETE CASCADE	Tenant reference
logo_url	VARCHAR(500)		Logo URL
primary_color	VARCHAR(7)		Primary brand color (hex)
accent_color	VARCHAR(7)		Accent color (hex)
favicon_url	VARCHAR(500)		Favicon URL
region	VARCHAR(2)		ISO country code
timezone	VARCHAR(50)		Timezone (e.g., Australia/Sydney)
currency	VARCHAR(3)		Currency code (e.g., AUD)
date_format	VARCHAR(20)		Date format string
time_format	VARCHAR(20)		Time format string
quote_prefix	VARCHAR(10)		Quote number prefix
booking_prefix	VARCHAR(10)		Booking number prefix
connote_prefix	VARCHAR(10)		Connote number prefix
manifest_prefix	VARCHAR(10)		Manifest number prefix
quote_counter	INTEGER	DEFAULT 1	Quote counter
booking_counter	INTEGER	DEFAULT 1	Booking counter
connote_counter	INTEGER	DEFAULT 1	Connote counter
manifest_counter	INTEGER	DEFAULT 1	Manifest counter
enabled_modules	TEXT[]		Enabled module list
module_settings	JSONB		Module-specific settings
default_workflows	JSONB		Default workflow configurations
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_tenant_settings_tenant_id

companies

Transport companies using the SaaS platform.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
uuid	UUID	UNIQUE	Unique identifier
tenant_id	UUID	FK -> tenants.tenant_id	Tenant reference
name	VARCHAR(255)	NOT NULL	Company name
trading_name	VARCHAR(255)		Trading name
abn	VARCHAR(20)		Australian Business Number
company_code	VARCHAR(20)	UNIQUE	Unique company code
contact_name	VARCHAR(200)		Primary contact name
contact_email	VARCHAR(255)		Primary contact email
contact_phone	VARCHAR(50)		Primary contact phone
address_line1	VARCHAR(255)		Address line 1
address_line2	VARCHAR(255)		Address line 2
city	VARCHAR(100)		City
state	VARCHAR(100)		State
postal_code	VARCHAR(20)		Postal code
country	VARCHAR(100)	DEFAULT ‘Australia’	Country
website	VARCHAR(255)		Website URL
industry	VARCHAR(100)		Industry type
status	VARCHAR(20)	DEFAULT ‘ACTIVE’	Company status
subscription_tier	VARCHAR(50)		Subscription tier
subscription_start_date	DATE		Subscription start
subscription_end_date	DATE		Subscription end
is_customer	BOOLEAN	DEFAULT FALSE	Is customer flag
is_transport_company	BOOLEAN	DEFAULT TRUE	Is transport company flag
primary_color	VARCHAR(7)		Brand primary color
secondary_color	VARCHAR(7)		Brand secondary color
logo_path	VARCHAR(500)		Logo file path
enabled_modules	JSONB		Enabled modules config
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_companies_status, idx_companies_tenant_id, idx_companies_company_code

users

User authentication and management.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
uuid	UUID	UNIQUE	Unique identifier
company_id	INTEGER	FK -> companies.id, ON DELETE CASCADE	Company reference
username	VARCHAR(50)	UNIQUE, NOT NULL	Username
email	VARCHAR(255)	UNIQUE, NOT NULL	Email address
password_hash	VARCHAR(255)		Hashed password
first_name	VARCHAR(100)		First name
last_name	VARCHAR(100)		Last name
full_name	VARCHAR(200)		Full name
phone	VARCHAR(50)		Phone number
mobile	VARCHAR(50)		Mobile number
role	VARCHAR(50)		User role
permissions	TEXT[]		Permission list
is_active	BOOLEAN	DEFAULT TRUE	Active status
is_verified	BOOLEAN	DEFAULT FALSE	Email verified
is_super_admin	BOOLEAN	DEFAULT FALSE	Super admin flag
email_verification_token	VARCHAR(255)		Email verification token
password_reset_token	VARCHAR(255)		Password reset token
password_reset_expires	TIMESTAMP		Token expiry
last_login	TIMESTAMP		Last login time
last_login_ip	VARCHAR(45)		Last login IP
login_count	INTEGER	DEFAULT 0	Total logins
customer_code	VARCHAR(50)		Customer portal code
customer_type	VARCHAR(50)		Customer type
credit_limit	DECIMAL(15,2)		Credit limit
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_users_company_id, idx_users_email, idx_users_username, idx_users_role, idx_users_is_active, idx_users_customer_code

customers

Customer account management.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
uuid	UUID	UNIQUE	Unique identifier
company_id	INTEGER	FK -> companies.id	Company reference
name	VARCHAR(255)	NOT NULL	Customer name
contact_name	VARCHAR(200)		Primary contact
email	VARCHAR(255)		Email address
phone	VARCHAR(50)		Phone number
address	VARCHAR(500)		Address
city	VARCHAR(100)		City
state	VARCHAR(100)		State
postcode	VARCHAR(20)		Postcode
country	VARCHAR(100)	DEFAULT ‘Australia’	Country
suburb	VARCHAR(100)		Suburb
account_number	VARCHAR(50)	UNIQUE	Account number
account_type	VARCHAR(50)		Account type
status	VARCHAR(20)	DEFAULT ‘ACTIVE’	Status
abn	VARCHAR(20)		ABN
account_manager	VARCHAR(200)		Account manager
payment_terms	VARCHAR(50)		Payment terms
payment_method	VARCHAR(50)		Payment method
credit_limit	DECIMAL(15,2)		Credit limit
tax_number	VARCHAR(50)		Tax number
tax_exempt	BOOLEAN	DEFAULT FALSE	Tax exempt flag
ops_contact_name	VARCHAR(200)		Operations contact name
ops_contact_email	VARCHAR(255)		Operations contact email
ops_contact_phone	VARCHAR(50)		Operations contact phone
ops_contact_position	VARCHAR(100)		Operations contact position
accounts_contact_name	VARCHAR(200)		Accounts contact name
accounts_contact_email	VARCHAR(255)		Accounts contact email
accounts_contact_phone	VARCHAR(50)		Accounts contact phone
accounts_contact_position	VARCHAR(100)		Accounts contact position
additional_contact_name	VARCHAR(200)		Additional contact name
additional_contact_email	VARCHAR(255)		Additional contact email
additional_contact_phone	VARCHAR(50)		Additional contact phone
additional_contact_position	VARCHAR(100)		Additional contact position
additional_contact_type	VARCHAR(50)		Additional contact type
is_active	BOOLEAN	DEFAULT TRUE	Active flag
active	BOOLEAN	DEFAULT TRUE	Active flag (legacy)
notes	TEXT		Notes
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_customers_company_id, idx_customers_status, idx_customers_is_active, idx_customers_active, idx_customers_account_number, idx_customers_name

customer_addresses

Multiple addresses per customer.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
customer_id	INTEGER	FK -> customers.id, ON DELETE CASCADE	Customer reference
address_type	VARCHAR(50)		pickup, delivery, billing, head_office
company_name	VARCHAR(255)		Company name at address
site_name	VARCHAR(255)		Site name
contact_name	VARCHAR(200)		Contact name
phone	VARCHAR(50)		Phone
email	VARCHAR(255)		Email
address_line1	VARCHAR(255)	NOT NULL	Address line 1
address_line2	VARCHAR(255)		Address line 2
suburb	VARCHAR(100)		Suburb
city	VARCHAR(100)		City
state	VARCHAR(100)		State
postcode	VARCHAR(20)		Postcode
country	VARCHAR(100)	DEFAULT ‘Australia’	Country
is_default	BOOLEAN	DEFAULT FALSE	Default address flag
is_residential	BOOLEAN	DEFAULT FALSE	Residential flag
access_instructions	TEXT		Access instructions
delivery_instructions	TEXT		Delivery instructions
pickup_instructions	TEXT		Pickup instructions
notes	TEXT		Notes
operating_hours	JSONB		Operating hours
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_customer_addresses_customer_id, idx_customer_addresses_type, idx_customer_addresses_default, idx_customer_addresses_postcode, idx_customer_addresses_suburb

site_contacts

Reusable site contact information.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
site_name	VARCHAR(255)		Site name
contact_person	VARCHAR(255)		Contact person
phone_number	VARCHAR(50)		Phone number
email	VARCHAR(255)		Email
address_line1	VARCHAR(255)		Address line 1
address_line2	VARCHAR(255)		Address line 2
suburb	VARCHAR(100)		Suburb
state	VARCHAR(100)		State
postcode	VARCHAR(20)		Postcode
notes	TEXT		Notes
usage_count	INTEGER	DEFAULT 0	Usage count
created_by	INTEGER		User ID who created
customer_id	INTEGER		Optional customer link
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_site_contacts_address, idx_site_contacts_phone, idx_site_contacts_email, idx_site_contacts_customer, idx_site_contacts_usage

states (Reference Data)

Australian states and territories.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
code	VARCHAR(3)	UNIQUE	State code (NSW, VIC, etc.)
name	VARCHAR(100)		State name
abbreviation	VARCHAR(10)		Abbreviation
country	VARCHAR(100)	DEFAULT ‘Australia’	Country
is_active	BOOLEAN	DEFAULT TRUE	Active flag

Default Data: NSW, VIC, QLD, SA, WA, TAS, NT, ACT

countries (Reference Data)

Countries reference data.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
code	VARCHAR(3)	UNIQUE	Country code
name	VARCHAR(100)		Country name
currency	VARCHAR(3)		Currency code
is_active	BOOLEAN	DEFAULT TRUE	Active flag

Default Data: AU, NZ, US, CA, GB, CN, JP, KR, SG, MY, TH, IN, ID, PH, VN, FR, DE, IT, ES, NL

2. Transport Schema

zones

Geographic service zones.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
name	VARCHAR(255)	NOT NULL	Zone name
short_name	VARCHAR(50)		Short name
code	VARCHAR(10)	UNIQUE	Zone code
zone_type	VARCHAR(50)		Metro, Regional, Remote
state	VARCHAR(100)		State
country	VARCHAR(100)	DEFAULT ‘Australia’	Country
boundary_geojson	TEXT		GeoJSON boundary
center_lat	DECIMAL(10,6)		Center latitude
center_lng	DECIMAL(10,6)		Center longitude
service_flag	VARCHAR(50)		Full Service, Limited Service, No Service
coverage_radius_km	DECIMAL(10,2)		Coverage radius
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_zones_company_id, idx_zones_code, idx_zones_is_active, idx_zones_zone_type

zone_suburbs

Suburbs and localities within zones.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
zone_id	INTEGER	FK -> zones.id, ON DELETE CASCADE	Zone reference
name	VARCHAR(255)		Name
suburb_name	VARCHAR(255)		Suburb name
city	VARCHAR(100)		City
postcode	VARCHAR(20)		Postcode
state_code	VARCHAR(10)		State code
latitude	DECIMAL(10,6)		Latitude
longitude	DECIMAL(10,6)		Longitude
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_zone_suburbs_zone_id, idx_zone_suburbs_postcode, idx_zone_suburbs_state_code

service_levels

Service level definitions.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
name	VARCHAR(100)	NOT NULL	Service level name
description	TEXT		Description
priority	INTEGER		Priority (lower = higher)
base_cost_multiplier	DECIMAL(8,4)	DEFAULT 1.0	Cost multiplier
transit_time_multiplier	DECIMAL(8,4)	DEFAULT 1.0	Transit time multiplier
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_service_levels_company_id, idx_service_levels_is_active

rate_cards

Freight rate structures.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
customer_id	INTEGER	FK -> customers.id	Customer reference
name	VARCHAR(255)	NOT NULL	Rate card name
rate_type	VARCHAR(50)	NOT NULL	quantity, time, flat_rate, weight
description	TEXT		Description
effective_date	DATE		Effective date
expiry_date	DATE		Expiry date
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_by	INTEGER		Created by user ID
updated_by	VARCHAR(50)		Updated by user
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_rate_cards_customer_id, idx_rate_cards_is_active, idx_rate_cards_effective_date

rate_entries

Individual rates within rate cards.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
rate_card_id	INTEGER	FK -> rate_cards.id, ON DELETE CASCADE	Rate card reference
origin_zone_id	INTEGER	FK -> zones.id	Origin zone (nullable)
destination_zone_id	INTEGER	FK -> zones.id	Destination zone (nullable)
service_level_id	INTEGER	FK -> service_levels.id	Service level reference
rate_per_kg	DECIMAL(10,4)		Rate per kilogram
rate_per_pallet	DECIMAL(10,2)		Rate per pallet
rate_per_cubic_meter	DECIMAL(10,2)		Rate per cubic meter
flat_rate	DECIMAL(10,2)		Flat rate
hourly_rate	DECIMAL(10,2)		Hourly rate
minimum_charge	DECIMAL(10,2)		Minimum charge
minimum_weight_kg	DECIMAL(10,2)		Minimum weight
minimum_pallets	INTEGER		Minimum pallets
transit_time_hours	INTEGER		Transit time in hours
charging_type	VARCHAR(50)		per_kg, per_pallet, per_cubic_meter, flat_rate
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_rate_entries_rate_card_id, idx_rate_entries_origin_zone, idx_rate_entries_destination_zone, idx_rate_entries_service_level Composite Index: idx_rate_entries_route_service (origin_zone_id, destination_zone_id, service_level_id WHERE is_active = TRUE)

surcharges

Global surcharge definitions.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
name	VARCHAR(255)	NOT NULL	Surcharge name
type	VARCHAR(50)	NOT NULL	FIXED, PERCENTAGE, PER_KG, PER_PALLET
description	TEXT		Description
amount	DECIMAL(10,2)		Fixed amount
percentage	DECIMAL(5,2)		Percentage value
applies_to	VARCHAR(50)		ALL, METRO, REGIONAL, REMOTE
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_surcharges_company_id, idx_surcharges_is_active, idx_surcharges_type

customer_surcharges

Customer-specific surcharge adjustments.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
customer_id	INTEGER	FK -> customers.id, ON DELETE CASCADE	Customer reference
surcharge_id	INTEGER	FK -> surcharges.id, ON DELETE CASCADE	Surcharge reference
is_enabled	BOOLEAN	DEFAULT TRUE	Enabled flag
adjustment_type	VARCHAR(20)		none, override, discount, markup
adjustment_value	DECIMAL(10,2)		Adjustment value
adjustment_method	VARCHAR(20)		percentage, fixed
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_customer_surcharges_customer_id, idx_customer_surcharges_surcharge_id

bookings

Transport bookings.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
customer_id	INTEGER	FK -> customers.id	Customer reference
booking_number	VARCHAR(50)	UNIQUE	Booking number
booking_reference	VARCHAR(100)		Reference
service_level_id	INTEGER	FK -> service_levels.id	Service level
job_type	VARCHAR(50)		ltl, ftl, hourly_hire, linehaul
pickup_company	VARCHAR(255)		Pickup company
pickup_contact	VARCHAR(200)		Pickup contact
pickup_phone	VARCHAR(50)		Pickup phone
pickup_email	VARCHAR(255)		Pickup email
pickup_address	VARCHAR(500)		Pickup address
pickup_suburb	VARCHAR(100)		Pickup suburb
pickup_state	VARCHAR(100)		Pickup state
pickup_postcode	VARCHAR(20)		Pickup postcode
pickup_date	DATE		Pickup date
pickup_time	TIME		Pickup time
pickup_instructions	TEXT		Pickup instructions
delivery_company	VARCHAR(255)		Delivery company
delivery_contact	VARCHAR(200)		Delivery contact
delivery_phone	VARCHAR(50)		Delivery phone
delivery_email	VARCHAR(255)		Delivery email
delivery_address	VARCHAR(500)		Delivery address
delivery_suburb	VARCHAR(100)		Delivery suburb
delivery_state	VARCHAR(100)		Delivery state
delivery_postcode	VARCHAR(20)		Delivery postcode
delivery_date	DATE		Delivery date
delivery_time	TIME		Delivery time
delivery_instructions	TEXT		Delivery instructions
freight_description	TEXT		Freight description
weight_kg	DECIMAL(10,2)		Weight in kg
cubic_weight_kg	DECIMAL(10,2)		Cubic weight
chargeable_weight_kg	DECIMAL(10,2)		Chargeable weight
volume_cubic_meters	DECIMAL(10,4)		Volume
pallet_count	INTEGER		Pallet count
pallet_type	VARCHAR(50)		Pallet type
pieces_count	INTEGER		Pieces count
is_dangerous_goods	BOOLEAN	DEFAULT FALSE	DG flag
is_fragile	BOOLEAN	DEFAULT FALSE	Fragile flag
is_perishable	BOOLEAN	DEFAULT FALSE	Perishable flag
requires_tailgate	BOOLEAN	DEFAULT FALSE	Tailgate required
requires_appointment	BOOLEAN	DEFAULT FALSE	Appointment required
base_rate	DECIMAL(10,2)		Base rate
surcharges_total	DECIMAL(10,2)		Surcharges total
total_amount	DECIMAL(10,2)		Total amount
status	VARCHAR(50)		PENDING, CONFIRMED, DISPATCHED, DELIVERED, CANCELLED
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_bookings_company_id, idx_bookings_customer_id, idx_bookings_booking_number, idx_bookings_status, idx_bookings_pickup_date, idx_bookings_delivery_date Composite Index: idx_bookings_customer_status_date (customer_id, status, pickup_date DESC)

quotes

Transport quotes.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
customer_id	INTEGER	FK -> customers.id	Customer reference
quote_number	VARCHAR(50)	UNIQUE	Quote number
quote_reference	VARCHAR(100)		Reference
service_level_id	INTEGER	FK -> service_levels.id	Service level
origin_suburb	VARCHAR(100)		Origin suburb
origin_state	VARCHAR(100)		Origin state
origin_postcode	VARCHAR(20)		Origin postcode
destination_suburb	VARCHAR(100)		Destination suburb
destination_state	VARCHAR(100)		Destination state
destination_postcode	VARCHAR(20)		Destination postcode
freight_type	VARCHAR(50)		Freight type
weight_kg	DECIMAL(10,2)		Weight
volume_cubic_meters	DECIMAL(10,4)		Volume
pallet_count	INTEGER		Pallet count
base_rate	DECIMAL(10,2)		Base rate
surcharges_total	DECIMAL(10,2)		Surcharges
total_amount	DECIMAL(10,2)		Total
valid_until	DATE		Validity date
status	VARCHAR(50)		PENDING, SENT, ACCEPTED, DECLINED, EXPIRED
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_quotes_company_id, idx_quotes_customer_id, idx_quotes_quote_number, idx_quotes_status, idx_quotes_valid_until

unified_quotes

Streamlined unified quoting system.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
customer_id	INTEGER	FK -> customers.id	Customer reference
quote_number	VARCHAR(20)	UNIQUE	Quote number
customer_name	VARCHAR(255)		Customer name
origin_suburb	VARCHAR(100)	NOT NULL	Origin suburb
origin_state	VARCHAR(100)		Origin state
origin_postcode	VARCHAR(20)		Origin postcode
origin_address	VARCHAR(500)		Origin address
origin_company	VARCHAR(255)		Origin company
origin_contact	VARCHAR(200)		Origin contact
origin_phone	VARCHAR(50)		Origin phone
origin_email	VARCHAR(255)		Origin email
origin_tailgate	BOOLEAN	DEFAULT FALSE	Origin tailgate
dest_suburb	VARCHAR(100)	NOT NULL	Destination suburb
dest_state	VARCHAR(100)		Destination state
dest_postcode	VARCHAR(20)		Destination postcode
dest_address	VARCHAR(500)		Destination address
dest_company	VARCHAR(255)		Destination company
dest_contact	VARCHAR(200)		Destination contact
dest_phone	VARCHAR(50)		Destination phone
dest_email	VARCHAR(255)		Destination email
dest_tailgate	BOOLEAN	DEFAULT FALSE	Destination tailgate
freight_type	VARCHAR(50)	DEFAULT ‘per_kg’	Freight type
service_level	VARCHAR(50)		Service level
weight	DECIMAL(10,2)	NOT NULL	Weight
volume_weight	DECIMAL(10,2)		Volume weight
chargeable_weight	DECIMAL(10,2)		Chargeable weight
volume	DECIMAL(10,4)		Volume
pallet_count	INTEGER		Pallet count
pieces_count	INTEGER		Pieces count
base_rate	DECIMAL(10,2)		Base rate
fuel_surcharge	DECIMAL(10,2)		Fuel surcharge
other_surcharges	DECIMAL(10,2)		Other surcharges
total_amount	DECIMAL(10,2)		Total amount
reference_number	VARCHAR(100)		Reference
description	TEXT		Description
special_instructions	TEXT		Special instructions
status	VARCHAR(50)	DEFAULT ‘PENDING’	Status
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_unified_quotes_company_id, idx_unified_quotes_customer_id, idx_unified_quotes_quote_number, idx_unified_quotes_status

3. Asset Management Schema

assets

Base asset model.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
asset_code	VARCHAR(20)	UNIQUE	Asset code
name	VARCHAR(100)	NOT NULL	Asset name
description	TEXT		Description
asset_type	VARCHAR(50)	NOT NULL	Asset type
status	VARCHAR(20)	DEFAULT ‘ACTIVE’	Status
purchase_date	DATE		Purchase date
purchase_cost	DECIMAL(15,2)		Purchase cost
current_value	DECIMAL(15,2)		Current value
expected_lifetime	INTEGER		Lifetime in months
manufacturer	VARCHAR(100)		Manufacturer
model	VARCHAR(100)		Model
serial_number	VARCHAR(100)		Serial number
year	INTEGER		Year
notes	TEXT		Notes
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_assets_asset_code, idx_assets_asset_type, idx_assets_status

fleets

Fleet management and organization.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
company_id	INTEGER	FK -> companies.id	Company reference
name	VARCHAR(100)	NOT NULL	Fleet name
description	TEXT		Description
fleet_manager_id	INTEGER	FK -> users.id	Fleet manager
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_fleets_company_id, idx_fleets_fleet_manager_id, idx_fleets_is_active

vehicles

Vehicle asset management.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
registration	VARCHAR(20)	UNIQUE	Registration number
make	VARCHAR(100)		Make
model	VARCHAR(100)		Model
year	INTEGER		Year
fleet_id	INTEGER	FK -> fleets.id	Fleet reference
vehicle_type	VARCHAR(30)	NOT NULL	Vehicle type
capacity_weight	DECIMAL(10,2)		Weight capacity (kg)
capacity_volume	DECIMAL(10,2)		Volume capacity (m³)
capacity_pallets	INTEGER		Pallet capacity
status	VARCHAR(20)	DEFAULT ‘AVAILABLE’	Status
engine_number	VARCHAR(50)		Engine number
chassis_number	VARCHAR(50)		Chassis number
fuel_type	VARCHAR(30)		Fuel type
transmission	VARCHAR(30)		Transmission type
current_lat	DECIMAL(10,6)		Current latitude
current_lng	DECIMAL(10,6)		Current longitude
location_updated_at	TIMESTAMP		Location update time
odometer	INTEGER		Current odometer
insurance_expiry	DATE		Insurance expiry
registration_expiry	DATE		Registration expiry
roadworthy_expiry	DATE		Roadworthy expiry
purchase_price	DECIMAL(15,2)		Purchase price
current_value	DECIMAL(15,2)		Current value
depreciation_rate	DECIMAL(5,2)		Depreciation rate
last_service_date	DATE		Last service date
last_service_odometer	INTEGER		Last service odometer
next_service_due_date	DATE		Next service date
next_service_due_odometer	INTEGER		Next service odometer
is_active	BOOLEAN	DEFAULT TRUE	Active flag
notes	TEXT		Notes
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_vehicles_registration, idx_vehicles_fleet_id, idx_vehicles_status, idx_vehicles_vehicle_type, idx_vehicles_is_active

maintenance_records

Vehicle maintenance and repair history.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
vehicle_id	INTEGER	FK -> vehicles.id, ON DELETE CASCADE	Vehicle reference
maintenance_type	VARCHAR(50)		Service, Repair, Inspection
description	TEXT	NOT NULL	Description
maintenance_date	DATE		Maintenance date
odometer_reading	INTEGER		Odometer reading
service_provider	VARCHAR(200)		Service provider
technician_name	VARCHAR(200)		Technician
parts_cost	DECIMAL(10,2)		Parts cost
labor_cost	DECIMAL(10,2)		Labor cost
total_cost	DECIMAL(10,2)		Total cost
status	VARCHAR(20)		SCHEDULED, IN_PROGRESS, COMPLETED, CANCELLED
priority	VARCHAR(20)		LOW, MEDIUM, HIGH, URGENT
notes	TEXT		Notes
next_service_date	DATE		Next service date
next_service_odometer	INTEGER		Next service odometer
warranty_expiry	DATE		Warranty expiry
created_by	INTEGER	FK -> users.id	Created by
updated_by	INTEGER	FK -> users.id	Updated by
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_maintenance_records_vehicle_id, idx_maintenance_records_date, idx_maintenance_records_type, idx_maintenance_records_status, idx_maintenance_records_created_by

maintenance_items

Individual parts and items in maintenance records.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
maintenance_record_id	INTEGER	FK -> maintenance_records.id, ON DELETE CASCADE	Record reference
item_name	VARCHAR(200)	NOT NULL	Item name
item_description	TEXT		Description
part_number	VARCHAR(50)		Part number
quantity	INTEGER	DEFAULT 1	Quantity
unit_cost	DECIMAL(10,2)		Unit cost
total_cost	DECIMAL(10,2)		Total cost
supplier	VARCHAR(200)		Supplier
warranty_period	INTEGER		Warranty (months)
notes	TEXT		Notes
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_maintenance_items_record_id

vehicle_inspections

Safety and compliance inspections.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
vehicle_id	INTEGER	FK -> vehicles.id, ON DELETE CASCADE	Vehicle reference
inspection_type	VARCHAR(50)		Pre-trip, Daily, Weekly, Annual
inspection_date	DATE	NOT NULL	Inspection date
inspector_id	INTEGER	FK -> users.id	Inspector
inspector_name	VARCHAR(200)		Inspector name
overall_result	VARCHAR(20)		PASS, FAIL, CONDITIONAL
odometer_reading	INTEGER		Odometer reading
checklist_items	JSONB		Checklist items
defects_found	TEXT		Defects found
recommendations	TEXT		Recommendations
requires_immediate_attention	BOOLEAN	DEFAULT FALSE	Immediate attention flag
next_inspection_due	DATE		Next inspection date
status	VARCHAR(20)	DEFAULT ‘COMPLETED’	Status
created_by	INTEGER	FK -> users.id	Created by
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_vehicle_inspections_vehicle_id, idx_vehicle_inspections_date, idx_vehicle_inspections_type, idx_vehicle_inspections_result

fuel_records

Fuel consumption and efficiency tracking.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
vehicle_id	INTEGER	FK -> vehicles.id, ON DELETE CASCADE	Vehicle reference
fuel_date	DATE	NOT NULL	Fuel date
fuel_type	VARCHAR(30)		Fuel type
quantity_liters	DECIMAL(10,2)		Quantity (liters)
cost_per_liter	DECIMAL(6,3)		Cost per liter
total_cost	DECIMAL(10,2)		Total cost
fuel_station	VARCHAR(200)		Fuel station
location	VARCHAR(200)		Location
odometer_reading	INTEGER		Odometer reading
receipt_number	VARCHAR(50)		Receipt number
payment_method	VARCHAR(50)		Payment method
driver_id	INTEGER	FK -> users.id	Driver
driver_name	VARCHAR(200)		Driver name
distance_since_last_fill	DECIMAL(10,2)		Distance since last fill
fuel_efficiency_kmpl	DECIMAL(6,2)		Fuel efficiency
notes	TEXT		Notes
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_fuel_records_vehicle_id, idx_fuel_records_date, idx_fuel_records_driver_id

asset_tracking

Real-time GPS tracking for assets.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
asset_type	VARCHAR(20)		VEHICLE, TRAILER, CONTAINER
asset_id	INTEGER		Asset ID
latitude	DECIMAL(10,6)		Latitude
longitude	DECIMAL(10,6)		Longitude
accuracy_meters	DECIMAL(8,2)		GPS accuracy
altitude_meters	DECIMAL(10,2)		Altitude
heading_degrees	DECIMAL(5,2)		Heading
speed_kmh	DECIMAL(6,2)		Speed
tracked_at	TIMESTAMP	NOT NULL	Tracking timestamp
engine_status	VARCHAR(10)		ON, OFF, IDLE
fuel_level_percent	DECIMAL(5,2)		Fuel level
battery_level_percent	DECIMAL(5,2)		Battery level
driver_id	INTEGER	FK -> users.id	Driver
trip_id	VARCHAR(50)		Trip ID
odometer_reading	INTEGER		Odometer
device_id	VARCHAR(50)		Device ID
device_type	VARCHAR(50)		Device type
signal_strength	INTEGER		Signal strength
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp

Indexes: idx_asset_tracking_asset, idx_asset_tracking_tracked_at, idx_asset_tracking_driver_id, idx_asset_tracking_trip_id

pallet_types

Pallet specifications and dimensions.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
name	VARCHAR(100)	NOT NULL	Pallet name
description	TEXT		Description
standard_size	VARCHAR(20)		Standard size (e.g., 1200x1000)
length_mm	INTEGER		Length (mm)
width_mm	INTEGER		Width (mm)
height_mm	INTEGER		Height (mm)
max_weight_kg	DECIMAL(10,2)		Max weight
max_stack_height_mm	INTEGER		Max stack height
material	VARCHAR(50)		Wood, Plastic, Metal
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_pallet_types_name, idx_pallet_types_is_active

4. Workflow Schema

workflow_definitions

Workflow templates and business process definitions.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
name	VARCHAR(100)	NOT NULL	Workflow name
code	VARCHAR(50)	UNIQUE	Workflow code
description	TEXT		Description
entity_type	VARCHAR(50)		Entity type (BOOKING, QUOTE, etc.)
version	VARCHAR(20)	DEFAULT ‘1.0.0’	Version
initial_state	VARCHAR(50)		Initial state
states	JSONB		State definitions
transitions	JSONB		Transition definitions
workflow_metadata	JSONB	DEFAULT ’{}‘	Metadata
is_active	BOOLEAN	DEFAULT TRUE	Active flag
created_by	INTEGER	FK -> users.id	Created by
updated_by	INTEGER	FK -> users.id	Updated by
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_workflow_definitions_code, idx_workflow_definitions_entity_type, idx_workflow_definitions_is_active

workflow_instances

Active workflow instances.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
reference_number	VARCHAR(50)		Reference number
entity_type	VARCHAR(50)		Entity type
entity_id	INTEGER		Entity ID
workflow_definition_id	INTEGER	FK -> workflow_definitions.id	Definition reference
current_state	VARCHAR(50)		Current state
status	VARCHAR(20)		ACTIVE, COMPLETED, CANCELLED, ERROR
due_date	DATE		Due date
company_id	INTEGER	FK -> companies.id	Company reference
started_by	INTEGER	FK -> users.id	Started by
completed_by	INTEGER	FK -> users.id	Completed by
completed_at	TIMESTAMP		Completion time
workflow_data	JSONB	DEFAULT ’{}‘	Workflow data
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_workflow_instances_definition_id, idx_workflow_instances_entity, idx_workflow_instances_status, idx_workflow_instances_current_state, idx_workflow_instances_company_id, idx_workflow_instances_started_by

workflow_transitions

Workflow state changes history.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
instance_id	INTEGER	FK -> workflow_instances.id, ON DELETE CASCADE	Instance reference
definition_id	INTEGER	FK -> workflow_definitions.id	Definition reference
from_state	VARCHAR(50)		From state
to_state	VARCHAR(50)		To state
transition_name	VARCHAR(100)		Transition name
user_id	INTEGER	FK -> users.id	User
user_name	VARCHAR(200)		User name
notes	TEXT		Notes
transition_data	JSONB	DEFAULT ’{}‘	Transition data
transition_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Transition date
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp

Indexes: idx_workflow_transitions_instance_id, idx_workflow_transitions_definition_id, idx_workflow_transitions_states, idx_workflow_transitions_user_id, idx_workflow_transitions_date

workflow_states

Available states within workflow definitions.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
definition_id	INTEGER	FK -> workflow_definitions.id, ON DELETE CASCADE	Definition reference
state_name	VARCHAR(50)	NOT NULL	State name
display_name	VARCHAR(100)		Display name
description	TEXT		Description
state_type	VARCHAR(20)		INITIAL, NORMAL, FINAL, ERROR
color_code	VARCHAR(7)		Hex color
icon	VARCHAR(50)		Icon name
is_active	BOOLEAN	DEFAULT TRUE	Active flag
requires_approval	BOOLEAN	DEFAULT FALSE	Requires approval
auto_transition	BOOLEAN	DEFAULT FALSE	Auto transition
auto_transition_delay	INTEGER		Delay in seconds
notify_on_enter	BOOLEAN	DEFAULT FALSE	Notify on enter
notification_template	VARCHAR(100)		Notification template
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_workflow_states_definition_id, idx_workflow_states_name, idx_workflow_states_type

workflow_rules

Business rules for workflows.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
definition_id	INTEGER	FK -> workflow_definitions.id, ON DELETE CASCADE	Definition reference
rule_name	VARCHAR(100)		Rule name
rule_type	VARCHAR(50)		CONDITION, VALIDATION, ACTION
trigger_state	VARCHAR(50)		Trigger state
condition_expression	TEXT		Condition expression
action_type	VARCHAR(50)		Action type
action_config	JSONB		Action configuration
is_active	BOOLEAN	DEFAULT TRUE	Active flag
priority	INTEGER	DEFAULT 0	Priority
on_error_action	VARCHAR(20)	DEFAULT ‘LOG’	LOG, IGNORE, FAIL
created_by	INTEGER	FK -> users.id	Created by
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_workflow_rules_definition_id, idx_workflow_rules_trigger_state, idx_workflow_rules_is_active

workflow_assignments

Task assignments in workflows.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
instance_id	INTEGER	FK -> workflow_instances.id, ON DELETE CASCADE	Instance reference
state_name	VARCHAR(50)		State name
assigned_to_user	INTEGER	FK -> users.id	Assigned user
assigned_to_role	VARCHAR(50)		Assigned role
assigned_by	INTEGER	FK -> users.id	Assigned by
assignment_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Assignment date
task_name	VARCHAR(200)		Task name
task_description	TEXT		Task description
priority	VARCHAR(20)	DEFAULT ‘MEDIUM’	LOW, MEDIUM, HIGH, URGENT
due_date	TIMESTAMP		Due date
status	VARCHAR(20)	DEFAULT ‘PENDING’	PENDING, IN_PROGRESS, COMPLETED, CANCELLED
started_at	TIMESTAMP		Started time
completed_at	TIMESTAMP		Completion time
completion_notes	TEXT		Completion notes
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_workflow_assignments_instance_id, idx_workflow_assignments_assigned_to, idx_workflow_assignments_status, idx_workflow_assignments_due_date

workflow_history

Detailed workflow event audit log.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
instance_id	INTEGER	FK -> workflow_instances.id, ON DELETE CASCADE	Instance reference
event_type	VARCHAR(50)		STATE_CHANGE, ASSIGNMENT, COMMENT, ERROR
event_description	TEXT		Event description
old_state	VARCHAR(50)		Old state
new_state	VARCHAR(50)		New state
user_id	INTEGER	FK -> users.id	User
user_name	VARCHAR(200)		User name
event_data	JSONB		Event data
event_timestamp	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Event timestamp
ip_address	VARCHAR(45)		IP address
user_agent	VARCHAR(500)		User agent
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp

Indexes: idx_workflow_history_instance_id, idx_workflow_history_event_type, idx_workflow_history_timestamp, idx_workflow_history_user_id

workflow_notifications

Notifications triggered by workflow events.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
instance_id	INTEGER	FK -> workflow_instances.id, ON DELETE CASCADE	Instance reference
notification_type	VARCHAR(50)		EMAIL, SMS, PUSH, WEBHOOK
recipient_type	VARCHAR(20)		USER, ROLE, EMAIL
recipient_identifier	VARCHAR(255)		Recipient identifier
subject	VARCHAR(500)		Subject
message	TEXT		Message
message_data	JSONB		Message data
status	VARCHAR(20)	DEFAULT ‘PENDING’	PENDING, SENT, FAILED
sent_at	TIMESTAMP		Sent time
error_message	TEXT		Error message
retry_count	INTEGER	DEFAULT 0	Retry count
max_retries	INTEGER	DEFAULT 3	Max retries
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Updated timestamp

Indexes: idx_workflow_notifications_instance_id, idx_workflow_notifications_status, idx_workflow_notifications_recipient

workflow_metrics

Performance metrics and analytics.

Column	Type	Constraints	Description
id	SERIAL	PK	Primary key
definition_id	INTEGER	FK -> workflow_definitions.id	Definition reference
instance_id	INTEGER	FK -> workflow_instances.id	Instance reference
metric_name	VARCHAR(100)		Metric name
metric_type	VARCHAR(50)		DURATION, COUNT, COST
metric_value	DECIMAL(15,4)		Metric value
metric_unit	VARCHAR(20)		Metric unit
state_name	VARCHAR(50)		State name
user_id	INTEGER	FK -> users.id	User
measurement_date	DATE		Measurement date
metric_data	JSONB		Metric data
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Created timestamp

Indexes: idx_workflow_metrics_definition_id, idx_workflow_metrics_instance_id, idx_workflow_metrics_name, idx_workflow_metrics_date

5. Operations Schema

(Tables include: ltl_bookings, ltl_pickup_jobs, consolidation_manifests, linehaul_dispatches, delivery_manifests, driver_manifests, manifest_shipments, dispatch_centers, dispatch_jobs, job_status_updates, pallet_tracking)

See the Operations diagram above for relationships. Each table follows the same pattern with primary keys, foreign keys, indexes, and audit timestamps.

6. Compliance Schema

(Tables include: compliance_categories, accreditations, staff_certifications, safety_incidents, training_programs, training_records, compliance_audit_trails, regulatory_inspections, compliance_checklists, checklist_assessments)

See the Compliance diagram above for relationships.

7. Staff Management Schema

(Tables include: user_profiles, certifications, schedules, time_records, leave_requests, leave_balances, performance_reviews, disciplinary_actions)

See the Staff Management diagram above for relationships.

Key Relationships

Multi-tenancy Chain

tenants → tenant_settings (1:1)
tenants → companies (1:N)
companies → users (1:N)
companies → customers (1:N)

User & Employee Chain

users → user_profiles (1:1)
user_profiles → certifications (1:N)
user_profiles → schedules (1:N)
user_profiles → time_records (1:N)
user_profiles → leave_requests (1:N)
user_profiles → performance_reviews (1:N)

Customer & Rates Chain

customers → customer_addresses (1:N)
customers → rate_cards (1:N)
rate_cards → rate_entries (1:N)
rate_entries → zones (N:1 for origin/destination)
rate_entries → service_levels (N:1)

Booking Flow

customers → bookings (1:N)
customers → quotes (1:N)
customers → ltl_bookings (1:N)
ltl_bookings → ltl_pickup_jobs (N:1)
ltl_bookings → consolidation_manifests (N:1)
consolidation_manifests → linehaul_dispatches (N:1)
ltl_bookings → delivery_manifests (N:1)

Asset Management Chain

companies → fleets (1:N)
fleets → vehicles (1:N)
vehicles → maintenance_records (1:N)
vehicles → vehicle_inspections (1:N)
vehicles → fuel_records (1:N)

Workflow Chain

workflow_definitions → workflow_instances (1:N)
workflow_definitions → workflow_states (1:N)
workflow_definitions → workflow_rules (1:N)
workflow_instances → workflow_transitions (1:N)
workflow_instances → workflow_assignments (1:N)
workflow_instances → workflow_history (1:N)

Indexes and Performance

Composite Indexes

Table	Index Name	Columns	Purpose
rate_entries	idx_rate_entries_route_service	origin_zone_id, destination_zone_id, service_level_id	Rate lookups
bookings	idx_bookings_customer_status_date	customer_id, status, pickup_date DESC	Customer booking queries
zone_suburbs	idx_zone_suburbs_postcode_state	postcode, state_code	Postcode lookups

Partial Indexes (Active Records)

idx_customers_active WHERE is_active = TRUE
idx_zones_active WHERE is_active = TRUE
idx_service_levels_active WHERE is_active = TRUE
idx_rate_cards_active WHERE is_active = TRUE

Full-Text Search Indexes (GIN)

idx_customers_name_search ON customers (name)
idx_zones_name_search ON zones (name)
idx_zone_suburbs_name_search ON zone_suburbs (suburb_name)

Check Constraints

Table	Constraint	Expression
rate_entries	chk_different_zones	origin_zone_id != destination_zone_id
bookings	chk_delivery_after_pickup	delivery_date >= pickup_date
rate_entries	chk_positive_rates	All rate columns > 0

Reference Data

States (Australia)

Code	Name
NSW	New South Wales
VIC	Victoria
QLD	Queensland
SA	South Australia
WA	Western Australia
TAS	Tasmania
NT	Northern Territory
ACT	Australian Capital Territory

Countries (20 supported)

Australia, New Zealand, USA, Canada, UK, China, Japan, South Korea, Singapore, Malaysia, Thailand, India, Indonesia, Philippines, Vietnam, France, Germany, Italy, Spain, Netherlands

Status Values

Booking Status

PENDING, CONFIRMED, DISPATCHED, IN_TRANSIT, DELIVERED, CANCELLED

Quote Status

PENDING, SENT, ACCEPTED, DECLINED, EXPIRED

Workflow Status

ACTIVE, COMPLETED, CANCELLED, ERROR

Vehicle Status

AVAILABLE, IN_USE, MAINTENANCE, OUT_OF_SERVICE, RETIRED

Leave Status

PENDING, APPROVED, REJECTED, CANCELLED

Audit and Timestamps

All tables include:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

An automatic trigger updates updated_at on every row modification:

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_[table]_updated_at
    BEFORE UPDATE ON [table]
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Most tables also include:

created_by (VARCHAR or INTEGER FK -> users.id)
updated_by (VARCHAR or INTEGER FK -> users.id)

Summary Statistics

Category	Count
Total Tables	69+
Total Indexes	100+
Total Foreign Keys	150+
Check Constraints	5+
Audit Triggers	69+
Database Views	2

File Locations

Schema SQL files: exports/01_core_schema.sql through exports/07_staff_management_schema.sql
SQLAlchemy models: models/ directory
Migration files: migrations/ directory

Last updated: December 2024