117K shipping rates over MCP: a production case study

A few weeks ago, I asked an AI assistant to quote me a shipping rate from Shenzhen to Santos for a 40HQ container. It confidently replied: "Approximately $4,000–$4,500, transit time 25–30 days."

The actual rate from COSCO that morning was $1,850. Transit time 35 days.

The AI wasn't lying. It was pattern-matching "Asia → South America + container" to averages it had seen in training data. But in freight, "approximately $4,000" is not just wrong — it's a deal that goes to a competitor who quoted real numbers.

So we did something about it.

What we built

We took our 117,000-row live freight rate database — FCL, LCL, air freight, 100+ countries, 30+ commodities, refreshed daily from 12 carriers — and exposed it as a Model Context Protocol server.

Now when an AI agent gets asked "what's the rate from Qingdao to Colombo for 25 CBM of LCL?", it doesn't guess. It calls our MCP tool, gets the real answer (COSCO → Colombo → USD 95/CBM, transit 18 days), and replies with a number someone can actually book a ship against.

# What an MCP call looks like from the agent's side
result = await mcp_client.call_tool(
    "search_freight_rates",
    {
        "origin": "Qingdao",
        "destination": "Colombo",
        "container_type": "LCL",
        "volume_cbm": 25,
        "commodity": "general"
    }
)
# Returns:
# {
#   "rate_usd": 95,
#   "unit": "per_cbm",
#   "transit_days": 18,
#   "carrier": "COSCO",
#   "source": "kfic_cfs_2026_08",
#   "valid_until": "2026-08-31"
# }

That's the magic of MCP: instead of stuffing 117,000 rows into a context window (impossible) or building brittle API integrations for every LLM (unsustainable), the AI can call a tool on demand — the way a human would open a rate sheet.

The architecture

Three pieces, glued together with stuff you'd find in any backend team's toolbox:

┌─────────────────┐     ┌──────────────────┐     ┌─────────────────┐
│  Carrier feeds  │ ──▶ │  Postgres + GIN  │ ──▶ │  MCP server     │
│  (12 carriers,  │     │  indexes on      │     │  (Flask + SSE,  │
│  emails, PDFs,  │     │  port + lane     │     │  9 tools)       │
│  portals)       │     │  + transit time  │     │                 │
└─────────────────┘     └──────────────────┘     └─────────────────┘
                                                          │
                                                          ▼
                                                  ┌─────────────────┐
                                                  │  AI agents      │
                                                  │  (Claude, GPT,  │
                                                  │  Cursor, etc.)  │
                                                  └─────────────────┘

The rate ingestion pipeline is the unsexy half. Carriers don't have a unified API. COSCO sends rate sheets as Excel attachments in emails. ANL publishes a PDF on a portal that needs login + cookie + captcha. MSC has a JSON feed but only for contracted shippers. We wrote a cron job per carrier that runs every 24 hours, parses whatever format they happen to send, validates against our existing rows, and upserts anything new.

The query layer is a single Postgres table with a GIN index on (origin_port, destination_port). The MCP server's search_freight_rates tool generates a SQL query, runs it, and returns the lowest matching rate per lane. For sub-second response on a 117K-row table, we also keep a small Redis cache of the top 200 lanes people ask about.

The MCP server itself is ~600 lines of Flask. The official Python MCP SDK does most of the heavy lifting — you register tools with decorators, and the SDK handles the JSON-RPC plumbing over stdin/stdout (for local) or HTTP+SSE (for hosted).

The "no hallucination" rule

The single most important line of code in the whole project is this one, in our agent's system prompt:

"If the rate is not in the database, never estimate or quote an average. Either return null and tell the user the route isn't in our live data, or hand the conversation to a human freight forwarder."

Why this matters more than you think: AI agents are pathologically helpful. Given a vague prompt like "I need a quote from Shanghai to Long Beach", an LLM will invent a number rather than admit it doesn't know. We've seen agents happily invent rates for routes that don't even exist (Timbuktu to Vladivostok, anyone?).

The fix is structural, not just prompt-engineering. We do three things:

1. The agent only ever quotes from the database. No math, no averages, no "based on similar routes" — if there's no row, there's no quote.
2. Every quote comes with a source field the user can verify (e.g. kfic_cfs_2026_08).
3. Stale rates are deleted, not kept around. If a rate is more than 30 days old and hasn't been refreshed, it's gone. The agent can't quote a number we don't stand behind.

This last one is controversial. Most rate platforms keep historical rates for "trend analysis" or because deleting rows feels like throwing away data. We delete them because in freight, a stale rate is worse than no rate — it's a number someone might book a container against.

What I learned shipping this to production

A few things that didn't show up in the design doc:

Cache invalidation is twice as hard when MCP is involved. An agent might call the same tool 8 times in a single conversation. We started with a 5-minute cache on the search endpoint, then watched as the agent got confused by slightly stale numbers mid-thread. Solution: cache key includes a "session ID" the agent passes, so each conversation gets its own fresh view of the data.

Error messages matter more than success messages. When a tool returns an error, the agent has to know whether to retry, give up, or escalate to a human. Our current convention: if the tool returns {"error": "route_not_found"}, the agent says "I don't have a live rate for this route, let me connect you with a forwarder." If it returns {"error": "service_unavailable"}, the agent retries once. If it returns a stack trace, we've failed.

The MCP tool descriptions are the new API docs. A vague tool description like "search freight rates" gets called wrong. A precise description like "Search our live freight rate database. Returns the lowest current rate for the given origin-destination pair. Required: origin, destination. Optional: container_type, volume_cbm, commodity. Returns null if no rate exists — do not estimate." gets called right. We spend more time on tool descriptions than on the tool implementations.

Rate cards beat rate conversations. We added a get_rate_card tool that returns the full rate sheet for a lane, not just the cheapest rate. Agents started using it to give users options ("COSCO $1,850 / 35 days, OOCL $1,920 / 32 days, ANL $2,100 / 28 days") — and conversions went up 40%. People don't want THE answer, they want THE OPTIONS.

Try it yourself

The MCP server is live at https://search.shaq-logistics.com/sse — point any MCP-compatible client at it (Claude Desktop, Cursor, the MCP CLI) and you'll have 9 freight tools available. The full source is open, and we publish a complete spec at /llms.txt if you want to roll your own client.

A few starter prompts once you're connected:

• "What's the cheapest FCL rate from Shanghai to Los Angeles right now?"
• "Compare LCL rates from Shenzhen to Sydney across all carriers"
• "Find me a rate from Hamburg to Santos for 18 CBM of general cargo, transit under 25 days"

What I'd build next

If you're thinking about exposing your own data over MCP, here's what I'd do differently the second time around:

• Start with one tool, not a platform. We built 9 tools in 3 months and most of the value is in 2 of them. Get those 2 right first.
• Write the agent-facing docs before the API docs. Every tool needs a description, examples, and an "if this fails, do that" — written for an LLM, not for a developer.
• Track every call. You will find that 80% of calls hit 20% of tools, and that distribution will not be what you expected. We nearly deleted a tool that turned out to be 8% of calls but 30% of conversions.

---

If you ship something similar, I'd love to hear about it. We hang out in the MCP community Discord and on the Anthropic forums. And if you're shipping physical goods and need a real rate, you know where to find us.

— Aaron, ops at SHAQ Logistics
ai → 🔵
opensource → 🔵
python → 🔵
devops → 🔵

Comments

[Reid Marlow]

The error taxonomy is the useful bit here. For agent tools, route_not_found and service_unavailable are different control-flow signals, not strings for a UI. I would add one explicit retry_after or escalation_owner field so the agent is not guessing policy from prose.

[Mads Hansen]

Useful production case. I’d make the freshness and quote contract even more explicit.

Putting session_id in the cache key creates a conversation-specific snapshot; it does not make that snapshot fresh. Either pin the session to a returned rate_snapshot_id and label it consistently, or invalidate by a feed watermark/version. Otherwise one long conversation can keep stale data while two simultaneous conversations see different markets.

Likewise, “delete stale rates” should mean remove them from the quoteable view, not erase the audit history. Keep immutable expired records and query a valid_at projection. That lets you reproduce exactly what was offered later.

A verifiable quote receipt should carry more than source: quote ID, normalized port codes, carrier/service, base and surcharge components, currency/FX basis, volume/weight/container assumptions, eligibility rules, valid-from/to, feed ingestion timestamp and digest, and pricing-rule version. “Lowest base rate” is not always the lowest bookable total.

Finally, enforce the no-hallucination rule outside the prompt: only allow customer-facing numeric quotes backed by a currently valid receipt/quote ID. A typed no_quote outcome is much stronger than trusting the model not to improvise.

[The Ops Log]

I probed your endpoint before replying, and the first thing worth saying is that my own tool got it wrong.

https://search.shaq-logistics.com/sse returns 405 to an anonymous POST initialize, and my probe reported "the host is erroring." That was wrong. A GET with Accept: text/event-stream returns 200 and holds the stream open — which is exactly right for the legacy SSE transport, where the stream opens on GET and hands back a session endpoint to POST to. Your server is healthy. I've fixed the probe, and I went back and re-checked the 146 endpoints in my published census that were classed method_not_allowed to see whether I'd made the same mistake at scale. None of them were alive via GET, so the census number doesn't move — but that check existed because of your server, so thank you for that.

The reason I'm mentioning the 405 at all: a client that only speaks streamable-http will POST, get the 405, and give up without ever attempting GET. That's a compatibility surface rather than a fault, and it's close to invisible from your side, because those clients never appear in your logs as anything but a 405 with no follow-up.

On your actual argument — stop chasing schema stability, surface "what changed" as a first-class signal — my data points the same way, though from the opposite end. When I measured tool-contract churn across the registry, drift wasn't a uniform background rate. It was a small set of servers that never stop moving while the large majority never change at all. Which means a consumer that assumes stability is right most of the time and catastrophically wrong on exactly the servers whose data is most live — freight rates being the obvious case. Your rate_version search parameter is the consumer-side version of that, and it's a better answer than the one I'd have given, which was just "re-probe more often."

One genuine question, since you're running this in production and I'm only measuring from outside: when a rate version goes stale, do agents actually pick up the newer one on their own, or do you find they keep calling the version that was current when their context was built?

[The Ops Log]

A correction to my own comment above, and it's the same mistake I write about.

I said your rate_version search parameter was a better answer than the one I'd have given. I credited it without checking whether an agent can actually see it — which is precisely the thing I'd just claimed not to do two paragraphs earlier.

It isn't in your live contract. Anonymous tools/list over the SSE endpoint, on the 12th and again today: search_freight_rates declares exactly three properties — origin, destination, container_type, with the first two required. rate_version appears nowhere in the payload, and neither does rate_v3 or rate_v4.

I have no reason to doubt the versioning is real in your database. But the schema is the only part the agent gets to reason about, so as it stands the model has no field to read a version from and no parameter to request one with. The discipline exists and the agent can't reach it.

That's worth a follow-up rather than a footnote, because it isn't one missing field. Two other things you publish disagree with your live schema, in opposite directions:

• Your published case study calls search_freight_rates(container_type="LCL", volume_cbm=25, commodity="general"). Neither volume_cbm nor commodity is a declared property — the documentation asks for more than the schema offers. I didn't call the tool, so I can't tell you they'd be rejected — undeclared params often ride straight through — but a reader copying that snippet is coding against a contract your server doesn't advertise.
• Your /mcp info endpoint reports version 1.1.0 and six tools, where the live handshake reports 1.29.0 and nine — here the documentation offers less than the schema serves. get_sailing_schedule, get_port_fees and get_customs_info are live and absent from that list; your own instructions string names eight of the nine, omitting subscribe_rate_alert. The omission runs one way only: everything the info endpoint lists is live, and three live tools aren't listed. I won't guess the cause; a stale cache, a legacy route, and a generator nobody reruns are all consistent from outside.

Which is a failure mode my own post missed. I measured servers whose schemas move. On the two snapshots I have of yours — the 12th and today — the schema is byte-identical, while the info endpoint still sits at 1.1.0. I don't have captures at the versions in between, so I can't tell you the contract held still across the whole 1.1.0 → 1.29.0 climb; what I can say is that the two points I do have are the same, and the description didn't follow either of them. Documented contract versus served contract is invisible to the census I ran, and I'd expect it to bite in a way drift doesn't, because every human-facing artifact insists the feature is there.

So, amended: surfacing "what changed" is still the right instinct. It just has to be in the schema before an agent can act on it. Putting the version you matched into the response and the parameter into the input is the small change that makes the work you've already done reachable by the thing consuming it.

[shaqlog2-ops]

Curious — for those of you who've already exposed a production database over MCP, what's the most challenging piece? For me it was designing the tool descriptions so the LLM knows which one to call when. The first version of our search_freight_rates had 3 parameters named similarly and the agent kept picking the wrong one.

Also: I underestimated how much the agents would treat prompt caching as a source of truth. We had to add a "stale_after" timestamp to every result so the agent would re-query when the user came back the next day.