Evolving Our Shipping Platform: From .env Secrets to Multi‑Carrier, Multi‑Provider Architecture

Date: 2025-09-04

TL;DR

We shipped a full-stack refactor to move carrier credentials out of .env into admin-managed system configs; introduced a scalable, multi-provider/multi-carrier model; added admin UI with a clean workflow; unified admin-only routes under /admin; aligned page styles; and added order-level provider + carrier_code for better tracking and reconciliation. We also fixed an API upsert edge case and cleaned up i18n strings.

---

Why This Matters

• Security and Governance: Secrets in .env don’t scale across admins or providers. Admins now manage carrier credentials in the app.
• Future‑proofing: The new schema and adapter interface supports RapidDeals today and ABF/CEVA (or others) tomorrow with minimal changes.
• Operational Clarity: Orders now record provider and carrier_code, making reporting and reconciliation straightforward.
• Consistency: Admin-only pages now live under /admin, and the Carrier Config page matches the Addresses page UX.

---

Highlights

• Admin-managed carrier credentials (DB-backed) replace .env use.
• Carrier Config page redone as a list + modal form (English-only), styled like Addresses, with Provider/Name/Code/Env/Auth Extra support.
• Multi-provider, multi-carrier model with clean unique constraints.
• Adapter interface added; RapidDeals adapter scaffolded (plug in more providers later).
• Orders now persist provider + carrier_code.
• Admin-only routes unified under /admin.
• Fixed “Failed to save carrier config” by handling SQL upserts with NULLs correctly.
• Removed remaining Chinese UI strings in updated files.

---

What Changed

1) Database Migrations

New and updated migrations for carriers and orders:

• supabase/migrations/20250904_carrier_configs.sql (if not previously applied)
  • Creates public.carrier_configs.
• supabase/migrations/20250904_carrier_configs_add_name.sql
  • Adds carrier_name.
• supabase/migrations/20250904_carrier_configs_ext.sql
  • Adds carrier_code (optional; SCAC for direct carriers), auth_extra JSONB, environment ('prod'|'sandbox').
  • Changes uniqueness to (admin_id, provider, COALESCE(carrier_code,'')).
• supabase/migrations/20250904_carrier_configs_unique_tuple.sql
  • Adds a plain unique index (admin_id, provider, carrier_code) to support PostgREST upsert on non-null carrier_code.
• supabase/migrations/20250904_orders_add_provider_code.sql
  • Adds provider and carrier_code to orders + useful indexes.

Why the extra unique index? PostgREST upsert can’t use expression indexes like COALESCE(). We kept both:

• Plain unique index for upserts when carrier_code is not null.
• Expression uniqueness for overall data integrity.

2) Admin API: Carrier Config

• File: app/api/admin/carrier-config/route.ts
• Endpoints:
  • GET: returns current admin’s configs; ?provider= optional filter.
  • POST: creates/updates config. Handles carrier_code null by doing a manual upsert (select → update or insert). Uses upsert for non-null carrier_code.
  • PUT: updates by id.
• Fields supported: provider, carrier_name, carrier_code, api_id, api_key, api_url, is_active, environment, auth_extra.
• Fix: “Failed to save carrier config” 500 error resolved by implementing manual upsert for null codes; upsert with onConflict for non-null codes.

3) Admin UI: Carrier Config

• Page: app/admin/carrier-config/page.tsx
• Style alignment: Matches /addresses page structure.
• Features:
  • Header with title/description on left; “New” button on right.
  • List shows Provider, Carrier Name, Carrier Code, API ID (masked), Env, Active, Updated.
  • Modal form for New/Edit (English-only UI):
    • Provider (select: rapiddeals, abf, ceva)
    • Carrier Name
    • Carrier Code (SCAC, optional)
    • API ID / API Key / API URL
    • Environment (prod / sandbox)
    • Auth Extra (JSON)
    • Active
• Implementation details:
  • GET returns list; modal writes via POST/PUT to /api/admin/carrier-config.
  • Form state reset after successful save; table refreshes.

4) Route Unification for Admin‑Only Pages

Admin-only pages are moved or re-exposed under /admin:

• New wrappers (re-exporting original pages under /admin):
  • app/admin/insurance/quotes/page.tsx
  • app/admin/insurance/certificates/page.tsx
  • app/admin/customers/page.tsx
  • app/admin/roles/page.tsx

Menu updates:

• lib/menu-structure.ts
  • Insurance/Get Quote → /admin/insurance/quotes
  • Insurance/Certificates → /admin/insurance/certificates
  • Customers → /admin/customers
  • Roles → /admin/roles
• Customers’ default menu removed the Insurance section.

5) Replace .env Reads with System Config

• RapidDeals credentials now retrieved via DB first, with .env fallback for compatibility.
• lib/rapiddeals-config.ts:
  • Added getCarrierConfig(provider, { carrierCode?, adminId?, isActive? })
  • getRapidDealsConfig() uses getCarrierConfig('rapiddeals') internally.

Callers updated earlier in this project now rely on the DB-backed config. Existing RapidDeals API helpers continue to function as before.

6) Adapter Interface (Future Providers)

• lib/carriers/adapter.ts:
  • Defines CarrierAdapter: getQuotes, placeOrder, getTracking, cancelOrder.
  • Adds normalized types: QuoteItem, PlaceOrderRequestBase, etc.
• lib/carriers/providers/rapiddeals-adapter.ts:
  • Scaffolded RapidDeals adapter (placeholders to be wired). Keeps the codebase ready for ABF/CEVA by just adding their adapters.

This sets us up to support:

• Single-source flow by selected provider.
• Aggregated quotes across all active configs (RapidDeals + ABF + CEVA) by normalizing results.

7) Orders Persist provider and carrier_code

• app/api/orders/place/route.ts now writes:
  • provider: 'rapiddeals'
  • carrier_code: body.carrierSCAC
• Ensures downstream tracking, analytics, and reconciliation are easier.

8) Carrier Config Save Bug Fix

• Resolved an upsert failure when carrier_code was null.
• Implemented manual upsert logic for null codes; upsert with onConflict for non-null codes.
• Added plain unique index (admin_id, provider, carrier_code) to match onConflict.

9) UI and i18n Clean-up

• Converted carrier config page and updated modules to English-only strings in changed files.
• Carrier Config page styling aligned to Addresses (header padding, container, table styles).

---

How to Roll This Out

1. Run Migrations (in order)

• 20250904_carrier_configs.sql (if not previously applied)
• 20250904_carrier_configs_add_name.sql
• 20250904_carrier_configs_ext.sql
• 20250904_carrier_configs_unique_tuple.sql
• 20250904_orders_add_provider_code.sql

2. Rebuild

• ./build-and-restart.sh

3. Configure Carriers

• Go to /admin/carrier-config.
• Add RapidDeals: provider=rapiddeals, carrier_code leave empty, set API ID/Key/URL, environment=prod, is_active=true.
• (Future) Add direct carriers (e.g., ABF/CEVA) with provider=abf/ceva, carrier_code=SCAC, any auth_extra, and credentials.

4. Verify

• Get quotes and place an order.
• Check orders table for provider and carrier_code populated.
• Confirm /admin routes are accessible and consistent with admin-only scope.

---

Developer Notes

• Config retrieval:
  • Use getCarrierConfig('rapiddeals', { carrierCode, isActive: true }) to target specific credentials; default uses admin’s active config.
  • getRapidDealsConfig() remains for backward compatibility (wraps the generic function).
• Extending providers:
  • Implement a new CarrierAdapter under lib/carriers/providers/*.
  • Decide on aggregated quotes (iterate active configs and merge), or single-source based on user selection.

---

Next Steps (Optional)

• Implement aggregated quotes across active providers with the new adapter interface.
• Add provider + carrier_code to admin order lists and filters.
• Add redirect/cleanup for old non-admin paths (e.g., redirect /insurance/quotes → /admin/insurance/quotes).
• Encrypt api_key at rest (if using KMS or external secrets manager).

---

Closing

This refactor transitions our platform from single-provider secrets to a robust, admin-configurable, multi-provider architecture. It’s secure, scalable, and ready for direct carrier integrations like ABF/CEVA with minimal additional work.