Itinera — Database Schema

Generated from prisma/schema.prisma · Last updated: February 2026

---

Entity Relationship Diagram

erDiagram

  users {
    uuid    id                         PK
    string  email                      UK
    string  password_hash
    string  name
    enum    role                       "ADMIN | DISPATCHER"
    bool    is_active
    bool    must_change_password
    string  two_factor_secret
    bool    two_factor_enabled
    string  two_factor_recovery_codes  "JSON array (hashed)"
    datetime created_at
    datetime updated_at
  }

  invites {
    uuid     id             PK
    string   code           UK
    string   email          "optional — restricts to specific email"
    uuid     created_by_id  FK
    uuid     used_by_id     FK "unique — one invite per user"
    datetime used_at
    datetime expires_at
    datetime created_at
  }

  routes {
    uuid     id              PK
    string   route_number    UK
    string   name
    enum     status          "DRAFT | ACTIVE | COMPLETED | CANCELLED"
    string   origin
    string   destination
    string   equipment_type
    string   driver_id       "Samsara ID (no local FK)"
    string   driver_name
    string   vehicle_id      "Samsara ID (no local FK)"
    string   vehicle_name
    text     route_url
    text     route_polyline
    string   notes
    uuid     created_by_id   FK
    datetime created_at
    datetime updated_at
  }

  waypoints {
    uuid     id          PK
    uuid     route_id    FK
    int      sequence    "order: 1, 2, 3…"
    enum     type        "ORIGIN | STOP | DESTINATION | FUEL_STOP"
    string   name
    string   address
    decimal  latitude    "10,7"
    decimal  longitude   "10,7"
    string   notes
    datetime created_at
    datetime updated_at
  }

  route_deviations {
    uuid     id                  PK
    uuid     route_id            FK
    datetime started_at
    datetime ended_at            "null = still off-route"
    decimal  start_latitude      "10,7"
    decimal  start_longitude     "10,7"
    string   start_address
    decimal  max_distance_miles  "6,2"
    string   vehicle_name
    string   driver_name
    datetime created_at
    datetime updated_at
  }

  activity_logs {
    uuid     id          PK
    uuid     route_id    FK "nullable — SetNull on delete"
    uuid     user_id     FK
    string   action      "e.g. ROUTE_CREATED, DISPATCHED"
    json     details
    datetime created_at
  }

  fuel_stops {
    uuid     id              PK
    int      store_number    "UK with effective_date"
    string   network         "LOVES | PILOT | TA | PETRO | OTHER"
    string   city
    string   state
    decimal  latitude        "10,7"
    decimal  longitude       "10,7"
    decimal  best_price      "6,4"
    date     effective_date  "UK with store_number"
    datetime created_at
  }

  fuel_transactions {
    uuid     id               PK
    string   vehicle_id       "Samsara ID (no local FK)"
    string   driver_name
    uuid     route_id         FK "nullable — SetNull on delete"
    datetime transaction_date
    string   network          "LOVES | PILOT | TA | PETRO | OTHER"
    string   location_name
    string   city
    string   state
    decimal  latitude         "10,7"
    decimal  longitude        "10,7"
    decimal  gallons          "8,3"
    decimal  price_per_gallon "6,4"
    decimal  total_amount     "10,2"
    int      odometer
    string   card_number      "last 4 digits only"
    string   source           "COMPASS_CSV | PILOT_SFTP | EFS_API | etc."
    datetime imported_at
    datetime created_at
  }

  notifications {
    uuid     id           PK
    enum     type         "FUEL_LOW | ROUTE_DEVIATION | ROUTE_COMPLETED"
    uuid     route_id     FK "nullable"
    string   title
    string   message
    json     metadata     "fuelPercent, vehicleName, etc."
    datetime resolved_at  "null = unresolved (system-level)"
    datetime created_at
  }

  notification_reads {
    uuid     id                PK
    uuid     notification_id   FK
    uuid     user_id           FK
    datetime read_at
  }

  route_documents {
    uuid     id             PK
    uuid     route_id       FK
    uuid     uploaded_by_id FK
    string   file_name
    string   file_key       "Minio object key"
    int      file_size      "bytes"
    string   mime_type
    string   document_type  "PERMIT | BOL | OTHER"
    datetime created_at
  }

  app_settings_v2 {
    string   id               PK "always 'default' — singleton"
    string   company_name
    string   samsara_api_key
    string   telegram_chat_id
    datetime created_at
    datetime updated_at
  }

  vehicle_profiles {
    uuid     id                  PK
    string   samsara_vehicle_id  UK
    int      height_inches       "nullable"
    int      width_inches        "nullable"
    int      length_feet         "nullable"
    int      weight_lbs          "nullable"
    int      axle_count          "nullable"
    bool     hazmat              "default false"
    string   equipment_type      "nullable"
    datetime created_at
    datetime updated_at
  }

  %% Relationships

  users              ||--o{ invites             : "creates"
  users              ||--o| invites             : "uses"
  users              ||--o{ routes              : "creates"
  users              ||--o{ activity_logs       : "performs"
  users              ||--o{ notification_reads  : "reads"
  users              ||--o{ route_documents     : "uploads"

  routes             ||--o{ waypoints           : "has"
  routes             ||--o{ route_deviations    : "has"
  routes             ||--o{ activity_logs       : "logged on"
  routes             ||--o{ notifications       : "triggers"
  routes             ||--o{ fuel_transactions   : "has"
  routes             ||--o{ route_documents     : "has"

  notifications      ||--o{ notification_reads  : "read by"

---

Tables at a Glance

Table	Rows Represent	Key Relations
users	Dispatchers and admins	Creates routes, invites; tracked in activity logs and notification reads
invites	Single-use registration codes	Created by a user; optionally consumed by a user
routes	A truck route from origin to destination	Has waypoints, deviations, notifications, activity logs, fuel transactions, documents
waypoints	Ordered stops on a route	Belongs to one route; cascade deleted with route
route_deviations	Off-route events detected by cron	Belongs to one route; cascade deleted with route
activity_logs	Audit trail of every user action	Belongs to a user; optionally linked to a route (SetNull on delete)
fuel_stops	Fuel station prices (daily upload, multi-network)	Standalone — queried by proximity to route waypoints
fuel_transactions	Actual fuel card transaction records	Optionally linked to a route; source of truth for analytics
notifications	System alerts (fuel low, deviation, completion)	Optionally linked to a route; has per-user read records
notification_reads	Tracks which user has read which notification	Join table between notifications and users
route_documents	Permit scans, BOLs, and other files stored in Minio	Belongs to a route and an uploader
vehicle_profiles	Truck dimensions and attributes for HERE routing	Standalone — joined to routes via samsara_vehicle_id (no FK)
app_settings_v2	Singleton company config	Standalone — always a single row with id = 'default'

---

Enums

Enum	Values
UserRole	ADMIN, DISPATCHER
RouteStatus	DRAFT, ACTIVE, COMPLETED, CANCELLED
WaypointType	ORIGIN, STOP, DESTINATION, FUEL_STOP
NotificationType	FUEL_LOW, ROUTE_DEVIATION, ROUTE_COMPLETED
Fuel network (string)	LOVES, PILOT, TA, PETRO, OTHER — used in fuel_stops and fuel_transactions

---

Key Constraints & Indexes

Table	Constraint / Index
users	email unique
invites	code unique; used_by_id unique (one invite per registered user)
routes	route_number unique
waypoints	(route_id, sequence) unique — no duplicate positions per route
fuel_stops	(store_number, effective_date) unique; indexes on effective_date, (latitude, longitude)
fuel_transactions	Indexes on vehicle_id, transaction_date, route_id
notifications	Indexes on route_id, created_at, type, resolved_at; vehicle_id index (Phase 3)
notification_reads	(notification_id, user_id) unique — one read record per user per notification
vehicle_profiles	samsara_vehicle_id unique
routes	Indexes on (status, created_at), vehicle_id, driver_id, status (Phase 3)

---

Cascade Behavior

Deleted record	Effect on related records
Route deleted	waypoints, route_deviations, notifications, route_documents → cascade deleted
Route deleted	activity_logs.route_id, fuel_transactions.route_id → set to null (records preserved)
Notification deleted	notification_reads → cascade deleted
User deleted	notification_reads → cascade deleted

---

Notes

• routes.driver_id / vehicle_id are Samsara IDs stored as strings — fleet data lives in Samsara, not the local DB.
• notifications.resolved_at is system-level resolution (e.g. fuel recovered). Separate from per-user notification_reads.
• fuel_transactions.route_id is nullable — not every fuel fill-up will map to a route, especially if the driver fuels up off-duty or before dispatch.
• route_documents.file_key is the Minio object key — the app constructs the full URL from this using the Minio endpoint + bucket config.
• vehicle_profiles is a standalone enrichment table — routes.vehicle_id is NOT a FK to this table. Join via samsara_vehicle_id when truck dimensions are needed for HERE routing requests. FK normalization planned for Phase 8 multitenancy.
• notifications.vehicle_id (Phase 3) provides a direct column for vehicle reference instead of requiring JSON metadata parsing. Backfilled from existing metadata.vehicleId for FUEL_LOW notifications.
• PostGIS is permanently enabled via custom itinera-postgres:18-postgis Docker image (not ephemeral). Built on postgres:18-alpine with PostGIS 3.6.1 installed via apk add --no-cache postgis; Dockerfile at /data/coolify/databases/pg-postgis/Dockerfile on production droplet. Adds geometry and geography types plus spatial functions like ST_DWithin, ST_Intersects. Survives container rebuilds; PGDATA path preserved at /var/lib/postgresql/18/docker.
• app_settings (no _v2) was a legacy key-value table, now removed. All config lives in app_settings_v2.

---

Schema Roadmap

Changes are grouped by when they should be implemented.

✅ Applied — February 2026

• Removed routes.samsara_route_id — deprecated, Samsara route pushing was replaced by Google Maps URL generation
• Removed routes.last_fuel_notified_at and routes.last_fuel_notified_percent — redundant; fuel alert deduplication is handled by notifications.resolved_at in lib/notifications.ts
• Removed legacy app_settings table — replaced by app_settings_v2
• Added fuel_stops.network — prepares for multi-network fuel pricing beyond Love's (Pilot, TA, Petro)

⏳ Phase 3 — Schema Foundations

Route enrichment:

• Add total_distance_miles (Decimal 8,2 nullable) and toll_cost_estimated (Decimal 8,2 nullable) to routes — populated by HERE Routing API in Phase 4

Vehicle profiles (standalone lookup — no FK from routes):

• Add vehicle_profiles table — id (uuid PK), samsara_vehicle_id (String, unique), height_inches (Int nullable), width_inches (Int nullable), length_feet (Int nullable), weight_lbs (Int nullable), axle_count (Int nullable), hazmat (Boolean default false), equipment_type (String nullable), created_at, updated_at
• routes.vehicle_id stays as a loose Samsara ID string — join to vehicle_profiles via samsara_vehicle_id when truck dimensions are needed for HERE routing. FK normalization deferred to Phase 8 multitenancy.

Permits (placeholder — schema TBD):

• permits table reserved — exact fields to be designed when working with state DOT permit systems. Will include at minimum: route_id FK, state, permit_number, document_key (nullable, for Minio in Phase 6)

Notification improvements:

• Add vehicle_id (String nullable) to notifications — currently fuel alerts reference vehicles only via JSON metadata; explicit column improves querying and deduplication
• Add indexes on notifications.type and notifications.resolved_at — cron queries these every 30 seconds
• Backfill vehicle_id from existing metadata.vehicleId JSON for FUEL_LOW notifications

Route indexes (performance):

• Add composite index (status, created_at) on routes
• Add index on routes.vehicle_id, routes.driver_id, routes.status

User ↔ Samsara linking:

• Add samsara_driver_id (String nullable) and samsara_vehicle_id (String nullable) to users — needed for DRIVER/OWNER_OPERATOR scoping in Phase 9

PostGIS:

• Enable PostGIS extension — CREATE EXTENSION IF NOT EXISTS postgis; — needed for bridge/tunnel spatial queries (Phase 4) and state mileage calculations (Phase 5). Zero overhead if unused, built into PostgreSQL 18.

⏳ Phase 5 — Fuel card integration

• Add fuel_transactions table — stores actual card transaction data from Compass CSV, Pilot SFTP, EFS API, WEX. Source of truth for the analytics pages replacing mock data. Fields: vehicle_id, driver_name, route_id (nullable), transaction_date, network, location fields, gallons, price_per_gallon, total_amount, odometer, card_number (last 4), source.

⏳ Phase 6 — Minio file storage

• Add route_documents table — tracks permit scans, BOLs, and exported PDFs stored in Minio. Fields: route_id, uploaded_by_id, file_name, file_key (Minio object key), file_size, mime_type, document_type (PERMIT / BOL / OTHER).

⏳ Phase 8 — Multi-tenancy

• Add companies table — id, name, slug, subscription_tier, created_at
• Add company_id FK to: users, routes, invites, notifications, activity_logs, fuel_stops, fuel_transactions, route_documents, vehicle_profiles, permits
• app_settings_v2 becomes per-company — either add company_id FK or fold settings into the companies table
• All Prisma queries will need where: { companyId } scoping — centralize through service functions now to make this migration easier later
• Consider normalizing routes.vehicle_id as FK to vehicle_profiles at this point