How to Build a Custom MCP Server for E-commerce Automation with Node.js and PostgreSQL

Every e-commerce team I talk to has the same pile of small, annoying tasks. "Where is order 48213?" "How many units of the blue variant are left in the Dhaka warehouse?" "Which customers haven't reordered in 90 days?" A human opens the admin panel, clicks around, exports a CSV, and pastes the answer into Slack. It takes four minutes and it happens two hundred times a week.

That is exactly the problem a custom MCP server fixes. Model Context Protocol gives an AI agent a typed, permissioned way to call your own business logic. Instead of scraping your admin UI, the agent calls a tool you wrote, and your tool hits PostgreSQL directly.

I have built MCP servers for marketing workflows and internal ops tooling, and the e-commerce case is the one that pays for itself fastest. In this tutorial I will walk through building an MCP server ecommerce automation layer in Node.js with PostgreSQL, from schema to running agent, including the parts most tutorials skip: auth, read-only vs write tools, and idempotency.

What an MCP server actually is (the 90-second version)

MCP is a protocol, not a library and not a framework. It standardizes how an AI client (Claude Desktop, Claude Code, Cursor, your own agent loop) discovers and calls capabilities exposed by a server. The canonical specification lives at modelcontextprotocol.io.

Three concepts cover most of what you need:

• Tools: functions the model can call. get_order_status, search_products, create_refund.
• Resources: readable data the client can pull into context. Your return policy, a product catalog snapshot.
• Prompts: reusable templates the user can invoke.

The transport matters. For local development, stdio is simplest: the client spawns your process and talks over stdin/stdout. For anything shared across a team or a fleet of agents, you want Streamable HTTP, where your server is a normal long-running HTTP service behind your load balancer. (The older HTTP+SSE transport has been superseded by Streamable HTTP in the current spec, check the transports documentation for the latest guidance.)

For e-commerce automation I almost always recommend HTTP transport in production. Your database credentials stay on your infrastructure, not on every employee's laptop. If you are still weighing how MCP fits alongside the rest of your stack, this developer's overview of MCP in modern AI automation stacks is a good primer before you commit.

Why PostgreSQL is the right backend for this

You already have your orders in Postgres, or you have them in Shopify and a mirror in Postgres. Either way, the MCP server should not be the source of truth. It should be a thin, well-guarded read/write layer over data you already trust.

A few things Postgres gives you that make this pleasant:

• Row-level security if you run multi-tenant or multi-store.
• Materialized views for the expensive aggregate queries (repeat purchase rate, inventory aging) so the agent never triggers a 12-second scan.
• JSONB for the messy parts of order payloads from payment gateways.
• pg_stat_statements so you can see exactly which tool calls are slow in production.

The anti-pattern I see most: pointing the MCP server at your production OLTP tables and letting the model generate arbitrary SQL. Do not do that. Write explicit parameterized queries per tool. I will show the pattern below.

Project setup

Node 20 or newer. I use TypeScript because the MCP SDK's types catch tool schema mistakes before the model does.

mkdir mcp-ecommerce && cd mcp-ecommerce
npm init -y
npm i @modelcontextprotocol/sdk zod pg dotenv express
npm i -D typescript tsx @types/node @types/pg @types/express
npx tsc --init --target ES2022 --module NodeNext --moduleResolution NodeNext --outDir dist

Your .env:

DATABASE_URL=postgres://mcp_reader:secret@localhost:5432/shop
MCP_API_KEY=generate-a-long-random-string
PORT=8787

Create a dedicated database role. This is not optional, and it is the single highest-value five minutes you will spend:

CREATE ROLE mcp_reader LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE shop TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;

Read-only by default. When you need a write tool later, you create a second role with narrowly scoped INSERT/UPDATE grants on specific tables. Never GRANT ALL.

Designing the tool surface

This is where most MCP servers fail. Developers expose twenty tools with overlapping responsibilities and the model picks the wrong one half the time.

My rule: one tool per question a support agent or ops person would actually ask. Not one tool per table.

Here is a surface that has worked well across stores:

Tool	Type	Backing query	Notes
get_order_status	Read	orders + shipments join	By order number or email
search_products	Read	products with trigram index	Returns variant IDs
check_inventory	Read	inventory_levels	By SKU + location
list_recent_orders	Read	orders filtered by date/status	Paginated, hard cap 50
get_customer_summary	Read	Materialized view	LTV, order count, last order
create_draft_order	Write	orders insert	Idempotency key required
tag_order	Write	order_tags insert	Allowlisted tags only
request_refund	Write	Queue table, not direct	Human approves downstream

Notice that request_refund does not refund anything. It writes to a queue. Anything that moves money should land in a human review step, at least for the first six months. I have watched an agent confidently issue 40 refunds because a prompt said "resolve customer issues." Do not be that story. If you are deploying agents that can take real-world actions, pair this with the safeguards in securing AI agents in production.

Wiring up the server

Here is a minimal but real HTTP MCP server with two tools. I am using the SDK's McpServer class and Zod for input schemas. The exact API surface of the SDK evolves between releases, so treat this as a working sketch and check the official TypeScript SDK README for the current method signatures.

// src/server.ts
import express from 'express';
import { McpServer } from '@modelcontextprotocol/sdk/server/mcp.js';
import { StreamableHTTPServerTransport } from '@modelcontextprotocol/sdk/server/streamableHttp.js';
import { z } from 'zod';
import { pool } from './db.js';

const server = new McpServer({
  name: 'ecommerce-ops',
  version: '1.0.0',
});

server.registerTool(
  'get_order_status',
  {
    title: 'Get order status',
    description:
      'Look up an order by its order number or the customer email. Returns status, ' +
      'fulfillment state, tracking number, and line items.',
    inputSchema: {
      orderNumber: z.string().optional().describe('e.g. ORD-48213'),
      email: z.string().email().optional().describe('Customer email'),
    },
  },
  async ({ orderNumber, email }) => {
    if (!orderNumber && !email) {
      return {
        content: [{ type: 'text', text: 'Provide orderNumber or email.' }],
        isError: true,
      };
    }

    const { rows } = await pool.query(
      `SELECT o.id, o.number, o.status, o.total_cents, o.placed_at,
              s.carrier, s.tracking_number, s.delivered_at
         FROM orders o
         LEFT JOIN shipments s ON s.order_id = o.id
        WHERE ($1::text IS NOT NULL AND o.number = $1)
           OR ($2::text IS NOT NULL AND o.email = lower($2))
        ORDER BY o.placed_at DESC
        LIMIT 5`,
      [orderNumber ?? null, email ?? null],
    );

    if (rows.length === 0) {
      return { content: [{ type: 'text', text: 'No matching orders.' }] };
    }

    return {
      content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }],
    };
  },
);

server.registerTool(
  'check_inventory',
  {
    title: 'Check inventory',
    description: 'Return available units for a SKU at each warehouse location.',
    inputSchema: {
      sku: z.string().describe('Product SKU, case sensitive'),
    },
  },
  async ({ sku }) => {
    const { rows } = await pool.query(
      `SELECT location_code, on_hand, reserved, (on_hand - reserved) AS available
         FROM inventory_levels
        WHERE sku = $1
        ORDER BY location_code`,
      [sku],
    );
    return { content: [{ type: 'text', text: JSON.stringify(rows) }] };
  },
);

const app = express();
app.use(express.json());

app.post('/mcp', async (req, res) => {
  if (req.headers['x-api-key'] !== process.env.MCP_API_KEY) {
    return res.status(401).json({ error: 'unauthorized' });
  }
  const transport = new StreamableHTTPServerTransport({
    sessionIdGenerator: undefined,
  });
  res.on('close', () => transport.close());
  await server.connect(transport);
  await transport.handleRequest(req, res, req.body);
});

app.listen(process.env.PORT ?? 8787, () => {
  console.log('MCP server listening');
});

Two things worth calling out.

The description field is a prompt. The model reads it to decide which tool to call. Write it like documentation for a new hire: what it returns, what the inputs look like, what it does not do. Vague descriptions are the number one cause of bad tool selection.

Parameterized queries only. No string interpolation, ever. The model controls the input. Treat every tool argument as hostile.

If you would rather see the whole thing built from scratch in a smaller, framework-free shape, my Node.js MCP server step-by-step tutorial covers the same SDK without the e-commerce domain layer.

The Postgres layer: indexes that matter

The tools above are only fast if the schema supports them. Three indexes carry most of the load:

-- Order lookup by number and by email
CREATE UNIQUE INDEX orders_number_idx ON orders (number);
CREATE INDEX orders_email_placed_idx ON orders (lower(email), placed_at DESC);

-- SKU search with fuzzy matching for typos
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING gin (name gin_trgm_ops);

-- Inventory by SKU
CREATE INDEX inventory_sku_idx ON inventory_levels (sku);

The trigram index is the one people forget. An agent searching for "wireles earbuds" should still find the product. pg_trgm plus similarity() handles that in one index scan. See the pg_trgm documentation for the operator classes and functions available.

For the customer summary tool, precompute:

CREATE MATERIALIZED VIEW customer_summary AS
SELECT c.id,
       c.email,
       COUNT(o.id) AS order_count,
       SUM(o.total_cents) AS lifetime_value_cents,
       MAX(o.placed_at) AS last_order_at
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
 GROUP BY c.id, c.email;

CREATE UNIQUE INDEX ON customer_summary (id);

Refresh it every 15 minutes with REFRESH MATERIALIZED VIEW CONCURRENTLY customer_summary. The CONCURRENTLY keyword needs the unique index, and it means reads never block during refresh. (Note: CONCURRENTLY requires PostgreSQL 9.4+, which is well below any version you should be running today.)

Write tools and idempotency

Read tools are easy. Write tools are where you earn your salary.

The pattern I use for every mutating tool:

1. Require an idempotencyKey in the input schema.
2. Insert into a tool_invocations table with a unique constraint on that key.
3. If the insert conflicts, return the previously stored result instead of re-executing.
4. Wrap the actual mutation and the invocation record in one transaction.

CREATE TABLE tool_invocations (
  idempotency_key text PRIMARY KEY,
  tool_name text NOT NULL,
  result jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

async function withIdempotency<T>(
  key: string,
  toolName: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const existing = await client.query(
      'SELECT result FROM tool_invocations WHERE idempotency_key = $1',
      [key],
    );
    if (existing.rowCount) {
      await client.query('ROLLBACK');
      return existing.rows[0].result as T;
    }
    const result = await fn(client);
    await client.query(
      'INSERT INTO tool_invocations (idempotency_key, tool_name, result) VALUES ($1,$2,$3)',
      [key, toolName, result],
    );
    await client.query('COMMIT');
    return result;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

Agents retry. Networks flap. Without this, a retried create_draft_order creates two orders and your warehouse ships twice. I have seen this exact bug in a client's system before we added the pattern, and the cleanup took a full day.

Connecting an agent

For Claude Code, add the server to your MCP config. The exact config shape varies by client and version, check the Claude Code MCP documentation for the current format.

{
  "mcpServers": {
    "ecommerce-ops": {
      "type": "http",
      "url": "https://mcp.internal.yourstore.com/mcp",
      "headers": { "x-api-key": "generate-a-long-random-string" }
    }
  }
}

For a local dev loop, use stdio instead so you skip the HTTP hop:

{
  "mcpServers": {
    "ecommerce-ops": {
      "command": "node",
      "args": ["dist/stdio.js"],
      "env": { "DATABASE_URL": "postgres://mcp_reader:secret@localhost:5432/shop" }
    }
  }
}

Then ask it: "Check inventory for SKU BLU-TSHIRT-M at all locations and tell me if we can fulfill 400 units." If your tool descriptions are good, it will call check_inventory once and reason over the result. If it calls three wrong tools first, rewrite the descriptions.

Security checklist before you ship

Risk	Mitigation
Prompt injection via order notes	Never pass raw customer text into tool descriptions; sanitize before returning
Over-broad DB access	Dedicated role, SELECT only, separate write role
Credential sprawl	Server holds creds, clients hold only an API key
Runaway queries	statement_timeout = '5s' on the MCP role
PII leakage into logs	Log tool name and duration, not arguments
Unbounded result sets	Hard LIMIT in every query, paginate explicitly
Money movement	Queue table + human approval, never direct

Set the statement timeout at the role level:

ALTER ROLE mcp_reader SET statement_timeout = '5s';

An agent that hits a timeout gets a clean error and retries differently. An agent that hits a 90-second query gets a hung session and a support ticket.

Measuring whether it worked

Track three numbers from day one:

• Tool calls per resolved question. If it is above 3, your tool descriptions or granularity are wrong.
• p95 latency per tool. Anything above 500ms needs an index or a materialized view.
• Human escalation rate. The share of conversations where someone still opened the admin panel. This is the real ROI metric.

I have seen teams cut support response time substantially just by exposing get_order_status and check_inventory. The flashy automation is not where the value is. The value is in eliminating the four-minute lookup. If you want a broader menu of workflows worth automating first, these seven AI automation workflows for small business are a good place to start.

When to build vs when to buy

If you are on Shopify and only need product and order reads, an off-the-shelf MCP server will get you most of the way in an afternoon. Build custom when:

• Your data lives in Postgres or an ERP, not the platform.
• You need write operations with your own approval rules.
• You have multi-store or multi-tenant authorization requirements.
• You need to enforce business logic the platform does not know about.

For founders evaluating this, the honest framing is: an MCP server is a thin API with a schema an AI can read. If you already have clean APIs, it is days of work, not months. If your data is spread across a legacy ERP, three spreadsheets, and a WooCommerce install, budget for the integration work first. The MCP layer is the easy part.

If you are in Dhaka, Sydney, or anywhere else and want a second opinion on whether your stack is ready for this, that conversation usually takes thirty minutes and saves a quarter of misdirected effort.

FAQ

Q: Do I need to use TypeScript for a Node.js MCP server?

A: No, the SDK works fine in plain JavaScript. But the tool input schemas are the contract between your server and the model, and TypeScript plus Zod catches mismatches at build time instead of at 2am when an agent sends a string where you expected a number. For anything beyond a prototype I use TypeScript.

Q: Can I point the MCP server directly at my production database?

A: Yes, with a dedicated read-only role and a short statement timeout. That is what the mcp_reader role above is for. What you should not do is give the server a superuser connection or let it generate arbitrary SQL from model output. Explicit parameterized queries per tool, always.

Q: How is this different from just giving the AI a REST API?

A: Functionally similar, but MCP standardizes discovery, schema, and transport so any compliant client can use your server without custom glue. You write the tool definitions once and Claude, Cursor, and your own agent loop all understand them. With REST you end up writing an OpenAPI-to-tool adapter per client.

Q: What is the minimum viable MCP server for e-commerce?

A: Two read tools: order status lookup and inventory check. Those cover the majority of repetitive support questions in most stores I have worked with. Add write tools only after you have idempotency and an approval path in place.

Where to go from here

Start with the read-only server. Get one real question answered end to end. Then add a single write tool behind an approval queue and watch what the agent does with it for a week before you widen the permissions.

If you want help designing the tool surface, hardening the Postgres layer, or wiring MCP into an existing Next.js or NestJS backend, I build these regularly for clients across Bangladesh, Australia, the USA, Canada, and New Zealand. Reach out through the contact page and tell me what your team is doing manually today. That is usually enough for me to tell you whether an MCP server is the right fix or whether you should just add an index.

Frequently asked questions

Do I need to use TypeScript for a Node.js MCP server?

No, the SDK works fine in plain JavaScript. But the tool input schemas are the contract between your server and the model, and TypeScript plus Zod catches mismatches at build time instead of at 2am when an agent sends a string where you expected a number. For anything beyond a prototype I use TypeScript.

Can I point the MCP server directly at my production database?

Yes, with a dedicated read-only role and a short statement timeout. That is what the mcp_reader role above is for. What you should not do is give the server a superuser connection or let it generate arbitrary SQL from model output. Explicit parameterized queries per tool, always.

How is this different from just giving the AI a REST API?

Functionally similar, but MCP standardizes discovery, schema, and transport so any compliant client can use your server without custom glue. You write the tool definitions once and Claude, Cursor, and your own agent loop all understand them. With REST you end up writing an OpenAPI-to-tool adapter per client.

What is the minimum viable MCP server for e-commerce?

Two read tools: order status lookup and inventory check. Those cover the majority of repetitive support questions in most stores I have worked with. Add write tools only after you have idempotency and an approval path in place.