Domain Model

This page describes the full data model of pakAG as defined in schema.sql. All seven tables, their fields, foreign keys, and the business rules encoded in the application layer are documented here.

Entity overview

addresses ──────────────────────────────────────┐
                                                 │ address_id
users ──┬──────────────────────────────────── packages ──┬── package_status_logs
        │ assigned_to / created_by                       │
        │                                                 │ package_id
        └── routes ──── route_stops ───────────┘
              │ route_id    │ package_id
              └─────────────┘
                                    tokens (user_id OR package_id)

users          1 ──< routes          (one user, many routes — one per date)
routes         1 ──< route_stops     (one route, many stops)
packages       1 ──< route_stops     (one package, appears in many stops across routes)
packages       1 ──< package_status_logs
packages       1 ──  addresses       (each package has one delivery address)
packages       1 ──< tokens          (tracking token per package)
users          1 ──< tokens          (refresh / reset / activation tokens per user)
users          1 ──< packages        (assigned_to — distributor)
users          1 ──< packages        (created_by — admin)

Entities

addresses

Stores geocoded delivery addresses. Coordinates are resolved at package creation time via the Google Geocoding API.

[!NOTE] Application code aliases latitude/longitude to lat/lng with SQL AS clauses. When writing new queries, always use latitude AS lat to stay consistent with TypeScript types.

users

Represents both admins and distributors. The role column is the single source of truth for authorization.

Roles

Business rule: An admin cannot delete themselves (DELETE /api/users/remove enforces this check in the service layer).

Account activation flow

1. Admin creates user (is_active = FALSE, password = DEFAULT_USER_PASSWORD hashed)
2. Backend generates activate_account_token (24h TTL)
3. Email sent to user with activation link
4. User submits new password → is_active = TRUE, token revoked

packages

The central entity. A package moves through a defined lifecycle and is optionally assigned to a distributor and placed on a route.

Package status lifecycle

                ┌──────────────────────────────────────────┐
                │                                          │
  [created] → pending → assigned → in_transit → delivered │
                                        │                  │
                                        ├──→ undelivered   │
                                        │                  │
                                        └──→ failed        │
                                                           │
                └──────────────────────────────────────────┘

Side effects on status change (handled by packageStatusSideEffects.service.ts):

Every transition is also recorded as a row in package_status_logs.

tokens

A unified token table handling four distinct token types. Each token is an opaque hex string.

Token types

[!NOTE] Tracking tokens are public — they are embedded in emails sent to recipients and can be shared freely. They reveal only package status and address, not internal IDs.

package_status_logs

Immutable audit log of every package status change.

Business rule: Rows in this table are never updated or deleted (except via cascading package deletion). They form an append-only audit trail.

routes

A route is a single distributor’s delivery plan for a specific calendar date. Each distributor can have at most one route per day (enforced by UNIQUE KEY uq_route_user_date).

Route statuses

Business rules:

• Route creation calls the Google Directions API with optimize:true to reorder stops for minimum travel time.
• Maximum 20 stops per route.
• “Carryover” packages — packages with status assigned from previous days belonging to the distributor — are automatically prepended to new routes.
• If a distributor has unfinished stops from a previous day, GET /api/routes/continueFromPast returns them. POST /api/routes/continueFromPast migrates them into today’s route.

route_stops

Junction table linking a route to its ordered package stops, with arrival time tracking.

UNIQUE KEY uq_stop_order (route_id, stop_order) ensures no two stops share the same position in a route.

Business rule: Stop reordering is available via PATCH /api/stops/reorder (admin only). The distributor records their actual arrival time via PATCH /api/stops/updateArrival.

Database indexes