Building a Read-Only MCP Server for Loyalty Diagnostics

Operational support for a loyalty platform can be surprisingly hard. A simple customer question like "where did my points go?" may require checking profile state, card links, point balances, holds, order traces, campaign activity, audit history, and sometimes migration history.

The old way to answer those questions was a mix of dashboards, SQL snippets, service logs, and tribal knowledge. It worked, but it was slow and risky. Raw database access is too powerful for repetitive support checks, and large language models should not be handed arbitrary SQL access just because they are good at summarising evidence.

The design I settled on was a small Model Context Protocol server: read-only, purpose-built, and shaped around the diagnostic questions engineers actually answer.

The Shape of the Service

The service is a Spring Boot application exposing MCP tools over HTTP. The agent does not connect to production databases directly. It calls a fixed set of tools, each with typed inputs and a narrow output model.

AI agent
  |
  | MCP tool call
  v
Read-only diagnostics service
  |
  +-- tool layer: named workflows, read-only hints
  +-- validator: UUIDs, dates, site codes, limits
  +-- service layer: compose evidence across systems
  +-- repository layer: fixed SQL per data source
  |
  v
Read-only data sources

The important decision is that the MCP service is not a general-purpose database proxy. It is closer to an operational API for investigation. Every tool represents a question we are comfortable letting an agent ask.

Designing Tools Around Workflows

I found it more useful to expose investigation workflows than to expose individual tables. The tool set ended up covering a few common support paths:

• Customer snapshot: current loyalty profile, active cards, points balance, and held points.
• Card trace: where a card is linked now, and whether there are suspicious duplicate active links.
• Order trace: transaction rows, points logs, context summaries, and holds for one order or source ID.
• Transaction trace: adapter state and points ledger logs for a customer or transaction ID.
• Promotion trace: active promotions or a specific promotion by ID and market.
• Audit trace: focused customer audit history, including merge-related events.
• Health check: whether the configured read-only data sources are reachable.

Each response includes raw enough evidence to be auditable, but not so much that the agent has to rediscover the whole domain every time. For example, an order trace returns the rows it found plus a compact summary such as counts of reserve, purchase, refund, and held-point signals.

Safety and Privacy Boundaries

The first safety rule is simple: no writes. The service uses read-only data source credentials, configures database sessions as read-only, and exposes no mutation tools. It is for diagnosis, not repair.

The second rule is to avoid arbitrary SQL. Agents are good at exploring, but exploration against live systems needs guardrails. Fixed tool contracts make the blast radius smaller:

• Only known workflows are exposed.
• Inputs are validated before reaching the repository layer.
• Row limits are capped.
• Dates must be explicit and parseable.
• Identifiers must match expected formats.
• Partial failures are returned as diagnostic gaps rather than hidden behind confident prose.

Privacy matters as much as database safety. A public demo or blog post should use synthetic identifiers, generic market names, and anonymized examples. Internally, the MCP result may contain data needed for an investigation; externally, the story should focus on the design pattern.

Implementation Notes

Read-only tool declarations

Each MCP method is declared as read-only and idempotent. That metadata is not a substitute for proper database controls, but it documents intent at the tool boundary and helps compatible clients reason about safe tool usage.

@McpTool(
    name = "get_customer_snapshot",
    description = "Read-only snapshot of loyalty state for a customer and market",
    annotations = @McpAnnotations(
        readOnlyHint = true,
        destructiveHint = false,
        idempotentHint = true
    )
)
public CustomerSnapshot getCustomerSnapshot(String customerId, int marketId) {
    CustomerMarketInput input = validator.parseCustomerMarket(customerId, marketId);
    return diagnosticsService.getCustomerSnapshot(input.customerId(), input.marketId());
}

Validation at the edge

The tool layer validates UUIDs, optional filters, ISO timestamps, site or market identifiers, order IDs, card IDs, and result limits. Invalid input fails early with a clear message instead of becoming a slow or unsafe query.

Repository isolation

The repository layer owns source-specific SQL. The service layer composes those repositories into business traces: customer state, order state, promotion state, and audit state. This keeps the MCP tool methods small and keeps database details out of the agent-facing contract.

Probable issue flags

One useful addition was a small "probable issue" field on diagnostic responses. It does not replace the evidence, but it gives the agent a stable signal to start from:

{
  "orderId": "ORDER-123",
  "summary": {
    "purchaseLogCount": 1,
    "refundLogCount": 2,
    "hasHeldPoints": false
  },
  "probableIssue": "POSSIBLE_DUPLICATE_REVERSAL"
}

These flags are deliberately plain and conservative. They help the response stay concrete: "I found this evidence, and this is the likely category of problem."

Health checks as a first-class tool

A diagnostics service is only useful if you can tell whether its own data sources are reachable. Exposing health as an MCP tool made setup and incident checks much easier. Before investigating a customer issue, the agent can verify whether the underlying sources are up, down, or unconfigured.

Lessons Learned

1. Build tools for questions, not tables. Support workflows start with business questions. The API should match that shape.
2. Read-only still needs design. A read-only query can still be expensive, confusing, or privacy-sensitive.
3. Return evidence and interpretation separately. Raw rows let an engineer verify the answer. Summary fields and issue flags help the agent explain it.
4. Make failure visible. If one data source times out, the result should say what was missing instead of pretending the world is complete.
5. Redaction is part of the product. A tool used by agents should make it easy to answer with only the necessary customer information.

MCP is useful here because it gives agents a clean way to call operational tools. The real value, though, comes from the boring backend discipline around it: narrow interfaces, read-only credentials, input validation, capped outputs, health checks, and clear domain-specific summaries.