Quotation System — Developer Guide

This guide covers the iDrv5-MyFR8 quotation system end-to-end: schema, API endpoints, rate computation, email workflow, and integration patterns for all portals (Operations, Customer, Public Widget).

---

1. Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│                        QUOTE SOURCES                                │
│                                                                     │
│  Operations Portal    Customer Portal    Public Widget    REST API   │
│  (source: OPERATIONS) (source: CUSTOMER) (source: PUBLIC) (source: API/SYSTEM)│
│        │                    │                  │               │     │
│        ▼                    ▼                  ▼               ▼     │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │              POST /api/quotes/                                │   │
│  │         (Primary insertion endpoint)                          │   │
│  └──────────────────────────┬───────────────────────────────────┘   │
│                             ▼                                       │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │              unified_quotes table                             │   │
│  │         (Single source of truth)                              │   │
│  └──────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘

All quotes from all sources flow into the unified_quotes table via POST /api/quotes/.

---

2. Database Schema — unified_quotes

Core Columns

Column	Type	Nullable	Default	Description
id	SERIAL	NOT NULL	auto	Primary key
quote_reference	VARCHAR(50)	NOT NULL	—	Unique reference (e.g., OQ-20260408-0001)
source_portal	VARCHAR(20)	NOT NULL	SYSTEM	Origin of the quote (see Source Portals)
status	VARCHAR(20)	—	PENDING	Workflow state (see Status Lifecycle)
status_notes	TEXT	—	—	Reason for rejection/decline
email_sent_at	TIMESTAMP	—	NULL	When last email was sent

Customer

Column	Type	Description
customer_id	INTEGER	FK to customers.id (optional for public submissions)
customer_name	VARCHAR(255)	Display name
customer_email	VARCHAR(255)	Email for notifications and Accept/Decline flow
customer_phone	VARCHAR(50)	Contact phone
company_name	VARCHAR(255)	Company name
company_abn	VARCHAR(20)	ABN (Australia)
reference_number	VARCHAR(100)	Customer’s own reference

Route

Column	Type	Description
origin_location	VARCHAR(255)	REQUIRED. Origin display name (suburb used as fallback)
destination_location	VARCHAR(255)	REQUIRED. Destination display name
origin_address	TEXT	Street address
origin_suburb	VARCHAR(100)	Suburb (used for zone lookup)
origin_state	VARCHAR(20)	State code (e.g., NSW, VIC)
origin_postcode	VARCHAR(10)	Postcode
destination_address	TEXT	Street address
destination_suburb	VARCHAR(100)	Suburb
destination_state	VARCHAR(20)	State code
destination_postcode	VARCHAR(10)	Postcode

Cargo

Column	Type	Default	Description
freight_type	VARCHAR(50)	STANDARD	STANDARD, PALLET, FTL
job_type	VARCHAR(20)	LTL	LTL, FTL, Towhaul
weight_kg	NUMERIC(10,2)	0	Total weight in kg
volume_m3	NUMERIC(10,3)	0	Total volume
quantity	INTEGER	1	Number of items
packaging_type	VARCHAR(50)	—	Pallet, Carton, etc.
length_cm	NUMERIC(8,2)	0	Item dimensions
width_cm	NUMERIC(8,2)	0
height_cm	NUMERIC(8,2)	0

Pricing

Column	Type	Description
base_rate	NUMERIC(10,2)	Base freight rate
fuel_surcharge	NUMERIC(10,2)	Fuel levy amount
additional_charges	NUMERIC(10,2)	Other surcharges
gst_amount	NUMERIC(10,2)	GST (10% in AU)
total_amount	NUMERIC(10,2)	Grand total (inclusive of all)
currency	VARCHAR(3)	Default AUD

Options & Flags

Column	Type	Default	Description
dangerous_goods	BOOLEAN	false	DG shipment
dangerous_goods_class	VARCHAR(20)	—	DG class
tailgate_pickup	BOOLEAN	false	Tail lift at pickup
tailgate_delivery	BOOLEAN	false	Tail lift at delivery
express_delivery	BOOLEAN	false	Express service
insurance_required	BOOLEAN	false	Freight insurance
pickup_date	DATE	—	Requested pickup date
delivery_date	DATE	—	Requested delivery date
expiry_date	DATE	NOW + 30 days	Quote validity expiry

Multi-Rate Storage — special_requirements

When multiple service levels are quoted, the special_requirements TEXT column stores both user notes AND a JSON blob of rate options:

User's notes text here

__SERVICE_LEVEL_QUOTES__[{"service":"Express","base":22.5,"fuel":5.06,"surcharges":2.76,"gst":2.76,"total":64.07},{"service":"Standard","base":15.0,"fuel":3.38,"surcharges":1.84,"gst":1.84,"total":42.71}]

Parsing pattern (Python):

marker = '__SERVICE_LEVEL_QUOTES__'
idx = special_requirements.find(marker)
notes = special_requirements[:idx].strip() if idx >= 0 else special_requirements
sl_quotes = json.loads(special_requirements[idx + len(marker):]) if idx >= 0 else []

Parsing pattern (JavaScript):

const marker = '__SERVICE_LEVEL_QUOTES__';
const idx = raw.indexOf(marker);
const notes = idx >= 0 ? raw.slice(0, idx).trim() : raw;
const slQuotes = idx >= 0 ? JSON.parse(raw.slice(idx + marker.length)) : [];

Additional Columns

Column	Type	Description
additional_data	JSONB	Flexible key-value storage for custom fields
template_id	INTEGER	FK to quote_templates.id
created_by	INTEGER	User ID who created the quote
updated_by	INTEGER	User ID who last updated
created_at	TIMESTAMPTZ	Auto-set on INSERT
updated_at	TIMESTAMPTZ	Auto-set on UPDATE (via trigger)

CHECK Constraints

-- Source portal must be one of:
chk_source_portal: CUSTOMER | OPERATIONS | PUBLIC | SYSTEM | API

-- Status must be one of:
chk_status: DRAFT | PENDING | QUOTED | APPROVED | REJECTED | ACCEPTED | DECLINED | EXPIRED | CONVERTED

Indexes

PRIMARY KEY (id)
UNIQUE (quote_reference)
INDEX (created_at)
INDEX (customer_id)
INDEX (email_sent_at)
INDEX (quote_reference)
INDEX (origin_suburb, destination_suburb)
INDEX (source_portal)
INDEX (status)

---

3. Source Portals

Value	Who Creates	Initial Status	Description
OPERATIONS	Operations staff	DRAFT or QUOTED	Internal quotes sent to customers
CUSTOMER	Logged-in customer	PENDING	Customer portal submissions
PUBLIC	Public widget (Osiris)	PENDING	Unauthenticated widget submissions
SYSTEM	Automated/default	PENDING	System-generated quotes
API	Third-party API integration	PENDING	External API submissions

Quote reference prefixes are derived from the first letter of source_portal:

• OPERATIONS → OQ-20260408-0001
• CUSTOMER → CQ-...
• PUBLIC → PQ-...
• SYSTEM → SQ-...
• API → AQ-...

---

4. Status Lifecycle

Operations-Created Quotes (source_portal = OPERATIONS)

DRAFT ──[Send to Customer]──> QUOTED ──[Customer Accepts]──> ACCEPTED
                                    └──[Customer Declines]──> DECLINED
                                    └──[Expires]────────────> EXPIRED

• DRAFT: Saved but not sent. Operations can edit and send later.
• QUOTED: Sent to customer via email with secure Accept/Decline links.
• ACCEPTED: Customer accepted via email token link.
• DECLINED: Customer declined (with optional comment in status_notes).

Customer/Widget/API Quotes (source_portal != OPERATIONS)

PENDING ──[Ops Approves]──> APPROVED
        └──[Ops Rejects]──> REJECTED

• PENDING: Awaiting operations review.
• APPROVED: Operations approved the customer’s request.
• REJECTED: Operations rejected (reason in status_notes).

Terminal States

Status	Can Transition To	Description
ACCEPTED	CONVERTED	Customer accepted; can be converted to booking
APPROVED	CONVERTED	Ops approved; can be converted to booking
CONVERTED	—	Booking created from this quote
EXPIRED	—	Past expiry date
REJECTED	—	Ops rejected
DECLINED	—	Customer declined

---

5. API Endpoints

POST /api/quotes/ — Create Quote

Authentication: None required (supports public submissions).

Minimum required fields:

{
  "origin_location": "Melbourne",
  "destination_location": "Brisbane"
}

Full payload (all optional fields):

{
  "customer_id": 1,
  "customer_name": "Auz Pack Pty Ltd",
  "customer_email": "john@auzpack.com.au",
  "customer_phone": "+61 400 123 456",
  "company_name": "Auz Pack Pty Ltd",
  "origin_location": "Melbourne",
  "origin_address": "570 Bourke Street",
  "origin_suburb": "Melbourne",
  "origin_state": "VIC",
  "origin_postcode": "3000",
  "destination_location": "Brisbane City",
  "destination_address": "340 Queen Street",
  "destination_suburb": "Brisbane City",
  "destination_state": "QLD",
  "destination_postcode": "4000",
  "job_type": "LTL",
  "freight_type": "PALLET",
  "weight_kg": 150.00,
  "quantity": 2,
  "packaging_type": "Pallet",
  "length_cm": 120,
  "width_cm": 120,
  "height_cm": 150,
  "base_rate": 22.50,
  "fuel_surcharge": 5.06,
  "additional_charges": 2.76,
  "gst_amount": 2.76,
  "total_amount": 64.07,
  "source_portal": "PUBLIC",
  "status": "PENDING",
  "special_requirements": "Fragile items\n\n__SERVICE_LEVEL_QUOTES__[{...}]"
}

Behavior by status and source_portal:

status	source_portal	Email Sent	Notes
DRAFT	OPERATIONS	None	Saved only, no notification
QUOTED	OPERATIONS	Quote sent to customer with Accept/Decline links
PENDING	CUSTOMER / PUBLIC / API	Confirmation email to customer

Response:

{
  "success": true,
  "message": "Quote created and sent",
  "data": {
    "id": 42,
    "quote_reference": "OQ-20260408-0001"
  }
}

GET /api/quotes/ — List Quotes

Query parameters: status, customer_id, search, limit (default 100), offset (default 0)

GET /api/quotes/<id> — Get Single Quote

PUT /api/quotes/<id>/status — Update Status

{
  "status": "APPROVED",
  "status_notes": "Approved by Sarah — customer is priority account"
}

POST /api/quotes/<id>/send-to-customer — Send Draft to Customer

Changes DRAFT → QUOTED and sends email with Accept/Decline links.

POST /api/quotes/<id>/send-email — Resend Email

Sends appropriate email based on current status and source_portal.

GET /api/quotes/statistics — Status Counts

{
  "success": true,
  "data": {
    "DRAFT": 2, "PENDING": 5, "QUOTED": 3, "ACCEPTED": 1,
    "APPROVED": 10, "DECLINED": 0, "REJECTED": 2, "total": 23
  }
}

---

6. Rate Computation — POST /api/rate-entries/compute-rate

This is the rate calculation engine. It does NOT save anything — it returns a pricing breakdown that you store in the quote payload.

Request

{
  "customer_id": 1,
  "pickup_zone_id": null,
  "delivery_zone_id": null,
  "pickup_postcode": "3000",
  "delivery_postcode": "4000",
  "pickup_suburb": "Melbourne",
  "delivery_suburb": "Brisbane City",
  "service_level_id": 1,
  "items": [
    {
      "packaging_type": "Pallet",
      "quantity": 2,
      "length_cm": 120,
      "width_cm": 120,
      "height_cm": 150,
      "weight_kg": 75
    }
  ],
  "chargeable_weight_kg": 150,
  "job_type": "LTL"
}

Key fields:

• customer_id — REQUIRED. Used to find customer-specific rate cards.
• pickup_zone_id / delivery_zone_id — If not provided, resolved automatically from pickup_postcode or pickup_suburb.
• service_level_id — Required to apply service-level multipliers (Express, Standard, Economy, etc.).
• items — Array of cargo items. Fields: packaging_type, quantity, length_cm, width_cm, height_cm, weight_kg.

Response

{
  "success": true,
  "found": true,
  "computation": {
    "rate_card_name": "JATT Per KG Rates",
    "rate_card_id": 5,
    "rate_entry_id": 123,
    "customer_specific": true,
    "service_level": {
      "name": "Express",
      "multiplier": 1.5,
      "is_override": false
    },
    "initial_cost": 22.50,
    "totals": {
      "initial_cost": 22.50,
      "base_charge": 0.57,
      "flat_rate_charge": 0,
      "subtotal": 56.25,
      "minimum_rate": 56.25,
      "minimum_applied": true,
      "final_total": 56.25
    },
    "addons": {
      "addons": [
        { "name": "Fuel Levy", "amount": 5.06, "is_tax_addon": false },
        { "name": "GST", "amount": 2.76, "is_tax_addon": true }
      ],
      "total_addon_amount": 7.82
    },
    "calculation_steps": ["..."]
  }
}

How to Extract Pricing from the Response

const comp = response.computation;
const totals = comp.totals;
let fuelLevy = 0, gst = 0, surcharges = 0;

if (comp.addons && comp.addons.addons) {
    comp.addons.addons.forEach(a => {
        if (a.is_tax_addon) gst += a.amount;
        else if (/fuel/i.test(a.name)) fuelLevy += a.amount;
        else surcharges += a.amount;
    });
}

const grandTotal = totals.final_total + fuelLevy + surcharges + gst;

Multi-Service-Level Calculation

To get rates for all service levels, call compute-rate once per service level:

const serviceLevels = [
    { id: 1, name: 'Express' },
    { id: 2, name: 'Hot Shot' },
    { id: 3, name: 'Standard' },
    { id: 4, name: 'Economy' }
];

const promises = serviceLevels.map(sl => {
    const payload = { ...basePayload, service_level_id: sl.id };
    return fetch('/api/rate-entries/compute-rate', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(payload)
    }).then(r => r.json());
});

const results = await Promise.all(promises);

---

7. Zone Lookup — POST /api/rate-entries/check-zone

Before calling compute-rate, resolve suburb/postcode to a zone ID:

// Request
{ "suburb": "Melbourne", "state": "VIC", "postcode": "3000" }

// Response
{ "success": true, "zone_id": 5, "zone_name": "Melbourne" }

The compute-rate endpoint can also resolve zones automatically from pickup_postcode/pickup_suburb, but explicit zone resolution is faster for UI feedback.

---

8. Email System

Architecture

utils/email_service.py     — SMTP EmailService (Mailosaur sandbox / production SMTP)
utils/quote_email_service.py — Quote-specific orchestration (prepare data, send, update DB)
templates/mail/*.py         — Python email templates (get_html_content / get_text_content)

Email Templates

Template File	When Sent	Recipient
quote_confirmation.py	Customer/Public quote created	Customer
quote_sent.py	Ops sends quote to customer	Customer (with Accept/Decline links)
quote_accepted.py	Customer accepts	Customer
quote_declined.py	Customer declines	Customer
quote_approved.py	Ops approves customer quote	Customer (with PDF)
quote_rejected.py	Ops rejects customer quote	Customer
quote_ops_notification.py	Customer accepts/declines	Operations

Sandbox (Development)

Default config points to Mailosaur — no real emails sent:

MAIL_HOST=mailosaur.net
MAIL_PORT=2525
MAIL_USERNAME=dx4p4hhk@mailosaur.net
MAIL_PASSWORD=biXvRAo4

Auto-detected by EmailService._detect_mailosaur(). For production, swap to real SMTP credentials.

Customer Accept/Decline Flow (Token-Based)

Tokens are generated using Flask’s itsdangerous.URLSafeTimedSerializer:

from utils.quote_tokens import generate_response_url

accept_url = generate_response_url(quote_id, 'accept')
# → http://localhost:5002/quotes/respond?token=SIGNED_TOKEN&action=accept

The /quotes/respond route (in modules/quote_response/routes.py) is public — no login required. Tokens expire after 30 days (matching quote expiry).

---

9. Integration Guide — Public Widget (Osiris Pattern)

Step 1: Calculate Rate

// POST /api/rate-entries/compute-rate
const response = await fetch('/api/rate-entries/compute-rate', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
        customer_id: null,  // null for public quotes (uses global rate cards)
        pickup_suburb: 'Sydney',
        pickup_postcode: '2000',
        delivery_suburb: 'Melbourne',
        delivery_postcode: '3000',
        service_level_id: 3,  // Standard
        items: [{
            packaging_type: 'Pallet',
            quantity: 1,
            length_cm: 120, width_cm: 120, height_cm: 150,
            weight_kg: 100
        }],
        job_type: 'LTL'
    })
});

Step 2: Submit Quote

// POST /api/quotes/
const quoteResponse = await fetch('/api/quotes/', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
        // Required
        origin_location: 'Sydney',
        destination_location: 'Melbourne',

        // Source identification
        source_portal: 'PUBLIC',
        status: 'PENDING',

        // Customer (collected from widget form)
        customer_name: 'John Doe',
        customer_email: 'john@example.com',
        customer_phone: '+61 400 123 456',
        company_name: 'Acme Corp',

        // Route
        origin_suburb: 'Sydney CBD', origin_state: 'NSW', origin_postcode: '2000',
        destination_suburb: 'Melbourne CBD', destination_state: 'VIC', destination_postcode: '3000',

        // Cargo
        job_type: 'LTL',
        freight_type: 'PALLET',
        quantity: 1,
        weight_kg: 100,
        packaging_type: 'Pallet',
        length_cm: 120, width_cm: 120, height_cm: 150,

        // Pricing (from compute-rate response)
        base_rate: 15.00,
        fuel_surcharge: 3.38,
        additional_charges: 1.84,
        gst_amount: 1.84,
        total_amount: 42.71,

        // Multi-rate (optional, stored in special_requirements)
        special_requirements: '__SERVICE_LEVEL_QUOTES__' + JSON.stringify([
            { service: 'Express', base: 22.50, fuel: 5.06, surcharges: 2.76, gst: 2.76, total: 64.07 },
            { service: 'Standard', base: 15.00, fuel: 3.38, surcharges: 1.84, gst: 1.84, total: 42.71 }
        ])
    })
});

Step 3: Quote Appears in Operations Portal

The quote will appear in /operations/quotes with status PENDING and the Approve / Reject buttons visible to operations staff.

---

10. Integration Guide — Customer Portal

Same pattern as Public Widget, but:

• source_portal: 'CUSTOMER'
• customer_id set from the logged-in user’s associated customer record
• Authentication required (session-based)

---

11. Key Files Reference

API Layer

File	Purpose
api/quotes_api.py	All quote CRUD, status updates, email triggers
api/rate_entries_api.py	Rate computation engine (compute-rate)

Email System

File	Purpose
utils/email_service.py	Core SMTP service (Mailosaur sandbox / production)
utils/quote_email_service.py	Quote-specific email orchestration
utils/quote_tokens.py	Signed token generation for Accept/Decline links
templates/mail/*.py	Python email templates (HTML + plain text)

Customer Response

File	Purpose
modules/quote_response/routes.py	Public Accept/Decline page (no login)
templates/quote_response/respond.html	Standalone response page

Operations Portal

File	Purpose
portals/operations/routes.py	Quote list, PDF generation
templates/portals/operations/simple_quote_form.html	Create Quote form (Save Draft / Send Quote)
templates/portals/operations/quotes_list.html	Quotes list with drawer, actions, email status
templates/portals/operations/quote_pdf.html	Print-ready PDF template

Configuration

File	Purpose
.env.dev	Development config (Mailosaur defaults)
.env.example	Template for all env vars

---

12. Environment Variables

# SMTP Email (defaults to Mailosaur sandbox)
MAIL_HOST=mailosaur.net
MAIL_PORT=2525
MAIL_USERNAME=dx4p4hhk@mailosaur.net
MAIL_PASSWORD=biXvRAo4
MAIL_ENCRYPTION=              # 'tls', 'ssl', or blank
MAIL_FROM_ADDRESS=no-reply@myfr8.com.au
MAILOSAUR_API_KEY=biXvRAo4
MAILOSAUR_SERVER_ID=dx4p4hhk

# Operations
OPERATIONS_EMAIL=operations@myfr8.com.au
COMPANY_NAME=MyFR8 Logistics

# Token signing (uses Flask's session secret)
SESSION_SECRET=your-secret-key

---

13. Database Migrations

Run these against your database if setting up fresh:

# Add email tracking column
psql $DATABASE_URL -f migrations/add_email_sent_at_to_unified_quotes.sql

# If CHECK constraints need updating (DRAFT, ACCEPTED, DECLINED)
psql $DATABASE_URL -c "
ALTER TABLE unified_quotes DROP CONSTRAINT IF EXISTS chk_status;
ALTER TABLE unified_quotes ADD CONSTRAINT chk_status CHECK (
    status IN ('DRAFT','PENDING','QUOTED','APPROVED','REJECTED','ACCEPTED','DECLINED','EXPIRED','CONVERTED')
);
"