DEV Community

Cover image for From Rate Card to Database: Engineering UPS Shipping Rates Into a Multi-Carrier Logistics Platform published: false
OLALEKAN OGUNDIMU
OLALEKAN OGUNDIMU

Posted on

From Rate Card to Database: Engineering UPS Shipping Rates Into a Multi-Carrier Logistics Platform published: false

#typescript #postgres #prisma #backend

How I modeled a static shipping rate card into a queryable database, caught schema design mistakes early, and reduced 400+ database calls down to 4.

How I Got Here

I was brought onto a logistics platform project — a third-party logistics system that lets businesses and individuals ship packages internationally. The platform aggregates multiple carriers: DHL, FedEx, UPS, and others. The idea is simple from the customer's perspective — enter your destination, your package weight, get a price, book a shipment.

My task was specific: integrate UPS shipping rates into the system.

Under the hood each carrier has its own API, its own pricing model, its own quirks. DHL was already integrated and working. UPS was a different story.

The UPS API does not support Nigeria as an origin country.

That means we can't call UPS's rate endpoint at booking time. We had to find another way.

The Problem: A Spreadsheet Full of #REF! Errors

UPS provided a rate card — a spreadsheet that maps shipping zones to weights to prices. The structure is straightforward:

  • Every country in the world belongs to a zone (Zone 2 through Zone 9 for shipments from Nigeria)
  • Each zone has a price per weight bracket (0.5kg, 1kg, 1.5kg... up to 30kg)
  • There are two product types: Express Envelopes and Packages

The spreadsheet looked like this:

Weight Zone 2 Zone 3 Zone 4 Zone 5 ...
0.5kg #REF! #REF! #REF! #REF! ...
1.0kg #REF! #REF! #REF! #REF! ...

Every price cell showed #REF! — broken formula references. The actual prices would be filled in later. But the structure was there, and that was enough to start building.

The goal: turn this spreadsheet into a database structure that the backend can query in real time. Customer ships to Germany, 2.5kg package — look up Germany's zone, find the price for that zone at 2.5kg, return it instantly.

Designing the Schema

The project uses Prisma as its ORM with PostgreSQL. Prisma lets you define your database structure in .prisma schema files, which it then uses to generate both SQL migrations and type-safe TypeScript query methods.

I needed three things:

  1. A table to store zones
  2. A table to map countries to zones
  3. A table to store the actual prices per zone and weight

My first pass looked like this:

enum UpsProductType {
  ENVELOPE
  PACKAGE
}

model UpsZone {
  id           Int               @id @default(autoincrement())
  zoneNumber   Int               @unique @map("zone_number")
  zoneName     String            @map("zone_name") @db.VarChar(100)
  description  String?           @db.VarChar(255)
  createdAt    DateTime          @default(now()) @map("created_at")
  updatedAt    DateTime          @updatedAt @map("updated_at")

  countries    UpsZoneCountry[]
  rates        UpsRate[]

  @@map("ups_zones")
}

model UpsZoneCountry {
  id         Int      @id @default(autoincrement())
  zoneId     Int      @map("zone_id")
  countryId  Int      @map("country_id")
  createdAt  DateTime @default(now()) @map("created_at")

  zone       UpsZone  @relation(fields: [zoneId], references: [id])
  country    Country  @relation(fields: [countryId], references: [id])

  @@unique([zoneId, countryId])
  @@map("ups_zone_countries")
}

model UpsRate {
  id          Int            @id @default(autoincrement())
  zoneId      Int            @map("zone_id")
  productType UpsProductType @map("product_type")
  weightKg    Decimal        @map("weight_kg") @db.Decimal(5, 2)
  price       Decimal?       @db.Decimal(10, 2)
  currency    String         @default("USD") @db.VarChar(3)
  createdAt   DateTime       @default(now()) @map("created_at")
  updatedAt   DateTime       @updatedAt @map("updated_at")

  zone        UpsZone        @relation(fields: [zoneId], references: [id])

  @@unique([zoneId, productType, weightKg])
  @@map("ups_rates")
}
Enter fullscreen mode Exit fullscreen mode

It worked. It was correct. But during review, three problems came up immediately.

The Design Review: Three Things I Got Wrong

Problem 1: Double Tagging on UpsZone

pointed at UpsZone straight away:

id           Int  @id @default(autoincrement())  // auto-generated: 1, 2, 3...
zoneNumber   Int  @unique                         // the actual zone: 2, 3, 4...
Enter fullscreen mode Exit fullscreen mode

Two columns both uniquely identifying the same thing. The id column is a surrogate key — a meaningless auto-generated number. The zoneNumber is the natural key — it actually means something. UPS Zone 2 is West Africa. That number is stable, defined by UPS, and will never change.

The problem with keeping both: foreign keys in other tables point to id: 1 which means zoneNumber: 2. One level of indirection that serves no purpose. When you read zone_id: 1 in the rates table you have to mentally translate "1 maps to zone 2." That's unnecessary cognitive overhead.

When you have a natural key that is stable, make it the primary key. Don't add a surrogate on top.

Fix: Make zoneNumber the @id directly.

Problem 2: Modeling a Feature We Don't Have

I had modeled two product types — ENVELOPE and PACKAGE — because the rate card had two sections. But the platform only handles packages. UPS Express Envelopes are a specialized service we're not offering.

I had built complexity for a feature that didn't exist. The UpsProductType enum, the productType column, the three-part unique constraint (zoneId, productType, weightKg) — all of it was solving a problem we don't have. The constraint simplifies to (zoneNumber, weightKg).

Model what the system actually does, not what it could theoretically do one day.

Fix: Remove the enum and the column entirely.

Problem 3: No Database Indexes

The rate lookup query at booking time will be:

SELECT price FROM ups_rates
WHERE zone_number = $1 AND weight_kg = $2
Enter fullscreen mode Exit fullscreen mode

Without an index, PostgreSQL scans every row to answer this. With 400 rows that's negligible today. At scale, under load, with many concurrent bookings — it becomes a problem.

The @@unique constraints already create implicit indexes, but explicit @@index declarations make the intent clear and give more control. The also realized, indexing zone_number and weight_kg on the rates table, and country_id on the zone-country table since that's how we look up which zone a destination country belongs to.

Fix: Add explicit indexes on the query path columns.

The Revised Schema 

model UpsZone {
  zoneNumber  Int      @id @map("zone_number")
  zoneName    String   @map("zone_name") @db.VarChar(100)
  description String?  @db.VarChar(255)
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  countries   UpsZoneCountry[]
  rates       UpsRate[]

  @@map("ups_zones")
}

model UpsZoneCountry {
  zoneNumber  Int      @map("zone_number")
  countryId   Int      @map("country_id")
  createdAt   DateTime @default(now()) @map("created_at")

  zone        UpsZone  @relation(fields: [zoneNumber], references: [zoneNumber])
  country     Country  @relation(fields: [countryId], references: [id])

  @@id([zoneNumber, countryId])
  @@index([zoneNumber])
  @@index([countryId])
  @@map("ups_zone_countries")
}

model UpsRate {
  zoneNumber  Int      @map("zone_number")
  weightKg    Decimal  @map("weight_kg") @db.Decimal(5, 2)
  price       Decimal? @db.Decimal(10, 2)
  currency    String   @default("NGN") @db.VarChar(3)
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  zone        UpsZone  @relation(fields: [zoneNumber], references: [zoneNumber])

  @@id([zoneNumber, weightKg])
  @@index([zoneNumber, weightKg, price])
  @@map("ups_rates")
}
Enter fullscreen mode Exit fullscreen mode

Cleaner. Every primary key is meaningful. Foreign keys point directly to zone numbers. The rate lookup path is (zoneNumber, weightKg) — two readable values, no translation layer needed.

Writing the Seed: My First Version Was Slow

With the schema sorted I needed to write the seed — the script that populates the database with initial data. The seed needed to:

  1. Create 8 zone rows
  2. Map roughly 150 countries to their zones
  3. Create rate rows for every zone and weight combination (8 zones × 50 weight brackets = 400 rows)

My first implementation followed the most natural pattern — loop through everything and upsert each row:

for (const zone of UPS_ZONES) {
  await db.upsZone.upsert({ ... });          // 1 call per zone

  for (const iso2 of iso2List) {
    const country = await db.country.findFirst({ where: { iso2 } }); // 1 call per country
    await db.upsZoneCountry.upsert({ ... });  // 1 call per country
  }

  for (const weightKg of PACKAGE_WEIGHTS) {
    await db.upsRate.upsert({ ... });         // 1 call per weight
  }
}
Enter fullscreen mode Exit fullscreen mode

"Too many DB calls. Look at upsertMany."

I counted the actual calls:

Operation Calls
Zone upserts 8
Country lookups ~150
Zone-country upserts ~150
Rate upserts 400
Total ~708

708 database round trips to seed a static reference table. Locally this ran in seconds and felt fine. But on a cloud database with network latency between the app server and the database, every round trip adds real overhead. And this runs every time you reset or re-seed a dev environment.

The Fix: Think in Sets, Not Rows

Prisma doesn't have upsertMany. But it has createMany with skipDuplicates: true — which inserts all rows in a single SQL statement and silently ignores any that would violate a unique constraint. That's exactly what idempotent seeding needs.

The real insight i got from this: stop doing work in the database that you can do in memory. JavaScript is fast. Building an array of 400 objects in memory takes microseconds. There's no reason to make 400 network round trips when you can build the entire dataset locally and send it in one shot.

Here's the refactored seed:

export async function addUpsSeed() {
  // 1. All zones — one call
  await db.upsZone.createMany({
    data: UPS_ZONES,
    skipDuplicates: true,
  });

  // 2. Fetch all countries at once — one call
  // Build a lookup map in memory — zero additional calls
  const allCountries = await db.country.findMany({
    select: { id: true, iso2: true },
  });
  const countryMap = new Map(allCountries.map(c => [c.iso2, c.id]));

  // 3. Build zone-country rows in memory, insert in one call
  const zoneCountryRows = [];
  for (const [zoneNumber, iso2List] of Object.entries(UPS_ZONE_COUNTRIES)) {
    for (const iso2 of iso2List) {
      const countryId = countryMap.get(iso2);
      if (!countryId) continue;
      zoneCountryRows.push({ zoneNumber: Number(zoneNumber), countryId });
    }
  }
  await db.upsZoneCountry.createMany({
    data: zoneCountryRows,
    skipDuplicates: true,
  });

  // 4. Build rate rows in memory, insert in one call
  const rateRows = UPS_ZONES.flatMap(zone =>
    PACKAGE_WEIGHTS.map(weightKg => ({
      zoneNumber: zone.zoneNumber,
      weightKg,
      price: null,
      currency: "NGN",
    }))
  );
  await db.upsRate.createMany({
    data: rateRows,
    skipDuplicates: true,
  });
}
Enter fullscreen mode Exit fullscreen mode

Total database calls: 4.

The country lookup that previously fired once per iso2 code is now a single findMany. Everything is fetched once, stored in a JavaScript Map for O(1) lookups, and the full dataset is built in memory before a single insert happens. The 400 individual rate upserts become one createMany. The 8 zone upserts become one createMany.

The first approach thought in rows — "insert this row, insert that row." The second approach thinks in sets — "here is the complete dataset, insert it all at once." Databases are built for set operations. Writing code that works with that instead of against it is one of those things that separates junior from senior thinking.

A Debugging Detour: The Adapter Problem

One challenge worth documenting separately because it wasn't obvious at all.

This project uses Prisma's newer prisma-client engine type, which requires a driver adapter — a separate package that handles the actual database connection. In production the project uses PrismaNeon from @prisma/adapter-neon, which connects to a Neon cloud database via WebSocket.

When I ran the seed locally, every database call failed silently. The error was a cryptic WebSocket ErrorEvent — no useful message, just a connection failure. It took real time to figure out what was happening.

The root cause: WebSocket doesn't work for localhost. A local PostgreSQL instance speaks standard TCP, not WebSocket. PrismaNeon is built for Neon's cloud infrastructure — it's the wrong adapter for a local connection.

The fix was to detect the environment from the connection string and switch adapters accordingly:

const isLocal = connectionString.includes('localhost') 
             || connectionString.includes('127.0.0.1')

if (isLocal) {
  const { PrismaPg } = await import('@prisma/adapter-pg')
  db = new PrismaClient({ 
    adapter: new PrismaPg({ connectionString }) 
  })
} else {
  const { PrismaNeon } = await import('@prisma/adapter-neon')
  db = new PrismaClient({ 
    adapter: new PrismaNeon({ connectionString }) 
  })
}
Enter fullscreen mode Exit fullscreen mode

PrismaPg uses standard TCP. PrismaNeon uses WebSocket. Same Prisma client, different transport layer depending on where you're running.

If you're working with Prisma's driver adapters mode and hitting silent connection failures locally, this is likely why.

Where Things Stand

The migration is done. The seed runs cleanly. The database has:

  • 8 zones correctly structured with meaningful primary keys
  • 400 rate rows with price: null — ready to be filled from the actual rate card
  • Zone-country mappings ready to populate once the country seed is fully set up

This was a good task to get early on a new project. You touch the database layer, you touch the seed infrastructure, you run into real environment issues, and you get a design review that teaches you things you wouldn't have caught on your own. All of that in one task.

If you found this useful or have questions about any of it, drop a comment.

Top comments (0)