Freight Quote Tool · Maintenance

Maintenance — Freight Quote Tool

Setup-and-upkeep notes for whoever maintains the tool. You can do these tasks directly, or ask us (Rogue Agents) to do them. For day-to-day quoting and admin, see the Usage guide.

Stack: one Cloudflare Worker (src/index.js) + static UI (public/) + D1 database (schema.sql); UPS Rating lives in src/ups.js. Sign-in is handled by Cloudflare Access (Zero Trust), not by the app. Deployed at https://rcb-freight-tool.<your-account>.workers.dev (your live address is on the Freight Quote Tool page under Your Tools).

Who can sign in (Cloudflare Access)

Access is configured in Cloudflare Zero Trust, not in this repo:

Team: rcb-awards
Application: "Freight Quote Tool" · id <access-app-id>
Policy: "Freight users" · id <access-policy-id>
Account id: <cloudflare-account-id>
Sign-in: email one-time PIN, ~2-week (336h) session.

The current rule allows any @rcbawards.com address, plus a Rogue Agents support address kept for testing and follow-on support. That support address can be removed at any time without affecting your staff.

The app reads the signed-in address from the Cf-Access-Authenticated-User-Email header to show who's logged in and stamp catalog uploads. Locally (no Access) it shows dev@local (no Access).

Gotcha: an address not covered by the policy fails silently — Access still says "we sent you a code," but none arrives (Cloudflare's anti-enumeration design). So "no code arrived" almost always means a typo or an address outside the policy. Check the policy first.

Changing who has access

Dashboard: Zero Trust → Access → Applications → "Freight Quote Tool" → Policies → edit "Freight users". Include rules are OR'd — add an Emails ending in selector for a domain, or individual Emails entries. Save.

API (scriptable): needs a Cloudflare API token scoped Account → Access: Apps and Policies → Edit on the account above. Supply the token at run time (env var or a local file) — never commit it.

export CF_TOKEN=...            # token with Access: Apps and Policies → Edit
ACCT=<cloudflare-account-id>
APP=<access-app-id>
POL=<access-policy-id>
BASE=https://api.cloudflare.com/client/v4/accounts/$ACCT/access/apps/$APP/policies/$POL

# Read the current allow-list:
curl -s "$BASE" -H "Authorization: Bearer $CF_TOKEN" | python3 -m json.tool

# Replace the allow-list (PUT sends the whole policy). Example: a whole domain
# plus one named support email. Drop the email_domain entry to go named-only;
# add more {"email": {...}} entries for individuals.
curl -s -X PUT "$BASE" -H "Authorization: Bearer $CF_TOKEN" -H "Content-Type: application/json" --data '{
  "name": "Freight users",
  "decision": "allow",
  "include": [
    {"email_domain": {"domain": "rcbawards.com"}},
    {"email": {"email": "nate@natestpierre.me"}}
  ],
  "require": [],
  "exclude": []
}'

Rotating the UPS credentials

UPS OAuth client ID/secret live as Cloudflare secrets (never in the repo). To rotate:

npx wrangler secret put UPS_CLIENT_ID
npx wrangler secret put UPS_CLIENT_SECRET

Non-secret UPS settings are plain vars in wrangler.jsonc:

UPS_STUB — "0" = live rates, "1" = fake-but-plausible stub rates (no credentials needed; useful for UI work).
ORIGIN_ZIP (53186), ORIGIN_STATE (WI), UPS_ACCOUNT (your UPS account number).

Quick credential check without touching the Worker:

node scripts/smoke-ups.mjs [destZip] [weightLb]   # reads creds from .dev.vars

For local dev, the same two keys go in .dev.vars (gitignored) — never commit it.

Catalog format and parsing

The catalog is your complete product-list export (your "Freight Inputs" export). The parser (parseCatalog in src/index.js) matches columns by header name, not position, and reads both .xlsx and .csv. Columns used: Item No, Saleprice* (first such column — per-item price), Case Weight, Case Quantity, Case Length/Width/Height. Rows with zero/blank weight or units are kept but flagged as "no carton data" (they prompt for manual carton entry).

If a future export renames a column, add the new spelling to the synonym lists in parseCatalog — that's the only place. Verify any change quickly with node scripts/test-parse.mjs <file> (parses without touching the DB).

After a new file is uploaded through Admin → Catalog, the whole products table is replaced. The price column feeds the declared-value calculation.

Declared value

The tool computes declared value as quantity × price, shows it on the quote as a prefilled but editable field, logs it, and sends it into the UPS rate request — so the quoted rates include UPS's insurance. This is the intended behavior (confirmed with you: insurance is wanted in the quote). To rate without declared value, set RATE_WITH_DECLARED_VALUE = false in src/ups.js and redeploy.

Deploying

npm run deploy        # stamps version into the footer, then wrangler deploy

The displayed version comes from package.json version (auto-stamped into the id="ver" element by scripts/stamp-version.mjs on dev/deploy). Bump it there; the footer follows.

Requires wrangler login (OAuth) the first time. Note: a Wrangler OAuth login carries Workers + D1 scope but not Access/Zero-Trust scope — which is why the Access policy is managed separately (above), with its own API token.

Database (D1)

Schema: schema.sql (tables: products, catalog_meta, quote_log).
Apply schema to the remote DB: npm run db:remote. Local dev DB: npm run db:local.
DB binding DB → database rcb-freight (id in wrangler.jsonc).

The quote log is append-only in D1 and pulled out via the Admin Download log (.xlsx) button. By decision it is download-only — not synced anywhere else. Clearing it is gated behind typing DELETE.

Validating rates against real quotes

scripts/validate-rates.mjs replays a set of answer-key shipments through the live rating code and diffs against real UPS rates:

node scripts/validate-rates.mjs [dataDir]

Reads UPS creds from .dev.vars. On the last validation run, 7 of 9 sample shipments matched to the penny; the two misses traced to errors in the answer-key data, not the tool.