Query Patterns

All database queries are implemented in the apollon-db crate using sqlx with compile-time checked SQL.

Source: crates/apollon-db/src/queries/

Two Pool Instances

The API passes the correct pool to each query function:

Pool	Variable	Database	Tables
db_pool	PgPool	PostgreSQL	clients, contact_persons, cargo_categories, trips
tsdb_pool	TsdbPool(PgPool)	TimescaleDB	gps_locations

GPS queries that also need trip data (e.g., find_active_trip_for_gps) use the main db_pool since the trips table lives in PostgreSQL.

Query Functions

sqlx Patterns

All queries use sqlx::query_as with explicit SQL and positional parameters ($1, $2, ...):

sqlx::query_as::<_, ModelType>("SELECT ... FROM table WHERE id = $1")
    .bind(id)
    .fetch_one(pool)    // Returns Result<T, Error>
    .await

Fetch methods:

Method	Returns	Use Case
fetch_one(pool)	Result<T, Error>	Exactly one row expected (INSERT RETURNING, UPDATE RETURNING)
fetch_optional(pool)	Result<Option<T>, Error>	Zero or one row (get by ID)
fetch_all(pool)	Result<Vec<T>, Error>	Multiple rows (list all)

GPS Queries

Source: crates/apollon-db/src/queries/gps.rs

Insert GPS Location

Inserts a GPS data point into the TimescaleDB hypertable:

pub async fn save_gps_location(
    pool: &PgPool,
    latitude: f64,
    longitude: f64,
    altitude: Option<f64>,
    speed_kmh: f64,
    speed_mps: f64,
    speed_mph: f64,
    speed_knots: f64,
    timestamp: DateTime<Utc>,
    trip_id: Option<Uuid>,
) -> Result<GpsLocation, sqlx::Error>

INSERT INTO gps_locations
  (latitude, longitude, altitude, speed_kmh, speed_mps, speed_mph, speed_knots, timestamp, trip_id)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING id, timestamp, latitude, longitude, altitude, speed_kmh, speed_mps, speed_mph, speed_knots, trip_id

GPS History

Fetches GPS locations within a time range, ordered chronologically:

pub async fn get_gps_history(
    pool: &PgPool,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<GpsLocation>, sqlx::Error>

SELECT id, timestamp, latitude, longitude, altitude, speed_kmh, speed_mps, speed_mph, speed_knots, trip_id
FROM gps_locations
WHERE timestamp >= $1 AND timestamp <= $2
ORDER BY timestamp ASC

Find Active Trip for GPS

Finds a trip with status = 'ACTIVE' and log_gps = true to auto-associate GPS data:

pub async fn find_active_trip_for_gps(pool: &PgPool) -> Result<Option<Uuid>, sqlx::Error>

SELECT id FROM trips
WHERE status = 'ACTIVE' AND log_gps = true
LIMIT 1

Note: This query runs against the PostgreSQL pool (not TimescaleDB) since the trips table lives there.

Trip Queries

Source: crates/apollon-db/src/queries/trips.rs

Create Trip

pub async fn create_trip(
    pool: &PgPool,
    start_latitude: f64, start_longitude: f64,
    cargo: &str, category_id: Uuid, weight: f64,
    sender_id: Uuid, receiver_id: Uuid, log_gps: bool,
) -> Result<Trip, sqlx::Error>

Sets start_time to Utc::now() automatically.

List All Trips

pub async fn get_all_trips(pool: &PgPool) -> Result<Vec<Trip>, sqlx::Error>

Returns all trips ordered by start_time DESC (newest first).

Get Trip by ID

pub async fn get_trip_by_id(pool: &PgPool, id: Uuid) -> Result<Option<Trip>, sqlx::Error>

Update Trip

pub async fn update_trip(
    pool: &PgPool, id: Uuid,
    cargo: &str, category_id: Uuid, weight: f64,
    sender_id: Uuid, receiver_id: Uuid,
    status: TripStatus, log_gps: bool,
) -> Result<Trip, sqlx::Error>

Uses an explicit cast $7::trip_status for the enum parameter.

Complete Trip

Sets the end time, end coordinates, and status to COMPLETED:

pub async fn complete_trip(
    pool: &PgPool, id: Uuid,
    end_latitude: f64, end_longitude: f64,
) -> Result<Trip, sqlx::Error>

Delete Trip

Uses a transaction to delete associated GPS locations first, then the trip:

pub async fn delete_trip(pool: &PgPool, id: Uuid) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    sqlx::query("DELETE FROM gps_locations WHERE trip_id = $1")
        .bind(id).execute(&mut *tx).await?;

    sqlx::query("DELETE FROM trips WHERE id = $1")
        .bind(id).execute(&mut *tx).await?;

    tx.commit().await
}

Client Queries

Source: crates/apollon-db/src/queries/clients.rs

CRUD Operations

• create_client(pool, name, email, phone, street, ...) -- Insert new client
• get_all_clients(pool) -- List all clients ordered by name ASC
• get_client_by_id(pool, id) -- Get single client
• update_client(pool, id, name, email, phone, street, ...) -- Update all fields

Contact Person Management

• get_contacts_for_client(pool, client_id) -- Ordered by is_primary DESC, name ASC
• create_contact(pool, client_id, name, email, phone, position, is_primary) -- Uses transaction; if is_primary, first resets all other contacts for that client
• update_contact(pool, contact_id, client_id, ...) -- Same primary-contact logic
• delete_contact(pool, contact_id, client_id) -- Uses transaction; if the deleted contact was primary, auto-promotes the first remaining contact (by name)

Primary Contact Logic

When creating or updating a contact with is_primary = true, the query first clears the flag on all existing contacts for that client within a transaction:

if is_primary {
    sqlx::query("UPDATE contact_persons SET is_primary = false WHERE client_id = $1")
        .bind(client_id)
        .execute(&mut *tx)
        .await?;
}

Category Queries

Source: crates/apollon-db/src/queries/categories.rs

• create_category(pool, name, description) -- Trims whitespace from name before insert
• get_all_categories(pool) -- Ordered by name ASC
• get_category_by_id(pool, id) -- Get single category
• update_category(pool, id, name, description) -- Trims name, updates updated_at to Utc::now()
• delete_category(pool, id) -- Returns Ok(false) if trips reference the category (respects RESTRICT), Ok(true) on success

Safe Delete Pattern

The category delete function checks for references before attempting deletion, avoiding a database error from the ON DELETE RESTRICT constraint:

pub async fn delete_category(pool: &PgPool, id: Uuid) -> Result<bool, sqlx::Error> {
    let count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM trips WHERE category_id = $1")
        .bind(id).fetch_one(pool).await?;

    if count.0 > 0 {
        return Ok(false); // Category is in use
    }

    sqlx::query("DELETE FROM cargo_categories WHERE id = $1")
        .bind(id).execute(pool).await?;

    Ok(true)
}