Skip to content

erd.md

Latest commit

 

History

History
180 lines (155 loc) · 10.1 KB

erd.md

File metadata and controls

180 lines (155 loc) · 10.1 KB
Raw

ELMS — Entity Relationship Diagram

PostgreSQL schema. Types shown are Postgres types; *_minor money columns are integer minor units (cents) — no floating-point money anywhere. See TDD §6 for migration tooling and query-layer details.

1. Diagram

erDiagram
    STATIONS     ||--o{ LOCKERS : "contains"
    LOCKER_TYPES ||--o{ LOCKERS : "sizes"
    LOCKERS      ||--o{ PACKAGES : "stores (≤1 active)"
    USERS        ||--o{ PACKAGES : "stored_by (agent)"
    PACKAGE_TEMPLATES ||--o{ PACKAGES : "measured as (optional)"
    PACKAGES     ||--o{ PAYMENTS : "settled by (≤1 pending)"
    USERS        ||--o{ SESSIONS : "authenticates"
    USERS        ||--o{ PRICING_CONFIG : "updated_by"
    USERS        ||--o{ AUDIT_EVENTS : "actor (null at kiosk)"
    STATIONS     ||--o{ AUDIT_EVENTS : "concerns (optional)"
    LOCKERS      ||--o{ AUDIT_EVENTS : "concerns (optional)"
    PACKAGES     ||--o{ AUDIT_EVENTS : "concerns (optional)"

    USERS {
        uuid id PK
        text username UK "unique"
        text password_hash "bcrypt"
        user_role role "admin | agent"
        timestamptz created_at
    }

    SESSIONS {
        text token_hash PK "sha256 of random 256-bit token"
        uuid user_id FK
        timestamptz expires_at "24 h; indexed for cleanup"
        timestamptz created_at
    }

    STATIONS {
        uuid id PK
        text name
        text address
        timestamptz created_at
    }

    LOCKER_TYPES {
        uuid id PK
        text name UK "Small | Medium | Large | Tower XL"
        int width_mm "interior"
        int height_mm "interior"
        int depth_mm "interior"
        int dim_max_mm "generated — sides sorted descending"
        int dim_mid_mm "generated"
        int dim_min_mm "generated"
        bigint volume_mm3 "generated — smallest-fit ordering"
        timestamptz created_at
        timestamptz updated_at
    }

    PACKAGE_TEMPLATES {
        uuid id PK
        text name UK "Shoebox, Laptop / electronics, …"
        int length_mm "outer"
        int width_mm "outer"
        int height_mm "outer"
        boolean active "false retires it from the agent form"
        timestamptz created_at
        timestamptz updated_at
    }

    LOCKERS {
        uuid id PK
        uuid station_id FK
        uuid locker_type_id FK "interior dimensions live on the type"
        text label "unique per station, auto e.g. M-03"
        locker_status status "available | occupied | out_of_service"
        timestamptz created_at
        timestamptz updated_at
    }

    PACKAGES {
        uuid id PK
        uuid locker_id FK
        uuid stored_by FK "agent user"
        uuid template_id FK "nullable — null when custom"
        int length_mm "frozen at store time"
        int width_mm "frozen at store time"
        int height_mm "frozen at store time"
        text customer_name "nullable in DB for legacy rows; required at the API"
        text customer_contact "nullable in DB for legacy rows; required at the API — phone/email for external notify"
        text pickup_code_hash "bcrypt of 6-digit code"
        smallint failed_attempts "reset on success"
        timestamptz blocked_until "nullable — set after 5 fails"
        package_status status "stored | retrieved"
        timestamptz stored_at "charge clock starts (L3)"
        timestamptz retrieved_at "nullable"
        bigint storage_charge_minor "nullable — final, set at retrieval"
        text currency "copied from config at retrieval"
    }

    PAYMENTS {
        uuid id PK
        uuid package_id FK
        bigint amount_minor "fixed at quote time"
        text currency
        payment_status status "pending | paid | expired"
        text method "mock"
        timestamptz expires_at "quote TTL: 15 min"
        timestamptz paid_at "nullable"
        timestamptz created_at
    }

    PRICING_CONFIG {
        smallint id PK "CHECK id = 1 — single row"
        bigint x_rate_minor "base daily rate X, minor units"
        text currency "ISO 4217, default USD"
        uuid updated_by FK "nullable"
        timestamptz updated_at
    }

    AUDIT_EVENTS {
        bigint id PK "bigserial — insertion order"
        text event_type "package_stored, package_retrieved, pickup_attempt_failed, locker_created, pricing_changed, …"
        uuid actor_user_id FK "nullable — null for kiosk actions; from session, never client input"
        uuid station_id FK "nullable — ON DELETE SET NULL"
        uuid locker_id FK "nullable — ON DELETE SET NULL"
        uuid package_id FK "nullable — ON DELETE SET NULL"
        jsonb details "labels/names snapshot, old→new for edits — never pickup codes"
        timestamptz occurred_at
    }
Loading

2. Enums

CREATE TYPE user_role      AS ENUM ('admin', 'agent');
CREATE TYPE locker_status  AS ENUM ('available', 'occupied', 'out_of_service');
CREATE TYPE package_status AS ENUM ('stored', 'retrieved');
CREATE TYPE payment_status AS ENUM ('pending', 'paid', 'expired');

There is no size enum. locker_size existed until migration 0003 dropped it: a three-value rank cannot say whether a 1200 mm box enters a 600 mm locker, which is the only question allocation actually asks. Migration 0002 adds its replacement alongside and backfills; 0003 removes the old column and the type once nothing reads them — expand then contract, so no revision of the tree is left uncompilable.

2a. Fit and smallest-fit, in millimetres

A package fits a locker when every package side, sorted descending, clears the corresponding sorted locker side by ClearanceMM = 20:

sort(pkg)[i] + 20 ≤ sort(locker_interior)[i]    for i in 0..2

Sorting both triples is what allows the package to be rotated into any of the six axis-aligned orientations, and it makes the order the agent typed the numbers in irrelevant. locker_types.dim_max_mm / dim_mid_mm / dim_min_mm are generated columns holding that sort, so the allocation query compares three integers instead of sorting per row inside the locking SELECT; the package side is sorted and clearance-added in Go before the query runs, keeping the sort rule in one place.

Among the lockers that fit, the smallest is the one with the least volume_mm3, then the lowest label. For a fixed package, least locker volume and least wasted volume are the same ordering.

3. Constraints and indexes that carry the design

Constraint / index Purpose
UNIQUE (station_id, label) on lockers Human-readable labels are the kiosk lookup key within a station
INDEX (station_id, status, locker_type_id) on lockers Backs the allocation query (L1); the dimension predicate resolves on the joined locker_types row
UNIQUE (name) on locker_types, package_templates The name is what an admin and an agent identify a catalog entry by
CHECK (… BETWEEN 1 AND 5000) on every *_mm column Non-physical and absurd measurements never reach the fit rule
FK … ON DELETE RESTRICT from lockers.locker_type_id, packages.template_id A catalog entry with history cannot be deleted out from under it; package_templates.active = false is the retire path
UNIQUE (locker_id) WHERE status = 'stored' on packages (partial) L4 backstop: the database physically cannot hold two active packages in one locker, regardless of application bugs
UNIQUE (package_id) WHERE status = 'pending' on payments (partial) At most one open quote per package; re-quote after expiry creates a new row
INDEX (expires_at) on sessions Expired-session sweep
INDEX (status, stored_at) on packages Dashboard dwell/accrual queries
CHECK (id = 1) on pricing_config Enforced single row
INDEX (occurred_at) on audit_events Activity screen is reverse-chronological
INDEX (event_type, occurred_at), INDEX (station_id, occurred_at) on audit_events The two Activity filters (FR-17)

4. Modeling notes

  • Locker ↔ package is 1-to-many over time, 1-to-≤1 at any instant. Retrieved packages keep their locker_id, preserving history for receipts and the dashboard; the partial unique index constrains only stored rows.
  • Pickup codes are never stored in plaintext. Only a bcrypt hash is kept; the code appears exactly once in the store-package API response. Validation is by station + locker label + code, so global code uniqueness is checked only against active packages at generation time (regenerate on the rare collision).
  • Charges are computed, then frozen. Dashboard shows live accrued charge (computed from stored_at); the final charge is fixed at quote time in payments.amount_minor and copied to packages.storage_charge_minor on retrieval. Rate changes never mutate history.
  • stored_by links a package to the agent who stored it — the inline copy of what audit_events records as package_stored.
  • audit_events is append-only and outlives what it references. No update or delete path exists in code; rows are written by the service layer inside the transaction of the action they record (TDD §5), so log and state cannot disagree. Entity FKs are ON DELETE SET NULL — deleting an empty locker (US-A5) must not take its history with it — and details carries the human-readable snapshot (label, station name, old→new values) that survives the null. Pickup codes never appear in details, attempted or real.
  • Station deletion is restricted (ON DELETE RESTRICT) while lockers exist; locker deletion restricted while any stored package references it.
  • Package dimensions are frozen, not referenced. A package stored from a template copies the three numbers and keeps template_id only as a label for the receipt and dashboard. Correcting "Shoebox" next month changes what the agent form offers, never what a package already in a locker was measured at — the same reasoning that fixes a charge at quote time rather than recomputing it from the current rate.
  • Locker dimensions are referenced, not copied. The opposite choice, deliberately: a locker's interior is a fact about the hardware, so correcting a type's measurements should correct every locker built from it. Nothing historical depends on it — allocation reads it live, and the package it produced already recorded its own dimensions.