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.

[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.