Maintenance — Freight Quote Tool

Setup-and-upkeep notes for whoever maintains the tool. Keystone 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://keystone-freight-tool.<your-account>.workers.dev.

Who can sign in (Cloudflare Access)

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

• Team: your-team-name
• 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 @keystonerecognition.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 Keystone 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).

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": "keystonerecognition.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 (46514), ORIGIN_STATE (IN), 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 the Ordova "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, and logs it — but by default does not send it into the UPS rate request (sending it adds UPS's insurance surcharge and would diverge from Keystone's validated quotes; the original prototype didn't send it). To rate with declared value, set RATE_WITH_DECLARED_VALUE = true 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 keystone-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.