⚙️ Deploy / Environment Requirements (read first)

This PR is inert without these — publishing no-ops with a clear message and the admin Rate Sheet button shows "Unavailable" — so it can merge and deploy safely before the wiring is done. To activate:

Var	Value	Where needed
`GDRIVE_SERVICE_ACCOUNT_JSON`	Path to the service-account key FILE (mount the key as a secret file in the container — the value is a path, not the JSON contents)	web/app containers (admin rate-sheet link) and wherever `rake billing_config:publish` runs
`GDRIVE_FOLDER_ID`	`1cYCxT4gwvyLUBz1cZVLqyVtmWS6BTNUO` ("Generated Rate Sheets" — must stay a Shared Drive folder; service accounts have no My Drive quota)	same as above
`GDRIVE_SHARE_DOMAIN` (optional)	e.g. `rocket-house.com` — grants domain users read on each uploaded file only (the folder is never shared). Leave unset until Andrew decides who may open the sheet	publish path only
`GDRIVE_SHARE_ANYONE` (optional)	`true` for link-sharing — not recommended; the sheet is the full pricing card	publish path only

Deploy-repo notes:

Each var is a two-part change: the value in sites/.env and an x-env entry in compose.tpl.yml — either alone silently no-ops. An apply carries new env; a plain restart does not.
The service-account key already exists (rate-sheet-updater@hillebrand-last-mile, Content manager on the folder) — it needs mounting as a secret file, path in GDRIVE_SERVICE_ACCOUNT_JSON.
New gem google-apis-drive_v3 (+ googleauth) is in the Gemfile/lockfile — picked up by the normal image build, no manual step.
Both sites' deployments want the same vars; the code scopes everything (filenames, outdating, the admin link) per site automatically.

Description

Replaces the hand-edited monolithic billing_prices.yml with a system where the master rate-sheet spreadsheet is the source of truth for billing prices — now for both sites (LMCA and LMNY) — so rate changes no longer require a developer translating numbers into YAML.

Prices now live in config/sites/lmca/billing_configs/ — one self-documenting file per tier (Tier_NN.yml) plus non-tiered prices, association mappings, system conversions, display names, and sheet layout.
make billing.xlsx generates the dated master workbook (matching the agreed sheet format cell-for-cell); make billing.ingest IN=... reads an edited workbook back into the configs, preserving comments and formatting byte-for-byte on unedited cells. make billing.publish uploads to Google Drive (inert until ops provisions credentials).
Fuel surcharge becomes one knob: rake billing_config:fsc BASE=0.60 applies the site rules (half rate for Tiers 19/20, minus 4pt for Tier 24) across every tier.
Velocity-storage exemption moved from a hidden id list in global.yml onto each tier's tab as a Yes/No setting.
Billing tags renamed to current-day business names (e.g. deliveries → delivery-rate, storage-cases-tiered → monthly-storage-rate) across both sites' data and every call site.
LMNY is migrated too (config/sites/lmny/billing_configs/, 17 tiers), with its own exhaustive equivalence proof. NY's card inverts several CA shapes and the split is data-driven rather than assumed: case-break and the transfer minimums are flat on NY, returns/COD/aggregation are tiered, returns-or-refusals renders as a per-case-count bracket table, and the NY sheet carries its own legends (11 hub-grouped zones, 7 format types, Common Carrier). Velocity exemption on NY: every tier except Tier 3, as per-tier sheet settings. The unmigrated-site fallback machinery remains (spec-pinned) for any future site.
Both sites publish dated workbooks into the same Drive folder; outdating is site-scoped so one site's publish can never retire the other's current sheet.
Admins get a link to the current sheet: the Billing page shows a "Billing Rate Sheet" button (with a "Last updated" stamp, mirroring the Delivery Schedule button on Accounts) that opens the latest non-outdated sheet for that site in a new tab. Only the file's own view link is ever exposed — the Drive folder stays private — and both endpoints are admin-gated server-side.

Relationship to SD-767 (PR #1420). The NY Tier 15 linehaul zeroing ships independently before 8/1 on its own branch in the legacy format. This branch carries the same values in the new format as a single labeled carry commit (b38512e19), so the generated NY sheet shows the change for review; after #1420 merges, a rebase reconciles the carry to a no-op.

How Has This Been Tested?

Exhaustive equivalence proof (spec/services/billing_config_spec.rb): every tag × tier × association id × label resolves identically through the new loader vs the original production YAML — the engine (Report::Billing.price_for) is untouched.
Byte-identical round-trip: generating the sheet from the live configs and ingesting it back reproduces every yml file byte-for-byte.
Master-sheet fidelity: the generated workbook was diffed cell-by-cell against the supplied master sheet (Non-Tiered tab: zero differences; only intentional deltas elsewhere, e.g. live FSC rate over the sheet's stale sample).
93 examples across the new service/config specs, all green; full billing-report spec suites re-run after each rebase.
Review gauntlet: six-persona board review, four local CodeRabbit runs, and a four-agent adversarial sweep — the sweep's findings (most notably ingest silently dropping edits typed into previously-unpriced cells, and a fuel-surcharge negative-value trap) are fixed and spec-pinned.

Screenshots (optional)

Anything Else?

Google Drive publishing is scaffolded but inert until ops adds the google-apis-drive_v3 gem and sets GDRIVE_SERVICE_ACCOUNT_JSON / GDRIVE_FOLDER_ID. Follow-ups before activation: pass supports_all_drives flags (Shared Drive folders won't work without them) and run a real smoke test against a scratch folder.
Three tags absent from the master sheet are preserved in labeled "under review" sections pending client/boss confirmation: inbound-storage-fee, crossdock-transfer-minimum, aggregate-delivery-fees.
Data oddity worth a glance: Tier 22 charges a keg delivery surcharge on Case (Small) — the master sheet's template assumed that cell is inapplicable.
Pre-existing, out of scope: Report::Transfer references an inbound-receive-tiered tag that exists in neither site's data (unreachable — both sites run inbound: cliff); worth its own ticket.
Tier ids remain append-only in billing_tiers.csv (owners reference them permanently); a sentinel spec now guards against mid-list edits.

🤖 Generated with Claude Code

Note

High Risk
Touches core billing fee resolution and tag names across the engine; incorrect loader or rename would change invoiced amounts, though the PR claims exhaustive equivalence tests against legacy YAML.

Overview
Billing prices move from a single billing_prices.yml to per-site billing_configs/ (tier YAML, Prices.yml, System.yml, layout/descriptions). BillingConfig rebuilds the fee_data shape Report::Billing.price_for already uses, with legacy fallback for unmigrated sites.

Operational round-trip: BillingRateSheet / BillingRateSheetIngest sync configs ↔ master XLSX; optional BillingRateSheetUpload + BillingRateSheetLatest publish to Google Drive and power admin Billing Rate Sheet link + last-updated API. Makefile adds billing.xlsx, billing.ingest, billing.rebuild, billing.publish; BillingFuelSurcharge batch-updates FSC from one base rate.

Runtime wiring: Fee tags are renamed (e.g. deliveries → delivery-rate, storage-inbound → inbound-storage-fee) across billing tasks, reports, helpers, and deprecated scripts. Pallet size, format classes, velocity rules, and exempt accounts read BillingConfig.system instead of GLOBAL['billing'] / GLOBAL['orders'] where migrated.

^{Reviewed by Cursor Bugbot for commit d0e3301. Bugbot is set up for automated code reviews on this repo. Configure here.}

Summary by CodeRabbit

New Features
- Added an end-to-end billing rate-sheet workflow: generate the master XLSX, ingest edits back into billing configuration, and publish to Google Drive when configured.
- Added a fuel surcharge batch update to refresh tier factors based on a provided base rate.
Updates
- Billing calculations and billing reports were updated to use the revised fee/rate identifiers.
- Pallet/case sizing, format defaults, and velocity-storage rules now come from the centralized billing system configuration; API defaults updated accordingly.
- Added new billing configuration assets for LMCA (system, layout, pricing, tier files).
Tests
- Expanded RSpec coverage for rate-sheet generation/ingest fidelity, fuel updates, tier stability, and velocity storage tier coverage.

Open Pull Requests

6

2

1