PostgreSQL Schema

The relational schema lives in the main PostgreSQL database and is managed via sqlx migrations.

Migration file: crates/apollon-db/migrations/20260609000000_initial.up.sql

Entity Relationship

┌─────────────────┐      ┌──────────────────────┐
│  cargo_categories│      │       clients        │
│─────────────────│      │──────────────────────│
│  id (PK)        │◄──┐  │  id (PK)             │
│  name (UNIQUE)  │   │  │  name, email, phone  │
│  description    │   │  │  street, house_number │
│  created_at     │   │  │  postal_code, city    │
│  updated_at     │   │  │  country              │
└─────────────────┘   │  │  additional_info      │
                      │  └──────────┬─────────────┘
                      │             │
                      │             │ ON DELETE CASCADE
                      │             ▼
                      │  ┌──────────────────────┐
                      │  │   contact_persons     │
                      │  │──────────────────────│
                      │  │  id (PK)             │
                      │  │  name, email, phone  │
                      │  │  position            │
                      │  │  is_primary          │
                      │  │  client_id (FK)      │
                      │  └──────────────────────┘
                      │
                      │  ┌──────────────────────┐
                      └──┤       trips           │
                         │──────────────────────│
                         │  id (PK)             │
                         │  start_time          │
                         │  end_time            │
                         │  start_latitude      │
                         │  start_longitude     │
                         │  end_latitude        │
                         │  end_longitude       │
                         │  cargo               │
                         │  category_id (FK)    │──► ON DELETE RESTRICT
                         │  weight              │
                         │  sender_id (FK)      │──► ON DELETE RESTRICT
                         │  receiver_id (FK)    │──► ON DELETE RESTRICT
                         │  status (enum)       │
                         │  log_gps             │
                         └──────────────────────┘

Enum Types

trip_status

CREATE TYPE trip_status AS ENUM ('ACTIVE', 'COMPLETED', 'CANCELLED');

Tables

clients

Stores sender and receiver organizations for cargo trips.

CREATE TABLE clients (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    email           TEXT NOT NULL,
    phone           TEXT NOT NULL,
    street          TEXT NOT NULL,
    house_number    TEXT NOT NULL,
    postal_code     TEXT NOT NULL,
    city            TEXT NOT NULL,
    country         TEXT NOT NULL,
    additional_info TEXT
);

Column	Type	Constraints	Description
`id`	`UUID`	PK, auto-generated	Unique identifier
`name`	`TEXT`	NOT NULL	Company or person name
`email`	`TEXT`	NOT NULL	Contact email
`phone`	`TEXT`	NOT NULL	Contact phone
`street`	`TEXT`	NOT NULL	Street name
`house_number`	`TEXT`	NOT NULL	House/building number
`postal_code`	`TEXT`	NOT NULL	Postal/ZIP code
`city`	`TEXT`	NOT NULL	City
`country`	`TEXT`	NOT NULL	Country
`additional_info`	`TEXT`	nullable	Free-text notes

contact_persons

Contact persons associated with a client. Supports a primary contact designation.

CREATE TABLE contact_persons (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    email       TEXT NOT NULL,
    phone       TEXT NOT NULL,
    position    TEXT NOT NULL,
    is_primary  BOOLEAN NOT NULL DEFAULT false,
    client_id   UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE
);

Column	Type	Constraints	Description
`id`	`UUID`	PK, auto-generated	Unique identifier
`name`	`TEXT`	NOT NULL	Full name
`email`	`TEXT`	NOT NULL	Email address
`phone`	`TEXT`	NOT NULL	Phone number
`position`	`TEXT`	NOT NULL	Job title / role
`is_primary`	`BOOLEAN`	NOT NULL, default `false`	Primary contact flag
`client_id`	`UUID`	FK to `clients(id)`, ON DELETE CASCADE	Parent client

When a client is deleted, all associated contact persons are automatically removed (CASCADE).

cargo_categories

Categories for classifying cargo types. Names must be unique.

CREATE TABLE cargo_categories (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Column	Type	Constraints	Description
`id`	`UUID`	PK, auto-generated	Unique identifier
`name`	`TEXT`	NOT NULL, UNIQUE	Category name
`description`	`TEXT`	nullable	Optional description
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`	Last update timestamp

trips

Core trip records linking senders, receivers, cargo categories, and GPS tracking.

CREATE TABLE trips (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    start_time      TIMESTAMPTZ NOT NULL,
    end_time        TIMESTAMPTZ,
    start_latitude  DOUBLE PRECISION NOT NULL,
    start_longitude DOUBLE PRECISION NOT NULL,
    end_latitude    DOUBLE PRECISION,
    end_longitude   DOUBLE PRECISION,
    cargo           TEXT NOT NULL,
    category_id     UUID NOT NULL REFERENCES cargo_categories(id) ON DELETE RESTRICT,
    weight          DOUBLE PRECISION NOT NULL,
    sender_id       UUID NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
    receiver_id     UUID NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
    status          trip_status NOT NULL DEFAULT 'ACTIVE',
    log_gps         BOOLEAN NOT NULL DEFAULT false
);

Column	Type	Constraints	Description
`id`	`UUID`	PK, auto-generated	Unique identifier
`start_time`	`TIMESTAMPTZ`	NOT NULL	Trip start timestamp
`end_time`	`TIMESTAMPTZ`	nullable	Trip end timestamp (set on completion)
`start_latitude`	`DOUBLE PRECISION`	NOT NULL	Starting position latitude
`start_longitude`	`DOUBLE PRECISION`	NOT NULL	Starting position longitude
`end_latitude`	`DOUBLE PRECISION`	nullable	End position latitude
`end_longitude`	`DOUBLE PRECISION`	nullable	End position longitude
`cargo`	`TEXT`	NOT NULL	Cargo description
`category_id`	`UUID`	FK to `cargo_categories(id)`, ON DELETE RESTRICT	Cargo type
`weight`	`DOUBLE PRECISION`	NOT NULL	Cargo weight
`sender_id`	`UUID`	FK to `clients(id)`, ON DELETE RESTRICT	Sending client
`receiver_id`	`UUID`	FK to `clients(id)`, ON DELETE RESTRICT	Receiving client
`status`	`trip_status`	NOT NULL, default `'ACTIVE'`	Trip lifecycle state
`log_gps`	`BOOLEAN`	NOT NULL, default `false`	Enable GPS tracking

Foreign key behavior:

category_id -- ON DELETE RESTRICT: Cannot delete a category that has trips
sender_id -- ON DELETE RESTRICT: Cannot delete a client used as sender
receiver_id -- ON DELETE RESTRICT: Cannot delete a client used as receiver

Indexes

CREATE INDEX idx_trips_status ON trips(status);
CREATE INDEX idx_trips_sender_id ON trips(sender_id);
CREATE INDEX idx_trips_receiver_id ON trips(receiver_id);
CREATE INDEX idx_contact_persons_client_id ON contact_persons(client_id);

Index	Table	Column(s)	Purpose
`idx_trips_status`	`trips`	`status`	Filter trips by status (ACTIVE/COMPLETED/CANCELLED)
`idx_trips_sender_id`	`trips`	`sender_id`	Look up trips by sender client
`idx_trips_receiver_id`	`trips`	`receiver_id`	Look up trips by receiver client
`idx_contact_persons_client_id`	`contact_persons`	`client_id`	Look up contacts by client

Notes

All primary keys use gen_random_uuid() (PostgreSQL 13+ built-in, no pgcrypto needed)
All timestamps use TIMESTAMPTZ (timezone-aware)
GPS location data is not in this database -- it lives in TimescaleDB

Entity Relationship​

Enum Types​

trip_status​

Tables​

clients​

contact_persons​

cargo_categories​

trips​

Indexes​

Notes​