MCP Server: an AI interface over real commerce data, and an independent way to check its answers

At a glance

What it is

An MCP server over a Spree store in PostgreSQL, imported from the public Olist dataset, and an independent correctness harness that recomputes every answer from the raw data.

What runs today

A live, read-only demo deployment: OAuth with PKCE, exactly 9 read-only tools, writes disabled. Only the store admin can grant access, so visitors can inspect the discovery documents, not use the tools.

Hardest problems

1. Tools that already looked right, and were not: 5 of 35 canonical questions right before the fixes.
2. Deciding what each commerce metric means over reviews, sellers, split payments and cancelled orders.
3. An oracle that shares no code with the server, so a shared bug cannot hide.
4. A current Claude client exposed two problems the repository's own tests had not.

Stack

Ruby on Rails, Spree Commerce, PostgreSQL, Doorkeeper for OAuth, the MCP Ruby SDK, Kamal, and CI on every push.

My role

Sole engineer: I designed, built and operate it, using AI coding agents (Claude) under my direction and review.

More

The repository (MIT) · the build log, with a dated update

The demo in 88 seconds

The question

The first version, written up in August 2026, answered questions about the store fluently. Nothing showed whether its numbers were right. So the engineering question became concrete: how do you design MCP tools that give an AI correct answers over real, messy commerce data, and how do you show that they do?

Real orders do not line up with the easy join. 555 orders carry more than one review, 1,278 orders have more than one seller, payments can be split across methods, and cancelled and unavailable orders sit next to completed ones. Every tool returned a plausible number; none had a written definition of what it counted. Every metric now has one: its population, freight, multi-seller and multi-review treatment, grain, and whether it adds up across a breakdown, with five decisions I approved as the owner. Figures on this page are under those definitions.

The approach: two paths that share no code

• A system under test. The Olist CSVs are imported into a Spree store in PostgreSQL. The MCP server exposes 9 read-only tools over OAuth with PKCE; the two write tools in the code are not exposed.
• An independent oracle. Plain Ruby over the raw CSVs, with no importer, Spree model, SQL or tool code; a test asserts it issues no database queries. It shares only the input files and the written definitions, and compares answers field by field, to the cent.
• A harness over everything the tools say. 35 canonical questions, 9 invariants over 150 seeded cases, 6 differential checks over 200 seeded random cases, a CSV-to-database reconciliation, and 12 known bugs it must catch.
• An agent evaluation. Claude Code answers 15 questions with only the tools, graded deterministically against answers the oracle computed and committed before any run. No model judges the answers.

What was wrong

Run against the tools as they were, with the same harness and seed, the tools answered 5 of 35 canonical questions correctly. The import itself was sound: the CSV-to-database reconciliation passed before and after. The defects were in the tools.

Check	Before	After
Canonical questions, to the cent	5 / 35	35 / 35
Invariants (150 seeded cases)	4 / 9	9 / 9
Differential checks (200 seeded random cases)	0 / 6	6 / 6
Reconciliation, CSV to database	2 / 2	2 / 2
Known bugs caught	3 / 3 historical	12 / 12: 3 historical, 9 pre-fix

The nine defects

Defect	What the harness showed	The rule now
Review fan-out	555 orders have several reviews; search reported 100,000 matches for 99,441 orders; one customer's lifetime value doubled (R$46.85 shown as R$93.70)	One review score per order
Review policy	No rule for which review counts	An order's most recent review
Category revenue	Two tools returned R$1,258,681 and R$1,445,137 for Health Beauty	Named metrics: item revenue R$1,255,695.13, gross R$1,437,665.78
Seller attribution	In 1,278 multi-seller orders, sellers were credited with other sellers' items; the top seller read R$253,122 in one tool, R$229,473 in another	Allocate at the item grain
Cancelled as sales	Product, category and seller figures counted cancelled and unavailable orders	Completed orders only
Overlapping totals	Overlapping buckets presented as if they added up	Each breakdown says whether it adds up
Split payments	Credit card overstated by R$185,429 (R$12,534,959 against R$12,349,530 collected); voucher about 1.4x	Payment value, its own metric
Delivery population	Six cancelled orders in delivery metrics	Delivered orders only
Customer lookup	Email matched as a substring	Exact, case-insensitive match

The MCP plumbing was not the hard part. The hard part was deciding what each commerce metric means over messy relational data, writing it down, and checking every tool against it. Each fix is kept as a regression the harness must go on catching.

Reproducible evaluation

Every push to the repository rebuilds the evidence in CI: the pinned Olist dataset, checked file by file with SHA-256; a fresh PostgreSQL database and a full import; 52 tests (20 oracle self-tests, 21 HTTP/MCP boundary tests, 11 grader tests); then the whole harness. A run takes about 6 to 8 minutes.

The harness is deterministic. It ends with a fingerprint that hashes every check's id, status and differences and every regression verdict, leaving out timings, so two runs on the same data, code and seed agree exactly or visibly do not. Local runs and CI print the same one: f4841a48894b268d.

The 12 known bugs are the 9 pre-fix versions of the tools plus 3 reconstructed historical bugs. One reproduces the original write-up's inflated category revenue, R$52,545,084, exactly on the pre-fix counters; another (33 dropped payments) is a simulated effect.

Agent evaluation

Claude Code 2.1.284 with one model, claude-sonnet-5, answered 15 questions three times each with only the store's tools. 45 of 45 answers agreed with the pre-registered oracle answers, with an acceptable tool and acceptable arguments in all 45 runs, and 15 of 15 questions correct on every run.

14 of 15 gave an identical answer every time. The one variation was arithmetic: the overall late rate, which no tool reports, came back as 7.93 twice and 7.9 once against a correctly rounded 7.92, all inside the pre-registered 0.1-point tolerance. This is an engineering sample of one model and one client, not a benchmark and not a measure of model performance in general. A planned tool-description experiment was not run: the baseline was already at the ceiling.

Live verification

The live, read-only demo deployment at https://mcp-demo.railsfanatics.com/mcp requires OAuth with PKCE (S256), with discovery and dynamic client registration. Only the store admin can grant access, and only the mcp:read scope is issued, so a client sees the 9 read tools and no write tools.

Testing with a current Claude client found two problems the repository's own tests had not. Claude Code negotiated an MCP protocol revision the Ruby SDK claimed but did not implement: it saw zero tools and, in one probe, invented an answer (6 cancelled 2018 orders; the oracle says 334). Pinning the server to revision 2025-11-25 fixed it. The OAuth server also rejected Claude Code's localhost callback; plain HTTP is now allowed for loopback callbacks only, and every other callback still requires HTTPS.

After both fixes, Claude Code connected over OAuth, negotiated 2025-11-25 and saw exactly 9 read tools. Over the live endpoint, 334 orders placed in 2018 were cancelled, and November 2017 revenue was R$1,172,191.68 (gross revenue: items plus freight, completed orders). Both match the oracle.

What is shown and what is not

Area	Status, and what it means
Correctness of the tools	Shown, within the checks run. 35/35 canonical questions to the cent, 9/9 invariants, 6/6 differential and 2/2 reconciliation checks, 12/12 known bugs caught. Not a claim about questions outside these checks.
Deployment	A live, read-only demo. Not a production system and not used by a business. Access is owner-consented through OAuth.
Data	Public and anonymised. The Olist orders are real but anonymised, and product names are synthesized by the importer.
Agent evaluation	An engineering sample. One model, one client, 15 questions, graded against pre-registered answers. Not a benchmark.
Write tools	Not exposed. Two write tools exist in the code; the deployment serves read tools only.
Open findings	Tracked. 14 findings: 11 verified, 2 deferred, 1 out of scope. One deferred item is a defence-in-depth consideration: customer-written review text reaches the model without a "treat as data" label in structured results. The model declined it in 6 of 6 local probes; it is not claimed as solved.

How it was built

I designed, built and operate it as a solo engineer, from the first build in August 2026 to the verification work in September 2026. I used AI coding agents (Claude) for much of the implementation, under my direction and review. I set the architecture, the metric definitions and the acceptance bars, approved every production change and ran the releases.

What this demonstrates for client work

• Define the metric before the tool, or every tool answers a different question.
• Check AI-facing tools against a source that shares no code with them, so a shared bug cannot pass both.
• Keep every fixed defect as a regression that the checks must go on catching.
• Test with the real client, not only with the repository's own tests.
• Say what is not shown, alongside what is.

Related writing

• I'm Learning MCP by Building a Server Claude Can Actually Log Into, with a dated update on what the verification found
• Remote MCP on Rails: everything that broke
• Doorkeeper's most dangerous defaults are the ones you didn't set

Data: Brazilian E-Commerce Public Dataset by Olist, CC BY-NC-SA 4.0, 99,441 orders from 2016 to 2018, anonymised. State as of 29 September 2026.